Schedule query in BigQuery

问题:

I have a BigQuery query which unions two tables (daily Google Analytics exports). What I want is to save the query as a view or table but have that final view/table update automatically everyday as the new Google Analytics data is loaded into BigQuery.

Thanks for your help

回答1:

BigQuery by itself doesn’t provide any scheduling mechanism.

You can use 3rd party solution or develop your own using client of your choice
https://cloud.google.com/bigquery/docs/reference/libraries

For example,

You can use App Engine Cron Service to automate BigQuery query execution https://cloud.google.com/appengine/docs/java/config/cron https://cloud.google.com/appengine/features/#cron https://cloud.google.com/bigquery/bigquery-api-quickstart

Or, you can use Google Apps Script to script BigQuery query with a Time based trigger to execute that script
https://developers.google.com/apps-script/advanced/bigquery https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers

Also, check recently introduced Google Cloud Functions (Beta)
https://cloud.google.com/functions/docs/

回答2:

New Feature: https://cloud.google.com/bigquery/docs/scheduling-queries

BigQuery has scheduled queries feature in Beta now.

You would need to enable the BigQuery Data Transfer Service and the "scheduled queries" is in the BigQuery Transfer Service menu.

回答3:

To add to Mikhail's answer - check out this easy-to-deploy Google sheet:

https://docs.google.com/spreadsheets/d/1JZkkKcT9yrcYc1g8QqJaIE6PJtoFig3G_ZNbtXuK6fw/edit

1. Copy this sheet.
2. Customize with your query and destination.
3. Run script, ensure permissions are set up right.
4. Schedule an hourly/daily/etc trigger.

Source: https://twitter.com/anilopez/status/958382085407883264