google-big-queryHow can I export data from Google Big Query to an XLSX file?
Exporting data from Google Big Query to an XLSX file is possible using the BigQuery Storage API. The steps to do this are as follows:
- Create a service account with appropriate permissions (BigQuery Admin, BigQuery Data Viewer, and Storage Object Admin)
- Generate a private key for the service account
- Create a Google Cloud Storage bucket
- Create a BigQuery job to export the data from BigQuery to the Google Cloud Storage bucket
- Use the Storage API to download the file from the Google Cloud Storage bucket to your local machine
Example code for steps 4 and 5:
# Step 4
from google.cloud import bigquery
# Construct a BigQuery client object.
client = bigquery.Client()
# Set the destination URI of the exported data
destination_uri = 'gs://my-bucket/exported_data.csv'
# Construct a BigQuery Extract Job Config
job_config = bigquery.job.ExtractJobConfig()
job_config.destination_format = bigquery.DestinationFormat.CSV
# Set the destination table
dataset_ref = client.dataset('my_dataset')
table_ref = dataset_ref.table('my_table')
# Start the extraction job
extract_job = client.extract_table(
table_ref,
destination_uri,
job_config=job_config
)
extract_job.result()
# Step 5
from google.cloud import storage
# Create a storage client
storage_client = storage.Client()
# Get the bucket that the file is in
bucket = storage_client.get_bucket('my-bucket')
# Create a blob object from the filepath
source_blob = bucket.blob('exported_data.csv')
# Download the file to a destination
destination_file_name = 'exported_data.xlsx'
source_blob.download_to_filename(destination_file_name)
Helpful links
More of Google Big Query
- How do I use a JSON service account with Google Big Query?
- How do I set up a Google Big Query zone?
- How do I use Google Big Query to zip files?
- ¿Cuáles son las ventajas y desventajas de usar Google BigQuery?
- How can I use the CASE WHEN statement in Google Big Query?
- How can I compare Google BigQuery, Snowflake, and Redshift for software development?
- How can I compare Google BigQuery and AWS Redshift for software development?
- How do I use the UNION clause in Google BigQuery?
- How do I use Google BigQuery to unnest an array?
- How do I update data in Google BigQuery?
See more codes...