概要

BigQuery は、Google が低価格で提供する NoOps、フルマネージドの分析データベースです。BigQuery では、インフラストラクチャを所有して管理したりデータベース管理者を置いたりすることなく、テラバイト単位の大規模なデータでクエリを実行できます。また、SQL が採用されており、従量課金制というメリットもあります。このような特徴を活かし、ユーザーは有用な情報を得るためのデータ分析に専念できます。

このラボでは、BigQuery での半構造化データの操作（JSON や配列データ型の取り込み）について詳しく学習します。スキーマを非正規化し、ネストされた繰り返しのフィールドを持つ単一のテーブルにすることで、パフォーマンスが改善する場合があります。ただし、配列データを操作する SQL 構文は複雑になることがあります。ここでは、さまざまな半構造化データセットに対する読み込み、クエリ実行、トラブルシューティング、ネスト解除を実際に行います。

目標

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

• 半構造化 JSON を BigQuery に読み込む
• 配列の作成とクエリ
• 構造体の作成とクエリ
• ネストされた繰り返しフィールドのクエリ

設定と要件

各ラボでは、新しい Google Cloud プロジェクトとリソースセットを一定時間無料で利用できます。

1. Qwiklabs にシークレット ウィンドウでログインします。

2. ラボのアクセス時間（例: 1:15:00）に注意し、時間内に完了できるようにしてください。
   一時停止機能はありません。必要な場合はやり直せますが、最初からになります。

3. 準備ができたら、[ラボを開始] をクリックします。

4. ラボの認証情報（ユーザー名とパスワード）をメモしておきます。この情報は、Google Cloud Console にログインする際に使用します。

5. [Google Console を開く] をクリックします。

6. [別のアカウントを使用] をクリックし、このラボの認証情報をコピーしてプロンプトに貼り付けます。
   他の認証情報を使用すると、エラーが発生したり、料金の請求が発生したりします。

7. 利用規約に同意し、再設定用のリソースページをスキップします。

BigQuery コンソールを開く

1. Google Cloud Console で、ナビゲーション メニュー > [BigQuery] を選択します。

[Cloud Console の BigQuery へようこそ] メッセージ ボックスが開きます。このメッセージ ボックスにはクイックスタート ガイドへのリンクと、UI の更新情報が表示されます。

2. [完了] をクリックします。

タスク 1. 新しいデータセットを作成してテーブルを保存する

1. データセットを作成するには、プロジェクト ID の横にある [アクションを表示] アイコンをクリックし、[データセットを作成] を選択します。

2. 新しいデータセットに「fruit_store」という名前を付けます。その他のオプションはデフォルト値のままにします（データのロケーション、[デフォルトのテーブルの有効期限]）。

3. [データセットを作成] をクリックします。

タスク 2. SQL での配列の操作を練習する

通常、SQL では、以下の果物リストのように各行に値が 1 つ含まれます。

行	果物
1	raspberry
2	blackberry
3	strawberry
4	cherry

果物リストに店舗の担当者名が必要な場合はどうすればよいでしょうか。次のようになります。

行	果物	担当者
1	raspberry	sally
2	blackberry	sally
3	strawberry	sally
4	cherry	sally
5	orange	frederick
6	apple	frederick

従来のリレーショナル データベースの SQL では、同じ名前が複数回出現する場合、上記のテーブルを果物と担当者の 2 つの別個のテーブルに分割することを考えます。これを正規化と呼びます（1 つのテーブルを多数のテーブルに分割）。mySQL のようなトランザクション データベースでよく行われます。

データ ウェアハウジングでよく行われるのはその逆の操作（非正規化）で、多数のテーブルを 1 つの大きなレポート テーブルにまとめます。

ここでは、繰り返しフィールドを使用して、粒度の異なるデータをすべて 1 つのテーブルに格納する方法を学びます。

行	果物（配列）	担当者
1	raspberry	sally
	blackberry
	strawberry
	cherry
2	orange	frederick
	apple

上のテーブルの不自然な点はどこでしょうか。

• 行が 2 つだけである。
• [果物] 列では、1 つの行に複数のフィールド値がある。
• 担当者がすべてのフィールド値に関連付けられている。

ここからわかるのは、array データ型が使用されているということです。

以下のように記述すると、果物の配列について理解しやすくなります。

行	果物（配列）	担当者
1	[raspberry, blackberry, strawberry, cherry]	sally
2	[orange, apple]	frederick

これら両方のテーブルは同じ内容を表します。主な学習のポイントは 2 つあります。

• 配列は単純に [ ] で囲まれた項目のリストである
• BigQuery では配列が「フラット化」されて表示され、配列の値が単純に一列にリストされる（それでも、これらの値は 1 つの行に含まれています）

実際に試す

1. BigQuery のクエリエディタに次のクエリを入力します。

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry'] AS fruit_array

2. [実行] をクリックします。

3. 次のクエリを実行します。

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry', 1234567] AS fruit_array

次のようなエラーが表示されます。

Error: Array elements of types {INT64, STRING} do not have a common supertype at [3:1]

配列内では同じデータ型を使用する必要があります（すべて文字列、すべて数値など）。

4. 最後に、テーブルに対して次のクエリを実行します。

#standardSQL SELECT person, fruit_array, total_cost FROM `data-to-insights.advanced.fruit_store`;

5. [実行] をクリックします。

6. 結果が表示されたら [JSON] タブをクリックして、ネストされた結果の構造を確認します。

半構造化 JSON を BigQuery に読み込む

BigQuery に JSON ファイルを取り込む必要がある場合はどうすればよいでしょうか。

1. fruit_store データセットに新しいテーブルを作成します。
2. テーブルを作成するには、fruit_store の横にある [アクションを表示] アイコンをクリックして、[テーブルを作成] を選択します。

3. 次のようにテーブルの詳細を設定します。

• ソース: [テーブルの作成元] プルダウンで [Google Cloud Storage] を選択します。
• GCS バケットからファイルを選択（次を入力または貼り付け）: 「cloud-training/data-insights-course/labs/optimizing-for-performance/shopping_cart.json」
• ファイル形式: JSONL（改行区切り JSON）{自動的に入力されます}
• [スキーマ]: [自動検出] チェックボックスをオンにします（スキーマと入力パラメータ）。

4. 新しいテーブルの名前を「fruit_details」にします。

5. [テーブルを作成] をクリックします。

スキーマで fruit_array が「REPEATED」に設定されているため、このフィールドが配列であることがわかります。

内容のまとめ

• BigQuery は配列をネイティブにサポートする
• 配列値のデータ型はすべて同じでなければならない
• BigQuery では配列を繰り返しフィールド（REPEATED）と呼ぶ

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 新しいデータセットを作成し、JSON データをテーブルに読み込む

タスク 3. ARRAY_AGG() を使用して独自の配列を作成する

今度は配列を自分のテーブル内に作成しましょう。

1. 以下のクエリをコピーして貼り付け、この一般公開データセットを探索します。

SELECT fullVisitorId, date, v2ProductName, pageTitle FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 ORDER BY date

2. [実行] をクリックして結果を確認します。

3. 次に、ARRAY_AGG() 関数を使用して、これらの文字列値を 1 つの配列にまとめます。以下のクエリをコピーして貼り付け、この一般公開データセットを探索します。

SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

4. [実行] をクリックして結果を確認します。

5. 次に、ARRAY_LENGTH() 関数を使用して、閲覧されたページと商品の数を調べます。

SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(v2ProductName)) AS num_products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(pageTitle)) AS num_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

6. 次に、ページと商品の重複を除去して、閲覧された一意の商品の数を調べます。DISTINCT を ARRAY_AGG() に追加するだけです。

SELECT fullVisitorId, date, ARRAY_AGG(DISTINCT v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT v2ProductName)) AS distinct_products_viewed, ARRAY_AGG(DISTINCT pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT pageTitle)) AS distinct_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

内容のまとめ

次のように、配列に関連する便利な関数があります。

• ARRAY_LENGTH(<array>) で要素の数を調べる
• ARRAY_AGG(DISTINCT <field>) で要素の重複を除去する
• ARRAY_AGG(<field> ORDER BY <field>) で要素を並べ替える
• ARRAY_AGG(<field> LIMIT 5) で要素の数を制限する

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 ARRAY_AGG() を使用して配列を作成する

タスク 4. すでに配列が含まれているデータセットをクエリする

Google アナリティクス向けの BigQuery 一般公開データセット bigquery-public-data.google_analytics_sample には、このコースのデータセット data-to-insights.ecommerce.all_sessions より多くのフィールドと行が含まれています。さらに重要な点として、商品、ページ、トランザクションなどのフィールド値が、配列としてネイティブに格納されています。

1. 以下のクエリをコピーして貼り付け、どのようなデータがあるか調べます。繰り返し値（配列）を含むフィールドがないか探してみてください。

SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

2. [実行] をクリックしてクエリを実行します。

3. 結果を右にスクロールして、hits.product.v2ProductName フィールドを探します（複数フィールドのエイリアスについてはこの後で説明します）。

4. Google アナリティクス スキーマに含まれているフィールドは、ここで分析するには多すぎます。前と同じように、訪問者とページ名のフィールドだけをクエリしてみましょう。

SELECT visitId, hits.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

「Cannot access field product on a value with type ARRAY> at [5:8]」というエラーが表示されます。

繰り返しフィールド（配列）を通常どおりにクエリするには、まず配列を分割して複数の行に戻す必要があります。

たとえば、hits.page.pageTitle の配列は、次のように 1 つの行として格納されています。

['homepage','product page','checkout']

これを次のようにする必要があります。

['homepage', 'product page', 'checkout']

これを SQL で行うにはどうすればよいでしょうか。

5. 解答: 配列フィールドで UNNEST() 関数を使用します。

SELECT DISTINCT visitId, h.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, UNNEST(hits) AS h WHERE visitId = 1501570398 LIMIT 10

UNNEST() については後ほど詳しく説明します。ここでは差し当たり、次のことを覚えておいてください。

• 配列要素を行に戻すには UNNEST() を使用する
• UNNEST() は常に FROM 句のテーブル名の後に指定する（概念的には、事前に結合されたテーブルに似ています）

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 すでに配列が含まれているデータセットをクエリする

タスク 5. 構造体の概要

フィールド エイリアスである hit.page.pageTitle について、3 つのフィールドをドットで区切って 1 つにまとめたように見えるのが気になった方もいらっしゃるでしょう。配列値を使用すると、フィールドの粒度をより細かく「掘り下げる」ことができますが、これと同様に、関連するフィールドをグループ化してスキーマを「広げる」ことができるデータ型があります。それが、SQL データ型の STRUCT（構造体）です。

概念的には、構造体はメインテーブルに事前に結合された別テーブルのようなものと考えるとわかりやすくなります。

構造体には次のような特徴があります。

• 1 つ以上のフィールドを含めることができる
• フィールドのデータ型は同じでなくてもよい
• 固有のエイリアスがある

このように、構造体はテーブルによく似ています。

構造体を含むデータセットを探索する

1. [エクスプローラ] で、bigquery-public-data データセットを探します。

2. まだ表示されていない場合は、[追加] > [名前を指定してプロジェクトにスターを付ける] をクリックします。

3. [プロジェクト名を入力] をクリックします。

4. 「bigquery-public-data」と入力して [スターを付ける] をクリックします。

5. 固定されたプロジェクトのリストで [bigquery-public-data] をクリックして開きます。

6. google_analytics_sample を探して開きます。

7. ga_sessions テーブルをクリックします。

8. スキーマをスクロールし、ブラウザの検索機能（Ctrl+F キー）を使って次の質問に答えてください。ヒント: カウントを開始する前にすべての列を開きます。

9. ご想像のとおり、今日の e コマースサイトで格納されるウェブサイトのセッション データは膨大な量になります。1 つのテーブルで 32 個もの構造体を使用する最大のメリットは、結合を一切行わずに次のようなクエリを実行できることです。

SELECT visitId, totals.*, device.* FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398 LIMIT 10 注: `.*` という構文を使用すると、その構造体のすべてのフィールドが　BigQuery から返されます（totals.* で別のテーブルを結合した場合によく似ています）。

大きなレポート テーブルを構造体（事前に結合された「テーブル」）や配列（粒度が細かい）として格納すると、次のようなメリットがあります。

• テーブルを 32 回も結合する必要がなくなるため、パフォーマンスが大幅に向上する
• 配列から必要に応じて細かい粒度でデータを取得でき、その必要がないときのデメリットもない（BigQuery では各列が個別にディスクに保存されます）
• すべてのビジネスデータが 1 つのテーブルに含まれるようになるため、結合キーに煩わされたり、必要なデータがどのテーブルにあるかを調べたりする必要がなくなる

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 構造体を含むデータセットを探索する

タスク 6. 構造体と配列の使い方を練習する

次のデータセットは、トラックを走るランナーのラップタイムです。各ラップは「スプリット」と呼ばれます。

1. このクエリで STRUCT 構文を試します。構造体コンテナ内には異なるフィールド タイプが混在する点に注意してください。

#standardSQL SELECT STRUCT("Rudisha" as name, 23.4 as split) as runner

行	runner.name	runner.split
1	Rudisha	23.4

フィールドのエイリアスについて、どのようなことがわかりますか。構造体内にネストされているフィールドがあるため（name と split が runner のサブセット）、ドットを使用して区切りが示されています。

1 つのレースにランナーのスプリット タイムが複数ある場合はどうなるでしょうか（ラップごとのタイムなど）。

2. その場合は配列を使用します。次のクエリを実行して確認します。

#standardSQL SELECT STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner

行	runner.name	runner.splits
1	Rudisha	23.4
		26.3
		26.4
		26.1

まとめると次のようになります。

• STRUCT は、ネストされた複数のフィールド名とデータ型を内部に保持できるコンテナです。
• 構造体内のフィールド タイプの 1 つに配列があります（上記の splits フィールドを参照）。

JSON データを実際に取り込む

1. 「racing」という名前の新しいデータセットを作成します。

2. 「race_results」という名前の新しいテーブルを作成します。

3. 以下の Google Cloud Storage の JSON ファイルを取り込みます。

cloud-training/data-insights-course/labs/optimizing-for-performance/race_results.json

• ソース: [テーブルの作成元] プルダウン メニューから [Google Cloud Storage] を選択します。
• GCS バケットからファイルを選択: cloud-training/data-insights-course/labs/optimizing-for-performance/race_results.json
• ファイル形式: JSONL（改行区切り JSON）
• [スキーマ] で、[テキストとして編集] スライダーを移動して次の内容を追加します。

[ { "name": "race", "type": "STRING", "mode": "NULLABLE" }, { "name": "participants", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "name", "type": "STRING", "mode": "NULLABLE" }, { "name": "splits", "type": "FLOAT", "mode": "REPEATED" } ] } ]

4. [テーブルを作成] をクリックします。

5. 読み込みジョブが完了したら、新しく作成されたテーブルのスキーマをプレビューします。

構造体のフィールドはどれでしょうか。それを知る手掛かりは何ですか。

participants フィールドは RECORD 型なので構造体です。

配列のフィールドはどれでしょうか。

participants.splits フィールドは、親である participants 構造体内の FLOAT の配列です。モードが REPEATED であるため、配列であることがわかります。この配列の値は、単一のフィールドに複数の値が含まれるため「ネストされた値」と呼ばれます。

ネストされた繰り返しフィールドに対してクエリを実行する

1. 800 メートルレースのすべてのランナーを確認しましょう。

#standardSQL SELECT * FROM racing.race_results

何件の行が返されましたか。

解答: 1

各ランナーの名前とレースの種類を一覧表示するにはどうすればよいでしょうか。

2. 次のクエリを実行するとどうなるか確認しましょう。

#standardSQL SELECT race, participants.name FROM racing.race_results

Error: Cannot access field name on a value with type ARRAY\<STRUCT\<name STRING, splits ARRAY\<FLOAT64\>>>> at [1:21]

集計関数を使う際に GROUP BY を忘れた状態に似ています。ここでは、粒度の異なる 2 つの項目があります。レースが 1 行、参加者名が 3 行です。これをどのように変更すればよいでしょうか。

行	race	participants.name
1	800M	Rudisha
2	???	Makhloufi
3	???	Murphy

これを次のようにします。

行	race	participants.name
1	800M	Rudisha
2	800M	Makhloufi
3	800M	Murphy

従来のリレーショナル SQL では、レースのテーブルと参加者のテーブルがある場合、両方のテーブルから情報を取得するにはテーブルを結合する必要があります。ここでは、参加者の STRUCT（概念的にはテーブルによく似ています）は、すでにレースのテーブルには含まれていますが、STRUCT ではない「race」フィールドとはまだ適切に関連付けられていません。

最初のテーブルで 800M レースを各ランナーと関連付けるために使用する SQL コマンドは何ですか。

解答: CROSS JOIN

では、先に進みましょう。

3. 次のクエリを実行します。

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN participants # これは STRUCT（テーブル内のテーブルのようなもの）です

Error: Table name "participants" cannot be resolved: dataset name is missing.

参加者の STRUCT はテーブルに似ていますが、技術的には racing.race_results テーブル内のフィールドの 1 つです。

4. クエリにデータセット名を追加します。

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN race_results.participants # 完全な STRUCT 名

5. [実行] をクリックします。

効果がありました。各レースのランナーがすべて一覧表示されました。

行	race	name
1	800M	Rudisha
2	800M	Makhloufi
3	800M	Murphy
4	800M	Bosse
5	800M	Rotich
6	800M	Lewandowski
7	800M	Kipketer
8	800M	Berian

以下の方法で最後のクエリを簡素化できます。

• 元のテーブルのエイリアスを追加する
• 「CROSS JOIN」の句をカンマで置き換える（カンマは暗黙的にクロス結合を表す）

これで同じクエリ結果が得られます。

#standardSQL SELECT race, participants.name FROM racing.race_results AS r, r.participants

レースの種類が複数ある場合（800M、100M、200M）、クロス結合では、デカルト積のように各ランナーの名前がすべてのレースと関連付けられることはないのでしょうか。

解答: そのようにはなりません。これは相関クロス結合であり、個々の行に関連付けられた要素のみが展開されます。詳しくは、配列と構造体の操作をご覧ください。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 構造体と配列の使い方を練習する

構造体のまとめ:

• SQL の STRUCT は、単純に他のデータ フィールド（データ型は異なっていても構いません）のコンテナです。構造体という言葉はデータが構造化されていることを表します。先ほどの例を思い出してください。
• STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner
• STRUCT にはエイリアス（上記の runner）が指定されます。概念的には、メインテーブル内に含まれるテーブルと考えることができます。
• STRUCT（および ARRAY）の要素を操作するには、事前にその STRUCT（および ARRAY）を展開する必要があります。構造体自体の名前または構造体に含まれる配列のフィールドを UNNEST() で囲み、展開してフラット化します。

タスク 7. ラボの質問: STRUCT()

先ほど作成した racing.race_results テーブルを使用して以下の質問に解答してください。

タスク: 参加したランナーの合計数を取得するクエリを作成してください。

• まず、部分的に作成済みの次のクエリを使用します。

#standardSQL SELECT COUNT(participants.name) AS racer_count FROM racing.race_results 注: FROM の後に追加のデータソースとして、構造体名でクロス結合する必要があります。

解答例:

#standardSQL SELECT COUNT(p.name) AS racer_count FROM racing.race_results AS r, UNNEST(r.participants) AS p

行	racer_count
1	8

解答: レースに参加したランナーは 8 人です。

タスク 8. ラボの質問: ARRAY を UNNEST( ) で展開する

名前が「R」で始まるランナーの合計レース時間を一覧表示するクエリを作成します。合計時間が短いランナーが先に表示されるように並べ替えます。UNNEST() 演算子を使って、部分的に作成済みの次のクエリで作業を開始します。

• クエリを完成させてください。

#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_times WHERE GROUP BY ORDER BY ; 注:

FROM 句で、構造体と構造体に含まれる配列の両方をデータソースとして展開する必要があります。

必要に応じてエイリアスを使用します。

解答例:

#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_times WHERE p.name LIKE 'R%' GROUP BY p.name ORDER BY total_race_time ASC;

行	name	total_race_time
1	Rudisha	102.19999999999999
2	Rotich	103.6

タスク 9. ラボの質問: 配列内の値をフィルタする

800M のレースで最も速いラップタイムは、23.2 秒でした。ただし、それがどのランナーの記録であるかは確認できていません。その結果を返すクエリを作成します。

• タスク: 不完全なクエリを補う

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_time WHERE split_time = ;

解答例:

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_time WHERE split_time = 23.2;

行	name	split_time
1	Kipketer	23.2

お疲れさまでした

JSON データセットを取り込み、配列と構造体を作成し、半構造化データをネスト解除して分析情報を得ることができました。

次のステップと詳細情報

詳しくは、配列の操作をご覧ください。

Copyright 2020 Google LLC All rights reserved. Google および Google のロゴは Google LLC の商標です。その他すべての企業名および商品名はそれぞれ各社の商標または登録商標です。