参考

sql-rest

SQL REST API

  • 概述
  • 响应数据格式
  • 对大型响应进行分页
  • 使用Elasticsearch 查询 DSL 进行过滤
  • 列式结果
  • 将参数传递给查询
  • 使用运行时字段
  • 运行异步 SQL 搜索

    概述

    SQL 搜索 API 接受 JSON 文档中的 SQL，执行它，然后返回结果。例如：
    1. POST /_sql?format=txt
    2. {
    3. "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
    4. }
    返回
    1.    author     |     name      |  page_count   |      release_date      
    2. ----------------+---------------+---------------+------------------------
    3. Frank Herbert   |Dune           |604            |1965-06-01T00:00:00.000Z
    4. James S.A. Corey|Leviathan Wakes|561            |2011-06-02T00:00:00.000Z
    5. Dan Simmons     |Hyperion       |482            |1989-05-26T00:00:00.000Z

    使用Kibana控制台

    如果您使用的是 Kibana 控制台（强烈建议您使用），请在创建查询时利用三重引号。这不仅会自动转义查询字符串中的双引号 （），而且还支持多行，如下所示：””””
    1. POST /_sql?format=txt
    2. {
    3. "query": """SELECT * FROM library ORDER BY page_count DESC LIMIT 5"""
    4. }

    响应数据格式

    虽然文本格式对人类来说很好，但计算机更喜欢更有条理的东西。
    Elasticsearch SQL 可以按以下格式返回数据，这些格式可以通过 URL 中的属性或通过设置 HTTP 标头进行设置：formatAccept
格式 接受HTTP 标头 描述: __
人类可读
csv text/csv 逗号分隔值
json application/json JSON（JavaScript 对象表示法）人类可读格式
tsv text/tab-separated-values 制表符分隔的值
txt text/plain 类似命令行的表示形式
yaml application/yaml YAML（非标记语言）人类可读格式
二进制格式
cbor application/cbor 简洁的二进制对象表示
smile application/smile 类似于 CBOR 的微笑)二进制数据格式

json格式

  1. POST /_sql?format=json
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "fetch_size": 5
  5. }

返回

  1. {
  2.   "columns": [
  3.     {
  4.       "name": "author",
  5.       "type": "text"
  6.     },
  7.     {
  8.       "name": "name",
  9.       "type": "text"
  10.     },
  11.     {
  12.       "name": "page_count",
  13.       "type": "long"
  14.     },
  15.     {
  16.       "name": "release_date",
  17.       "type": "datetime"
  18.     }
  19.   ],
  20.   "rows": [
  21.     [
  22.       "Frank Herbert",
  23.       "Dune",
  24.       604,
  25.       "1965-06-01T00:00:00.000Z"
  26.     ],
  27.     [
  28.       "James S.A. Corey",
  29.       "Leviathan Wakes",
  30.       561,
  31.       "2011-06-02T00:00:00.000Z"
  32.     ],
  33.     [
  34.       "Dan Simmons",
  35.       "Hyperion",
  36.       482,
  37.       "1989-05-26T00:00:00.000Z"
  38.     ]
  39.   ]
  40. }

yaml格式

  1. POST /_sql?format=yaml
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "fetch_size": 5
  5. }

返回

  1. ---
  2. columns:
  3. - name: "author"
  4.   type: "text"
  5. - name: "name"
  6.   type: "text"
  7. - name: "page_count"
  8.   type: "long"
  9. - name: "release_date"
  10.   type: "datetime"
  11. rows:
  12. - - "Frank Herbert"
  13.   - "Dune"
  14.   - 604
  15.   - "1965-06-01T00:00:00.000Z"
  16. - - "James S.A. Corey"
  17.   - "Leviathan Wakes"
  18.   - 561
  19.   - "2011-06-02T00:00:00.000Z"
  20. - - "Dan Simmons"
  21.   - "Hyperion"
  22.   - 482
  23.   - "1989-05-26T00:00:00.000Z"

对大型响应进行分页

使用以下示例，可以通过返的Cursor字段来继续下一页。对于 CSV、TSV 和 TXT 格式，Cursor将在 HTTP 标头中返回。

  1. POST /_sql?format=json
  2. {
  3.   "query": "SELECT * FROM library ORDER BY page_count DESC",
  4.   "fetch_size": 2
  5. }

分页查询返回

  1. {
  2.   "columns": [
  3.     {
  4.       "name": "author",
  5.       "type": "text"
  6.     },
  7.     {
  8.       "name": "name",
  9.       "type": "text"
  10.     },
  11.     {
  12.       "name": "page_count",
  13.       "type": "long"
  14.     },
  15.     {
  16.       "name": "release_date",
  17.       "type": "datetime"
  18.     }
  19.   ],
  20.   "rows": [
  21.     [
  22.       "Frank Herbert",
  23.       "Dune",
  24.       604,
  25.       "1965-06-01T00:00:00.000Z"
  26.     ],
  27.     [
  28.       "James S.A. Corey",
  29.       "Leviathan Wakes",
  30.       561,
  31.       "2011-06-02T00:00:00.000Z"
  32.     ]
  33.   ],
  34.   "cursor": "h97qA0RGTACEkd9OwjAUxnvqQgwx8RF8BScQ44UXVVfIokQHDNlNU7oOFrsWtoLg0/gQvp9uAyJe+V003/nT05xfIUIwRxhQpe9S57VDZQqaSSpVzAqT2+aCzyQTZqUtarAkzQsLgMBRRs92/fAFJwjj0lRHPcHdm13dafCVnZscYUfzTCJ8PBOf5VJJXkgWcyvhorB5KmwdMLOwqdFcMZtmkmmuTYHhE+Y310vy7vWmXZWKjG5lED1Mw+hR0HgoFO3FY+vK15iK7tvmyQtb5G7SkdR/ngwnnT7116NW6IWe3w66NIpHbkAO8pcRTS/bLyPaCrQah7TfGdy7a+7ZbT/bfAyuBCH/vUXILWrugTqQ7FcHx8qNLVkkNYLf8AjFgSkkf5GcViwqAuX18o9+AAAA//8DAA=="
  35. }

继续查询下页

  1. POST /_sql?format=json
  2. {
  3.   "cursor": "h97qA0RGTACEkd9OwjAUxnvqQgwx8RF8BScQ44UXVVfIokQHDNlNU7oOFrsWtoLg0/gQvp9uAyJe+V003/nT05xfIUIwRxhQpe9S57VDZQqaSSpVzAqT2+aCzyQTZqUtarAkzQsLgMBRRs92/fAFJwjj0lRHPcHdm13dafCVnZscYUfzTCJ8PBOf5VJJXkgWcyvhorB5KmwdMLOwqdFcMZtmkmmuTYHhE+Y310vy7vWmXZWKjG5lED1Mw+hR0HgoFO3FY+vK15iK7tvmyQtb5G7SkdR/ngwnnT7116NW6IWe3w66NIpHbkAO8pcRTS/bLyPaCrQah7TfGdy7a+7ZbT/bfAyuBCH/vUXILWrugTqQ7FcHx8qNLVkkNYLf8AjFgSkkf5GcViwqAuX18o9+AAAA//8DAA=="
  4. }

返回下一页面的数据

  1. {
  2.   "error": {
  3.     "root_cause": [
  4.       {
  5.         "type": "search_context_missing_exception",
  6.         "reason": "No search context found for id [39577]"
  7.       }
  8.     ],
  9.     "type": "search_phase_execution_exception",
  10.     "reason": "all shards failed",
  11.     "phase": "query",
  12.     "grouped": true,
  13.     "failed_shards": [
  14.       {
  15.         "shard": 0,
  16.         "index": "library",
  17.         "node": "9xROa694RoSuDTBxDaYuMQ",
  18.         "reason": {
  19.           "type": "search_context_missing_exception",
  20.           "reason": "No search context found for id [39577]"
  21.         }
  22.       }
  23.     ]
  24.   },
  25.   "status": 404
  26. }