将 Machine Learning (ML) 与 Aurora PostgreSQL 结合使用 - Amazon Aurora
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

将 Machine Learning (ML) 与 Aurora PostgreSQL 结合使用

借助 Amazon Aurora 机器学习,您可以使用 SQL 语言向数据库应用程序添加基于机器学习的预测。Aurora 机器学习 使用 Aurora 数据库与 AWS Machine Learning (ML) 服务 SageMaker 和 Amazon Comprehend 之间的高度优化集成。

Aurora 机器学习 具有以下好处:

  • 您可以向现有的数据库应用程序添加基于 ML 的预测。您无需构建自定义集成或另外学习工具。您可以将机器学习处理作为对函数的调用直接嵌入到 SQL 查询中。

  • ML 集成是使 ML 服务能够处理事务数据的快速方法。无需将数据移出数据库即可执行机器学习操作。无需转换或重新导入机器学习操作的结果即可在数据库应用程序中使用它们。

  • 您可以使用现有的治理策略来控制谁有权访问基础数据和所生成的见解。

AWS Machine Learning 服务是在其自己的生产环境中设置和运行的托管服务。目前,Aurora 机器学习 与 Amazon Comprehend 集成以执行情绪分析,与 SageMaker 集成以执行各种 ML 算法。

有关 Amazon Comprehend 的一般信息,请参阅Amazon Comprehend。有关结合使用 Aurora 和 Amazon Comprehend 的详细信息,请参阅将 Amazon Comprehend 用于自然语言处理

有关 SageMaker 的一般信息,请参阅SageMaker。有关结合使用 Aurora 和 SageMaker 的详细信息,请参阅使用 SageMaker 运行您自己的 ML 模型

注意

Aurora 机器学习 for PostgreSQL 仅在同一 AWS 区域中将 Aurora 集群连接到 SageMaker 或 Amazon Comprehend 服务。

启用 Aurora 机器学习

Aurora 机器学习 适用于以下 Aurora PostgreSQL 版本:

  • 运行 PostgreSQL 引擎版本 10.11 的 版本 2.4 的 与 PostgreSQL 兼容的 Aurora。

  • 运行 PostgreSQL 引擎版本 11.6 及更高版本的版本 3.1 及更高版本的 与 PostgreSQL 兼容的 Aurora

有关升级较旧的 Aurora 集群的信息,请参阅升级适用于 Aurora PostgreSQL 的 PostgreSQL 数据库引擎

启用 ML 功能包括以下步骤。

设置 AWS Machine Learning 服务的 IAM 访问权限

您需要先设置 AWS Identity and Access Management (IAM) 角色,然后才能访问 SageMaker 和 Amazon Comprehend 服务。然后,将 IAM 角色添加到 Aurora PostgreSQL 集群。这些角色授权 Aurora PostgreSQL 数据库的用户访问 AWS ML 服务。

您可以使用 AWS 管理控制台自动执行 IAM 设置,如此处所示。要使用 AWS CLI 设置 IAM 访问权限,请参阅使用 AWS CLI 手动设置 SageMaker 和 Amazon Comprehend 的 IAM 角色

使用控制台自动将 Aurora 数据库集群连接到 AWS 服务

Aurora 机器学习 要求您的数据库集群使用 Amazon S3、SageMaker 和 Amazon Comprehend 的某种组合。Amazon Comprehend 用于情绪分析。SageMaker 用于各种机器学习算法。

对于 Aurora 机器学习,您仅将 Amazon S3 用于训练 SageMaker 模型。如果您还没有训练后的模型,而训练又是您的责任,则只需对 Aurora 机器学习 使用 Amazon S3。

要将数据库集群连接到这些服务,需要为每个 Amazon 服务设置 AWS Identity and Access Management (IAM) 角色。IAM 角色使数据库集群的用户可以使用相应的服务进行身份验证。

要为 SageMaker、Amazon Comprehend 或 Amazon S3 生成 IAM 角色,请对所需的每个服务重复以下步骤。

将数据库集群连接到 Amazon 服务

  1. 登录 AWS 管理控制台 并通过以下网址打开 Amazon RDS 控制台:https://console.amazonaws.cn/rds/

  2. 在导航窗格中,选择 Databases (数据库),然后选择要使用的 Aurora PostgreSQL 数据库集群。

  3. 选择 Connectivity & security (连接和安全性) 选项卡。

  4. Manage IAM roles (管理 IAM 角色) 部分中选择 Select a service to connect to this cluster (选择一个服务以连接到此集群)

  5. 从列表中选择要连接到的服务:

    • Amazon S3

    • Amazon Comprehend

    • SageMaker

  6. 选择 Connect service (连接服务)

  7. Connect cluster (连接集群) 窗口中输入特定服务所需的信息:

    • 对于 SageMaker,输入 SageMaker 终端节点的 Amazon 资源名称 (ARN)。

      SageMaker 控制台的导航窗格中选择 Endpoints (终端节点),然后复制要使用的终端节点的 ARN。有关终端节点表示内容的详细信息,请参阅在 Amazon SageMaker 托管服务上部署模型

    • 对于 Amazon Comprehend,无需指定任何附加参数。

    • 对于 Amazon S3,请输入要使用的 Amazon S3 存储桶的 ARN。

      Amazon S3 存储桶 ARN 的格式为 arn:aws:s3:::bucket_name。确保您使用的 Amazon S3 存储桶是按照训练 SageMaker 模型的要求设置。当您训练模型时,您的 Aurora 数据库集群需要权限才能将数据导出到 Amazon S3 存储桶,也需要权限才能从存储桶中导入数据。

      有关 Amazon S3 存储桶 ARN 的更多信息,请参阅 Amazon Simple Storage Service 开发人员指南 中的在策略中指定资源。 有关将 Amazon S3 存储桶与 SageMaker 结合使用的更多信息,请参阅 Amazon SageMaker 开发人员指南 中的步骤 1:创建 Amazon S3 存储桶

  8. 选择 Connect service (连接服务)

  9. Aurora 创建一个新 IAM 角色并将其添加到数据库集群的 Current IAM roles for this cluster (此集群的当前 IAM 角色) 列表中。IAM 角色的状态最初为 In progress (正在进行)。对于每个已连接的服务,将使用以下模式自动生成 IAM 角色名称:

    • Amazon S3 IAM 角色名称模式为 rds-cluster_ID-S3-role-timestamp

    • SageMaker IAM 角色名称模式为 rds-cluster_ID-SageMaker-role-timestamp

    • Amazon Comprehend IAM 角色名称模式为 rds-cluster_ID-Comprehend-role-timestamp

    Aurora 还创建新的 IAM 策略并将其附加到角色。策略名称遵循类似的命名约定,并具有时间戳。

安装适用于模型推理的 aws_ml 扩展

在创建所需的 IAM 角色并将其与 Aurora PostgreSQL 数据库集群关联后,请安装使用 SageMaker 和 Amazon Comprehend 功能的函数。aws_ml Aurora PostgreSQL 扩展提供了直接与 SageMaker 通信的 aws_sagemaker.invoke_endpoint 函数。aws_ml 扩展还提供了直接与 Amazon Comprehend 通信的 aws_comprehend.detect_sentiment 函数。

要在特定的数据库中安装这些函数,请在 psql 提示符处输入以下 SQL 命令。

psql>CREATE EXTENSION IF NOT EXISTS aws_ml CASCADE;

如果在 template1 默认数据库中创建 aws_ml 扩展,则这些函数在您创建的每个新数据库中都可用。

要验证安装,请在 psql 提示符处输入以下内容。

psql>\dx

如果您为 Amazon Comprehend 设置 IAM 角色,则可验证设置,如下所示。

SELECT sentiment FROM aws_comprehend.detect_sentiment(null, 'I like it!', 'en');

在安装 aws_ml 扩展时,将创建 aws_ml 管理角色并将其授予给 rds_superuser 角色。还将为 aws_sagemaker 服务和 aws_comprehend 服务创建独立架构。将 rds_superuser 角色设置为这两个架构的 OWNER

要让用户或角色获取对 aws_ml 扩展中的函数的访问权限,请授予这些函数的 EXECUTE 权限。如果需要,可以随后对执行权限进行 REVOK 操作。默认情况下,从 PULIC 撤销对这些架构的函数的 EXECUTE 权限。在多租户数据库配置中,要防止租户访问这些函数,请对一个或多个 ML 服务架构使用 REVOKE USAGE

有关对安装的 aws_ml 扩展函数的引用,请参阅 Aurora 机器学习 的 PostgreSQL 函数参考

将 Amazon Comprehend 用于自然语言处理

Amazon Comprehend 使用机器学习在文本中查找见解和关系。Amazon Comprehend 使用自然语言处理来提取有关文档内容的见解。它可以通过识别文档中的实体、关键短语、语言、情绪和其他常见元素生成见解。您只需很少的机器学习经验即可使用此 Aurora 机器学习 服务。

Aurora 机器学习 将 Amazon Comprehend 用于对存储在数据库中的文本进行的情绪分析。情绪 是用文本表达的意见。情绪分析可以识别情绪并对其进行分类,从而确定对某个事物(例如,某个主题或产品)的态度是积极的、消极的还是中性的。

注意

Amazon Comprehend 当前只在部分 AWS 区域中可用。要查看可以在哪些 AWS 区域内使用 Amazon Comprehend,请参阅 AWS 网站上的 AWS 区域表

例如,使用 Amazon Comprehend,您可以分析联络中心的呼入文档,以检测呼叫方情绪并更好地了解呼叫方-代理的动态。您可以在 AWS 机器学习博客上的分析联络中心呼叫帖子中找到进一步的描述。

您也可以使用单个查询将情绪分析与对数据库中其他信息的分析相结合。例如,您可以检测联络中心呼入文档的平均情绪,以了解综合了以下方面的问题:

  • 超过 30 天未解决。

  • 关于特定产品或功能。

  • 由社交媒体影响力最大的客户提出。

从 Aurora 机器学习 使用 Amazon Comprehend 就像调用 SQL 函数那样轻松。在安装 aws_ml 扩展 (安装适用于模型推理的 aws_ml 扩展) 后,它会提供 aws_comprehend.detect_sentiment 函数以通过 Amazon Comprehend 执行情绪分析。

对于您分析的每个文本片段,此函数将帮助您确定情绪和置信度。典型的 Amazon Comprehend 查询会查找情绪具有特定值(POSITIVE 或 NEGATIVE)且置信度大于特定百分比的表行。

例如,以下查询说明了如何确定数据库表 myTable.document 中文档的平均情绪。该查询只考虑评估的置信度至少为 80% 的文档。在以下示例中,英语 (en) 是情绪文本的语言。

SELECT AVG(CASE s.sentiment WHEN 'POSITIVE' then 1 WHEN 'NEGATIVE' then -1 ELSE 0 END) as avg_sentiment, COUNT(*) AS total FROM myTable, aws_comprehend.detect_sentiment (myTable.document, 'en') s WHERE s.confidence >= 0.80;

要避免针对每个表行支付多次情绪分析费用,您可以对每个行实施一次分析结果。队感兴趣的行执行此操作。在以下示例中,法语 (fr) 是情绪文本的语言。

-- *Example:* Update the sentiment and confidence of French text. -- UPDATE clinician_notes SET sentiment = (aws_comprehend.detect_sentiment (french_notes, 'fr')).sentiment, confidence = (aws_comprehend.detect_sentiment (french_notes, 'fr')).confidence WHERE clinician_notes.french_notes IS NOT NULL AND LENGTH(TRIM(clinician_notes.french_nodes)) > 0 AND clinician_notes.sentiment IS NULL;

有关优化函数调用的更多信息,请参阅Aurora 机器学习 最佳实践

有关情绪检测函数的参数和返回类型的信息,请参阅 aws_comprehend.detect_sentiment

将数据导出到 Amazon S3 以进行 SageMaker 模型训练

根据您的团队划分机器学习任务的方式,您可能无法执行模型训练。如果其他人为您提供了 SageMaker 模型,则可跳过此部分。

要训练 SageMaker 模型,请将数据导出到 Amazon S3 存储桶。SageMaker 使用 Amazon S3 存储桶在部署模型之前对其进行训练。您可以从 Aurora PostgreSQL 数据库集群中查询数据,并将数据直接保存到 Amazon S3 存储桶中存储的文本文件。然后,SageMaker 使用 Amazon S3 存储桶中的数据进行训练。有关 SageMaker 模型训练的更多信息,请参阅使用 Amazon SageMaker 训练模型

注意

在为 SageMaker 模型训练或批处理评分创建 S3 存储桶时,始终在 S3 存储桶名称中包含文本 sagemaker。有关为 SageMaker 创建 S3 存储桶的更多信息,请参阅步骤 1:创建 Amazon S3 存储桶

有关导出数据的更多信息,请参阅将数据从 Aurora PostgreSQL 数据库集群导出到 Amazon S3

使用 SageMaker 运行您自己的 ML 模型

SageMaker 是一项完全托管的机器学习服务。借助 SageMaker,数据科学家和开发人员可以构建和训练机器学习模型。随后,他们可以将模型直接部署到生产就绪的托管环境中。

SageMaker 提供了对数据源的访问权限,以便您能够在不管理服务器硬件基础设施的情况下执行探索和分析。SageMaker 还提供了通用的机器学习算法,这些算法经过了优化,可针对分布式环境中的极大数据集高效运行。借助对自带算法和框架的原生支持,SageMaker 可以提供灵活并且适合具体工作流程的分布式训练选项。

注意

目前,Aurora 机器学习 通过 text/csvContentType 值支持任何可读取和写入逗号分隔值 (CSV) 格式的 SageMaker 终端节点。目前接受此格式的内置 SageMaker 算法为 Random Cut Forest、Linear Learner 和 XGBoost。

请务必将您正在使用的模型部署到 Aurora PostgreSQL 集群所在的同一 AWS 区域中。Aurora 机器学习 始终调用 Aurora 集群所在的 AWS 区域中的 SageMaker 终端节点。

在安装 aws_ml 扩展(如安装适用于模型推理的 aws_ml 扩展中所述)时,它将提供 aws_sagemaker.invoke_endpoint 函数。可使用此函数调用 SageMaker 模型并直接从 SQL 数据库应用程序中执行模型推理。

创建用户定义的函数以调用 SageMaker 模型

创建单独的用户定义的函数来为每个 SageMaker 模型调用 aws_sagemaker.invoke_endpoint。您的用户定义的函数表示托管模型的 SageMaker 终端节点。aws_sagemaker.invoke_endpoint 函数在用户定义的函数中运行。用户定义的函数提供了许多好处:

  • 可以为 ML 模型指定其自己的名称,而不是仅为所有 ML 模型调用 aws_sagemaker.invoke_endpoint

  • 可以仅在 SQL 应用程序代码中的一个位置指定模型终端节点 URL。

  • 可以单独控制每个 ML 函数的 EXECUTE 权限。

  • 可以使用 SQL 类型声明模型输入和输出类型。SQL 强制实施传递给 ML 模型的参数的数量和类型,并在必要时执行类型转换。使用 SQL 类型还会将 SQL NULL 转换为 ML 模型预期的适当默认值。

  • 如果要更快地返回前几行,则可以减小最大批处理大小。

要指定用户定义的函数,请使用 SQL 数据定义语言 (DDL) 语句 CREATE FUNCTION。在定义函数时,您指定了以下内容:

  • 模型的输入参数。

  • 要调用的特定 SageMaker 终端节点。

  • 返回类型。

用户定义的函数在对输入参数执行模型后,将返回 SageMaker 终端节点计算的推理。以下示例为带两个输入参数的 SageMaker 模型创建用户定义的函数。

CREATE FUNCTION classify_event (IN arg1 INT, IN arg2 DATE, OUT category INT) AS $$ SELECT aws_sagemaker.invoke_endpoint ( 'sagemaker_model_endpoint_name', NULL, arg1, arg2 -- model inputs are separate arguments )::INT -- cast the output to INT $$ LANGUAGE SQL PARALLEL SAFE COST 5000;

请注意以下几点:

  • aws_sagemaker.invoke_endpoint 函数输入可以是任意数据类型的一个或多个参数。

    有关参数的更多详细信息,请参阅 aws_sagemaker.invoke_endpoint 函数参考。

  • 此示例使用 INT 输出类型。如果将输出从 varchar 类型强制转换为其他类型,则必须将其强制转换为 PostgreSQL 内置标量类型,例如 INTEGERREALFLOATNUMERIC。有关这些类型的更多信息,请参阅 PostgreSQL 文档中的数据类型

  • 指定 PARALLEL SAFE 可启用并行查询执行。有关更多信息,请参阅 利用并行查询处理

  • 指定 COST 5000 可估计函数的执行成本。使用正数来提供函数的估计执行成本,单位为 cpu_operator_cost

将数组作为输入传递到 SageMaker 模型

aws_sagemaker.invoke_endpoint 函数最多可具有 100 个输入参数,这是 PostgreSQL 函数的限制。如果 SageMaker 模型需要 100 个以上的相同类型的参数,则将模型参数作为数组传递。

以下示例创建一个用户定义的函数,该函数将数组作为输入传递到 SageMaker 回归模型。

CREATE FUNCTION regression_model (params REAL[], OUT estimate REAL) AS $$ SELECT aws_sagemaker.invoke_endpoint ( 'sagemaker_model_endpoint_name', NULL, params -- model input parameters as an array )::REAL -- cast output to REAL $$ LANGUAGE SQL PARALLEL SAFE COST 5000;

在调用 SageMaker 模型时指定批处理大小

以下示例为 SageMaker 模型创建一个用户定义的函数,该函数将批处理大小的默认值设置为 NULL。此外,在您调用该函数时,它可让您提供其他批处理大小。

CREATE FUNCTION classify_event ( IN event_type INT, IN event_day DATE, IN amount REAL, -- model inputs max_rows_per_batch INT DEFAULT NULL, -- optional batch size limit OUT category INT) -- model output AS $$ SELECT aws_sagemaker.invoke_endpoint ( 'sagemaker_model_endpoint_name', max_rows_per_batch, event_type, event_day, COALESCE(amount, 0.0) )::INT -- casts output to type INT $$ LANGUAGE SQL PARALLEL SAFE COST 5000;

请注意以下几点:

  • 使用可选的 max_rows_per_batch 参数可控制批处理模式函数调用的行数。如果您使用 NULL 值,则查询优化程序会自动选择最大批处理大小。有关更多信息,请参阅 优化 Aurora 机器学习 函数调用的批处理模式执行

  • 默认情况下,作为参数值传递的 NULL 将转换为空字符串,然后再传递到 SageMaker。对于此示例,输入具有不同的类型。

  • 如果您具有非文本输入,或具有需要默认为空字符串以外的某个值的文本输入,请使用 COALESCE 语句。使用 COALESCE 可在对 aws_sagemaker.invoke_endpoint 的调用中将 NULL 转换为所需的空替换值。对于此示例中的 amount 参数,NULL 值将转换为 0.0。

调用带多个输出的 SageMaker 模型

以下示例为返回多个输出的 SageMaker 模型创建一个用户定义的函数。您的函数需要将 aws_sagemaker.invoke_endpoint 函数的输出强制转换为相应的数据类型。例如,可以将内置 PostgreSQL 点类型用于 (x,y) 对或用户定义的复合类型。

此用户定义的函数将返回一个模型中的值,该模型通过对输出使用复合类型来返回多个输出。

CREATE TYPE company_forecasts AS ( six_month_estimated_return real, one_year_bankruptcy_probability float); CREATE FUNCTION analyze_company ( IN free_cash_flow NUMERIC(18, 6), IN debt NUMERIC(18,6), IN max_rows_per_batch INT DEFAULT NULL, OUT prediction company_forecasts) AS $$ SELECT (aws_sagemaker.invoke_endpoint( 'endpt_name', max_rows_per_batch, free_cash_flow, debt))::company_forecasts; $$ LANGUAGE SQL PARALLEL SAFE COST 5000;

对于复合类型,按照字段在模型输出中的显示顺序来使用字段,并将 aws_sagemaker.invoke_endpoint 的输出强制转换为复合类型。调用方可以按名称或使用 PostgreSQL“.*”表示法来提取各个字段。

Aurora 机器学习 最佳实践

aws_ml 函数调用中的大部分工作都在外部 Aurora 机器学习 服务中进行。通过这种分离,您可以独立于 Aurora 集群扩展机器学习服务的资源。在 Aurora 中,您主要关注的是使用户定义的函数调用本身尽可能高效。您可以从 Aurora 集群中影响的一些方面包括:

  • 适用于对 aws_ml 函数的调用的 max_rows_per_batch 设置。

  • 数据库实例的虚拟 CPU 的数量,它确定数据库在执行 ML 函数时可能使用的最大并行度。

  • 用于控制并行查询执行的 PostgreSQL 参数。

优化 Aurora 机器学习 函数调用的批处理模式执行

通常,PostgreSQL 一次运行一行函数。Aurora 机器学习 可通过使用称作批处理模式执行 的方法,针对多个行将对外部 Aurora 机器学习 服务的调用合并到批处理中来最大程度地减少此开销。在批处理模式中,Aurora 机器学习 将接收对一批输入行的响应,然后将响应传回正在运行的查询(一次一行)。此优化将提高 Aurora 查询的吞吐量,而不限制 PostgreSQL 查询优化程序。

如果从 SELECT 列表、WHERE 子句或 HAVING 子句引用函数,则 Aurora 会自动使用批处理模式。请注意,顶级的简单 CASE 表达式符合批处理模式执行的条件。顶级搜索的 CASE 表达式符合批处理模式执行的条件,前提是第一个 WHEN 子句是具有批处理模式函数调用的简单谓词。

您的用户定义的函数必须是一个 LANGUAGE SQL 函数,并且应指定 PARALLEL SAFECOST 5000

从 SELECT 语句到 FROM 子句的函数迁移

通常,Aurora 会自动将符合批处理模式执行条件的 aws_ml 函数迁移到 FROM 子句。

可以在每个查询级别上手动检查合格的批处理模式函数到 FROM 子句的迁移。为此,您可以使用 EXPLAIN 语句(以及 ANALYSE 和 VEROSE),并在每个批处理模式 Function Scan 下查找“批处理”信息。您也可以在不运行查询的情况下使用 EXPLAIN(以及 VERBOSE)。然后,您可以观察对函数的调用是否在原始语句中未指定的嵌套循环联接下显示为 Function Scan

在以下示例中,计划中存在嵌套循环联接运算符表明 Aurora 已迁移 anomaly_score 函数。它已将此函数从 SELECT 列表迁移到 FROM 子句,其中它符合批处理模式执行的条件。

EXPLAIN (VERBOSE, COSTS false) SELECT anomaly_score(ts.R.description) from ts.R; QUERY PLAN ------------------------------------------------------------- Nested Loop Output: anomaly_score((r.description)::text) -> Seq Scan on ts.r Output: r.id, r.description, r.score -> Function Scan on public.anomaly_score Output: anomaly_score.anomaly_score Function Call: anomaly_score((r.description)::text)

要禁用批处理模式执行,请将 apg_enable_function_migration 参数设置为 false。这可阻止 aws_ml 函数从 SELECT 迁移到 FROM 子句。下面演示了如何操作。

SET apg_enable_function_migration = false;

apg_enable_function_migration 参数是一个由查询计划管理的 Aurora PostgreSQL apg_plan_mgmt 扩展识别的 Grand Unified Configuration (GUC) 参数。要在会话中禁用函数迁移,请使用查询计划管理将生成的计划另存为 approved 计划。在运行时,查询计划管理会使用其 apg_enable_function_migration 设置强制执行 approved 计划。无论 apg_enable_function_migration GUC 参数设置如何,此强制执行都会发生。有关更多信息,请参阅管理 Aurora PostgreSQL 的查询执行计划

使用 max_rows_per_batch 参数

aws_sagemaker.invoke_endpointaws_comprehend.detect_sentiment 函数的 max_rows_per_batch 参数将影响传输到 Aurora 机器学习 服务的行数。用户定义的函数处理的数据集越大,批处理大小就可以越大。

批处理模式函数通过构建批量行来将 Aurora 机器学习 函数调用的成本分散在大量行上,从而提高效率。但是,如果 SELECT 语句因 LIMIT 子句而提前完成,则可以在比查询使用的行更多的行上构建批处理。此方法可能会导致您的 AWS 账户产生额外费用。要获得批处理模式执行的好处,并避免构建过大的批处理,请在函数调用中使用较小的 max_rows_per_batch 参数值。

如果您执行使用批处理模式执行的查询的 EXPLAINVERBOSEANALYZE),则嵌套循环联接下方将显示 FunctionScan 运算符。EXPLAIN 报告的循环数指明了已从 FunctionScan 运算符提取某个行的次数。如果语句使用了 LIMIT 子句,则提取次数是一致的。要优化批处理的大小,请将 max_rows_per_batch 参数设置为此值。但是,如果批处理模式函数是在 WHERE 子句或 HAVING 子句的谓词中引用的,则您可能无法预先知道提取次数。在此情况下,使用循环作为指南,并尝试使用 max_rows_per_batch 查找可优化性能的设置。

验证批处理模式执行

要查看某个函数是否已在批处理模式中执行,请使用 EXPLAIN ANALYZE。如果使用了批处理模式执行,则查询计划将在“批处理”部分中包含此信息。

EXPLAIN ANALYZE SELECT user-defined-function(); Batch Processing: num batches=1 avg/min/max batch size=3333.000/3333.000/3333.000 avg/min/max batch call time=146.273/146.273/146.273

在此示例中,有 1 个批处理包含 3333 个行,其处理时间为 146.273 毫秒。“批处理”部分显示以下内容:

  • 针对此函数扫描操作的批处理的数目

  • 批处理大小的平均值、最小值和最大值

  • 批处理执行时间的平均值、最小值和最大值

最后一个批处理通常会小于其余的批处理,这通常会导致最小批处理大小比平均值小得多。

要更快地返回前几行,请将 max_rows_per_batch 参数设置为较小的值。

要在用户定义的函数中使用 LIMIT 时减少对 ML 服务的批处理模式调用的次数,请将 max_rows_per_batch 参数设置为较小的值。

利用并行查询处理

要在处理大量行时显著提高性能,可以将并行查询处理与批处理模式处理结合使用。您可以对 SELECTCREATE TABLE AS SELECTCREATE MATERIALIZED VIEW 语句使用并行查询处理。

注意

PostgreSQL 尚不支持对数据操作语言 (DML) 语句进行并行查询。

并行查询处理可同时在数据库和 ML 服务内进行。数据库实例类中的核心数将限制查询执行期间可使用的并行度。数据库服务器可以构建并行查询执行计划,来将任务划分到一组并行工作线程中。随后,所有这些工作线程都能构建包含数万个(或每个服务允许的数量)行的批处理的请求。

来自所有并行工作线程的批处理请求将发送到 AWS 服务的终端节点(例如 SageMaker)。因此,AWS 服务终端节点后面的实例的数量和类型也会限制可有效利用的并行度。甚至双核心实例类也能从并行查询处理中获得大量好处。但是,要充分利用更高 K 度的并行性,您需要一个至少具有 K 个核心的数据库实例类。您还需要配置 AWS 服务,使其能够并行处理 K 批处理请求。对于 SageMaker,您需要为 ML 模型配置 SageMaker 终端节点,以具有性能足够高的实例类的 K 个初始实例。

要利用并行查询处理,您可以设置包含了计划传递的数据的表的 parallel_workers 存储参数。将 parallel_workers 设置为批处理模式函数,例如 aws_comprehend.detect_sentiment。如果优化程序选择并行查询计划,则可以批量调用和并行调用 AWS ML 服务。可以将以下参数与 aws_comprehend.detect_sentiment 函数结合使用来获取具有四向并行度的计划。

-- If you change either of the following two parameters, you must restart -- the database instance for the changes to take effect. -- -- SET max_worker_processes to 8; -- default value is 8 -- SET max_parallel_workers to 8; -- not greater than max_worker_processes -- SET max_parallel_workers_per_gather to 4; -- not greater than max_parallel_workers -- You can set the parallel_workers storage parameter on the table that the data -- for the ML function is coming from in order to manually override the degree of -- parallelism that would otherwise be chosen by the query optimizer -- ALTER TABLE yourTable SET (parallel_workers = 4); -- Example query to exploit both batch mode execution and parallel query -- EXPLAIN (verbose, analyze, buffers, hashes) SELECT aws_comprehend.detect_sentiment(description, 'en')).* FROM yourTable WHERE id < 100;

有关控制并行查询的更多信息,请参阅 PostgreSQL 文档中的并行计划

使用具体化视图和具体化列

当您从数据库中调用 AWS 服务(例如 SageMaker 或 Amazon Comprehend)时,将根据该服务的定价政策向您的账户收费。要最大程度地减少向您的账户收取的费用,您可以将调用 AWS 服务的结果具体化为一个具体化列,以便针对每个输入行调用 AWS 服务的次数不会超过一次。如果需要,您可以添加 materializedAt 时间戳列来记录将列具体化的时间。

通用的单行 INSERT 语句的延迟通常比调用批处理模式函数的延迟短得多。因此,如果您为应用程序执行的每个单行 INSERT 调用批处理模式函数,则可能无法满足应用程序的延迟要求。要将调用 AWS 服务的结果具体化为一个具体化列,高性能的应用程序通常需要填充具体化列。为此,它们会定期发布同时在大批行上运行的 UPDATE 语句。

UPDATE 接受可能影响正在运行的应用程序的行级锁定。因此,您可能需要使用 SELECT ... FOR UPDATE SKIP LOCKED,或使用 MATERIALIZED VIEW

对大量行实时运行的分析查询可以将批处理模式具体化与实时处理相结合。为此,这些查询会将预具体化结果的 UNION ALL 与对尚没有具体化结果的行执行的查询相结合。在某些情况下,多个位置需要此类 UNION ALL,否则查询将由由第三方应用程序生成。如果是这样,您可以创建一个 VIEW 来封装 UNION ALL 操作,以便不向 SQL 应用程序的其余部分公开此详细信息。

您可以使用具体化视图在即时快照中具体化任意 SELECT 语句的结果。您还可以将来随时使用它刷新具体化视图。目前,PostgreSQL 不支持增量刷新,因此每次刷新具体化视图时,都会完全重新计算具体化视图。

可以使用 CONCURRENTLY 选项刷新具体化视图,这将在不使用排他锁的情况下更新具体化视图的内容。通过执行此操作,使 SQL 应用程序能够在刷新具体化视图时从该视图中进行读取。

监控 Aurora 机器学习

要监控 aws_ml 包中的函数,请设置 track_functions 参数,然后查询 PostgreSQL pg_stat_user_functions 视图

有关监控从 Aurora 机器学习 函数调用的 SageMaker 操作的性能的信息,请参阅监控 Amazon SageMaker

要在会话级别设置 track_functions,请执行以下操作。

SET track_functions = 'all';

使用下列值之一:

  • all – 跟踪未内联放置的 C 语言函数和 SQL 语言函数。要跟踪 aws_ml 函数,请使用 all,因为这些函数是在 C 中实施的。

  • pl – 仅跟踪程序语言函数。

  • none – 禁用函数统计数据跟踪。

在启用 track_functions 并运行用户定义的 ML 函数后,查询 pg_stat_user_functions 视图以获取信息。该视图包括每个函数的 callstotal_timeself_time 的数量。要查看 aws_sagemaker.invoke_endpointaws_comprehend.detect_sentiment 函数的统计数据,请按以 aws_ 开头的架构名称筛选结果。

run your statement SELECT * FROM pg_stat_user_functions WHERE schemaname LIKE 'aws_%'; SELECT pg_stat_reset(); -- To clear statistics

要查找调用 aws_sagemaker.invoke_endpoint 函数的 SQL 函数的名称,请在 PostgreSQL pg_proc 目录 表中查询函数的源代码。

SELECT proname FROM pg_proc WHERE prosrc LIKE '%invoke_endpoint%';

使用查询计划管理监控 ML 函数

如果您已使用查询计划管理的 apg_plan_mgmt 扩展捕获计划,则可以搜索工作负载中所有引用这些函数名称的语句。在搜索中,您可以检查 plan_outline 以了解是否使用了批处理模式执行。您也可以列出语句统计数据,例如执行时间和计划成本。使用批处理模式函数扫描的计划在计划大纲中包含 FuncScan 运算符。未作为联接运行的函数不包含 FuncScan 运算符。

有关查询计划管理的更多信息,请参阅 管理 Aurora PostgreSQL 的查询执行计划

要查找对未使用批处理模式的 aws_sagemaker.invoke_endpoint 函数的调用,请使用以下语句。

\dx apg_plan_mgmt SELECT sql_hash, plan_hash, status, environment_variables, sql_text::varchar(50), plan_outline FROM pg_proc, apg_plan_mgmt.dba_plans WHERE prosrc LIKE '%invoke_endpoint%' AND sql_text LIKE '%' || proname || '%' AND plan_outline NOT LIKE '%"FuncScan"%';

前面的示例搜索工作负载中所有调用 SQL 函数的语句,这些函数反过来将调用 aws_sagemaker.invoke_endpoint 函数。

要获取其中的各个语句的详细运行时统计数据,请调用 apg_plan_mgmt.get_explain_stmt 函数。

SELECT apg_plan_mgmt.get_explain_stmt(sql_hash, plan_hash, 'analyze,verbose,buffers') FROM pg_proc, apg_plan_mgmt.dba_plans WHERE prosrc LIKE '%invoke_endpoint%' AND sql_text LIKE '%' || proname || '%' AND plan_outline NOT LIKE '%"FuncScan"%';

Aurora 机器学习 的 PostgreSQL 函数参考

aws_comprehend.detect_sentiment

使用 Amazon Comprehend 执行情绪分析。有关使用情况的更多信息,请参阅 将 Amazon Comprehend 用于自然语言处理

语法

aws_comprehend.detect_sentiment ( IN input_text varchar, IN language_code varchar, IN max_rows_per_batch int, OUT sentiment varchar, OUT confidence real) )

输入参数

input_text

要从中检测情绪的文本。

language_code

input_text 的语言。有关有效值,请参阅 Amazon Comprehend 中支持的语言

max_rows_per_batch

批处理模式处理的每个批处理的最大行数。有关更多信息,请参阅 优化 Aurora 机器学习 函数调用的批处理模式执行

输出参数

情绪

文本的情绪。有效值为:POSITIVE、NEGATIVE、NEUTRAL 或 MIXED。

confidence

sentiment 值中的置信度。1.0(表示 100%)到 0.0(表示 0%)的值范围。

aws_sagemaker.invoke_endpoint

在训练一个模型并使用 SageMaker 服务将该模型部署到生产中后,客户端应用程序将使用 aws_sagemaker.invoke_endpoint 函数从该模型中得到推论。模型必须托管于指定的终端节点,并且必须位于数据库实例所在的同一 AWS 区域中。有关使用情况的更多信息,请参阅 使用 SageMaker 运行您自己的 ML 模型

语法

aws_sagemaker.invoke_endpoint( IN endpoint_name varchar, IN max_rows_per_batch int, VARIADIC model_input "any", OUT model_output varchar )

输入参数

endpoint_name

独立于 AWS 区域的终端节点 URL。

max_rows_per_batch

批处理模式处理的每个批处理的最大行数。有关更多信息,请参阅 优化 Aurora 机器学习 函数调用的批处理模式执行

model_input

适用于 ML 模型的一个或多个输入参数。它们可以是任意数据类型。

PostgreSQL 允许您为一个函数指定最多 100 个输入参数。数组数据类型必须是一维的,但可以包含 SageMaker 模型期望的尽可能多的元素。SageMaker 模型的输入数仅受 SageMaker 5 MB 消息大小限制的约束。

输出参数

model_output

SageMaker 模型的输出参数(文本形式)。

使用说明

aws_sagemaker.invoke_endpoint 函数仅连接到同一 AWS 区域中的模型终端节点。如果您的数据库实例在多个 AWS 区域中拥有副本,请始终将每个 Amazon SageMaker 模型部署到所有这些 AWS 区域。

aws_sagemaker.invoke_endpoint 的调用将通过数据库实例的 SageMaker IAM 角色进行身份验证。

SageMaker 模型终端节点的作用域限于单个账户,并且不是公有的。endpoint_name URL 不包含账户 ID。SageMaker 通过由数据库实例的 SageMaker IAM 角色提供的身份验证令牌确定账户 ID。

使用 AWS CLI 手动设置 SageMaker 和 Amazon Comprehend 的 IAM 角色

注意

如果您使用 AWS 管理控制台,则 AWS 会自动为您进行 IAM 设置。在此情况下,您可以跳过以下信息,并按照使用控制台自动将 Aurora 数据库集群连接到 AWS 服务中的过程操作。

使用 AWS CLI 或 RDS API 为 SageMaker 或 Amazon Comprehend 设置 IAM 角色的过程包括以下步骤:

  1. 创建一个 IAM 策略,以指定 Aurora PostgreSQL 集群可以调用的 SageMaker 终端节点,或启用对 Amazon Comprehend 的访问。

  2. 创建一个 IAM 角色,以允许您的 Aurora PostgreSQL 数据库集群访问 AWS ML 服务。此外,将之前创建的 IAM 策略附加到此处创建的 IAM 角色。

  3. 将您之前创建的 IAM 角色关联到 Aurora PostgreSQL 数据库集群,以允许访问 AWS ML 服务。

使用 AWS CLI 创建 IAM 策略以访问 SageMaker

注意

Aurora 会自动为您创建 IAM 策略。您可以跳过以下信息,并使用使用控制台自动将 Aurora 数据库集群连接到 AWS 服务中的过程。

以下策略添加 Aurora PostgreSQL 代表您调用 SageMaker 函数所需的权限。可以在一个策略中指定需要数据库应用程序从 Aurora PostgreSQL 集群访问的所有 SageMaker 终端节点。

注意

该策略允许您为 SageMaker 终端节点指定 AWS 区域。但是,Aurora PostgreSQL 集群只能调用部署在与集群所在的同一 AWS 区域中的 SageMaker 模型。

{ "Version": "2012-10-17", "Statement": [ { "Sid": "AllowAuroraToInvokeRCFEndPoint", "Effect": "Allow", "Action": "sagemaker:InvokeEndpoint", "Resource": "arn:aws:sagemaker:region:123456789012:endpoint/endpointName" } ] }

以下 AWS CLI 命令使用这些选项创建一个 IAM 策略。

aws iam create-policy --policy-name policy_name --policy-document '{ "Version": "2012-10-17", "Statement": [ { "Sid": "AllowAuroraToInvokeRCFEndPoint", "Effect": "Allow", "Action": "sagemaker:InvokeEndpoint", "Resource": "arn:aws:sagemaker:region:123456789012:endpoint/endpointName" } ] }'

有关下一步,请参阅创建 IAM 角色以访问 SageMaker 和 Amazon Comprehend

使用 AWS CLI 创建 IAM 策略以访问 Amazon Comprehend

注意

Aurora 会自动为您创建 IAM 策略。您可以跳过以下信息,并使用使用控制台自动将 Aurora 数据库集群连接到 AWS 服务中的过程。

以下策略添加 Aurora PostgreSQL 代表您调用 AWS Amazon Comprehend 所需的权限。

{ "Version": "2012-10-17", "Statement": [ { "Sid": "AllowAuroraToInvokeComprehendDetectSentiment", "Effect": "Allow", "Action": [ "comprehend:DetectSentiment", "comprehend:BatchDetectSentiment" ], "Resource": "*" } ] }

创建 IAM 策略来授予对 Amazon Comprehend 的访问权限

  1. 打开 IAM 管理控制台

  2. 在导航窗格中,选择 Policies

  3. 选择 Create policy

  4. Visual editor (可视化编辑器) 选项卡上,选择 Choose a service (选择服务),然后选择 Comprehend

  5. 对于 Actions (操作),请选择 DetectSentimentBatchDetectSentiment

  6. 选择查看策略

  7. 对于 Name (名称),请输入 IAM 策略名称。在创建 IAM 角色与 Aurora 数据库集群关联时,需要使用此名称。您也可以添加可选的 Description (描述) 值。

  8. 选择 Create policy

有关下一步,请参阅创建 IAM 角色以访问 SageMaker 和 Amazon Comprehend

创建 IAM 角色以访问 SageMaker 和 Amazon Comprehend

注意

Aurora 会自动为您创建 IAM 角色。您可以跳过以下信息,并使用使用控制台自动将 Aurora 数据库集群连接到 AWS 服务中的过程。

创建 IAM 策略后,请创建一个 IAM 角色,Aurora PostgreSQL 集群可以代入该角色以便数据库用户能够访问 ML 服务。要创建 IAM 角色,请执行创建角色以向 IAM 用户委派权限中描述的步骤。

将上述策略附加到您创建的 IAM 角色。有关更多信息,请参阅 将 IAM 策略附加到 IAM 用户或角色

有关 IAM 角色的更多信息,请参阅 AWS Identity and Access Management 用户指南中的 IAM 角色

有关下一步,请参阅使用 AWS CLI 将 IAM 角色与 Aurora PostgreSQL 数据库集群关联

使用 AWS CLI 将 IAM 角色与 Aurora PostgreSQL 数据库集群关联

注意

Aurora 会自动为您将 IAM 角色与数据库集群关联。您可以跳过以下信息,并使用使用控制台自动将 Aurora 数据库集群连接到 AWS 服务中的过程。

设置 IAM 访问的最后一个过程是,将 IAM 角色及其 IAM 策略与您的 Aurora PostgreSQL 数据库集群关联。执行以下操作:

  1. 将角色添加到数据库集群的关联角色列表中。

    要将角色与数据库群集相关联,请使用 AWS 管理控制台或 add-role-to-db-cluster AWS CLI 命令。

    • 使用控制台为 PostgreSQL 数据库集群添加 IAM 角色

      1. 通过以下网址登录 AWS 管理控制台并打开 Amazon RDS 控制台:https://console.aws.amazon.com/rds/

      2. 选择 PostgreSQL 数据库集群名称以显示其详细信息。

      3. Connectivity & security (连接性和安全性) 选项卡上的 Manage IAM roles (管理 IAM 角色) 部分中,在 Add IAM roles to this cluster (向此集群实例添加 IAM 角色) 下选择要添加的角色。

      4. Feature (功能) 下,选择 SageMakerComprehend

      5. 选择 Add role (添加角色)

    • 使用 CLI 为 PostgreSQL 数据库实例添加 IAM 角色

      使用以下命令将角色添加到名为 my-db-cluster 的 PostgreSQL 数据库集群中。将 your-role-arn 替换为您在上一步中记下的角色 ARN。对于 --feature-name 选项的值,请使用 SageMakerComprehends3Export,具体取决于要使用的服务。

      对于 Linux、macOS 或 Unix:

      aws rds add-role-to-db-cluster \ --db-cluster-identifier my-db-cluster \ --feature-name external-service \ --role-arn your-role-arn \ --region your-region

      对于 Windows:

      aws rds add-role-to-db-cluster ^ --db-cluster-identifier my-db-cluster ^ --feature-name external-service ^ --role-arn your-role-arn ^ --region your-region
  2. 将每个 AWS ML 服务的集群级参数设置为关联的 IAM 角色的 ARN。

    根据要在 Aurora 集群中使用的 AWS ML 服务来使用 electroencephalographic 和/或 miscomprehended 参数。

    集群级别的参数分成数据库集群参数组。要设置上述集群参数,请使用现有的自定义数据库集群组或创建一个新的集群组。要创建新的数据库集群参数组,请从 AWS CLI 中调用 create-db-cluster-parameter-group 命令,例如:

    aws rds create-db-cluster-parameter-group --db-cluster-parameter-group-name AllowAWSAccessToExternalServices \ --db-parameter-group-family aurora-postgresql-group --description "Allow access to Amazon S3, Amazon SageMaker, and Amazon Comprehend"

    在数据库集群参数组中设置相应的集群级参数以及相关的 IAM 角色 ARN 值。执行以下操作。

    aws rds modify-db-cluster-parameter-group \ --db-cluster-parameter-group-name AllowAWSAccessToExternalServices \ --parameters "ParameterName=aws_default_s3_role,ParameterValue=arn:aws:iam::123456789012:role/AllowAuroraS3Role,ApplyMethod=pending-reboot" \ --parameters "ParameterName=aws_default_sagemaker_role,ParameterValue=arn:aws:iam::123456789012:role/AllowAuroraSageMakerRole,ApplyMethod=pending-reboot" \ --parameters "ParameterName=aws_default_comprehend_role,ParameterValue=arn:aws:iam::123456789012:role/AllowAuroraComprehendRole,ApplyMethod=pending-reboot"

    修改数据库集群以使用新的数据库集群参数组。然后,重新启动集群。下面演示了如何操作。

    aws rds modify-db-cluster --db-cluster-identifier your_cluster_id --db-cluster-parameter-group-nameAllowAWSAccessToExternalServices aws rds failover-db-cluster --db-cluster-identifier your_cluster_id

在重新启动实例后,您的 IAM 角色将与数据库集群关联。