SQL和Elasticsearch查询的对比

2 年 ago
文, 翔
3 minutes

我大致总结了有关SQL和Elasticsearch查询的常见用法，各自是如何对应的。

环境

Elasticsearch的版本从6.2.4 升级到了7.5。

前提条件 (Qian ti tiao jian)

以下的条件为记载内容的前提。

在Elasticsearch中，可以省略URL的指定进行搜索。

在Elasticsearch中，当执行搜索时，会向以下URL发送请求。
{Elasticsearch的URL}/{索引名称}/_search
例如，对于名为product的表在SQL中进行索引，Elasticsearch会发送请求到以下URL。
http://localhost:9200/product/_search

假设以前述的URL进行请求，无需详细说明Elasticsearch请求的目标URL。

所有文字字符的数据类型都是关键字（不使用全文搜索）。

Elasticsearch具有全文搜索功能，字符串数据是否可进行全文搜索取决于字段的数据类型。

    • 通常の文字列 : Keyword

全文検索対象 : Text

尽管关系数据库管理系统（RDBMS）也可以进行全文搜索，但不同的RDBMS处理方式有较大差异，所以在执行全文搜索时，我认为了解Elasticsearch专用的语法作为搜索方法比与SQL进行比较更好。
因此，在这种情况下，我们假设所有的字符串数据都是关键字（Keyword）数据类型的。

只需输入无限制地搜索。

在SQL中，没有WHERE子句的搜索，在Elasticsearch中可以使用Match All Query进行。

SELECT *
FROM product

{
  "query": {
    "match_all": {}
  }
}

Elasticsearch的搜索结果会被存储在一个名为“hits”的字段中并返回。

指定获取数量和获取起始位置

在SQL中使用LIMIT（或者OFFSET或者ROWNUM）来指定获取的数量和起始位置，在Elasticsearch中可以使用From / Size来实现。

由于Elasticsearch使用了from和size两个参数，因此给出了一个示例来指定LIMIT和OFFSET。（请根据需要对应地调整为RDBMS）
例如，要跳过前5个并且只获取10个记录，可以按以下方式进行。

SELECT *
FROM product
LIMIT 10 OFFSET 5

{
  "from": 5,
  "size": 10, 
  "query": {
    "match_all": {}
  }
}

Elasticsearch的默认配置是”from”: 0, “size”: 10。

指定所需的列

在SQL中，要指定SELECT语句中的提取列，在Elasticsearch中使用Source过滤功能来实现。

SELECT
  product_name,
  product_count 
FROM product

{
  "_source": [
    "product_name",
    "product_count"
  ],
  "query": {
    "match_all": {}
  }
}

刪除重複行

在SQL中使用SELECT语句时，通过指定DISTINCT关键字来删除重复行，在Elasticsearch中可以使用Field Collapsing功能。

SELECT DISTINCT
  product_name
FROM product

{
  "collapse": {
    "field": "product_name"
  },
  "query": {
    "match_all": {}
  }
}

在数据库中搜索符合条件的数据（使用WHERE子句）。

在后面的例子中，我们提到了特定数据类型的情况，但是如果要指定其他数据类型，基本上也是类似的。

等於指定的數值

在SQL中，查找与特定值相等的数据可以使用Elasticsearch的Term Query进行搜索。

SELECT *
FROM product
WHERE product_name = 'りんご'

{
  "query": {
    "term": {
      "product_name": {
        "value": "りんご"
      }
    }
  }
}

与指定的多个值之一相等（IN句）

在SQL中使用IN子句进行检索，在Elasticsearch中使用Terms Query进行检索。

SELECT *
FROM product
WHERE product_name IN ('りんご', 'にんじん')

{
  "query": {
    "terms": {
      "product_name": [
        "りんご",
        "にんじん"
      ]
    }
  }
}

包含指定的值（LIKE语句）

在SQL中，使用LIKE句进行搜索，在Elasticsearch中可以使用通配符查询（Wildcard Query）。

SELECT *
FROM product
WHERE product_name LIKE '%じ%'

{
  "query": {
    "wildcard": {
      "product_name": {
        "value": "*じ*"
      }
    }
  }
}

比指定的值大(或小)。

在SQL中，基于指定值的大小条件搜索，在Elasticsearch中使用Range Query。大小条件可按以下方式指定。

SQLElasticsearchMEMO>=gteGreater-Than or Equal to>gtGreater-Than<=lteLess-Than or Equal to<ltLess-Than
SELECT *
FROM product
WHERE product_count >= 10

{
  "query": {
    "range": {
      "product_count": {
        "gte" : 10
      }
    }
  }
}

多个条件符合

在SQL中，指定多个条件进行搜索在Elasticsearch中使用Bool Query的must和should。多个条件可以如下指定。

SQLElasticsearchANDmustORshould

如果是AND条件的情况下(如果是AND条件的情况下)

SELECT *
FROM product
WHERE product_name = 'りんご'
  AND product_count >= 10

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "product_name": "りんご"
          }
        },
        {
          "range": {
            "product_count": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}

如果是OR条件的情况

SELECT *
FROM product
WHERE product_name= 'りんご'
   OR product_count >= 10

{
  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "product_name": "りんご"
          }
        },
        {
          "range": {
            "product_count": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}

在AND和OR的组合情况下

通过在 Bool Query 中嵌套 Bool Query，可以实现 AND 和 OR 的组合。

SELECT *
FROM product
WHERE product_count >= 10
  AND (product_name = 'りんご' OR product_price = 100) 

{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "count": {
              "gte": 10
            }
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "product_name": "りんご"
                }
              },
              {
                "term": {
                  "product_price": 100
                }
              }
            ]
          }
        }
      ]
    }
  }
}

在中文中进行更多的句子重述以获得相同的搜索结果，可以通过使用minimum_should_match参数来实现。然而，与SQL相比，我认为前面提到的方法更易理解。

不符合指定条件

在SQL中，如果不符合指定条件的搜索，则在Elasticsearch中使用Bool Query的must_not进行搜索。

SELECT *
FROM product
WHERE product_name <> 'りんご'

{
  "query": {
    "bool": {
      "must_not": [
        {
          "term": {
            "product_name": "りんご"
          }
        }
      ]
    }
  }
}

对获取的数据进行排序（使用ORDER BY子句）

在SQL中使用ORDER BY子句进行排序，而在Elasticsearch中则使用Sort功能进行排序。

SELECT *
FROM product
ORDER BY product_name ASC

{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "product_name": {
        "order": "asc"
      }
    }
  ]
}

对数据进行汇总

在Elasticsearch中，我们使用Aggregations进行数据聚合。与搜索相比，Elasticsearch有一些特殊的语法，可能会有些难以理解，但基本的语法如下所示。

{
  "aggs": {
    "{Aggregation名(わかりやすければ何でもOK)}": {
        "{Aggregationの種類}": {}
  }
}

Elasticsearch的聚合结果会存储在aggregations字段中返回，而不是hits字段。
搜索结果通常会一起存储在hits字段中返回，如果不需要搜索结果的话，可以将size设为0来指定。

计算指定列的平均值与最大值（汇总函数）。

在SQL中，使用聚合函数进行数据聚合，在Elasticsearch中使用Metrics Aggregations实现。
以下是SQL的聚合函数与Elasticsearch的对应列表。

SELECT
  AVG(product_count)
FROM product

{
  "aggs": {
    "product_count_avg": {
      "avg": {
        "field": "product_count"
      }
    }
  }
}

按照具有相同数值的数据进行汇总（使用GROUP BY子句）。

在Elasticsearch中，使用Terms Aggregation来执行类似于SQL中的GROUP BY语句的数据汇总。

SELECT
  product_name
FROM product
GROUP BY product_name

{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      }
    }
  }
}

使用术语聚合计算的结果将被汇总到指定的前N个条目中（例如，10个）。

如果想与聚合函数结合使用，需要将Aggregation嵌套起来。

SELECT
  AVG(product_count)
FROM product
GROUP BY product_name

{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      },
      "aggs": {
        "product_count_avg": {
          "avg": {
            "field": "product_count"
          }
        }
      }
    }
  }
}

根据指定条件进行数据汇总（与WHERE子句结合使用）

如果你想筛选出特定的原始数据并进行汇总，你可以通过与查询语句(query)结合来实现。

SELECT
  AVG(product_count)
FROM product
WHERE product_name = 'りんご'

{
  "size": 0,
  "query": {
    "term": {
      "product_name": {
        "value": "りんご"
      }
    }
  },
  "aggs": {
    "product_count_avg": {
      "avg": {
        "field": "product_count"
      }
    }
  }
}

有点难理解, 但是通过查询筛选的结果将被返回到hits中, 而汇总结果将被返回到aggregations中, 它们是两个不同的项目。
如果只需要获取汇总结果, 可以通过像上面的示例一样在size中指定0, 这样将不会有任何返回到hits中, 从而减轻了响应数据的负担。
（aggregations将会返回根据查询条件匹配的数据进行汇总的结果）

对于集计结果进行条件指定（使用HAVING子句）。

在Elasticsearch中，使用Bucket Selector聚合进行检索对应于SQL中使用HAVING子句对聚合结果的查询。

SELECT
  product_name,
  SUM(product_count)
FROM product
GROUP BY product_name
HAVING SUM(product_count) > 10

{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      },
      "aggs": {
        "product_count_sum": {
          "sum": {
            "field": "product_count"
          }
        },
        "product_count_sum_bucket": {
          "bucket_selector": {
            "buckets_path": {
              "count_sum": "product_count_sum"
            },
            "script": "params.count_sum > 10"
          }
        }
      }
    }
  }
}

在buckets_path中指定要进行筛选条件的字段。
例子中，我们将product_count_sum作为等价于SUM(product_count)的条件指定对象。

在脚本中，您可以写入筛选条件。
如果您想要使用在buckets_path中指定的值，可以通过指定变量名为params.count_sum来实现。
params是用于引用执行查询中的变量的指定值。

其他的例子包括JOIN和子查询。

由于Elasticsearch不是关系型数据库，所以很遗憾地没有像JOIN和子查询一样的功能。
为了能够成功搜索，您需要将数据通过非规范化或使用Nested数据类型来嵌套数据进行注册。

✖
广告
将在 10 秒后关闭
bannerAds