Скачать или смотреть How to Work Around the EXPORT DATA Statement Limitation in BigQuery

Learn how to successfully export data into Google Cloud Storage using BigQuery's `EXPORT DATA` statement without encountering meta table issues.
---
This video is based on the question https://stackoverflow.com/q/67397911/ asked by the user 'Alfi Syahri' ( https://stackoverflow.com/u/7935858/ ) and on the answer https://stackoverflow.com/a/67429532/ provided by the user 'Alfi Syahri' ( https://stackoverflow.com/u/7935858/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Export Data in Standard SQL Bigquery : EXPORT DATA statement cannot reference meta tables in the queries

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Work Around the EXPORT DATA Statement Limitation in BigQuery

Exporting data from BigQuery to Google Cloud Storage (GCS) can be a complicated task, especially when using dynamic table names. If you're facing the warning message "EXPORT DATA statement cannot reference meta tables in the queries", don’t worry! This guide will guide you through a practical solution.

The Problem

When attempting to schedule a query in BigQuery that dynamically generates table names based on yesterday's date, many users (like yourself) run into an issue. The EXPORT DATA statement, which should facilitate exporting the query results to a CSV file in GCS, doesn't allow referencing certain meta tables. Specifically, the error arises when you try to include parameters like _TABLE_SUFFIX in your EXPORT DATA statement.

Your Scenario

You want to:

Export data from a dynamically named table (suffixed with yesterday's date).

Automate this process using scheduled queries.

Avoid the error that prohibits referencing meta tables.

The Solution

To circumvent the limitations of the EXPORT DATA statement, we can break down the process into two separate jobs. This method effectively captures your requirements while adhering to BigQuery’s constraints.

Step 1: Create a Temporary Table

The first step is to run a query that retrieves the data you need and stores it in a temporary table. Here’s how you can do it:

[[See Video to Reveal this Text or Code Snippet]]

Schedule This Query:

Set a schedule for this query to run daily, creating a new table (e.g., table1) that contains the data from yesterday.

Step 2: Export Data to Google Cloud Storage

Once your temporary table has been successfully created, the next step is to export this data to GCS. Use the following EXPORT DATA statement:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Export Script:

URI: Generates the path in GCS where the files will be saved. It uses CONCAT to dynamically name the files with the appropriate date.

Format: Specifies the output format (CSV).

Overwrite: Allows overwriting existing files with the same name.

Header: Includes column headers in the exported CSV.

Field Delimiter: Sets a comma as the field delimiter for the CSV format.

Conclusion

By separating the data selection and export processes into two distinct jobs, you can effectively work around the limitations imposed by BigQuery on the EXPORT DATA statement. This method not only satisfies your requirement to dynamically handle table names but also aligns with best practices for managing data exports in a scheduled manner.

Implement this approach, and you’ll be on your way to successfully exporting your BigQuery data without further issues!