Query Guide¶
AirSQL provides a comprehensive set of operators and decorators for SQL query operations. Each operation type supports both direct operator usage and decorator-based syntax.
Overview¶
AirSQL offers two ways to work with SQL operations:
| Approach | Description | Best For |
|---|---|---|
| Decorators | Python-decorated functions that return operators | Clean, Pythonic syntax; most common use |
| Operators | Direct class instantiation | Maximum control; dynamic configuration |
Operation Types¶
| Operation | Description | Output |
|---|---|---|
@sql.dataframe |
Execute SQL and return a pandas DataFrame | DataFrame |
@sql.query |
Execute SQL and write results to a table | Table reference |
@sql.append |
Execute SQL and append to existing table | Table reference |
@sql.replace |
Execute SQL and replace entire table content | Table reference |
@sql.truncate |
Truncate table and insert new data | Table reference |
@sql.merge |
Execute SQL and merge/upsert into table | Table reference |
@sql.load_dataframe |
Load a pandas DataFrame into a table | None |
@sql.merge_dataframe |
Merge/upsert a DataFrame into a table | None |
@sql.extract_and_load |
Extract via SQL and load into table | String summary |
@sql.extract_and_merge |
Extract via SQL and merge into table | String summary |
@sql.check |
Execute data quality checks | None |
@sql.ddl |
Execute DDL statements | None |
Common Parameters¶
Most query operations share these common parameters:
| Parameter | Type | Description |
|---|---|---|
output_table |
Table |
Destination table reference |
source_conn |
str |
Connection ID for source database |
sql_file |
str |
Path to SQL file (alternative to function return) |
dry_run |
bool |
If True, simulate without writing data |
pre_truncate |
bool |
If True, truncate before writing (where applicable) |
Quick Examples¶
Using Decorators¶
from airsql import sql, Table
@sql.dataframe(source_conn='postgres_conn')
def get_active_users():
return 'SELECT * FROM users WHERE active = true'
@sql.query(output_table=Table('postgres_conn', 'reports.daily_summary'))
def create_report():
return 'SELECT DATE(created_at) as date, COUNT(*) as total FROM orders GROUP BY 1'
@sql.replace(output_table=Table('postgres_conn', 'cache.user_cache'))
def refresh_cache():
return "SELECT * FROM users WHERE last_login > CURRENT_DATE - INTERVAL '30 days'"
Using Operators Directly¶
from airsql.operators import SQLQueryOperator, SQLDataFrameOperator, SQLReplaceOperator
from airsql import Table
df_task = SQLDataFrameOperator(
task_id='get_active_users',
sql='SELECT * FROM users WHERE active = true',
source_conn='postgres_conn',
)
query_task = SQLQueryOperator(
task_id='create_report',
sql='SELECT DATE(created_at) as date, COUNT(*) as total FROM orders GROUP BY 1',
output_table=Table('postgres_conn', 'reports.daily_summary'),
source_conn='postgres_conn',
)
replace_task = SQLReplaceOperator(
task_id='refresh_cache',
sql="SELECT * FROM users WHERE last_login > CURRENT_DATE - INTERVAL '30 days'",
output_table=Table('postgres_conn', 'cache.user_cache'),
source_conn='postgres_conn',
)