elasticsearch sql


sqlクエリによるフレックス検索
sql構文を使用して
  • ElasticSearchを簡単に使用する方法
  • 1.表形式の出力
  • 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形式では、カーソル(ローの位置)を出力できます.これは、後でデータ全体を収集するのに役立ちます.

  • 検索例
  • 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の次の行出力は
  • である.
    {
      "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