scheduling a query to copy data from a dataset between projects in BigQuery

Question

We want to perform a test on BigQuery with scheduled queries.

The test retrieves a table from a dataset and, basically, copies it in another dataset (for which we have permission as owners) in another project. So far, we managed to do that with a script we wrote in R against the BigQuery API in a Google Compute Engine instance but we want/need to do it with scheduled queries in BigQuery.

If I just compose a query for retrieving the initial table data and I try to schedule it, I see there's a project selector but it's disabled so seems like I'm tied to the project for the user I'm logging in with.

Is this doable or am I overdoing it and using the API is the only option to do this?

Answer 1

Is this doable or am I overdoing it and using the API is the only option to do this?

The current scheduler logic doesn't allow this and for that reason, the project drop-down is disabled in the webUI.

As an example, I tried setting this schedule Job

CREATE TABLE IF NOT EXISTS `projectId.partitionTables.tableName` (Field0 TIMESTAMP) --AS SELECT * FROM mydataset.myothertable

And this is the error returning from the transferAPI

You will need to ask BigQuery team to add this option to future version of th scheduler API