Transferring objects from BigQuery to a Cloud Storage bucket using Storage Read API

user_16964629 0
Viewed 278

I plan to export ~600 TB of data from BigQuery to a bucket in Cloud Storage for archiving purposes. So far I've tried using BQ's export function, but the 50 TB limit per day is holding back the transfer process. I've also looked into using the EXPORT DATA SQL statement, but that might be quite expensive considering on-demand BQ Pricing.

Storage Read API looks like it was made to solve the export limit, but there doesn't seem to be any examples on how to transfer large amounts of data from BQ to GCS explicitly. I was wondering how it this process would look like in a Python script for transferring a large amount of tables. Thank you!

google-cloud-storage google-bigquery bucket google-cloud-platform python
1 Answers

According to the documentation for the Google Cloud Storage API "Streaming transfers are not supported for the Console, Python, or Ruby."
You can however "Pipe the data to the gsutil cp command and use a dash for the source URL".
You should then be able to write a Python script using the BigQuery Storage API Python Client library and pipe it to the gsuitl cp command to write to your Cloud Storage bucket:
PYTHON PROCESS | gsutil cp - gs://BUCKET_NAME/OBJECT_NAME
where PYTHON PROCESS is some wrapping around your Python code which uses the BigQuery Storage API Python Client library.


To clarify you could do something like this: 

python script_using_storageapi.py | gsutil cp - gs://name_of_bucket/name_of_object

I just tried this with a simple Python script using print statements and it worked, but there are probably more elegant ways to send your Storage Read API objects: 

def print_list(some_list):
    for element in some_list:
        print(element)


if __name__ == '__main__':
    test_list = ['Write', 'to', 'cloud', 'storage', 'bucket']
    print_list(test_list)
user_159219410
Related