重要:

このハンズオンラボは、デスクトップまたはノートパソコンでのみ完了するようにしてください。

ラボごとに 5 回までしか試行できません。

なお、最初の試行で全問正解できないことや、タスクをやり直す必要があることはよくあります。これは学習プロセスの一部です。

ラボを開始すると、タイマーを一時停止することはできません。1 時間 30 分後にラボは終了し、最初からやり直す必要があります。

詳しくは、ラボでの技術的なヒントの資料をご覧ください。

アクティビティの概要

データ パイプラインとは、保管や分析のために、さまざまなソースからデータを宛先に転送する一連のプロセスです。

データ パイプラインは、データの流れを自動化し、データの処理方法を効率化することで、データ変換のパフォーマンスと効率性を高めることができます。

SQL パイプラインを使用すると、不要な手順を排除してパフォーマンスを改善できます。これは、SQL パイプラインにより中間データセットを作成することなく宛先に対して直接データの読み書きができるようになるためです。これにより、時間とリソースを節約し、結果の精度を向上させることができます。

また、SQL パイプラインはデータ ストレージ容量の最適化にも役立ちます。パイプラインを使用して、不要になったデータを削除またはアーカイブできるためです。これにより、ストレージ システムの空き容量を増やし、パフォーマンスを高速化させることもできます。

効率的で柔軟な SQL パイプラインの構築は、特に組織のニーズが急速に変化する環境では困難な場合があります。しかし、パイプラインを慎重に計画して設計することで、最も複雑なデータニーズにも対応できるパイプラインを作成できます。

このラボでは、データを管理し、SQL を使用してビジネスニーズに対応する効率的で柔軟なパイプラインを構築します。

シナリオ

近年、TheLook eCommerce の収益はオンライン ショッピングのおかげで急増しています。しかし、配達時間に遅れが生じており、顧客満足度は低下しています。

あなたは TheLook eCommerce のクラウドデータ アナリストとして、ロジスティクス チームの責任者であるカイと協力して、配送センターから各顧客までの配送距離など、顧客への配達に関するデータの収集、クリーニング、変換、読み込みを行うためのデータ パイプラインを開発するよう求められています。

この情報は、新しい配送センターを開設するのか、既存の配送センターを移転するのか、新しい輸送方法に投資するのかといった、ロジスティクス チームが配送時間を短縮し、顧客満足度を高める方法を判断する材料となります。

そこであなたは、BigQuery と SQL のスキルを活かして、柔軟なパイプラインを設計します。このパイプラインは、ロジスティクス チームに、配送パフォーマンスをより適切にモニタリングするための信頼性の高いデータを提供し、かつチームのデータニーズの変化に応じて簡単に更新できるものである必要があります。

まず、データセットを作成し、取り込まれるデータのテーブル スキーマを定義します。次に、一連の変換を実行して確認します。その後、データに変換を適用し、変換後のデータを新しく定義したテーブルに読み込みます。最後に、これらのクエリをストアド プロシージャの形式にします。

設定

[ラボを開始] をクリックする前に

こちらの手順をお読みください。ラボには時間制限があり、一時停止することはできません。[ラボを開始] をクリックすることでスタートするタイマーは、Google Cloud のリソースを利用できる時間を示しています。

この実践ラボでは、シミュレーションやデモ環境ではなく、実際のクラウド環境を使ってご自身でアクティビティを行うことができます。そのため、ラボの受講中に Google Cloud にログインおよびアクセスするための、新しい一時的な認証情報が提供されます。

このラボを完了するためには、以下が必要です。

• 標準的なインターネット ブラウザ（Chrome を推奨）

注: このラボの実行には、シークレット モードまたはシークレット ブラウジング ウィンドウを使用してください。これにより、個人アカウントと受講者アカウントの競合を防ぎ、個人アカウントに追加料金が発生することを防ぎます。

• ラボを完了するための時間（開始後は一時停止できません）

注: すでに個人の Google Cloud アカウントやプロジェクトをお持ちの場合でも、このラボでは使用しないでください。アカウントへの追加料金が発生する可能性があります。

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

1. [ラボを開始] ボタンをクリックします。左側の [ラボの詳細] パネルには、以下が表示されます。

   • 残り時間
   • [Google Cloud コンソールを開く] ボタン
   • このラボで使用する必要がある一時的な認証情報
   • このラボを行うために必要なその他の情報（ある場合）
   注: ラボの料金をお支払いいただく必要がある場合は、表示されるポップアップでお支払い方法を選択してください。

2. [Google Cloud コンソールを開く] をクリックします（Chrome ブラウザを使用している場合は、右クリックして [シークレット ウィンドウで開く] を選択します）。新しいブラウザタブで [ログイン] ページが開きます。

   ヒント: タブをそれぞれ別のウィンドウで開き、並べて表示しておくと、簡単に切り替えられます。

   注: [アカウントの選択] ダイアログが表示されたら、[別のアカウントを使用] をクリックします。

3. 必要に応じて、下のGoogle Cloud ユーザー名をコピーして、[ログイン] ダイアログに貼り付けます。[Next] をクリックします。

{{{user_0.username | "Google Cloud username"}}}

[ラボの詳細] パネルでも Google Cloud ユーザー名を確認できます。

4. 以下の Google Cloud パスワードをコピーして、[ようこそ] ダイアログに貼り付けます。[Next] をクリックします。

{{{user_0.password | "Google Cloud password"}}}

[ラボの詳細] パネルでも Google Cloud のパスワードを確認できます。

重要: ラボで提供された認証情報を使用する必要があります。Google Cloud アカウントの認証情報は使用しないでください。 注: このラボでご自身の Google Cloud アカウントを使用すると、追加料金が発生する場合があります。

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

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

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

タスク 1. データセットを作成する

このタスクでは、既存のプロジェクトでデータセットを作成し、スキーマとデータ型を定義します。

1. Cloud コンソールのナビゲーション メニュー（）で、[BigQuery] を選択します。

注: [Cloud コンソールの BigQuery へようこそ] メッセージ ボックスが表示されることがあります。このメッセージ ボックスには、クイックスタート ガイドと UI の更新に関するリリースノートへのリンクが表示されます。[完了] をクリックして次に進みます。

ナビゲーション メニュー、エクスプローラ ペイン、クエリエディタが表示されます。

2. プロジェクト ID の横のアクションを表示アイコン（）をクリックし、[データセットを作成] をクリックします。

注: 最初にプロジェクトを選択する必要がある場合があります。これを行うには、Google Cloud コンソールのタイトルバーにある [プロジェクトの選択] をクリックし、[プロジェクトを選択] ダイアログでプロジェクト リンクを選択します。

3. [データセットの作成] ペインで、[データセット ID] を「thelook_ecommerce」に設定します。ロケーションが US マルチリージョンに設定されていることを確認し、他のフィールドはデフォルト値のままにします。

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

プロジェクト名の下に thelook_ecommerce データセットが表示されるようになります。

5. クエリエディタで、SQL クエリ（+）アイコンをクリックして、新しい [無題のクエリ] タブを開き、クエリを実行します。

注: クエリエディタで新しいクエリを実行する際は、同じ [無題のクエリ] タブで新しいクエリをコピーして貼り付けて以前のクエリを上書きすることで古いクエリを新しいクエリに置き換えるか、SQL クエリ（+）アイコンをクリックして新しい [無題のクエリ] タブを開き、そこでクエリを実行できます。

6. 次のクエリを [無題のクエリ] タブにコピーして貼り付け、product_order_fulfillment テーブルを作成します。

--Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64);

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

注: このクエリは、product_orders_fulfillment という名前の新しいテーブルが存在しない場合は作成し、既存のテーブルがある場合は新しいテーブル定義で置き換えます。

8. [テーブルに移動] をクリックして、作成されたテーブル スキーマとデータ型を確認します。

このデータセットは現在空ですが、データが読み込まれると、この場所にデータが入力されます。

[進行状況を確認] をクリックして、このタスクが正しく完了したことを確認します。

データセットを作成する

タスク 2. クエリ結果からテーブルを作成する

分析の範囲が拡大し、配送センターから注文した顧客までの距離を分析する必要が生じました。そのためには、地理的位置に基づいて値を算出する必要があります。

このタスクでは、original users テーブルと distribution_centers テーブルで提供されている緯度と経度の値から幾何学的ポイントを作成します。

1. 次のクエリをクエリエディタにコピーします。

--Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY);

このクエリは、customers テーブルと centers テーブルの両方のテーブル定義を作成します。これには、users テーブルと distribution_centers テーブルの元の列が含まれます。また、このクエリでは、両方のテーブルに point_location 列が追加されます。これらの point_location 列のデータ型は Geography に設定されます。

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

[進行状況を確認] をクリックして、このタスクが正しく完了したことを確認します。

関連データを保存するための新しいテーブルを作成する

タスク 3. BigQuery データで変換を実行する

このタスクでは、ST_GEOGPOINT(lon, lat) を呼び出す ST_GEOGPOINT 地理関数を使用してポイント ジオメトリを作成します。

まず、次の SQL クエリを実行して、centers テーブルと customers テーブルを作成し、データを入力します。これらのクエリは、データの元の場所（経度、緯度）が BigQuery の thelook_ecommerce 一般公開データセットにあることを前提として、SELECT ステートメントからデータを読み込みます。

Google 一般公開データセットは、BigQuery に保存され、Google Cloud 一般公開データセット プログラムを通じて一般提供されているデータセットです。一般公開データセットを使用すると、BigQuery 内で利用可能な一般公開データを簡単に使用できます。読み込みやメンテナンスは不要です。

1. 次のクエリをクエリエディタにコピーします。

--load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters;

このクエリは、地理変換を含む centers テーブルを読み込みます。

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

注: [テーブルに移動] をクリックすると、作成されたテーブル スキーマとデータ型を確認できます。

3. 次のクエリをクエリエディタにコピーします。

--load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users;

このクエリは、地理変換を含む customers テーブルを読み込みます。

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

次に、分析用のデータ値を計算する必要があります。

顧客と配送センターの関わりは重要であるため、これらの地点位置を使用して、各顧客に最も近い配送センターと、顧客の所在地との距離を計算します。

これを行うには、地理関数 ST_DISTANCE を使用して、顧客の所在地とセンター間の最短距離を計算します。

5. 以下のスカラー サブクエリをクエリエディタにコピーします。

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

このクエリは距離をキロメートル単位で計算します。

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

スクリーンショットに示すように、このクエリは各顧客と最寄りの配送センター間の距離を返します。SELECT ステートメントを確認して、距離の計算方法を理解してください。

7. [保存] プルダウンを開き、[クエリを保存] を選択します。

[クエリを保存] ダイアログが開きます。

8. [名前] フィールドに「Calculate Customer Distance to Closest Center」と入力します。
9. [リージョン] で [] を選択します。
10. [保存] をクリックします。

クエリが [クエリ] リストに保存されます。

[進行状況を確認] をクリックして、このタスクが正しく完了したことを確認します。

BQ データに対して変換を実行する

タスク 4. 更新を容易にするストアド プロシージャを作成する

ストアド プロシージャは、データベースに保存され、単一のユニットとして実行できる SQL ステートメントのセットです。

このタスクでは、タスク 1～3 において手動で順次生成してきたすべてのテーブル定義、変換、取り込みステートメントを含むストアド プロシージャを作成します。

ストアド プロシージャ（この場合は sp_create_load_tables）を作成するには、スクリプトの先頭に CREATE OR REPLACE PROCEDURE ステートメントを追加し、その後にストアド プロシージャの名前と BEGIN ステートメントを追加します。プロシージャのすべてのコンポーネントを追加したら、END ステートメントを追加します。

プロシージャに追加するには、テーブル定義とデータ取り込み部分を配置します。好みに応じて、すべてのテーブル定義を最初に配置することも、テーブルごとにクラスタリングすることもできます。ただし、このラボでは、各テーブル定義の後に、データ取り込み部分と、前の手順で行われた他の変換が配置されます。

顧客テーブルに、計算された distance_to_closest_center と closest_centervalues を保存する列を作成することもできます。この手順の優れた点は、スキーマが変更された場合や、追加の列を含める場合、計算列を生成する場合に、これらの列を簡単にストアド プロシージャに追加したり、ストアド プロシージャを更新したりできることです。更新が完了したら、ストアド プロシージャを定期的に実行するように設定することが可能です。

次に、以前にタスク 1～3 で実行したすべての手順を 1 つの単位として実行するストアド プロシージャを作成します。次のコードを実行すると、テーブルが更新され、適切にデータが入力されます。

1. 次のクエリを新しい [無題] クエリタブにコピーします。

CREATE OR REPLACE PROCEDURE `thelook_ecommerce.sp_create_load_tables`() BEGIN --Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64) ; --load the product_order_fulfillment table from public dataset CREATE OR REPLACE TABLE thelook_ecommerce.product_orders_fulfillment AS SELECT items.*, products.id AS product_id_products, products.name AS product_name, products.category AS product_category, FROM bigquery-public-data.thelook_ecommerce.order_items AS items JOIN bigquery-public-data.thelook_ecommerce.products AS products ON (items.product_id = products.id); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters ; --Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users ; END ;

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

注: このストアド プロシージャは、必要なテーブルをすべて作成し、変換されたデータを取り込みます。ただし、顧客と最寄りの配送拠点との距離を示すクエリ結果は返しません。その操作は、以下のステップ 7 に進んで行います。

3. [保存] プルダウンを開き、[クエリを保存] を選択します。[クエリを保存] ダイアログが開きます。
4. [名前] フィールドに「sp_create_load_tables」と入力します。
5. [リージョン] で [] を選択します。
6. [保存] をクリックします。このストアド プロシージャは、データセットの [ルーティン] セクションに表示されます。
7. [実行] をクリックして、このストアド プロシージャを実行します。

上記のタスク 3 のステップ 5 に示すように、SELECT SQL ステートメントを実行して、各顧客と最寄りの配送センター間の距離を取得します。

8. 以下のスカラー サブクエリをクエリエディタにコピーします。

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

このクエリは距離をキロメートル単位で計算します。

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

[進行状況を確認] をクリックして、このタスクが正しく完了したことを確認します。

更新を容易にするストアド プロシージャを作成する 注: このラボではスケジュールされたクエリを実行しませんが、データを更新して SQL パイプラインを維持するさまざまな方法を理解することが重要です。

スケジュールされたクエリを設定するには、クエリエディタで [スケジュール] をクリックします。[新しいスケジュール クエリ] ウィンドウが開き、指定するクエリの詳細が表示されます。これは、定期的に更新する必要があるクエリに対して行うことができます。

まとめ

これで完了です。

SQL と BigQuery を使用して、ビジネスニーズを満たすシンプルで柔軟なデータ パイプラインを作成できました。

まず、データセットを作成し、取り込むデータのテーブル スキーマを定義しました。次に、一連の変換を実行して調べました。

さらに、データに変換を適用してから、変換されたデータを新しく定義されたテーブルに読み込みました。

最後に、これらのクエリをストアド プロシージャとして規定して、目的の計算値、および定期的な更新のためにスケジュールされたクエリを設定する方法を含めました。

このラボでは、データセットの作成と、データセットに含まれるデータのテーブル スキーマの定義の重要性について学習しました。一連の変換を実行して調べることで、データに変換を適用してから、変換後のデータを新しく定義したテーブルに読み込むスキルを習得しました。また、これらのクエリをストアド プロシージャとして規定して、目的の計算値を含める方法、および定期的な更新のためにスケジュールされたクエリを設定する方法も学びました。

これで、BigQuery を使用して SQL パイプラインを作成、管理する方法を理解する準備が整いました。

ラボを終了する

すべてのタスクが問題なく完了したことを確認してから、ラボを終了してください。準備ができたら、[ラボを終了] をクリックし、[送信] をクリックします。

ラボを終了すると、ラボ環境へのアクセス権が削除され、完了した作業にもう一度アクセスすることはできなくなります。

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