Now you can schedule queries directly in BigQuery

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.

Moving your data warehouse to the cloud? Here’s what you need to know: 

ABOUT CRYSTALLOIDS

Crystalloids helps companies improve their customer experiences and build marketing technology. Founded in 2006 in the Netherlands, Crystalloids builds crystal-clear solutions that turn customer data into information and knowledge into wisdom. As a leading Google Cloud Partner, Crystalloids combines experience in software development, data science, and marketing making them one of a kind IT company. Using the Agile approach Crystalloids ensures that use cases show immediate value to their clients and make their job focus more on decision making and less on programming.

For more information, please visit www.crystalloids.com or follow us on LinkedIn.