sql语句转为es查询条件（elasticsearch-sql使用）-Toy模板网

这篇具有很好参考价值的文章主要介绍了sql语句转为es查询条件（elasticsearch-sql使用）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

需求：将sql语句转为es查询所用的json语句

github源码地址：

https://gitee.com/weiyxiong_admin/elasticsearch-sql/blob/master/src/test/java/org/nlpcn/es4sql/ExplainTest.java

比如将select * from esIndex where lastTime = 1648540192929；
转化为es查询语句
{"from":0,"size":1000,"query":{"bool":{"filter":[{"bool":{"must":[{"match_phrase":{"lastTime ":{"query":1648540192929,"slop":0,"zero_terms_query":"NONE","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}}

1、添加pom.xml依赖

<!-- https://mvnrepository.com/artifact/org.nlpcn/elasticsearch-sql -->
		<dependency>
    		<groupId>org.nlpcn</groupId>
    		<artifactId>elasticsearch-sql</artifactId>
    		<version>6.8.13.0</version>
		</dependency>

2、scala 将sql转为es查询json语句

object ESUtils {
// 1、获取searchDao
lazy val searchDao = {
		import org.elasticsearch.client.Client
		import org.elasticsearch.client.node.NodeClient
		import org.elasticsearch.common.settings.Settings
		import org.elasticsearch.common.util.concurrent.ThreadContext
		import org.elasticsearch.threadpool.ThreadPool
		// 填写上自己的cluster.name
        val settings = Settings.builder().put("cluster.name", MetaStorageImplementation.elasticsearchClusterName).build()
        // val settings = Settings.builder().put("cluster.name", "my-application").build()
        val  threadPool = new ThreadPool(settings)
		val client = new NodeClient(settings, threadPool)
		new org.nlpcn.es4sql.SearchDao(client) 
	}
//2、通过sql转为es查询语句
	def sqlToEsQuery(sql : String) : String = {
		val string = searchDao.explain(sql).explain().explain()
		string
	}

3、测试

def main(args: Array[String]): Unit = {
	val sql = ESUtils.sqlToEsQuery("select * from esIndex where lastTime = 1648540192929")
	val esJSON =ESUtils.sqlToEsQuery(sql)
    println(esJSON)
   }
}

4、查询返回结果展示（即步骤三esJSON结果打印）

{"from":0,"size":1000,"query":{"bool":{"filter":[{"bool":{"must":[{"match_phrase":{"lastAccessed":{"query":1648540192929,"slop":0,"zero_terms_query":"NONE","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}}

5、打开postman文章来源地址https://www.toymoban.com/news/detail-538041.html

POST请求
请求头： http://ip:port/esIndex /_search
请求体：
为 步骤四的返回结果展示esJSON
{"from":0,"size":1000,"query":{"bool":{"filter":[{"bool":{"must":[{"match_phrase":{"lastTime":{"query":1648540192929,"slop":0,"zero_terms_query":"NONE","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}}
查询，即可查到符合条件的数据

到了这里，关于sql语句转为es查询条件（elasticsearch-sql使用）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！