GSP414

總覽

BigQuery 是 Google 提供的全代管數據分析資料庫，不但免人工管理，而且價格低廉。您可以使用 BigQuery 查詢 TB 規模的資料，不必管理基礎架構，也不需要資料庫管理員。BigQuery 使用 SQL，並且採用「即付即用」模式。這項服務可讓您專心分析資料，找出有意義的深入分析結果。

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

學習內容

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

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

設定和需求

點選「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. 點選「+ 編寫新的查詢」，加入以下查詢：

#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. 按一下「編寫新的查詢」清除查詢編輯器內容，然後新增以下查詢。請注意，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. 按一下「編寫新的查詢」，新增以下查詢，然後點選「執行」：

#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 預測模型預測計程車費用

Google Cloud 教育訓練與認證

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

使用手冊上次更新日期：2024 年 2 月 3 日

實驗室上次測試日期：2024 年 1 月 1 日

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