Elasticsearch SQL과 실제적인 사례 소개 - 파트 2

By
Dale McDiarmid

이 Elasticsearch SQL 시리즈 소개의 파트 1에서는 새 Elasticsearch SQL 기능 일부와 _translate API에 대해 간단하게 소개해 드렸습니다. 이 특별 포스팅에서는 계속해서 좀 더 복잡한 기능을 살펴보면서 이 시리즈를 이어갑니다. 또한 첫 번째 SQL 릴리스에서 사용자가 직면하게 될 가능성이 높은 몇 가지 제한 사항도 살펴보겠습니다. 그 후에는, 로드맵 내용을 살짝 알려드리겠습니다!

demo.elastic.co에서 제공되는 모든 예제를 실행해 보거나 Kibana에서 데이터세트를 사용할 수 있을 때까지 기다릴 수 있다는 것을 상기시켜 드립니다. 이 시리즈에서 사용된 모든 데이트세트에는 인덱스가 생성되었고, SQL은 Kibana 콘솔을 통해 사용 가능합니다. 각 예제에는 demo.elastic.co에 대한 링크가 제공됩니다. 아니면 모든 쿼리를 데모 Kibana 콘솔에서 단일 스레드로 볼 수 있습니다.

복잡한 예제 및 Elasticsearch의 이점

그룹화

수십억 개의 데이터 요소를 요약할 수 있는 잠재력을 가진 Elasticsearch의 집계 프레임워크는 스택에서 가장 강력하고 인기있는 기능 중 하나입니다. 기능 측면에서 SQL의 GROUP BY 연산자와 자연스러운 대응성을 가집니다. GROUP BY 기능의 몇 가지 예를 제공할 뿐만 아니라 translate API를 다시 사용하여 상응되는 집계를 보여드리겠습니다.

“런던행 항공편의 출발 국가별 평균 비행 시간을 찾아보세요. 국가별로 알파벳순으로 나열해보세요.”

demo.elastic.co에서 해보세요.

sql> SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY OriginCountry LIMIT 5;
 Avg_Flight_Time  | OriginCountry
------------------+---------------
9.342180244924574 |AE
13.49582274385201 |AR
4.704097126921018 |AT
15.081367354940724|AU
7.998943401875511 |CA

이 쿼리에 대한 DSL 검사는 복합 집계(Composite Aggregation)가 사용되었음을 보여줍니다. 

{
  "size": 0,
  "_source": false,
  "stored_fields": "_none_",
  "aggregations": {
    "groupby": {
      "composite": {
        "size": 1000,
        "sources": [
          {
            "3471": {
              "terms": {
                "field": "OriginCountry.keyword",
                "order": "asc"
              }
            }
          }
        ]
      },
      "aggregations": {
        "3485": {
          "avg": {
            "field": "FlightTimeHour"
          }
        }
      }
    }
  }
}

demo.elastic.co에서 해보세요.

함수를 사용하여 select에 정의된 별칭 필드별로 그룹화할 수도 있습니다.

“매월 비행 횟수와 평균 비행 시간을 찾아보세요.”

POST _xpack/sql
{
  "query":"SELECT COUNT(*), MONTH_OF_YEAR(timestamp) AS month_of_year, AVG(FlightTimeHour) AS Avg_Flight_Time FROM flights GROUP BY month_of_year"
}

demo.elastic.co에서 해보세요.

   COUNT(1)    | month_of_year | Avg_Flight_Time 
---------------+---------------+-----------------
1303           |5              |8.628949653846158
8893           |6              |8.520481551839334
2863           |7              |8.463433805045094

복합 집계(Composite Aggregation)를 사용하면 한 가지 장점이 있습니다. 즉, 높은 카디널리티 필드에서도 GROUP BY 구현이 확장 가능하도록 보장하여 문서에 대해 수행되는 스크롤과 유사하게 특정 집계의 모든 버킷을 스트리밍하는 메커니즘을 제공합니다. 또한 용어 집계(Terms Aggregation)를 사용하는 경우와 같이 동일한 메모리 제한을 받지 않아도 됩니다. 그러나 현재 GROUP BY 메트릭을 정렬할 수 없음을 의미합니다. 예를 들어 다음과 같은 경우 오류가 발생합니다. 

POST _xpack/sql/translate
{
  "query":"SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY Avg_Flight_Time"
}

demo.elastic.co에서 해보세요.

그룹 필터링

그룹을 필터링하기 위해 HAVING 연산자를 사용할 수 있으며, 또한 SELECT 절에 지정된 별칭을 사용할 수도 있습니다. 이것은 HAVING 이후에 SELECT가 실행되는 관계로 RDBMS 기반 구현에서는 일반적으로 불가능하기 때문에 일부 SQL 전문가에게는 흔한 경우일 수 있습니다. 여기서 HAVING 절은 실행 단계에서 선언된 별칭을 사용하고 있습니다. 하지만 분석기는 HAVING에 사용할 선언을 미리 선택할 만큼 스마트합니다.

“평균 거리가 3000에서 4000마일 사이인 각 출발 도시로부터 항공편의 수, 평균 및 95 백분위수 거리를 찾아보세요.”

demo.elastic.co에서 해보세요.

sql> SELECT OriginCityName, ROUND(AVG(DistanceKilometers)) avg_distance, COUNT(*) c, ROUND(PERCENTILE(DistanceKilometers,95)) AS percentile_distance FROM flights GROUP BY OriginCityName HAVING avg_distance BETWEEN 3000 AND 4000;
OriginCityName | avg_distance  |       c       |percentile_distance
---------------+---------------+---------------+-------------------
Verona         |3078           |120            |7927
Vienna         |3596           |120            |7436
Xi'an          |3842           |114            |7964

HAVING 기능을 구현하기 위해 SQL Elasticsearch에서는 Bucket Selector 파이프라인 집계를 사용하여 매개변수화된 손쉬운 스크립트를 사용하여 값을 필터링합니다. 아래에서 필드 데이터가 활성화되어 있지 않아 실패할 가능성이 있는 표준 텍스트 변형의 사용을 시도하지 않고 집계에 대해 OriginCityName필드의 키워드 변형이 자동으로 선택된다는 것에 유의하세요. avg 및 백분위수 메트릭 집계는 SQL 변형과 동등한 기능을 제공합니다.

demo.elastic.co에서 해보세요.

{
  "size": 0,
  "_source": false,
  "stored_fields": "_none_",
  "aggregations": {
    "groupby": {
      "composite": {
        "size": 1000,
        "sources": [
          {
            "4992": {
              "terms": {
                "field": "OriginCityName.keyword",
                "order": "asc"
              }
            }
          }
        ]
      },
      "aggregations": {
        "4947": {
          "bucket_selector": {
            "buckets_path": {
              "a0": "5010",
              "a1": "5010"
            },
            "script": {
              "source": "params.v0 <= params.a0 && params.a1 <= params.v1",
              "lang": "painless",
              "params": {
                "v0": 3000,
                "v1": 4000
              }
            },
            "gap_policy": "skip"
          }
        },
        "5010": {
          "avg": {
            "field": "DistanceKilometers"
          }
        },
        "5019": {
          "percentiles": {
            "field": "DistanceKilometers",
            "percents": [
              95
            ],
            "keyed": true,
            "tdigest": {
              "compression": 100
            }
          }
        }
      }
    }
  }
}

문자 연산자 및 관련성

전통적인 RDBMS와 비교할 때 검색 엔진으로서의 Elasticsearch의 독특한 기능 중 하나는 관련성 계산을 사용해서 텍스트 데이터의 속성을 고려하여 간단한 예/아니오 이상으로 일치 항목을 채점하는 기능입니다. SQL 구문을 확장하면 이 기능이 노출되며, 기존의 RDBMS가 제공할 수 있는 것을 훨씬 능가합니다.

따라서 QUERY와 MATCH의 두 가지 새로운 연산자를 소개해 드립니다. Elasticsearch에 익숙하신 분들의 경우, 이것은 기본 multi_match 및 query_string 연산자와 동일합니다. Kibana의 사용자는 기본 검색 창을 지원하는 query_string 연산자의 동작에 익숙하실 것입니다. 이것은 지능형 구문 분석 기능을 제공하며 자연어 스타일의 쿼리를 허용합니다. 이 두 연산자에 대한 자세한 내용은 이 블로그의 범위를 벗어나지만 최종 가이드 항목은 이러한 개념을 잘 소개하고 있습니다.

예를 들어, 다음을 고려해 보십시오.

“2018-06-06과 2018-06-17 사이에 Kastrup 공항 출발 및 도착 항공편 중 지연된 항공편을 찾아 날짜별로 정렬하세요.”

demo.elastic.co에서 해보세요.

Kastrup 공항은 실제로 코펜하겐에 위치하며, 정식 명칭은 "Copenhagen Kastrup Airport"입니다. QUERY 연산자를 사용하여 단순히 Kastrup을 검색하겠습니다. 

sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName FROM flights WHERE QUERY('Kastrup') AND FlightDelay=true AND timestamp > '2018-06-20' AND timestamp < '2018-06-27' ORDER BY timestamp;
       timestamp        |   FlightNum   |OriginCityName | DestCityName
------------------------+---------------+---------------+---------------
2018-06-21T01:46:28.000Z|57SWSLT        |Copenhagen     |Orlando
2018-06-21T07:28:07.000Z|X43J6GE        |Abu Dhabi      |Copenhagen
2018-06-21T13:36:31.000Z|7T04SK7        |Copenhagen     |Milan
2018-06-22T19:52:15.000Z|NXMN87D        |Mumbai         |Copenhagen
2018-06-23T08:05:02.000Z|YXHMDKV        |Copenhagen     |Oslo
2018-06-25T18:21:04.000Z|2R86JEZ        |Copenhagen     |Shanghai
2018-06-26T22:16:10.000Z|TCE99LO        |Copenhagen     |Edmonton

여기에 필드를 지정할 요구 사항이 없었다는 것에 유의하세요. QUERY 연산자를 사용하여 "kastrup"을 검색하기만 하면 됩니다. 또한 Kastrup 도착 또는 Kastrup출발 항공편 모두 지연되고 있음에 유의하세요. Elasticsearch 쿼리는 여기 있습니다.

demo.elastic.co에서 해보세요.

{
  "size": 1000,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "filter": [
              {
                "bool": {
                  "filter": [
                    {
                      "query_string": {
                        "query": "Kastrup",
                        "fields": [],
                        "type": "best_fields",
                        "default_operator": "or",
                        "max_determinized_states": 10000,
                        "enable_position_increments": true,
                        "fuzziness": "AUTO",
                        "fuzzy_prefix_length": 0,
                        "fuzzy_max_expansions": 50,
                        "phrase_slop": 0,
                        "escape": false,
                        "auto_generate_synonyms_phrase_query": true,
                        "fuzzy_transpositions": true,
                        "boost": 1
                      }
                    },
                    {
                      "term": {
                        "FlightDelay": {
                          "value": true,
                          "boost": 1
                        }
                      }
                    }
                  ],
                  "adjust_pure_negative": true,
                  "boost": 1
                }
              },
              {
                "range": {
                  "timestamp": {
                    "from": "2018-06-20",
                    "to": null,
                    "include_lower": false,
                    "include_upper": false,
                    "boost": 1
                  }
                }
              }
            ],
            "adjust_pure_negative": true,
            "boost": 1
          }
        },
        {
          "range": {
            "timestamp": {
              "from": null,
              "to": "2018-06-27",
              "include_lower": false,
              "include_upper": false,
              "boost": 1
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "_source": {
    "includes": [
      "FlightNum",
      "OriginCityName",
      "DestCityName"
    ],
    "excludes": []
  },
  "docvalue_fields": [
    "timestamp"
  ],
  "sort": [
    {
      "timestamp": {
        "order": "asc"
      }
    }
  ]
}

Elasticsearch를 처음 사용하는 사용자의 경우 이 예제가 비교적 복잡한 쿼리로 보일 것입니다. 중첩된 범위, 용어 제한 및 쿼리 문자열 연산자를 가진 부울(bool) 쿼리가 있기 때문입니다. SQL에서 애플리케이션을 마이그레이션하는 사용자의 경우, 최종 쿼리가 기능적으로 정확하고 최적화되었는지 우려하기도 전에 먼저 이 쿼리 작업이 늘 버거운 작업이었을 것입니다. 실제 query_string 연산자는 관련성이 필요 없으므로(날짜별로 정렬) 필터에 중첩되며 따라서 필터 캐시를 활용하여 점수 평가를 건너 뛰고 응답 시간을 향상시킬 수 있습니다.

이러한 연산자에 대한 매개변수도 SQL에 노출됩니다. 이 마지막 예는 결과를 제한하기 위해 여러 필드에서 여러 검색어와 함께 MATCH 쿼리를 사용하는 방법을 보여줍니다.

“번개를 포함한 날씨를 가진 바르셀로나 출발 및 도착 항공편을 찾아보세요.”

demo.elastic.co에서 해보세요.

예를 들어 Score () 함수를 통해 관련성 점수를 정렬하고 표시합니다. 

sql> SELECT Score(), timestamp, FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM flights WHERE MATCH('*Weather,*City*', 'Lightning Barcelona', 'type=cross_fields;operator=AND') ORDER BY Score() DESC LIMIT 5;
    SCORE()    |       timestamp        |   FlightNum   |OriginCityName | DestCityName  |    DestWeather    |   OriginWeather
---------------+------------------------+---------------+---------------+---------------+-------------------+-------------------
6.990964       |2018-05-31T06:00:41.000Z|L637ISB        |Barcelona      |Santiago       |Rain               |Thunder & Lightning
6.990964       |2018-06-13T12:23:44.000Z|0GIHB62        |Barcelona      |Buenos Aires   |Clear              |Thunder & Lightning
6.9796515      |2018-06-14T21:04:51.000Z|7GEMEDR        |Barcelona      |Hyderabad      |Thunder & Lightning|Rain
6.9133706      |2018-05-31T01:58:51.000Z|ZTOD7RQ        |Barcelona      |Dubai          |Sunny              |Thunder & Lightning
6.9095163      |2018-06-06T14:02:34.000Z|QSQA5CT        |Barcelona      |Naples         |Rain               |Thunder & Lightning

와일드 카드 패턴을 사용하여 일치시킬 필드를 지정하고 일치를 부울(boolean) AND로 요청합니다. 교차 필드 매개변수는 용어가 한 필드에 모두 표시되도록 요구하지 않으며, 양쪽 필드에 존재하는 경우 다른 필드에도 표시되도록 허용합니다. 데이터의 구조를 감안할 때 이는 일치에 필수입니다.

여기의 예제에서는 행 대 그룹을 반환했습니다. 하지만 QUERY 및 MATCH 연산자는 GROUP BY(사실상 Elasticsearch로 필터링된 집계)와 함께 사용할 수도 있습니다.

교차 인덱스 검색 및 별칭

지금까지 쿼리는 하나의 표/인덱스만을 대상으로 했습니다. 재인덱스 요청을 통해 문서를 새로운 명명된 버전으로 복사하는 항공편 인덱스를 복제하는 경우, 두 인덱스가 동일한 매핑을 가진다고 가정할 때 이 둘을 동시에 쿼리할 수 있습니다. 매핑에 차이가 있으면 분석 시 오류가 발생합니다. 여러 인덱스를 함께 쿼리하려면 사용자는 이를 Elasticsearch 별칭에 추가하거나 WHERE 절에서 와일드 카드를 사용할 수 있습니다. 예를 들어, 다음 두 개의 "f_alias"별칭을 가진 "flights" 및 "flights-2" 인덱스가 있다고 가정해 보겠습니다. 

POST /_aliases
{
    "actions" : [
       { "add" : { "index" : "flights-2", "alias" : "f_alias" } },
        { "add" : { "index" : "flights", "alias" : "f_alias" } }
    ]
}

다음은 논리적으로 동등합니다. 

sql> SELECT FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM flights* ORDER BY timestamp DESC LIMIT 1;
   FlightNum   |OriginCityName | DestCityName  |  DestWeather  | OriginWeather
---------------+---------------+---------------+---------------+---------------
6UPDDGK        |Zurich         |Zurich         |Rain           |Hail
sql> SELECT FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM f_alias ORDER BY timestamp DESC LIMIT 1;
   FlightNum   |OriginCityName | DestCityName  |  DestWeather  | OriginWeather
---------------+---------------+---------------+---------------+---------------
6UPDDGK        |Zurich         |Zurich         |Rain           |Hail

demo.elastic.co에서 해보세요. 데모 환경에는 사용자가 상기 예제를 테스트할 수 있는 사전 구성된 별칭이 포함된 두 개의 인덱스가 포함되어 있습니다.

앞으로 상기 요구 사항을 완화할 수도 있지만, 현재로서는 이것이 첫 번째 릴리스의 논리를 단순화합니다.

JOIN

전통적인 RDBMS SQL 구현에서의 JOIN은 개별 표의 행을 관련 열을 통해 단일 표 형식의 응답으로 결합할 수 있습니다. 이를 통해 관계형 데이터 모델링이 가능하며 이를 달성하기 위해 Elasticsearch에서 기본적으로 사용할 수 있는 옵션과 비교할 때 중요한 주제가 됩니다. Elasticsearch SQL은 현재 JOIN 연산자를 노출하지 않지만 사용자가 일대다의 간단한 관계형 모델링을 제공하는 중첩된 문서를 활용할 수 있게 합니다. 중첩된 문서의 쿼리는 사용자에게 투명하게 처리됩니다. 이 기능을 설명하기 위해 이러한 데이터를 가진 인덱스가 필요합니다. 예를 들어, demo.elastic.co에 "orders" 인덱스를 로드했습니다. 이 인덱스의 문서는 전자 상거래 사이트의 주문을 나타내며 order_date, billing_city 및 customer_last_name과 같은 필드를 포함합니다. 또한 "products" 필드에는 모든 제품에 대한 중첩된 하위 문서가 순서대로 포함되어 있습니다. 예: 

{
          "billing_last_name": "Green",
          "billing_first_name": "Jason",
          "order_id": 519894,
          "products": [
            {
              "tax_amount": 0,
              "taxful_price": 22.99,
              "quantity": 1,
              "taxless_price": 22.99,
              "discount_amount": 0,
              "base_unit_price": 22.99,
              "discount_percentage": 0,
              "product_name": "Briefcase - black",
              "manufacturer": "Pier One",
              "min_price": 11.27,
              "created_on": "2016-11-08T04:16:19+00:00",
              "unit_discount_amount": 0,
              "price": 22.99,
              "product_id": 12733,
              "base_price": 22.99,
              "_id": "sold_product_519894_12733",
              "category": "Men's Accessories",
              "sku": "PI952HA0M-Q11"
            },
            {
              "tax_amount": 0,
              "taxful_price": 16.99,
              "quantity": 1,
              "taxless_price": 16.99,
              "discount_amount": 0,
              "base_unit_price": 16.99,
              "discount_percentage": 0,
              "product_name": "3 PACK - Boxer shorts - white/navy",
              "manufacturer": "Pier One",
              "min_price": 8.33,
              "created_on": "2016-11-08T04:16:19+00:00",
              "unit_discount_amount": 0,
              "price": 16.99,
              "product_id": 18370,
              "base_price": 16.99,
              "_id": "sold_product_519894_18370",
              "category": "Men's Clothing",
              "sku": "PI982AA0Y-A11"
            }
          ],
          "has_error": false,
          "customer_last_name": "Green",
          "currency": "EUR",
          "billing_first_name": "Jason",
          "shipping_country_code": "US",
          "email": "swagelastic@gmail.com",
          "day_of_week": "Tuesday",
          "geoip": {
            "continent_name": "North America",
            "city_name": "New York",
            "country_iso_code": "US",
            "location": {
              "lon": -73.9862,
              "lat": 40.7662
            },
            "region_name": "New York"
          },
          "payment_status": "FULLY_PAID",
          ...
}

일반적으로 이러한 문서를 쿼리하려면 사용자는 제품 필드에 중첩 데이터 형식을 사용하는 이유와 중첩된 쿼리 구문에 대해 이해해야 합니다. 그러나 Elasticsearch SQL을 사용하면 각각이 상위 필드와 별도의 행을 나타내는 것처럼 중첩된 문서를 쿼리할 수 있습니다 (즉, 프레젠테이션을 위해 구조를 효율적으로 플랫화함). 두 가지 제품을 가진 상기 주문을 고려하세요. 쿼리를 받으면 제품 하위 문서에서 필드를 요청할 때 이것은 두 행으로 표시됩니다. 각 행에는 또한 요청 시 상위 주문 필드가 포함될 수도 있습니다. 예:

“항공편 주문 518894에 대해 사용된 청구서 이름과 주문 제품을 찾으세요.”

sql> SELECT billing_last_name, billing_first_name, products.price, products.product_id FROM orders WHERE order_id=519894;
billing_last_name|billing_first_name|products.price |products.product_id
-----------------+------------------+---------------+-------------------
Green            |Jason             |16.984375      |18370
Green            |Jason             |22.984375      |12733

demo.elastic.co에서 해보세요.

_translate API는 중첩된 쿼리를 사용하여 이 쿼리를 생성하는 방법을 보여줍니다. 

{
  "size": 1000,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "order_id": {
              "value": 519894,
              "boost": 1
            }
          }
        },
        {
          "nested": {
            "query": {
              "match_all": {
                "boost": 1
              }
            },
            "path": "products",
            "ignore_unmapped": false,
            "score_mode": "none",
            "boost": 1,
            "inner_hits": {
              "ignore_unmapped": false,
              "from": 0,
              "size": 99,
              "version": false,
              "explain": false,
              "track_scores": false,
              "_source": false,
              "stored_fields": "_none_",
              "docvalue_fields": [
                "products.product_id",
                "products.price"
              ]
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "_source": {
    "includes": [
      "billing_last_name",
      "billing_first_name"
    ],
    "excludes": []
  },
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ]
}

demo.elastic.co에서 해보세요.

반대로 상위 필드에 대해 쿼리하면 행이 하나만 표시됩니다.

“항공편 주문 518894에 대해 사용된 청구서 이름을 찾으세요.”

sql> SELECT billing_last_name, billing_first_name FROM orders WHERE order_id=519894;
billing_last_name|billing_first_name
-----------------+------------------
Green            |Jason

demo.elastic.co에서 해보세요.

제한 사항 및 향후 계획

어떤 실험에서든 초기에 접할 수 있는 현재의 제한 사항

  • 교차 표/인덱스 쿼리 - 이것은 인덱스의 매핑이 동일한 경우 가능합니다. 차이가 있으면 현재 쿼리를 수행할 때 오류가 발생합니다.
  • JOIN - 위에서 강조한 것처럼 중첩된 문서를 사용하여 제한된 JOIN 지원만 제공합니다.
  • GROUP BY의 그룹 순서 - 위에서 설명한 것처럼 이 제한 사항은 Elasticsearch 복합 집계로부터 비롯됩니다.
  • 중첩 SELECT 절 - BI 도구의 심층적으로 지원하는 일반적인 메커니즘입니다. 여러 인덱스를 사용하는 중첩 SELECT 문은 JOIN과 동일하다고 하겠지만, 동일한 인덱스를 사용하는 문은 재작성과 실행이 가능할 수도 있겠습니다. 이것은 향후 고려할 수 있는 사항입니다.

차후 릴리스에서 다루도록 계획하고 있는 Elasticsearcch의 이점 중 일부는 다음과 같습니다.

여기까지 함께 해 주셔서 감사드리며 이 Elasticsearch SQL 시리즈 소개에서 후속편을 기대해 주세요!