概要

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

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

目標

このラボでは、次の方法について学びます。

• JSON データセットを取り込む
• ARRAY と STRUCT を作成する
• 半構造化データをネスト解除してインサイトを得る

設定と要件

各ラボでは、新しい 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 つの別個のテーブルに分割することを考えます。

BigQuery では、配列データ型を使用して以下のような形を実現することで、より簡単に対応できます。

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

従来のテーブルとこのテーブルの違いを確認しましょう。

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

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

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

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

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

1. 実際に試してみましょう。BigQuery のクエリエディタに次のクエリを入力します。

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

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

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

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry', 33] 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 に JSON ファイルを取り込む必要がある場合はどうすればよいでしょうか。実際に試してみましょう。

fruit_store データセットに新しいテーブルを作成します。

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

2. 次に右側のパネルの [テーブルを作成] をクリックします。

注: 場合によっては、[テーブルを作成] オプションを表示するためにブラウザ ウィンドウを広げる必要があります。

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

• ソース: [テーブルの作成元] プルダウンで [Google Cloud Storage] を選択します。
• [GCS バケットからファイルを選択]: cloud-training/data-insights-course/labs/optimizing-for-performance/shopping_cart.json
• [ファイル形式]: JSONL（改行区切り JSON）

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

5. [スキーマ] で、[自動検出] チェックボックスをクリックします。

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

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

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

タスク 3. 異なるデータ型を含むエンティティのデータを保存する

先ほど確認したように、配列には同じデータ型のみを格納できますが、複数のフィールド名とデータ型が存在する場合は STRUCT（構造体）のデータ型を使用できます。

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

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.split
1	Rudisha	23.4 26.3 26.4 26.1

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

• STRUCT は、内部に複数のフィールド名とデータ型を保持できるコンテナです。
• 構造体内のフィールドの型には配列を使用できます（上記の splits フィールドを参照）。

他のランナーのレース結果を新しいテーブルに読み込む

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）に指定し、[テーブル名] を race_results に設定します。

4. [テキストとして編集] スライダーを移動し、次の内容を追加します。

[ { "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" } ] } ]

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

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

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

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

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

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

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

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

#standardSQL SELECT * FROM racing.race_results

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

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 [2:27]

集計関数を使う際に 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 # this is the STRUCT (it's like a table within a table)

Error: Table name "participants" missing dataset while no default dataset is set in the request.

参加者の STRUCT はテーブルに似ていますが、厳密に言えば racing.race_results テーブル内のフィールドの 1 つです。

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

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN race_results.participants # full STRUCT name

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」の句をカンマで置き換える（カンマは暗黙的にクロス結合を表す）

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

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

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

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

STRUCT（構造体）のまとめ:

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

ラボの質問: 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 人です。

タスク 4. 配列を UNNEST( ) で展開する

STRUCT の操作方法を確認したので、この知識を利用して、ARRAY を展開して従来の複数行による配列にします。

UNNEST 演算子は ARRAY を受け取り、ARRAY 内の各要素を 1 行にしてテーブルを返します。

これにより、以下のような通常の SQL オペレーションを実行できます。

• ARRAY 内の値を集計する。
• 特定の値で配列をフィルタする。
• 配列を並べ替える。

配列は、要素の順序付きリストであり、要素はすべて同じデータ型である点に注意してください。

8 人のランナーの名前で構成される文字列配列があります。

['Rudisha','Makhloufi','Murphy','Bosse','Rotich','Lewandowski','Kipketer','Berian']

BigQuery で配列を作成するには、カンマで値を区切り、角かっこ [ ] で囲みます。

1. 次のクエリを実行し、出力される行数を確認してください。8 行でしょうか。

#standardSQL SELECT ['Rudisha','Makhloufi','Murphy','Bosse','Rotich','Lewandowski','Kipketer','Berian'] AS normal_array

解答: 8 個の配列要素がある単一の行が出力されます。

行	normal_array
1	Rudisha
	Makhloufi
	Murphy
	Bosse
	Rotich
	Lewandowski
	Kipketer
	Berian

ヒント: 配列形式ではないフィールドがすでに存在する場合、ARRAY_AGG() を使用して、それらの値を配列にすることができます。

名前が「M」の文字で始まるランナーを探すには、上記の配列を個々の行に展開して WHERE 句を使用できるようにする必要があります。

以下に示すように、配列を展開するには、配列（または配列の名前）を UNNEST() で囲みます。

2. 次のクエリを実行し、返される行数を確認してください。

#standardSQL SELECT * FROM UNNEST(['Rudisha','Makhloufi','Murphy','Bosse','Rotich','Lewandowski','Kipketer','Berian']) AS unnested_array_of_names

次のように出力されます。

行	unnested_array_of_names
1	Rudisha
2	Makhloufi
3	Murphy
4	Bosse
5	Rotich
6	Lewandowski
7	Kipketer
8	Berian

これで配列のネストが解除されました。これは、配列のフラット化とも呼ばれます。

3. ここで、通常の WHERE 句を追加して行をフィルタし、クエリを実行します。

#standardSQL SELECT * FROM UNNEST(['Rudisha','Makhloufi','Murphy','Bosse','Rotich','Lewandowski','Kipketer','Berian']) AS unnested_array_of_names WHERE unnested_array_of_names LIKE 'M%'

行	unnested_array_of_names
1	Makhloufi
2	Murphy

ラボの質問: 配列を 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

タスク 5. 配列内の値でフィルタする

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 データセットを取り込み、配列と構造体を作成し、半構造化データをネスト解除してインサイトを得ることができました。

ラボを終了する

ラボが完了したら、[ラボを終了] をクリックします。ラボで使用したリソースが Google Cloud Skills Boost から削除され、アカウントの情報も消去されます。

ラボの評価を求めるダイアログが表示されたら、星の数を選択してコメントを入力し、[送信] をクリックします。

星の数は、それぞれ次の評価を表します。

• 星 1 つ = 非常に不満
• 星 2 つ = 不満
• 星 3 つ = どちらともいえない
• 星 4 つ = 満足
• 星 5 つ = 非常に満足

フィードバックを送信しない場合は、ダイアログ ボックスを閉じてください。

フィードバックやご提案の送信、修正が必要な箇所をご報告いただく際は、[サポート] タブをご利用ください。

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