'BigQuery: Insert dynamic folder name into an EXPORT DATA statement

I am trying to dynamically set a folder name on a data export.
The below creates a folder called "@date" and does not input the actual date variable.

Is this possible to insert dynamic values into the uri?

EXECUTE IMMEDIATE 

"EXPORT DATA OPTIONS(uri='gs://bucket/folder/@date/*.csv',  format='CSV',   overwrite=true,  header=true,  field_delimiter=',') AS SELECT field1, field2 FROM `dataset.table` ORDER BY field1 LIMIT 10"

USING CAST(CURRENT_DATE() AS STRING) as date;
sqlgoogle-bigquerypythonpandastimetime-series


Solution 1:[1]

My answer may not be suitable for your answer, but might help others who came across my situation where the file name with GCS path must be passed as an input parameter like below:

StoredProcedures_Offers(filePath STRING)

then this filePath needs to passed into EXPORT DATA OPTIONS uri section as follows:

EXPORT DATA OPTIONS( uri=""||filePath||"", format='JSON', overwrite=true) AS

Note: Excluding query part here in the answer just to highlight the EXPORT section

Solution 2:[2]

Try concat:

EXECUTE IMMEDIATE CONCAT(
    "EXPORT DATA OPTIONS(uri='gs://bucket/folder/",
    CAST(CURRENT_DATE() AS STRING),
    "/*.csv',  format='CSV',   overwrite=true,  header=true,  field_delimiter=',') AS SELECT field1, field2 FROM `dataset.table` ORDER BY field1 LIMIT 10"
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 shary.sharath
Solution 2 Sergey Geron

Related Questions