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
- What are the advantages and disadvantages of using Google BigQuery?
- How do I set up a Google Big Query zone?
- How can I use Google Big Query to integrate with Zephyr?
- How do I set up permissions for Google BigQuery?
- How do I use the "not in" operator in Google BigQuery?
- How can I use Google Big Query with MicroStrategy?
- How do I use Google Big Query with Zoom?
- ¿Cuáles son las ventajas y desventajas de usar Google BigQuery?
- How can I use Google BigQuery to wait for a query to complete?
- How do I use wildcards in Google BigQuery?
See more codes...