developersIO
[Update] Trying out Amazon Redshift Data API long polling, list session, and flexible batch execution

[Update] Trying out Amazon Redshift Data API long polling, list session, and flexible batch execution

I actually tried out the three new features added to Amazon Redshift Data API: long polling, session list retrieval, and flexible batch execution.
AWSAmazon RedshiftAmazon Redshift ServerlessAmazon Redshift Data API
2026.07.30

This page has been translated by machine translation. View original

This is Ishikawa from the Cloud Business Division. Long polling, list session, and flexible batch execution have been added to the Amazon Redshift Data API, so I tried them out.

https://aws.amazon.com/jp/about-aws/whats-new/2026/07/amazon-redshift-data-api-longpolling-listsession-flexiblebatchexecute/

The Amazon Redshift Data API is a feature that allows you to execute SQL against Amazon Redshift via HTTPS-based API calls without managing JDBC/ODBC drivers or persistent connections. It is commonly used when working with Amazon Redshift from AWS Lambda or AWS Step Functions.

This Data API was fundamentally based on an asynchronous model. When you submit SQL with ExecuteStatement, only a statement ID is returned, and you had to repeatedly call DescribeStatement to wait for completion, then retrieve results with GetStatementResult. This polling loop had to be implemented on the caller's side.

With the July 29, 2026 update, three features were added that move this boilerplate processing to the API side: long polling (WaitTimeSeconds), session list retrieval (ListSessions), and flexible batch execution (ExecutionMode and common parameters). It is generally available for both Amazon Redshift provisioned clusters and Amazon Redshift Serverless, in all AWS commercial regions and AWS GovCloud (US) regions where the Data API is available.

https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html

The Three New Features

Long Polling

Long polling is a mechanism where, by specifying a value between 1 and 30 seconds in WaitTimeSeconds, the API delays returning the response until either the statement completes or the wait time is reached, whichever comes first. The supported operations are five: ExecuteStatement, BatchExecuteStatement, DescribeStatement, GetStatementResult, and GetStatementResultV2.

https://docs.aws.amazon.com/redshift/latest/mgmt/data-api-calling-considerations-long-polling.html

Session List Retrieval and Management (ListSessions)

ListSessions is an API that lists sessions created by the caller in the past 24 hours. By default, it returns AVAILABLE or BUSY sessions, and can be filtered by status, target compute, and database.

https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ListSessions.html

Flexible Batch Execution

Flexible batch execution is the ExecutionMode parameter added to BatchExecuteStatement. The default TRANSACTION treats all SQL as a single transaction, but specifying AUTO_COMMIT causes each SQL to be committed individually. Additionally, Parameters (an array of SqlParameter) is now supported, allowing multiple statements within a batch to share the same parameters.

https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_BatchExecuteStatement.html

Trying It Out

Prerequisites

  • Region: Tokyo (ap-northeast-1)
  • AWS CLI: aws-cli/2.36.9
  • Test environment: Amazon Redshift Serverless (base capacity 4 RPU)
  • Authentication: Temporary IAM credentials (specifying only --workgroup-name and --database. DB user is IAM identity)

The execution results in this article are the actual output as-is, but the AWS account ID has been replaced with 123456789012, and the DB username derived from the IAM role has been replaced with IAMR:cm-user. Status, timestamps, execution times, row counts, and error messages are actual values.

Creating Tables for Verification

1. Creating Verification Data (Orders and Items Tables)

-- Creating the orders table and inserting test data
CREATE TABLE public.blog_orders (
    order_id INT,
    load_date DATE,
    amount DECIMAL(10,2)
);

INSERT INTO public.blog_orders VALUES 
    (1, '2026-07-29', 100.00),
    (2, '2026-07-29', 200.00),
    (3, '2026-07-30', 300.00);

-- Creating the items table and inserting test data
CREATE TABLE public.blog_items (
    item_id INT,
    load_date DATE,
    qty INT
);

INSERT INTO public.blog_items VALUES 
    (1, '2026-07-29', 5),
    (2, '2026-07-29', 3),
    (3, '2026-07-30', 7);

2. Creating Large-Scale Test Data (1 Million Rows)

-- Generating seed data (numbers 1 to 10)
CREATE TABLE public.blog_seed AS 
SELECT 1 AS x 
UNION ALL SELECT 2 
UNION ALL SELECT 3 
UNION ALL SELECT 4 
UNION ALL SELECT 5 
UNION ALL SELECT 6 
UNION ALL SELECT 7 
UNION ALL SELECT 8 
UNION ALL SELECT 9 
UNION ALL SELECT 10;

-- Creating a 1 million row table by cross joining the seed table 6 times (10^6 = 1,000,000 rows)
CREATE TABLE public.blog_big AS 
SELECT 
    a.x + 
    b.x * 10 + 
    c.x * 100 + 
    d.x * 1000 + 
    e.x * 10000 + 
    f.x * 100000 AS n 
FROM 
    public.blog_seed a, 
    public.blog_seed b, 
    public.blog_seed c, 
    public.blog_seed d, 
    public.blog_seed e, 
    public.blog_seed f;

3. Confirming the Row Count of the Created Large-Scale Data

-- Counting the number of rows created (result: 1,000,000 rows)
SELECT COUNT(*) FROM public.blog_big;

4. Creating Tables for Batch Comparison Verification

-- Table for transaction verification
CREATE TABLE public.batch_tx_test (
    id INT, 
    note VARCHAR(20)
);

-- Table for auto-commit/individual processing verification
CREATE TABLE public.batch_ac_test (
    id INT, 
    note VARCHAR(20)
);

5. Disabling the Query Cache

ALTER USER "IAMR:cm-user" SET enable_result_cache_for_session TO off;

Confirming the Conventional Asynchronous Execution

For comparison, let's first confirm the conventional behavior without specifying WaitTimeSeconds.

% time aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT 1 AS test"
{
    "Id": "f3bb2899-af18-4f6d-b21c-9b18a871f390",
    "CreatedAt": "2026-07-30T20:27:45.967000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg"
}
aws redshift-data execute-statement --workgroup-name dataapi-blog-wg  dev    0.21s user 0.10s system 29% cpu 1.058 total

A statement ID is returned, but Status is not included. Even for a query that completes in an instant like SELECT 1, you need to separately call DescribeStatement to know when it's done.

Trying Long Polling

Run the same SQL with --wait-time-seconds 30 added.

% time aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT 1 AS test" \
  --wait-time-seconds 30
{
    "Id": "f1afa90d-c749-417a-83a8-3e91d10f70ab",
    "CreatedAt": "2026-07-30T20:28:12.374000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg",
    "Status": "FINISHED",
    "RedshiftPid": 1073832146,
    "HasResultSet": true
}
aws redshift-data execute-statement --workgroup-name dataapi-blog-wg  dev      0.23s user 0.09s system 25% cpu 1.232 total

Three fields have been added: Status, RedshiftPid, and HasResultSet. With a single call, we can confirm it has reached FINISHED.

Confirming That Long Polling Actually Waits

With SELECT 1, it's too fast to tell whether it actually "waited." So I measured using a query that cross joins the 1 million row table with the 10-row seed table twice and calculates md5 on 100 million rows.

% time aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT COUNT(DISTINCT md5(a.n::varchar || b.x::varchar || c.x::varchar)) FROM public.blog_big a, public.blog_seed b, public.blog_seed c" \
  --wait-time-seconds 30
{
    "Id": "d83406a7-55df-4214-858c-59aff6e35ff4",
    "CreatedAt": "2026-07-30T20:28:42.774000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg",
    "Status": "FINISHED",
    "RedshiftPid": 1073897683,
    "HasResultSet": true
}
aws redshift-data execute-statement --workgroup-name dataapi-blog-wg  dev      0.23s user 0.09s system 2% cpu 12.368 total

The API call was held for 12.368 seconds, and FINISHED was returned. Let's check the server-side execution time with DescribeStatement.

% aws redshift-data describe-statement --id d83406a7-55df-4214-858c-59aff6e35ff4
{
    "Id": "d83406a7-55df-4214-858c-59aff6e35ff4",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "Duration": 10943844591,
    "Status": "FINISHED",
    "CreatedAt": "2026-07-30T20:28:42.774000+09:00",
    "UpdatedAt": "2026-07-30T20:28:54.367000+09:00",
    "RedshiftPid": 1073897683,
    "HasResultSet": true,
    "QueryString": "SELECT COUNT(DISTINCT md5(a.n::varchar || b.x::varchar || c.x::varchar)) FROM public.blog_big a, public.blog_seed b, public.blog_seed c",
    "ResultRows": 1,
    "ResultSize": 20,
    "RedshiftQueryId": 1837,
    "WorkgroupName": "dataapi-blog-wg",
    "ResultFormat": "json"
}

Duration is 10943844591 nanoseconds, which is 10.94 seconds. We can see that the API waited for exactly the duration of the query execution and returned the status as soon as it completed. Conventionally, you would have needed to call DescribeStatement multiple times during those 11 seconds.

One thing to note here: when I ran the same query again with --wait-time-seconds 5, it completed without waiting for the wait time. This was because Amazon Redshift's result cache kicked in, and the query that had taken 10.94 seconds returned in essentially zero seconds. If you want to verify long polling behavior, you need to measure with a different query string.

Confirming Behavior When Wait Time Expires

The upper limit for WaitTimeSeconds is 30 seconds. Let's see what happens with processing that exceeds that. To avoid the result cache, I used a different query with the string 'v13' appended to the previous query, and specified --wait-time-seconds 5.

% time aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT COUNT(DISTINCT md5(a.n::varchar || b.x::varchar || c.x::varchar || 'v13')) FROM public.blog_big a, public.blog_seed b, public.blog_seed c" \
  --wait-time-seconds 5
{
    "Id": "a4940560-6e1a-4d82-98d1-ff8b52e3c185",
    "CreatedAt": "2026-07-30T20:44:03.030000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg",
    "Status": "STARTED",
    "RedshiftPid": 1073963131,
    "HasResultSet": false
}
aws redshift-data execute-statement --workgroup-name dataapi-blog-wg  dev      0.21s user 0.11s system 6% cpu 5.038 total

It returned in exactly 5.038 seconds, with STARTED indicating it is still running. Next, I specify long polling in DescribeStatement to wait for the remainder.

% time aws redshift-data describe-statement \
  --id a4940560-6e1a-4d82-98d1-ff8b52e3c185 \
  --wait-time-seconds 30
{
    "Id": "a4940560-6e1a-4d82-98d1-ff8b52e3c185",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "Duration": 10949178655,
    "Status": "FINISHED",
    "CreatedAt": "2026-07-30T20:44:03.030000+09:00",
    "UpdatedAt": "2026-07-30T20:44:14.590000+09:00",
    "RedshiftPid": 1073963131,
    "HasResultSet": true,
    "QueryString": "SELECT COUNT(DISTINCT md5(a.n::varchar || b.x::varchar || c.x::varchar || 'v13')) FROM public.blog_big a, public.blog_seed b, public.blog_seed c",
    "ResultRows": 1,
    "ResultSize": 20,
    "RedshiftQueryId": 203141,
    "WorkgroupName": "dataapi-blog-wg",
    "ResultFormat": "json"
}
aws redshift-data describe-statement --id a4940560-6e1a-4d82-98d1-ff8b52e3c18  0.23s user 0.09s system 52% cpu 0.614 total

5.038 seconds + 0.614 seconds = approximately 5.65 seconds, and adding the 5 seconds between command executions, this roughly matches the server-side execution time of approximately 11 seconds. Even for processing that exceeds 30 seconds, the flow is to receive the wait-time-expired response and then long poll again using the same statement ID.

I also verified the case where --wait-time-seconds is set even shorter, to 1 second. This is a lighter query that cross joins 1 million rows with 10 rows only once.

% time aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT COUNT(DISTINCT md5(a.n::varchar || b.x::varchar)) FROM public.blog_big a, public.blog_seed b" \
  --wait-time-seconds 1
{
    "Id": "3d9b375e-0cf7-4de9-8a91-1deda1af19b8",
    "CreatedAt": "2026-07-30T20:52:39.798000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg",
    "Status": "PICKED",
    "HasResultSet": false
}
aws redshift-data execute-statement --workgroup-name dataapi-blog-wg  dev      0.23s user 0.09s system 31% cpu 1.024 total

PICKED (the stage where it has been selected for execution) was returned. At this point, RedshiftPid is not included. This is likely because a process has not yet been assigned at this stage.

When I followed this statement with DescribeStatement, it had already completed.

% time aws redshift-data describe-statement --id 3d9b375e-0cf7-4de9-8a91-1deda1af19b8 --wait-time-seconds 30
{
    "Id": "3d9b375e-0cf7-4de9-8a91-1deda1af19b8",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "Duration": 1252004741,
    "Status": "FINISHED",
    "CreatedAt": "2026-07-30T20:52:39.798000+09:00",
    "UpdatedAt": "2026-07-30T20:52:41.648000+09:00",
    "RedshiftPid": 1073971314,
    "HasResultSet": true,
    "QueryString": "SELECT COUNT(DISTINCT md5(a.n::varchar || b.x::varchar)) FROM public.blog_big a, public.blog_seed b",
    "ResultRows": 1,
    "ResultSize": 20,
    "RedshiftQueryId": 203348,
    "WorkgroupName": "dataapi-blog-wg",
    "ResultFormat": "json"
}
aws redshift-data describe-statement --id 3d9b375e-0cf7-4de9-8a91-1deda1af19b  0.22s user 0.11s system 46% cpu 0.719 total

The execution time was 1.25 seconds. With a setting of only 1 second wait, even a query of this level will exceed the wait time. Also, the call to a statement that had already completed returned immediately in 0.719 seconds, which is consistent with the official documentation's statement that "if already completed, it immediately returns the terminal status."

Result Retrieval Can Also Use Long Polling

You can specify WaitTimeSeconds for GetStatementResult as well. Let's try calling result retrieval immediately after submitting with ExecuteStatement, without going through DescribeStatement.

% ID=$(aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT load_date, COUNT(*) AS cnt, SUM(amount) AS total FROM public.blog_orders GROUP BY load_date ORDER BY load_date" \
  --query 'Id' --output text)

time aws redshift-data get-statement-result --id "${ID}" --wait-time-seconds 30
{
    "Records": [
        [
            {
                "stringValue": "2026-07-29"
            },
            {
                "longValue": 2
            },
            {
                "stringValue": "300.00"
            }
        ],
        [
            {
                "stringValue": "2026-07-30"
            },
            {
                "longValue": 1
            },
            {
                "stringValue": "300.00"
            }
        ]
    ],
    "ColumnMetadata": [
        {
            "isCaseSensitive": false,
            "isCurrency": false,
            "isSigned": false,
            "label": "load_date",
            "name": "load_date",
            "nullable": 1,
            "precision": 13,
            "scale": 0,
            "schemaName": "public",
            "tableName": "blog_orders",
            "typeName": "date",
            "length": 0
        },
        {
            "isCaseSensitive": false,
            "isCurrency": false,
            "isSigned": true,
            "label": "cnt",
            "name": "cnt",
            "nullable": 1,
            "precision": 19,
            "scale": 0,
            "schemaName": "",
            "tableName": "",
            "typeName": "int8",
            "length": 0
        },
        {
            "isCaseSensitive": false,
            "isCurrency": false,
            "isSigned": true,
            "label": "total",
            "name": "total",
            "nullable": 1,
            "precision": 38,
            "scale": 2,
            "schemaName": "",
            "tableName": "",
            "typeName": "numeric",
            "length": 0
        }
    ],
    "TotalNumRows": 2
}
aws redshift-data get-statement-result --id "${ID}" --wait-time-seconds 30  0.21s user 0.08s system 32% cpu 0.915 total

Results were obtained with just two calls: submission and result retrieval. The call for status confirmation is no longer necessary.

Trying ListSessions

First, maintain a session with --session-keep-alive-seconds and create a temporary table.

% aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "CREATE TEMP TABLE session_scratch (k VARCHAR(20), v INT)" \
  --session-keep-alive-seconds 300 \
  --wait-time-seconds 30
{
    "Id": "3e5c64ed-ab2a-4d24-8b0f-a576b7f2318a",
    "CreatedAt": "2026-07-30T21:47:18.053000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg",
    "SessionId": "39012909-75bb-4187-a1ec-b75873f7d593",
    "Status": "FINISHED",
    "RedshiftPid": 1073832059,
    "HasResultSet": false
}

Run list-sessions in this state.

% aws redshift-data list-sessions --workgroup-name dataapi-blog-wg
{
    "Sessions": [
        {
            "SessionId": "39012909-75bb-4187-a1ec-b75873f7d593",
            "Status": "AVAILABLE",
            "CreatedAt": "2026-07-30T21:47:17.859000+09:00",
            "UpdatedAt": "2026-07-30T21:47:18.583000+09:00",
            "Database": "dev",
            "DbUser": "IAMR:cm-user",
            "WorkgroupName": "dataapi-blog-wg",
            "SessionAliveSeconds": 300,
            "SessionTtl": "2026-07-30T21:52:18+09:00"
        }
    ]
}

The session expiry was returned as SessionTtl. Previously, the application side had to retain the SessionId, but now it can be listed after the fact.

Specify the retrieved SessionId to confirm that the temporary table has been carried over.

% aws redshift-data execute-statement \
  --session-id 39012909-75bb-4187-a1ec-b75873f7d593 \
  --sql "INSERT INTO session_scratch VALUES ('alpha', 1), ('beta', 2)" \
  --wait-time-seconds 30 --query 'Status' --output text
FINISHED

% RES=$(aws redshift-data execute-statement \
  --session-id 39012909-75bb-4187-a1ec-b75873f7d593 \
  --sql "SELECT k, v FROM session_scratch ORDER BY k" \
  --wait-time-seconds 30 --query 'Id' --output text)

aws redshift-data get-statement-result --id "${RES}" --wait-time-seconds 30
{
    "Records": [
        [
            {
                "stringValue": "alpha"
            },
            {
                "longValue": 1
            }
        ],
        [
            {
                "stringValue": "beta"
            },
            {
                "longValue": 2
            }
        ]
    ],
    "ColumnMetadata": [
        {
            "isCaseSensitive": true,
            "isCurrency": false,
            "isSigned": false,
            "label": "k",
            "name": "k",
            "nullable": 1,
            "precision": 20,
            "scale": 0,
            "schemaName": "pg_temp_8",
            "tableName": "session_scratch",
            "typeName": "varchar",
            "length": 0
        },
        {
            "isCaseSensitive": false,
            "isCurrency": false,
            "isSigned": true,
            "label": "v",
            "name": "v",
            "nullable": 1,
            "precision": 10,
            "scale": 0,
            "schemaName": "pg_temp_8",
            "tableName": "session_scratch",
            "typeName": "int4",
            "length": 0
        }
    ],
    "TotalNumRows": 2
}

The temporary table was accessible across sessions. We can also see from the schemaName in ColumnMetadata being pg_temp_14 that it was created in a session-specific temporary schema.

Next, let's try calling list-sessions while a query is running. I submit a heavy query asynchronously and immediately retrieve the list.

% aws redshift-data execute-statement \
  --session-id 39012909-75bb-4187-a1ec-b75873f7d593 \
  --sql "SELECT COUNT(DISTINCT md5(a.n::varchar || b.x::varchar || c.x::varchar || 'v17')) FROM public.blog_big a, public.blog_seed b, public.blog_seed c" \
  --query 'Id' --output text

78537eb3-3b95-4217-9893-1015249f7c4f



% aws redshift-data list-sessions --workgroup-name dataapi-blog-wg
{
    "Sessions": [
        {
            "SessionId": "39012909-75bb-4187-a1ec-b75873f7d593",
            "Status": "AVAILABLE",
            "CreatedAt": "2026-07-30T21:47:17.859000+09:00",
            "UpdatedAt": "2026-07-30T21:50:23.151000+09:00",
            "Database": "dev",
            "DbUser": "IAMR:cm-user",
            "WorkgroupName": "dataapi-blog-wg",
            "SessionAliveSeconds": 300,
            "SessionTtl": "2026-07-30T21:55:23+09:00"
        }
    ]
}

The Status became BUSY, and the submitted statement ID was directly in CurrentStatementId. Since you can see which session is blocked by which query, this seems useful for investigating when sessions are exhausted.

By specifying --session-id, you can retrieve only that session.

% aws redshift-data list-sessions --session-id 39012909-75bb-4187-a1ec-b75873f7d593
{
    "Sessions": [
        {
            "SessionId": "39012909-75bb-4187-a1ec-b75873f7d593",
            "Status": "AVAILABLE",
            "CreatedAt": "2026-07-30T21:47:17.859000+09:00",
            "UpdatedAt": "2026-07-30T21:51:09.723000+09:00",
            "Database": "dev",
            "DbUser": "IAMR:cm-user",
            "WorkgroupName": "dataapi-blog-wg",
            "SessionAliveSeconds": 300,
            "SessionTtl": "2026-07-30T21:55:23+09:00"
        }
    ]
}

Since the previous query has finished, it has returned to AVAILABLE and CurrentStatementId has also disappeared.

There are restrictions on filter conditions. --session-id cannot be used in combination with other filters.

% aws redshift-data list-sessions --session-id 39012909-75bb-4187-a1ec-b75873f7d593 --status AVAILABLE

aws: [ERROR]: An error occurred (ValidationException) when calling the ListSessions operation: SessionId cannot be specified with other filter parameters.

Next, let's check whether CLOSED sessions can also be retrieved. Since there is no operation to explicitly close a session in the Data API, I created a short-lived session with --session-keep-alive-seconds 1 and waited for the TTL to expire.

% aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT 'short-lived session' AS note" \
  --session-keep-alive-seconds 1 --wait-time-seconds 30 \
  --query 'SessionId' --output text

d22dd9dc-ffb5-44c5-b0c1-9fb74d260af9

% sleep 25
aws redshift-data list-sessions --workgroup-name dataapi-blog-wg --status CLOSED
{
    "Sessions": [
        {
            "SessionId": "d22dd9dc-ffb5-44c5-b0c1-9fb74d260af9",
            "Status": "CLOSED",
            "CreatedAt": "2026-07-30T21:53:02.796000+09:00",
            "UpdatedAt": "2026-07-30T21:53:17.410000+09:00",
            "Database": "dev",
            "DbUser": "IAMR:cm-user",
            "WorkgroupName": "dataapi-blog-wg",
            "SessionAliveSeconds": 1,
            "SessionTtl": "2026-07-30T21:53:04+09:00"
        }
    ]
}

CLOSED sessions, which are not returned by default, can be retrieved when explicitly specified.

Trying Flexible Batch Execution

This is the area where the behavioral differences were most significant this time. We'll run the same 3-statement batch with TRANSACTION (default) and AUTO_COMMIT to compare them. The second statement is intentionally made to fail with a division by zero error.

First, the default TRANSACTION.

% aws redshift-data batch-execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sqls "INSERT INTO public.batch_tx_test VALUES (1, 'first')" \
         "INSERT INTO public.batch_tx_test VALUES (1/0, 'boom')" \
         "INSERT INTO public.batch_tx_test VALUES (3, 'third')" \
  --wait-time-seconds 30
{
    "Id": "b32c3c58-3ffc-4e0b-9faa-74e50721f8b8",
    "CreatedAt": "2026-07-30T21:56:18.770000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg",
    "Status": "FAILED",
    "RedshiftPid": 1073938530,
    "HasResultSet": false
}

Thanks to long polling, FAILED was determined in the same call as the submission. Let's check the substatement statuses.

% aws redshift-data describe-statement --id b32c3c58-3ffc-4e0b-9faa-74e50721f8b8
{
    "Id": "b32c3c58-3ffc-4e0b-9faa-74e50721f8b8",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "Duration": 233169010,
    "Error": "Query #2 failed with ERROR: division by zero",
    "Status": "FAILED",
    "CreatedAt": "2026-07-30T21:56:18.770000+09:00",
    "UpdatedAt": "2026-07-30T21:56:20.018000+09:00",
    "RedshiftPid": 1073938530,
    "HasResultSet": false,
    "ResultRows": -1,
    "ResultSize": -1,
    "RedshiftQueryId": 0,
    "SubStatements": [
        {
            "Id": "b32c3c58-3ffc-4e0b-9faa-74e50721f8b8:1",
            "Duration": 233169010,
            "Status": "FINISHED",
            "CreatedAt": "2026-07-30T21:56:18.999000+09:00",
            "UpdatedAt": "2026-07-30T21:56:19.977000+09:00",
            "QueryString": "INSERT INTO public.batch_tx_test VALUES (1, 'first')",
            "ResultRows": 1,
            "ResultSize": 0,
            "RedshiftQueryId": 405066,
            "HasResultSet": false
        },
        {
            "Id": "b32c3c58-3ffc-4e0b-9faa-74e50721f8b8:2",
            "Duration": -1,
            "Error": "ERROR: division by zero",
            "Status": "FAILED",
            "CreatedAt": "2026-07-30T21:56:19.005000+09:00",
            "UpdatedAt": "2026-07-30T21:56:19.977000+09:00",
            "QueryString": "INSERT INTO public.batch_tx_test VALUES (1/0, 'boom')",
            "ResultRows": -1,
            "ResultSize": -1,
            "RedshiftQueryId": 405066,
            "HasResultSet": false
        },
        {
            "Id": "b32c3c58-3ffc-4e0b-9faa-74e50721f8b8:3",
            "Duration": -1,
            "Error": "Connection or an prior query failed.",
            "Status": "ABORTED",
            "CreatedAt": "2026-07-30T21:56:19.010000+09:00",
            "UpdatedAt": "2026-07-30T21:56:19.977000+09:00",
            "QueryString": "INSERT INTO public.batch_tx_test VALUES (3, 'third')",
            "ResultRows": -1,
            "ResultSize": -1,
            "RedshiftQueryId": 0,
            "HasResultSet": false
        }
    ],
    "WorkgroupName": "dataapi-blog-wg",
    "ResultFormat": "json"
}

The third is ABORTED with the error Connection or an prior query failed., indicating it was not executed. The fact that the first and second statements share the same RedshiftQueryId of 1881 also indicates they were handled as a single transaction.

Next, let's do the same with AUTO_COMMIT.

% aws redshift-data batch-execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --execution-mode AUTO_COMMIT \
  --sqls "INSERT INTO public.batch_ac_test VALUES (1, 'first')" \
         "INSERT INTO public.batch_ac_test VALUES (1/0, 'boom')" \
         "INSERT INTO public.batch_ac_test VALUES (3, 'third')" \
  --wait-time-seconds 30
{
    "Id": "128682c9-5fb9-4fb4-99a6-ec5bb54541ec",
    "CreatedAt": "2026-07-30T21:58:02.140000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg",
    "Status": "FAILED",
    "RedshiftPid": 1073791095,
    "HasResultSet": false
}

The parent status is FAILED, the same as with TRANSACTION. Let's check the substatements.

% aws redshift-data describe-statement --id 128682c9-5fb9-4fb4-99a6-ec5bb54541ec
{
    "Id": "128682c9-5fb9-4fb4-99a6-ec5bb54541ec",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "Duration": 515891245,
    "Error": "Queries failed in AUTO_COMMIT mode: [2]",
    "Status": "FAILED",
    "CreatedAt": "2026-07-30T21:58:02.140000+09:00",
    "UpdatedAt": "2026-07-30T21:58:03.529000+09:00",
    "RedshiftPid": 1073791095,
    "HasResultSet": false,
    "ResultRows": -1,
    "ResultSize": -1,
    "RedshiftQueryId": 0,
    "SubStatements": [
        {
            "Id": "128682c9-5fb9-4fb4-99a6-ec5bb54541ec:1",
            "Duration": 244527325,
            "Status": "FINISHED",
            "CreatedAt": "2026-07-30T21:58:02.361000+09:00",
            "UpdatedAt": "2026-07-30T21:58:03.021000+09:00",
            "QueryString": "INSERT INTO public.batch_ac_test VALUES (1, 'first')",
            "ResultRows": 1,
            "ResultSize": 0,
            "RedshiftQueryId": 405117,
            "HasResultSet": false
        },
        {
            "Id": "128682c9-5fb9-4fb4-99a6-ec5bb54541ec:2",
            "Duration": -1,
            "Error": "ERROR: division by zero",
            "Status": "FAILED",
            "CreatedAt": "2026-07-30T21:58:02.367000+09:00",
            "UpdatedAt": "2026-07-30T21:58:03.109000+09:00",
            "QueryString": "INSERT INTO public.batch_ac_test VALUES (1/0, 'boom')",
            "ResultRows": -1,
            "ResultSize": -1,
            "RedshiftQueryId": 405117,
            "HasResultSet": false
        },
        {
            "Id": "128682c9-5fb9-4fb4-99a6-ec5bb54541ec:3",
            "Duration": 271363920,
            "Status": "FINISHED",
            "CreatedAt": "2026-07-30T21:58:02.372000+09:00",
            "UpdatedAt": "2026-07-30T21:58:03.468000+09:00",
            "QueryString": "INSERT INTO public.batch_ac_test VALUES (3, 'third')",
            "ResultRows": 1,
            "ResultSize": 0,
            "RedshiftQueryId": 405122,
            "HasResultSet": false
        }
    ],
    "WorkgroupName": "dataapi-blog-wg",
    "ResultFormat": "json",
    "ExecutionMode": "AUTO_COMMIT"
}

The third statement is now FINISHED. Statements after the failed one were also executed. Additionally, comparing the raw outputs side by side reveals the following differences.

  • The parent Error becomes Queries failed in AUTO_COMMIT mode: [2], where the numbers of the failed statements are shown as an array. This differs from TRANSACTION's expression of Query #2 failed with ERROR: division by zero
  • "ExecutionMode": "AUTO_COMMIT" is appended at the end of the response. The earlier output executed with TRANSACTION (default) does not have this field at all
  • The third statement's RedshiftQueryId is 1897, separated from the 1892 shared by the first and second statements

Let's compare the rows that actually remained in the tables.

% aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT 'TRANSACTION' AS mode, COALESCE(SUM(1),0) AS rows_left, LISTAGG(note, ',') AS notes FROM public.batch_tx_test UNION ALL SELECT 'AUTO_COMMIT', COALESCE(SUM(1),0), LISTAGG(note, ',') FROM public.batch_ac_test" \
  --wait-time-seconds 30 --query 'Id' --output text

ce3a3840-e574-4385-a4e3-fe8950a45a0a

% aws redshift-data get-statement-result --id ce3a3840-e574-4385-a4e3-fe8950a45a0a

{
    "Records": [
        [
            {
                "stringValue": "TRANSACTION"
            },
            {
                "longValue": 0
            },
            {
                "isNull": true
            }
        ],
        [
            {
                "stringValue": "AUTO_COMMIT"
            },
            {
                "longValue": 2
            },
            {
                "stringValue": "first,third"
            }
        ]
    ],
    "ColumnMetadata": [
        {
            "isCaseSensitive": true,
            "isCurrency": false,
            "isSigned": false,
            "label": "mode",
            "name": "mode",
            "nullable": 1,
            "precision": 11,
            "scale": 0,
            "schemaName": "",
            "tableName": "",
            "typeName": "varchar",
            "length": 0
        },
        {
            "isCaseSensitive": false,
            "isCurrency": false,
            "isSigned": true,
            "label": "rows_left",
            "name": "rows_left",
            "nullable": 1,
            "precision": 19,
            "scale": 0,
            "schemaName": "",
            "tableName": "",
            "typeName": "int8",
            "length": 0
        },
        {
            "isCaseSensitive": true,
            "isCurrency": false,
            "isSigned": false,
            "label": "notes",
            "name": "notes",
            "nullable": 1,
            "precision": 65535,
            "scale": 0,
            "schemaName": "",
            "tableName": "",
            "typeName": "varchar",
            "length": 0
        }
    ],
    "TotalNumRows": 2
}

TRANSACTION has 0 rows (notes is isNull), while AUTO_COMMIT has 2 rows with first,third remaining. The TRANSACTION side had the first substatement recorded as ResultRows: 1, yet the table is empty, making it clear that a rollback occurred.

Sharing Parameters Within a Batch

Let's verify whether values passed with --parameters can be referenced from multiple statements within a batch.

% aws redshift-data batch-execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --execution-mode AUTO_COMMIT \
  --sqls "DELETE FROM public.blog_orders WHERE load_date = :load_date" \
         "DELETE FROM public.blog_items WHERE load_date = :load_date" \
         "INSERT INTO public.blog_orders VALUES (99, :load_date, 999.00)" \
  --parameters name=load_date,value=2026-07-29 \
  --wait-time-seconds 30
{
    "Id": "b88a807d-f848-46f5-a6a4-18b2d880fe1c",
    "CreatedAt": "2026-07-30T22:04:41.517000+09:00",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "WorkgroupName": "dataapi-blog-wg",
    "Status": "FINISHED",
    "RedshiftPid": 1073897582,
    "HasResultSet": false
}

% aws redshift-data describe-statement --id b88a807d-f848-46f5-a6a4-18b2d880fe1c
{
    "Id": "b88a807d-f848-46f5-a6a4-18b2d880fe1c",
    "DbUser": "IAMR:cm-user",
    "Database": "dev",
    "Duration": 1251001472,
    "Status": "FINISHED",
    "CreatedAt": "2026-07-30T22:04:41.517000+09:00",
    "UpdatedAt": "2026-07-30T22:04:43.676000+09:00",
    "RedshiftPid": 1073897582,
    "HasResultSet": false,
    "ResultRows": -1,
    "ResultSize": -1,
    "RedshiftQueryId": 0,
    "QueryParameters": [
        {
            "name": "load_date",
            "value": "2026-07-29"
        }
    ],
    "SubStatements": [
        {
            "Id": "b88a807d-f848-46f5-a6a4-18b2d880fe1c:1",
            "Duration": 616451380,
            "Status": "FINISHED",
            "CreatedAt": "2026-07-30T22:04:41.784000+09:00",
            "UpdatedAt": "2026-07-30T22:04:42.819000+09:00",
            "QueryString": "DELETE FROM public.blog_orders WHERE load_date = :load_date",
            "ResultRows": 2,
            "ResultSize": 0,
            "RedshiftQueryId": 405297,
            "HasResultSet": false
        },
        {
            "Id": "b88a807d-f848-46f5-a6a4-18b2d880fe1c:2",
            "Duration": 294325118,
            "Status": "FINISHED",
            "CreatedAt": "2026-07-30T22:04:41.789000+09:00",
            "UpdatedAt": "2026-07-30T22:04:43.189000+09:00",
            "QueryString": "DELETE FROM public.blog_items WHERE load_date = :load_date",
            "ResultRows": 2,
            "ResultSize": 0,
            "RedshiftQueryId": 405303,
            "HasResultSet": false
        },
        {
            "Id": "b88a807d-f848-46f5-a6a4-18b2d880fe1c:3",
            "Duration": 340224974,
            "Status": "FINISHED",
            "CreatedAt": "2026-07-30T22:04:41.795000+09:00",
            "UpdatedAt": "2026-07-30T22:04:43.609000+09:00",
            "QueryString": "INSERT INTO public.blog_orders VALUES (99, :load_date, 999.00)",
            "ResultRows": 1,
            "ResultSize": 0,
            "RedshiftQueryId": 405309,
            "HasResultSet": false
        }
    ],
    "WorkgroupName": "dataapi-blog-wg",
    "ResultFormat": "json",
    "ExecutionMode": "AUTO_COMMIT"
}

A single load_date was referenced by all 3 statements, processing 2 rows, 2 rows, and 1 row respectively. Since the values passed as QueryParameters are recorded, you can also trace back what was specified at execution time. This eliminates the need to embed common values like dates or partition keys directly into SQL.

The official documentation states that "parameters you specify must be referenced by at least one SQL in the batch." When I tried passing an unreferenced parameter just to see what would happen, the following error was returned.

% aws redshift-data batch-execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sqls "SELECT :used AS a" \
  --parameters name=used,value=1 name=unused,value=2 \
  --wait-time-seconds 30

aws: [ERROR]: An error occurred (ValidationException) when calling the BatchExecuteStatement operation: Parameters list contains unused parameters. Unused parameters: [unused]

Since the names of unused parameters are shown, identifying the cause is straightforward. Implementations that reuse a common set of parameters while varying only the SQL combination may run into this constraint.

Discussion

Here is a summary of what we learned from actual testing.

For long polling, we confirmed a hold of 12.368 seconds for a query with an execution time of 11 seconds. Since polling loops can be replaced with --wait-time-seconds, architectures that call the Data API from AWS Lambda can eliminate the loops and sleeps used for waiting. However, since the upper limit is 30 seconds, any processing that exceeds this will require an implementation that receives a wait-timeout response and initiates long polling again. In practice, with --wait-time-seconds 5, STARTED was returned in 5.038 seconds, and FINISHED was returned after approximately 6 seconds had elapsed.

For long polling of GetStatementResult, if results are not ready within the wait time, a ResourceNotFoundException occurs. An implementation that judges failure based solely on the error code will produce false positives, so this state needs to be treated as "results not yet available."

With ListSessions, CurrentStatementId is returned, allowing you to track which query is blocking a BUSY session. The session limit is 500 per cluster or workgroup, SessionKeepAliveSeconds has a maximum of 24 hours, and parallel execution of queries within the same session is not possible. I found it practically useful to now be able to trace down to the statement causing session exhaustion.

AUTO_COMMIT is suited for workloads that tolerate partial completion and fill gaps with retries. However, there are two points to keep in mind from an operational design perspective. The first is that the parent status becomes FAILED. However, since the parent's Error contains the numbers of the failed statements, such as Queries failed in AUTO_COMMIT mode: [2], you can determine where it failed from there. To find out which statements succeeded, you need to check SubStatements. The second is that successful statements are not rolled back. If you have non-idempotent INSERT statements in sequence, retries can result in duplicate registrations, so consider combining ClientToken or MERGE. For processing where everything must succeed, it is safer to continue using the default TRANSACTION.

It was also noted that the ExecutionMode field appears in the DescribeStatement response only when executed with AUTO_COMMIT. Since it is not output for the default TRANSACTION, if you need to determine the execution mode from the response, you must check for the presence or absence of this field.

Looking ahead to future improvements, the relaxation of the WaitTimeSeconds upper limit (currently 30 seconds) and an API for explicitly closing sessions would be welcome additions. When verifying CLOSED sessions this time, it was necessary to wait for TTL expiration by setting SessionKeepAliveSeconds short.

Closing

We tried out Amazon Redshift Data API's long polling, ListSessions, and flexible batch execution in practice. All of these are updates that shift code previously written on the application side over to the API side. Polling loops can be replaced with --wait-time-seconds, external session ID management with list-sessions, and avoiding full batch rollbacks with --execution-mode AUTO_COMMIT. Long polling in particular has an impact on both execution time and code volume in architectures that call the Data API from AWS Lambda or AWS Step Functions.

Note that updating the AWS CLI or SDK is required to use the new parameters. If --wait-time-seconds is not found in your local environment, first check your CLI version. Why not see if you can replace your existing polling logic?

Share this article

AWSのお困り事はクラスメソッドへ