Amazon Redshift Data¶
Amazon Redshift manages all the work of setting up, operating, and scaling a data warehouse: provisioning capacity, monitoring and backing up the cluster, and applying patches and upgrades to the Amazon Redshift engine. You can focus on using your data to acquire new insights for your business and customers.
Prerequisite Tasks¶
To use these operators, you must do a few things:
Create necessary resources using AWS Console or AWS CLI.
Install API libraries via pip.
pip install 'apache-airflow[amazon]'Detailed information is available Installation of Airflow®
Generic Parameters¶
- aws_conn_id
Reference to Amazon Web Services Connection ID. If this parameter is set to
Nonethen the default boto3 behaviour is used without a connection lookup. Otherwise use the credentials stored in the Connection. Default:aws_default- region_name
AWS Region Name. If this parameter is set to
Noneor omitted then region_name from AWS Connection Extra Parameter will be used. Otherwise use the specified value instead of the connection value. Default:None- verify
Whether or not to verify SSL certificates.
False- Do not validate SSL certificates.path/to/cert/bundle.pem - A filename of the CA cert bundle to use. You can specify this argument if you want to use a different CA cert bundle than the one used by botocore.
If this parameter is set to
Noneor is omitted then verify from AWS Connection Extra Parameter will be used. Otherwise use the specified value instead of the connection value. Default:None- botocore_config
The provided dictionary is used to construct a botocore.config.Config. This configuration can be used to configure Avoid Throttling exceptions, timeouts, etc.
Example, for more detail about parameters please have a look botocore.config.Config¶{ "signature_version": "unsigned", "s3": { "us_east_1_regional_endpoint": True, }, "retries": { "mode": "standard", "max_attempts": 10, }, "connect_timeout": 300, "read_timeout": 300, "tcp_keepalive": True, }
If this parameter is set to
Noneor omitted then config_kwargs from AWS Connection Extra Parameter will be used. Otherwise use the specified value instead of the connection value. Default:NoneNote
Specifying an empty dictionary,
{}, will overwrite the connection configuration for botocore.config.Config
Operators¶
Execute a statement on an Amazon Redshift cluster¶
Use the RedshiftDataOperator to execute
statements against an Amazon Redshift cluster.
This differs from RedshiftSQLOperator in that it allows users to query and retrieve data via the AWS API and avoid
the necessity of a Postgres connection.
create_table_redshift_data = RedshiftDataOperator(
task_id="create_table_redshift_data",
cluster_identifier=redshift_cluster_identifier,
database=DB_NAME,
db_user=DB_LOGIN,
sql=[
"""
CREATE TABLE IF NOT EXISTS fruit (
fruit_id INTEGER,
name VARCHAR NOT NULL,
color VARCHAR NOT NULL
);
"""
],
poll_interval=POLL_INTERVAL,
wait_for_completion=True,
)
Reuse a session when executing multiple statements¶
Specify the session_keep_alive_seconds parameter on an upstream task. In a downstream task, get the session ID from
the XCom and pass it to the session_id parameter. This is useful when you work with temporary tables.
create_tmp_table_data_api = RedshiftDataOperator(
task_id="create_tmp_table_data_api",
cluster_identifier=redshift_cluster_identifier,
database=DB_NAME,
db_user=DB_LOGIN,
sql=[
"""
CREATE TEMPORARY TABLE tmp_people (
id INTEGER,
first_name VARCHAR(100),
age INTEGER
);
"""
],
poll_interval=POLL_INTERVAL,
wait_for_completion=True,
session_keep_alive_seconds=600,
)
insert_data_reuse_session = RedshiftDataOperator(
task_id="insert_data_reuse_session",
sql=[
"INSERT INTO tmp_people VALUES ( 1, 'Bob', 30);",
"INSERT INTO tmp_people VALUES ( 2, 'Alice', 35);",
"INSERT INTO tmp_people VALUES ( 3, 'Charlie', 40);",
],
poll_interval=POLL_INTERVAL,
wait_for_completion=True,
session_id="{{ task_instance.xcom_pull(task_ids='create_tmp_table_data_api', key='session_id') }}",
)
Durable execution¶
RedshiftDataOperator submits a statement and then polls it to completion on the worker. By
default the operator runs in a durable mode that makes this crash-safe: the Redshift statement
id is persisted to task state store before
polling begins, so if the worker crashes or is preempted and the task is retried, the operator
reconnects to the statement that is already executing in Redshift instead of resubmitting the SQL.
On retry the operator checks the prior statement’s state:
if it is still running, the operator reconnects and continues polling
if it already succeeded, the operator returns immediately without resubmitting
if it failed terminally, or its id has expired and is no longer found, the operator submits the SQL fresh
This protection also applies when wait_for_completion=False – even though that task attempt
never polls at all, a retry after a successful submission still reconnects rather than
resubmitting, since the statement id is persisted immediately after submission regardless of
whether the task waits for it to finish.
Durable execution requires Airflow 3.3 or newer, since it relies on the task state store. On earlier Airflow versions the flag is a no-op and the operator always submits fresh SQL on retry, exactly as before. If the task state store is unavailable at runtime, the operator logs that crash recovery is disabled and behaves the same way.
Like the persisted state itself, the stored statement id isn’t deleted automatically, that only
happens when someone runs airflow state-store clean. If a task’s retry_delay is longer
than [state_store] default_retention_days (30 days by default) and cleanup runs in between,
the statement id won’t be there for the next retry, and the operator will submit the SQL fresh
instead of reconnecting. Avoid running cleanup on a schedule shorter than your longest
retry_delay.
Clearing a task instance from the UI or the REST API (so airflowctl too) deletes the stored
statement id by default from Airflow 3.4. Every other clear, such as airflow tasks clear or
clearing a whole Dag run, does so from Airflow 3.5. To keep it, tick Keep task state and resume
in the Clear dialog, or send "keep_task_state": true in the request. The
Keep task state on clear UI setting ticks that box by default. From Airflow 3.5,
airflow tasks clear --keep-task-state keeps it too. The next attempt then reads the stored
statement id back. In synchronous mode, what the next attempt does depends on the state of the
statement at that point:
Statement state |
Default clear |
Clear that keeps task state |
|---|---|---|
Still running (the task had already failed, or its worker died) |
Submits the SQL again. The first statement keeps running. |
Reconnects to the running statement. |
Succeeded |
Submits the SQL again. |
Returns right away without running the SQL again. |
Failed or aborted |
Submits the SQL again. |
Submits the SQL again. |
With deferrable=True, the operator does not store the statement id, so the next attempt submits
the SQL again whether or not task state was kept.
With [state_store] clear_on_success on, the stored statement id is deleted when the task
succeeds, so clearing a succeeded task submits again even when task state is kept.
Keep task state when a statement is still running and nothing about the code or inputs changed, so
you don’t pay for two. This assumes the statement survives the clear; keeping task state does not
prevent cancellation via on_kill. See Resumable Tasks
for the ways of clearing.
By default (cancel_on_kill=True), clearing a running task requests cancellation via
on_kill, including when deferred (Airflow 3.3+). With cancel_on_kill=False, the statement is
left running; a default clear submits the SQL again on the next synchronous attempt.
To opt out and always submit fresh SQL on retry, set durable=False:
statement = RedshiftDataOperator(
task_id="redshift_data",
database="dev",
sql="SELECT * FROM table",
cluster_identifier="cluster_identifier",
durable=False,
)
Durable execution applies to the synchronous path. When deferrable=True is set, the Triggerer
already tracks the statement across the wait, so deferrable mode takes precedence and durable
has no effect.
Durable execution requires Airflow 3.3 or newer, since it relies on the task state store. Below
3.3, durable has no effect either way: setting it explicitly only emits a warning, and the
operator always submits fresh SQL on retry, exactly as before this feature existed.