Export nested BigQuery data to cloud storage using python

1
votes

Trying to export bigquery data to storage but there is an error "400 Operation cannot be performed on a nested schema. Field: event_params".

Below is my code:

from google.cloud import bigquery
client = bigquery.Client()
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "/Users/Nitin/Desktop/big_query_test/soy-serty-897-ed73.json"
bucket_name = "soy-serty-897.appspot.com"
project = "soy-serty-897"
dataset_id = "analytics_157738"
table_id = "events_20190326"

destination_uri = 'gs://{}/{}'.format(bucket_name, 'basket.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='US')  # API request
extract_job.result()  # Waits for job to complete.

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

2 Answers

2
votes

Within the BigQuery export limitations, it's mentioned that CSV doesn't support nested and repeated data. Thus, try exporting to Avro or JSON:

from google.cloud import bigquery
client = bigquery.Client()
bucket_name = 'your_bucket'
project = 'bigquery-public-data'
dataset_id = 'samples'
table_id = 'shakespeare'

destination_uri = 'gs://{}/{}'.format(bucket_name, '<your_file>')
dataset_ref = client.dataset(dataset_id, project=project)
table_ref = dataset_ref.table(table_id)
configuration = bigquery.job.ExtractJobConfig()
#For AVRO
#configuration.destination_format ='AVRO'
#For JSON
#configuration.destination_format ='NEWLINE_DELIMITED_JSON'

extract_job = client.extract_table(
table_ref,
destination_uri,
job_config=configuration,
location='US')
extract_job.result()

Hope it helps.

1
votes

Can't test it now but maybe this works:

from google.cloud import bigquery as bq
ejc = bq.ExtractJobConfig()
ejc.destination_format='NEWLINE_DELIMITED_JSON'
extract_job = client.extract_table(
    table_ref,
    destination_uri,
    # Location must match that of the source table.
    location='US',
    job_config=ejc)  # API request

The idea is to use JSON instead of CSV so that you have support for nested data.