GSP414

總覽

BigQuery 是 Google 提供的全代管 NoOps 數據分析資料庫，價格相當實惠，可用來查詢 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 setup and access」面板會顯示下列項目：

   • 「Open Google Cloud console」按鈕
   • 此實驗室所需的臨時憑證 (使用者名稱和密碼)
   • 完成此實驗室所需的其他資訊 (如有)

   請注意，實驗室計時器位於頁面頂端附近，會顯示剩餘時間。

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

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

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

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

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

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

   您也可以在「Lab setup and access」面板找到「Username」。

4. 點選「下一步」。

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

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

   您也可以在「Lab setup and access」面板找到「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」資料集和新的「partition_by_day」資料表。

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

確認畫面上顯示：

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

注意：從日期欄顯示的值起算 90 天後，您實驗室帳戶中的分區資料表分區將自動到期。若使用已啟用計費功能的個人 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 資料集中的資料表 (已手動完成 shard，但未分區)：

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

• 查詢 2018 年起的天氣資料
• 篩選出有降水 (雨雪等) 的日期
• 從各分區對應的日期起算，每個分區的資料僅保留 730 天 (滾動週期)

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：使用 date 欄位
  • OPTIONS：指定 partition_expiration_days = 730
  • 資料表說明：weather stations with precipitation, partitioned by day

查詢內容應如下所示：

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=730, 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' 注意：這裡是將分區效期設為 730 天 (2 年)。在正式環境中，通常會根據特定的資料保留或隱私權規定來設定這個值，例如 60 或 90 天。本實驗室使用的公開天氣資料集屬於過往資料，不會每天更新，因此如果設定的效期較短，會導致資料表空白。

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

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

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

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

以下查詢可追蹤日本和歌山 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 值是否等於或少於 730 天

請更新 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 實驗室：
  • 排解資料 join 錯誤
  • 在 BigQuery 使用 JSON、陣列和 struct
  • 使用 BigQuery ML 預測模型預測計程車費用

Google Cloud 教育訓練與認證

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

使用手冊上次更新日期：2026 年 3 月 31 日

實驗室上次測試日期：2026 年 3 月 31 日

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