<input id="ohw05"></input>
  • <table id="ohw05"><menu id="ohw05"></menu></table>
  • <var id="ohw05"></var>
  • <code id="ohw05"><cite id="ohw05"></cite></code>
    <label id="ohw05"></label>
    <var id="ohw05"></var>
  • ElasticSearch7.3學習(三十)----ES7.X SQL新特性解析及使用Java api實現sql功能

    一、ES7 sql新特性

    1.1 數據準備

    創建索引及映射

    建立價格、顏色、品牌、售賣日期 四個字段

    PUT /tvs
    PUT /tvs/_mapping
    {
      "properties": {
        "price": {
          "type": "long"
        },
        "color": {
          "type": "keyword"
        },
        "brand": {
          "type": "keyword"
        },
        "sold_date": {
          "type": "date"
        }
      }
    }

    插入數據

    POST /tvs/_bulk
    {"index":{}}
    {"price":1000,"color":"紅色","brand":"長虹","sold_date":"2019-10-28"}
    {"index":{}}
    {"price":2000,"color":"紅色","brand":"長虹","sold_date":"2019-11-05"}
    {"index":{}}
    {"price":3000,"color":"綠色","brand":"小米","sold_date":"2019-05-18"}
    {"index":{}}
    {"price":1500,"color":"藍色","brand":"TCL","sold_date":"2019-07-02"}
    {"index":{}}
    {"price":1200,"color":"綠色","brand":"TCL","sold_date":"2019-08-19"}
    {"index":{}}
    {"price":2000,"color":"紅色","brand":"長虹","sold_date":"2019-11-05"}
    {"index":{}}
    {"price":8000,"color":"紅色","brand":"三星","sold_date":"2020-01-01"}
    {"index":{}}
    {"price":2500,"color":"藍色","brand":"小米","sold_date":"2020-02-12"}

    1.2 簡單示例

    POST /_sql?format=txt
    {
      "query": "SELECT * FROM tvs "
    }

    返回類似于數據庫的界面

     

    二、啟動方式

    2.1 http請求

    POST /_sql?format=txt
    {
      "query": "SELECT * FROM tvs "
    }

    2.2 客戶端啟動

    elasticsearch-sql-cli.bat

    雙擊執行,在控制臺輸入show tables;即可查詢到ES中所有的索引

    2.3 Java代碼

    這種方式在下面會介紹

    三、顯示方式

    上面的ES語句中可以看到format = txt,這個是控制返回結果為txt格式,當然也支持其他的格式。如下圖所示

    可以簡單的看下其他的格式,例如說tsv,以tab做分割的格式

    例如說csv

    四、sql 翻譯

    既然支持sql語句,那么底層是怎么運行的呢? 實際上還是轉換成query語句來執行,我們可以用translate來查看一下

    五、與其他DSL結合

    也可以與其他查詢語句相結合使用,例如說,查詢價格在1200~2000范圍內的數據

    六、Java 代碼實現sql功能

    6.1 免費試用sql功能

    如果要使用代碼實現sql功能,對于ES來說就要收費了。必須具備白金版功能,沒有的話 Java代碼會報錯current license is non-compliant for [jdbc]

    java.sql.SQLInvalidAuthorizationSpecException: current license is non-compliant for [jdbc]
    	at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection$SqlExceptionType.asException(JreHttpUrlConnection.java:321)
    	at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.parserError(JreHttpUrlConnection.java:198)
    	at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.request(JreHttpUrlConnection.java:161)
    	at org.elasticsearch.xpack.sql.client.HttpClient.lambda$post$1(HttpClient.java:105)
    	at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.http(JreHttpUrlConnection.java:65)
    	at org.elasticsearch.xpack.sql.client.HttpClient.lambda$post$2(HttpClient.java:104)
    	at java.security.AccessController.doPrivileged(Native Method)
    	at org.elasticsearch.xpack.sql.client.HttpClient.post(HttpClient.java:103)
    	at org.elasticsearch.xpack.sql.client.HttpClient.query(HttpClient.java:80)
    	at org.elasticsearch.xpack.sql.jdbc.JdbcHttpClient.query(JdbcHttpClient.java:68)
    	at org.elasticsearch.xpack.sql.jdbc.JdbcStatement.initResultSet(JdbcStatement.java:160)
    	at org.elasticsearch.xpack.sql.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
    	at org.elasticsearch.xpack.sql.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:40)
    	at com.itheima.es.TestJdbc.main(TestJdbc.java:17)

    當然我們可以免費試用一段時間

    kibana中管理 ==> 許可管理  ==> 開啟白金版試用

    6.2 導入依賴

        <dependency>
            <groupId>org.elasticsearch.plugin</groupId>
            <artifactId>x-pack-sql-jdbc</artifactId>
            <version>7.3.0</version>
        </dependency>
        
        <repositories>
            <repository>
                <id>elastic.co</id>
                <url>https://artifacts.elastic.co/maven</url>
            </repository>
        </repositories>

    6.3 Java代碼

        public static void main(String[] args) {
            //1創建連接
            try {
                Connection connection = DriverManager.getConnection("jdbc:es://http://localhost:9200");
                //2創建statement
                Statement statement = connection.createStatement();
                //3執行sql語句
                ResultSet resultSet = statement.executeQuery("select * from tvs");
                //4獲取結果
                while (resultSet.next()) {
                    String str = resultSet.getString(1)+","
                            +resultSet.getString(2)+","
                            +resultSet.getString(3)+","
                            +resultSet.getString(4);
                    System.out.println(str);
                    System.out.println("======================================");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    返回結果

     

     

    posted @ 2022-06-06 22:39  |舊市拾荒|  閱讀(175)  評論(0編輯  收藏  舉報
    国产美女a做受大片观看