當前位置：
首頁 > 知識 > Elasticsearch SQL案例介紹

Elasticsearch SQL案例介紹

知識 10-17

bboss ES SQL是針對es jdbc的替代解決方案

the best elasticsearch highlevel java rest api-----bboss

bboss 提供一組sql和fetchQuery API，可替代es jdbc模塊；採用bboss即可擁有bboss的客戶端自動發現和容災能力、對es、jdk、spring boot的兼容性能力，又可以擁有es jdbc的所有功能，同時還解決了因為引入es jdbc導致項目對es版本的強依賴和兼容性問題，參考demo：

orm查詢

https://gitee.com/bbossgroups/eshelloword-booter/blob/master/src/test/java/org/bboss/elasticsearchtest/sql/SQLOrmTest.java

分頁查詢

https://gitee.com/bbossgroups/eshelloword-booter/blob/master/src/test/java/org/bboss/elasticsearchtest/sql/SQLPagineTest.java

本文詳細說明上面的案例（目前es sql提供的功能有限）：

  • 集合orm查詢
  • 單文orm檔查詢
  • fetchsize orm查詢

1 orm查詢

1.1 定義orm查詢的實體bean

package org.bboss.elasticsearchtest.sql;
import com.frameworkset.orm.annotation.Column;
import java.util.Date;
public class DocObject {
private int isnew;
private Date createtime;
private String content;
private int documentId;
private int channelId;
/**
* 通過column指定索引文檔和對象屬性的映射關係
* 通過column註解還可以指定日期格式和時區信息
* @Column(name="docInfo.author",dataformat = "yyyy-MM-dd"T"HH:mm:ss.SSS"Z"",timezone = "Etc/UTC",locale = "zh")
*
*/
@Column(name="docInfo.author")
private String docInfoAuthor;
public int getIsnew() {
return isnew;
}
public void setIsnew(int isnew) {
this.isnew = isnew;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getDocumentId() {
return documentId;
}
public void setDocumentId(int documentId) {
this.documentId = documentId;
}
public int getChannelId() {
return channelId;
}
public void setChannelId(int channelId) {
this.channelId = channelId;
}
public String getDocInfoAuthor() {
return docInfoAuthor;
}
public void setDocInfoAuthor(String docInfoAuthor) {
this.docInfoAuthor = docInfoAuthor;
}
}

實體定義說明：

通過column指定索引文檔和對象屬性的映射關係，指定日期格式和時區信息,示例如下：

@Column(name="docInfo.author")
private String docInfoAuthor;

指定屬性的映射關係、日期格式和時區信息,示例如下：

@Column(name="docInfo.author",dataformat = "yyyy-MM-dd"T"HH:mm:ss.SSS"Z"",timezone = "Etc/UTC",locale = "zh")

執行orm查詢

以rest sql api為例來介紹es 6.3.0的sql orm查詢功能

package org.bboss.elasticsearchtest.sql;
import org.frameworkset.elasticsearch.ElasticSearchHelper;
import org.frameworkset.elasticsearch.client.ClientInterface;
import org.frameworkset.elasticsearch.entity.sql.SQLRestResponse;
import org.frameworkset.elasticsearch.entity.sql.SQLRestResponseHandler;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 以rest sql api為例來介紹es 6.3.0的sql orm查詢功能
*/
public class SQLOrmTest {
/**
* 代碼中的sql檢索，返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testDemoQuery(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
List<Map> json = clientUtil.sql(Map.class,"{"query": "SELECT * FROM demo"}");
System.out.println(json);
}
/**
* 代碼中的sql檢索，返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testMapQuery(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
List<Map> json = clientUtil.sql(Map.class,"{"query": "SELECT * FROM dbclobdemo"}");
System.out.println(json);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testMapSQLQueryFromDSL(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",1);
List<Map> json = clientUtil.sql(Map.class,"sqlQuery",params);
System.out.println(json);
}
/**
* 代碼中的sql檢索，返回Map類型對象，亦可以返回自定義的對象
*/
@Test
public void testMapObjectQuery(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
Map json = clientUtil.sqlObject(Map.class,"{"query": "SELECT * FROM dbclobdemo"}");
System.out.println(json);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型對象，亦可以返回自定義的對象
*/
@Test
public void testMapObjectSQLQueryFromDSL(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",1);
Map json = clientUtil.sqlObject(Map.class,"sqlQuery",params);
System.out.println(json);
}
/**
* 代碼中的sql檢索，返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testObjectListQuery(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
List<DocObject> json = clientUtil.sql(DocObject.class,"{"query": "SELECT * FROM dbclobdemo"}");
System.out.println(json);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testObjectSQLQueryFromDSL(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",1);
List<DocObject> json = clientUtil.sql(DocObject.class,"sqlQuery",params);
System.out.println(json);
}
/**
* 代碼中的sql檢索，返回Map類型對象，亦可以返回自定義的對象
*/
@Test
public void testObjectQuery(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
DocObject json = clientUtil.sqlObject(DocObject.class,"{"query": "SELECT * FROM dbclobdemo where documentId = 1"}");
System.out.println(json);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型對象，亦可以返回自定義的對象
*/
@Test
public void testConditionObjectSQLQueryFromDSL(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",1);
DocObject json = clientUtil.sqlObject(DocObject.class,"sqlQuery",params);
System.out.println(json);
}
/**
* sql轉換為dsl
*/
@Test
public void testTranslate(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
String json = clientUtil.executeHttp("/_xpack/sql/translate",
"{"query": "SELECT * FROM dbclobdemo limit 5","fetch_size": 5}",
ClientInterface.HTTP_POST
);
System.out.println(json);
}
/**
* 低階的檢索方法
*/
@Test
public void testSQLRestResponse(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
SQLRestResponse sqlRestResponse = clientUtil.executeHttp("/_xpack/sql",
"{"query": "SELECT * FROM dbclobdemo where documentId = 1"}",
ClientInterface.HTTP_POST,
new SQLRestResponseHandler());
System.out.println(sqlRestResponse);
}
}

上面每個orm查詢方法的都有功能說明，可以根據需要選擇。

下面是上面用到的sql dsl檢索配置文件及內容：esmapper/sql.xml

<properties>
<!--
sql query
-->
<property name="sqlQuery">
<![CDATA[
{"query": "SELECT * FROM dbclobdemo where channelId=#[channelId]"}
]]>
</property>
<property name="sqlQueryWithStringParam">
<![CDATA[
{"query": "SELECT * FROM dbclobdemo where channelId=#[channelId] and docTitle="#[channelId,quoted=false]""} ## sql中特定的字元串參數語法
]]>
</property>
<!--
分頁sql query
每頁顯示 fetch_size對應的記錄條數
-->
<property name="sqlPagineQuery">
<![CDATA[
{
## 指示sql語句中的回車換行符會被替換掉開始符,注意dsl注釋不能放到sql語句中，否則會有問題，因為sql中的回車換行符會被去掉，導致回車換行符後面的語句變道與注釋一行
## 導致dsl模板解析的時候部分sql段會被去掉
"query": #"""
SELECT * FROM dbclobdemo
where channelId=#[channelId]
""",
## 指示sql語句中的回車換行符會被替換掉結束符
"fetch_size": #[fetchSize]
}
]]>
</property>
</properties>

我們將配置文件放到工程resources目錄下面即可。sql配置說明：

sql中特定的字元串參數語法

{"query": "SELECT * FROM dbclobdemo where channelId=#[channelId] and docTitle="#[channelId,quoted=false]""}

我們使用#[xxx]類型變數傳遞sql參數時，如果是字元串內容會自動在值的兩邊帶上雙引號，但是在sql語句是字元串值是用單引號"來標識的，所以通過qutoed=false來指示解析引擎不要在值的兩邊加雙引號，然後在外部手動添加單引號：

"#[channelId,quoted=false]"

如果sql語句比較長，可能要換行，es暫時不支持多行sql語句的執行，bboss通過下面特定的語法，來包圍多行sql，sql解析引擎在第一次解析sql的時候講其中的多行sql解析為一行：

#"""

...

...

"""

例如：

{
## 指示sql語句中的回車換行符會被替換掉開始符,注意dsl注釋不能放到sql語句中，否則會有問題，因為sql中的回車換行符會被去掉，導致回車換行符後面的語句變道與注釋一行
## 導致dsl模板解析的時候部分sql段會被去掉
"query": #"""
SELECT * FROM dbclobdemo
where channelId=#[channelId]
""",
## 指示sql語句中的回車換行符會被替換掉結束符
"fetch_size": #[fetchSize]
}

通過fetch_size實現分頁查詢

package org.bboss.elasticsearchtest.sql;
import org.frameworkset.elasticsearch.ElasticSearchHelper;
import org.frameworkset.elasticsearch.client.ClientInterface;
import org.frameworkset.elasticsearch.entity.sql.SQLResult;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SQLPagineTest {
/**
* 代碼中的sql檢索，返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testMapQuery(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
SQLResult<Map> sqlResult = clientUtil.fetchQuery(Map.class,"{"query": "SELECT * FROM dbclobdemo","fetch_size": 1}");
do{
List<Map> datas = sqlResult.getDatas();
if(datas == null || datas.size() == 0){
break;
}
else{
System.out.println(datas.size());//處理數據
sqlResult = sqlResult.nextPage();//獲取下一頁數據
}
}while(true);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testMapSQLQueryFromDSL(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",1);
params.put("fetchSize",1);
SQLResult<Map> sqlResult = clientUtil.fetchQuery(Map.class,"sqlPagineQuery",params);
do{
List<Map> datas = sqlResult.getDatas();
if(datas == null || datas.size() == 0){
break;
}
else{
System.out.println(datas.size());//處理數據
sqlResult = sqlResult.nextPage();//獲取下一頁數據
}
}while(true);
}
/**
* 代碼中的sql檢索，返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testObjectListQuery(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
SQLResult<DocObject> sqlResult = clientUtil.fetchQuery(DocObject.class,"{"query": "SELECT * FROM dbclobdemo","fetch_size": 1}");
do{
List<DocObject> datas = sqlResult.getDatas();
if(datas == null || datas.size() == 0){
break;
}
else{
System.out.println(datas.size());//處理數據
sqlResult = sqlResult.nextPage();//獲取下一頁數據
}
}while(true);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testObjectSQLQueryFromDSL(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",1);
params.put("fetchSize",1);
SQLResult<DocObject> sqlResult = clientUtil.fetchQuery(DocObject.class,"sqlPagineQuery",params);
do{
List<DocObject> datas = sqlResult.getDatas();
if(datas == null || datas.size() == 0){
System.out.println(0);//處理數據
break;
}
else{
System.out.println(datas.size());//處理數據
sqlResult = sqlResult.nextPage();//獲取下一頁數據
}
}while(true);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testObjectSQLQueryFromDSL1(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",1);
params.put("fetchSize",1);
SQLResult<DocObject> sqlResult = clientUtil.fetchQuery(DocObject.class,"sqlPagineQuery",params);
do{
List<DocObject> datas = sqlResult.getDatas();
if(datas == null || datas.size() == 0){
System.out.println(0);//處理數據
break;
}
else{
System.out.println(datas.size());//處理數據
sqlResult = clientUtil.fetchQueryByCursor(DocObject.class,sqlResult);//獲取下一頁數據,通過api獲取下一頁數據
}
}while(true);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型集合，亦可以返回自定義的對象集合
* 測試沒有返回數據的情況
*/
@Test
public void testNodataSQLQueryFromDSL1(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",2);
params.put("fetchSize",1);
SQLResult<DocObject> sqlResult = clientUtil.fetchQuery(DocObject.class,"sqlPagineQuery",params);
do{
List<DocObject> datas = sqlResult.getDatas();
if(datas == null || datas.size() == 0){
System.out.println(0);//處理數據
break;
}
else{
System.out.println(datas.size());//處理數據
sqlResult = clientUtil.fetchQueryByCursor(DocObject.class,sqlResult);//獲取下一頁數據,通過api獲取下一頁數據
}
}while(true);
}
/**
* 配置文件中的sql dsl檢索,返回Map類型集合，亦可以返回自定義的對象集合
*/
@Test
public void testObjectSQLQueryFromDSL2(){
ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一個載入sql配置文件的es客戶端介面
//設置sql查詢的參數
Map params = new HashMap();
params.put("channelId",1);
params.put("fetchSize",1);
SQLResult<DocObject> sqlResult = clientUtil.fetchQuery(DocObject.class,"sqlPagineQuery",params);
do{
List<DocObject> datas = sqlResult.getDatas();
if(datas == null || datas.size() == 0){
System.out.println(0);//處理數據
break;
}
else{
System.out.println(datas.size());//處理數據
sqlResult = clientUtil.fetchQueryByCursor(DocObject.class,sqlResult.getCursor(),sqlResult.getColumns());//獲取下一頁數據,通過api獲取下一頁數據
}
}while(true);
}
/**
* 代碼中的sql檢索，返回 DocObject類型集合
*/
@Test
public void testCloseCursor(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
SQLResult<DocObject> sqlResult = clientUtil.fetchQuery(DocObject.class,"{"query": "SELECT * FROM dbclobdemo","fetch_size": 1}");
List<DocObject> datas = sqlResult.getDatas();
System.out.println(datas.size());//處理數據
System.out.println(sqlResult.closeCursor());//只處理第一頁數據，就主動關閉分頁游標
}
/**
* 代碼中的sql檢索，返回DocObject類型集合
*/
@Test
public void testCloseCursor1(){
ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
SQLResult<DocObject> sqlResult = clientUtil.fetchQuery(DocObject.class,"{"query": "SELECT * FROM dbclobdemo","fetch_size": 1}");
List<DocObject> datas = sqlResult.getDatas();
System.out.println(datas.size());//處理數據
String ret = clientUtil.closeSQLCursor(sqlResult.getCursor());
System.out.println(ret);//只處理第一頁數據，就主動關閉分頁游標
}
}

Elasticsearch SQL案例介紹

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！


請您繼續閱讀更多來自 程序員小新人學習 的精彩文章:

※typescript入門，可以一起探討提點意見互相學習
※使用KETTLE從mysql同步增量數據到oracle

TAG:程序員小新人學習 |