使用 SQL 在 Amazon OpenSearch 中搜索和分析数据
注册 / 登录 亚马逊云科技账户
已安装 CDK：访问 Amazon CDK 入门，了解更多信息。
本教程中使用的示例代码来自 GitHub
应用程序开发者需要存储许多种类型的数据，不仅是为了提供核心应用程序功能，也是为了提供遥测和跟踪记录，进而监控应用程序的性能。需要捕获的数据常常会随着时间而变化，因为随着应用程序更新迭代，新的指标类型也会随之出现。对于应用程序开发者来说，使用传统关系型数据库、纳入频繁的架构变更同时还要保证这些数据的高效检索和搜索可能会成为一项挑战，特别是当数据量随时间增长变得庞大时。这时候 Amazon OpenSearch 就可以派上用场。它为您的应用程序提供了可扩展的数据存储解决方案，不仅可以存储简单的关系数据，还能存储复杂的 JSON 文档，适用于诸如网站搜索、企业搜索、日志分析、应用程序性能监控以及安全分析等多种使用场景。
在本教程中，您可以亲自将 SQL 与 Amazon OpenSearch 结合使用，使用熟悉的 SQL 查询语法（包括聚合、分组依据和 Where 子句）来查阅数据。您可以通过 JSON 文档或 CSV 表格的方式读取数据，从而可以灵活选择最适合您的格式。您将逐步学习如何在亚马逊云科技控制台中新建 Amazon OpenSearch Serverless 域。您将探索不同类型的可用搜索查询。您还会了解如何在 Amazon OpenSearch Service 中创建和搜索文档。当您以 JSON 文档的格式将数据添加到索引时，OpenSearch 服务会根据您添加的第一个文档创建索引。除了熟悉的 SQL 查询语法之外，您还可以使用丰富的搜索功能，例如模糊匹配、提升权重、短语匹配等。
什么是 Amazon OpenSearch？
理解 OpenSearch 的功能非常重要，这样您才能根据自己的应用程序需求充分利用其优势。OpenSearch 是一个可扩展、灵活且可拓展的开源软件套件，适用于搜索、分析和可观察性应用程序，采用 Apache 2.0 许可证进行授权。OpenSearch 由 Apache Lucene 提供支持并由 OpenSearch 项目社区驱动。借助 OpenSearch，您可以捕获、存储和分析来自各种来源的业务、运维和安全数据。您可以使用常用的数据收集器，并通过异常检测等集成机器学习工具丰富分析管道。OpenSearch 还具备全文搜索、自动异常检测和矢量数据库功能，用于为生成式 AI 应用程序实施语义搜索和检索增强生成 (RAG)。
Amazon OpenSearch 还捆绑了控制面板可视化工具，即 OpenSearch Dashboards，它不仅有助于可视化日志和跟踪数据，还有助于可视化机器学习驱动的异常检测和搜索相关性排名结果。
现在，您已经了解了使用 Amazon OpenSearch 的优势，接下来让我们在亚马逊云科技中设置这项服务。
注意：本教程使用的是具有开放访问权限的域。为了获得最高的安全性，我们建议您将域放入虚拟私有云 (VPC)。
步骤 1 - 创建 Amazon OpenSearch Service 域
为了体验在 OpenSearch 中使用 SQL，我们会创建一个 OpenSearch 服务域，也就是 OpenSearch 集群。
域是指具有指定的设置、实例类型、实例数量和存储资源的集群。您可以使用控制台、Amazon CLI 或 Amazon SDK 来创建 OpenSearch Service 域。
使用控制台创建 OpenSearch Service 域
以下的步骤展示了如何使用亚马逊云科技控制台创建 OpenSearch Service 域，让您可以开始加载示例数据并尝试针对实例数据进行 SQL 查询。
- 转到 https://aws.amazon.com 并选择 Sign In to the Console（登录控制台）。
- 在 Analytics（分析）中，选择 Amazon OpenSearch Service。
- 在“开始使用”选择对话框中，选择“托管集群”，然后点击 Create domain（创建域）。
- 为域命名。本教程中的示例使用的名称是 movies（电影）。
- 至于域的创建方法，选择 Standard create（标准创建）。【注意：要使用最佳实践快速配置生产域，您可以选择 Easy create（简易创建）。本教程的目的是开发和测试，所以我们使用 Standard create（标准创建）。】
- 模板选择 Dev/test（开发/测试）。
- 部署选项选择 Domain with standby（带备用的域）。
- Version（版本）选择最新版本。
- 现在，忽略 Data nodes（数据节点）、Warm and cold data storage（温冷数据存储）、Dedicated master nodes（专用主节点）、Snapshot configuration（快照配置）和 Custom endpoint（自定义端点）部分。
- 为方便起见，本教程使用公共访问域。在 Network（网络）中选择 Public access（公共接入网）。
- 在细粒度访问控制设置中，保持 Enable fine-grained access control（启用细粒度访问控制）复选框处于选中状态。选择 Create master user（创建主用户）并输入用户名和密码。
- 现在，忽略 SAML authentication（SAML 身份验证）和 Amazon Cognito authentication（Amazon Cognito 身份验证）。
- Access policy（访问策略）选择 Only use fine-grained access control（仅使用细粒度访问控制）。在本教程中，细粒度访问控制是用于处理身份验证，而不是域访问策略。
- 忽略其余设置并选择 Create（创建）。新创建的域通常需要 15-30 分钟才能初始化，但根据具体配置可能需要更长时间。域初始化后，选中以打开配置窗格。记录 General information（一般信息）中的域端点（例如 https://search-my-domain.us-east-1.es.amazonaws.com），我们会在下一步中用到。
步骤 2 - 将示例数据引入 OpenSearch 域
这一步包括将示例数据引入 OpenSearch，这样您就可以在示例数据上测试示例 SQL 查询。
1. 在亚马逊云科技控制台上导航到您的 OpenSearch 服务。在控制面板部分，您的域应显示在 Name（名称）表头下。点击任意一个域。
2. 点击域的 OpenSearch 控制面板 URL 链接。
3. 使用您在步骤 1.11 中创建的用户名和密码登录 OpenSearch 控制面板
4. 登录成功后，您将依次看到以下页面。点击“Add data（添加数据）”，选择“Global（全局）”租户，然后点击“Sample eCommerce orders（示例电商订单）”下的“Add data（添加数据）”按钮。这会将包含电商订单的示例数据引入到 OpenSearch 域中，您将在本教程的后续部分中使用 SQL 对示例数据进行查询。
注意：如果示例电商订单已经导入，您会看到“View data（查看数据）”，在这种情况下您可以跳过以上步骤。**
5. 点击汉堡图标【“Open Search Dashboards（打开搜索控制面板）”下左上角的图标，看起来像堆在一起的 3 条水平线】以展开菜单，然后点击“Query Workbench（查询工作台）”。这样会打开“Query Editor（查询编辑器）”，您可以在其中键入 SQL 查询，结果显示在下面的“Output（输出）”窗格中。
将 SQL 和 OpenSearch 中的概念进行映射
在本节中，我们会将 SQL 和 OpenSearch 中的概念进行映射，以便您能更有效地使用 SQL 在您的 OpenSearch 域中搜索数据。虽然 SQL 和 OpenSearch 对于数据的组织方式（以及不同的语义）有不同的术语，但本质上它们的用途是相同的。那就让我们从基础开始吧，大致如下：
| SQL | OpenSearch | 说明 |
| column | field | 对于两者中最基础的概念——数据，存储在各种数据类型的 named（命名）条目中，每个条目包含一个值。在 SQL 中，此类条目叫做 column（列）而在 OpenSearch 叫做 field（字段）。注意，在 OpenSearch 中，一个字段可以包含多个同类型值（本质上是一个列表），而在 SQL 中，一个 column（列）可以只包含某类型的一个值。OpenSearch SQL 将尽可能保留 SQL 语义，并根据查询拒绝返回具有多个值的字段。 |
| row | document | Column和 field 本身并不存在，它们是 row 或 document 的一部分。两者的语义略有不同：row往往是严格的（并且有更多的强制措施），而 document 往往更灵活或宽松（不过仍具有结构）。 |
| table | index | 执行查询（无论是在 SQL 还是 OpenSearch 中）的目标。 |
| schema | 隐式映射 | 在 RDBMS（关系数据库管理系统）中， schema 主要是表的命名空间，通常用作安全边界。OpenSearch 没有为其提供等同的概念。但是，启用安全性后，OpenSearch 会自动应用安全性强制措施，让角色只能看到获得允许的数据【即 SQL 概念中的 schema（模式）】。 |
| catalog 或 database | cluster 实例或域 | 在 SQL 中，catalog 或 database 可以互换使用，并且表示一组模式，即多个表。在 OpenSearch 中，可用索引集分组在 cluster`` or domain 中。语义也略有不同；database 本质上又是一个命名空间（可能会对数据的存储方式产生一些影响），而 OpenSearch cluster 是一个运行时实例，或者更确切地说是至少一个 OpenSearch 实例（通常是分布式运行）组成的一组实例。实际上，这意味着在 SQL 中，一个实例内可能有多个目录，而在 OpenSearch 中，一个实例内只有 一个目录。 |
| cluster | cluster （联合） | 传统意义上，在 SQL 中，集群是指包含多个 catalog 或 database 的单个 RDMBS 实例（见上文）。虽然 RDBMS 往往在一台机器上只有一个运行的实例（非分布式），但 OpenSearch 却采取相反的方式，默认情况下是分布式和多实例的。 此外，OpenSearch cluster 可以以联合的方式连接到其他cluster，这样 cluster 就意味着： single cluster：多个 Elasticsearch 实例通常分布在多台机器上，在同一命名空间中运行。 multiple clusters：多个集群在联合设置中相互连接，每个集群都有自己的命名空间。 Amazon OpenSearch Service 中的跨集群搜索可让您跨多个相互连接的域进行查询和聚合。使用多个较小的域而不是单个大域通常更有意义，特别是在运行的工作负载类型不同的情况下。 |
步骤 3 - 运行基本 SQL 查询
我们从运行一组基本 SQL 查询开始，帮助您理解关键概念。
1. 要列出当前域中的所有索引，请在查询工作台中运行以下 SQL 查询：
SHOW TABLES LIKE %示例结果（摄入的示例数据表为 opensearch_dashboards_sample_data_ecommerce）
| TABLE_NAME |
| .kibana_1 |
| .opendistro_security |
| fruit |
| opensearch_dashboards_sample_data_ecommerce |
| opensearch_dashboards_sample_data_flights |
| opensearch_dashboards_sample_data_logs |
| .kibana |
2. 从示例电商表中检索有限的一组文档 (5)（示例结果如以下 SQL 查询所示）
SELECT *
FROM opensearch_dashboards_sample_data_ecommerce
limit 5
注意：“order（订单）”旁边的（+）号表示存在嵌套 JSON 文档，可展开查看。稍后我们将了解如何使用 SQL 查询嵌套的 JSON 文档字段。
点击第一个订单旁边的（+）号，里面会显示展开结果（第 1 层级），会看到该订单随附的（相关）products（产品）、event（事件）和 geoip 实体。
点击（订单 ID 584677）products（产品）、event（事件）和geoip 主题旁边的（+）号。在 geoip 下，点击 Africa（非洲）旁边的（+）号和 location（位置）旁边的（+）号。这样就能显示所有与该订单相关的嵌套数据。
3. 我们想要检索特定的订单，因此需要使用下面的 SQL 查询，并翻阅到结果找到 order_id 列的数据类型来获取该表中所有列的数据类型。
DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_ecommerce
4. 由于该列的数据类型 order_id 是关键字，因此我们需要将搜索放在在引号中（已更新）。
SELECT *
FROM opensearch_dashboards_sample_data_ecommerce
WHERE order_id = '584677'5. 使用 SQL SELECT 子句以及 FROM、WHERE、GROUP BY、HAVING、ORDER BY 和 LIMIT 来搜索和聚合数据。在这些子句中，SELECT 和 FROM 是必需的，因为它们指定要检索哪些字段以及对哪些索引进行检索。所有其他子句都是可选的。
搜索和聚合数据的完整语法如下（详细信息请参阅此链接）：
SELECT [DISTINCT] (* | expression) [[AS] alias] [, ...]
FROM index_name
[WHERE predicates]
[GROUP BY expression [, ...]
[HAVING predicates]]
[ORDER BY expression [IS [NOT] NULL] [ASC | DESC] [, ...]]
[LIMIT [offset, ] size]我们希望使用复杂的过滤器运行 SQL 查询，以从电商示例表中获取有限的一组结果。下面是一个使用括号来绑定 where 子句组件的 SQL 示例，即如果查询中有多个 and or 条件，则需要使用括号来保证运算顺序无误。简而言之，对于复杂的查询，有必要使用圆括号（半角），对于简单的查询，可以不使用圆括号。此示例展示了如何在单个语句中组合使用 >、OR、BETWEEN、AND、=、NOT 和 SQL 函数。
SELECT type, day_of_week_i, total_quantity, taxless_total_price
FROM opensearch_dashboards_sample_data_ecommerce
where (total_quantity > 3 or taxless_total_price between 200 and 300)
and day_of_week = 'Friday'
and customer_gender is not 'MALE'
and MATCHQUERY(category, 'ACCESSORIES')
limit 5| type | day_of_week_i | total_quantity | taxless_total_price |
| order | 4 | 4 | 133.96 |
| order | 4 | 4 | 86.96 |
| order | 4 | 4 | 112.96 |
| order | 4 | 4 | 121.96 |
| order | 4 | 4 | 70.96 |
使用 DISTINCT 子句会只返回唯一字段值。您可以指定一个或多个字段名称：
SELECT DISTINCT manufacturer
FROM opensearch_dashboards_sample_data_ecommerce| manufacturer |
| Angeldale |
| Champion Arts |
| Crystal Lighting |
| Elitelligence |
| Gnomehouse |
| Gnomehouse mom |
7. 使用 SUM 等聚合函数时，要将单个字段名/列或表达式作为一个参数括起来。如果您在聚合函数之前指定有一个或多个字段的列表，则必须指定相同的列表作为 GROUP BY 子句的一部分，否则聚合函数会计算索引中所有文档的表达式。运行以下的查询来计算 manufacturer（制造商）的 total_quantity（总销售额）总和。
使用 GROUP BY 子句定义结果集的子集。您可以在 GROUP BY 子句中指定要聚合的字段名（列名）。例如，以下查询会返回部门编号以及每个部门的总销售额：
SELECT manufacturer, sum(total_quantity)
FROM opensearch_dashboards_sample_data_ecommerce
group by manufacturer| manufacturer | sum(total_quantity) |
| 102 | |
| Angeldale | 1220 |
| Champion Arts | 556 |
| Crystal Lighting | 50 |
| Elitelligence | 3076 |
| Gnomehouse | 1046 |
| Gnomehouse mom | 60 |
| Karmanite | 34 |
| Low Tide Media | 3507 |
| Microlutions | 799 |
| Oceanavigations | 2743 |
| Primemaster | 152 |
| Pyramidustries | 2142 |
| Pyramidustries active | 328 |
| Spherecords | 958 |
| Spherecords Curvy | 122 |
| Spherecords Maternity | 128 |
| Spritechnologies | 616 |
| Tigress Enterprises | 2388 |
| Tigress Enterprises Curvy | 138 |
| Tigress Enterprises MAMA | 148 |
8. 您可以在 GROUP BY 子句中使用表达式。例如，以下查询返回每年和每月的平均销售额或 total_price 以及记录次数 (count)：
SELECT year(order_date), month(order_date), avg(taxless_total_price), count(*)
FROM opensearch_dashboards_sample_data_ecommerce
GROUP BY year(order_date),month(order_date);| year(order_date) | month(order_date) | avg(taxless_total_price) | count(*) |
| 2023 | 6 | 74.69634 | 1338 |
| 2023 | 7 | 75.19941 | 3337 |
OpenSearch 支持以下聚合函数：
| 函数 | 说明 |
| AVG | 返回结果的平均值。 |
| COUNT | 返回结果数量。 |
| SUM | 返回结果的总和。 |
| MIN | 返回结果中的最小值。 |
| MAX | 返回结果中的最大值。 |
| VAR_POP 或 VARIANCE | 返回舍去空值后结果的总体方差。若只有一行结果则返回 0。 |
| VAR_SAMP | 返回舍去空值后结果的样本方差。若只有一行结果则返回 null。 |
| STD 或 STDDEV | 返回结果的样本标准差。若只有一行结果则返回 0。 |
| STDDEV_POP | 返回结果的总体标准差。若只有一行结果则返回 0。 |
| STDDEV_SAMP | 返回结果的样本标准差。若只有一行结果则返回 null。 |
9. 使用聚合表达式作为 SELECT 中较大表达式的一部分。以下查询将每个 manufacturer（制造商）的 average commission（平均佣金）以 average sales（平均销售额）的 5% 计算：
SELECT manufacturer, avg(taxless_total_price) * 0.05 as avg_commission
FROM opensearch_dashboards_sample_data_ecommerce
GROUP BY manufacturer;| manufacturer | avg_commission |
|---|---|
| 10.1878 | |
| Angeldale | 5.70938 |
| Champion Arts | 3.46576 |
| Crystal Lighting | 4.1681 |
| Elitelligence | 3.42215 |
| Gnomehouse | 4.50266 |
| Gnomehouse mom | 4.33275 |
| Karmanite | 7.10333 |
| Low Tide Media | 4.22025 |
| Microlutions | 3.64217 |
| Oceanavigations | 4.53148 |
| Primemaster | 7.92308 |
| Pyramidustries | 3.38982 |
| Pyramidustries active | 3.2559 |
| Spherecords | 3.09347 |
| Spherecords Curvy | 3.22758 |
| Spherecords Maternity | 3.54196 |
| Spritechnologies | 3.83885 |
| Tigress Enterprises | 3.60001 |
| Tigress Enterprises Curvy | 3.53235 |
| Tigress Enterprises MAMA | 3.85656 |
在 SQL 中使用 WHERE 和 HAVING 子句
1. WHERE 和 HAVING 都是用于过滤结果。由于 WHERE 过滤器在 GROUP BY 阶段之前应用，因此不能在 WHERE 子句中使用聚合函数。但是，您可以使用 WHERE 子句来将行限制为随后要聚合的行。以下是可与 WHERE 子句一起使用的运算符列表，用于指定过滤结果的条件。
| 运算符 | 行为 |
|---|---|
| ‘= | 等于。 |
| --- | --- |
| <> | 不等于。 |
| > | 大于。 |
| < | 小于。 |
| >= | 大于或等于。 |
| <= | 小于或等于。 |
| IN | 指定多个 OR 运算符。 |
| BETWEEN | 类似于 Range query（范围查询）。有关范围查询的更多信息，请参阅范围查询。 |
| LIKE | 用于全文搜索。有关全文查询的更多信息，请参阅全文查询。 |
| IS NULL | 检查字段值是否为 NULL。 |
| IS NOT NULL | 检查字段值是否为 NOT NULL。 |
您可以将比较运算符（=、<>、>、>=、<、<=）与布尔运算符 NOT、AND 或 OR 结合起来构建更复杂的表达式。
输入以下查询
SELECT order_id
FROM opensearch_dashboards_sample_data_ecommerce
where total_quantity > 3
limit 5| order_id |
|---|
| 723055 |
| 727462 |
| 727269 |
| 727381 |
| 718424 |
2. 由于 HAVING 过滤器在 GROUP BY 阶段之前应用，因此可以使用 HAVING 子句来限制结果中包含的组。
以下查询会返回 manufacturer（制造商）列表以及 total_quantity（总销售额）的总和，其中总和超过了 1000。
SELECT manufacturer, sum(total_quantity)
FROM opensearch_dashboards_sample_data_ecommerce
group by manufacturer
having sum(total_quantity)> 1000| manufacturer | sum(total_quantity) |
|---|---|
| Angeldale | 1220 |
| Elitelligence | 3076 |
| Gnomehouse | 1046 |
| Low Tide Media | 3507 |
| Oceanavigations | 2743 |
| Pyramidustries | 2142 |
| Tigress Enterprises | 2388 |
3. 在 HAVING 子句中使用聚合表达式的别名。以下查询使用别名返回 manufacturer（制造商）的总销售额，其中总和超过了 2000。
SELECT manufacturer, sum(total_quantity) tot_qty
FROM opensearch_dashboards_sample_data_ecommerce
group by manufacturer
having tot_qty >2000| manufacturer | tot_qty |
|---|---|
| Elitelligence | 3076 |
| Low Tide Media | 3507 |
| Oceanavigations | 2743 |
| Pyramidustries | 2142 |
| Tigress Enterprises | 2388 |
4. DELETE 语句会删除满足 WHERE 子句中谓词的文档。如果不指定 WHERE 子句，则所有文档都会被删除。这个语句在默认情况下是禁用的。要启用 SQL 中的 DELETE 功能，需要使用 Dev Tools（开发工具）中的控制台发送以下请求来更新配置。
点击汉堡包图标（左上角）和“Dev Tools（开发工具）”菜单选项（底部），如以下屏幕截图所示。
在“Dev Tools（开发工具）”控制台中，在控制台左侧窗格中键入以下代码，然后通过点击左侧窗格右上角的三角形图标发送请求。请求的响应会显示在下面的右侧窗格中。
PUT _plugins/_query/settings
{
"transient": {
"plugins.sql.delete.enabled": "true"
}
}
然后运行以下查询：
DELETE
FROM opensearch_dashboards_sample_data_ecommerce
WHERE total_quantity >4deleted_rows 字段会显示已删除的文档个数。
步骤 4 - 对多个索引或表进行复杂的 SQL 查询
我们已经介绍了 SQL 查询的基础知识，在这一步我们会运行更复杂的 SQL 查询来理解其中的工作原理。
我们将一些与员工和账户相关的数据插入（到单独的索引中）。导航到 DevTools（开发工具）并通过以下语句批量插入。在控制台左侧窗格中输入以下内容，结果将显示在控制台右侧。
插入到 employees_nested 中的语句：
POST employees_nested/_bulk?refresh
{"index":{"_id":"1"}}
{"id":3,"name":"Bob Smith","title":null,"projects":[{"name":"SQL Spectrum querying","started_year":1990},{"name":"SQL security","started_year":1999},{"name":"OpenSearch security","started_year":2015}]}
{"index":{"_id":"2"}}
{"id":4,"name":"Susan Smith","title":"Dev Mgr","projects":[]}
{"index":{"_id":"3"}}
{"id":6,"name":"Jane Smith","title":"Software Eng 2","projects":[{"name":"SQL security","started_year":1998},{"name":"Hello security","started_year":2015,"address":[{"city":"Dallas","state":"TX"}]}]}插入到账户中的语句：
PUT accounts/_bulk?refresh
{"index":{"_id":"1"}}
{"account_number":1,"balance":39225,"firstname":"Amber","lastname":"Duke","age":32,"gender":"M","address":"880 Holmes Lane","employer":"Pyrami","email":"amberduke@pyrami.com","city":"Brogan","state":"IL"}
{"index":{"_id":"6"}}
{"account_number":6,"balance":5686,"firstname":"Hattie","lastname":"Bond","age":36,"gender":"M","address":"671 Bristol Street","employer":"Netagy","email":"hattiebond@netagy.com","city":"Dante","state":"TN"}
{"index":{"_id":"13"}}
{"account_number":13,"balance":32838,"firstname":"Nanette","lastname":"Bates","age":28,"gender":"F","address":"789 Madison Street","employer":"Quility","email":"nanettebates@quility.com","city":"Nogal","state":"VA"}
{"index":{"_id":"18"}}
{"account_number":18,"balance":4180,"firstname":"Dale","lastname":"Adams","age":33,"gender":"M","address":"467 Hutchinson Court","email":"daleadams@boink.com","city":"Orick","state":"MD"}OpenSearch SQL 支持复杂查询，例如子查询、连接查询、联合查询和差集查询等。这些查询对多个 OpenSearch 索引进行操作。由于 OpenSearch 不是关系数据库，因此连接存在限制，并且性能可能存在瓶颈。OpenSearch SQL 支持内连接、交叉连接和左外连接。
1. 使用内连接
内连接可通过根据连接条件组合两个索引的列来创建新的结果集。内连接会迭代两个索引并比较每个文档来找到满足连接谓词的文档。您可以选择在 JOIN 子句前面添加 INNER 关键字。连接条件由 ON 子句指定。
SQL 查询：
SELECT
A.account_number,
A.firstname,
A.lastname,
E.id,
E.name
FROM accounts A
JOIN employees_nested E
ON A.account_number = E.id结果集：
| a.account**_number** | a.firstname | a.lastname | e.id | e.name |
| 6 | Hattie | Bond | 6 | Jane Smith |
2. 使用交叉连接（也称为笛卡尔连接）将第一个索引中的每个文档与第二个索引中的每个文档组合起来。结果集就是者两个索引中文档的笛卡尔积。此操作类似于没有 ON 子句指定连接条件的内连接。
注意：对两个中大型的索引执行交叉连接是存在风险的。可能会触发一个断路器来终止查询以避免内存不足。
SELECT
a.account_number, a.firstname, a.lastname,
e.id, e.name
FROM accounts a
JOIN employees_nested e结果集：
| a.account_number | a.firstname | a.lastname | e.id | e.name |
| 1 | Amber | Duke | 3 | Bob Smith |
| 1 | Amber | Duke | 4 | Susan Smith |
| 1 | Amber | Duke | 6 | Jane Smith |
| 6 | Hattie | Bond | 3 | Bob Smith |
| 6 | Hattie | Bond | 4 | Susan Smith |
| 6 | Hattie | Bond | 6 | Jane Smith |
| 13 | Nanette | Bates | 3 | Bob Smith |
| 13 | Nanette | Bates | 4 | Susan Smith |
| 13 | Nanette | Bates | 6 | Jane Smith |
| 18 | Dale | Adams | 3 | Bob Smith |
| 18 | Dale | Adams | 4 | Susan Smith |
| 18 | Dale | Adams | 6 | Jane Smith |
3. 如果第一个索引不满足连接谓词，则使用左外连接保留第一个索引中的行。关键字 OUTER 是可选的。
SELECT
a.account_number, a.firstname, a.lastname,
e.id, e.name
FROM accounts a
LEFT JOIN employees_nested e
ON a.account_number = e.id结果集：
| a.account**_number** | a.firstname | a.lastname | e.id | e.name |
| 1 | Amber | Duke | null | null |
| 6 | Hattie | Bond | 6 | Jane Smith |
| 13 | Nanette | Bates | null | null |
| 18 | Dale | Adams | null | null |
4. 使用子查询，这是在另一个语句中使用的完整的 SELECT 语句，并用括号括起来：
SELECT
a1.firstname,
a1.lastname,
a1.balance
FROM accounts a1
WHERE a1.account_number IN (
SELECT a2.account_number
FROM accounts a2
WHERE a2.balance >10000
)结果集：
| a1.firstname | a1.lastname | a1.balance |
| Amber | Duke | 39225 |
| Nanette | Bates | 32838 |
步骤 5 - 使用 SQL 函数
这一步中，我们将深入探究 OpenSearch 提供的多样化 SQL 函数，这将有助于应用程序开发者充分发掘其搜索与分析的强大功能。
有关支持的函数列表，请参阅此处文档。
1. 使用 SQL 中的 MATCH 函数进行全文搜索（支持在 OpenSearch 中可用的全文查询的子集）。您可以搜索与给定字段的 string、number、date 或 boolean 值相匹配的文档。MATCHQUERY 和 MATCH_QUERY 函数是 MATCH 关联性函数的同义词。语法：
match(field_expression, query_expression[, option=<option_value>]*)您可以按任意顺序指定以下选项：
- analyzer
- boost
SQL 查询：
SELECT address FROM accounts where match(address,"Street")| address |
|---|
| 789 Madison Street |
| 671 Bristol Street |
SELECT address FROM accounts where match(address,"Street Lane",operator='or')| address |
|---|
| 789 Madison Street |
| 671 Bristol Street |
| 880 Holmes Lane |
上述结果包含包含“Lane”或“Street”的地址。
2. 要在多个字段中搜索文本，请使用 MULTI_MATCH 函数。此函数会映射到 搜索引擎中使用的multi_match 查询，返回的文档会将所提供的为本、数字、日期或布尔值与给定的一个或多个字段相匹配。例如，在 firstname 或 lastname 字段中搜索Dale，这一操作会通过 SQL 使用 multi_match 函数来调用。MATCHQUERY 和 MATCH_QUERY 函数是 MATCH 关联性函数的同义词。
SELECT firstname, lastname
FROM accounts
WHERE multi_match(['*name'], 'Dale')| firstname | lastname |
|---|---|
| Dale | Adams |
3. 要返回每个匹配文档的关联性评分，请使用 SCORE、SCOREQUERY 或 SCORE_QUERY 函数。
语法：
SCORE 函数需要传入两个参数。第一个参数是 MATCH_QUERY 表达式。第二个参数是一个可选的浮点数，用于提高评分（默认值为 1.0）：
SCORE(match_query_expression, score)
SCOREQUERY(match_query_expression, score)
SCORE_QUERY(match_query_expression, score)以下示例使用 SCORE 函数来提高文档的评分（score）：
SELECT
account_number,
address,
_score
FROM
accounts
WHERE
SCORE(MATCH_QUERY(address, 'Lane'), 0.5) OR
SCORE(MATCH_QUERY(address, 'Street'), 100)
ORDER BY
_score结果包含带相应评分的匹配项：
| account_number | address | score |
| 1 | 880 Holmes Lane | 0.5 |
| --- | --- | --- |
| 6 | 671 Bristol Street | 100 |
| 13 | 789 Madison Street | 100 |
SQL 教程到此结束，如果您不再使用 OpenSearch 域/索引资源，可以将其删除。以下部分提供了关于 OpenSearch 的更多信息。
Querying OpenSearch using SQL REST API（使用 SQL REST API 查询 OpenSearch）有关 SQL 插件的完整 REST API 参考，请参阅 SQL/PPL API。
要将 SQL 插件与您自己的应用程序一起使用，请将请求发送到 _plugins/_sql 端点：
POST _plugins/_sql
{
"query": "SELECT * FROM accounts LIMIT 3,"
}可以使用用逗号分隔的列表查询多个索引：
POST _plugins/_sql
{
"query": "SELECT * FROM my-index1,myindex2,myindex3 LIMIT 50"
}还可以使用通配符表达式指定索引模式：
POST _plugins/_sql
{
"query": "SELECT * FROM my-index* LIMIT 50"
}要在命令行中运行上述查询，请使用 curl 命令：
curl -XPOST https://localhost:9200/_plugins/_sql -u 'admin:admin' -k -H 'Content-Type: application/json' -d '{"query": "SELECT * FROM my-index* LIMIT 50"}'您可以将响应格式 指定为 JDBC、标准 OpenSearch JSON、CSV 或 RAW 格式。默认情况下，查询会返回 JDBC 格式的数据。以下查询可将格式设置为 JSON：
POST _plugins/_sql?format=json
{
"query": "SELECT * FROM my-index LIMIT 50"
}使用 REST API 通过 SQL 在 OpenSearch 中查询数据的另一种方法是使用以下格式将 HTTP 请求发送到 _sql：
POST domain-endpoint/_plugins/_sql
{
"query": "SELECT * FROM my-index LIMIT 50"
}例如，使用开发工具控制台，再运行以下 SQL 查询（结果如下所示）：
通过 API 进行 SQL 查询
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": [
[
13,
"Nanette",
"789 Madison Street",
32838,
"F",
"Nogal",
"Quility",
"VA",
28,
"nanettebates@quility.com",
"Bates"
],
[
6,
"Hattie",
"671 Bristol Street",
5686,
"M",
"Dante",
"Netagy",
"TN",
36,
"hattiebond@netagy.com",
"Bond"
],
[
1,
"Amber",
"880 Holmes Lane",
39225,
"M",
"Brogan",
"Pyrami",
"IL",
32,
"amberduke@pyrami.com",
"Duke"
],
[
18,
"Dale",
"467 Hutchinson Court",
4180,
"M",
"Orick",
null,
"MD",
33,
"daleadams@boink.com",
"Adams"
]
],
"total": 4,
"size": 4,
"status": 200
}将 JDBC 与 OpenSearch 配合使用
现在，我们介绍如何将 JDBC 与 OpenSearch 配合使用，让开发者和 OpenSearch 用户能够运行 SQL 查询并将 OpenSearch 与您最喜欢的商业智能 (BI) 应用程序集成。
OpenSearch Java 数据库连接 (JDBC) 驱动程序采用 JAR 文件的格式，该文件可以通过 GitHub 上的 SQL 存储库链接中提供的信息下载。安装后，您就可以通过 SQL 客户端（例如 DBeaver）与 OpenSearch 建立连接，然后可以在该客户端上运行 SQL 查询。同样，Amazon Quicksight 有一个连接 OpenSearch 的连接器，用于实现数据可视化。然而，在 OpenSearch 中将数据可视化的最常见的方法是通过 OpenSearch 控制面板，这也是 Kibana 的一个分支。
关于查询 DSL 的说明：OpenSearch 提供了一种称为查询领域特定语言 (DSL) 的搜索语言，您可以使用它来搜索数据。查询 DSL 是一种具有 JSON 接口的灵活语言。使用查询 DSL，您需要在搜索的 query 参数中指定查询。OpenSearch 中最简单的搜索之一使用 match_all 查询，可匹配索引中的所有文档：
GET testindex/_search
{
"query": {
"match_all": {
}
}
}一个查询可以由许多查询子句组成。您可以组合查询子句形成复杂的查询。一般来说，查询可以分为两类，即叶子查询和复合查询：
复合查询：复合查询充当多个叶子查询或复合查询子句的包装器，以组合查询结果或修改查询行为。复合查询包括布尔查询、最大析取查询、Constant Score 查询、Function Score 查询和 Boosting 查询等类型。要了解更多信息，请参阅复合查询。
叶子查询：叶子查询可在某个或多个字段中搜索指定值。叶子查询可以单独使用。叶子查询包括以下查询类型：
- 全文查询：使用全文查询来搜索文本文档。对于分析后的文本字段搜索，全文查询使用索引字段时所使用的分析器将查询字符串拆分为术语。对于精确值搜索，全文查询会查找指定的值而不会应用文本分析。要了解更多信息，请参阅全文查询。
- 术语级查询：使用术语级查询在文档中精确搜索指定的术语，例如 ID 或值的范围。术语级查询不会分析搜索术语或按相关性评分对结果进行排序。要了解更多信息，请参阅术语级查询。
- 地理和 XY 查询：使用地理查询来搜索包含地理数据的文档。使用 XY 查询搜索包含二维坐标系中的点和形状的文档。要了解更多信息，请参阅地理和 XY 查询。
- 连接查询：使用连接查询来搜索嵌套字段或返回与特定查询匹配的父文档和子文档。连接查询类型包括 nested、has_child、has_parent 和 parent_id 查询。
- 跨度查询：使用跨度查询执行精确的位置搜索。跨度查询是低级的特定查询，可控制指定查询项的顺序和邻近度。跨度查询主要用于搜索法律文档。要了解更多信息，请参阅跨度查询。
- 特殊查询：特殊查询包括所有其他的查询类型（distance_feature、more_like_this、percolate、rank_feature、script、script_score、wrapper 和 pinned_query）。
总结
我们介绍了 Amazon OpenSearch 的功能、优势和常见使用场景。为了了解如何使用 SQL 在 OpenSearch 中轻松搜索数据，我们在 OpenSearch 中提取了示例数据，然后对示例数据运行了一套简单和复杂的 SQL 查询。
SQL 支持实际上对于 OpenSearch 应用程序开发人员和终端用户来说非常重要，因为它能为应用程序开发者和数据分析师提供了一种简单的机制来查询 OpenSearch 中的数据。通过组合使用 SQL 运算符、SQL 函数和表之间的连接（注意：从性能角度来看，表连接操作较为昂贵，尤其是对于大型表），开发人员可以缩短开发时间，这是因为更复杂的搜索可以用更少的代码来完成。这种方式实现了通过 SQL 以非编程的方式（而不是 REST API）从 BI 查询和报告工具中访问和分析 OpenSearch 数据，满足了一大关键要求。
后续步骤
建议：您可以通过查看本网站的 OpenSearch 文档中的文档来进一步了解最新版本的 OpenSearch。要使用 OpenSearch SQL API 构建应用程序，请查看此链接 OpenSearch SQL API 并参考不同的 SQL 函数和运算符，可根据应用程序的要求进行使用。
在本文中，您可以了解如何使用 Go 编程语言为 Amazon OpenSearch 构建 CRUD 应用程序。该项目包括构建个人的开发环境所需的一切，例如特定的发行版本，例如：
或者，您可以使用 Amazon OpenSearch，这是一项针对 OpenSearch 的全托管服务，让您可专注于构建应用程序，同时会照顾您的基础设施和操作。
