PostgreSQL to GCS¶
Export data from PostgreSQL directly to Google Cloud Storage in CSV, JSONL, or Parquet format.
Operator Usage¶
from airsql.transfers import PostgresToGCSOperator
export = PostgresToGCSOperator(
task_id='pg_to_gcs_export',
sql='SELECT * FROM reports.large_dataset',
postgres_conn_id='postgres_default',
bucket='backup-bucket',
filename='exports/{{ ds_nodash }}/report.csv',
gcp_conn_id='google_cloud_default',
)
Parameters¶
| Parameter | Type | Required | Description |
|---|---|---|---|
sql |
str |
Yes | SQL query to extract data |
postgres_conn_id |
str |
Yes | PostgreSQL connection ID |
bucket |
str |
Yes | GCS bucket name |
filename |
str |
Yes | GCS object path |
gcp_conn_id |
str |
No | GCP connection ID |
export_format |
str |
No | csv, jsonl, parquet (default: csv) |
schema_filename |
str |
No | Path for BigQuery schema JSON |
pandas_chunksize |
int |
No | Rows per chunk for large exports; omitted preserves the existing full-query path |
shard_size_mb |
float |
No | Maximum uploaded shard size when chunked extraction is enabled |
use_copy |
bool |
No | Use PostgreSQL COPY for streaming |
use_temp_file |
bool |
No | Use temp file instead of streaming |
csv_kwargs |
dict |
No | Additional pandas CSV options |
parquet_kwargs |
dict |
No | Additional pandas Parquet options |
auto_switch_format |
bool |
No | Auto-switch to JSONL for problematic data |
dry_run |
bool |
No | Simulate without writing |
Examples¶
CSV Export¶
export = PostgresToGCSOperator(
task_id='csv_export',
sql='SELECT * FROM users',
postgres_conn_id='postgres_default',
bucket='data-lake',
filename='exports/users/{{ ds }}/users.csv',
export_format='csv',
)
JSONL Export¶
export = PostgresToGCSOperator(
task_id='jsonl_export',
sql='SELECT * FROM events WHERE date = {{ ds }}',
postgres_conn_id='postgres_default',
bucket='data-lake',
filename='events/{{ ds }}.jsonl',
export_format='jsonl',
)
Large Dataset Streaming¶
export = PostgresToGCSOperator(
task_id='streaming_export',
sql='SELECT * FROM huge_table',
postgres_conn_id='postgres_default',
bucket='data-lake',
filename='huge_table/export.parquet',
export_format='parquet',
use_copy=True,
)
With Schema File¶
export = PostgresToGCSOperator(
task_id='export_with_schema',
sql='SELECT * FROM analytics.metrics',
postgres_conn_id='postgres_default',
bucket='data-lake',
filename='metrics/{{ ds }}.jsonl',
export_format='jsonl',
schema_filename='schemas/metrics.json',
)
Chunked Export¶
export = PostgresToGCSOperator(
task_id='chunked_export',
sql='SELECT * FROM large_table',
postgres_conn_id='postgres_default',
bucket='data-lake',
filename='large_table/data.parquet',
export_format='parquet',
pandas_chunksize=50000,
)
Custom CSV Options¶
export = PostgresToGCSOperator(
task_id='custom_csv',
sql='SELECT * FROM products',
postgres_conn_id='postgres_default',
bucket='exports',
filename='products.csv',
export_format='csv',
csv_kwargs={'sep': '|', 'encoding': 'utf-8'},
)
Dry Run¶
export = PostgresToGCSOperator(
task_id='test_export',
sql='SELECT * FROM large_table LIMIT 1000000',
postgres_conn_id='postgres_default',
bucket='test-bucket',
filename='test/data.csv',
dry_run=True,
)
How It Works¶
- Executes SQL query against PostgreSQL
- Streams results to GCS in chunks
- Automatically handles JSON columns (switches to JSONL)
- Generates BigQuery-compatible schema (optional)
- Cleans up temporary files
Format Selection¶
| Format | Use Case | JSON Support | Compression |
|---|---|---|---|
csv |
Simple data, smaller files | No | No |
jsonl |
JSON columns, nested data | Yes | No |
parquet |
Columnar storage, analytics | Limited | Yes |
JSON Columns¶
JSON/JSONB columns are automatically detected:
# If table has JSON columns, format automatically switches to JSONL
export = PostgresToGCSOperator(
task_id='json_export',
sql='SELECT id, data::jsonb as data FROM events',
postgres_conn_id='postgres_default',
bucket='data-lake',
filename='events.jsonl',
export_format='csv', # Will auto-switch to JSONL
auto_switch_format=True,
)
Incremental Exports¶
export = PostgresToGCSOperator(
task_id='incremental_export',
sql="""
SELECT * FROM orders
WHERE created_at >= '{{ ds }}'
AND created_at < '{{ tomorrow_ds }}'
""",
postgres_conn_id='postgres_default',
bucket='daily-exports',
filename='orders/{{ ds }}.csv',
)