ElasticSearch 使用外键映射两个 sql 表 [英] ElasticSearch map two sql tables with a foreign key

问题描述

假设我有下表:

`title`- ID- 名称- tv_series_id例子:id=1, name="第 2 集", tv_series_id=4

我可以使用以下 JSON 结构轻松地将其映射到 ElasticSearch 中:

<代码>{身份证":1，"名称": "第 2 集",电视剧ID":4}

如果我有第二个名为 tv_series 的表，title 表在外键中引用，例如:

`tv_series`- ID- 名称例子:id=4, name='朋友'

然后我将如何在 Elasticsearch 中映射这种关系?是否有一种通用方法可以在 Elasticsearch 中映射具有一个或多个外键关系的两个表?比如通过做某个join语句?

解决方案

在 SQL 世界中，一切都是为了保持表之间的关系，使数据永远不会重复两次(或尽可能少)，因此主要-键/外键方法.

在一般的 NoSQL 世界中，特别是在 Elasticsearch 中，索引和类型之间没有明确的关系.有多种方法可以在文档之间创建关系(例如 parent-子或嵌套对象)，但是没有JOIN的概念.

相反，主要思想是非规范化您的数据 这样您的文档就可以用于"执行您的特定用例.人们不应该害怕保留数据的冗余副本.更一般地说，您需要问自己以下两个问题(以及许多其他问题):

1. 您需要向用户显示哪些数据/字段?
2. 您需要执行哪些查询来检索上述数据?

在您突出显示的简单情况下，我肯定会使用包含两个表的 JOIN 的文档:

<代码>{身份证":1，"名称": "第 2 集",电视剧ID":4，电视剧名称":朋友"}

标题Friends 将包含在 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):

1. what data/fields do you need to display to your users?
2. 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屋！