Amazon Athena SQL¶

Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon Simple Storage Service (S3) using standard SQL. Athena is serverless, so there is no infrastructure to setup or manage, and you pay only for the queries you run. To get started, simply point to your data in S3, define the schema, and start querying using standard SQL.

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®
Setup Connection.

Operators¶

Execute a SQL query¶

The generic SQLExecuteQueryOperator can be used to execute SQL queries against Amazon Athena using a Athena connection.

To execute a single SQL query against an Amazon Athena without bringing back the results to Airflow, please use AthenaOperator instead.

tests/system/common/sql/example_sql_execute_query.py[source]

execute_query = SQLExecuteQueryOperator(
    task_id="execute_query",
    sql=f"SELECT 1; SELECT * FROM {AIRFLOW_DB_METADATA_TABLE} LIMIT 1;",
    split_statements=True,
    return_last=False,
)

Also, if you need to do simple data quality tests with Amazon Athena, you can use the SQLTableCheckOperator

The below example demonstrates how to instantiate the SQLTableCheckOperator task.

tests/system/common/sql/example_sql_column_table_check.py[source]

row_count_check = SQLTableCheckOperator(
    task_id="row_count_check",
    table=AIRFLOW_DB_METADATA_TABLE,
    checks={
        "row_count_check": {
            "check_statement": "COUNT(*) = 1",
        }
    },
)

Reference¶

PyAthena