CODEWITHSUNDEEP
sqlgoogle-bigquery
Sana
Sana

Reputation: 563

copy a table from different projects and push into an EU result project

I have got several bigquery projects, some EU region and some US region. All projects have 'customers' dataset and a table inside 'customers' dataset called 'purchases' I want to write a query to copy all the contents of purchases table into another table in another project which is called Results and it is EU region. So I created this query:

SELECT
  *
FROM
  `customers.purchases`
WHERE
  {{param.location}}='EU'
  AND _PARTITIONTIME = TIMESTAMP('{{ ds }}')
UNION ALL
SELECT
  *
FROM
  `customers_EU.purchases`
WHERE
  _PARTITIONTIME = TIMESTAMP('{{ ds }}')
AND {{param.location}}='EU'

As the results project is located in EU, I cannot copy from US into EU, so for US region projects, I created a customers_EU dataset and I copy purchases table from customers dataset into customers_EU in the same project. Then the above query checks if the project is EU region then copy from customers dataset else copy from customers_EU dataset.

But the problem is it fails and it gives an error for a US project:

customers.purchases was not found in location 'EU'

Yes, that is right, it is not located in EU but the location parameter is US and I am expecting it skip first part of query and return nothing from the first part of query as the condition is not satisfied

Upvotes: 0

Views: 90

Answers (1)

Alvaro
Alvaro

Reputation: 963

One approach you can take can be:

  1. Only query purchases tables with dataset located in EU (using UNION ALL). Set a destination table in a dataset located in EU as well.

  2. Only query purchases tables with dataset located in the US (using UNION ALL). Set a destination table in a dataset located in the US as well.

  3. Using the feature Dataset copy (BQ transfer service) move (copy) the US dataset created in the step 2 to EU.

  4. Create a view that UNION table created in step 1 with table created in step 3.

Upvotes: 1

Related Questions