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

1. 簡介

BigQuery 是具備高擴充性與成本效益的無伺服器資料倉儲系統。只要將資料移至 BigQuery，其他繁雜的工作就交給我們，您就可以專注經營業務和業務。您可以根據業務需求控管對專案與資料的存取權，例如授予他人檢視或查詢資料的權限。

在本研究室中，您將探索 BigQuery 的分析可能性。您將瞭解如何從 Google Cloud Storage 值區匯入資料集，以及如何使用零售銀行資料集來掌握 BigQuery UI。此外，本研究室將說明如何在 BigQuery 中發掘重要功能，更輕鬆地進行日常分析，例如將查詢結果匯出成試算表、查看和執行查詢記錄中的查詢、查看查詢效能，以及建立資料表檢視畫面供其他團隊和部門使用。

學習目標

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

  • 將新資料載入 BigQuery
  • 熟悉 BigQuery UI
  • 在 BigQuery 中執行查詢
  • 查看查詢效能
  • 在 BigQuery 中建立檢視表
  • 安全地與他人共用資料集

2. 簡介：瞭解 BigQuery UI

本節將說明如何瀏覽 BigQuery UI、查看可用資料集，以及執行簡單的查詢。

載入 BQ UI

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

檢視資料集和執行查詢

ee95ce13969ee1ad.png

  1. 在「資源」部分的左側窗格中，按一下您的 BigQuery 專案。
  2. 按一下 bq_demo，即可查看該資料集內的資料表
  3. 在搜尋框中輸入「card」即可查看包含「資訊卡」的資料表和資料集清單。
  4. 選取「card_transactions」表格

beb6ff6ca2930125.png

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

[競爭談話點]： 與 Google Data Catalog 整合，代表 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 UI 中能以直覺化的方式完成這項作業。在本節中，您將瞭解如何建立新的資料集、為資料集填入資料，以及共用該資料集。

查看查詢記錄

  1. 按一下 [查詢記錄]透過 GCP 控制台的左側窗格
  2. 在「Query History」(查詢記錄) 窗格中按一下「Refresh」(重新整理)
  3. 按一下查詢最右側的下載圖片/箭頭即可查看查詢結果。

6e3232ed96f647b8.png

建立新的資料集

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

bq_demo_shared

  1. 其他欄位則保留預設值
  2. 按一下 [建立資料集]

b433eba38f55124f.png dd774aca416e7fbc.png

建立檢視表

[競爭談話要點]： BigQuery 與 ANSI SQL 完全相容，支援簡單和複雜的多資料表彙整，以及豐富的分析函式。我們會持續強化支援傳統資料倉儲中常見的 SQL 資料類型和函式，簡化遷移程序。

  1. 選取「撰寫新查詢」位於「Query Editor」窗格頂端。
  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. 選取「bq_demo_shared」資料集。
  2. 按一下 [分享資料集]在資料集資訊窗格中
  3. 請輸入電子郵件地址
  4. 選取「BigQuery 資料檢視者」選取「角色」下拉式選單
  5. 請按一下 [新增]。
  6. 按一下「完成」

1c04b6b5ebc191dc.png

探索 Google 試算表中的資料

[競爭談話要點]： 與競爭對手相比，BigQuery 的另一項優勢是 BI Engine。BI Engine 可用於記憶體內快取引擎，讓 BI 類型摘要查詢在一秒內傳回。目前 Google 數據分析支援這項功能，但不久後就會加快 BigQuery 所有查詢的速度。

例如：

Snowflake 仰賴第三方商業智慧 (BI) 工具處理資訊主頁和資料視覺化，同時也是 GCP 提供的一系列整合式商業智慧工具，包括連結試算表、數據分析和 Looker。

  1. 選取「rev_change_by_card_type」查看報表
  2. 按一下放大鏡即可查詢檢視表 255be22b0eaf339.png
  3. Type:

SELECT *

FROM bq_demo_shared.rev_change_by_card_type

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

9617b522025fd337.png

  1. 按一下 [開始分析]
  2. 選取「資料透視表」
  3. 選取「新工作表」
  4. 按一下 [建立]
  5. 新增「revenue_date」「資料透視表編輯器」(位於「試算表」視窗右側) 的「列」部分
  6. 新增「card_type」資料透視表編輯器的「欄」部分
  7. 新增「monthly_rev」資料透視表編輯器的「欄」部分
  8. 按一下 [套用]

48e67c2e04965796.png

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

4. 設定：資料整合

本節將說明如何建立新的資料表，以及如何使用 Google Cloud 的其中一個公開資料集執行彙整作業。

[Competitive Talking Point]：

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

BigQuery 可以透過使用外部資料表，支援 GCS 中的資料湖泊。除了大量載入之外，BigQuery 也能以每秒高達數百 MB 的速率，將資料串流至資料庫。Snowflake 不支援串流資料。

將資料匯入新資料表

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

gs://retail-banking-looker/district

  1. 選取 CSV 做為檔案格式
  2. 輸入「區」代表資料表名稱
  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. 容量管理

使用運算單元和預留項目

BQ 提供多種計費模式，可滿足您的需求。大部分的大型客戶主要是採用固定費率，以享有預留容量的可預測價格。針對爆發速度超過基準容量的爆發模式，BigQuery 提供彈性運算單元，可即時擴增至額外容量，並在不影響執行查詢的情況下自動縮減資源。BQ 也是一種位元組掃描模型，可讓您僅為執行的查詢付費。

[競爭談話要點： 有些競爭對手只使用固定容量模型，客戶必須為機構的每項工作負載分配虛擬倉儲。除了以費用為低的個別查詢模式來輕鬆開始使用 BigQuery 之外，我們也支援固定費率定價模式，讓多個工作負載可共用閒置容量。]

  1. 前往「預訂」分頁。

964f4ab78d35d067.png

  1. 按一下「購買運算單元」

c8cb5ee61bbea814.png

  1. 選取「Flex」。
  2. 請選取 500 個運算單元。
  3. 確認購買。

d615f5908dffc1ee.png

  1. 按一下「查看運算單元使用承諾」。
  2. 按一下 [建立預留項目]
  3. 使用者「示範」視為預留項目名稱
  4. 將地區設為「美國」
  5. 針對運算單元，輸入 500 (所有可用)
  6. 按一下「指派項目」
  7. 為機構專案選擇目前的專案
  8. 選取「示範」預留項目 ID
  9. 按一下「建立」。