使用 SQL 在 Amazon OpenSearch 中搜索和分析数据

什么是 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），我们会在下一步中用到。

column field 对于两者中最基础的概念——数据，存储在各种数据类型的 named（命名） 条目中，每个条目包含一个值。在 SQL 中，此类条目叫做 column（列） 而在 OpenSearch 叫做 field（字段） 。注意，在 OpenSearch 中，一个字段可以包含多个同类型值（本质上是一个列表），而在 SQL 中，一个 column（列） 可以只包含某类型的一个值。OpenSearch SQL 将尽可能保留 SQL 语义，并根据查询拒绝返回具有多个值的字段。 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 中的跨集群搜索可让您跨多个相互连接的域进行查询和聚合。使用多个较小的域而不是单个大域通常更有意义，特别是在运行的工作负载类型不同的情况下。

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]

Code snippet copied

我们希望使用复杂的过滤器运行 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

Code snippet copied

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

Code snippet copied

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);

Code snippet copied

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;

Code snippet copied

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

Code snippet copied

SELECT manufacturer, sum(total_quantity) tot_qty
FROM opensearch_dashboards_sample_data_ecommerce
group by manufacturer
having tot_qty >2000

Code snippet copied

4. DELETE 语句会删除满足 WHERE 子句中谓词的文档。如果不指定 WHERE 子句，则所有文档都会被删除。这个语句在默认情况下是禁用的。要启用 SQL 中的 DELETE 功能，需要使用 Dev Tools（开发工具）中的控制台发送以下请求来更新配置。

点击汉堡包图标（左上角）和“Dev Tools（开发工具）”菜单选项（底部），如以下屏幕截图所示。

{"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"}]}]} Code snippet copied

插入到账户中的语句：

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":"[email protected]","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":"[email protected]","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":"[email protected]","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":"[email protected]","city":"Orick","state":"MD"}

Code snippet copied

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

Code snippet copied

2. 使用交叉连接（也称为笛卡尔连接）将第一个索引中的每个文档与第二个索引中的每个文档组合起来。结果集就是者两个索引中文档的笛卡尔积。此操作类似于没有 ON 子句指定连接条件的内连接。

注意：对两个中大型的索引执行交叉连接是存在风险的。可能会触发一个断路器来终止查询以避免内存不足。

SELECT
 a.account_number, a.firstname, a.lastname,
 e.id, e.name
FROM accounts a
JOIN employees_nested e

Code snippet copied

上述结果包含包含“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')

Code snippet copied

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)

Code snippet copied

以下示例使用 SCORE 函数来提高文档的评分（score）：

SELECT 
 account_number, 
 address, 
 _score
 accounts
WHERE 
 SCORE(MATCH_QUERY(address, 'Lane'), 0.5) OR
 SCORE(MATCH_QUERY(address, 'Street'), 100)
ORDER BY 
 _score

Code snippet copied

结果包含带相应评分的匹配项：

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,"
          Code snippet copied 
        可以使用用逗号分隔的列表查询多个索引： 
        POST _plugins/_sql
 "query": "SELECT * FROM my-index1,myindex2,myindex3 LIMIT 50"
          Code snippet copied 
        还可以使用通配符表达式指定索引模式： 
        POST _plugins/_sql
 "query": "SELECT * FROM my-index* LIMIT 50"
          Code snippet copied 
        要在命令行中运行上述查询，请使用 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"}' 
          Code snippet copied 
        您可以将响应格式 指定为 JDBC、标准 OpenSearch JSON、CSV 或 RAW 格式。默认情况下，查询会返回 JDBC 格式的数据。以下查询可将格式设置为 JSON： 
        POST _plugins/_sql?format=json
"query": "SELECT * FROM my-index LIMIT 50"
          Code snippet copied 
        使用 REST API 通过 SQL 在 OpenSearch 中查询数据的另一种方法是使用以下格式将 HTTP 请求发送到 _sql： 
        POST domain-endpoint/_plugins/_sql
 "query": "SELECT * FROM my-index LIMIT 50"
          Code snippet copied 
        例如，使用开发工具控制台，再运行以下 SQL 查询（结果如下所示）： 
        通过 API 进行 SQL 查询 
        POST /_plugins/_sql
 "query" : "SELECT * FROM accounts"
          Code snippet copied 
 "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": [
 "Nanette",
 "789 Madison Street",
 32838,
 "Nogal",
 "Quility",
 "VA",
 "[email protected]",
 "Bates"
 "Hattie",
 "671 Bristol Street",
 5686,
 "Dante",
 "Netagy",
 "TN",
 "[email protected]",
 "Bond"
 "Amber",
 "880 Holmes Lane",
 39225,
 "Brogan",
 "Pyrami",
 "IL",
 "[email protected]",
 "Duke"
 "Dale",
 "467 Hutchinson Court",
 4180,
 "Orick",
 null,
 "MD",
 "[email protected]",
 "Adams"
 "total": 4,
 "size": 4,
 "status": 200
          Code snippet copied 
        将 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": {
          Code snippet copied 
        一个查询可以由许多查询子句组成。您可以组合查询子句形成复杂的查询。一般来说，查询可以分为两类，即叶子查询和复合查询： 
        复合查询：复合查询充当多个叶子查询或复合查询子句的包装器，以组合查询结果或修改查询行为。复合查询包括布尔查询、最大析取查询、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 应用程序。该项目包括构建个人的开发环境所需的一切，例如特定的发行版本，例如： 
         Tarball 
         Debian 
         Windows 
         Docker 
         Ansible Playbook。 
        或者，您可以使用 Amazon OpenSearch，这是一项针对 OpenSearch 的全托管服务，让您可专注于构建应用程序，同时会照顾您的基础设施和操作。 
            步骤 1 - 创建 Amazon OpenSearch Service 域 
            为了体验在 OpenSearch 中使用 SQL，我们会创建一个 OpenSearch 服务域，也就是 OpenSearch 集群。 
            步骤 2 - 将示例数据引入 OpenSearch 域 
            这一步包括将示例数据引入 OpenSearch，这样您就可以在示例数据上测试示例 SQL 查询。 
            步骤 3 - 运行基本 SQL 查询 
            我们从运行一组基本 SQL 查询开始，帮助您理解关键概念。 
            步骤 4 - 对多个索引或表进行复杂的 SQL 查询 
            我们已经介绍了 SQL 查询的基础知识，在这一步我们会运行更复杂的 SQL 查询来理解其中的工作原理。 
            步骤 5 - 使用 SQL 函数 
            这一步中，我们将深入探究 OpenSearch 提供的多样化 SQL 函数，这将有助于应用程序开发者充分发掘其搜索与分析的强大功能。 
       AWS 对 Internet Explorer 的支持将于 07/31/2022 结束。受支持的浏览器包括 Chrome、Firefox、Edge 和 Safari。 
      了解详情 »

推荐文章

虚心的牛肉面 · 装甲掷弹兵：台湾军队空防能力如何？20年前就看出来了_手机新浪网
2 月前

被表白的楼梯 · 人民的名义：钟小艾是什么来头？侯亮平攀上高枝，成为了乘龙快婿 ...
3 月前

兴奋的脆皮肠 · 新加坡国立大学（NUS）招生信息网-National University of Singapore
5 月前

爱吹牛的水煮肉 · 虽然华为Nova9搭载麒麟990E5G，但还是非常值得期待的_手机搜狐网
5 月前

小胡子的李子 · 属于我们的婚礼蛋仔_属于我们的婚礼英文_梦想中属于我们的婚礼-抖音
1 年前