概要

BigQuery は、データ定義言語（DDL）、データ操作言語（DML）、ユーザー定義関数（UDF）、ストアド プロシージャなど、さまざまな種類の SQL ステートメントをサポートしています。

このラボの目的は、Redshift のプロフェッショナルが BigQuery で SQL の操作を開始するために必要な知識とスキルを習得することです。このラボを完了すると、Redshift のプロフェッショナルは、BigQuery で SQL を使用して BigQuery のデータ構造を作成、更新、操作する方法について、より深く理解できるようになります。

このラボでは、DDL ステートメントを使用してテーブルとビューを作成し、DML ステートメントを使用してテーブルを更新し、SQL を使用してデータを結合し、カスタムのユーザー定義関数（UDF）とストアド プロシージャを定義します。

Redshift から BigQuery への SQL の変換について詳しくは、Amazon Redshift SQL 変換ガイドをご覧ください。

目標

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

• DDL ステートメントを使用して新しいテーブルとビューを作成する。
• DML ステートメントを使用して既存のテーブルデータを更新する。
• SQL SELECT ステートメントを使用して、データを結合し、共通テーブル式（CTE）を定義する。
• カスタム UDF とストアド プロシージャを定義する。

設定と要件

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

1. シークレット ウィンドウを使用して Google Skills にログインします。

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

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

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

5. [Google コンソールを開く] をクリックします。

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

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

ラボを開始してコンソールにログインする方法

1. [ラボを開始] ボタンをクリックします。ラボの料金をお支払いいただく必要がある場合は、表示されるポップアップでお支払い方法を選択してください。 左側のパネルには、このラボで使用する必要がある一時的な認証情報が表示されます。

   

2. ユーザー名をコピーし、[Google Console を開く] をクリックします。 ラボでリソースが起動し、別のタブで [アカウントの選択] ページが表示されます。

   注: タブをそれぞれ別のウィンドウで開き、並べて表示しておきましょう。

3. [アカウントの選択] ページで [別のアカウントを使用] をクリックします。[ログイン] ページが開きます。

   

4. [接続の詳細] パネルでコピーしたユーザー名を貼り付けます。パスワードもコピーして貼り付けます。

注: 認証情報は [接続の詳細] パネルに表示されたものを使用してください。Google Skills の認証情報は使用しないでください。請求が発生する事態を避けるため、Google Cloud アカウントをお持ちの場合でも、このラボでは使用しないでください。

5. その後次のように進みます。

• 利用規約に同意します。
• 一時的なアカウントなので、復元オプションや 2 要素認証プロセスは設定しないでください。
• 無料トライアルには登録しないでください。

しばらくすると、このタブで Cloud コンソールが開きます。

注: 左上にある [ナビゲーション メニュー] をクリックすると、Google Cloud のプロダクトやサービスのリストが含まれるメニューが表示されます。

タスク 1. DDL ステートメントを使用して BigQuery データセットとテーブルを作成する

BigQuery では、データ定義言語（DDL）を使用してデータセットとテーブルを作成できます。また、SQL ステートメント LOAD DATA を使用して、1 つ以上のファイルからデータを新規または既存のテーブルに読み込むこともできます。

DDL ステートメントを使用して BigQuery データセットとテーブルを作成する方法と、LOAD DATA SQL ステートメントを使用してデータを読み込む方法について詳しくは、CREATE SCHEMA ステートメント、CREATE TABLE ステートメント、LOAD DATA ステートメントのドキュメントをご覧ください。

このタスクでは、DDL を使用して BigQuery にデータセットとテーブルを作成し、LOAD DATA ステートメントを使用して新しいテーブルにデータを読み込みます。

1. Google Cloud コンソールのナビゲーション メニュー（）で、[プロダクト] の [BigQuery] をクリックします。

[Cloud コンソールの BigQuery へようこそ] メッセージ ボックスが開きます。このメッセージ ボックスには、クイックスタート ガイドとリリースノートへのリンクが表示されます。

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

2. [従来のエクスプローラ] パネルで、[無題のクエリ] アイコンをクリックして SQL コードエディタを開きます。

3. クエリエディタに以下のクエリをコピーして貼り付け、[実行] をクリックします。

CREATE SCHEMA IF NOT EXISTS animals_dataset OPTIONS( location="us");

このクエリは、animals_dataset という名前の新しい BigQuery データセットを作成します。DDL ステートメントでは、SCHEMA という用語はテーブル、ビュー、その他のリソースの論理的なコレクションを指します。これは BigQuery ではデータセットと呼ばれます。

4. クエリエディタで、次のクエリを実行して 2 つの新しいテーブルを作成します。

CREATE TABLE animals_dataset.owners( OwnerID INT64 NOT NULL, Name STRING); CREATE TABLE animals_dataset.pets( PetID INT64 NOT NULL, OwnerID INT64 NOT NULL, Type STRING, Name STRING, Weight FLOAT64);

これらのクエリは、先ほど作成した animals_dataset という BigQuery データセットの中に、owners と pets という 2 つのテーブルを作成します。

次のステップに進む前に、ステップ 5 でエラーが発生しないように、テーブル スキーマのフィールド モードを REQUIRED から NULLABLE に更新します。

[従来のエクスプローラ] パネルでテーブル名を選択し、[スキーマ] タブを選択して [スキーマの編集] をクリックし、フィールドモードを REQUIRED から NULLABLE に更新します。両方のテーブルでモードが「REQUIRED」となっているすべてのフィールドに対して、この手順を繰り返してください。

5. クエリエディタで、次のクエリを実行します。

LOAD DATA INTO animals_dataset.owners FROM FILES ( skip_leading_rows=1, format = 'CSV', field_delimiter = ',', uris = ['gs://tcd_repo/data/environmental/animals/owners.csv']); LOAD DATA INTO animals_dataset.pets FROM FILES ( skip_leading_rows=1, format = 'CSV', field_delimiter = ',', uris = ['gs://tcd_repo/data/environmental/animals/pets.csv']);

これらのクエリは、Cloud Storage の CSV ファイルから owners テーブルと pets テーブルにデータを読み込みます。

[結果] ペインに、LOAD ステートメントが正常に実行されたことを示すメッセージが表示されます。

6. [従来のエクスプローラ] ペインで、データセットを開いてテーブルを表示し、owners テーブルを選択します。

7. [詳細] タブと [プレビュー] タブをクリックして、テーブルの詳細情報とデータのプレビューを確認します。

[プレビュー] タブのデータを更新するには、[更新]（右上）をクリックします。

8. 手順 6 ～ 7 を繰り返して、pets という名前のテーブルの詳細情報とデータのプレビューを表示します。

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

タスク 2. DML ステートメントを使用して BigQuery テーブルのデータを更新する

BigQuery では、DML を使用して既存のテーブルのデータを更新できます。これには、BigQuery テーブルのデータの追加、変更、削除が含まれます。

DML ステートメントを使用して BigQuery テーブルのデータを操作する方法について詳しくは、GoogleSQL のデータ操作言語（DML）ステートメントのドキュメントをご覧ください。

このタスクでは、DML を使用して既存の BigQuery テーブルに対してデータの挿入、更新、削除を行います。

1. クエリエディタで、次のクエリを実行して Mary という名前の新しい飼い主を追加します。

INSERT INTO animals_dataset.owners (OwnerID, Name) VALUES (9, 'Mary');

2. クエリエディタで、次のクエリを実行して Mary の 2 匹の犬を追加します。

INSERT INTO animals_dataset.pets (PetID, OwnerID, Type, Name, Weight) VALUES (28, 9, 'Dog', "George", 50); INSERT INTO animals_dataset.pets (PetID, OwnerID, Type, Name, Weight) VALUES (29, 9, 'Dog', "Washington", 60);

各挿入クエリの実行後、[結果] ペインには、pets テーブルに 1 件のレコードが追加されたことが表示されます。

3. クエリエディタで、次のクエリを実行して、Mary と 2 匹の犬が適切なテーブルに追加されたことを確認します。

SELECT o.Name, p.Type, p.Name FROM animals_dataset.owners o JOIN animals_dataset.pets p ON o.OwnerID = p.OwnerID WHERE o.Name = 'Mary';

owners テーブルの Mary のレコードが、pets テーブルの 2 匹の飼い犬（George と Washington）のレコードに結合されています。

4. クエリエディタで、次のクエリを実行して、動物の種類が「Dog」になっているすべての値を「Canine」に更新します。

UPDATE animals_dataset.pets SET Type = 'Canine' WHERE Type = 'Dog';

このステートメントにより、pets テーブル内の 10 行が変更されます。

5. [テーブルに移動] をクリックし、[プレビュー] タブをクリックします。

pets テーブルで、すべての犬が「Canine」と表示されていることを確認します。

[プレビュー] タブのデータを更新するには、[更新]（右上）をクリックします。

6. クエリエディタで次のクエリを実行して、動物の種類が「Frog」であるすべての値を削除します。

DELETE FROM animals_dataset.pets WHERE Type = 'Frog';

このステートメントにより、pets テーブルから 1 行が削除されます。

7. [テーブルに移動] をクリックし、[プレビュー] タブをクリックします。

pets テーブルからすべての「Frog」が削除されていることを確認します。

[プレビュー] タブのデータを更新するには、[更新]（右上）をクリックします。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 DML ステートメントを使用して BigQuery テーブルのデータを更新する

タスク 3. SQL SELECT ステートメントを使用してデータを結合し、CTE を記述する

BigQuery では、SQL SELECT ステートメントで結合、CTE、並べ替え、グループ化、フィルタリング、ピボット、ウィンドウ処理などの構文を使用して、必要なデータを取得できます。

BigQuery テーブルのデータを操作するための SQL SELECT 構文の詳細については、クエリ構文のドキュメントをご覧ください。

このタスクでは、複数のテーブルを結合する JOIN オペレーションと、CTE を定義する WITH 句を含む SQL SELECT ステートメントを記述します。

1. クエリエディタで、JOIN を使用した次のクエリを実行し、すべての飼い主とそのペットを抽出します。

SELECT o.Name, p.Type, p.Name, p.Weight FROM animals_dataset.owners o JOIN animals_dataset.pets p ON o.OwnerID = p.OwnerID;

2. クエリエディタで、同じクエリに WHERE 句を加えて実行し、「Canine」のみを抽出します。

SELECT o.Name, p.Type, p.Name, p.Weight FROM animals_dataset.owners o JOIN animals_dataset.pets p ON o.OwnerID = p.OwnerID WHERE p.Type = "Canine";

3. クエリエディタで、同じクエリに ORDER BY を加えて実行し、結果を飼い主の名前で並べ替えます。

SELECT o.Name, p.Type, p.Name, p.Weight FROM animals_dataset.owners o JOIN animals_dataset.pets p ON o.OwnerID = p.OwnerID WHERE p.Type = "Canine" ORDER BY o.Name ASC;

4. クエリエディタで次のクエリを実行して、ペットの種類ごとに数をカウントします。

SELECT type, COUNT(*) AS count FROM animals_dataset.pets GROUP BY type ORDER BY count DESC;

pets テーブルには、犬 10 匹、猫 5 匹、豚 2 匹、カメ 1 匹が含まれています。

5. クエリエディタで次のクエリを実行して、飼い主ごとのペットの数を取得します。

SELECT o.Name, COUNT(p.Name) AS count FROM animals_dataset.owners o JOIN animals_dataset.pets p ON o.OwnerID = p.OwnerID GROUP BY o.Name ORDER BY count DESC;

Doug という名前の飼い主が最も多くのペットを飼っており、その数は合計 4 匹です。

6. クエリエディタで次のクエリを実行して、ペットの情報をネストした繰り返しフィールドとして返します。

SELECT o.OwnerID, o.Name AS OwnerName, ARRAY_AGG(STRUCT( p.Name AS PetName, p.Type, p.Weight)) AS Pets FROM animals_dataset.owners AS o JOIN animals_dataset.pets AS p ON o.OwnerID = p.OwnerID GROUP BY o.OwnerID, o.Name; 注: BigQuery では、ネストした繰り返しフィールドは STRUCT の ARRAY として保存されます。ARRAY_AGG(STRUCT…) 構文は、ネストした繰り返し値として結果を返し、結合された値が明確に整理されるため、データ間の関係を把握しやすくなります。

BigQuery で役立つ SQL のオプションとして、WITH 句を使用して CTE を定義し、別のクエリの結果に対してクエリを行うという方法があります。この構文を使用すると、ネストした SQL ステートメントの使用を避け、コードを読みやすくすることができます。

7. クエリエディタで次のクエリを実行します。このクエリでは、前のクエリに基づく CTE を定義しています。

WITH owners_pets AS (SELECT o.OwnerID, o.Name AS OwnerName, ARRAY_AGG(STRUCT( p.Name AS PetName, p.Type, p.Weight)) AS Pets FROM animals_dataset.owners AS o JOIN animals_dataset.pets AS p ON o.OwnerID = p.OwnerID GROUP BY o.OwnerID, o.Name) SELECT op.OwnerName, op.Pets FROM owners_pets AS op;

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 SQL SELECT ステートメントを使用してデータを結合し、CTE を記述する

タスク 4. DDL ステートメントを使用して新しいテーブルとビューを作成する

前のタスクでは、DDL を使用して新しい BigQuery データセットとテーブルを作成しました。BigQuery では、DDL を使用して論理ビューとマテリアライズド ビューを作成することもできます。

DDL ステートメントを使用して BigQuery でビューを作成する方法について詳しくは、論理ビューの概要をご覧ください。

このタスクでは、DDL を使用して新しいテーブル、論理ビュー、マテリアライズド ビューを作成します。

1. クエリエディタで次のコードを実行して、クエリの結果を新しいテーブルに書き込みます。

CREATE OR REPLACE TABLE animals_dataset.owners_pets AS ( SELECT o.OwnerID, o.Name AS OwnerName, ARRAY_AGG(STRUCT( p.PetID, p.Name AS PetName, p.Type, p.Weight)) AS Pets FROM animals_dataset.owners AS o JOIN animals_dataset.pets AS p ON o.OwnerID = p.OwnerID GROUP BY o.OwnerID, o.Name );

2. [従来のエクスプローラ] ペインで、データセットを開いてテーブルを表示し、owner_pets テーブルを選択します。

3. [スキーマ] タブをクリックして、新しく作成された owners_pets という名前のテーブルのスキーマを確認します。

スキーマには、Pets という名前のネストした繰り返しフィールドが含まれています。このフィールドには、それぞれの飼い主が飼っているそれぞれのペットのペット ID、名前、種類、体重が含まれています。

前のタスクでは、owners テーブルと pets テーブルを結合して、飼い主ごとのペット数をカウントするクエリを実行しました。今回は、ネストした繰り返しフィールドにデータが含まれているため、ARRAY_LENGTH 関数を使用して、それぞれの飼い主のペットの数を返すことができます。

4. クエリエディタで次のクエリを実行して、それぞれの飼い主のペットの数を返します。

SELECT OwnerName, ARRAY_LENGTH(Pets) AS count FROM animals_dataset.owners_pets ORDER BY count DESC;

5. クエリエディタで次のクエリを実行して、小型のペット（20 ポンド以下）のみを返す論理ビューを作成します。

CREATE OR REPLACE VIEW animals_dataset.small_pets AS ( SELECT * FROM animals_dataset.pets WHERE weight <= 20 );

6. [ビューに移動] をクリックします。

7. [従来のエクスプローラ] ペインで、small_pets ビューの [アクションを表示]（縦に並んだ 3 つの点のアイコン）をクリックし、[クエリ] を選択します。

8. クエリエディタで次のクエリを実行して、ビューのデータを調べます。

SELECT PetID, Weight FROM animals_dataset.small_pets;

BigQuery のマテリアライズド ビューは事前に計算されたビューで、パフォーマンスと効率を向上させるためにクエリの結果を定期的にキャッシュに保存します。マテリアライズド ビューは、集計などの複雑な処理を必要とするクエリで特に役立ちます。

9. クエリエディタで次のクエリを実行して、ペットの種類ごとの合計体重を返すマテリアライズド ビューを作成します。

CREATE OR REPLACE MATERIALIZED VIEW animals_dataset.pet_weight_by_type AS ( SELECT type, SUM(Weight) AS total_weight FROM animals_dataset.pets GROUP BY type );

10. [マテリアライズド ビューに移動] をクリックします。

11. [従来のエクスプローラ] ペインで、pet_weight_by_type マテリアライズド ビューの [アクションを表示]（縦に並んだ 3 つの点のアイコン）をクリックし、[クエリ] を選択します。

12. クエリエディタで次のクエリを実行して、マテリアライズド ビューのデータを調べます。

SELECT type, total_weight FROM animals_dataset.pet_weight_by_type;

犬の合計重量は最も重く、314 ポンドです。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 DDL ステートメントを使用して新しいテーブルとビューを作成する

タスク 5. カスタム UDF とストアド プロシージャを定義する

BigQuery では、目的の処理を行う組み込み関数が存在しない場合に、カスタム UDF を定義できます。UDF は 1 つ以上の入力列を受け取り、入力に対してアクションを実行し、それらのアクションの結果を出力として返します。また、SELECT や INSERT などの SQL ステートメントのコレクションを任意の順序で実行する関数として、ストアド プロシージャを定義することもできます。

BigQuery での UDF とストアド プロシージャの定義について詳しくは、ユーザー定義関数と SQL ストアド プロシージャを操作するをご覧ください。

このタスクでは、テーブル内の既存の値を再計算し、テーブルに新しいデータレコードを簡単に追加できるようにする UDF とストアド プロシージャを定義します。

1. クエリエディタで次のコードを実行して、ポンドをキログラムに変換する UDF を作成します。

CREATE OR REPLACE FUNCTION animals_dataset.PoundsToKilos(pounds FLOAT64) AS ( round(pounds / 2.2, 1) );

2. クエリエディタで次のクエリを実行して UDF をテストします。

SELECT name, weight AS pounds, animals_dataset.PoundsToKilos(Weight) AS Kilos FROM animals_dataset.pets;

新しいペットを簡単に追加できるようにするストアド プロシージャを作成することもできます。次の手順では、pets テーブルで最大のペット ID を検索し、その ID に 1 の値を加算します。次に、その新しい値を新しいペットの ID として割り当てます。新しいペットが追加されると、新しいペット ID の値が返されます。

3. クエリエディタで次のクエリを実行して、新しいペットを追加するストアド プロシージャを作成します。

CREATE OR REPLACE PROCEDURE animals_dataset.create_pet( customerID INT64, type STRING, name STRING, weight FLOAT64, out newPetID INT64) BEGIN SET newPetID = (SELECT MAX(PetID) + 1 FROM animals_dataset.pets); INSERT INTO animals_dataset.pets (PetID, OwnerID, Type, Name, Weight) VALUES(newPetID, customerID, type, name, weight); END

4. クエリエディタで次のクエリを実行して、ストアド プロシージャをテストします。

DECLARE newPetID INT64; CALL animals_dataset.create_pet(1, 'Dog', 'Duke', 15.0, newPetID); SELECT * FROM animals_dataset.pets WHERE PetID = newPetID;

5. 最後のステートメント SELECT * の [結果を表示] をクリックします。

関数の出力変数は、Duke という名前の犬に新しく作成されたレコードで、ペット ID が 30 であることに注目してください。

6. クエリエディタで次のクエリを実行して、別の新しいペットを追加します。

DECLARE newPetID INT64; CALL animals_dataset.create_pet(4, 'Cat', 'Fluffy', 6.0, newPetID); SELECT * FROM animals_dataset.pets WHERE PetID = newPetID;

7. 最後のステートメント SELECT * の [結果を表示] をクリックします。

ID フィールドの値がさらに増えていることに注目してください。Fluffy という名前の猫の新しいペット ID は 31 です。

8. クエリエディタで次のクエリを実行して、2 匹の新しいペットが pets テーブルに追加されたことを確認します。

SELECT * FROM animals_dataset.pets WHERE Name in ('Duke', 'Fluffy');

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 カスタム UDF とストアド プロシージャを定義する

ラボを終了する

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

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

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

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

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

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

Copyright 2026 Google LLC All rights reserved. Google および Google のロゴは、Google LLC の商標です。その他すべての社名および製品名は、それぞれ該当する企業の商標である可能性があります。