search
LoginSignup
242
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

Organization

SQLとElasticsearchとのクエリの比較

SQLとElasticsearchのクエリがそれぞれどう対応しているのか、よく使われると思うものについて大体まとめました。

環境

Elasticsearch 6.2.4 〜 7.5

前提条件

記載する内容は以下の条件を前提とします。

Elasticsearchで検索する際のURLの記載は省略

Elasticsearchでは検索を行う際に以下のようなURLへリクエストします。
{ElasticsearchのURL}/{index名}/_search
例えばSQLでproductというテーブルに対して索引を行っている場合、Elasticsearchでは以下のURLへリクエストを行っています。
http://localhost:9200/product/_search

以降Elasticsearchのリクエスト先URLは記載しませんが、上述のようなURLへリクエストしている前提です。

文字列のデータタイプは全てKeyword(全文検索は使わない)

Elasticsearchは全文検索が可能であり、文字列データはフィールドのデータタイプによって全文検索対象かどうかが変わります。

  • 通常の文字列 : Keyword
  • 全文検索対象 : Text

RDBMSでも全文検索が可能ですが利用するRDBMSによって扱い方がかなり異なるので、全文検索を行う場合はSQLと比較するよりもElasticsearch専用の構文として検索方法を理解した方が良いかなと思います。
そのため、今回は文字列のデータは全てKeywordのデータタイプである前提とします。

条件なしで検索

SQLにおけるWHERE句なしの検索は、ElasticsearchではMatch All Queryを利用します。

SQL
SELECT *
FROM product
Elasticsearch
{
  "query": {
    "match_all": {}
  }
}

Elasticsearchの検索結果はhitsというフィールドに格納されて返ってきます。

取得件数や取得開始位置を指定

SQLにおけるLIMIT(その他OFFSETだったりROWNUMだったり)を利用した取得件数や取得開始位置の指定は、ElasticsearchではFrom / Sizeを利用します。

Elasticsearchがfromとsizeで2つのパラメータを指定しているので、SQLの例はLIMITとOFFSETを指定した例を記載します。(RDBMSに合わせて適宜読み替えてください)
例えば先頭5件をスキップして10件だけ取得する場合は以下のようになります。

SQL
SELECT *
FROM product
LIMIT 10 OFFSET 5
Elasticsearch
{
  "from": 5,
  "size": 10, 
  "query": {
    "match_all": {}
  }
}

Elasticsearchのデフォルトでは"from": 0, "size": 10となっています。

取得する列を指定

SQLにおけるSELECT句での取得カラムの指定は、ElasticsearchではSource filteringの機能を利用します。

SQL
SELECT
  product_name,
  product_count 
FROM product
Elasticsearch
{
  "_source": [
    "product_name",
    "product_count"
  ],
  "query": {
    "match_all": {}
  }
}

重複行の削除

SQLにおけるSELECT句でDISTINCTキーワードを指定した重複行の削除は、ElasticsearchではField Collapsingの機能を利用します。

SQL
SELECT DISTINCT
  product_name
FROM product
Elasticsearch
{
  "collapse": {
    "field": "product_name"
  },
  "query": {
    "match_all": {}
  }
}

条件に一致するデータを検索(WHERE句)

以降の例では特定のデータタイプの場合を記載しますが、他のデータタイプを指定する場合も基本的には同様です。

指定した値に等しい

SQLにおける特定の値に等しいデータの検索は、ElasticsearchではTerm Queryを利用します。

SQL
SELECT *
FROM product
WHERE product_name = 'りんご'
Elasticsearch
{
  "query": {
    "term": {
      "product_name": {
        "value": "りんご"
      }
    }
  }
}

指定した複数の値のいずれかに等しい(IN句)

SQLにおけるIN句を利用した検索は、ElasticsearchではTerms Queryを利用します。

SQL
SELECT *
FROM product
WHERE product_name IN ('りんご', 'にんじん')
Elasticsearch
{
  "query": {
    "terms": {
      "product_name": [
        "りんご",
        "にんじん"
      ]
    }
  }
}

指定した値が含まれている(LIKE句)

SQLにおけるLIKE句を利用した検索は、ElasticsearchではWildcard Queryを利用します。

SQL
SELECT *
FROM product
WHERE product_name LIKE '%じ%'
Elasticsearch
{
  "query": {
    "wildcard": {
      "product_name": {
        "value": "*じ*"
      }
    }
  }
}

指定した値より大きい(or 小さい)

SQLにおける指定した値に対する大小を条件にした検索は、ElasticsearchではRange Queryを利用します。
大小の条件は以下のように指定します。

SQL Elasticsearch MEMO
>= gte Greater-Than or Equal to
> gt Greater-Than
<= lte Less-Than or Equal to
< lt Less-Than
SQL
SELECT *
FROM product
WHERE product_count >= 10
Elasticsearch
{
  "query": {
    "range": {
      "product_count": {
        "gte" : 10
      }
    }
  }
}

複数の条件に一致

SQLにおける複数条件を指定した検索は、ElasticsearchではBool Querymustshouldを利用します。
複数の条件は以下のように指定します。

SQL Elasticsearch
AND must
OR should

AND条件の場合

SQL(AND)
SELECT *
FROM product
WHERE product_name = 'りんご'
  AND product_count >= 10
Elasticsearch(must)
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "product_name": "りんご"
          }
        },
        {
          "range": {
            "product_count": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}

OR条件の場合

SQL
SELECT *
FROM product
WHERE product_name= 'りんご'
   OR product_count >= 10
Elasticsearch
{
  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "product_name": "りんご"
          }
        },
        {
          "range": {
            "product_count": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}

ANDとORの組み合わせの場合

ANDとORを組み合わせる場合はBool Queryの中にBool Queryを書くことで実現できます。

SQL
SELECT *
FROM product
WHERE product_count >= 10
  AND (product_name = 'りんご' OR product_price = 100) 
Elasticsearch
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "count": {
              "gte": 10
            }
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "product_name": "りんご"
                }
              },
              {
                "term": {
                  "product_price": 100
                }
              }
            ]
          }
        }
      ]
    }
  }
}

この書き方以外にもminimum_should_matchというパラメータを利用して同じ検索結果を得る方法があるのですが、SQLと比べた場合には上述の方がわかりやすいかなと思います。

指定した条件に一致しない

SQLにおける指定した条件に一致しない場合の検索は、ElasticsearchではBool Querymust_notを利用します。

SQL
SELECT *
FROM product
WHERE product_name <> 'りんご'
Elasticsearch
{
  "query": {
    "bool": {
      "must_not": [
        {
          "term": {
            "product_name": "りんご"
          }
        }
      ]
    }
  }
}

取得するデータを並び変える(ORDER BY句)

SQLにおけるORDER BY句を利用した並び替えは、ElasticsearchではSortの機能を利用します。

SQL
SELECT *
FROM product
ORDER BY product_name ASC
Elasticsearch
{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "product_name": {
        "order": "asc"
      }
    }
  ]
}

データを集計する

Elasticsearchでの集計にはAggregationsを利用します。
検索に比べるとElasticsearch特有の書き方がやや強いので少しわかりにくいかもしれませんが、基本的な構文は以下の通りです。

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

Elasticsearchの集計結果はhitsではなくaggregationsというフィールドに格納されて返ってきます。
検索結果は通常通りhitsの方に格納されて一緒に返ってくるので、検索結果が不要な場合はsizeを0に指定しておくと良いです。

指定した列の平均値や最大値を求める(集計関数)

SQLにおける集計関数を利用したデータ集計は、ElasticsearchではMetrics Aggregationsを利用します。
SQLの集計関数とElasticsearchの対応一覧は以下の通りです。

SQL Elasticsearch
AVG Avg Aggregation
MAX Max Aggregation
MIN Min Aggregation
SUM Sum Aggregation
SQL
SELECT
  AVG(product_count)
FROM product
Elasticsearch
{
  "aggs": {
    "product_count_avg": {
      "avg": {
        "field": "product_count"
      }
    }
  }
}

同じ値を持つデータ毎に集計する(GROUP BY句)

SQLにおけるGROUP BY句を利用したデータ集計は、ElasticsearchではTerms Aggregationを利用します。

SQL
SELECT
  product_name
FROM product
GROUP BY product_name
Elasticsearch
{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      }
    }
  }
}

Terms Aggregationを利用した集計はsizeに指定された上位N件までが集計されます。(例では10件)

集計関数と組み合わせたい場合はAggregationをネストさせます。

SQL
SELECT
  AVG(product_count)
FROM product
GROUP BY product_name
Elasticsearch
{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      },
      "aggs": {
        "product_count_avg": {
          "avg": {
            "field": "product_count"
          }
        }
      }
    }
  }
}

指定した条件のデータを集計する(WHERE句との組み合わせ)

元となるデータを絞り込んだ上で集計したい場合、queryと組み合わせることで実現できます。

SQL
SELECT
  AVG(product_count)
FROM product
WHERE product_name = 'りんご'
Elasticsearch
{
  "size": 0,
  "query": {
    "term": {
      "product_name": {
        "value": "りんご"
      }
    }
  },
  "aggs": {
    "product_count_avg": {
      "avg": {
        "field": "product_count"
      }
    }
  }
}

ちょっとわかりにくいのですが、queryで絞り込んだ結果が返却されるhitsと、集計結果が返却されるaggregationsはそれぞれ別の項目です。
集計結果のみが取得できれば良い場合、上述の例のようにsizeに0を指定することでhitsに何も返却されなくなるため、レスポンスデータが軽くなります。
aggregationsにはちゃんとqueryで指定した条件に一致するデータから集計した結果が返却されます)

集計結果に対して条件を指定する(HAVING句)

SQLにおけるHAVING句を利用した集計結果に対する検索は、ElasticsearchではBucket Selector Aggregationを利用します。

SQL
SELECT
  product_name,
  SUM(product_count)
FROM product
GROUP BY product_name
HAVING SUM(product_count) > 10
Elasticsearch
{
  "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に絞り込み条件の対象となる項目を指定します。
例ではSUM(product_count)に相当するproduct_count_sumを条件指定の対象にしています。

scriptには絞り込み条件を記述します。
buckets_pathで指定した値を利用したい場合はparams.count_sumというように変数名を指定することで実現できます。
paramsは実行したクエリ内の変数を参照する場合に指定する値です。

その他JOINとかサブクエリとか

ElasticsearchはRDBMSでは無いため、残念ながらJOINやサブクエリのような機能はありません。
登録するデータを非正規化したり、データをネストして持つことが出来るNested datatypeを利用したりして上手く検索できるようにデータを登録する必要があります。

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
242
Help us understand the problem. What are the problem?