CODEWITHSUNDEEP
google-bigquery
Akos
Akos

Reputation: 69

How can I schedule a script in BigQuery?

At last BigQuery supports using ; in the queries, so I can write more than one query in one "block", if I seperate them with semicolon. If I run the code manually, it works. But I cannot schedule that.

When I want to schedule, I have two choices:

  1. (New) Web UI: I must give a destination table. If I don't do it, I could not save the scheduled query. But all my queries are updates and inserts with different "destination tables". Like these:

UPDATE project.exampledataset.a SET date = current_date() WHEN TRUE ;

INSERT INTO project.otherdataset.b SELECT c,d FROM project.otherdataset.c

So I cannot even make a scheduling in the Web UI.

  1. Classic UI: I tried this, because the official documentary states, that I should leave the "destination table" blank, and Classic UI allows it. I can setup the scheduling, but it doesn't run, when it should. I get the error message in email "Error status: Dataset specified in the query ('') is not consistent with Destination dataset 'exampledataset'."

AIK scripting (and using semicolon) is a very new feature in BigQuery, but I hope someone can help me. Yes, I know that I could schedule every query one by one, but I would like to resolve it with one big script.

Upvotes: 2

Views: 3982

Answers (3)

MandisaW
MandisaW

Reputation: 1021

As of 2022, the BQ Console UI will let you create a new scheduled query without a destination dataset, but it won't let you update a prior SELECT to use DDL/DML block syntax. However, you can use the BigQuery Data Transfer API to update the destinationDatasetId field, via transferconfigs/patch. Use transferconfigs/list to get the configId for a given scheduled query.

Note that you can either use the in-browser API Explorer, if you have the appropriate credentials, or write a programmatic solution. Also seems useful for setting/updating any other fields, including renaming scheduled queries.

Upvotes: 0

Abani Mahapatra
Abani Mahapatra

Reputation: 76

Looks like the scheduled query was defined earlier with destination dataset defined with APPEND/TRUNCATE type transaction. While updating the same scheduled query to a DML query, GUI doesn't show the dataset field / table name to update to NULL. Hence this error is coming considering the previously set dataset and table name in the scheduled query.

Hence the fix is to delete the scheduled query and create it from scratch with DML query option. It worked for me.

Upvotes: 2

Yun Zhang
Yun Zhang

Reputation: 5503

Scripting is supported in scheduled query now. However, scripting query, when being scheduled, doesn't support setting a destination table for now. You still need to use DDL/DML to make change to existing table.

E.g.:

CREATE OR REPLACE TABLE destinationTable AS
SELECT *
FROM sourceTable
WHERE date >= maxDate

Upvotes: 2

Related Questions