How to Transfer a BigQuery view to a Google Cloud Storage bucket as a csv file

lugger1 0
Viewed 85

I need to export the content of a BigQuery view to the csv file in GCP, with Airflow DAG. To export the content of the BQ TABLE, I can use BigQueryToCloudStorageOperator. But in my case I need to use an existing view, and BigQueryToCloudStorageOperator fails with this error, which I see while checking the logs for failed DAG:

BigQuery job failed: my_view is not allowed for this operation because it is currently a VIEW

So, what options do I have here? I can't use a regular table, so may be there is another operator that would work with a view data stored in BQ, instead of table? Or may be the same operator would work with some addition options (although I don't see anything useful in here Apache documentation for BigQueryToCloudStorageOperator)?

directed-acyclic-graphs airflow google-cloud-platform
1 Answers

I think the Bigquery client doesn’t give the possibility to export a view to a GCS file.

It’s not perfect but I propose you 2 solutions

First solution (more native with existing operators) :

  • Create a staging table to export it to GCS
  • At the beginning of your DAG, create a task that truncate this staging table
  • Add a task with a select on your view and an insert in your staging table (insert/select)
  • Use the bigquery_to_gcs operator from your staging table

Second solution (less native with Python clients and PythonOperator) :

  • Use a PythonOperator
  • In this operator, use a Bigquery Python client to load data from your view as Dict and the storage Python client to generate a file to GCS from this Dict

I have a preference for the first solution, even if it forces me to create a staging table.

Mazlum Tosun0
Related