GSP414

總覽

BigQuery 是 Google 提供的全代管數據分析資料庫，價格相當實惠。您可以使用 BigQuery 查詢 TB 規模的資料，不必管理基礎架構，也不需要資料庫管理員。BigQuery 使用 SQL，並提供「即付即用」模式，您可專心分析資料，找出有意義的深入分析結果。

本實驗室將說明如何在 BigQuery 查詢及建立分區資料表，以便提升查詢效能並減少資源用量。本實驗室使用的資料，是已載入到 BigQuery 的電子商務資料集，其中包含 Google 商品網路商店的數百萬筆 Google Analytics 記錄。

學習內容

在本實驗室中，您將瞭解如何執行下列工作：

• 查詢分區資料表。
• 建立您的分區資料表。

設定和需求

瞭解以下事項後，再點選「Start Lab」按鈕

請詳閱以下操作說明。實驗室活動會計時，且中途無法暫停。點選「Start Lab」後就會開始計時，顯示可使用 Google Cloud 資源的時間。

您將在真正的雲端環境完成實作實驗室活動，而不是模擬或示範環境。為此，我們會提供新的暫時憑證，供您在實驗室活動期間登入及存取 Google Cloud。

為了順利完成這個實驗室，請先確認：

• 可以使用標準的網際網路瀏覽器 (Chrome 瀏覽器為佳)。

注意事項：請使用無痕模式 (建議選項) 或私密瀏覽視窗執行此實驗室，這可以防止個人帳戶和學員帳戶之間的衝突，避免個人帳戶產生額外費用。

• 是時候完成實驗室活動了！別忘了，活動一旦開始將無法暫停。

注意事項：務必使用實驗室專用的學員帳戶。如果使用其他 Google Cloud 帳戶，可能會產生額外費用。

如何開始研究室及登入 Google Cloud 控制台

1. 點選「Start Lab」按鈕。如果實驗室會產生費用，畫面上會出現選擇付款方式的對話方塊。左側的「Lab Details」窗格會顯示下列項目：

   • 「Open Google Cloud console」按鈕
   • 剩餘時間
   • 必須在這個研究室中使用的臨時憑證
   • 完成這個實驗室所需的其他資訊 (如有)

2. 點選「Open Google Cloud console」；如果使用 Chrome 瀏覽器，也能按一下滑鼠右鍵，選取「在無痕視窗中開啟連結」。

   接著，實驗室會啟動相關資源，並開啟另一個分頁，顯示「登入」頁面。

   提示：您可以在不同的視窗中並排開啟分頁。

   注意：如果頁面中顯示「選擇帳戶」對話方塊，請點選「使用其他帳戶」。

3. 如有必要，請將下方的 Username 貼到「登入」對話方塊。

   {{{user_0.username | "Username"}}}

   您也可以在「Lab Details」窗格找到 Username。

4. 點選「下一步」。

5. 複製下方的 Password，並貼到「歡迎使用」對話方塊。

   {{{user_0.password | "Password"}}}

   您也可以在「Lab Details」窗格找到 Password。

6. 點選「下一步」。

   重要事項：請務必使用實驗室提供的憑證，而非自己的 Google Cloud 帳戶憑證。 注意：如果使用自己的 Google Cloud 帳戶來進行這個實驗室，可能會產生額外費用。

7. 按過後續的所有頁面：

   • 接受條款及細則。
   • 由於這是臨時帳戶，請勿新增救援選項或雙重驗證機制。
   • 請勿申請免費試用。

Google Cloud 控制台稍後會在這個分頁開啟。

注意：如要使用 Google Cloud 產品和服務，請點選「導覽選單」，或在「搜尋」欄位輸入服務或產品名稱。

開啟 BigQuery 控制台

1. 在 Google Cloud 控制台中，依序選取「導覽選單」>「BigQuery」。

接著，畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊，當中會列出快速入門導覽課程指南的連結和版本資訊。

2. 點選「完成」。

BigQuery 控制台會隨即開啟。

工作 1：建立新的資料集

1. 首先，您要建立資料集，存放資料表。

2. 在「Explorer」窗格中，點選專案 ID 旁邊的「查看動作」，然後按一下「建立資料集」。

3. 將「資料集 ID」設為 ecommerce。

資料位置、預設資料表到期時間等其他設定，請保留預設狀態。

4. 點選「建立資料集」。

點選「Check my progress」，確認目標已達成。

建立名為 ecommerce 的資料集

工作 2：建立依日期分區的資料表

分區資料表會劃分為多個區段 (稱為分區)，管理和查詢資料時更加方便。將大型資料表分成許多較小分區，不但可以提高查詢效能，還能降低查詢讀取的位元組數，進一步控管費用。

在這個工作中，我們會建立新資料表，並將日期或時間戳記欄綁定為一個分區。但在這之前，我們先瀏覽一下未分區資料表中的資料。

查詢網頁分析資料中 2017 年部分訪客的資料

1. 點選「+ SQL 查詢」，並加入下列查詢：

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170708' LIMIT 5

執行前，查詢驗證工具圖示旁會顯示要處理的資料總量：「這個查詢會在執行時處理 1.74 GB」。

2. 按一下「執行」。

查詢會傳回 5 筆結果。

查詢網頁分析資料中 2018 年部分訪客的資料

接著修改查詢，看看 2018 年的訪客資料。

1. 點選「+ SQL 查詢」來清除查詢編輯器內容，然後新增下列查詢。請注意，WHERE date 參數已改為 20180708：

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20180708' LIMIT 5

查詢驗證工具會顯示此查詢處理的資料量。

2. 按一下「執行」。

請注意，雖然此查詢傳回 0 筆結果，處理的資料量仍為 1.74 GB。這是因為查詢引擎需要掃描資料集內的所有記錄，才能確定是否符合 WHERE 子句內的日期比對條件。引擎必須查看每一筆記錄，將日期與「20180708」這個條件比較。

此外，LIMIT 5 並不會減少處理的資料總量，這是常見的誤解。

依日期分區資料表的常見用途

每次都要掃描整個資料集，才能將資料列與 WHERE 條件比較，會浪費許多資源。如果只想瞭解特定時段的記錄 (如下) 更是如此：

• 去年的所有交易
• 過去 7 天內的所有訪客互動
• 上個月售出的所有產品

設定依日期分區的資料表，就不必像之前的查詢一樣，需掃描整個資料集，並按日期欄位篩選。這個方式可略過與查詢無關的特定分區，完全不會掃描其中的記錄。

依據日期建立新的分區資料表

1. 點選「+ SQL 查詢」，新增下列查詢，然後點選「執行」：

#standardSQL CREATE OR REPLACE TABLE ecommerce.partition_by_day PARTITION BY date_formatted OPTIONS( description="a table partitioned by date" ) AS SELECT DISTINCT PARSE_DATE("%Y%m%d", date) AS date_formatted, fullvisitorId FROM `data-to-insights.ecommerce.all_sessions_raw`

在這個查詢中，請留意 PARTITION BY 這個新欄位。兩個可用的分區選項為 DATE 和 TIMESTAMP。日期欄位 (儲存為字串) 套用了 PARSE_DATE 函式，將資料轉為進行分區所需的 DATE 類型。

2. 點選「ecommerce」資料集，然後選取新的「partiton_by_day」資料表：

3. 點選「詳細資料」分頁標籤。

確認畫面上顯示：

• 分區依據：Day
• 用於分區的欄位：date_formatted

注意：從日期欄顯示的值起算 60 天後，您實驗室帳戶中的分區資料表分區將會自動到期。如果不希望分區資料表過期，請在個人 Google Cloud 帳戶中啟用計費功能。為配合本實驗室的學習目標，我們將針對已建立的分區資料表執行剩下的查詢。

點選「Check my progress」，確認目標已達成。

根據日期建立新的分區資料表

工作 3：查看分區資料表的查詢結果

1. 執行以下查詢，留意要處理的位元組總數：

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2016-08-01'

這次處理的資料量是 25 KB (即 0.025 MB)，只是之前查詢資料量的一小部分。

2. 現在執行以下查詢，留意要處理的位元組總數：

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2018-07-08'

您應該會看到「這項查詢會在執行時處理 0 B」。

工作 4：建立會自動到期的分區資料表

使用會自動到期的分區資料表，不僅有助於遵循資料隱私權法規，也可避免浪費不必要的儲存空間 (在正式環境中將因此而付費)。如果您想為資料建立滾動週期，不妨新增一個到期日，這樣當分區使用完畢後就會消失。

瀏覽可用的 NOAA 天氣資料表

1. 在左選單的「Explorer」，點選「+ 新增資料」並選取「公開資料集」。

2. 搜尋「GSOD NOAA」，然後選取該資料集。

3. 點選「查看資料集」。

4. 捲動瀏覽 noaa_gsod 資料集中的資料表 (已手動完成資料分割但未分區)：

您的目標是建立符合以下條件的資料表：

• 查詢 2018 年起的天氣資料
• 篩選出僅有部分降水 (雨雪等) 的日期
• 僅儲存自該分區建立日期起算 90 天 (滾動週期) 內的各分區資料

1. 首先，複製並貼上以下查詢：

#standardSQL SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation AND _TABLE_SUFFIX >= '2018' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10 注意：FROM 子句中會使用表格萬用字元 *，限制 TABLE_SUFFIX 篩選器中參照的資料表數量。 注意：雖然已新增 LIMIT 10，但這仍然不會減少掃描的資料總量 (約 1.83 GB)，因為目前沒有任何分區。

2. 按一下「執行」。

3. 確認日期格式正確，且降水量欄位顯示非零的值。

工作 5：大展身手，建立分區資料表

• 修改前一個查詢，按照以下規格建立資料表：

  • 資料表名稱：ecommerce.days_with_rain
  • PARTITION BY：使用日期欄位
  • OPTIONS：指明 partition_expiration_days = 60
  • 資料表說明：weather stations with precipitation, partitioned by day

您的查詢應會像是這樣：

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=60, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter AND _TABLE_SUFFIX >= '2018'

點選「Check my progress」，確認目標已達成。

大展身手，建立分區資料表

確認資料分區到期功能正常運作

如要確認只儲存過去 60 天至今的資料，請執行 DATE_DIFF 查詢，瞭解分區已建立多久 (這些分區設為在 60 天後到期)。

以下查詢可追蹤 NOAA 氣象站記錄日本和歌山的平均降雨量，該地降水顯著。

• 新增並執行此查詢：

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY date DESC; # most recent days first

工作 6：確認最早的 partition_age 等於或少於 60 天

更新 ORDER BY 子句，優先顯示最早的分區。

• 新增並執行此查詢：

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY partition_age DESC 注意：如果日後重新執行此查詢，由於天氣資料和分區都在不斷更新，結果也會不同。

恭喜！

您已成功在 BigQuery 中建立並查詢分區資料表。

後續步驟/瞭解詳情

• 如果您想瞭解如何建立不綁定特定日期/時間戳記欄的擷取時間分區資料表，請參閱 BigQuery 分區說明文件與相關範例。
• 您有 Google Analytics 帳戶，並且想在 BigQuery 中查詢自己的資料集嗎？請按照匯出指南的說明操作。
• 看看下列其他 BigQuery 實驗室：
  • 排解資料彙整錯誤
  • 在 BigQuery 使用 JSON、陣列和結構體
  • 使用 BigQuery ML (BigQuery 機器學習) 預測模型來預測計程車車資

Google Cloud 教育訓練與認證

協助您瞭解如何充分運用 Google Cloud 的技術。我們的課程會介紹專業技能和最佳做法，讓您可以快速掌握要領並持續進修。我們提供從基本到進階等級的訓練課程，並有隨選、線上和虛擬課程等選項，方便您抽空參加。認證可協助您驗證及證明自己在 Google Cloud 技術方面的技能和專業知識。

使用手冊上次更新日期：2025 年 4 月 29 日

實驗室上次測試日期：2025 年 4 月 29 日

Copyright 2025 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標，其他公司和產品名稱則有可能是其關聯公司的商標。