Write the result of a Looker query to BigQuery with Cloud Functions (workaround for System Activity ETL)

  • 7 December 2021
  • 0 replies
  • 107 views

Userlevel 3
  • Good Looker
  • 21 replies

日本語版もご覧になります:Cloud Functionsを使用してLookerで実行したクエリの結果をBigQueryにロードする

Hi all,

I am sharing code example for a Google Cloud Function that leverages Looker Python SDK and the Python client for BigQuery to get the result of a query in Looker and load the result to a BigQuery table. The workflow can be set up to run automatically with a cron job using Cloud Workflow.

 

Link to 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

 

 


0 replies

Be the first to reply!

Reply