Schedule query in BigQuery

Solution 1:

Update

Scheduling recurring queries is available now in BigQuery since September 2018


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/

Solution 2:

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

BigQuery has scheduled queries feature in Beta now.

enter image description here

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

Solution 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.

enter image description here

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