Chapter 17: BigQuery: Hands-on Workshop
17.13. Saving Queries in BigQuery and Connecting to Looker Studio
Save Query Results
When running a query in BigQuery, you might want to save the results for visualization or later use. You have several options for saving your query results, such as:
- CSV to Google Drive
- CSV to your computer
- Google Sheets (static)
- Copying to clipboard
- BigQuery Table
The most effective approach in this case is defining a BigQuery table within your project's dataset.
Create a New Table
- Run your query.
- Choose the option to save the result.
- Select "BigQuery table."
- Choose your project and dataset.
- Name your table (e.g., "query_analysis_underlying_table").
Now you have a new table in your dataset.
Connect Looker Studio with BigQuery
- Go to Looker Studio and create a blank report.
- Connect it to a data source.
- Choose BigQuery as the data source.
- Select the project and dataset containing your newly created table.
After connecting, you can visualize and analyze your data in Looker Studio using various dimensions and metrics.
Acceleration with BigQuery BI Engine
Your report will benefit from the BigQuery BI Engine, which caches queries, tables, and data in RAM for sub-second querying and analysis capabilities.
However, there's an issue with this setup: if data changes in Google Search Console or Google Sheets, this method won't update the analysis every hour or day as needed.
Creating Live Connections
To address this issue:
- Ensure both tables are external and connected live to Google Sheets so that any changes in Google Sheets will be reflected instantly in BigQuery tables. External Tables in BigQeury is explained in previous lessons.
- Schedule Supermetrics queries (if possible) to run every hour so that live data is accessed more frequently.
This way, even though your final result isn't live, you'll be able to access updated data more frequently and have more accurate insights in Looker Studio.
📩 Receive my weekly Looker Studio tips
🖇 Connect with me on LinkedIn