SQL查询自动转换为ElasticSearch查询

2021-12-13 00:00:00 elasticsearch mysql

我有一项服务，目前将数据存储在 Oracle DB 中.我正在做一个项目，我需要运行一组 sql 查询来获取一些聚合数据.我想将这些查询存储在一个地方，我可以对其进行迭代并获取所需的数据.比如说，我今天有 10 个查询.但是，我可以继续添加更多内容，而无需修改代码.

I have a service which currently stores data in Oracle DB. I am working on a project where I need to run a set of sql queries to get some aggregated data. I would want to store these queries at one place, which I can iterate over, and get the required data. Say, I have 10 queries today. But, I can keep adding more, without toching the code.

但是，明天我们想切换到 ElasticSearch.有没有办法，我可以使用相同的 sql 查询来搜索甚至 ElasticSearch.

But, tomorrow we would want to switch to ElasticSearch. Is there a way, that i can use the same sql queries to search through even ElasticSearch.

推荐答案

With Elasticsearch 6.3 于 2018 年 6 月发布，您可能不再需要自动转换".

With Elasticsearch 6.3 released in June 2018, you might not need an "automatic conversion" anymore.

6.3 版本带有原生 SQL 支持！(目前仍处于试验阶段)

The 6.3 release comes with native SQL support! (still experimental for now)

你(或你认识的人)有没有:

Have you (or someone you know) ever:

  • 说我知道如何在 SQL 语句中做这件事——我如何在 Elasticsearch 中做同样的事情?"
  • 尝试使用标记化来构建全文搜索a>、词干提取、同义词、相关性排序在 SQL 引擎(如关系数据库)之上?
  • 是否尝试将传统数据库扩展到数十亿行?
  • 是否尝试将 BI 系统等 3rd 方工具连接到 Elasticsearch?

这些都是我们希望能够进入新的 Elasticsearch SQL 版本的东西.

These are all things which we hope we can make inroads into our new Elasticsearch SQL release.

我们希望让开发人员、数据科学家和其他熟悉 SQL 语言的人——但目前还不熟悉或无法使用 Elasticsearch 查询语言——使用速度、可扩展性和全文Elasticsearch 提供的强大功能以及其他人已经逐渐了解和喜爱的功能.

Our hope is to allow developers, data scientists, and others that are familiar with the SQL language -- but so far unfamiliar with or unable to use the Elasticsearch query language -- to use the speed, scalability, and full-text power that Elasticsearch offers and others have grown to know and love.

如果您刚刚开始使用此功能或为其提供支持的 Elasticsearch 的强大功能，请尝试以下几点:

If you’re just getting started using this functionality or the power of Elasticsearch that powers it, here are a few things to try:

  • SELECT ... ORDER BY SCORE() DESC 能够根据搜索结果的相关性进行排序
  • 通过使用 MATCH 运算符(如 SELECT ... WHERE MATCH(fieldname, 'some text')
    • 获得从标记化到词干的所有全文魔法>
  • 使用我们的 JDBC 驱动程序将您最喜欢的 JDBC 兼容工具连接到 Elasticsearch
  • 通过翻译 API 翻译您知道的 SQL 查询，了解如何使用 Elasticsearch DSL 的全部功能
  • SELECT … ORDER BY SCORE() DESC to be able to sort by the relevance of the search results
  • Get all of the full-text magic from tokenization to stemming by using the MATCH operator like SELECT … WHERE MATCH(fieldname, 'some text')
  • Connect your favorite JDBC-compatible tool to Elasticsearch with our JDBC driver
  • Learn how to use the full power of the Elasticsearch DSL by translating a SQL query you know via the translate API

请注意，此功能在 Elasticsearch 和 REST API 的默认"(非 OSS 专用)发行版中可用——包括翻译"功能和 CLI 工具是完全免费的.

Note that this feature is made available in the "default" (non-OSS-only) distribution of Elasticsearch and the REST API -- including the "translate" functionality and the CLI tool are completely free.

相关文章