[BigQuery] 一支SQL搞定每日排程從BigQuery(BQ)到Google Cloud Storage(GCS)

快速導覽

閱讀對象：數據分析師、經常使用BigQuery撈資料的人

如果你在BigQuery(BQ)寫了一支SQL，想要執行結果匯出資料到Google Cloud Storage(GCS)，今天這一招簡單又方便，從BQ匯出資料到GCS的技巧非常適合你!

EXPORT DATA介紹

EXPORT DATA
OPTIONS (
uri = CONCAT('gs://{your_bucket_name}', '/*.csv'),
format = 'CSV',
compression = 'GZIP',
overwrite = TRUE
) AS
SELECT * FROM {project_id}.{dataset}.{table_name};

語法相當簡單，URI只要寫GCS bucket URI的位置，指定匯出格式(format)和壓縮方式(compression)以及你想執行的SQL指令，Overwrite的功能則可以覆蓋更新相同的資料。此外，EXPORT DATA功能匯出時也會幫你把資料分成好幾個檔案儲存到GCS，可以避免程式一次要讀取很大的檔案，如果你是要寫code做到這件事是相當麻煩的，還得確保資料分段儲存檔案時不會有字串位元分段問題，這麻煩事EXPORT DATA都幫你做好了。

匯出格式(format)和壓縮方式(compression)請參考下方：

參考資料：https://cloud.google.com/bigquery/docs/exporting-data

假設你想每日匯出前三天的資料，那麼應該怎麼做呢?

CREATE OR REPLACE TABLE {project_id}.{dataset}.last_three_days_data
AS
SELECT *
FROM {project_id}.{dataset}.{table_name}_*
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY));

EXPORT DATA
OPTIONS (
uri = CONCAT('gs://{your_bucket_name}/', FORMAT_DATE('%Y%m%d', event_date), '/*.csv'),
format = 'CSV',
compression = 'GZIP',
overwrite = TRUE
) AS
SELECT * FROM {project_id}.{dataset}.last_three_days_data;

由於我的範例中是[PREFIX]_YYYYMMDD區隔，相當於一個日期一個資料表，要做日期範圍的查詢必須使用_TABLE_SUFFIX，這邊也順便教大家日期查詢的方式

日期範圍篩選通常透過「BETWEEN...AND...」的作法，針對目標日期給定「起始日期」和「結束日期」。

[PREFIX]_YYYYMMDD日期範圍篩選前三天作法

一般table已存在多個日期，日期範圍篩選前三天作法

EXPORT DATA不允許_TABLE_SUFFIX寫法，所以需要在最外層先把要匯出的資料儲存到實體Table中，在範例中我將SQL的前三天的結果儲存到last_three_days_data，那建立Table的技巧是CREATE OR REPLACE TABLE，這個方法可以確保當Table不存在時可以建立Table，若Table存在時覆蓋資料，確保last_three_days_data都是新鮮的前三天資料，最後再透過「SELECT * FROM {project_id}.{dataset}.last_three_days_data」以last_three_days_data的event_date欄位作為資料夾命名區分「CONCAT('gs://{your_bucket_name}/', FORMAT_DATE('%Y%m%d', event_date), '/*.csv')」，將資料匯出到GCS。

當然你可以將這個指令儲存下來，再去BQ設定排程，你就可以定期每日匯出了，你完全不需要開任何Computer Engine(VM)、Cloud Function去寫Code，只要一支SQL指令輕鬆達成目的，以上是不是很方便呢!

---