Elasticsearch SQL入門
本投稿はElastic Stack (Elasticsearch) Advent Calendar 2020 24 日目の記事です。
まえがき
みなさん、SQL は好きですか。という書き出しで記事を書くのは2 度目ですが、私は SQL のことを理解しきっているわけではないので好きになり途中です。
本投稿では、Elaticsearch に対して SQL でリクエスト可能な Elastic 社公式のプラグイン、Elasticsearch SQLをご紹介いたします。
前回の記事との違い
前回の記事でも Elasticsearch に対して SQL を発行しリクエストする方法をご紹介しましたが、あちらは Open Distro for Elasticsearch のプラグインでした。両者の大きな違いは以下の表の通りです。
Elasticsearch SQL | Open Distor for Elasticsearch SQL | |
---|---|---|
ライセンス | Elastic License (Basic Subscription) [1] | Apache License |
コネクション | JDBC, ODBC | JDBC, ODBC |
GUI | × | Query Workbench |
CLI | ○ | ○ |
DSL への変換 | ○(translate API) | ○ (explain API) |
Elasticsearch SQL の制限について
Elasticsearch SQL には様々な制限があり、SQL Limitationsページに一覧の記載があります。
2020/12 現在でクリティカルなものをピックアップすると以下の様なものが挙げられます。
他にも、記載されていない部分では以下の様な制限が今回の検証を通して見つかりました。
- DISTINCT 句がサポートされていない
- JOIN 句がサポートされていない
Elatic License 版コンテナの立ち上げ
検証のための環境構築には docker-compose を用います。主な環境は以下の通りです。
バージョン | |
---|---|
mac OS | 10.15.7 |
docker for mac | 2.4.0.0 |
Elaticsearch | 7.10.0 |
Kibana | 7.10.0 |
また、今回の検証では Kibana の初回起動時にデフォルトでセットアップ可能なSample eCommerce ordersデータセットを用います。
docker-compose.yml の用意
今回用意した検証用クラスタの構成は以下の通りです。特に複数台構成にする必要がないため、single node となっています。
version: '2.2'
services:
es01:
image: docker.elastic.co/elasticsearch/elasticsearch:7.10.0
container_name: es01
environment:
- node.name=es01
- discovery.seed_hosts=es02
- cluster.initial_master_nodes=es01
- cluster.name=docker-cluster
- bootstrap.memory_lock=true
- "ES_JAVA_OPTS=-Xms256m -Xmx256m"
ulimits:
memlock:
soft: -1
hard: -1
ports:
- 9200:9200
networks:
- esnet
kibana:
image: docker.elastic.co/kibana/kibana:7.10.0
links:
- es01:elasticsearch
ports:
- 5601:5601
networks:
- esnet
environment:
- xpack.monitoring.ui.container.elasticsearch.enabled=true
networks:
esnet:
この際の注意点として、docker イメージには*-oss
が付与されていないものを利用します。
現在 Elatic 社 が公式に公開している docker イメージには、Apache ライセンス下で利用可能な elasticsearch-oss と Elastic ライセンス下の機能も利用可能な elasticsearch の 2 種類があります。
elasticsearch | Docker
今回利用したい機能は先述の通り Elastic ライセンス下の機能であるため、後者のものを明示的に指定し利用しています。
実践
全権取得クエリを投げてみる
まずはお試しで全件取得する SQL を投げてみます。
Kibana > Dev Tools からリクエスト実行画面を表示します。
▼ Dev Tools
投げるクエリは一般的な SELECT *です。
SELECT * FROM "kibana_sample_data_ecommerce"
結果
{
"error" : {
"root_cause" : [
{
"type" : "ql_illegal_argument_exception",
"reason" : "Arrays (returned by [manufacturer]) are not supported"
}
],
"type" : "ql_illegal_argument_exception",
"reason" : "Arrays (returned by [manufacturer]) are not supported"
},
"status" : 500
}
…エラーとなりました。Array は対応していないとのことです。実際にデータをみて、何が原因かをみてみるために、translate API を用いてで DSL に変換してみます。
GET /_sql/translate
{
"query": """
SELECT * FROM "kibana_sample_data_ecommerce"
"""
}
結果
{
"size": 1000,
"_source": {
"includes": [
"category",
"customer_first_name",
... 中略
"total_unique_products"
],
"excludes": []
},
"docvalue_fields": [
{
"field": "currency"
},
... 中略
{
"field": "user"
}
],
"sort": [
{
"_doc": {
"order": "asc"
}
}
]
}
こちらを実際元に、実際のインデックスに対して_search リクエストをかけてみます。
{
"took" : 8,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 4675,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "kibana_sample_data_ecommerce",
"_type" : "_doc",
"_id" : "uzf7j3YBp8s7-c5IvNTe",
"_score" : null,
"_source" : {
"customer_full_name" : "Eddie Underwood",
"customer_last_name" : "Underwood",
"customer_first_name" : "Eddie",
"day_of_week_i" : 0,
"total_quantity" : 2,
"taxless_total_price" : 36.98,
"total_unique_products" : 2,
"category" : [
"Men's Clothing"
],
"manufacturer" : [
"Elitelligence",
"Oceanavigations"
],
"products" : [
{
"tax_amount" : 0,
"taxful_price" : 11.99,
"quantity" : 1,
"taxless_price" : 11.99,
"discount_amount" : 0,
"base_unit_price" : 11.99,
"discount_percentage" : 0,
"product_name" : "Basic T-shirt - dark blue/white",
"manufacturer" : "Elitelligence",
"min_price" : 6.35,
"unit_discount_amount" : 0,
"price" : 11.99,
"product_id" : 6283,
"base_price" : 11.99,
"_id" : "sold_product_584677_6283",
"category" : "Men's Clothing"
},
{
"tax_amount" : 0,
"taxful_price" : 24.99,
"quantity" : 1,
"taxless_price" : 24.99,
"discount_amount" : 0,
"base_unit_price" : 24.99,
"discount_percentage" : 0,
"product_name" : "Sweatshirt - grey multicolor",
"manufacturer" : "Oceanavigations",
"min_price" : 11.75,
"unit_discount_amount" : 0,
"price" : 24.99,
"product_id" : 19400,
"base_price" : 24.99,
"_id" : "sold_product_584677_19400",
"category" : "Men's Clothing"
}
],
"taxful_total_price" : 36.98
},
"fields" : {
"products.sku" : [
"ZO0299602996",
"ZO0549605496"
],
"customer_phone" : [
""
],
"geoip.city_name" : [
"Cairo"
],
"geoip.region_name" : [
"Cairo Governorate"
],
"type" : [
"order"
],
"order_date" : [
"1609752528000"
],
"geoip.location" : [
"30.09999997448176, 31.29999996162951"
],
"geoip.country_iso_code" : [
"EG"
],
"products.created_on" : [
"1482744528000",
"1482744528000"
],
"currency" : [
"EUR"
],
"geoip.continent_name" : [
"Africa"
],
"customer_id" : [
"38"
],
"sku" : [
"ZO0299602996",
"ZO0549605496"
],
"order_id" : [
"584677"
],
"user" : [
"eddie"
],
"customer_gender" : [
"MALE"
],
"email" : [
"eddie@underwood-family.zzz"
],
"event.dataset" : [
"sample_ecommerce"
],
"day_of_week" : [
"Monday"
]
},
"sort" : [
0
]
}
]
}
}
取得結果を見ると、category
やmanufacture
等 Array 型のフィールドが含まれていることがわかります。
これは制約に記載のある通りの事項なので、以降は Array 型のフィールドを選択しないことで回避します。
order id 降順で Top10 の顧客名を取得
SELECT customer_full_name FROM "kibana_sample_data_ecommerce" ORDER BY order_id DESC LIMIT 10
{
"size" : 10,
"_source" : {
"includes" : [
"customer_full_name"
],
"excludes" : [ ]
},
"sort" : [
{
"order_id" : {
"order" : "desc",
"missing" : "_first",
"unmapped_type" : "keyword"
}
}
]
}
{
"columns" : [
{
"name" : "customer_full_name",
"type" : "text"
}
],
"rows" : [
[
"Jim Pratt"
],
... 中略
[
"Wilhemina St. Graham"
]
]
}
customer_first_name を重複排除して取得
SELECT DISTINCT customer_first_name from "kibana_sample_data_ecommerce"
{
"error" : {
"root_cause" : [
{
"type" : "verification_exception",
"reason" : "Found 1 problem\nline 2:8: SELECT DISTINCT is not yet supported"
}
],
"type" : "verification_exception",
"reason" : "Found 1 problem\nline 2:8: SELECT DISTINCT is not yet supported"
},
"status" : 400
}
Limitation のページに記載はありませんが、どうやら DISTINCT 句はサポートされていないようです。Aggregation で取得できそうな内容だけに少し残念ですね。
taxless_total_price が 100 以上 200 以下の order id を 10 件取得
SELECT order_id
FROM kibana_sample_data_ecommerce
WHERE taxless_total_price BETWEEN 100 AND 200
LIMIT 10
{
"size" : 10,
"query" : {
"range" : {
"taxless_total_price" : {
"from" : 100,
"to" : 200,
"include_lower" : true,
"include_upper" : true,
"time_zone" : "Z",
"boost" : 1.0
}
}
},
"_source" : false,
"stored_fields" : "_none_",
"docvalue_fields" : [
{
"field" : "order_id"
}
],
"sort" : [
{
"_doc" : {
"order" : "asc"
}
}
]
}
{
"columns" : [
{
"name" : "order_id",
"type" : "keyword"
}
],
"rows" : [
[
"584058"
],
... 中略
[
"578650"
]
]
}
顧客名ごとに注文件数を取得
SELECT COUNT(customer_full_name), customer_full_name FROM kibana_sample_data_ecommerce GROUP BY customer_full_name
{
"size" : 0,
"_source" : false,
"stored_fields" : "_none_",
"aggregations" : {
"groupby" : {
"composite" : {
"size" : 1000,
"sources" : [
{
"f8f23918" : {
"terms" : {
"field" : "customer_full_name.keyword",
"missing_bucket" : true,
"order" : "asc"
}
}
}
]
},
"aggregations" : {
"8df1ff4b" : {
"filter" : {
"exists" : {
"field" : "customer_full_name",
"boost" : 1.0
}
}
}
}
}
}
}
{
"columns" : [
{
"name" : "COUNT(customer_full_name)",
"type" : "long"
},
{
"name" : "customer_full_name",
"type" : "text"
}
],
"rows" : [
[
2,
"Abd Adams"
],
... 中略
[
1,
"Abd Bradley"
]
]
}
JOIN
データの準備
JOIN 先のテーブルを作成するため、以下の定義でインデックスを作成します。
PUT /ecommerce_customer_info
{
"mappings": {
"properties": {
"customer_id": {
"type": "integer",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"customer_full_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
}
}
}
}
仮でいくつかデータを投入します。
POST /_bulk
{"index": {"_index": "ecommerce_customer_info"}}
{"customer_id": 1, "customer_full_name": "Eddie Underwood"}
{"index": {"_index": "ecommerce_customer_info"}}
{"customer_id": 2, "customer_full_name": "Mary Bailey"}
{"index": {"_index": "ecommerce_customer_info"}}
{"customer_id": 3, "customer_full_name": "Gwen Butler"}
{"index": {"_index": "ecommerce_customer_info"}}
{"customer_id": 4, "customer_full_name": "Diane Chandler"}
クエリの実行
SELECT e.customer_id
FROM kibana_sample_data_ecommerce k
INNER JOIN ecommerce_customer_info e
ON k.customer_full_name = e.customer_full_name
{
"error" : {
"root_cause" : [
{
"type" : "parsing_exception",
"reason" : "line 4:2: Queries with JOIN are not yet supported"
}
],
"type" : "parsing_exception",
"reason" : "line 4:2: Queries with JOIN are not yet supported"
},
"status" : 400
}
JOIN 句も DISTINCT 句と同じく、Limitations のページに記載はありませんがサポートされていないようです。
そもそも Elasticsearch のピュアな機能で JOIN ができないので、サポートしないことについては納得ができます。
全文検索
ここからは通常の SQL から外れますが、Elasticsearch らしく全文検索をしてみます。
Full-Text Search Functionsを見ると、2020/12 現在以下がサポートされている様でした。
- Match Query
- query_string Query
- Score
スコアリングもできるのはすごいですね。早速いくつか試してみます。
名前が smith にマッチする顧客の order id を取得
SELECT order_id FROM kibana_sample_data_ecommerce WHERE MATCH(customer_full_name, 'smith')
{
"size" : 1000,
"query" : {
"match" : {
"customer_full_name" : {
"query" : "smith",
"operator" : "OR",
"prefix_length" : 0,
"max_expansions" : 50,
"fuzzy_transpositions" : true,
"lenient" : false,
"zero_terms_query" : "NONE",
"auto_generate_synonyms_phrase_query" : true,
"boost" : 1.0
}
}
},
"_source" : false,
"stored_fields" : "_none_",
"docvalue_fields" : [
{
"field" : "order_id"
}
],
"sort" : [
{
"_doc" : {
"order" : "asc"
}
}
]
}
{
"columns" : [
{
"name" : "order_id",
"type" : "keyword"
}
],
"rows" : [
[
"557262"
],
... 中略
[
"573691"
]
]
}
名前が smith にマッチする顧客をマッチ度順にスコアリング
SELECT customer_full_name
FROM kibana_sample_data_ecommerce
WHERE MATCH(customer_full_name, 'smith')
ORDER BY SCORE()
{
"size" : 1000,
"query" : {
"match" : {
"customer_full_name" : {
"query" : "smith",
"operator" : "OR",
"prefix_length" : 0,
"max_expansions" : 50,
"fuzzy_transpositions" : true,
"lenient" : false,
"zero_terms_query" : "NONE",
"auto_generate_synonyms_phrase_query" : true,
"boost" : 1.0
}
}
},
"_source" : {
"includes" : [
"customer_full_name"
],
"excludes" : [ ]
},
"sort" : [
{
"_score" : {
"order" : "asc"
}
}
]
}
{
"columns" : [
{
"name" : "customer_full_name",
"type" : "text"
}
],
"rows" : [
[
"Wilhemina St. Smith"
],
... 中略
[
"Tariq Smith"
]
]
}
名前に smith が含まれる、それっぽい結果が取得できました。
最後に
本投稿では、Elasticsearch SQL を使ってどこまでの操作が可能かについて検証しました。
個人的には SCORE 関数がしっかり用意しているあたり、全文検索ソリューションらしさを感じて好きでした。その一方まだまだサポートしていないクエリも多く、ログの解析やデータの抽出で使うにしてもすこし苦労する場面は多そうです。
また、translate API を用いることで、初学者の学びのサポートにもなりそうな印象を持ちました。布教の際には使えそうですね。
ただ、こちらの SQL 機能と比較するとOpen Distro for Elasticsearch SQLで実現可能な事の方が多く、Query Work Bench などもあるためより優しい印象を受けました。
色々と選択肢がある中ですが、公式のプラグインとして今後伸びていくことを期待します。
参考
-
https://github.com/elastic/elasticsearch/blob/master/licenses/ELASTIC-LICENSE.txt 各課金体系と利用可能な機能はこちらをご覧ください。 ↩︎