BigQuery UI のナビゲーションとデータ探索の Codelab

1. はじめに

BigQuery は、サーバーレスでスケーラビリティと費用対効果に優れたデータ ウェアハウスです。データを BigQuery に読み込んだら、後の処理は Google に任せられるため、お客様は本当に重要な業務、つまり事業運営に集中できます。他のユーザーにデータの表示やクエリを許可するなど、ビジネスニーズに基づいてプロジェクトとデータへのアクセスを制御できます。

このラボでは、BigQuery が持つ分析の可能性を確認します。ここでは、Google Cloud Storage バケットからデータセットをインポートし、リテール バンキングのデータセットを使用して BigQuery の UI を把握する方法を学びます。さらに、スプレッドシートでのクエリ結果のエクスポート、クエリ履歴からのクエリの表示と実行、クエリのパフォーマンスの表示、他のチームや部門で使用するテーブルビューの作成など、日々の分析を容易にする BigQuery の主な機能を確認する方法を学びます。

学習内容

このラボでは、次のタスクの実行方法について学びます。

  • BigQuery に新しいデータを読み込む
  • BigQuery UI について理解する
  • BigQuery でのクエリの実行
  • クエリのパフォーマンスを表示
  • BigQuery でビューを作成する
  • 他のユーザーとデータセットを安全に共有する

2. 概要: BigQuery UI について

このセクションでは、BigQuery UI を操作する方法、利用可能なデータセットを表示する方法、簡単なクエリを実行する方法を学習します。

BQ UI の読み込み

  1. 「BigQuery」と入力しますGoogle Cloud Platform Console の最上部にあります。
  2. オプション リストから [BigQuery] を選択します。BigQuery のロゴと虫メガネがあるオプションを選択してください。

データセットの表示とクエリの実行

ee95ce13969ee1ad.png

  1. 左側のペインの [リソース] で、BigQuery プロジェクトをクリックします。
  2. bq_demo をクリックすると、そのデータセット内のテーブルが表示されます。
  3. 検索ボックスに「カード」と入力します「card」を含むテーブルとデータセットのリストを表示する表示されます。
  4. [card_transactions] を選択します結果リストからテーブルを選択し

beb6ff6ca2930125.png

  1. card_transactions ペインの [Details] タブをクリックして、このテーブルのメタデータを表示します。
  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] ペインで [更新] をクリックする
  3. クエリの右端にあるダウンロード画像または矢印をクリックして、クエリの結果を表示します。

6e3232ed96f647b8.png

新しいデータセットの作成

  1. BigQuery UI のリソースペインで [プロジェクト名] を選択します。
  2. [新しいデータセットを作成] を選択します。プロジェクト情報ペインから
  3. データセット ID:

bq_demo_shared

  1. その他のフィールドはすべてデフォルトのままにする
  2. [データセットを作成] をクリックします。

b433eba38f55124f.png dd774aca416e7fbc.png

ビューの作成

[競合についての論点]: BigQuery は ANSI SQL に完全に対応しており、単純な、複雑な複数テーブル結合と豊富な分析関数の両方をサポートしています。Google は、移行プロセスを容易にするために、従来のデータ ウェアハウスで使用されている一般的な 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. [bq_demo_shared] を選択しますBigQuery UI の左側のリソースペインから確認できます。
  2. [データセットを共有] をクリックします。データセット情報のペインから
  3. メールアドレスを入力
  4. [BigQuery Data Viewer] を選択します。[ロール] プルダウン メニューから
  5. [追加] をクリックします。
  6. [完了] をクリックします。

1c04b6b5ebc191dc.png

スプレッドシートでデータを探索

[競合についての論点]: 競合他社と比較した BigQuery のもう一つのメリットは、BI Engine です。BI Engine を使用すると、メモリ内キャッシュ エンジンによって BI タイプのサマリークエリが 1 秒未満で返されるようになります。これは現在 Google データポータルでサポートされていますが、まもなく BigQuery ですべてのクエリを高速化できるようになります。

例:

Snowflake はダッシュボードとデータの可視化にサードパーティの BI ツールを利用しています。一方、GCP はコネクテッド シート、データポータル、Looker などの幅広い統合 BI ツールを提供しています。

  1. [rev_change_by_card_type] を選択します。BigQuery UI の左側のリソースペインから確認できます。
  2. 虫メガネをクリックしてビュー 255be22b0eaf339.png をクエリします。
  3. タイプ:

SELECT *

FROM bq_demo_shared.rev_change_by_card_type（bq_demo_shared.rev_change_by_card_type）

  1. [実行] をクリックします。
  2. [エクスポート] をクリックします。結果ペインのアイコン
  3. [スプレッドシートでデータを探索] を選択します。

9617b522025fd337.png

  1. [分析を開始] をクリックします
  2. [ピボット テーブル] を選択します。
  3. [新しいシート] を選択します。
  4. [作成] をクリック
  5. 「revenue_date」を追加[スプレッドシート] ウィンドウの右側にあるピボット テーブル エディタの [行] セクション
  6. 「card_type」を追加しますピボットテーブルエディタの [列]セクションで
  7. 「monthly_rev」を追加ピボットテーブルエディタの [列]セクションで
  8. [申請] をクリックします

48e67c2e04965796.png

  1. スプレッドシートの UI の一番上に移動し、[グラフを挿入] を選択します。

4. 設定: データ統合

このセクションでは、新しいテーブルを作成し、Google Cloud で利用可能な多くの一般公開データセットのいずれかで JOIN を実行する方法を学びます。

[競争力のある説明のポイント]:

BigQuery は長年にわたって共有データセットをサポートしてきました。どのプロジェクトのユーザーでも、一般公開データセットと、共有されている他のプロジェクトのデータセットの両方に対してクエリを実行できます。

BigQuery は、外部テーブルを使用することで GCS 内のデータレイクをサポートできます。BigQuery では、一括読み込みに加え、毎秒数百 MB 以上の速度でデータをデータベースにストリーミングできます。Snowflake はストリーミング データをサポートしていません。

新しいテーブルへのデータのインポート

  1. [リソース] ペインで、bq_demo データセットを選択します。
  2. データセット情報ペインで [テーブルを作成] を選択します。
  3. ソースに Google Cloud Storage を選択する
  4. ファイルパスのテキスト ボックスで次の操作を行います。

gs://retail-banking-looker/district

  1. ファイル形式に CSV を選択
  2. 「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. 容量管理

スロットと予約の操作

BQ には、ニーズに合わせて複数の料金モデルが用意されています。大規模なお客様のほとんどは主に、予約された容量を使用した予測可能な料金で定額料金を利用しています。ベースライン容量を超えてバーストする場合、BQ には Flex Slots のスロットが用意されており、その場で追加の容量に拡張し、クエリの実行に影響を与えることなく自動的に縮小できます。BigQuery にはバイトスキャン モデルも用意されており、実行したクエリに対してのみ料金が発生します。

[競合他社に関する説明: 一部の競合他社は固定容量モデルのみに取り組んでいます。このモデルでは、お客様が組織内の各ワークロードに仮想ウェアハウスを 1 つ割り当てる必要があります。BigQuery を簡単に使い始めることができる低コストのクエリごとのモデルに加え、一連のワークロード間でアイドル容量を共有できる定額容量料金モデルもサポートしています。]

  1. [予約] タブに移動します。

964f4ab78d35d067.png

  1. [スロットの購入] をクリックします。

c8cb5ee61bbea814.png

  1. [Flex] を選択します。指定します。
  2. 500 個のスロットを選択します。
  3. 購入を確認します。

d615f5908dffc1ee.png

  1. [スロットのコミットメントを表示] をクリックします。
  2. [予約を作成] をクリックします
  3. ユーザー「demo」予約名として
  4. 地域として米国を選択
  5. スロットにタイプ 500（すべて使用可能）
  6. [割り当て] をクリックします。
  7. 組織のプロジェクトの現在のプロジェクトを選択する
  8. 「demo」を選択（予約 ID 用）
  9. [作成] をクリックします。