PrestoSql、Trino链接elasticsearch使用DSL语法查询

普通查询很简单：select * from elasticsearch."default".test_table;使用es的DSL语法进行查询，网上基本查询不到太多的资料，例子更查询不到，现添加记录；查询Trino官网资料，支持ES的DSL语法查询，结果存储到单字段的result字段中：Elasticsearch 连接器允许您在 SQL 查询中嵌入任何使用Elasticsearch Query D

Cy@Liang

1747人浏览 · 2021-07-19 17:55:43

Cy@Liang · 2021-07-19 17:55:43 发布

普通查询很简单：

select * from elasticsearch."default".test_table;

使用es的DSL语法进行查询，网上基本查询不到太多的资料，例子更查询不到，现添加记录；

查询Trino官网资料，支持ES的DSL语法查询，结果存储到单字段的result字段中：

Elasticsearch 连接器允许您在 SQL 查询中嵌入任何使用Elasticsearch Query DSL 的有效 Elasticsearch查询。

然后可以在任何 SQL 语句中使用结果，包装 Elasticsearch 查询。该语法使用以下内容扩展了增强型 Elasticsearch 表名称的语法：
SELECT * FROM es.default."<index>$query:<es-query>"
Elasticsearch 查询字符串es-query是 base32 编码的，以避免必须处理表标识符中的转义引号和区分大小写问题。

这些查询表的结果是一个单行单列的表，名称result为 VARCHAR 类型。它包含 Elasticsearch 返回的 JSON 负载，并且可以使用内置的 JSON 函数进行处理。

注意：<index>：查询节点，<es-query> ：dsl语法(base32编码) 网上未能查到资料，自己折腾测试了很久...

实例：

需查询ES数据

<index> ：需要查询节点名

<es-query>：dsl语法(base32编码)

生成Trina SQL(不要官网案例中两边尖括号)：

select * from elasticsearch.default."test_bable$query:PMFCAIBCOF2WK4TZEI5CA6YKEAQCAIBCMJXW63BCHIQHWCRAEAQCAIBAEJTGS3DUMVZCEORALMFCAIBAEAQCAIBAPMFCAIBAEAQCAIBAEAQCE5DFOJWSEORAPMFCAIBAEAQCAIBAEAQCAIBCOBQXI2KJMQRDUID3BIQCAIBAEAQCAIBAEAQCAIBAEJ3GC3DVMURDUIBCHFSWKODBMQ3DCLJVMU4WELJUGRRTGLJZGI2WELJWMEZTCMTEMZSTCOJUGMRAUIBAEAQCAIBAEAQCAIBAPUFCAIBAEAQCAIBAEAQH2CRAEAQCAIBAEAQH2CRAEAQCAIBALUFCAIBAEB6QUIBAPUFCAIBMBIQCAITGOJXW2IR2EAYCYCRAEARHG2L2MURDUIBRGAYDAMAKPU======"

查询结果全部在result中

数据处理

查询结果数据，转换成字段列表(以下SQL进行拆分，可以进行简化，也有其他写法优化)

-- 解析数据
select result, json_extract_scalar(result, '$.patientId') as patientId,json_extract_scalar(result, '$.patiId') as patiId from
	(
	-- json中提取 _source 字段
	select json_extract(result, '$._source') as result from
		(
		-- 列表转JSON(一转多)
		select result_JSON as result from
			(
			-- 数据转列表
			select cast(result as array(JSON)) as result from
				(
				-- 提取数据
				select json_extract(result, '$.hits.hits') as result from
					(
					-- 查询数据，
					select result from elasticsearch.default."test_bable$query:PMFCAIBCOF2WK4TZEI5CA6YKEAQCAIBCMJXW63BCHIQHWCRAEAQCAIBAEJTGS3DUMVZCEORALMFCAIBAEAQCAIBAPMFCAIBAEAQCAIBAEAQCE5DFOJWSEORAPMFCAIBAEAQCAIBAEAQCAIBCMRUWCZ3ON5ZWS42EMVZWGIR2EB5QUIBAEAQCAIBAEAQCAIBAEAQCE5TBNR2WKIR2EARONBNC42AKP2MYXPS2DHXGQCT6RAV246LL5Z4XQURAUIBAEAQCAIBAEAQCAIBAPUFCAIBAEAQCAIBAEAQH2CRAEAQCAIBAEAQH2CRAEAQCAIBALUFCAIBAEB6QUIBAPUFCAIBMBIQCAITGOJXW2IR2EAYCYCRAEARHG2L2MURDUIBRGAYDAMAKPU======"
					)
				)
			)
		cross join unnest(result) as t (result_JSON)
		)
)

结果：