BigQuery：資料來源防火牆

許博淳

Published in

數據共筆

May 15, 2022

為什麼需要防火牆？是有什麼會燒起來嗎？萬一燒起來再救火不行嗎？

圖片來源：https://images.idgesg.net/images/article/2019/05/cso_security_lock_firewall_fires_threats_exploits_by_matejmo_gettyimages-879868000_2400x1600-100798015-large.jpg?auto=webp&quality=85,70

先解釋一下這邊防火牆的意思是，在 Data Lake和實際的 Data warehouse之間的一個預先處理措施。

以均一來說 Data Lake包含了 Google Cloud Storage (GCS) 和 GA，GCS之中有大量後端的資料，GA之中則是特定事件觸發的紀錄，都會直接傳送到 BigQuery之中。

傳送過來之前有經過一定的處理，所以組成大致上是資料表形式，能夠直接被 Query操作，但可能存在重複資料，有些資料欄位並不需要等問題，如果直接使用，將會非常浪費效能。

另外，如果一個欄位有問題，會需要改動數張到數十張使用到該欄位的 Data warehouse表格，相當的麻煩。

因此我們的想法是，在資料來源的資料表與 Data warehouse之間再多一層，形成有如下圖的 1對1對多架構

以下以均一實際案例來說明改動的前後差異

-- 創造防火牆表格程式碼
CREATE OR REPLACE PROCEDURE
  `DataSource.VideoLog_from_DB`(dump_date STRING)
BEGIN
  CREATE OR REPLACE TABLE
    `DataSource.VideoLog_from_DB`
  AS
  SELECT
    TIMESTAMP_ADD(time_watched, INTERVAL 8 HOUR) AS active_timestamp_TW,
    user.email AS user_primary_key,
    IFNULL(video.path, auditable.path) AS video_path,
    ip_address,
    is_video_completed,
    last_second_watched,
    points_earned,
    seconds_watched AS time_taken_second,
    youtube_id,
  FROM
    `datastore_backup.VideoLog_*`
  WHERE
    _TABLE_SUFFIX = dump_date
    AND (video.path IS NOT NULL OR auditable.path IS NOT NULL);
END;