LinkedIn
Twitter
日本語版もご覧になります：Cloud Functionsを使用してLookerで実行したクエリの結果をBigQueryにロードする
Updated: August 2022
Hi all,
I am sharing the code to send a Looker query to BigQuery with Cloud Functions.
- Static solution (using Looker Python SDK, BigQuery API, and Cloud Functions): There is no option in Looker UI, and the query id or look id is hardcoded into Cloud Functions. Use this option to only allow certain queries to be sent to BigQuery (i.e.: you want to send data from System Activity to BigQuery as a workaround for ETL). Link to code example
- Dynamic solution (using Looker Action API, BigQuery API, and Cloud Functions): There is an option for Looker users to select “BigQuery” as a destination for their queries in the UI (same menu with “Schedule” or “Send). Use this option to allow users to send any queries to BigQuery using the Looker UI. Link to video and code example
Potential use cases
- Workaround for System Activity ETL: Get data from Looker’s System Activity and write to BigQuery directly. These BigQuery tables can then be registered as a connection in Looker for additional LookML data modeling. Currently, Looker's System Activity stores a maximum of 100k rows, or 90 days of historical query and event data
- Transfer data from a different database to BigQuery
Considerations
- Cloud Function is easy to set up, and suitable for light-weighted, on-the-fly tasks. For heavy workloads, consider using Looker's native actions (sending to Google Cloud Storage, S3) or additional ETL/ELT tools (such as GCP's Dataflow)
- Consider using Elite System Activity for more flexibility on System Activity
Let me know if you have any questions and feedback
