Extract and Load¶
The @sql.extract_and_load decorator combines SQL extraction and DataFrame loading into a single TaskFlow-compatible task.
Usage¶
from airsql import sql, Table
@sql.extract_and_load(
output_table=Table('postgres_conn', 'analytics.daily_summary'),
source_conn='bigquery_conn',
if_exists='replace',
)
def extract_daily_summary():
return """
SELECT
DATE(created_at) as summary_date,
COUNT(*) as total_records,
AVG(amount) as avg_amount
FROM transactions
WHERE DATE(created_at) = '{{ ds }}'
GROUP BY 1
"""
load_task = extract_daily_summary()
Parameters¶
| Parameter | Type | Required | Description |
|---|---|---|---|
output_table |
Table |
Yes | Destination table reference |
source_conn |
str |
Yes | Connection ID for source database |
if_exists |
str |
No | Behavior: 'append', 'replace', 'fail', 'truncate' |
timestamp_column |
str |
No | Column to populate with current timestamp |
sql_file |
str |
No | Path to SQL file |
dry_run |
bool |
No | If True, simulate without writing |
**template_vars |
Any |
No | Variables passed to Jinja template |
Examples¶
Cross-Database Loading¶
@sql.extract_and_load(
output_table=Table('postgres_conn', 'warehouse.events'),
source_conn='bigquery_conn',
if_exists='replace',
)
def sync_events():
return """
SELECT *
FROM analytics.events
WHERE DATE(event_time) = '{{ ds }}'
"""
Append Daily Data¶
@sql.extract_and_load(
output_table=Table('postgres_conn', 'archive.all_orders'),
source_conn='postgres_staging',
if_exists='append',
)
def archive_orders():
return """
SELECT * FROM orders
WHERE created_at >= '{{ ds }}'
AND created_at < '{{ tomorrow_ds }}'
"""
With Timestamp Tracking¶
@sql.extract_and_load(
output_table=Table('postgres_conn', 'audit.sync_log'),
source_conn='bigquery_conn',
if_exists='append',
timestamp_column='loaded_at',
)
def log_sync():
return """
SELECT
table_name,
row_count,
CURRENT_TIMESTAMP() as sync_time
FROM metadata.table_stats
"""
Using SQL Files¶
@sql.extract_and_load(
output_table=Table('postgres_conn', 'reports.complex_report'),
source_conn='bigquery_conn',
sql_file='sql/complex_report.sql',
if_exists='replace',
)
def generate_report(report_date):
pass
With Template Variables¶
@sql.extract_and_load(
output_table=Table('postgres_conn', 'reports.{{ region }}_metrics'),
source_conn='bigquery_conn',
if_exists='replace',
region='us-west',
)
def regional_metrics(date_filter):
return """
SELECT * FROM metrics
WHERE region = '{{ region }}'
AND date = '{{ date_filter }}'
"""
Dry Run Mode¶
@sql.extract_and_load(
output_table=Table('postgres_conn', 'test.preview'),
source_conn='bigquery_conn',
dry_run=True,
)
def preview_data():
return 'SELECT * FROM large_table LIMIT 1000'
How It Works¶
- Extracts data from the source database using the SQL query
- Loads results into a pandas DataFrame
- Writes the DataFrame to the destination table
- Returns a summary string with row count
TaskFlow Integration¶
This decorator is TaskFlow-compatible and can be used in dependencies:
from airflow.decorators import dag
from airsql import sql, Table
@dag(schedule='@daily')
def my_dag():
@sql.extract_and_load(
output_table=Table('postgres_conn', 'staging.raw_data'),
source_conn='bigquery_conn',
if_exists='replace',
)
def extract_raw():
return "SELECT * FROM source WHERE date = '{{ ds }}'"
@sql.query(output_table=Table('postgres_conn', 'reports.final'))
def process(raw_data):
return f'SELECT * FROM {raw_data} WHERE valid = true'
raw_data = extract_raw()
process(raw_data)
my_dag()
Related Operations¶
@sql.extract_and_merge- Extract and merge/upsert@sql.query- Execute SQL and write to table@sql.load_dataframe- Load DataFrame directly