BigQuery UI 導覽與資料探索程式碼研究室

1. 簡介

BigQuery 是無伺服器、具備高擴充性與成本效益的資料倉儲。只要將資料移至 BigQuery，其他相關的繁雜工作可以放心交給 Google，讓您專注於真正重要的事，也就是經營業務。您可以根據業務需求控管專案與資料的存取權，例如授予他人檢視或查詢資料的權限。

在本實驗室中，您將瞭解 BigQuery 的分析功能。您將瞭解如何從 Google Cloud Storage 值區匯入資料集，並透過處理零售銀行資料集，掌握 BigQuery 使用者介面。此外，本實驗室也會說明如何發掘 BigQuery 的重要功能，讓日常分析工作更加輕鬆，例如將查詢結果匯出至試算表、查看及執行查詢記錄中的查詢、查看查詢效能，以及建立資料表檢視畫面供其他團隊和部門使用。

學習目標

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

  • 將新資料載入 BigQuery
  • 熟悉 BigQuery 使用者介面
  • 在 BigQuery 中執行查詢
  • 查看查詢成效
  • 在 BigQuery 中建立檢視區塊
  • 安全地與他人共用資料集

2. 簡介：瞭解 BigQuery 使用者介面

在本節中，您將瞭解如何瀏覽 BigQuery UI、查看可用的資料集，以及執行簡單的查詢。

載入 BQ UI

  1. 在 Google Cloud Platform 控制台頂端輸入「BigQuery」。
  2. 從選項清單中選取 BigQuery。請務必選取有 BigQuery 標誌 (放大鏡) 的選項。

查看資料集及執行查詢

ee95ce13969ee1ad.png

  1. 在左側窗格的「資源」部分中，點選 BigQuery 專案。
  2. 按一下 bq_demo 即可查看該資料集中的資料表
  3. 在「輸入要搜尋的內容」方塊中輸入「card」，即可查看名稱中含有「card」的資料表和資料集清單。
  4. 從搜尋結果清單中選取「card_transactions」資料表

beb6ff6ca2930125.png

  1. 按一下 card_transactions 窗格下方的「詳細資料」分頁標籤，即可查看這個資料表的相關中繼資料。
  2. 按一下「預覽」分頁標籤，即可預覽表格

[競爭優勢]： 與 Google 資料目錄整合後，BigQuery 中繼資料就能與其他資料來源 (例如資料湖泊或作業資料來源) 一併管理。這個例子說明 Google Cloud 不只是關聯資料倉儲，更是完整的分析資料平台。

  1. 按一下放大鏡圖示，查詢「card_transactions」表格。系統會自動產生文字，並填入 BigQuery 查詢編輯器。
  2. 請輸入下列程式碼，從 Card_Transactions 表格中顯示不同的商家
SELECT distinct (merchant) FROM bq_demo.card_transactions LIMIT 1000
  1. 按一下「執行」按鈕執行查詢。

35113542e7ec6fa6.png

3. 建立資料集及共用檢視畫面

分享資料和管理資料至關重要，您可以在 BQ 使用者介面中直覺地完成這項操作。在本節中，您將瞭解如何建立新資料集、填入檢視區塊，以及共用該資料集。

查看查詢記錄

  1. 在 GCP 主控台的左側窗格中，按一下「查詢記錄」
  2. 按一下「查詢記錄」窗格中的「重新整理」
  3. 按一下查詢最右側的下載圖片/箭頭，即可查看查詢結果。

6e3232ed96f647b8.png

建立新的資料集

  1. 在 BigQuery UI 的資源窗格中選取「[您的專案名稱]」。
  2. 從專案資訊窗格選取「建立新的資料集」
  3. 資料集 ID：

bq_demo_shared

  1. 其他欄位則全部保留預設值
  2. 按一下「建立資料集」

b433eba38f55124f.png dd774aca416e7fbc.png

建立檢視區塊

[競爭優勢]： BigQuery 完全符合 ANSI SQL 標準，支援簡單和複雜的多表格聯結，以及豐富的分析函式。我們持續強化對傳統資料倉儲中常見 SQL 資料類型和函式的支援，簡化遷移程序。

  1. 在查詢編輯器窗格頂端選取「撰寫新查詢」。
  2. 在查詢編輯器中插入下列程式碼
WITH revenue_by_month AS (
SELECT
    card.type AS card_type,
    FORMAT_DATE('%Y-%m', trans_date) as revenue_date,
    SUM(amount) as revenue
FROM bq_demo.card_transactions
JOIN bq_demo.card ON card_transactions.cc_number = card.card_number
WHERE trans_date  DATE_ADD(CURRENT_DATE, INTERVAL -1 YEAR)
GROUP BY card_type, revenue_date
)
SELECT
    card_type,
    revenue_date,
    revenue as monthly_rev,
    revenue -  LAG(revenue) OVER (ORDER BY card_type, revenue_date ASC) as rev_change
FROM revenue_by_month
ORDER BY card_type, revenue_date ASC;
  1. 按一下「儲存檢視畫面」
  2. 在「專案名稱」中選取目前的專案
  3. 選取新建立的資料集：

bq_demo_shared

  1. 資料表名稱：

rev_change_by_card_type

  1. 按一下「儲存」。

4b111056b544c27d.png

共用檢視區塊和資料集

  1. 在 BigQuery UI 的左側資源窗格中，選取「bq_demo_shared」資料集。
  2. 在資料集資訊窗格中，按一下「分享資料集」
  3. 輸入電子郵件地址
  4. 從「角色」下拉式選單中選取「BigQuery 資料檢視者」
  5. 請按一下 [新增]。
  6. 按一下「完成」

1c04b6b5ebc191dc.png

在 Google 試算表中探索資料

[競爭優勢 ]：與競爭對手相比，BigQuery 的另一項優勢是 BI Engine。BI Engine 可透過記憶體內快取引擎，讓 BI 類型的摘要查詢在不到一秒內傳回結果。Google 數據分析目前支援這項功能，但很快就會在 BigQuery 中推出，加快所有查詢速度。

例如：

Snowflake 依賴第三方商業智慧工具來製作資訊主頁和資料視覺化圖表，而 GCP 提供一系列整合式商業智慧工具，包括連結試算表、數據分析和 Looker。

  1. 在 BigQuery UI 的左側資源窗格中，選取「rev_change_by_card_type」檢視畫面。
  2. 按一下放大鏡 查詢檢視畫面 255be22b0eaf339.png
  3. Type:

SELECT *

FROM bq_demo_shared.rev_change_by_card_type

  1. 按一下「執行」
  2. 按一下「結果」窗格中的「匯出」圖示
  3. 選取「透過試算表探索資料」

9617b522025fd337.png

  1. 按一下「開始分析」
  2. 選取「樞紐分析表」
  3. 選取「新工作表」
  4. 按一下 [建立]
  5. 在 Google 試算表視窗右側的「資料透視表編輯器」中，將「revenue_date」新增至「資料列」部分
  6. 在資料透視表編輯器的「欄」部分下方新增「card_type」
  7. 在資料透視表編輯器的「欄」部分下方新增「monthly_rev」
  8. 按一下 [套用]

48e67c2e04965796.png

  1. 前往 Google 試算表使用者介面頂端的功能區，然後選取「插入」圖表

4. 設定：資料整合

在本節中，您將瞭解如何建立新資料表，並對 Google Cloud 提供的其中一個公開資料集執行 JOIN。

[Competitive Talking Point]：

BigQuery 多年來一直支援共用資料集。任何專案中的客戶都可以查詢公開資料集，以及與他們共用的其他專案資料集。

BigQuery 可透過外部資料表支援 GCS 中的資料湖。除了大量載入，BigQuery 也支援以每秒數百 MB 以上的速度，將資料串流至資料庫。Snowflake 不支援串流資料。

將資料匯入新資料表

  1. 在資源窗格中選取 bq_demo 資料集
  2. 在資料集資訊窗格中，選取「建立資料表」
  3. 選取 Google Cloud Storage 做為來源
  4. 在檔案路徑文字方塊中：

gs://retail-banking-looker/district

  1. 選取「CSV」做為檔案格式
  2. 在「Table Name」(資料表名稱) 輸入「district」
  3. 勾選「自動偵測結構定義」核取方塊
  4. 按一下「建立資料表」

查詢公開資料集

  1. 在查詢編輯器中輸入下列查詢：
SELECT
    CAST(geo_id as STRING) AS zip_code,
    total_pop,
    median_age,
    households,
    income_per_capita,
    housing_units,
    vacant_housing_units_for_sale,
    ROUND(SAFE_DIVIDE(employed_pop, pop_16_over),4) AS rate_employment,
    ROUND(SAFE_DIVIDE(bachelors_degree_or_higher_25_64, pop_25_64),4) AS rate_bachelors_degree_or_higher_25_64
  FROM
    `bigquery-public-data.census_bureau_acs.zip_codes_2017_5yr`;
  1. 按一下「執行」
  2. 查看結果

dff40709db70d75.png

  1. 現在，我們要將這項公開資料與另一個查詢合併。在查詢編輯器中輸入下列 SQL 程式碼：
WITH customer_counts AS (
    select regexp_extract(address, "[0-9][0-9][0-9][0-9][0-9]") as zip_code, 
    count(*) as num_clients
    FROM bq_demo.client
    GROUP BY zip_code
    )
SELECT 
    CAST(geo_id as STRING) AS zip_code,
    total_pop,
    median_age,
    households,
    income_per_capita,
    ROUND(SAFE_DIVIDE(employed_pop, pop_16_over),4) AS rate_employment,
    num_clients
FROM
    `bigquery-public-data.census_bureau_acs.zip_codes_2017_5yr`
JOIN customer_counts on zip_code = geo_id
ORDER BY num_clients DESC
  1. 按一下「執行」
  2. 查看結果

b853ad571e7a3038.png

5. 容量管理

使用運算單元和保留項目

BigQuery 提供多種計費模式，可滿足您的需求。大多數大型客戶主要採用固定費率，以預留容量來確保價格穩定。如要超出基準容量，BQ 提供彈性運算單元，讓您即時擴充額外容量，然後自動縮減，不會影響正在執行的查詢。BQ 也提供位元組掃描模型，讓您只須支付執行的查詢費用。

[競爭優勢： 部分競爭對手只提供固定容量模式，客戶必須為機構中的每項工作負載分配虛擬資料倉儲。除了低成本的查詢計費模式，方便您輕鬆開始使用 BigQuery，我們也支援固定費率的容量計費模式，讓一組工作負載共用閒置容量。]

  1. 前往「預訂」分頁。

964f4ab78d35d067.png

  1. 按一下「購買位置」

c8cb5ee61bbea814.png

  1. 選取「彈性」做為時間長度。
  2. 選取 500 個運算單元。
  3. 確認購買。

d615f5908dffc1ee.png

  1. 按一下「查看運算單元使用承諾」。
  2. 按一下「建立預留項目」
  3. 預訂名稱為「demo」的使用者
  4. 選取「美國」做為位置
  5. 輸入 500 個運算單元 (所有可用的運算單元)
  6. 按一下「指派項目」
  7. 為機構專案選取目前的專案
  8. 預訂 ID 請選取「demo」
  9. 按一下「建立」。