AgentSkillsCN

gcp-bq-data-export

当将BigQuery数据导出到Cloud Storage、提取表为CSV、JSON、Avro或Parquet格式，或使用EXPORT DATA语句时，请使用此技能。涵盖bq extract命令、格式选项、压缩和通配符导出。

SKILL.md
--- frontmatter
name: gcp-bq-data-export
description: Use when exporting BigQuery data to Cloud Storage, extracting tables to CSV, JSON, Avro, or Parquet formats, or using EXPORT DATA statements. Covers bq extract command, format options, compression, and wildcard exports.

BigQuery Data Export

Use this skill when exporting data from BigQuery to Cloud Storage or local files.

Basic Extract Command

bash
bq extract \
  --location=LOCATION \
  --destination_format=FORMAT \
  --compression=COMPRESSION \
  PROJECT:DATASET.TABLE \
  gs://bucket/file.ext

Extract to CSV

Basic CSV Export

bash
bq extract \
  --destination_format=CSV \
  --print_header=true \
  dataset.table \
  gs://bucket/export.csv

CSV with Options

bash
bq extract \
  --destination_format=CSV \
  --compression=GZIP \
  --field_delimiter=',' \
  --print_header=true \
  dataset.table \
  gs://bucket/export.csv.gz

CSV flags:

  • •--field_delimiter=',' - Column separator
  • •--print_header=true/false - Include header row

Tab-Delimited Export

bash
bq extract \
  --destination_format=CSV \
  --field_delimiter=$'\t' \
  dataset.table \
  gs://bucket/export.tsv

Extract to JSON

Newline-Delimited JSON

bash
bq extract \
  --destination_format=NEWLINE_DELIMITED_JSON \
  --compression=GZIP \
  dataset.table \
  gs://bucket/export.json.gz

Output format:

json
{"id": 1, "name": "Alice", "amount": 100.50}
{"id": 2, "name": "Bob", "amount": 250.75}

Extract to Avro

bash
bq extract \
  --destination_format=AVRO \
  --compression=SNAPPY \
  dataset.table \
  gs://bucket/export.avro

Benefits:

  • •Preserves schema
  • •Efficient binary format
  • •Fast re-import to BigQuery

Extract to Parquet

bash
bq extract \
  --destination_format=PARQUET \
  dataset.table \
  gs://bucket/export.parquet

Benefits:

  • •Columnar format
  • •Good compression
  • •Compatible with many analytics tools

Compression Options

Available compression:

  • •GZIP - Good compression, slower (CSV, JSON, Avro)
  • •SNAPPY - Fast, moderate compression (Avro, Parquet)
  • •DEFLATE - Similar to GZIP (Avro)
  • •NONE - No compression (fastest)

Example:

bash
bq extract \
  --destination_format=CSV \
  --compression=GZIP \
  dataset.table \
  gs://bucket/export.csv.gz

Large Table Exports

Using Wildcards (>1 GB)

BigQuery limitation: 1 GB per file

Solution: Use wildcard in destination

bash
bq extract \
  --destination_format=CSV \
  dataset.large_table \
  'gs://bucket/export-*.csv'

Output:

code
gs://bucket/export-000000000000.csv
gs://bucket/export-000000000001.csv
gs://bucket/export-000000000002.csv
...

Shard Pattern

bash
# Create sharded exports
bq extract \
  --destination_format=AVRO \
  dataset.large_table \
  'gs://bucket/shard/data-*.avro'

Note: Number of files depends on data size, not configurable.

Export Specific Partitions

Single Partition

bash
# Export 2024-01-15 partition only
bq extract \
  --destination_format=CSV \
  dataset.partitioned_table\$20240115 \
  gs://bucket/export_20240115.csv

Date Range (use WHERE in EXPORT DATA)

See EXPORT DATA section below.

EXPORT DATA SQL Statement

Basic EXPORT DATA

sql
EXPORT DATA OPTIONS(
  uri='gs://bucket/export-*.csv',
  format='CSV',
  overwrite=true,
  header=true,
  field_delimiter=','
) AS
SELECT * FROM `project.dataset.table`
WHERE date >= '2024-01-01';

Export Query Results

sql
EXPORT DATA OPTIONS(
  uri='gs://bucket/aggregated-*.parquet',
  format='PARQUET',
  overwrite=true
) AS
SELECT
  customer_id,
  DATE(order_timestamp) as order_date,
  SUM(amount) as total_amount,
  COUNT(*) as order_count
FROM `project.dataset.orders`
WHERE DATE(order_timestamp) >= '2024-01-01'
GROUP BY customer_id, order_date;

Format Options

CSV:

sql
EXPORT DATA OPTIONS(
  uri='gs://bucket/*.csv',
  format='CSV',
  header=true,
  field_delimiter=',',
  compression='GZIP'
) AS SELECT ...;

JSON:

sql
EXPORT DATA OPTIONS(
  uri='gs://bucket/*.json',
  format='JSON',
  compression='GZIP'
) AS SELECT ...;

Avro:

sql
EXPORT DATA OPTIONS(
  uri='gs://bucket/*.avro',
  format='AVRO',
  compression='SNAPPY'
) AS SELECT ...;

Parquet:

sql
EXPORT DATA OPTIONS(
  uri='gs://bucket/*.parquet',
  format='PARQUET'
) AS SELECT ...;

Export to Local Files (Not Recommended)

Small Results via Query

bash
# For small datasets only
bq query \
  --format=csv \
  --max_rows=10000 \
  --use_legacy_sql=false \
  'SELECT * FROM `project.dataset.table` LIMIT 10000' \
  > local_export.csv

Limitation: Not suitable for large datasets. Use GCS for production.

Export Scheduled (Automation)

Using Cloud Scheduler + EXPORT DATA

bash
# Create scheduled query
bq mk --transfer_config \
  --target_dataset=dataset \
  --display_name='Daily Export' \
  --schedule='every 24 hours' \
  --params='{"query":"EXPORT DATA OPTIONS(uri='\''gs://bucket/daily-*.csv'\'', format='\''CSV'\'') AS SELECT * FROM dataset.table WHERE date = CURRENT_DATE()"}' \
  --data_source=scheduled_query

Using Cloud Composer (Airflow)

python
from airflow.providers.google.cloud.operators.bigquery import BigQueryInsertJobOperator

export_task = BigQueryInsertJobOperator(
    task_id='export_to_gcs',
    configuration={
        'extract': {
            'sourceTable': {
                'projectId': 'project',
                'datasetId': 'dataset',
                'tableId': 'table'
            },
            'destinationUris': ['gs://bucket/export-*.csv'],
            'destinationFormat': 'CSV'
        }
    }
)

Monitoring Exports

Check Extract Jobs

bash
bq ls --jobs --max_results=10

Job Details

bash
bq show -j JOB_ID

Failed Exports

sql
SELECT
  job_id,
  user_email,
  error_result.message as error_message,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  job_type = 'EXTRACT'
  AND state = 'DONE'
  AND error_result IS NOT NULL
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
ORDER BY creation_time DESC;

Export Best Practices

Format Selection

CSV:

  • •✅ Human-readable
  • •✅ Universal compatibility
  • •❌ Larger file size
  • •❌ No schema preservation

JSON:

  • •✅ Human-readable
  • •✅ Preserves nested structures
  • •❌ Larger file size

Avro:

  • •✅ Preserves schema
  • •✅ Efficient binary format
  • •✅ Fast BigQuery re-import
  • •❌ Not human-readable

Parquet:

  • •✅ Columnar format
  • •✅ Good compression
  • •✅ Analytics tool compatible
  • •❌ Not human-readable

Compression Recommendations

For long-term storage: GZIP (best compression) For processing pipelines: SNAPPY (fast) For network transfer: GZIP (smaller size) For speed: NONE (no compression overhead)

Wildcards for Large Exports

Always use wildcards for:

  • •Tables >500 MB
  • •Unknown data size
  • •Distributed processing

Example:

bash
bq extract dataset.large_table 'gs://bucket/export-*.avro'

Cost Considerations

Export Costs

  • •BigQuery extract: FREE
  • •GCS storage: Standard GCS pricing
  • •Network egress: Free within same region

Optimization

Reduce costs:

  • •Export only needed columns (use EXPORT DATA with SELECT)
  • •Filter rows before export (WHERE clause)
  • •Use compression (smaller files)
  • •Export to GCS in same region as BigQuery

Example - filtered export:

sql
EXPORT DATA OPTIONS(
  uri='gs://bucket/*.parquet',
  format='PARQUET'
) AS
SELECT customer_id, order_date, amount  -- Only needed columns
FROM `project.dataset.orders`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)  -- Last 30 days only
  AND amount > 0;  -- Filter out zero amounts

Common Patterns

Daily Export

bash
#!/bin/bash
DATE=$(date +%Y%m%d)
bq extract \
  --destination_format=CSV \
  --compression=GZIP \
  dataset.table\$$DATE \
  gs://bucket/exports/daily_export_$DATE.csv.gz

Incremental Export

sql
-- Create temp table with new data
CREATE TEMP TABLE new_data AS
SELECT * FROM `project.dataset.table`
WHERE updated_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

-- Export only new data
EXPORT DATA OPTIONS(
  uri='gs://bucket/incremental/data-*.parquet',
  format='PARQUET'
) AS
SELECT * FROM new_data;

Export with Transformation

sql
EXPORT DATA OPTIONS(
  uri='gs://bucket/transformed-*.csv',
  format='CSV'
) AS
SELECT
  customer_id,
  UPPER(customer_name) as customer_name,
  ROUND(amount, 2) as amount,
  FORMAT_DATE('%Y-%m-%d', order_date) as order_date
FROM `project.dataset.orders`
WHERE order_date >= '2024-01-01';

Troubleshooting

"Permission denied"

Problem: No write access to GCS bucket Solution: Grant BigQuery service account Storage Object Creator role

"Table too large"

Problem: Export exceeds 1GB without wildcard Solution: Use wildcard pattern gs://bucket/export-*.csv

"Invalid URI"

Problem: Incorrect GCS path format Solution: Use gs://bucket/path/file format, not https://

"Quota exceeded"

Problem: Too many extract jobs Solution: Batch exports or increase quota

Quick Reference

Format recommendations:

  • •Re-import to BigQuery → Avro
  • •Analytics tools → Parquet
  • •Data exchange → CSV
  • •API consumption → JSON

Compression guide:

  • •Best ratio → GZIP
  • •Fastest → SNAPPY or NONE
  • •Balance → SNAPPY

Size limits:

  • •1 GB per file (use wildcards)
  • •10 TB per extract job
  • •50,000 URIs per export

Syntax patterns:

bash
# Single file
gs://bucket/file.csv

# Wildcard (recommended)
'gs://bucket/prefix-*.csv'

# Sharded with path
'gs://bucket/path/to/shard-*.parquet'