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

Ishikawa here from the Cloud Business Division. Long polling, list session, and flexible batch execution have been added to 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/

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. Submitting SQL with ExecuteStatement returns only a statement ID, then you repeatedly call DescribeStatement to wait for completion, and 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 to shift this boilerplate processing to the API side. These are 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, across all AWS commercial regions and AWS GovCloud (US) regions where 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 specifying a value of 1 to 30 seconds in WaitTimeSeconds causes the API to delay returning a response until either the statement finishes 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, sessions with AVAILABLE or BUSY status are returned, and you can filter by status, compute target, 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, it now supports Parameters (an array of SqlParameter), 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: IAM temporary credentials (only --workgroup-name and --database specified. DB user is IAM identity)

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

Note that this article measures elapsed time for API calls. Measurement is performed in the following form, and results are noted as elapsed: lines in subsequent steps.

S=$(python3 -c 'import time;print(time.time())')
aws redshift-data execute-statement \
  --workgroup-name dataapi-blog-wg --database dev \
  --sql "SELECT 1 AS test" \
  --wait-time-seconds 30
E=$(python3 -c 'import time;print(time.time())')
python3 -c "print(f'elapsed: {$E-$S:.2f}s')"

Creating Verification Tables

1. Creating Verification Data (Orders and Items Tables)

-- Create orders table and insert 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);

-- Create items table and insert 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)

-- Generate seed data (numbers 1 through 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;

-- Create 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. Verifying the Row Count of the Created Large-Scale Data

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

4. Creating Tables for Batch Comparison Testing

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

-- Table for auto-commit/individual processing testing
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 still need to call DescribeStatement separately to know when it's done.

Trying Long Polling

Execute 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 FINISHED.

Confirming That Long Polling Actually Waits

With SELECT 1, it's too fast to tell whether it actually "waited." So we measured using a query that cross-joins the 1-million-row table twice with the 10-row seed table and computes 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 returned FINISHED. Let's confirm 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, i.e., 10.94 seconds. We can see that the API waited for the query execution time and returned the status as soon as it completed. Conventionally, you would have needed to call DescribeStatement multiple times during these 11 seconds.

One important note here: when I ran the same query again with --wait-time-seconds 5, it completed without waiting for the wait time. This is because Amazon Redshift's result cache kicked in, returning the query that previously took 10.94 seconds in effectively zero seconds. If you want to verify long polling behavior, you need to change the query string when measuring.

Confirming Behavior When the Wait Time Expires

The upper limit of WaitTimeSeconds is 30 seconds. Let's check what happens with processing that exceeds that. To avoid the result cache, we 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 the query is still running. Next, we specify long polling on DescribeStatement and 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 call long polling again with the same statement ID after receiving a wait-time-expired response.

We also confirmed the case where --wait-time-seconds is set even shorter to 1 second. This uses 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 execution has been selected as a target) was returned. At this point, RedshiftPid is not included. This is likely because a process has not yet been assigned at this stage.

When we followed up on 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 to wait, the wait time expires even for a query of this level. Also, a call to a statement that has already completed returned instantly 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 Also Supports Long Polling

You can also specify WaitTimeSeconds on GetStatementResult. 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.

Try 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
}

Now 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 expiration is returned as SessionTtl. Previously, the application had to hold onto the SessionId, but now it can be listed after the fact.

Specify the retrieved SessionId and verify that the temporary table is 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. The schemaName in ColumnMetadata is pg_temp_14, which also shows that it was created in a session-specific temporary schema.

Next, let's call list-sessions while a query is running. 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 changed to BUSY, and CurrentStatementId contained the submitted statement ID directly. Since you can see which session is blocked by which query, this looks useful for investigating when sessions are exhausted.

Specifying --session-id retrieves 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 finished, it returned to AVAILABLE and CurrentStatementId is gone.

There are restrictions on filter conditions. --session-id cannot be combined 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 verify whether CLOSED sessions can also be retrieved. Since the Data API has no operation to explicitly close a session, 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"
        }
    ]
}

Even CLOSED, which is not returned by default, can be retrieved when explicitly specified.

Try Flexible Batch Execution

This was the part with the biggest behavioral differences this time. I'll compare the same 3-statement batch executed with TRANSACTION (default) and AUTO_COMMIT. 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 sub-statement 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., meaning it was not executed. The fact that the first and second statements share the same RedshiftQueryId of 1881 also indicates they were treated 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, same as TRANSACTION. Let's check the sub-statements.

% 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 are also executed. Additionally, comparing the raw output reveals the following differences:

  • The parent Error is Queries failed in AUTO_COMMIT mode: [2], with the failed statement numbers shown as an array. This differs from the TRANSACTION expression Query #2 failed with ERROR: division by zero
  • "ExecutionMode": "AUTO_COMMIT" is appended at the end of the response. The earlier output run with TRANSACTION (default) does not have this field at all
  • The third statement's RedshiftQueryId is 1897, separated from the 1892 of 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. On the TRANSACTION side, despite the first sub-statement recording ResultRows: 1, the table is empty, clearly showing that it was rolled back.

Sharing Parameters Within a Batch

Let's verify that values passed with --parameters can be referenced by multiple statements within the 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 value passed as QueryParameters is 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 in the SQL.

The official documentation states that "parameters specified must be referenced by at least one SQL statement in the batch." I tried passing an unreferenced parameter just in case, and received the following error:

% 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 unused parameter name is shown, identifying the cause is straightforward. Implementations that reuse a common set of parameters while varying only the SQL combinations may run into this constraint.

Observations

Here is a summary of what I found through actual measurements.

For long polling, I confirmed a 12.368-second wait with a query that had an execution time of 11 seconds. Since the conventional polling loop can be replaced with --wait-time-seconds, configurations where AWS Lambda calls the Data API can eliminate the loop and sleep used for waiting. However, since the upper limit is 30 seconds, for processes that exceed this, an implementation is needed that receives a wait-timeout response and performs 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 with GetStatementResult, if results are not ready within the wait time, a ResourceNotFoundException occurs. An implementation that determines failure based solely on the error code will produce false positives, so this must be treated as a "results not yet available" state.

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

AUTO_COMMIT is suited for workloads that tolerate partial completion and fill gaps through retries. On the other hand, there are 2 points to keep in mind for operational design. The first is that the parent status becomes FAILED. However, the parent's Error contains the numbers of the failed statements, such as Queries failed in AUTO_COMMIT mode: [2], so you can determine where the failure occurred from this. To know which statements succeeded, you need to check SubStatements. The second point is that succeeded statements are not rolled back. If you have non-idempotent INSERT statements lined up, there is a possibility of duplicate registration upon retry, so you may want to consider combining ClientToken or MERGE. For processes where everything must succeed, it is safer to continue using the default TRANSACTION.

Additionally, the ExecutionMode field only appears in DescribeStatement responses when executed with AUTO_COMMIT. Since it is not output with the default TRANSACTION, when determining the execution mode from the response, you need to check for the presence or absence of the field.

Points I would like to see improved in the future include relaxing the upper limit on WaitTimeSeconds (currently 30 seconds) and an API for explicitly closing sessions. When verifying CLOSED sessions this time, it was necessary to set SessionKeepAliveSeconds to a short value and wait for the TTL to expire.

Closing

I actually tried out long polling, ListSessions, and flexible batch execution with the Amazon Redshift Data API. 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 rollbacks of entire batches with --execution-mode AUTO_COMMIT. Long polling in particular has an impact on both execution time and code volume in configurations where the Data API is called 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 cannot be found in your local environment, first check your CLI version. Why not check whether your existing polling logic can be replaced?

Share this article

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