如何使用弹性搜索来获得JOIN功能，如同SQL？ [英] How to use elasticsearch to get JOIN functionality as in SQL?

问题描述

我有一个SQL查询，可以通过3-4个表执行JOIN操作来获取数据。现在我们正在转向弹性材料以获得更好的性能。如何使用弹性搜索复制相同的JOIN查询？我已经阅读了关于父/子文档，但我的数据没有任何严格的父/子类数据。

解决方案

弹性搜索不支持JOIN，这是 NoSQL技术的全部目的。有一些方法可以使用父母/儿童关系（正如你所看到的），嵌套对象，并使用特殊的术语查找优化，但这是关于它。

但是，为了充分利用弹性搜索，主要思想是尽可能地反规范化您的数据，并存储自包含的文档。这意味着您可以使用单个文档，并包含所需的所有信息。您不关心数据将被重复的事实。

例如，假设您的SQL数据库中有以下JOIN查询，用于检索所有与他们的地址，城市和国家（即4个表）：

  SELECT p.first_name，p.last_name，
 a .street_name，a.street_num，
 c.name，c2.name 
 FROM person p 
 JOIN地址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，您将创建一个包含上述查询返回的字段的文档，例如

  {
 first_name：John，
last_name：Doe，
street_num：34，
street_name：Main Street，
city 旧金山，
国家：美国
} 
  

从RDBMS到Elasticsearch是一个范式转变。如果您要采取这一步骤，则需要根据您的数据考虑不同的情况。

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 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.

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.

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

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"
 }

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.

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.

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