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();
        }
    }

返回結果