elasticsearch sql

sql쿼리를 이용하여 elasticsearch 검색

• elasticsearch를 sql문법을 이용하여 쉽게 사용하는방법

1. table 형태의 출력

• search 예제

POST _sql?format=txt
{
  "query": "SELECT mall_id FROM datalake_market_category_matching",
  "fetch_size": 9
}

• 결과

    mall_id    
---------------
fetchingkorea  
foxclub99      
cafe0224       
fetchingkorea  
foxclub99      
cafe0224       
fetchingkorea  
foxclub99      
cafe0224   

2. jason 형태의 출력

• jason 형태의 경우 cursor 즉 해당 row의 위치를 출력해 주어 이후 전체 데이터를 수집하는데 큰 도움이 된다.

• search 예제

POST _sql?format=json
{
  "query": "SELECT mall_id FROM datalake_market_category_matching",
  "fetch_size": 9
}

• 결과

{
  "columns" : [
    {
      "name" : "mall_id",
      "type" : "text"
    }
  ],
  "rows" : [
    [
      "fetchingkorea"
    ],
    [
      "foxclub99"
    ],
    [
      "cafe0224"
    ],
    [
      "fetchingkorea"
    ],
    [
      "foxclub99"
    ],
    [
      "cafe0224"
    ],
    [
      "fetchingkorea"
    ],
    [
      "foxclub99"
    ],
    [
      "cafe0224"
    ]
  ],
  "cursor" : "q9qtAwFaAXOUAURuRjFaWEo1VkdobGJrWmxkR05vQXdBQUFBQUNKVk51RmpsbVFUQkpTRmRuVTBWNVNVZE1PRlo2TmpsUmNVRUFBQUFBQVE2VHZSWkNlVlY0YzNReGNGUnhiVVZuWlc5WVdHOHRSRVJSQUFBQUFBRU9rNzRXUW5sVmVITjBNWEJVY1cxRloyVnZXRmh2TFVSRVVRPT3/////DwEBZgdtYWxsX2lkAQdtYWxsX2lkAQR0ZXh0AAAAAQE="
}

• cursor를 이용한 다음 데이터 출력

POST _sql?format=json
{
  "query": "SELECT mall_id FROM datalake_market_category_matching",
  "fetch_size": 9,
  "cursor" : "q9qtAwFaAXOUAURuRjFaWEo1VkdobGJrWmxkR05vQXdBQUFBQUNKVk51RmpsbVFUQkpTRmRuVTBWNVNVZE1PRlo2TmpsUmNVRUFBQUFBQVE2VHZSWkNlVlY0YzNReGNGUnhiVVZuWlc5WVdHOHRSRVJSQUFBQUFBRU9rNzRXUW5sVmVITjBNWEJVY1cxRloyVnZXRmh2TFVSRVVRPT3/////DwEBZgdtYWxsX2lkAQdtYWxsX2lkAQR0ZXh0AAAAAQE="
}

• 결과
  • cursor로 입력된 다음 row들이 출력된다

{
  "rows" : [
    [
      "mylady333"
    ],
    [
      "fetchingkorea"
    ],
    [
      "orosiya"
    ],
    [
      "orosiya"
    ],
    [
      "fetchingkorea"
    ],
    [
      "fetchingkorea"
    ],
    [
      "bullanseo"
    ],
    [
      "fetchingkorea"
    ],
    [
      "fetchingkorea"
    ]
  ],
  "cursor" : "q9qtAwFaAXOUAURuRjFaWEo1VkdobGJrWmxkR05vQXdBQUFBQUNKVk51RmpsbVFUQkpTRmRuVTBWNVNVZE1PRlo2TmpsUmNVRUFBQUFBQVE2VHZSWkNlVlY0YzNReGNGUnhiVVZuWlc5WVdHOHRSRVJSQUFBQUFBRU9rNzRXUW5sVmVITjBNWEJVY1cxRloyVnZXRmh2TFVSRVVRPT3/////DwEBZgdtYWxsX2lkAQdtYWxsX2lkAQR0ZXh0AAAAAQE="
}

sql문을 query dsl로 변형

• search 예제

POST _sql/translate
{
  "query": "SELECT market_category_code, market_code, mall_id FROM datalake_market_category_matching"
}

• 결과

{
  "size" : 1000,
  "_source" : {
    "includes" : [
      "market_category_code",
      "market_code",
      "mall_id"
    ],
    "excludes" : [ ]
  },
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

reference

https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-search-api.html
https://www.elastic.co/kr/what-is/elasticsearch-sql