ElasticSearch将外部两个sql表映射到外键 [英] ElasticSearch map two sql tables with a foreign key

问题描述

假设我有以下表格：

 `title` 
  -  id 
  -  name 
  -  tv_series_id 
 
示例：
 id = 1，name =Episode 2，tv_series_id = 4 
  

我可以使用以下JSON结构轻松地在ElasticSearch中映射：

  { 
ID：1，
名称：剧集2，
TVSeriesID：4 
} 
  pre> 
 
 
如果我有一个名为 tv_series 的第二个表，标题表中引用外键，例如：
 `tv_series` 
  -  id 
  - 名称
 
示例：
 id = 4，name ='好友'
  
我如何在Elasticsearch中映射该关系？有没有一种通用的方法可以在Elasticsearch中映射两个具有一个或多个外键关系的表？例如，通过做一个连接语句？
解决方案
在SQL世界中，一切都是关于保持这样的数据从不重复两次（或尽可能少），因此是主键/外键方式。 
 
 
 
在NoSQL世界中，特别是Elasticsearch中，您的索引和类型之间没有明确的关系。有办法在文档本身之间建立关系（例如 parent-孩子或嵌套对象） ，但没有JOIN的概念。
 
 
 
相反，主要想法是使您的数据非规范化，使您的文档将可用来执行您的具体用例。不要害怕保留冗余的数据副本。更一般地，你需要问自己以下两个问题（其中很多）：
 
 
你需要显示哪些数据/字段您的用户？
 
 
您需要执行哪些查询来检索上述数据？
 
 
在简单的情况下，您突出显示，我一定会带有包含两个表的JOIN的文档：
  {
ID：1，
名称：剧集2，
TVSeriesID：4，
TVSeriesName：朋友
} 
  
标题朋友将是包含在236个文档中，想法是您可以检索任何剧集，它包含您需要了解的所有数据。
 
Suppose I have the following table:
`title`
- id
- name
- tv_series_id

Example:
id=1, name="Episode 2", tv_series_id=4
I can easily map this in ElasticSearch using the following JSON structure:
{
    "ID": 1,
    "Name": "Episode 2",
    "TVSeriesID": 4
}
If I then had a second table called tv_series that the title table referenced in a foreign key, for example:
`tv_series`
- id
- name

Example:
id=4, name='Friends'
How would I then map that relationship in Elasticsearch? Is there a generic way in which two tables with one or more foreign key relationships can be mapped in Elasticsearch? For example, by doing a certain join statement?
解决方案
In the SQL world, everything is about keeping relationships between tables in such a way that data is never repeated twice (or as seldom as possible), hence the primary-key/foreign-key approach. 

In the NoSQL world in general, and in Elasticsearch in particular, there are no explicit relationships between your indices and types. There are ways to create relationships between documents themselves (e.g. parent-child or nested objects), but there is no concept of JOIN.

Instead, the main idea is to denormalize your data in such a way that your documents will be "usable" to carry out your specific use cases. One should not be afraid of keeping redundant copies of data. More generally, you need to ask yourself the following two questions (among many others):

what data/fields do you need to display to your users?
what queries do you need to perform to retrieve the above data?
In the simple case you highlighted, I would definitely go with a document that contains the JOIN of your two tables:
{
    "ID": 1,
    "Name": "Episode 2",
    "TVSeriesID": 4,
    "TVSeriesName": "Friends"
}
It is no big deal that the title Friends will be contained in 236 documents, the idea is that you can retrieve any episode and it contains all the data you need to know about it.

                        
这篇关于ElasticSearch将外部两个sql表映射到外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！