CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Alfi Syahri
Alfi Syahri

Reputation: 69

Export Data in Standard SQL Bigquery : EXPORT DATA statement cannot reference meta tables in the queries

I want to export data to csv and save it to Google Storage using EXPORT DATA in Standard Query. It will be saved as scheduled query. Then, I set the table suffix into dynamic according to yesterday's date. Unfortunately, Bigquery didn't allow using the _TABLE_SUFFIX and resulted a warning of

"EXPORT DATA statement cannot reference meta tables in the queries."

It might mean I should use a static table name. But, in this case, I can only use table name with changing name according to yesterday date. Do you have any idea how to work around with this problem? Thank you.

EXPORT DATA OPTIONS(
  uri=CONCAT('gs://my_data//table1_', CONCAT(FORMAT_DATE("%Y%m%d",CURRENT_DATE()-1),'*.csv')),
  format='CSV',
  overwrite=true,
  header=true,
  field_delimiter=',') AS
SELECT *
FROM `mybigquery.123456.ga_sessions_*`
WHERE
_TABLE_SUFFIX = FORMAT_DATE("%Y%m%d",CURRENT_DATE()-1)

Upvotes: 5

Views: 2914

Answers (2)

MStikh
MStikh

Reputation: 384

This can also be solved using temporary table as follows:

CREATE TEMP TABLE ga_temp AS
(SELECT hits.*
FROM
`project-id.1234567.ga_sessions_*`,
 UNNEST(hits) AS hits);
 
EXPORT DATA OPTIONS(uri='gs://folder1-sftp/folder2/activities_online_base_2021-11-19_*.csv',
format='CSV',
overwrite=true,
header=False) AS
   SELECT * from ga_temp

Just do not forget to set some timelimits, GA tables are huge and expensive.

Upvotes: 4

Alfi Syahri
Alfi Syahri

Reputation: 69

I separated the query into 2 jobs. First job is to run query and save it to a table. Second job is to export the table to GCS. All can be done using scheduled query.

First job

SELECT *
FROM `mybigquery.123456.ga_sessions_*`
WHERE
_TABLE_SUFFIX = FORMAT_DATE("%Y%m%d",CURRENT_DATE()-1)

Then, schedule it to make a new table called table1.

Second job

EXPORT DATA OPTIONS(
  uri=CONCAT('gs://my_data//table1_', CONCAT(FORMAT_DATE("%Y%m%d",CURRENT_DATE()-1),'*.csv')),
  format='CSV',
  overwrite=true,
  header=true,
  field_delimiter=',') AS
SELECT *
FROM `mybigquery.123456.table1`

Upvotes: 1

Related Questions