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
- ¿Cuáles son las ventajas y desventajas de usar Google BigQuery?
- How can I use Google Big Query to count the number of zeros in a given dataset?
- How can I use Google Big Query to integrate with Zephyr?
- How do I use Google Big Query with Zoom?
- How do I set up a Google Big Query zone?
- How can I use Google Big Query to analyze Reddit data?
- How can I use Google BigQuery to answer specific questions?
- How can I learn to use Google BigQuery?
- How can I use Google BigQuery to retrieve data from a specific year?
- How do I use Google Big Query to zip files?
See more codes...