Elasticsearch 6.3.0 SQL查询

Elasticsearch 6.3.0 官方新增的SQL功能，下面介绍其使用方法：

通过sql实现查询功能（代码中直接操作sql，从配置中加载sql）

将sql转换为dsl功能

sql查询：

@Test
	public void testQuery(){
		ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
		String json = clientUtil.executeHttp("/_xpack/sql?format=txt",
				"{\"query\": \"SELECT * FROM dbclobdemo\"}",
				ClientInterface.HTTP_POST
				);
		System.out.println(json);

		json = clientUtil.executeHttp("/_xpack/sql?format=json",
				"{\"query\": \"SELECT * FROM dbclobdemo\"}",
				ClientInterface.HTTP_POST
		);
		System.out.println(json);
	}

sql转换为dsl：

public void testTranslate(){
		ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
		String json = clientUtil.executeHttp("/_xpack/sql/translate",
				"{\"query\": \"SELECT * FROM dbclobdemo\"}",
				ClientInterface.HTTP_POST
		);
		System.out.println(json);

	}

sql转换为dsl的结果：

{
    "size": 1000,
    "_source": {
        "includes": [
            "author",
            "content",
            "docClass",
            "docabstract",
            "keywords",
            "mediapath",
            "newpicPath",
            "parentDetailTpl",
            "picPath",
            "publishfilename",
            "secondtitle",
            "subtitle",
            "title",
            "titlecolor"
        ],
        "excludes": []
    },
    "docvalue_fields": [
        "auditflag",
        "channelId",
        "count",
        "createtime",
        "createuser",
        "detailtemplateId",
        "docLevel",
        "docsourceId",
        "doctype",
        "documentId",
        "docwtime",
        "flowId",
        "isdeleted",
        "isnew",
        "ordertime",
        "publishtime",
        "seq",
        "status",
        "version"
    ],
    "sort": [
        {
            "_doc": {
                "order": "asc"
            }
        }
    ]
}

配置文件管理sql并实现sql检索

定义一个包含sql的dsl配置文件，sql语句中包含一个channelId检索条件：

<properties>
    <!--
        sql query

    -->
    <property name="sqlQuery">
        <![CDATA[
         {"query": "SELECT * FROM dbclobdemo where channelId=#[channelId]"}
        ]]>
    </property>


</properties>

加载配置文件并实现sql检索操作 ，从外部传入检索的条件channelId

public void testSQLQueryFromDSL(){
		ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一个加载sql配置文件的es客户端接口
		//设置sql查询的参数
		Map params = new HashMap();
		params.put("channelId",1);
		String json = clientUtil.executeHttp("/_xpack/sql","sqlQuery",params,
				ClientInterface.HTTP_POST
		);
		System.out.println(json);//打印<span>查询</span>结果

	}

输出查询的结果为：

{
    "columns": [
        {
            "name": "auditflag",
            "type": "long"
        },
        {
            "name": "author",
            "type": "text"
        },
        {
            "name": "channelId",
            "type": "long"
        },
        {
            "name": "content",
            "type": "text"
        },
        {
            "name": "count",
            "type": "long"
        },
        {
            "name": "createtime",
            "type": "date"
        },
        {
            "name": "createuser",
            "type": "long"
        },
        {
            "name": "detailtemplateId",
            "type": "long"
        },
        {
            "name": "docClass",
            "type": "text"
        },
        {
            "name": "docLevel",
            "type": "long"
        },
        {
            "name": "docabstract",
            "type": "text"
        },
        {
            "name": "docsourceId",
            "type": "long"
        },
        {
            "name": "doctype",
            "type": "long"
        },
        {
            "name": "documentId",
            "type": "long"
        },
        {
            "name": "docwtime",
            "type": "date"
        },
        {
            "name": "flowId",
            "type": "long"
        },
        {
            "name": "isdeleted",
            "type": "long"
        },
        {
            "name": "isnew",
            "type": "long"
        },
        {
            "name": "keywords",
            "type": "text"
        },
        {
            "name": "mediapath",
            "type": "text"
        },
        {
            "name": "newpicPath",
            "type": "text"
        },
        {
            "name": "ordertime",
            "type": "date"
        },
        {
            "name": "parentDetailTpl",
            "type": "text"
        },
        {
            "name": "picPath",
            "type": "text"
        },
        {
            "name": "publishfilename",
            "type": "text"
        },
        {
            "name": "publishtime",
            "type": "date"
        },
        {
            "name": "secondtitle",
            "type": "text"
        },
        {
            "name": "seq",
            "type": "long"
        },
        {
            "name": "status",
            "type": "long"
        },
        {
            "name": "subtitle",
            "type": "text"
        },
        {
            "name": "title",
            "type": "text"
        },
        {
            "name": "titlecolor",
            "type": "text"
        },
        {
            "name": "version",
            "type": "long"
        }
    ],
    "rows": [
        [
            0,
            "不详",
            1,
            "asdfasdfasdfasdfsd",
            0,
            "2018-04-12T14:16:02.000Z",
            1,
            1,
            "普通分类",
            1,
            "无asdfasdf",
            1,
            0,
            1,
            "2018-05-06T03:30:04.000Z",
            2,
            0,
            0,
            "news",
            "uploadfiles/201803/gencode4.png",
            "",
            "2018-04-12T14:06:45.000Z",
            "1",
            "uploadfiles/201803/gencode1.png",
            "asdf.html",
            "2018-04-14T14:36:12.000Z",
            "",
            0,
            11,
            "asdf",
            "adsf",
            "#000000",
            1
        ]
    ]
}