Using the Amazon Redshift Data API
You can access your Amazon Redshift database using the built-in Amazon Redshift Data
API. Using
this API, you can access Amazon Redshift data with web services–based applications,
including AWS Lambda, Amazon SageMaker notebooks, and AWS Cloud9. For more information
on these
applications, see AWS Lambda
The Data API doesn't require a persistent connection to the cluster. Instead, it provides a secure HTTP endpoint and integration with AWS SDKs. You can use the endpoint to run SQL statements without managing connections. Calls to the Data API are asynchronous.
The Data API uses either credentials stored in AWS Secrets Manager or temporary database credentials. You don't need to pass passwords in the API calls with either authorization method. For more information about AWS Secrets Manager, see What Is AWS Secrets Manager? in the AWS Secrets Manager User Guide.
For more information about the Data API operations, see the Amazon Redshift Data API Reference.
Working with the Amazon Redshift Data API
Before you use the Amazon Redshift Data API, review the following steps:
-
Determine if you, as the caller of the Data API, are authorized. For more information about authorization, see Authorizing access to the Amazon Redshift Data API.
-
Determine if you plan to call the Data API with authentication credentials from Secrets Manager or temporary credentials. For more information, see Choosing authentication credentials when calling the Amazon Redshift Data API.
-
Set up a secret if you use Secrets Manager for authentication credentials. For more information, see Storing database credentials in AWS Secrets Manager.
-
Review the considerations and limitations when calling the Data API. For more information, see Considerations when calling the Amazon Redshift Data API.
-
Call the Data API from the AWS Command Line Interface (AWS CLI), from your own code, or using the query editor in the Amazon Redshift console. For examples of calling from the AWS CLI, see Calling the Data API with the AWS CLI.
Authorizing access to the Amazon Redshift Data API
To access the Data API, a user must be authorized. You can authorize a user
to access the Data API by adding a managed policy, which is a predefined AWS Identity
and Access Management
(IAM) policy, to that user. To see the permissions allowed and denied by managed
policies, see the IAM console (https://console.aws.amazon.com/iam/
Amazon Redshift provides the AmazonRedshiftDataFullAccess managed policy. This
policy provides full access to Amazon Redshift Data API operations. This policy also
allows scoped
access to specific Amazon Redshift, AWS Secrets Manager, and IAM API operations needed
to authenticate
and access an Amazon Redshift cluster. If you use AWS Secrets Manager to authenticate,
the policy allows use
of the secretsmanager:GetSecretValue action to retrieve the secret tagged
with the key RedshiftDataFullAccess. If you use temporary credentials to
authenticate, the policy allows use of the redshift:GetClusterCredentials
action to the database user name redshift_data_api_user for any database in
the cluster. This user name must have already been created in your database.
You can also create your own IAM policy that allows access to specific
resources. To create your policy, use the AmazonRedshiftDataFullAccess
policy as your starting template. After you create your policy, add it to each user
that
requires access to the Data API.
To run a query on a cluster that is owned by another account,
the owning account must provide an IAM role that the Data API
can assume in the calling account. For example, suppose Account B owns a cluster that
Account A needs to access.
Account B can attach the AWS-managed policy AmazonRedshiftDataFullAccess to Account B's IAM role.
Then Account B trusts Account A using a trust policy such as the following:
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": [ "arn:aws:iam::accountID-of-account-A:role/someRoleA" ] }, "Action": "sts:AssumeRole" } ] }
Finally, the Account A IAM role needs to be able to assume the Account B IAM role.
{ "Version": "2012-10-17", "Statement": { "Effect": "Allow", "Action": "sts:AssumeRole", "Resource": "arn:aws:iam::accountID-of-account-B:role/someRoleB" } }
The following links provide more information about AWS Identity and Access Management in the IAM User Guide.
-
For information about creating an IAM roles, see Creating IAM roles.
-
For information about creating an IAM policy, see Creating IAM policies.
-
For information about adding an IAM policy to a user, see Adding and removing IAM identity permissions.
Storing database credentials in AWS Secrets Manager
When you call the Data API, you can pass credentials for the cluster by using a secret in AWS Secrets Manager. To pass credentials in this way, you specify the name of the secret or the Amazon Resource Name (ARN) of the secret.
To store credentials with Secrets Manager, you need SecretManagerReadWrite managed policy permission.
For more information about the minimum permissions, see Creating and Managing Secrets with AWS Secrets Manager
in the AWS Secrets Manager User Guide.
To store your credentials in a secret for an Amazon Redshift cluster
-
Use AWS Secrets Manager to create a secret that contains credentials for your cluster:
-
When you choose Store a new secret, choose Credentials for Redshift cluster.
-
Store your values for User name (database user), Password, and DB cluster (cluster identifier) in your secret.
-
Tag the secret with the key
RedshiftDataFullAccess. The AWS-managed policyAmazonRedshiftDataFullAccessonly allows the actionsecretsmanager:GetSecretValuefor secrets tagged with the keyRedshiftDataFullAccess.
For instructions, see Creating a Basic Secret in the AWS Secrets Manager User Guide.
-
-
Use the AWS Secrets Manager console to view the details for the secret you created, or run the
aws secretsmanager describe-secretAWS CLI command.Note the name and ARN of the secret. You can use these in calls to the Data API.
Creating an Amazon VPC endpoint (AWS PrivateLink) for the Data API
Amazon Virtual Private Cloud (Amazon VPC) enables you to launch AWS resources, such as Amazon Redshift clusters and applications, into a virtual private cloud (VPC). AWS PrivateLink provides private connectivity between virtual private clouds (VPCs) and AWS services securely on the Amazon network. Using AWS PrivateLink, you can create VPC endpoints, which you can use connect to services across different accounts and VPCs based on Amazon VPC. For more information about AWS PrivateLink, see VPC Endpoint Services (AWS PrivateLink) in the Amazon Virtual Private Cloud User Guide.
You can call the Data API with Amazon VPC endpoints. Using an Amazon VPC endpoint keeps traffic between applications in your Amazon VPC and the Data API in the AWS network, without using public IP addresses. Amazon VPC endpoints can help you meet compliance and regulatory requirements related to limiting public internet connectivity. For example, if you use an Amazon VPC endpoint, you can keep traffic between an application running on an Amazon EC2 instance and the Data API in the VPCs that contain them.
After you create the Amazon VPC endpoint, you can start using it without making any code or configuration changes in your application.
To create an Amazon VPC endpoint for the Data API
-
Sign in to the AWS Management Console and open the Amazon VPC console at https://console.aws.amazon.com/vpc/
. -
Choose Endpoints, and then choose Create Endpoint.
-
On the Create Endpoint page, for Service category, choose AWS services. For Service Name, choose redshift-data (
com.amazonaws.).region.redshift-data -
For VPC, choose the VPC to create the endpoint in.
Choose the VPC that contains the application that makes Data API calls.
-
For Subnets, choose the subnet for each Availability Zone (AZ) used by the AWS service that is running your application.
To create an Amazon VPC endpoint, specify the private IP address range in which the endpoint is accessible. To do this, choose the subnet for each Availability Zone. Doing so restricts the VPC endpoint to the private IP address range specific to each Availability Zone and also creates an Amazon VPC endpoint in each Availability Zone.
-
For Enable DNS name, select Enable for this endpoint.
Private DNS resolves the standard Data API DNS hostname (
https://redshift-data.) to the private IP addresses associated with the DNS hostname specific to your Amazon VPC endpoint. As a result, you can access the Data API VPC endpoint using the AWS CLI or AWS SDKs without making any code or configuration changes to update the Data API endpoint URL.region.amazonaws.com -
For Security group, choose a security group to associate with the Amazon VPC endpoint.
Choose the security group that allows access to the AWS service that is running your application. For example, if an Amazon EC2 instance is running your application, choose the security group that allows access to the Amazon EC2 instance. The security group enables you to control the traffic to the Amazon VPC endpoint from resources in your VPC.
-
Choose Create endpoint.
After the endpoint is created, choose the link in the AWS Management Console to view the endpoint details.
The endpoint Details tab shows the DNS hostnames that were generated while creating the Amazon VPC endpoint.
You can use the standard endpoint
(redshift-data.) or
one of the VPC-specific endpoints to call the Data API within the Amazon VPC. The
standard Data API endpoint automatically routes to the Amazon VPC endpoint. This routing
occurs because the Private DNS hostname was enabled when the Amazon VPC endpoint was
created.
region.amazonaws.com
When you use an Amazon VPC endpoint in a Data API call, all traffic between your application and the Data API remains in the Amazon VPCs that contain them. You can use an Amazon VPC endpoint for any type of Data API call. For information about calling the Data API, see Considerations when calling the Amazon Redshift Data API.
Considerations when calling the Amazon Redshift Data API
Consider the following when calling the Data API:
-
The maximum duration of a query is 24 hours.
-
The maximum number of active queries (
STARTEDandSUBMITTEDqueries) per Amazon Redshift cluster is 200. -
The maximum query result size is 100 MB. If a call returns more than 100 MB of response data, the call is ended.
-
The maximum retention time for query results is 24 hours.
-
The maximum query statement size is 100 KB.
-
The Data API is available to query single-node and multiple-node clusters of the following node types:
-
dc2.large
-
dc2.8xlarge
-
ds2.xlarge
-
ds2.8xlarge
-
ra3.xlplus
-
ra3.4xlarge
-
ra3.16xlarge
-
-
The cluster must be in a virtual private cloud (VPC) based on the Amazon VPC service.
-
By default, users with the same IAM role or IAM user as the runner of an
ExecuteStatementorBatchExecuteStatementAPI operation can act on the same statement withCancelStatement,DescribeStatement,GetStatementResult, andListStatementsAPI operations. -
For a list of AWS Regions where the Data API is available, see Redshift Data API Endpoints in the Amazon Web Services General Reference.
Choosing authentication credentials when calling the Amazon Redshift Data API
When you call the Data API, you use one of the following authentication methods for some API operations. Each method requires a different combination of parameters.
- AWS Secrets Manager
-
With this method, provide the
secret-arnsecret value that is stored in AWS Secrets Manager. The specified secret contains credentials to connect to your database. You also supply a value forcluster-identifierthat matches the cluster identifier in the secret. - Temporary credentials
-
With this method, provide your
cluster-identifier,database, anddb-uservalues.
With either method, you can also supply a region value that specifies
the AWS Region where your cluster is located.
Mapping JDBC data types when calling the Amazon Redshift Data API
The following table maps Java Database Connectivity (JDBC) data types to the data types you specify in Data API calls.
|
JDBC data type |
Data API data type |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other types (including types related to date and time) |
|
String values are passed to the Amazon Redshift database and implicitly converted into a database data type.
Currently, the Data API doesn't support arrays of universal unique identifiers (UUIDs).
Running SQL statements with parameters when calling the Amazon Redshift Data API
You can control the SQL text submitted to the database engine by calling the Data API operation using parameters for parts of the SQL statement. Named parameters provide a flexible way to pass in parameters without hardcoding them in the SQL text. They help you reuse SQL text and avoid SQL injection problems.
The following example shows the named parameters of a parameters field of an
execute statement AWS CLI command.
--parameters "[{\"name\": \"id\", \"value\": \"1\"},{\"name\": \"address\", \"value\": \"Seattle\"}]"
Consider the following when using named parameters:
-
The named parameters can be in any order and parameters can be used more than one time in the SQL text. The parameters option shown in the previous example, the values
1andSeattleare inserted into the table columnsidandaddress. In the SQL text, you specify the named parameters as follows:--sql "insert into mytable values (:id, :address)" -
When the SQL runs, data is implicitly cast to a data type. For more information about data type casting, see Data types in the Amazon Redshift Database Developer Guide.
-
You can't set a value to NULL. The Data API interprets it as the literal string
NULL. The following example replacesidwith the literal stringnull. Not the SQL NULL value.--parameters "[{\"name\": \"id\", \"value\": \"null\"}]" -
You can't set a zero length value. The Data API SQL statement fails. The following example trys to set
idwith a zero length value and results in a failure of the SQL statement.--parameters "[{\"name\": \"id\", \"value\": \"\"}]" -
You can't set a table name in the SQL statement with a parameter. The Data API follows the rule of the JDBC
PreparedStatement. -
The output of the
describe statementoperation returns the query parameters of an SQL statement. -
Only the
execute-statementoperation supports SQL statements with parameters.
Calling the Data API
You can call the Data API or the AWS CLI to run SQL statements on your cluster.
The primary operation to run an SQL statement is ExecuteStatement. The Data API supports the
programming languages that are supported by the AWS SDK.
For more information on these,
see Tools to Build on AWS
To see code examples of calling the Data API,
see Getting Started with Redshift Data API
Calling the Data API with the AWS CLI
You can call the Data API using the AWS CLI.
The following examples use the AWS CLI to call the Data API. To run the examples, edit the parameter values to match your environment. These examples demonstrate a few of the Data API operations. For more information, see the AWS CLI Command Reference.
Commands in the following examples have been split and formatted for readability.
To run an SQL statement
To run an SQL statement, use the aws redshift-data execute-statement
AWS CLI command.
The following AWS CLI command runs an SQL statement and returns an identifier to fetch the results. This example uses the AWS Secrets Manager authentication method.
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
The following is an example of the response.
{ "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" }
The following AWS CLI command runs an SQL statement and returns an identifier to fetch the results. This example uses the temporary credentials authentication method.
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"
The following is an example of the response.
{ "ClusterIdentifier": "mycluster-test", "CreatedAt": 1598306924.632, "Database": "dev", "DbUser": "myuser", "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766" }
To run an SQL statement with parameters
To run an SQL statement, use the aws redshift-data execute-statement
AWS CLI command.
The following AWS CLI command runs an SQL statement and returns an identifier to
fetch the results. This example uses the AWS Secrets Manager authentication method.
The SQL text has named parameters colname and distance.
In this case, the column name in the table is ratecode and the distance used in
the predicate is 5.
Values for named parameters for the SQL statement are specified in the parameters option.
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
The following is an example of the response.
{ "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" }
The following example uses the EVENT table from the sample database.
For more information, see
EVENT table in the
Amazon Redshift Database Developer Guide.
If you don't already have the EVENT table in your database, you can create one using the Data API as follows:
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)"
The following command inserts one row into the EVENT table.
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\"}]"
The following command inserts a second row into the EVENT table. This example demonstrates the following:
-
The parameter named
idis used four times in the SQL text. -
Implicit type conversion is applied automatically when inserting parameter
starttime. -
The
venueidcolumn is type cast to SMALLINT data type. -
Character strings that represent the DATE data type are implicitly converted into the TIMESTAMP data type.
-
Comments can be used within SQL text.
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\"}]"
The following shows the two inserted rows:
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
The following command uses a named parameter in a WHERE clause to retrieve the row
where eventid is 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\"}]"
Run the following command to get the SQL results of the previous SQL statement:
aws redshift-data get-statement-result --id 7529ad05-b905-4d71-9ec6-8b333836eb5a
Provides the following results:
{ "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 }
To run multiple SQL statements
To run multiple SQL statements with one command, use the aws
redshift-data batch-execute-statement AWS CLI command.
The following AWS CLI command runs three SQL statements and returns an identifier to fetch the results. This example uses the temporary credentials authentication method.
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"
The following is an example of the response.
{ "ClusterIdentifier": "mycluster-test", "CreatedAt": 1598306924.632, "Database": "dev", "DbUser": "myuser", "Id": "d9b6c0c9-0747-4bf4-b142-e8883122f766" }
To list metadata about SQL statements
To list metadata about SQL statements, use the aws redshift-data
list-statements AWS CLI command. Authorization to run this command is
based on the caller's IAM permissions.
The following AWS CLI command lists SQL statements that ran.
aws redshift-data list-statements --region us-west-2 --status ALL
The following is an example of the response.
{ "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 } ] }
To describe metadata about an SQL statement
To get descriptions of metadata for an SQL statement, use the aws
redshift-data describe-statement AWS CLI command. Authorization to run
this command is based on the caller's IAM permissions.
The following AWS CLI command describes an SQL statement.
aws redshift-data describe-statement --id d9b6c0c9-0747-4bf4-b142-e8883122f766 --region us-west-2
The following is an example of the response.
{ "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 }
The following is an example of a describe-statement response after
running a batch-execute-statement command with multiple SQL statements.
{ "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 }
To fetch the results of an SQL statement
To fetch the result from an SQL statement that ran, use the
redshift-data get-statement-result AWS CLI command. You can
provide an Id that you receive in response to
execute-statement or batch-execute-statement.
The Id value for an SQL statement run by batch-execute-statement can be retrieved in the
result of describe-statement and
is suffixed by a colon and sequence number such as b2906c76-fa6e-4cdf-8c5f-4de1ff9b7652:2.
If you run multiple SQL statements with batch-execute-statement, each
SQL statement has an Id value as shown in
describe-statement.
Authorization to run this command is based
on the caller's IAM permissions.
The following statement returns the result of an SQL statement run by execute-statement.
aws redshift-data get-statement-result --id d9b6c0c9-0747-4bf4-b142-e8883122f766 --region us-west-2
The following statement returns the result of the second SQL statement run by batch-execute-statement.
aws redshift-data get-statement-result --id b2906c76-fa6e-4cdf-8c5f-4de1ff9b7652:2 --region us-west-2
The following is an example of the response to a call to 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 }
To describe a table
To get metadata that describes a table, use the aws redshift-data
describe-table AWS CLI command.
The following AWS CLI command runs an SQL statement and returns metadata that describes a table. This example uses the AWS Secrets Manager authentication method.
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
The following is an example of the response.
{ "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" } ] }
The following AWS CLI command runs an SQL statement that describes a table. This example uses the temporary credentials authentication method.
aws redshift-data describe-table --region us-west-2 --db-user myuser --cluster-identifier mycluster-test --database dev --schema information_schema --table sql_features
The following is an example of the response.
{ "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" } ] }
To list the databases in a cluster
To list the databases in a cluster, use the aws redshift-data
list-databases AWS CLI command.
The following AWS CLI command runs an SQL statement to list databases. This example uses the AWS Secrets Manager authentication method.
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
The following is an example of the response.
{ "Databases": [ "dev" ] }
The following AWS CLI command runs an SQL statement to list databases. This example uses the temporary credentials authentication method.
aws redshift-data list-databases --region us-west-2 --db-user myuser --cluster-identifier mycluster-test --database dev
The following is an example of the response.
{ "Databases": [ "dev" ] }
To list the schemas in a database
To list the schemas in a database, use the aws redshift-data
list-schemas AWS CLI command.
The following AWS CLI command runs an SQL statement to list schemas in a database. This example uses the AWS Secrets Manager authentication method.
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
The following is an example of the response.
{ "Schemas": [ "information_schema", "pg_catalog", "pg_internal", "public" ] }
The following AWS CLI command runs an SQL statement to list schemas in a database. This example uses the temporary credentials authentication method.
aws redshift-data list-schemas --region us-west-2 --db-user mysuser --cluster-identifier mycluster-test --database dev
The following is an example of the response.
{ "Schemas": [ "information_schema", "pg_catalog", "pg_internal", "public" ] }
To list the tables in a database
To list the tables in a database, use the aws redshift-data
list-tables AWS CLI command.
The following AWS CLI command runs an SQL statement to list tables in a database. This example uses the AWS Secrets Manager authentication method.
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
The following is an example of the response.
{ "Tables": [ { "name": "sql_features", "schema": "information_schema", "type": "SYSTEM TABLE" }, { "name": "sql_implementation_info", "schema": "information_schema", "type": "SYSTEM TABLE" } }
The following AWS CLI command runs an SQL statement to list tables in a database. This example uses the temporary credentials authentication method.
aws redshift-data list-tables --region us-west-2 --db-user myuser --cluster-identifier mycluster-test --database dev --schema information_schema
The following is an example of the response.
{ "Tables": [ { "name": "sql_features", "schema": "information_schema", "type": "SYSTEM TABLE" }, { "name": "sql_implementation_info", "schema": "information_schema", "type": "SYSTEM TABLE" } ] }
Troubleshooting issues for Amazon Redshift Data API
Use the following sections, titled with common error messages, to help troubleshoot problems that you have with the Data API.
Packet for query is too large
If you see an error indicating that the packet for a query is too large, generally the result set returned for a row is too large. The Data API size limit is 64 KB per row in the result set returned by the database.
To solve this issue, make sure that each row in a result set is 64 KB or less.
Database response exceeded size limit
If you see an error indicating that the database response has exceeded the size limit, generally the size of the result set returned by the database was too large. The Data API limit is 100 MB in the result set returned by the database.
To solve this issue, make sure that calls to the Data API return 100 MB of
data or less. If you need to return more than 100 MB, you can run multiple statement
calls with the LIMIT clause in your query.
Scheduling Amazon Redshift Data API operations with Amazon EventBridge
Amazon EventBridge helps you to respond to state changes in your AWS resources. When your resources change state, they automatically send events into an event stream. Events are sent to the account that contains the Amazon Redshift database. You can create rules that match selected events in the stream and route them to targets to take action. You can also use rules to take action on a predetermined schedule. For more information, see the Amazon EventBridge User Guide.
To schedule Data API operations with EventBridge, the associated IAM role must trust
the principal for CloudWatch Events (events.amazonaws.com). This role should have
the equivalent of
the managed policy AmazonEventBridgeFullAccess attached. It should also
have AmazonRedshiftDataFullAccess policy permissions that are managed by
the Data API. You can create an IAM role with these permissions on the IAM
console. When creating a role on the IAM console, choose the AWS service trusted
entity for CloudWatch Events. For more information about creating an IAM role, see
Creating a Role for an AWS Service (Console) in the
IAM User Guide.
The following example uses the AWS CLI to create an EventBridge rule that is used to run an SQL statement.
aws events put-rule --name test-redshift-data --schedule-expression "rate(1 minute)"
Then an EventBridge target is created to run on the schedule specified in the rule.
aws events put-targets --cli-input-json file://data.json
The input data.json file is as follows.
{ "Rule": "test-redshift-data", "EventBusName": "default", "Targets": [ { "Id": "2", "Arn": "arn:aws:redshift:us-east-1:123456789012:cluster:mycluster", "RoleArn": "arn:aws:iam::123456789012:role/Administrator", "RedshiftDataParameters": { "Database": "dev", "DbUser": "root", "Sql": "select 1;", "StatementName": "test-scheduler-statement", "WithEvent": true } } ] }
