BigQuery | Python | Export data from BigQuery table VIEW

This is a simple code to export from Biq Query to Google storage, in CSV format

def export_data():

  client = bigquery.Client()

  project = 'xxxxx'
  dataset_id = 'xxx'
  table_id = 'xxx'
  bucket_name = 'xxx'

  destination_uri = 'gs://{}/{}'.format(bucket_name, 'EXPORT_FILE.csv')
  dataset_ref = client.dataset(dataset_id, project=project)
  table_ref = dataset_ref.table(table_id)

  extract_job = client.extract_table(
      table_ref,
      destination_uri,
      # Location must match that of the source table.
      location='EU')  # API request
  extract_job.result()  # Waits for job to complete.

  print('Exported {}:{}.{} to {}'.format(
      project, dataset_id, table_id, destination_uri))

It works perfectly for general tables, BUT when I try to export data from saved table VIEW, it failed with this error:

BadRequest: 400 Using table xxx:xxx.xxx@123456 is not allowed for this operation because of its type. Try using a different table that is of type TABLE.

Does exist any way to export data from table view?

What I'm trying to achieve is, to get the data from BigQuery in CSV format, and upload to Google analytics Product Data

1 answer

  • answered 2018-08-13 14:18 Pentium10

    BigQuery views are subject to a few limitations:

    • You cannot run a BigQuery job that exports data from a view.

    There are more than 10+ other limitations which I didn't posted in the answer as they might change. Follow the link to read all of them.

    You need to query your view and write the results to a destination table, and then issue an export job on the destination table.