Now you can schedule queries directly in BigQuery

by Veronika Schipper, on Sep 28, 2018 1:13:11 PM

ow you can schedule queries directly in Big Query

Google just released a BETA version of BigQuery scheduling. That means that you can now run jobs automatically at a certain period with a simple click of a button. Since there is no code required, only a standard SQL, it is specially handy for data analysts who want to organise their query flow within the same interface.

BigQuery launched in 2010 and became very popular ever since. It is a scalable data warehouse that can manage and analyse terabytes of data in seconds. Until recently there was no option to schedule queries directly in BigQuery, but that does not mean it could not be done somewhere else. To plan your jobs, you had to use a third party or develop a solution using one of these options:

1. Cron jobs with Google App Engine

  • PROS: serverless solution, easy to use, support more programming languages
  • CONS: extra costs for having to run non-stop the application, hard to control, easy to make a mistake, limitations to the calls to the APIs, database request limitation to the 60s
  • HOW TO: https://cloud.google.com/appengine/docs/standard/java/config/cron

2. Time-based trigger in Google Apps Script

  • PROS: serverless solution, no maintenance needed, less coding than with Google App Engine, available monitoring of the project, nothing to install
  • CONS: Google Apps Script is naturally linked with your the Google account you are signed in with which might be inconvenient when using it for service production system, only for JavaScript, you cannot trigger data flows from Google Apps Script
  • HOW TO: https://shinesolutions.com/2017/11/01/scheduling-bigquery-jobs-using-google-apps-script/

3. Google Cloud Composer on Apache Airflow

Also relatively new, BETA version was released in May 2018, and at Crystalloids we are still testing it.

  • PROS: open source, integrates with BigQuery, Dataflow, Dataproc, Datastore and more, sequence scheduling and jobs monitoring possible
  • CONS: only for Python, not the final version (problem with monitoring and integration with Dataflow)

4. Run queries manually every day:-)

We are currently exploring the possibilities of BigQuery scheduling. You can start using it too, read the detailed description on how to in here.

Topics:GoogleBig data

Comments