Airflow - bigquery_to_gcs operator multiple output destination

Question

I am running an airflow job daily that runs aa query, and saves it to a table on Big Query and then another operator that copies the output into GCS.

Now, I have noticed that when the file size is more than 200 MB Airflow is dividing it into a multiple files in GCS. Is that a normal behavior for this operator? If so, how do we override it?

I read that this operator fails when the file sizes are more than 1 GB as well

Below is the code rendered

start = DummyOperator(task_id="start")

output_table = f"test.{DESTINATION_DATASET}.test_{{{{ ts_nodash }}}}"
output_path = f"gs://test/test/{{{{ ts_nodash }}}}"

query = BigQueryOperator(
    task_id=f"test1",
    sql="sql/test.sql",
    use_legacy_sql=False,
    priority="BATCH",
    write_disposition="WRITE_TRUNCATE",
    destination_dataset_table=output_table,
    params={
        "event_dataset": EVENT_DATASET,
        "days_to_query": 30,
    },
    bigquery_conn_id="google_cloud_default",
    labels={"team": "test"},
    dag=dag
)
export_to_gcs = BigQueryToCloudStorageOperator(
    task_id=f"export_to_gcs",
    source_project_dataset_table=output_table,
    destination_cloud_storage_uris=[f"{output_path}/data-*.csv"],
    compression="NONE",
    export_format="CSV",
    bigquery_conn_id="google_cloud_default",
    labels={"team": "test"},
    dag=dag,
)

start >> query >> export_to_gcs

user_14624409 · Answer

First BigQueryToCloudStorageOperator is deprecated. You should use BigQueryToGCSOperator.

Yes this is normal and it's not really Airflow. This is how BigQuery works. Airflow did not add/change the functionality of BigQuery.

Specifically in your case you asked BigQuery to do generate multiple files when you set multiple URIs in destination_cloud_storage_uris=[f"{output_path}/data-*.csv"],

You can read about the diffrent options of destination_cloud_storage_uris (Single URI, Single wildcard URI, Multiple wildcard URIs) in BigQuery docs here. You should also be aware of the limitations.

Assuming you want a single file in the output just change your code to Single URI:

destination_cloud_storage_uris=[f"{output_path}/data.csv"]

Airflow - bigquery_to_gcs operator multiple output destination

1 Answers