Return DataFrame¶
The @sql.dataframe decorator and SQLDataFrameOperator execute SQL queries and return the results as a pandas DataFrame.
Operator Usage¶
from airsql.operators import SQLDataFrameOperator
df_task = SQLDataFrameOperator(
task_id='get_active_users',
sql='SELECT * FROM users WHERE active = true',
source_conn='postgres_conn',
)
df_task.execute(context)
Parameters¶
| Parameter | Type | Required | Description |
|---|---|---|---|
task_id |
str |
Yes | Unique task identifier |
sql |
str |
Yes | SQL query to execute |
source_conn |
str |
Yes | Connection ID for the source database |
dynamic_params |
dict |
No | Parameters for dynamic task mapping |
Decorator Usage¶
from airsql import sql
@sql.dataframe(source_conn='postgres_conn')
def get_active_users():
return 'SELECT * FROM users WHERE active = true'
df_task = get_active_users()
Parameters¶
| Parameter | Type | Required | Description |
|---|---|---|---|
source_conn |
str |
No | Connection ID for simple queries |
sql_file |
str |
No | Path to SQL file |
**template_vars |
Any |
No | Variables passed to Jinja template |
Examples¶
Basic DataFrame Query¶
from airsql import sql
@sql.dataframe(source_conn='postgres_conn')
def get_active_users():
return 'SELECT * FROM users WHERE active = true'
# Use in DAG
with DAG('example_dag', ...) as dag:
df_task = get_active_users()
Query with Table References¶
from airsql import sql, Table
@sql.dataframe
def analyze_user_behavior(users_table, events_table):
return """
SELECT
u.id,
u.name,
COUNT(e.event_id) as event_count
FROM {{ users_table }} u
LEFT JOIN {{ events_table }} e ON u.id = e.user_id
GROUP BY u.id, u.name
"""
# Use with different database tables
analysis = analyze_user_behavior(
users_table=Table('postgres_conn', 'public.users'),
events_table=Table('bigquery_conn', 'analytics.user_events'),
)
Using SQL Files¶
@sql.dataframe(source_conn='postgres_conn', sql_file='queries/complex_analysis.sql')
def complex_analysis(start_date, end_date):
pass
Using in TaskFlow API¶
from airflow.decorators import dag, task
from airsql import sql
@dag(schedule='@daily')
def my_dag():
@sql.dataframe(source_conn='postgres_conn')
def extract_users():
return "SELECT * FROM users WHERE created_at >= '{{ ds }}'"
@task
def process_users(df):
return df.groupby('region').size().to_dict()
@task
def save_results(results):
print(f'Users by region: {results}')
users_df = extract_users()
processed = process_users(users_df)
save_results(processed)
my_dag()
Cross-Database Queries¶
@sql.dataframe
def compare_databases(source_a, source_b):
return """
WITH a_data AS (
SELECT 'source_a' as source, COUNT(*) as count FROM {{ source_a }}
),
b_data AS (
SELECT 'source_b' as source, COUNT(*) as count FROM {{ source_b }}
)
SELECT * FROM a_data
UNION ALL
SELECT * FROM b_data
"""
result = compare_databases(
source_a=Table('postgres_prod', 'public.orders'),
source_b=Table('postgres_staging', 'public.orders'),
)
Returns¶
The decorated function returns an Airflow operator that, when executed, returns a pandas.DataFrame containing the query results.
Important Notes¶
- The
source_connparameter is required for queries without table parameters - When using table parameters, the connection is inferred from the
Tablereferences - The DataFrame uses PyArrow as the dtype backend for better performance
- Results are not cached; each execution runs the query fresh