GSP281

概要

SQL（構造化クエリ言語）とはデータ操作のための標準言語であり、構造化データセットを照会して分析情報を得られるようにするものです。一般にデータベースの管理に使用され、リレーショナル データベースへのトランザクション レコードの入力やペタバイト規模のデータ分析といった作業を行うことができます。

二部構成の前半では SQL クエリの基本的なキーワードについて学び、ロンドン市内のシェア自転車に関する一般公開データセットに対して BigQuery でクエリを実行します。

後半では、ロンドン市内のシェア自転車に関するデータセットの一部を CSV ファイルにエクスポートしてから、Cloud SQL にアップロードします。その後、Cloud SQL を使用してデータセットとテーブルを作成および管理する方法を学びます。最後に、データの操作および編集に使用する他の SQL キーワードを実際に試してみます。

学習内容

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

• データベースとテーブルを BigQuery に読み込む。
• テーブルに対して簡単なクエリを実行して、データセットから有意のデータを pull する。
• データのサブセットを CSV ファイルにエクスポートし、そのファイルを Cloud Storage の新しいバケットに格納する。
• 新しい Cloud SQL インスタンスを作成し、エクスポートした CSV ファイルを新しいテーブルとして読み込む。

前提条件

最重要: ラボを開始する前に、個人または会社の Gmail アカウントからログアウトしてください。

これは入門レベルのラボで、これまでに SQL を使用した経験がほとんど、またはまったくない方を対象としています。Cloud Storage や Cloud Shell の知識があれば役立ちますが、必須ではありません。このラボでは、SQL でのクエリの読み書きの基礎について学び、その知識を BigQuery と Cloud SQL で実際に試してみます。

ラボを始める前に、ご自身の SQL の習熟度を考慮してください。以下のラボはこのラボよりも難易度が高く、お持ちの知識をより高度なユースケースに応用していただけます。

• Weather Data in BigQuery
• Vertex AI と BigQuery による出生率データの分析

準備ができたら下にスクロールし、以下に示す手順に沿ってラボ環境を設定します。

設定と要件

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

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

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

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

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

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

• ラボを完了するために十分な時間を確保してください。ラボをいったん開始すると一時停止することはできません。

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

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

1. [ラボを開始] ボタンをクリックします。ラボの料金をお支払いいただく必要がある場合は、表示されるポップアップでお支払い方法を選択してください。 左側の [ラボの詳細] パネルには、以下が表示されます。

   • [Google コンソールを開く] ボタン
   • 残り時間
   • このラボで使用する必要がある一時的な認証情報
   • このラボを行うために必要なその他の情報（ある場合）

2. [Google コンソールを開く] をクリックします。 ラボでリソースが起動し、別のタブで [ログイン] ページが表示されます。

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

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

3. 必要に応じて、[ラボの詳細] パネルから [ユーザー名] をコピーして [ログイン] ダイアログに貼り付けます。[次へ] をクリックします。

4. [ラボの詳細] パネルから [パスワード] をコピーして [ようこそ] ダイアログに貼り付けます。[次へ] をクリックします。

   重要: 認証情報は左側のパネルに表示されたものを使用してください。Google Cloud Skills Boost の認証情報は使用しないでください。 注: このラボでご自身の Google Cloud アカウントを使用すると、追加料金が発生する場合があります。

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

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

その後このタブで Cloud Console が開きます。

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

タスク 1. SQL の基礎

データベースとテーブル

前述のように、SQL では「構造化データセット」から情報を取り出すことができます。構造化データセットには明確なルールと書式があり、通常はテーブル形式（行と列のデータ）になっています。

非構造化データには、たとえば画像ファイルがあります。非構造化データは SQL で処理できず、BigQuery のデータセットにもテーブルにも格納できません（少なくともネイティブでは格納できません）。たとえば画像データを処理するには、API を通じて Cloud Vision のようなサービスを直接利用します。

構造化データセットの例（単純なテーブル）を以下に示します。

ユーザー	料金	発送済み
ショーン	$35	○
ロッキー	$50	×

Google スプレッドシートを使ったことがある方は、このようなテーブルに見覚えがあるでしょう。テーブルには「ユーザー」、「料金」、「発送済み」の列があり、各列に値が入力されている行が 2 つあります。

データベースは基本的に 1 つまたは複数のテーブルの集合です。SQL は構造化データベースの管理ツールですが、このラボのように、データベース全体ではなく 1 つのテーブルまたは結合された複数のテーブルに対してクエリを実行することも一般的です。

SELECT と FROM

SQL のキーワードは文字どおりの意味を持ちますが、クエリを実行する前にデータへの質問を組み立てておくと役立ちます（ただし、楽しみのためにデータを調べてみたいだけの場合は別です）。

SQL にはあらかじめ定義されたキーワードがあります。これらのキーワードを使用して、質問を英語に似た SQL 構文に変換することで、求める答えをデータベース エンジンから受け取ることができます。

特に重要なキーワードは SELECT と FROM です。

• SELECT では、データセットから pull するフィールドを指定します。
• FROM では、データを pull する 1 つ以上のテーブルを指定します。

わかりやすいように例を使って説明します。以下の example_table テーブルを使用するとします。このテーブルには USER、PRICE、SHIPPED の列があります。

そして、USER 列のデータのみを pull する必要があるとします。これを行うには、SELECT と FROM を使った次のクエリを実行します。

SELECT USER FROM example_table

上記のコマンドを実行すると、example_table にある USER 列のすべての名前が選択されます。

SQL の SELECT キーワードを使って複数の列を選択することもできます。USER 列と SHIPPED 列からデータを pull するとしましょう。これを行うには、以下のように、先ほどの SELECT クエリに別の列の値を追加します（必ずカンマで区切ってください）。

SELECT USER, SHIPPED FROM example_table

このコマンドを実行すると、メモリから USER と SHIPPED のデータを取得できます。

これで、基本的な SQL キーワードを 2 つ学ぶことができました。では、もう少し複雑な内容に進みましょう。

WHERE

WHERE というキーワードも SQL コマンドのひとつで、特定の列の値でテーブルをフィルタできます。example_table から、商品が発送済みのユーザーの名前を pull するとしましょう。先ほどのクエリに、次のように WHERE を追加します。

SELECT USER FROM example_table WHERE SHIPPED='YES'

このコマンドを実行すると、商品が発送済みのすべてのユーザーがメモリから返されます。

SQL の主なキーワードを理解できたところで、BigQuery コンソールでこれらを使ってクエリを実行し、学んだことを試してみましょう。

理解度チェック

ここまでに説明した内容についての理解を深めるために、以下の多肢選択問題を用意しました。正解を目指して頑張ってください。

タスク 2. BigQuery コンソールの操作

BigQuery の枠組み

BigQuery は、Google Cloud 上で稼働するペタバイト規模のフルマネージド データ ウェアハウスです。データ アナリストやデータ サイエンティストが、大規模なデータセットに対するクエリやフィルタの実行、結果の集計、複雑な操作を簡単に行うことができ、サーバーの設定および管理が必要ありません。コマンドライン ツール（Cloud Shell にインストール済み）またはウェブ コンソールを使用して、Google Cloud プロジェクトに格納されているデータを管理したり、クエリを実行したりすることができます。

このラボでは、ウェブ コンソールを使用して SQL クエリを実行します。

BigQuery コンソールを開く

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

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

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

BigQuery コンソールが開きます。

ここで、この UI の主な機能について簡単に見ておきましょう。コンソールの右側にはクエリエディタがあります。ここで、前述のような SQL コマンドを記述、実行します。その下にある [クエリ履歴] には、以前に実行したクエリの一覧が表示されます。

コンソールの左側のペインにはナビゲーション メニューがあります。クエリ履歴、保存したクエリ、ジョブ履歴のほかに、[エクスプローラ] があります。

[エクスプローラ] の最上位のリソースには Google Cloud プロジェクトが表示されています。これは、Google Cloud Skills Boost ラボでログインして使用する一時的な Google Cloud プロジェクトと同じようなものです。作業中のコンソールとこのスクリーンショットを見るとわかるように、[エクスプローラ] に表示されるのはこのラボのプロジェクトのみです。プロジェクト名の横にある矢印をクリックしても、何も表示されません。

これは、プロジェクトにデータセットもテーブルも含まれておらず、クエリの実行対象にできるものが何もないからです。データセットにはテーブルが含まれることは前に説明しました。プロジェクトにデータを追加する際は、BigQuery ではプロジェクトにデータセットが含まれ、データセットにテーブルが含まれることに注意してください。「プロジェクト > データセット > テーブル」という枠組みと、コンソールの詳細について理解できたので、クエリ可能なデータを読み込んでみましょう。

クエリ可能なデータをアップロードする

このセクションでは、BigQuery で SQL コマンドを実行できるようにするために、一般公開データをプロジェクトに pull します。

1. [+ 追加] をクリックします。

2. [名前を指定してプロジェクトにスターを付ける] を選択します。

3. プロジェクト名として「bigquery-public-data」と入力します。

4. [スターを付ける] をクリックします。

この新しいタブでも、引き続きラボのプロジェクトを操作している点に注意してください。ここでは、データセットとテーブルを含む一般公開プロジェクトを分析のために BigQuery に pull しただけであり、プロジェクトを切り替えたわけではありません。ジョブとサービスは引き続き Google Cloud Skills Boost アカウントに関連付けられています。これは、コンソール上部のプロジェクト フィールドで確認できます。

5. これで、次のデータにアクセスできるようになります。

• Google Cloud プロジェクト → bigquery-public-data
• データセット → london_bicycles

6. london bicycles データセットをクリックして、関連付けられているテーブルを表示します。

• テーブル → cycle_hire
• テーブル → cycle_stations

このラボでは、cycle_hire に含まれているデータを使用します。cycle_hire テーブルを開き、[プレビュー] タブをクリックします。ページは次のようになります。

列、および行に入力されている値を確認します。これで、cycle_hire テーブルに対して SQL クエリを実行できる状態になりました。

BigQuery で SELECT、FROM、WHERE を実行する

ここまでのところで、SQL クエリのキーワードと BigQuery のデータの枠組みについて理解し、操作対象のデータを用意しました。このサービスを使用して、いくつか SQL コマンドを実行しましょう。

コンソールの右下を見ると、24,369,201 行のデータがあることがわかります。これは、2015 年から 2017 年の間にロンドン市内で利用されたシェア自転車の件数を示しています（決して少ない数ではありません）。

7 列目のキー end_station_name をメモしておいてください。これは、シェア自転車の最終目的地を指定するキーです。詳細に進む前に、end_station_name 列を分離する簡単なクエリを実行します。

1. 次のコマンドをコピーして、クエリエディタに貼り付けます。

SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;

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

20 秒ほど経過すると、クエリで指定した列 end_station_name を含む 24,369,201 行が返されます。

それでは、乗車時間が 20 分以上だった件数を確認してみましょう。

3. クエリエディタの表示内容をクリアしてから、WHERE キーワードを使用した次のクエリを実行します。

SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;

このクエリの実行には 1 分程度かかる場合があります。

SELECT * により、テーブルからすべての列の値が返されます。duration は秒単位になっているため、1200（60 x 20）という値を使用しています。

右下を見ると、7,334,890 行が返されたことがわかります。全体に占める割合（7334890/24369201）で見ると、ロンドン市内のシェア自転車利用件数のうち約 30% が 20 分以上だった（長時間の利用が多い）ことがわかります。

理解度チェック

ここまでに説明した内容についての理解を深めるために、以下の多肢選択問題を用意しました。正解を目指して頑張ってください。

タスク 3. その他の SQL キーワード: GROUP BY、COUNT、AS、ORDER BY

GROUP BY

GROUP BY キーワードは、一定の基準（列値など）を満たす結果セットの行を集計し、その基準で見つかった一意のエントリをすべて返します。

このキーワードは、テーブルを分類する情報を理解するのに役立ちます。

1. このキーワードの機能をより深く理解するために、クエリエディタの表示内容をクリアしてから、次のコマンドをコピーして貼り付けます。

SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

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

結果として、一意の（重複しない）列値の一覧が表示されます。

GROUP BY を使用しなければ、24,369,201 行がすべて返されることになります。GROUP BY により、テーブルで見つかった一意の列値が出力されます。これは、右下を見ると確認できます。行数は 880 になっています。これは、ロンドン市内のシェア自転車に 880 か所の異なる出発地があることを示しています。

COUNT

COUNT() 関数は、同じ基準（列値など）を満たす行の数を返します。これは、GROUP BY と一緒に使用すると便利です。

前のクエリに COUNT 関数を追加して、出発地ごとの乗車件数を求めます。

• クエリエディタの表示内容をクリアしてから、次のコマンドをコピーして貼り付けて [実行] をクリックします。

SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

出発地ごとの乗車件数が出力されます。

AS

SQL には AS キーワードもあります。これは、テーブルまたは列のエイリアスを作成します。エイリアスはクエリで返される列またはテーブルに対して与えられる新しい名前です。その名前を AS で指定します。

1. 前のクエリに AS キーワードを追加して、実際の処理を見てみましょう。クエリエディタの表示内容をクリアしてから、次のコマンドをコピーして貼り付けます。

SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

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

結果では、右側の列名が COUNT(*) ではなく num_starts になっています。

ご覧のように、返されたテーブルの COUNT(*) 列に num_starts というエイリアス名が設定されています。このキーワードは、大規模なデータセットを操作する場合に特に便利です。あいまいなテーブル名や列名が何を指しているのかわからなくなることは、想像以上によくあります。

ORDER BY

ORDER BY キーワードは、指定の基準または列値に応じて、クエリから返されるデータを昇順または降順で並べ替えます。前のクエリにこのキーワードを追加して、以下の処理を行います。

• 出発ステーションをアルファベット順で並べ、各ステーションを出発地とする乗車件数を含むテーブルを返す。
• 各ステーションを出発地とする乗車件数を昇順で並べたテーブルを返す。
• 各ステーションを出発地とする乗車件数を降順で並べたテーブルを返す。

以下のコマンドは、それぞれが 1 つのクエリです。コマンドごとに、次の操作を行います。

1. クエリエディタの表示内容をクリアします。
2. コマンドをコピーして、クエリエディタに貼り付けます。
3. [実行] をクリックします。結果を確認します。

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

最後のクエリの結果には、出発ステーションが乗車件数の順で一覧表示されます。

「Belgrove Street, King's Cross」からの出発が最も多いことがわかります。ただし、全体に占める割合（24369201÷234458）を見ると、このステーションから出発している件数は 1% に満たないことがわかります。

理解度チェック

ここまでに説明した内容についての理解を深めるために、以下の多肢選択問題を用意しました。正解を目指して頑張ってください。

タスク 4. Cloud SQL を操作する

クエリを CSV ファイルとしてエクスポートする

Cloud SQL は、クラウド上の PostgreSQL と MySQL のリレーショナル データベースを簡単に設定、維持、運用、管理できるようにするフルマネージド データベース サービスで、Cloud SQL が対応しているデータ形式には、ダンプファイル（.sql）と CSV ファイル（.csv）があります。ここでは、cycle_hire テーブルのサブセットを CSV ファイルにエクスポートし、一時的な場所として Cloud Storage にアップロードする方法を説明します。

BigQuery コンソールでは、以下のコマンドを最後に実行しました。

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

1. [クエリ結果] セクションで、[結果を保存] > [CSV（ローカル ファイル）] をクリックします。これによってダウンロードが開始され、このクエリが CSV ファイルとして保存されます。ダウンロードされたファイルの場所と名前は、後で使用するためメモしておきます。

2. クエリエディタの表示内容をクリアしてから、次のコマンドをコピーしてクエリエディタで実行します。

SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;

このクエリは、各ステーションを到着地とする乗車件数を降順で並べたテーブルを返します。

3. [クエリ結果] セクションで、[結果を保存] > [CSV（ローカル ファイル）] をクリックします。これによってダウンロードが開始され、このクエリが CSV ファイルとして保存されます。ダウンロードされたファイルの場所と名前は、この後のセクションで使用するためメモしておきます。

CSV ファイルを Cloud Storage にアップロードする

1. Cloud コンソールに移動し、ストレージ バケットを作成します。作成したファイルは、ストレージ バケットにアップロードできます。

2. ナビゲーション メニュー で [Cloud Storage] > [バケット] の順に選択し、[バケットを作成] をクリックします。

注: 保存されていない作業に関するプロンプトが表示されたら、[終了] をクリックします。

3. バケットの一意の名前を入力します。その他の設定はデフォルトのままにして [作成] をクリックします。

4. プロンプトが表示されたら、公開アクセスの防止ダイアログで [確認] をクリックします。

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

下の [進行状況を確認] をクリックして、ラボの進捗状況を確認します。バケットが正常に作成されていれば、評価スコアが表示されます。

Cloud Storage バケットを作成する

Cloud コンソールに、新しく作成された Cloud Storage バケットが表示されているはずです。

1. [ファイルをアップロード] をクリックし、start_station_name のデータが含まれる CSV ファイルを選択します。

2. 次に [開く] をクリックします。end_station_name のデータについても同様に操作します。

3. start_station_name ファイルの名前を変更します。ファイル名の端にあるその他アイコンをクリックして、[名前を変更] をクリックします。ファイル名を「start_station_data.csv」に変更します。

4. end_station_name ファイルの名前を変更します。ファイル名の端にあるその他アイコンをクリックして、[名前を変更] をクリックします。ファイル名を「end_station_data.csv」に変更します。

[バケットの詳細] ページの [オブジェクト] リストに start_station_name.csv と end_station_data.csv が表示されているはずです。

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

[進行状況を確認] をクリックして、実行したタスクを確認します。バケットに CSV オブジェクトが正常にアップロードされている場合は、評価スコアが表示されます。

CSV ファイルを Cloud Storage にアップロードする

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

コンソールで、ナビゲーション メニュー > [SQL] の順に選択します。

1. [インスタンスを作成] > [MySQL を選択] をクリックします。

2. インスタンス ID として「my-demo」と入力します。

3. [パスワード] フィールドに安全なパスワードを入力します（このパスワードを忘れないでください）。

4. データベース バージョンとして [MySQL 8] を選択します。

5. [Cloud SQL のエディションの選択] で、[Enterprise] を選択します。

6. [プリセット] で、[開発]（vCPU 4 個、16 GB RAM、100 GB ストレージ、シングルゾーン）を選択します。

警告: [開発] よりも大きなプリセットを選択すると、プロジェクトで問題が報告され、ラボが終了します。

7. [複数のゾーン（高可用性）] フィールドを に設定します。

8. [インスタンスを作成] をクリックします。

注: インスタンスが作成されるまでに数分かかることがあります。作成されたら、SQL インスタンス ページのインスタンス名の横に緑色のチェックマークが表示されます。

9. Cloud SQL インスタンスをクリックします。SQL の [概要] ページが開きます。

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

ラボの進捗状況を確認するには、下の [進行状況を確認] をクリックします。Cloud SQL インスタンスが正常に設定されている場合は、評価スコアが表示されます。

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

タスク 6. Cloud SQL で新しいクエリを実行する

CREATE キーワード（データベースとテーブル）

Cloud SQL インスタンスが起動して実行中になったので、Cloud Shell コマンドラインを使用してそのインスタンス内にデータベースを作成します。

1. コンソールの右上にあるアイコンをクリックして Cloud Shell を開きます。

2. 次のコマンドを実行して、プロジェクト ID を環境変数として設定します。

export PROJECT_ID=$(gcloud config get-value project) gcloud config set project $PROJECT_ID

Cloud Shell でデータベースを作成する

1. Cloud Shell で以下のコマンドを実行して、ブラウザを開かずに認証を設定します。

gcloud auth login --no-launch-browser

これにより、ブラウザで開くことができるリンクが生成されます。ラボのアカウントにログイン済みのブラウザで、このリンクを開きます。ログインしたら、表示された確認コードをコピーします。コードを Cloud Shell に貼り付けます。

2. 以下のコマンドを実行して SQL インスタンスに接続します。インスタンスに my-demo 以外の名前を使用した場合はその名前に置き換えてください。

gcloud sql connect my-demo --user=root --quiet 注: インスタンスへの接続には 1 分ほどかかる場合があります。「Operation failed because another operation was already in progress」というメッセージが表示された場合は、SQL インスタンスの作成が終了するまで待ってから、もう一度接続を試します。

3. プロンプトが表示されたら、インスタンスに指定した root パスワードを入力します。

次のような出力が表示されます。

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 494 Server version: 8.0.31-google (Google) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Cloud SQL インスタンスにはデータベースが事前に構成されていますが、ここでは独自のデータベースを作成して、ロンドン市内のシェア自転車のデータを格納します。

4. MySQL サーバーのプロンプトで以下のコマンドを実行して、bike というデータベースを作成します。

CREATE DATABASE bike;

次の出力が表示されます。

Query OK, 1 row affected (0.05 sec) mysql>

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

進捗状況をチェックするには、[進行状況を確認] をクリックして、実行したタスクを確認します。Cloud SQL インスタンスにデータベースが正常に作成された場合は、評価スコアが表示されます。

データベースを作成する

Cloud Shell でテーブルを作成する

1. 次のコマンドを実行して、bike データベース内にテーブルを作成します。

USE bike; CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);

前と同じ CREATE キーワードが使われていますが、今回は TABLE 句を使用して、データベースではなくテーブルを作成することを指定しています。USE キーワードは、接続先のデータベースを指定しています。これで、「start_station_name」と「num」の 2 つの列を含む「london1」というテーブルが作成されます。VARCHAR(255) は、最大 255 文字を格納できる可変長の文字列型の列を指定し、INT は整数型の列です。

2. 以下のコマンドを実行して、「london2」という別のテーブルを作成します。

USE bike; CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);

3. 空のテーブルが作成されたことを確認します。MySQL サーバーのプロンプトで以下のコマンドを実行します。

SELECT * FROM london1; SELECT * FROM london2;

どちらのコマンドでも、以下の出力が表示されます。

Empty set (0.04 sec)

「空のセット」と表示されるのは、まだデータを読み込んでいないからです。

テーブルに CSV ファイルをアップロードする

Cloud SQL コンソールに戻ります。ここで、CSV ファイル start_station_name と end_station_name を、新しく作成した london1 テーブルと london2 テーブルにアップロードします。

1. Cloud SQL のインスタンス ページで、[インポート] をクリックします。
2. Cloud Storage ファイルのフィールドで [参照] をクリックし、バケット名の横にある矢印をクリックします。[start_station_data.csv] をクリックしてから、[選択] をクリックします。
3. ファイル形式は [CSV] を選択します。
4. bike データベースを選択し、「london1」をテーブルとして入力します。
5. [インポート] をクリックします。

もう 1 つの CSV ファイルについても同様の操作を行います。

1. Cloud SQL のインスタンス ページで、[インポート] をクリックします。
2. Cloud Storage ファイルの欄で [参照] をクリックします。次に、バケット名の横にある矢印、[end_station_data.csv]、[選択] の順にクリックします。
3. ファイル形式は [CSV] を選択します。
4. bike データベースを選択し、「london2」をテーブルとして入力します。
5. [インポート] をクリックします。

これで、両方の CSV ファイルが bike データベース内のテーブルにアップロードされました。

1. Cloud Shell セッションに戻り、MySQL サーバーのプロンプトで以下のコマンドを実行して、london1 の内容を確認します。

SELECT * FROM london1;

出力の行数は、ステーション名ごとに 1 行ずつ、計 955 行になります。

2. 以下のコマンドを実行して、london2 にデータが取り込まれていることを確認します。

SELECT * FROM london2;

出力の行数は、ステーション名ごとに 1 行ずつ、計 959 行になります。

DELETE キーワード

データ管理に役立つ SQL キーワードをさらにいくつか紹介しましょう。最初は DELETE キーワードです。

• 以下のコマンドを MySQL セッションで実行し、london1 と london2 の 1 行目を削除します。

DELETE FROM london1 WHERE num=0; DELETE FROM london2 WHERE num=0;

どちらのコマンドを実行しても、以下の出力が表示されます。

Query OK, 1 row affected (0.04 sec)

削除された行は CSV ファイルの列ヘッダーでした。この DELETE キーワードは、必ずファイルの 1 行目を削除するというものではなく、列名（この例では「num」）に特定の値（この例では「0」）が含まれるすべての行をテーブルから削除します。SELECT * FROM london1; と SELECT * FROM london2; の各クエリを実行してテーブルの先頭までスクロールしてみると、その行がすでにないことがわかります。

INSERT INTO キーワード

INSERT INTO キーワードを使用して、テーブルに値を挿入することもできます。

• 次のコマンドを実行して、london1 に新しい行を挿入します。start_station_name は「test destination」に、num は「1」に設定されます。

INSERT INTO london1 (start_station_name, num) VALUES ("test destination", 1);

INSERT INTO キーワードはテーブル（「london1」）を必要とし、最初のかっこ内の用語（この例では「start_station_name」と「num」）で指定された列が含まれる新しい行を作成します。「VALUES」句に続く値が新しい行に挿入されます。

次の出力が表示されます。

Query OK, 1 row affected (0.05 sec)

SELECT * FROM london1; のクエリを実行すると、「london1」テーブルの末尾に新しい行が追加されていることがわかります。

UNION キーワード

ここで紹介する最後の SQL キーワードは UNION です。このキーワードは、複数の SELECT クエリの出力を結果セットに結合します。ここでは、UNION を使用して、「london1」と「london2」の各テーブルのサブセットを結合します。

以下の連結クエリは、両方のテーブルから特定のデータを pull し、UNION 演算子で結合します。

• MySQL サーバーのプロンプトで次のコマンドを実行します。

SELECT start_station_name AS top_stations, num FROM london1 WHERE num>100000 UNION SELECT end_station_name, num FROM london2 WHERE num>100000 ORDER BY top_stations DESC;

最初の SELECT クエリは、「london1」テーブルから 2 つの列を選択し、「start_station_name」にはエイリアス「top_stations」を作成しています。WHERE キーワードを使用して、出発地となった回数が 10 万回を超えるステーション名のみを pull しています。

2 番目の SELECT クエリは、「london2」テーブルから 2 つの列を選択し、WHERE キーワードを使用して、到着地となった回数が 10 万回を超えるステーション名のみを pull しています。

間にある UNION キーワードは、「london2」のデータを「london1」に取り込むことで、これらのクエリの出力を結合します。「london1」に「london2」を結合するため、優先される列値は「top_stations」と「num」になります。

ORDER BY は、最終的に結合されたテーブルを、「top_stations」列の値を使ってアルファベット降順に並べ替えます。

次の出力が表示されます。

14 のうち 13 のステーションが、出発地としても到着地としても上位に入っていることがわかります。基本的な SQL キーワードを使って大規模なデータセットに対してクエリを実行し、データポイントと、具体的な質問への答えを受け取ることができました。

お疲れさまでした

このラボでは、SQL の基礎に加え、キーワードを使って BigQuery と Cloud SQL でクエリを実行する方法を学びました。また、プロジェクト、データベース、テーブルの基本概念とデータを操作および編集するキーワードについて確認しました。さらに、データセットを BigQuery に読み込む方法とテーブルに対するクエリの実行方法について学び、実際に Cloud SQL でインスタンスを作成して、データのサブセットをデータベース内のテーブルに送信しました。Cloud SQL でクエリを連結して実行した結果、ロンドン市内のシェア自転車の出発地と到着地について興味深い結論を得ることができました。

クエストを完了する

このセルフペース ラボは、「Data Science on Google Cloud」、「Cloud SQL」、「BigQuery Basics for Data Analysts」、「NCAA® March Madness®: Bracketology with Google Cloud」、「Cloud Engineering」、「Data Catalog Fundamentals」、「Applying BQML's Classification, Regression, and Demand Forecasting for Retail Applications」クエストの一部です。クエストとは学習プログラムを構成する一連のラボのことで、完了すると成果が認められて上のようなバッジが贈られます。バッジは公開して、オンライン レジュメやソーシャル メディア アカウントにリンクできます。このラボの修了後、このラボが含まれるクエストに登録すれば、すぐにクレジットを受け取ることができます。受講可能なすべてのクエストについては、Google Cloud Skills Boost カタログをご覧ください。

次のステップと詳細情報

以下の Google Cloud Skill Boost ラボで、Cloud SQL と BigQuery についての学習と演習を続けてください。

• Weather Data in BigQuery
• Exploring NCAA Data with BigQuery
• Loading Data into Google Cloud SQL
• Cloud SQL with Terraform

『Data Science on the Google Cloud Platform, 2nd Edition』（O'Reilly Media, Inc.）で、データ サイエンスの詳細を学んでください。

Google Cloud トレーニングと認定資格

Google Cloud トレーニングと認定資格を通して、Google Cloud 技術を最大限に活用できるようになります。必要な技術スキルとベスト プラクティスについて取り扱うクラスでは、学習を継続的に進めることができます。トレーニングは基礎レベルから上級レベルまであり、オンデマンド、ライブ、バーチャル参加など、多忙なスケジュールにも対応できるオプションが用意されています。認定資格を取得することで、Google Cloud テクノロジーに関するスキルと知識を証明できます。

マニュアルの最終更新日: 2024 年 1 月 16 日

ラボの最終テスト日: 2023 年 10 月 6 日

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