Skip to main content
Sign in

How to create a scheduled query in Google BigQuery?

Erik van Dorp
  • Updated

This support article explains the easiest way to get Google BigQuery up and running when using Cervinodata as the data engine. 

We assume that you have the following already set up in Cervinodata

  • Platform(s) connected
  • Account(s) switched on,
  • Google BigQuery connected
  • BigQuery project selected
  • Data synchronisation completed

If the above is true, go ahead and follow the steps below. If not, set up Cervinodata here or start a free trial here

Step 1: Create new data set

  • In Google BigQuery, click the project that is connected to Cervinodata
BigQuery_Cervinodata_connection.png Cervinodat_BigQuery_settings.png
  • Click "CREATE DATASET" (on the right of the screen)

BigQuery_Create_dataset.png

  • Give the new dataset a name (like "client_tables")
  • Make sure you select European Union (EU) as the Data location!
  • Click "Create data set" to save your settings.

BigQuery_create_dataset_details.png

Step 2: Create query

Your newly created dataset should now be visible in the left menu (under the project you have chosen)

BigQuery_dataset_created.png

  • Now click "More" > "Query settings" in the center of the screen. 

BigQuery_Querysettings_Cervinodata.png

  • In the Query settings, please pay attention to the following:
    • Give it a name that covers the content (like "client_a_ga", containing the Google Analytics data of client A)
    • Destination: choose "Set a destination table for query results"
    • Project name: select the project where you have created the dataset
    • Dataset name: choose the dataset that you created in step 1 (in our example "client_tables")
    • Destination table write preferences: choose "overwrite table"
  • Click save

Step 3: Create query (in the Cervinodata)

  • In Cervinodata, use the Google BigQuery builder to create a query for the table you wish to create
  • See a detailed instruction on how to build a query with the Query builder
  • Copy the query builder result and paste it in the BigQuery query editor
  • Run the query

Your new table should now have been created and should be visible in the left menu, under the Google BigQuery project.

BigQuery_table_created.png

 

Step 4: Schedule query

  • Click "Schedule query" > "Create new scheduled query"
    • Make sure you have Scheduled queries enabled (and make sure you have the Data transfer service API enabled)
  • Create a new scheduled query
    • Give the scheduled query a name that covers the content (like "client_a_ga_query")
    • Set the schedule options (to daily)
    • Set the start date & time (Cervinodata usually is ready synchronising well before 6:00 am CEST)
    • Choose the dataset from the list (in our example "client_tables")
    • The right table name should be already filled in (in our example "client_a_ga")
    • Choose: Overwrite table
    • Click Save
  • Done