Previous Stackoverflow Post on Topic
The bigquery.extract_data() function which extracts data from BigQuery into GCS does not maintain integer or float types. See below for python bigquery client library example:
client = bigquery.Client()
dataset_ref = bigquery.DatasetReference('our-gcp-project','our-bq-dataset')
configuration = bigquery.job.ExtractJobConfig()
configuration.destination_format = 'NEWLINE_DELIMITED_JSON'
extract_job = client.extract_table(
table_ref=dataset_ref.table('table_name'),
destination_uri=f'gs://our-gcs-bucket/our-output-file.json',
job_config=configuration,
location="us-west2",
)
extract_job.result()
An an alternative, we loop the bigquery.rowIterator object and simply write the results to a JSON:
full_query = 'select * from ...' # query to get BQ table
# results_row_iter = bq.query(full_query).result() # google.bq.rowIterator
results_row_iter = bq.query(full_query) # removed .result() to avoid bringing entire query result into memory
with open(f'ourfile.json', 'w') as f:
for row in results_row_iter:
f.write(json.dumps(dict(row), default=str) + '\n')
This "works". The query is very fast, however looping the query rowIterator and writing the results line-by-line to a local .JSON is very slow compared to the bigquery.extract_table table.
Is it possible to use the python bigquery client, along with the client.extract_table function, while maintaining integer and float types? If not, then is it possible to speed up our for-loop which writes a local .JSON file? The stackoverflow post linked above seems like the main online resource addressing the question, however the question is 5 years old, and perhaps there is a better solution than both of my above code snippets?
Edit: I've updated the BigQuery query in my working example above to not call .result() after the query, which seems to avoid bringing the entire query result into memory, which resolves an out of memory issues I was having. There's really no reason to have .result() in the code above.
Edit 2: Suggestions in comments are okay, but are no better than our currently working for loop shown above. Our main issue is that the client.extract_table is very fast, since it's all being done over GCP network I think, and it's very frustrating that we cannot use this function because of the loss of types. We are simply trying to replicate the benefits of client.extract_table (the speed/performance, and the lack of a need to bring data into memory), while maintaining types. Our current for-loop code above maintains types, but sacrifices a lot in speed/performance...