调用 Data API
您可以调用数据 API 或 Amazon CLI 以在集群或无服务器工作组上运行 SQL 语句。运行 SQL 语句的主要操作是《Amazon Redshift 数据 API 参考》中的 ExecuteStatement
和 BatchExecuteStatement
。数据 API 支持 Amazon 开发工具包所支持的编程语言。有关它们的更多信息,请参阅用于在 Amazon 上构建的工具
要查看调用 Data API 的代码示例,请参阅 GitHub 中的 Redshift 数据 API入门
您可以使用 Amazon CLI 调用 Data API。
以下示例使用 Amazon CLI 调用数据 API。要运行示例,请编辑参数值以匹配您的环境。在许多示例中,会提供 cluster-identifier
以针对集群运行。在针对无服务器工作组运行时,需改为提供 workgroup-name
。这些示例演示了一些数据 API 操作。有关更多信息,请参阅 Amazon CLI 命令参考。
以下示例中的命令已被拆分和格式化以便于阅读。
运行 SQL 语句
要运行 SQL 语句,请使用 aws redshift-data execute-statement
Amazon CLI 命令。
以下 Amazon CLI 命令针对集群运行一个 SQL 语句,并返回一个标识符来获取结果。此示例使用 Amazon Secrets Manager 身份验证方法。
aws redshift-data execute-statement --region us-west-2 --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn --cluster-identifier mycluster-test --sql "select * from stl_query limit 1" --database dev
以下为响应示例。
{ "ClusterIdentifier": "mycluster-test", "CreatedAt": 1598323175.823, "Database": "dev", "Id": "c016234e-5c6c-4bc5-bb16-2c5b8ff61814", "SecretArn": "arn:aws:secretsmanager:us-west-2:123456789012:secret:yanruiz-secret-hKgPWn" }
以下 Amazon CLI 命令针对集群运行一个 SQL 语句,并返回一个标识符来获取结果。此示例使用临时凭证身份验证方法。
aws redshift-data execute-statement --region us-west-2 --db-user myuser --cluster-identifier mycluster-test --database dev --sql "select * from stl_query limit 1"
以下为响应示例。
{ "ClusterIdentifier": "mycluster-test", "CreatedAt": 1598306924.632, "Database": "dev", "DbUser": "myuser", "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766" }
以下 Amazon CLI 命令针对无服务器工作组运行一个 SQL 语句,并返回一个标识符来获取结果。此示例使用临时凭证身份验证方法。
aws redshift-data execute-statement --database dev --workgroup-name myworkgroup --sql "select 1;"
以下为响应示例。
{ "CreatedAt": "2022-02-11T06:25:28.748000+00:00", "Database": "dev", "DbUser": "IAMR:RoleName", "Id": "89dd91f5-2d43-43d3-8461-f33aa093c41e", "WorkgroupName": "myworkgroup" }
以下 Amazon CLI 命令针对集群运行一个 SQL 语句,并返回一个标识符来获取结果。此示例使用 Amazon Secrets Manager 身份验证方法和幂等性令牌。
aws redshift-data execute-statement --region us-west-2 --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn --cluster-identifier mycluster-test --sql "select * from stl_query limit 1" --database dev --client-token b855dced-259b-444c-bc7b-d3e8e33f94g1
以下为响应示例。
{ "ClusterIdentifier": "mycluster-test", "CreatedAt": 1598323175.823, "Database": "dev", "Id": "c016234e-5c6c-4bc5-bb16-2c5b8ff61814", "SecretArn": "arn:aws:secretsmanager:us-west-2:123456789012:secret:yanruiz-secret-hKgPWn" }
运行带有参数的 SQL 语句
要运行 SQL 语句,请使用 aws redshift-data execute-statement
Amazon CLI 命令。
以下 Amazon CLI 命令针对集群运行一个 SQL 语句,并返回一个标识符来获取结果。此示例使用 Amazon Secrets Manager 身份验证方法。SQL 文本具有命名参数 colname
和 distance
。在这种情况下,表中的列名为 ratecode
并且谓词中使用的距离是 5
。SQL 语句的命名参数值在 parameters
选项中指定。
aws redshift-data execute-statement --region us-west-2 --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn --cluster-identifier mycluster-test --sql "SELECT :colname, COUNT(*) FROM demo_table WHERE trip_distance > :distance" --parameters "[{\"name\": \"colname\", \"value\": \"ratecode\"}, \ {\"name\": \"distance\", \"value\": \"5\"}]" --database dev
以下为响应示例。
{ "ClusterIdentifier": "mycluster-test", "CreatedAt": 1598323175.823, "Database": "dev", "Id": "c016234e-5c6c-4bc5-bb16-2c5b8ff61814", "SecretArn": "arn:aws:secretsmanager:us-west-2:123456789012:secret:yanruiz-secret-hKgPWn" }
以下示例使用示例数据库中的 EVENT
表。有关 COPY 语法的更多信息,请参阅 Amazon Redshift 数据库开发人员指南中的 EVENT 表。
如果您的数据库中没有 EVENT
表,则可以使用数据 API 创建一个,如下所示:
aws redshift-data execute-statement --database dev --cluster-id my-test-cluster --db-user awsuser --sql "create table event( eventid integer not null distkey, venueid smallint not null, catid smallint not null, dateid smallint not null sortkey, eventname varchar(200), starttime timestamp)"
以下命令将一个行插入 EVENT
表。
aws redshift-data execute-statement --database dev --cluster-id my-test-cluster --db-user awsuser --sql "insert into event values(:eventid, :venueid::smallint, :catid, :dateid, :eventname, :starttime)" --parameters "[{\"name\": \"eventid\", \"value\": \"1\"}, {\"name\": \"venueid\", \"value\": \"1\"}, {\"name\": \"catid\", \"value\": \"1\"}, {\"name\": \"dateid\", \"value\": \"1\"}, {\"name\": \"eventname\", \"value\": \"event 1\"}, {\"name\": \"starttime\", \"value\": \"2022-02-22\"}]"
以下命令将另一个行插入 EVENT
表。该示例演示以下内容:
名为
id
参数在 SQL 文本中使用了四次。插入参数
starttime
时自动应用隐式类型转换。venueid
列是转换为 SMALINT 数据类型的类型。表示 DATE 数据类型的字符串将隐式转换为 TIMESTAMP 数据类型。
注释可以在 SQL 文本中使用。
aws redshift-data execute-statement --database dev --cluster-id my-test-cluster --db-user awsuser --sql "insert into event values(:id, :id::smallint, :id, :id, :eventname, :starttime) /*this is comment, and it won't apply parameterization for :id, :eventname or :starttime here*/" --parameters "[{\"name\": \"eventname\", \"value\": \"event 2\"}, {\"name\": \"starttime\", \"value\": \"2022-02-22\"}, {\"name\": \"id\", \"value\": \"2\"}]"
下面显示了两个插入的行:
eventid | venueid | catid | dateid | eventname | starttime ---------+---------+-------+--------+-----------+--------------------- 1 | 1 | 1 | 1 | event 1 | 2022-02-22 00:00:00 2 | 2 | 2 | 2 | event 2 | 2022-02-22 00:00:00
以下命令使用 WHERE 子句中的命名参数来检索 eventid
为 1
的行。
aws redshift-data execute-statement --database dev --cluster-id my-test-cluster --db-user awsuser --sql "select * from event where eventid=:id" --parameters "[{\"name\": \"id\", \"value\": \"1\"}]"
运行以下命令以获取上一个 SQL 语句的 SQL 结果:
aws redshift-data get-statement-result --id 7529ad05-b905-4d71-9ec6-8b333836eb5a
提供以下结果:
{ "Records": [ [ { "longValue": 1 }, { "longValue": 1 }, { "longValue": 1 }, { "longValue": 1 }, { "stringValue": "event 1" }, { "stringValue": "2022-02-22 00:00:00.0" } ] ], "ColumnMetadata": [ { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "eventid", "length": 0, "name": "eventid", "nullable": 0, "precision": 10, "scale": 0, "schemaName": "public", "tableName": "event", "typeName": "int4" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "venueid", "length": 0, "name": "venueid", "nullable": 0, "precision": 5, "scale": 0, "schemaName": "public", "tableName": "event", "typeName": "int2" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "catid", "length": 0, "name": "catid", "nullable": 0, "precision": 5, "scale": 0, "schemaName": "public", "tableName": "event", "typeName": "int2" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "dateid", "length": 0, "name": "dateid", "nullable": 0, "precision": 5, "scale": 0, "schemaName": "public", "tableName": "event", "typeName": "int2" }, { "isCaseSensitive": true, "isCurrency": false, "isSigned": false, "label": "eventname", "length": 0, "name": "eventname", "nullable": 1, "precision": 200, "scale": 0, "schemaName": "public", "tableName": "event", "typeName": "varchar" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "label": "starttime", "length": 0, "name": "starttime", "nullable": 1, "precision": 29, "scale": 6, "schemaName": "public", "tableName": "event", "typeName": "timestamp" } ], "TotalNumRows": 1 }
要运行多个 SQL 语句
要使用一个命令运行多个 SQL 语句,请使用 aws
redshift-data batch-execute-statement
Amazon CLI 命令。
以下 Amazon CLI 命令针对集群运行三个 SQL 语句,并返回一个标识符来获取结果。此示例使用临时凭证身份验证方法。
aws redshift-data batch-execute-statement --region us-west-2 --db-user myuser --cluster-identifier mycluster-test --database dev --sqls "set timezone to BST" "select * from mytable" "select * from another_table"
以下为响应示例。
{ "ClusterIdentifier": "mycluster-test", "CreatedAt": 1598306924.632, "Database": "dev", "DbUser": "myuser", "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766" }
要列出有关 SQL 语句的元数据
要列出有关 SQL 语句的元数据,请使用 aws redshift-data
list-statements
Amazon CLI 命令。运行此命令的授权基于调用者的 IAM 权限。
以下 Amazon CLI 命令列出了运行的 SQL 语句。
aws redshift-data list-statements --region us-west-2 --status ALL
以下为响应示例。
{ "Statements": [ { "CreatedAt": 1598306924.632, "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766", "QueryString": "select * from stl_query limit 1", "Status": "FINISHED", "UpdatedAt": 1598306926.667 }, { "CreatedAt": 1598311717.437, "Id": "e0ebd578-58b3-46cc-8e52-8163fd7e01aa", "QueryString": "select * from stl_query limit 1", "Status": "FAILED", "UpdatedAt": 1598311719.008 }, { "CreatedAt": 1598313683.65, "Id": "c361d4f7-8c53-4343-8c45-6b2b1166330c", "QueryString": "select * from stl_query limit 1", "Status": "ABORTED", "UpdatedAt": 1598313685.495 }, { "CreatedAt": 1598306653.333, "Id": "a512b7bd-98c7-45d5-985b-a715f3cfde7f", "QueryString": "select 1", "Status": "FINISHED", "UpdatedAt": 1598306653.992 } ] }
描述有关 SQL 语句的元数据
要获取 SQL 语句的元数据描述,请使用 aws
redshift-data describe-statement
Amazon CLI 命令。运行此命令的授权基于调用者的 IAM 权限。
以下 Amazon CLI 命令描述 SQL 语句。
aws redshift-data describe-statement --id d9b6c0c9-0747-4bf4-b142-e8883122f766 --region us-west-2
以下为响应示例。
{ "ClusterIdentifier": "mycluster-test", "CreatedAt": 1598306924.632, "Duration": 1095981511, "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766", "QueryString": "select * from stl_query limit 1", "RedshiftPid": 20859, "RedshiftQueryId": 48879, "ResultRows": 1, "ResultSize": 4489, "Status": "FINISHED", "UpdatedAt": 1598306926.667 }
以下是使用多个 SQL 语句运行 batch-execute-statement
命令后的 describe-statement
响应示例。
{ "ClusterIdentifier": "mayo", "CreatedAt": 1623979777.126, "Duration": 6591877, "HasResultSet": true, "Id": "b2906c76-fa6e-4cdf-8c5f-4de1ff9b7652", "RedshiftPid": 31459, "RedshiftQueryId": 0, "ResultRows": 2, "ResultSize": 22, "Status": "FINISHED", "SubStatements": [ { "CreatedAt": 1623979777.274, "Duration": 3396637, "HasResultSet": true, "Id": "b2906c76-fa6e-4cdf-8c5f-4de1ff9b7652:1", "QueryString": "select 1;", "RedshiftQueryId": -1, "ResultRows": 1, "ResultSize": 11, "Status": "FINISHED", "UpdatedAt": 1623979777.903 }, { "CreatedAt": 1623979777.274, "Duration": 3195240, "HasResultSet": true, "Id": "b2906c76-fa6e-4cdf-8c5f-4de1ff9b7652:2", "QueryString": "select 2;", "RedshiftQueryId": -1, "ResultRows": 1, "ResultSize": 11, "Status": "FINISHED", "UpdatedAt": 1623979778.076 } ], "UpdatedAt": 1623979778.183 }
获取 SQL 语句的结果
要从运行的 SQL 语句中获取结果,请使用 redshift-data get-statement-result
Amazon CLI 命令。您可以提供响应 execute-statement
或者 batch-execute-statement
而收到的 Id
。可以在 describe-statement
的结果中检索 batch-execute-statement
运行的 SQL 语句的 Id
值,并以冒号和 b2906c76-fa6e-4cdf-8c5f-4de1ff9b7652:2
等序号为该值添加后缀。如果使用 batch-execute-statement
运行多个 SQL 语句,则每个 SQL 语句都有一个 Id
值,如 describe-statement
中所示。运行此命令的授权基于调用者的 IAM 权限。
以下语句返回 execute-statement
运行的 SQL 语句的结果。
aws redshift-data get-statement-result --id d9b6c0c9-0747-4bf4-b142-e8883122f766 --region us-west-2
以下语句返回 batch-execute-statement
运行的第二个 SQL 语句的结果。
aws redshift-data get-statement-result --id b2906c76-fa6e-4cdf-8c5f-4de1ff9b7652:2 --region us-west-2
以下是 get-statement-result
调用的响应的示例。
{ "ColumnMetadata": [ { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "userid", "length": 0, "name": "userid", "nullable": 0, "precision": 10, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "int4" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "query", "length": 0, "name": "query", "nullable": 0, "precision": 10, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "int4" }, { "isCaseSensitive": true, "isCurrency": false, "isSigned": false, "label": "label", "length": 0, "name": "label", "nullable": 0, "precision": 320, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "bpchar" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "xid", "length": 0, "name": "xid", "nullable": 0, "precision": 19, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "int8" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "pid", "length": 0, "name": "pid", "nullable": 0, "precision": 10, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "int4" }, { "isCaseSensitive": true, "isCurrency": false, "isSigned": false, "label": "database", "length": 0, "name": "database", "nullable": 0, "precision": 32, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "bpchar" }, { "isCaseSensitive": true, "isCurrency": false, "isSigned": false, "label": "querytxt", "length": 0, "name": "querytxt", "nullable": 0, "precision": 4000, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "bpchar" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "label": "starttime", "length": 0, "name": "starttime", "nullable": 0, "precision": 29, "scale": 6, "schemaName": "", "tableName": "stll_query", "typeName": "timestamp" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "label": "endtime", "length": 0, "name": "endtime", "nullable": 0, "precision": 29, "scale": 6, "schemaName": "", "tableName": "stll_query", "type": 93, "typeName": "timestamp" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "aborted", "length": 0, "name": "aborted", "nullable": 0, "precision": 10, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "int4" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "insert_pristine", "length": 0, "name": "insert_pristine", "nullable": 0, "precision": 10, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "int4" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": true, "label": "concurrency_scaling_status", "length": 0, "name": "concurrency_scaling_status", "nullable": 0, "precision": 10, "scale": 0, "schemaName": "", "tableName": "stll_query", "typeName": "int4" } ], "Records": [ [ { "longValue": 1 }, { "longValue": 3 }, { "stringValue": "health" }, { "longValue": 1023 }, { "longValue": 15279 }, { "stringValue": "dev" }, { "stringValue": "select system_status from stv_gui_status;" }, { "stringValue": "2020-08-21 17:33:51.88712" }, { "stringValue": "2020-08-21 17:33:52.974306" }, { "longValue": 0 }, { "longValue": 0 }, { "longValue": 6 } ] ], "TotalNumRows": 1 }
要描述表
要获取描述表的元数据,请使用 aws redshift-data
describe-table
Amazon CLI 命令。
以下 Amazon CLI 命令针对集群运行一个 SQL 语句,并返回描述表的元数据。此示例使用 Amazon Secrets Manager 身份验证方法。
aws redshift-data describe-table --region us-west-2 --cluster-identifier mycluster-test --database dev --schema information_schema --table sql_features --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn
以下为响应示例。
{ "ColumnList": [ { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "feature_id", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "feature_name", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" } ] }
以下 Amazon CLI 命令针对集群运行一个 SQL 语句来描述表。此示例使用临时凭证身份验证方法。
aws redshift-data describe-table --region us-west-2 --db-user myuser --cluster-identifier mycluster-test --database dev --schema information_schema --table sql_features
以下为响应示例。
{ "ColumnList": [ { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "feature_id", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "feature_name", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "sub_feature_id", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "sub_feature_name", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "is_supported", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "is_verified_by", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" }, { "isCaseSensitive": false, "isCurrency": false, "isSigned": false, "length": 2147483647, "name": "comments", "nullable": 1, "precision": 2147483647, "scale": 0, "schemaName": "information_schema", "tableName": "sql_features", "typeName": "character_data" } ] }
要列出集群中的数据库
要列出集群中的数据库,请使用 aws redshift-data
list-databases
Amazon CLI 命令。
以下 Amazon CLI 命令针对集群运行一个 SQL 语句来列出数据库。此示例使用 Amazon Secrets Manager 身份验证方法。
aws redshift-data list-databases --region us-west-2 --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn --cluster-identifier mycluster-test --database dev
以下为响应示例。
{ "Databases": [ "dev" ] }
以下 Amazon CLI 命令针对集群运行一个 SQL 语句来列出数据库。此示例使用临时凭证身份验证方法。
aws redshift-data list-databases --region us-west-2 --db-user myuser --cluster-identifier mycluster-test --database dev
以下为响应示例。
{ "Databases": [ "dev" ] }
要列出数据库中的 schema
要列出数据库中的 schema,请使用 aws redshift-data
list-schemas
Amazon CLI 命令。
以下 Amazon CLI 命令针对集群运行一个 SQL 语句来列出数据库中的架构。此示例使用 Amazon Secrets Manager 身份验证方法。
aws redshift-data list-schemas --region us-west-2 --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn --cluster-identifier mycluster-test --database dev
以下为响应示例。
{ "Schemas": [ "information_schema", "pg_catalog", "pg_internal", "public" ] }
以下 Amazon CLI 命令针对集群运行一个 SQL 语句来列出数据库中的架构。此示例使用临时凭证身份验证方法。
aws redshift-data list-schemas --region us-west-2 --db-user mysuser --cluster-identifier mycluster-test --database dev
以下为响应示例。
{ "Schemas": [ "information_schema", "pg_catalog", "pg_internal", "public" ] }
要列出数据库中的表
要列出数据库中的表,请使用 aws redshift-data
list-tables
Amazon CLI 命令。
以下 Amazon CLI 命令针对集群运行一个 SQL 语句来列出数据库中的表。此示例使用 Amazon Secrets Manager 身份验证方法。
aws redshift-data list-tables --region us-west-2 --secret arn:aws:secretsmanager:us-west-2:123456789012:secret:myuser-secret-hKgPWn --cluster-identifier mycluster-test --database dev --schema information_schema
以下为响应示例。
{ "Tables": [ { "name": "sql_features", "schema": "information_schema", "type": "SYSTEM TABLE" }, { "name": "sql_implementation_info", "schema": "information_schema", "type": "SYSTEM TABLE" } }
以下 Amazon CLI 命令针对集群运行一个 SQL 语句来列出数据库中的表。此示例使用临时凭证身份验证方法。
aws redshift-data list-tables --region us-west-2 --db-user myuser --cluster-identifier mycluster-test --database dev --schema information_schema
以下为响应示例。
{ "Tables": [ { "name": "sql_features", "schema": "information_schema", "type": "SYSTEM TABLE" }, { "name": "sql_implementation_info", "schema": "information_schema", "type": "SYSTEM TABLE" } ] }