Calling the Amazon RDS Data API with the Amazon CLI - Amazon Aurora
Starting a SQL transactionRunning a SQL statementRunning a batch SQL statement over an array of dataCommitting a SQL transactionRolling back a SQL transaction
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Calling the Amazon RDS Data API with the Amazon CLI

You can call RDS Data API (Data API) using the Amazon CLI.

The following examples use the Amazon CLI for Data API. For more information, see Amazon CLI reference for the Data API.

In each example, replace the Amazon Resource Name (ARN) for the DB cluster with the ARN for your Aurora DB cluster. Also, replace the secret ARN with the ARN of the secret in Secrets Manager that allows access to the DB cluster.

Note

The Amazon CLI can format responses in JSON.

Starting a SQL transaction

You can start a SQL transaction using the aws rds-data begin-transaction CLI command. The call returns a transaction identifier.

Important

Within Data API, a transaction times out if there are no calls that use its transaction ID in three minutes. If a transaction times out before it's committed, Data API rolls it back automatically.

MySQL data definition language (DDL) statements inside a transaction cause an implicit commit. We recommend that you run each MySQL DDL statement in a separate execute-statement command with the --continue-after-timeout option.

In addition to the common options, specify the --database option, which provides the name of the database.

For example, the following CLI command starts a SQL transaction.

For Linux, macOS, or Unix:

aws rds-data begin-transaction --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" \
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret"

For Windows:

aws rds-data begin-transaction --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" ^
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret"

The following is an example of the response.

{
    "transactionId": "ABC1234567890xyz"
}

Running a SQL statement

You can run a SQL statement using the aws rds-data execute-statement CLI command.

You can run the SQL statement in a transaction by specifying the transaction identifier with the --transaction-id option. You can start a transaction using the aws rds-data begin-transaction CLI command. You can end and commit a transaction using the aws rds-data commit-transaction CLI command.

Important

If you don't specify the --transaction-id option, changes that result from the call are committed automatically.

In addition to the common options, specify the following options:

  • --sql (required) – A SQL statement to run on the DB cluster.

  • --transaction-id (optional) – The identifier of a transaction that was started using the begin-transaction CLI command. Specify the transaction ID of the transaction that you want to include the SQL statement in.

  • --parameters (optional) – The parameters for the SQL statement.

  • --include-result-metadata | --no-include-result-metadata (optional) – A value that indicates whether to include metadata in the results. The default is --no-include-result-metadata.

  • --database (optional) – The name of the database.

    The --database option might not work when you run a SQL statement after running --sql "use database_name;" in the previous request. We recommend that you use the --database option instead of running --sql "use database_name;" statements.

  • --continue-after-timeout | --no-continue-after-timeout (optional) – A value that indicates whether to continue running the statement after the call exceeds the Data API timeout interval of 45 seconds. The default is --no-continue-after-timeout.

    For data definition language (DDL) statements, we recommend continuing to run the statement after the call times out to avoid errors and the possibility of corrupted data structures.

  • --format-records-as "JSON"|"NONE" – An optional value that specifies whether to format the result set as a JSON string. The default is "NONE". For usage information about processing JSON result sets, see Processing Amazon RDS Data API query results in JSON format.

The DB cluster returns a response for the call.

Note

The response size limit is 1 MiB. If the call returns more than 1 MiB of response data, the call is terminated.

For Aurora Serverless v1, the maximum number of requests per second is 1,000. For all other supported databases, there is no limit.

For example, the following CLI command runs a single SQL statement and omits the metadata in the results (the default).

For Linux, macOS, or Unix:

aws rds-data execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" \
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" \
--sql "select * from mytable"

For Windows:

aws rds-data execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" ^
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" ^
--sql "select * from mytable"

The following is an example of the response.

{
    "numberOfRecordsUpdated": 0,
    "records": [
        [
            {
                "longValue": 1
            },
            {
                "stringValue": "ValueOne"
            }
        ],
        [
            {
                "longValue": 2
            },
            {
                "stringValue": "ValueTwo"
            }
        ],
        [
            {
                "longValue": 3
            },
            {
                "stringValue": "ValueThree"
            }
        ]
    ]
}

The following CLI command runs a single SQL statement in a transaction by specifying the --transaction-id option.

For Linux, macOS, or Unix:

aws rds-data execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" \
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" \
--sql "update mytable set quantity=5 where id=201" --transaction-id "ABC1234567890xyz"

For Windows:

aws rds-data execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" ^
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" ^
--sql "update mytable set quantity=5 where id=201" --transaction-id "ABC1234567890xyz"

The following is an example of the response.

{
    "numberOfRecordsUpdated": 1
}

The following CLI command runs a single SQL statement with parameters.

For Linux, macOS, or Unix:

aws rds-data execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" \
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" \
--sql "insert into mytable values (:id, :val)" --parameters "[{\"name\": \"id\", \"value\": {\"longValue\": 1}},{\"name\": \"val\", \"value\": {\"stringValue\": \"value1\"}}]"

For Windows:

aws rds-data execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" ^
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" ^
--sql "insert into mytable values (:id, :val)" --parameters "[{\"name\": \"id\", \"value\": {\"longValue\": 1}},{\"name\": \"val\", \"value\": {\"stringValue\": \"value1\"}}]"

The following is an example of the response.

{
    "numberOfRecordsUpdated": 1
}

The following CLI command runs a data definition language (DDL) SQL statement. The DDL statement renames column job to column role.

Important

For DDL statements, we recommend continuing to run the statement after the call times out. When a DDL statement terminates before it is finished running, it can result in errors and possibly corrupted data structures. To continue running a statement after a call exceeds the RDS Data API timeout interval of 45 seconds, specify the --continue-after-timeout option.

For Linux, macOS, or Unix:

aws rds-data execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" \
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" \
--sql "alter table mytable change column job role varchar(100)" --continue-after-timeout

For Windows:

aws rds-data execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" ^
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" ^
--sql "alter table mytable change column job role varchar(100)" --continue-after-timeout

The following is an example of the response.

{
    "generatedFields": [],
    "numberOfRecordsUpdated": 0
}
Note

The generatedFields data isn't supported by Aurora PostgreSQL. To get the values of generated fields, use the RETURNING clause. For more information, see Returning data from modified rows in the PostgreSQL documentation.

Running a batch SQL statement over an array of data

You can run a batch SQL statement over an array of data by using the aws rds-data batch-execute-statement CLI command. You can use this command to perform a bulk import or update operation.

You can run the SQL statement in a transaction by specifying the transaction identifier with the --transaction-id option. You can start a transaction by using the aws rds-data begin-transaction CLI command. You can end and commit a transaction by using the aws rds-data commit-transaction CLI command.

Important

If you don't specify the --transaction-id option, changes that result from the call are committed automatically.

In addition to the common options, specify the following options:

  • --sql (required) – A SQL statement to run on the DB cluster.

    Tip

    For MySQL-compatible statements, don't include a semicolon at the end of the --sql parameter. A trailing semicolon might cause a syntax error.

  • --transaction-id (optional) – The identifier of a transaction that was started using the begin-transaction CLI command. Specify the transaction ID of the transaction that you want to include the SQL statement in.

  • --parameter-set (optional) – The parameter sets for the batch operation.

  • --database (optional) – The name of the database.

The DB cluster returns a response to the call.

Note

There isn't a fixed upper limit on the number of parameter sets. However, the maximum size of the HTTP request submitted through Data API is 4 MiB. If the request exceeds this limit, Data API returns an error and doesn't process the request. This 4 MiB limit includes the size of the HTTP headers and the JSON notation in the request. Thus, the number of parameter sets that you can include depends on a combination of factors, such as the size of the SQL statement and the size of each parameter set.

The response size limit is 1 MiB. If the call returns more than 1 MiB of response data, the call is terminated.

For Aurora Serverless v1, the maximum number of requests per second is 1,000. For all other supported databases, there is no limit.

For example, the following CLI command runs a batch SQL statement over an array of data with a parameter set.

For Linux, macOS, or Unix:

aws rds-data batch-execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" \
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" \
--sql "insert into mytable values (:id, :val)" \
--parameter-sets "[[{\"name\": \"id\", \"value\": {\"longValue\": 1}},{\"name\": \"val\", \"value\": {\"stringValue\": \"ValueOne\"}}],
[{\"name\": \"id\", \"value\": {\"longValue\": 2}},{\"name\": \"val\", \"value\": {\"stringValue\": \"ValueTwo\"}}],
[{\"name\": \"id\", \"value\": {\"longValue\": 3}},{\"name\": \"val\", \"value\": {\"stringValue\": \"ValueThree\"}}]]"

For Windows:

aws rds-data batch-execute-statement --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" ^
--database "mydb" --secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" ^
--sql "insert into mytable values (:id, :val)" ^
--parameter-sets "[[{\"name\": \"id\", \"value\": {\"longValue\": 1}},{\"name\": \"val\", \"value\": {\"stringValue\": \"ValueOne\"}}],
[{\"name\": \"id\", \"value\": {\"longValue\": 2}},{\"name\": \"val\", \"value\": {\"stringValue\": \"ValueTwo\"}}],
[{\"name\": \"id\", \"value\": {\"longValue\": 3}},{\"name\": \"val\", \"value\": {\"stringValue\": \"ValueThree\"}}]]"
Note

Don't include line breaks in the --parameter-sets option.

Committing a SQL transaction

Using the aws rds-data commit-transaction CLI command, you can end a SQL transaction that you started with aws rds-data begin-transaction and commit the changes.

In addition to the common options, specify the following option:

  • --transaction-id (required) – The identifier of a transaction that was started using the begin-transaction CLI command. Specify the transaction ID of the transaction that you want to end and commit.

For example, the following CLI command ends a SQL transaction and commits the changes.

For Linux, macOS, or Unix:

aws rds-data commit-transaction --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" \
--secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" \
--transaction-id "ABC1234567890xyz"

For Windows:

aws rds-data commit-transaction --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" ^
--secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" ^
--transaction-id "ABC1234567890xyz"

The following is an example of the response.

{
    "transactionStatus": "Transaction Committed"
}

Rolling back a SQL transaction

Using the aws rds-data rollback-transaction CLI command, you can roll back a SQL transaction that you started with aws rds-data begin-transaction. Rolling back a transaction cancels its changes.

Important

If the transaction ID has expired, the transaction was rolled back automatically. In this case, an aws rds-data rollback-transaction command that specifies the expired transaction ID returns an error.

In addition to the common options, specify the following option:

  • --transaction-id (required) – The identifier of a transaction that was started using the begin-transaction CLI command. Specify the transaction ID of the transaction that you want to roll back.

For example, the following Amazon CLI command rolls back a SQL transaction.

For Linux, macOS, or Unix:

aws rds-data rollback-transaction --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" \
--secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" \
--transaction-id "ABC1234567890xyz"

For Windows:

aws rds-data rollback-transaction --resource-arn "arn:aws-cn:rds:us-east-1:123456789012:cluster:mydbcluster" ^
--secret-arn "arn:aws-cn:secretsmanager:us-east-1:123456789012:secret:mysecret" ^
--transaction-id "ABC1234567890xyz"

The following is an example of the response.

{
    "transactionStatus": "Rollback Complete"
    }