将 Aurora PostgreSQL 扩展与 Babelfish 搭配使用
Aurora PostgreSQL 提供了与其他 Amazon 服务搭配使用的扩展。这些是支持各种使用案例的可选扩展,例如将 Amazon S3 与数据库集群搭配使用来导入或导出数据。
要将数据从 Amazon S3 桶导入 Babelfish 数据库集群,可以设置
aws_s3
Aurora PostgreSQL 扩展。此扩展还允许将数据从 Aurora PostgreSQL 数据库集群导出到 Amazon S3 存储桶。Amazon Lambda 是一项计算服务,可使您无需调配或管理服务器即可运行代码。您可以使用 Lambda 函数完成许多任务,例如处理来自数据库实例的事件通知。若要了解 Lambda 的更多信息,请参阅《Amazon Lambda 开发人员指南》中的什么是 Amazon Lambda? 要从 Babelfish 数据库集群调用 Lambda 函数,您可以设置
aws_lambda
Aurora PostgreSQL 扩展。
要为 Babelfish 集群设置这些扩展,首先需要向内部 Babelfish 用户授予加载扩展的权限。授予权限后,您就可以加载 Aurora PostgreSQL 扩展程序。
在 Babelfish 数据库集群中启用 Aurora PostgreSQL 扩展
必须向 Babelfish 数据库集群授予所需权限,才能加载 aws_s3
或者 aws_lambda
扩展。
以下过程使用 psql
PostgreSQL 命令行工具连接到该数据库集群。有关更多信息,请参阅使用 psql 连接到数据库集群。您还可以使用 pgAdmin。有关详细信息,请参阅 使用 pgAdmin 连接到数据库集群。
此过程将先后加载 aws_s3
和 aws_lambda
。如果只想使用其中一个扩展,则无需同时加载两个扩展。各种情况都需要 aws_commons
扩展,因此会默认加载该扩展,如输出中所示。
使用 Aurora PostgreSQL 扩展的权限设置 Babelfish 数据库集群
连接到 Babelfish 数据库集群。使用创建 Babelfish 数据库集群时指定的“主”用户名 (-U)。默认名称 (
postgres
) 如示例所示。对于 Linux、macOS 或 Unix:
psql -h
your-Babelfish.cluster.444455556666-us-east-1.rds.amazonaws.com
\ -U postgres \ -d babelfish_db \ -p 5432对于 Windows:
psql -h
your-Babelfish.cluster.444455556666-us-east-1.rds.amazonaws.com
^ -U postgres ^ -d babelfish_db ^ -p 5432该命令将提示输入用户名 (-U) 的密码。
Password:
输入数据库集群用户名 (-U) 的密码。成功连接后,便可看到类似以下内容的输出。
psql (13.4) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help.
postgres=>
向内部 Babelfish 用户授予创建和加载扩展的权限。
babelfish_db=>
GRANT rds_superuser TO master_dbo;
GRANT ROLE
创建并加载
aws_s3
扩展。aws_commons
扩展是必要扩展,会在aws_s3
安装时自动安装。babelfish_db=>
create extension aws_s3 cascade;
NOTICE: installing required extension "aws_commons" CREATE EXTENSION
创建并加载
aws_lambda
扩展。babelfish_db=>
create extension aws_lambda cascade;
CREATE EXTENSION
babelfish_db=>
将 Babelfish 与 Amazon S3 搭配使用
如果尚无可与 Babelfish 数据库集群搭配使用的 Amazon S3 存储桶,可以创建一个。您可以向要使用的任何 Amazon S3 存储桶提供访问权限。
请完成以下一次性步骤,再尝试使用 Amazon S3 存储桶导入或导出数据。
为 Babelfish 数据库实例设置对 Amazon S3 存储桶的访问权限
-
根据需要为 Babelfish 实例创建 Amazon S3 存储桶。为此,请参照《Amazon Simple Storage Service 控制台用户指南》中创建存储桶部分的说明。
将文件上传到 Amazon S3 存储桶。为此,请参照《Amazon Simple Storage Service 控制台用户指南》中在存储桶中添加对象部分的步骤。
-
根据需要设置权限:
-
要从 Amazon S3 导入数据,Babelfish 数据库集群需要获得访问桶的权限。建议使用 Amazon Identity and Access Management (IAM) 角色,并将 IAM 策略附加到集群的该角色。为此,请按照使用 IAM 角色访问 Amazon S3 存储桶中的步骤进行操作。
-
要从 Babelfish 数据库集群导出数据,必须向集群授予对 Amazon S3 桶的访问权限。与导入一样,建议使用 IAM 角色和策略。为此,请按照设置 Amazon S3 存储桶的访问权限中的步骤进行操作。
-
现在,您可以将带有 aws_s3
扩展的 Amazon S3 与 Babelfish 数据库集群搭配使用。
将数据从 Amazon S3 导入 Babelfish 以及将 Babelfish 数据导出到 Amazon S3
将
aws_s3
扩展与 Babelfish 数据库集群搭配使用。这样做时,请务必参考 PostgreSQL 上下文中存在的表格。也就是说,如果想导入名为
[database].[schema].[tableA]
的 Babelfish 表,则在aws_s3
函数中将该表称为database_schema_tableA
:有关使用
aws_s3
函数导入数据的示例,请参阅 将数据从 Amazon S3 导入到 Aurora PostgreSQL 数据库集群。有关使用
aws_s3
函数导出数据的示例,请参见 使用 aws_s3.query_export_to_s3 函数导出查询数据。
在使用
aws_s3
扩展和 Amazon S3 时,请务必使用 PostgreSQL 命名参考 Babelfish 表,如下表所示。
Babelfish 表 |
Aurora PostgreSQL 表 |
---|---|
|
|
若要了解有关将 Amazon S3 与 Aurora PostgreSQL 搭配使用的更多信息,请参阅 将 Amazon S3 中的数据导入到 Aurora PostgreSQL 数据库集群 和 将数据从 Aurora PostgreSQL 数据库集群导出到 Amazon S3。
将 Babelfish 和 Amazon Lambda 搭配使用
在 aws_lambda
扩展已加载到 Babelfish 数据库集群中之后,但在 Lambda 函数可以调用之前,您可以按照此过程授予 Lambda 对数据库集群的访问权限。
为 Babelfish 数据库集群设置访问权限以使用 Lambda
此过程使用 Amazon CLI 创建 IAM 策略和角色,并将其与 Babelfish 数据库集群关联。
创建允许从 Babelfish 数据库集群访问 Lambda 的 IAM 策略。
aws iam create-policy --policy-name
rds-lambda-policy
--policy-document '{ "Version": "2012-10-17", "Statement": [ { "Sid": "AllowAccessToExampleFunction", "Effect": "Allow", "Action": "lambda:InvokeFunction", "Resource": "arn:aws:lambda:
" } ] }'aws-region
:444455556666:function:my-function创建该策略可在运行时承担的 IAM 角色。
aws iam create-role --role-name
rds-lambda-role
--assume-role-policy-document '{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "rds.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }'将 策略附加到该角色。
aws iam attach-role-policy \ --policy-arn arn:aws:iam::
444455556666
:policy/rds-lambda-policy
\ --role-namerds-lambda-role
--regionaws-region
向 Babelfish 数据库集群附加角色
aws rds add-role-to-db-cluster \ --db-cluster-identifier
my-cluster-name
\ --feature-name Lambda \ --role-arn arn:aws:iam::444455556666:role/rds-lambda-role
\ --regionaws-region
完成这些任务之后,您可以调用 Lambda 函数。有关使用 Amazon Lambda 为 Aurora PostgreSQL 数据库集群设置 Amazon Lambda 的更多信息和示例,请参阅 步骤 2:为 Aurora PostgreSQL 数据库集群和 Amazon Lambda 配置 IAM。
从 Babelfish 数据库集群调用 Lambda 函数
Amazon Lambda 支持使用 Java、Node.js、Python、Ruby 和其他语言编写的函数。如果调用该函数时返回文本,则可以从 Babelfish 数据库集群中调用它。以下示例是返回问候语的占位符 Python 函数。
lambda_function.py import json def lambda_handler(event, context): #TODO implement return { 'statusCode': 200, 'body': json.dumps('Hello from Lambda!')
目前,Babelfish 不支持 JSON。如果函数返回 JSON,则可使用包装器来处理 JSON。例如,假设之前显示的 lambda_function.py
作为 my-function
存储在 Lambda 中。
使用
psql
客户端(或 pgAdmin 客户端)连接到 Babelfish 数据库集群。有关更多信息,请参阅使用 psql 连接到数据库集群。创建包装器。此示例使用 PostgreSQL 的 SQL 程序语言:
PL/pgSQL
。要了解详情,请参阅 PL/pgSQL–SQL 程序语言。 create or replace function master_dbo.lambda_wrapper() returns text language plpgsql as $$ declare r_status_code integer; r_payload text; begin SELECT payload INTO r_payload FROM aws_lambda.invoke( aws_commons.create_lambda_function_arn('my-function', 'us-east-1') ,'{"body": "Hello from Postgres!"}'::json ); return r_payload ; end; $$;
该函数现在可以从 Babelfish TDS 端口 (1433) 或从 PostgreSQL 端口 (5433) 运行。
若要从 PostgreSQL 端口调用此函数:
SELECT * from aws_lambda.invoke(aws_commons.create_lambda_function_arn('my-function', 'us-east-1'), '{"body": "Hello from Postgres!"}'::json );
输出类似于以下内容:
status_code | payload | executed_version | log_result -------------+-------------------------------------------------------+------------------+------------ 200 | {"statusCode": 200, "body": "\"Hello from Lambda!\""} | $LATEST | (1 row)
若要从 TDS 端口调用此函数,请使用 SQL Server
sqlcmd
命令行客户端连接到此端口。有关详细信息,请参阅 使用 SQL Server 客户端连接到数据库集群。连接后,运行以下命令:1>
select lambda_wrapper();
2>
go
该命令返回的输出类似于下方内容:
{"statusCode": 200, "body": "\"Hello from Lambda!\""}
若要了解有关将 Lambda 与 Aurora PostgreSQL 搭配使用的更多信息,请参阅 从 Aurora PostgreSQL 数据库集群中调用 Amazon Lambda 函数。有关使用 Lambda 函数的更多信息,请参阅《Amazon Lambda 开发人员指南》中的 Lambda 入门。
在 Babelfish 中使用 pg_stat_statements
从 3.3.0 开始,适用于 Aurora PostgreSQL 的 Babelfish 支持 pg_stat_statements
扩展。要了解更多信息,请参阅 pg_stat_statements
有关 Aurora PostgreSQL 支持的这一扩展的版本详细信息,请参阅扩展版本。
创建 pg_stat_statements 扩展
要开启 pg_stat_statements
,必须开启查询标识符计算。如果在参数组中将 compute_query_id
设置为 on
或 auto
,则会自动完成此操作。compute_query_id
参数的默认值为 auto
。您还需要创建此扩展,才能开启此功能。使用以下命令从 T-SQL 终端节点安装扩展:
1>
EXEC sp_execute_postgresql 'CREATE EXTENSION pg_stat_statements WITH SCHEMA sys';
您可以使用以下查询访问查询统计信息:
postgres=>
select * from pg_stat_statements;
注意
在安装过程中,如果您不提供扩展的模式名称,则默认情况下,将在公共模式中创建名称。要进行访问,必须对模式限定符使用方括号,如下所示:
postgres=>
select * from [public].pg_stat_statements;
您还可以从 PSQL 终端节点创建扩展。
授权扩展
默认情况下,无需任何授权即可查看在 T-SQL 数据库中执行的查询的统计信息。
要访问其他人创建的查询统计信息,您需要拥有 pg_read_all_stats
PostgreSQL 角色。按照下面提到的步骤构建 GRANT pg_read_all_stats 命令。
在 T-SQL 中,使用以下返回内部 PG 角色名称的查询。
SELECT rolname FROM pg_roles WHERE oid = USER_ID();
使用 rds_superuser 权限连接到适用于 Aurora PostgreSQL 的 Babelfish 数据库,然后使用以下命令:
GRANT pg_read_all_stats TO <rolname_from_above_query>
示例
从 T-SQL 终端节点:
1>
SELECT rolname FROM pg_roles WHERE oid = USER_ID();2>
go
rolname
-------
master_dbo
(1 rows affected)
从 PSQL 终端节点:
babelfish_db=# grant pg_read_all_stats to master_dbo;
GRANT ROLE
您可以使用 pg_stat_statements 视图访问查询统计信息:
1>
create table t1(cola int);2>
go1>
insert into t1 values (1),(2),(3);2>
go
(3 rows affected)
1>
select userid, dbid, queryid, query from pg_stat_statements;2>
go
userid dbid queryid query
------ ---- ------- -----
37503 34582 6487973085327558478 select * from t1
37503 34582 6284378402749466286 SET QUOTED_IDENTIFIER OFF
37503 34582 2864302298511657420 insert into t1 values ($1),($2),($3)
10 34582 NULL <insufficient privilege>
37503 34582 5615368793313871642 SET TEXTSIZE 4096
37503 34582 639400815330803392 create table t1(cola int)
(6 rows affected)
重置查询统计信息
您可以使用 pg_stat_statements_reset()
重置 pg_stat_statements 到目前为止收集的统计信息。要了解更多信息,请参阅 pg_stat_statementsrds_superuser
权限连接到适用于 Aurora PostgreSQL 的 Babelfish,然后使用以下命令:
SELECT pg_stat_statements_reset();
限制
目前,通过 T-SQL 终端节点不支持
pg_stat_statements()
。pg_stat_statements
视图是推荐的收集统计信息的方法。有些查询可能会由 Aurora PostgreSQL 引擎实现的 T-SQL 解析器重新写入,
pg_stat_statements
视图将显示重新写入的查询,而不是原始查询。示例
select next value for [dbo].[newCounter];
在 pg_stat_statements 视图中,上述查询被重新写入为以下内容。
select nextval($1);
根据语句的执行流程,pg_stat_statements 可能无法跟踪某些查询,因此在视图中也看不见这些查询。这包括以下语句:
use dbname
、goto
、print
、raise error
、set
、throw
、declare cursor
。对于 CREATE LOGIN 和 ALTER LOGIN 语句,将不显示查询和查询 ID。它将显示权限不足。
pg_stat_statements
视图始终包含以下两个条目,因为它们是由sqlcmd
客户端在内部执行。SET QUOTED_IDENTIFIER OFF
SET TEXTSIZE 4096