如何使用 elasticsearch 获得 SQL 中的 JOIN 功能? [英] How to use elasticsearch to get JOIN functionality as in SQL?

问题描述

我有一个 SQL 查询，它对 3-4 个表执行 JOIN 操作以获取数据.现在我们正在转向弹性搜索以获得更好的性能.如何使用 elasticsearch 复制相同的 JOIN 查询?我已阅读有关父/子文档的内容，但我的数据没有任何严格的父/子类型的数据.

I have an SQL query that does JOIN operations over 3-4 tables to get the data. Now we are shifting to elasticsearch for better performance. How can I replicate the same JOIN query using elasticsearch? I have read about parent/child documents but my data doesn't have any strict parent/child kind of data.

推荐答案

Elasticsearch 不支持 JOIN，这就是 NoSQL 技术.有一些方法可以使用 parent/在您的数据之间创建一些关系子关系(如您所见)，嵌套对象并使用特殊的术语查找优化，仅此而已.

Elasticsearch does not support JOINs, that's the whole purpose of NoSQL technologies in the first place. There are ways to create some relationships between your data using parent/child relationships (as you've noticed), nested objects and also using a special terms lookup optimization, but that's about it.

然而，为了充分利用 Elasticsearch，主要思想是 非规范化 尽可能多的数据并存储自包含的文档.这意味着您可以使用单个文档，其中包含所需的所有信息.您不关心数据将被复制的事实.

However, in order to take the most out of Elasticsearch, the main idea is to denormalize your data as much as possible and store self-contained documents. This means that you can take a single document and it contains all the info it needs. You don't care about the fact that data is going to be duplicated.

例如，假设您的 SQL 数据库中有以下 JOIN 查询，用于检索所有人的地址、城市和国家/地区(即 4 个表):

For instance, suppose you have the following JOIN query in your SQL database for retrieving all people with their address, city and country (i.e. 4 tables):

SELECT p.first_name, p.last_name, 
       a.street_name, a.street_num,
       c.name, c2.name
  FROM person p
  JOIN address a ON a.id = p.addr_id
  JOIN city c ON c.id = p.city_id
  JOIN country c2 ON c2.id = p.country_id

在 Elasticsearch 中，您将创建一个包含上述查询返回的字段的文档，即

In Elasticsearch, you would create a document containing exactly the fields that are returned by the above query, i.e.

 {
     "first_name": "John",
     "last_name": "Doe",
     "street_num": 34,
     "street_name": "Main Street",
     "city": "San Francisco",
     "country": "United States"
 }

因此，一种看待这一点的方式是，在 Elasticsearch 中，您将存储与在关系数据库中运行查询所获得的相同(或非常相似)的一组字段.

So, one way to see this, is that in Elasticsearch you're going to store the same (or very similar) set of fields that you would get as a result of running your query in your relational database.

从 RDBMS 到 Elasticsearch 是一种范式转变.如果您打算迈出这一步，您需要对数据进行不同的思考.

Taking the step from RDBMS to Elasticsearch is a paradigm shift. If you are ever going to take that step, you need to think different in terms of your data.

这篇关于如何使用 elasticsearch 获得 SQL 中的 JOIN 功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！