概要

このラボでは、データを CSV テキスト ファイルから Cloud SQL にインポートし、シンプルなクエリを使用していくつかの基本的なデータ分析を行う方法を学習します。

このラボでは、NYC Taxi and Limousine Commission によって収集されたデータセットを使用します。このデータセットには、2009 年から現在までのニューヨーク市内のイエロー タクシーとグリーン タクシーに関するすべての賃送記録と、2015 年から現在までのハイヤー（FHV）に関するすべての賃送記録が含まれます。記録には、乗車 / 降車の日時と場所、走行距離、料金明細、レートタイプ、支払いタイプ、運転手が記録した乗客数などの項目があります。

このデータセットは、データ サイエンスの多岐にわたるコンセプトと技術の実証に使用できるもので、データ エンジニアリングのカリキュラムに含まれる複数のラボで使用します。

目標

• Cloud SQL インスタンスを作成する
• Cloud SQL データベースを作成する
• Cloud SQL にテキストデータをインポートする
• データの整合性を確認する

設定と要件

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

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

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

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

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

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

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

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

Google Cloud Shell の有効化

Google Cloud Shell は、開発ツールと一緒に読み込まれる仮想マシンです。5 GB の永続ホーム ディレクトリが用意されており、Google Cloud で稼働します。

Google Cloud Shell を使用すると、コマンドラインで Google Cloud リソースにアクセスできます。

1. Google Cloud コンソールで、右上のツールバーにある [Cloud Shell をアクティブにする] ボタンをクリックします。

   

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

環境がプロビジョニングされ、接続されるまでしばらく待ちます。接続した時点で認証が完了しており、プロジェクトに各自のプロジェクト ID が設定されます。次に例を示します。

gcloud は Google Cloud のコマンドライン ツールです。このツールは、Cloud Shell にプリインストールされており、タブ補完がサポートされています。

• 次のコマンドを使用すると、有効なアカウント名を一覧表示できます。

gcloud auth list

出力:

Credentialed accounts: - @.com (active)

出力例:

Credentialed accounts: - google1623327_student@qwiklabs.net

• 次のコマンドを使用すると、プロジェクト ID を一覧表示できます。

gcloud config list project

出力:

[core] project =

出力例:

[core] project = qwiklabs-gcp-44776a13dea667a6 注: gcloud ドキュメントの全文については、 gcloud CLI の概要ガイド をご覧ください。

タスク 1. 環境を準備する

• プロジェクト ID の環境変数と、データを格納する Storage バケットの環境変数を作成します。これらの変数はラボの後半で使用します。

export PROJECT_ID=$(gcloud info --format='value(config.project)') export BUCKET=${PROJECT_ID}-ml

タスク 2. Cloud SQL インスタンスを作成する

1. 次のコマンドを実行して Cloud SQL インスタンスを作成します。

gcloud sql instances create taxi \ --tier=db-n1-standard-1 --activation-policy=ALWAYS

完了するまでに数分かかります。

完了したタスクをテストする

[進行状況を確認] をクリックして、実行したタスクを確認します。タスクが正常に完了すると、評価スコアが付与されます。

Cloud SQL インスタンスを作成する。

2. Cloud SQL インスタンスの root パスワードを設定します。

gcloud sql users set-password root --host % --instance taxi \ --password Passw0rd

3. パスワードの入力を求められたら、「Passw0rd」と入力して Enter キーを押します。これで、root パスワードが更新されます。

4. Cloud Shell の IP アドレスを格納する環境変数を作成します。

export ADDRESS=$(wget -qO - http://ipecho.net/plain)/32

5. SQL インスタンスへの管理アクセスを有効にするために、Cloud Shell インスタンスを許可リストに登録します。

gcloud sql instances patch taxi --authorized-networks $ADDRESS

6. プロンプトが表示されたら、Y キーを押して変更を確定します。

完了したタスクをテストする

[進行状況を確認] をクリックして、実行したタスクを確認します。タスクが正常に完了すると、評価スコアが付与されます。

SQL インスタンスにアクセスするために Cloud Shell インスタンスを許可リストに登録する。

7. 次のコマンドを実行して Cloud SQL インスタンスの IP アドレスを取得します。

MYSQLIP=$(gcloud sql instances describe \ taxi --format="value(ipAddresses.ipAddress)")

8. MYSQLIP 変数を確認します。

echo $MYSQLIP

出力として IP アドレスが表示されます。

9. mysql コマンドライン インターフェースにログインして、タクシー賃走（trips）テーブルを作成します。

mysql --host=$MYSQLIP --user=root \ --password --verbose

10. パスワードの入力を求められたら、「Passw0rd」と入力します。

11. 次の内容をコマンドラインに貼り付け、trips テーブルのスキーマを作成します。

create database if not exists bts; use bts; drop table if exists trips; create table trips ( vendor_id VARCHAR(16), pickup_datetime DATETIME, dropoff_datetime DATETIME, passenger_count INT, trip_distance FLOAT, rate_code VARCHAR(16), store_and_fwd_flag VARCHAR(16), payment_type VARCHAR(16), fare_amount FLOAT, extra FLOAT, mta_tax FLOAT, tip_amount FLOAT, tolls_amount FLOAT, imp_surcharge FLOAT, total_amount FLOAT, pickup_location_id VARCHAR(16), dropoff_location_id VARCHAR(16) );

完了したタスクをテストする

[進行状況を確認] をクリックして、実行したタスクを確認します。タスクが正常に完了すると、評価スコアが付与されます。

bts データベースと trips テーブルを作成する。

12. mysql コマンドライン インターフェースで、次のコマンドを入力してインポート内容を確認します。

describe trips;

13. trips テーブルに対するクエリを実行します。

select distinct(pickup_location_id) from trips;

データべースにはまだデータがないため、空のセットが返されます。

14. mysql インタラクティブ コンソールを終了します。

exit

タスク 3. Cloud SQL インスタンスにデータを追加する

次に、Cloud Storage に保存されているニューヨーク市のタクシー賃走の CSV ファイルをローカルにコピーします。リソース使用量を抑えるために、データのサブセットのみ（約 20,000 行）を使用します。

1. コマンドラインで次のコマンドを実行します。

gcloud storage cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_1.csv trips.csv-1 gcloud storage cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_2.csv trips.csv-2

2. mysql インタラクティブ コンソールに接続して、ローカルの infile データを読み込みます。

mysql --host=$MYSQLIP --user=root --password --local-infile

3. パスワードの入力を求められたら、「Passw0rd」と入力します。

4. mysql インタラクティブ コンソールで、データベースを選択します。

use bts;

5. local-infile を使用してローカル CSV ファイルのデータを読み込みます。

LOAD DATA LOCAL INFILE 'trips.csv-1' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id); LOAD DATA LOCAL INFILE 'trips.csv-2' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id);

タスク 4. データの整合性を確認する

ソースからデータをインポートするたびに、データの整合性を確認して、データが想定どおりであることを確認する必要があります。

1. trips テーブルに対してクエリを実行し、一意の乗車場所の地域を取得します。

select distinct(pickup_location_id) from trips;

すると、159 個の一意の ID が返されます。

2. 最初に、trip_distance 列について詳しく調べてみましょう。コンソールで次のクエリを入力します。

select max(trip_distance), min(trip_distance) from trips;

走行距離は 0 より大きく、1,000 マイル未満になると予想されます。最長走行距離として 85 マイルが返されることは妥当ですが、最短走行距離として 0 マイルが返されるのはバグのように思えます。

3. データセットには走行距離が 0 の賃走データがいくつあるでしょうか。

select count(*) from trips where trip_distance = 0;

データベース内には、走行距離が 0 の賃走データが 155 個存在します。このような賃走データは、さらに詳しく調べる必要があります。調べていくと、これらの賃走データに関連付けられている支払い金額が 0 ではないことがわかります。不正な処理が行われたのでしょうか。

4. 想定外のデータが他にあるか確認してみましょう。fare_amount 列は、プラスの金額であることが想定されます。次のクエリを入力して、データベース内のデータにマイナスの金額が存在しないか確認します。

select count(*) from trips where fare_amount < 0;

該当するデータが 14 個返されます。これらの賃走データについても、さらに詳しく調べる必要があります。運賃がマイナスの金額であることについて、妥当な説明があるかもしれませんが、このような結果をもたらすバグがデータ パイプラインから確実に排除されるようにするのはデータ エンジニアの責任です。

5. 最後に、payment_type 列を詳しく調べてみましょう。

select payment_type, count(*) from trips group by payment_type;

このクエリの結果は、4 つの異なる支払いタイプがあることを示しています。

• 支払いタイプ 1 が 13,863 行
• 支払いタイプ 2 が 6,016 行
• 支払いタイプ 3 が 113 行
• 支払いタイプ 4 が 32 行

こちらのドキュメントを詳しく見ていくと、支払いタイプ 1 はクレジット カードの使用、支払いタイプ 2 は現金、支払いタイプ 4 は支払いの異議申し立てを示しています。数値は妥当であったことがわかりました。

6. mysql インタラクティブ コンソールを終了します。

exit

ラボを終了する

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

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

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

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

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

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

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