SQL 和 PPL API

使用 SQL 和 PPL API 向 SQL 插件发送查询。使用 `_sql 端点发送 SQL 查询，使用 `_ppl 端点发送 PPL 查询。对于这两种情况，您还可以使用 `_explain 端点将您的查询转换为 OpenSearch 领域特定语言 (DSL) 或进行故障排除。

查询 API

向 SQL 插件发送 SQL/PPL 查询。您可以将响应格式作为查询参数传递。

查询参数

参数	数据类型	描述
format	字符串	响应的格式。``_sql` 端点支持 ``jdbc`、``csv`、``raw` 和 ``json` 格式。``_ppl` 端点支持 ``jdbc`、``csv` 和 ``raw` 格式。默认值为 ``jdbc`。
sanitize	布尔型	指定是否转义结果中的特殊字符。有关更多信息，请参阅响应格式。默认值为 ``true`。

请求正文字段

字段	数据类型	描述
query	字符串	要执行的查询。必填。
filter	JSON 对象	结果的过滤器。可选。
fetch_size	整型	在单个响应中返回的结果数量。用于结果分页。默认值为 1,000。可选。SQL 支持 ``fetch_size`，并且需要使用 ``jdbc` 响应格式。

请求示例

POST /_plugins/_sql 
{
  "query" : "SELECT * FROM accounts"
}

示例响应

响应包含架构和结果

{
  "schema": [
    {
      "name": "account_number",
      "type": "long"
    },
    {
      "name": "firstname",
      "type": "text"
    },
    {
      "name": "address",
      "type": "text"
    },
    {
      "name": "balance",
      "type": "long"
    },
    {
      "name": "gender",
      "type": "text"
    },
    {
      "name": "city",
      "type": "text"
    },
    {
      "name": "employer",
      "type": "text"
    },
    {
      "name": "state",
      "type": "text"
    },
    {
      "name": "age",
      "type": "long"
    },
    {
      "name": "email",
      "type": "text"
    },
    {
      "name": "lastname",
      "type": "text"
    }
  ],
  "datarows": [
    [
      1,
      "Amber",
      "880 Holmes Lane",
      39225,
      "M",
      "Brogan",
      "Pyrami",
      "IL",
      32,
      "amberduke@pyrami.com",
      "Duke"
    ],
    [
      6,
      "Hattie",
      "671 Bristol Street",
      5686,
      "M",
      "Dante",
      "Netagy",
      "TN",
      36,
      "hattiebond@netagy.com",
      "Bond"
    ],
    [
      13,
      "Nanette",
      "789 Madison Street",
      32838,
      "F",
      "Nogal",
      "Quility",
      "VA",
      28,
      "nanettebates@quility.com",
      "Bates"
    ],
    [
      18,
      "Dale",
      "467 Hutchinson Court",
      4180,
      "M",
      "Orick",
      null,
      "MD",
      33,
      "daleadams@boink.com",
      "Adams"
    ]
  ],
  "total": 4,
  "size": 4,
  "status": 200
}

响应正文字段

字段	数据类型	描述
schema	数组	指定所有字段的字段名和类型。
data_rows	二维数组	结果数组。每个结果代表一个匹配的行（文档）。
total	整数	索引中的总行数（文档数）。
size	整数	在单个响应中返回的结果数量。
status	字符串	OpenSearch 运行查询后返回的 HTTP 响应状态。

`Explain` API

SQL 插件的 `explain 功能显示了查询在 OpenSearch 中如何执行，这对于调试和开发非常有用。向 `_plugins/_sql/_explain 或 `_plugins/_ppl/_explain 端点发送 POST 请求将以 JSON 格式返回 OpenSearch 领域特定语言 (DSL)。

从 OpenSearch 3.0.0 开始，当您将 `plugins.calcite.enabled 设置为 `true 时，`explain 响应会提供关于查询执行计划的增强信息。此 API 支持四种输出格式：

standard: 显示逻辑和物理计划（未指定时的默认值）
simple: 显示不带属性的逻辑计划
cost: 显示带成本的逻辑和物理计划
extended: 显示带生成代码的逻辑和物理计划

示例

基本 SQL 查询

以下请求显示了一个基本的 SQL `explain 查询

POST _plugins/_sql/_explain
{
  "query": "SELECT firstname, lastname FROM accounts WHERE age > 20"
}

响应显示查询执行计划

{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": "[firstname, lastname]"
    },
    "children": [
      {
        "name": "OpenSearchIndexScan",
        "description": {
          "request": """OpenSearchQueryRequest(indexName=accounts, sourceBuilder={"from":0,"size":200,"timeout":"1m","query":{"range":{"age":{"from":20,"to":null,"include_lower":false,"include_upper":true,"boost":1.0}}},"_source":{"includes":["firstname","lastname"],"excludes":[]},"sort":[{"_doc":{"order":"asc"}}]}, searchDone=false)"""
        },
        "children": []
      }
    ]
  }
}

使用 Calcite 引擎的高级查询

以下请求演示了使用 Calcite 引擎的更复杂查询

POST _plugins/_ppl/_explain
{
  "query" : "source=state_country | where country = 'USA' OR country = 'England' | stats count() by country"
}

响应以标准格式显示逻辑和物理计划

{
  "calcite": {
    "logical": """LogicalProject(count()=[$1], country=[$0])
  LogicalAggregate(group=[{1}], count()=[COUNT()])
    LogicalFilter(condition=[SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7))])
      CalciteLogicalIndexScan(table=[[OpenSearch, state_country]])
""",
    "physical": """EnumerableCalc(expr#0..1=[{inputs}], count()=[$t1], country=[$t0])
  CalciteEnumerableIndexScan(table=[[OpenSearch, state_country]], PushDownContext=[[FILTER->SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7)), AGGREGATION->rel#53:LogicalAggregate.NONE.[](input=RelSubset#43,group={1},count()=COUNT())], OpenSearchRequestBuilder(sourceBuilder={"from":0,"size":0,"timeout":"1m","query":{"terms":{"country":["England","USA"],"boost":1.0}},"sort":[{"_doc":{"order":"asc"}}],"aggregations":{"composite_buckets":{"composite":{"size":1000,"sources":[{"country":{"terms":{"field":"country","missing_bucket":true,"missing_order":"first","order":"asc"}}}]},"aggregations":{"count()":{"value_count":{"field":"_index"}}}}}}, requestedTotalSize=10000, pageSize=null, startFrom=0)])
"""
  }
}

要简化查询计划视图，您可以使用 `simple 格式

POST _plugins/_ppl/_explain?format=simple
{
  "query" : "source=state_country | where country = 'USA' OR country = 'England' | stats count() by country"
}

响应显示了一个精简的逻辑计划

{
  "calcite": {
    "logical": """LogicalProject
  LogicalAggregate
    LogicalFilter
      CalciteLogicalIndexScan
"""
  }
}

对于需要后处理的查询，`explain 响应除了 OpenSearch DSL 之外，还包含一个查询计划。对于不需要后处理的查询，您将只看到完整的 DSL。

结果分页

要获得分页响应，请使用 `fetch_size 参数。`fetch_size 的值应大于 0。默认值为 1,000。值为 0 将回退到非分页响应。

`fetch_size 参数仅支持 `jdbc 响应格式。

示例

以下请求包含一个 SQL 查询，并指定每次返回五个结果

POST _plugins/_sql/
{
  "fetch_size" : 5,
  "query" : "SELECT firstname, lastname FROM accounts WHERE age > 20 ORDER BY state ASC"
}

响应包含不带 `fetch_size 的查询会包含的所有字段，以及一个用于检索后续结果页面的 `cursor 字段

{
  "schema": [
    {
      "name": "firstname",
      "type": "text"
    },
    {
      "name": "lastname",
      "type": "text"
    }
  ],
  "cursor": "d:eyJhIjp7fSwicyI6IkRYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQU1XZWpkdFRFRkZUMlpTZEZkeFdsWnJkRlZoYnpaeVVRPT0iLCJjIjpbeyJuYW1lIjoiZmlyc3RuYW1lIiwidHlwZSI6InRleHQifSx7Im5hbWUiOiJsYXN0bmFtZSIsInR5cGUiOiJ0ZXh0In1dLCJmIjo1LCJpIjoiYWNjb3VudHMiLCJsIjo5NTF9",
  "total": 956,
  "datarows": [
    [
      "Cherry",
      "Carey"
    ],
    [
      "Lindsey",
      "Hawkins"
    ],
    [
      "Sargent",
      "Powers"
    ],
    [
      "Campos",
      "Olsen"
    ],
    [
      "Savannah",
      "Kirby"
    ]
  ],
  "size": 5,
  "status": 200
}

要获取后续页面，请使用前一个响应中的 `cursor

POST /_plugins/_sql 
{
   "cursor": "d:eyJhIjp7fSwicyI6IkRYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQU1XZWpkdFRFRkZUMlpTZEZkeFdsWnJkRlZoYnpaeVVRPT0iLCJjIjpbeyJuYW1lIjoiZmlyc3RuYW1lIiwidHlwZSI6InRleHQifSx7Im5hbWUiOiJsYXN0bmFtZSIsInR5cGUiOiJ0ZXh0In1dLCJmIjo1LCJpIjoiYWNjb3VudHMiLCJsIjo5NTF9"
}

下一个响应只包含结果的 `datarows 和一个新的 `cursor。

{
  "cursor": "d:eyJhIjp7fSwicyI6IkRYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQU1XZWpkdFRFRkZUMlpTZEZkeFdsWnJkRlZoYnpaeVVRPT0iLCJjIjpbeyJuYW1lIjoiZmlyc3RuYW1lIiwidHlwZSI6InRleHQifSx7Im5hbWUiOiJsYXN0bmFtZSIsInR5cGUiOiJ0ZXh0In1dLCJmIjo1LCJpIjoiYWNjb3VudHMabcde12345",
  "datarows": [
    [
      "Abbey",
      "Karen"
    ],
    [
      "Chen",
      "Ken"
    ],
    [
      "Ani",
      "Jade"
    ],
    [
      "Peng",
      "Hu"
    ],
    [
      "John",
      "Doe"
    ]
  ]
}

如果嵌套字段被展平，`datarows 可能包含比 `fetch_size 更多的记录。

结果的最后一页只有 `datarows 而没有 `cursor。在最后一页上，`cursor 上下文会自动清除。

要显式清除游标上下文，请使用 `_plugins/_sql/close 端点操作

POST /_plugins/_sql/close 
{
   "cursor": "d:eyJhIjp7fSwicyI6IkRYRjFaWEo1UVc1a1JtVjBZMmdCQUFBQUFBQUFBQU1XZWpkdFRFRkZUMlpTZEZkeFdsWnJkRlZoYnpaeVVRPT0iLCJjIjpbeyJuYW1lIjoiZmlyc3RuYW1lIiwidHlwZSI6InRleHQifSx7Im5hbWUiOiJsYXN0bmFtZSIsInR5cGUiOiJ0ZXh0In1dLCJmIjo1LCJpIjoiYWNjb3VudHMiLCJsIjo5NTF9"
}'

响应是 OpenSearch 的确认信息

{"succeeded":true}

过滤结果

您可以直接使用 `filter 参数向 OpenSearch DSL 添加更多条件。

以下 SQL 查询返回所有客户的姓名和账户余额。然后对结果进行筛选，仅包含余额低于 10,000 美元的客户。

POST /_plugins/_sql/ 
{
  "query" : "SELECT firstname, lastname, balance FROM accounts",
  "filter" : {
    "range" : {
      "balance" : {
        "lt" : 10000
      }
    }
  }
}

响应包含匹配的结果

{
  "schema": [
    {
      "name": "firstname",
      "type": "text"
    },
    {
      "name": "lastname",
      "type": "text"
    },
    {
      "name": "balance",
      "type": "long"
    }
  ],
  "total": 2,
  "datarows": [
    [
      "Hattie",
      "Bond",
      5686
    ],
    [
      "Dale",
      "Adams",
      4180
    ]
  ],
  "size": 2,
  "status": 200
}

您可以使用 Explain API 查看此查询在 OpenSearch 中如何执行

POST /_plugins/_sql/_explain 
{
  "query" : "SELECT firstname, lastname, balance FROM accounts",
  "filter" : {
    "range" : {
      "balance" : {
        "lt" : 10000
      }
    }
  }
}'

响应包含与上述查询对应的 OpenSearch DSL 布尔查询

{
  "from": 0,
  "size": 200,
  "query": {
    "bool": {
      "filter": [{
        "bool": {
          "filter": [{
            "range": {
              "balance": {
                "from": null,
                "to": 10000,
                "include_lower": true,
                "include_upper": false,
                "boost": 1.0
              }
            }
          }],
          "adjust_pure_negative": true,
          "boost": 1.0
        }
      }],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "_source": {
    "includes": [
      "firstname",
      "lastname",
      "balance"
    ],
    "excludes": []
  }
}

使用参数

您可以使用 `parameters 字段将参数值传递给预处理的 SQL 查询。

以下 explain 操作使用了带有 `age 参数的 SQL 查询

POST /_plugins/_sql/_explain 
{
  "query": "SELECT * FROM accounts WHERE age = ?",
  "parameters": [{
    "type": "integer",
    "value": 30
  }]
}

响应包含与上述 SQL 查询对应的 OpenSearch DSL 布尔查询

{
  "from": 0,
  "size": 200,
  "query": {
    "bool": {
      "filter": [{
        "bool": {
          "must": [{
            "term": {
              "age": {
                "value": 30,
                "boost": 1.0
              }
            }
          }],
          "adjust_pure_negative": true,
          "boost": 1.0
        }
      }],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  }
}

查询 API
解释 API
- 示例
结果分页
- 示例
过滤结果
使用参数

此页面有帮助吗？

✔ 是 ✖ 否

告诉我们原因

剩余 350 字符

有问题？在 OpenSearch 论坛上提问。

想要贡献？编辑此页面或创建问题。