arrow_back

Introduction to SQL for BigQuery and Cloud SQL

参加 ログイン

Introduction to SQL for BigQuery and Cloud SQL

1時間 15分 クレジット: 1

GSP281

Google Cloud セルフペース ラボ

概要

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

このラボは SQL の入門編となっており、Qwiklabs でこれからデータ サイエンスに関する多数のラボとクエストに取り組むための足がかりとなります。二部構成の前半では SQL クエリの基本的なキーワードについて学び、ロンドン市内のシェア自転車に関する一般公開データセットに対して BigQuery コンソールでクエリを実行します。

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

目標

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

  • データベースをテーブルおよびプロジェクトと区別する。
  • SELECTFROMWHERE のキーワードを使用してシンプルなクエリを作成する。
  • BigQuery コンソール内のコンポーネントと階層構造を確認する。
  • データベースとテーブルを BigQuery に読み込む。
  • テーブルに対して簡単なクエリを実行する。
  • COUNTGROUP BYASORDER BY のキーワードについて学習する。
  • 上記のコマンドを実行、連結して、データセットから有意なデータを pull する。
  • データのサブセットを CSV ファイルにエクスポートし、そのファイルを Cloud Storage の新しいバケットに格納する。
  • 新しい Cloud SQL インスタンスを作成し、エクスポートした CSV ファイルを新しいテーブルとして読み込む。
  • CREATE DATABASECREATE TABLEDELETEINSERT INTOUNION の各クエリを Cloud SQL で実行する。

前提事項

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

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

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

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

設定と要件

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

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

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

必要なもの

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

  • 標準的なインターネット ブラウザ(Chrome を推奨)
  • ラボを完了するために十分な時間

注: すでに個人の Google Cloud アカウントやプロジェクトをお持ちの場合でも、ラボでは使用しないでください。

注: Chrome OS デバイスを使用している場合は、シークレット ウィンドウを開いてこのラボを実行してください。

ラボを開始して Console にログインする方法

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

    Google Console を開く

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

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

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

    アカウントを選択

  4. [ログイン] ページが開きます。[接続の詳細] パネルでコピーしたユーザー名を貼り付けます。パスワードもコピーして貼り付けます。

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

  5. 以降のページでは次の点にご注意ください。

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

しばらくすると、このタブで GCP Console が開きます。

SQL の基礎

データベースとテーブル

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

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

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

ユーザー

料金

発送済み

山田

3,500 円

佐藤

5,000 円

×

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

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

SELECT と FROM

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

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

特に重要なキーワードに SELECTFROM があります。

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

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

14422cb7144f3ae.png

pull するのは USER 列のデータだけだとします。これを行うには、SELECTFROM を使った次のクエリを実行します。

SELECT USER FROM example_table

このコマンドを実行した場合、example_table 内から USER 列の名前がすべて選択されます。

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

SELECT USER, SHIPPED FROM example_table

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

a4027fb83edf734.png

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

WHERE

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

SELECT USER FROM example_table WHERE SHIPPED='YES'

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

5566150a165277e8.png

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

理解度を確認する

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

BigQuery コンソールの操作

BigQuery の枠組み

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

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

BigQuery コンソールを開く

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

BigQuery_menu.png

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

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

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

Bigquery-UI.png

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

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

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

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

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

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

[+ データを追加] リンクをクリックし、[一般公開データセットを調べる] を選択します。

BQ_adddata_2.png

検索バーに「london」と入力し、London Bicycle Hires のタイルを選択して、[データセットを表示] をクリックします。

新しいタブが開いて、「bigquery-public-data」という新しいプロジェクトが [エクスプローラ] パネルに表示されます。

BQ_pubdata_2.png

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

BQ_proj_check_2.png

[bigquery-public-data] > [london_bicycles] の順に展開し、[cycle_hire] を選択します。データは以下のように BigQuery の枠組みに従っています。

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

cycle_hire テーブルが開いたら、コンソールの中央で [プレビュー] タブをクリックします。ページは次のようになります。

cycle_hire.png

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

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

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

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

7 列目のキー end_station_name に注目します。これは、シェア自転車の最終目的地を示しています。詳細に進む前に、end_station_name 列を分離する簡単なクエリを実行します。次のコマンドをコピーして、クエリエディタに貼り付けます。

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

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

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

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

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

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

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

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

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

理解度を確認する

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

その他の SQL キーワード: GROUP BY、COUNT、AS、ORDER BY

GROUP BY

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

このキーワードは、テーブルを分類する情報を理解するのに役立ちます。このキーワードの機能をより深く理解するために、クエリエディタの表示をクリアしてから、次のコマンドをコピーして貼り付けます。

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

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

以下のような出力が返されます(行の値は異なる場合があります)。

30b04fc6f21c544c.png

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;

以下のような出力が返されます(行の値は異なる場合があります)。

b62aaf26b7a35e35.png

結果は、出発地ごとの乗車件数を示しています。

AS

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

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

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

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

以下のような出力が返されます(行の値は異なる場合があります)。

a45a8b122dbfea2b.png

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

ORDER BY

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

  • 各ステーションを出発地とする乗車件数を含み、出発ステーションがアルファベット順になっているテーブルを返す。
  • 各ステーションを出発地とする乗車件数を含み、件数が昇順になっているテーブルを返す。
  • 各ステーションを出発地とする乗車件数を含み、件数が降順になっているテーブルを返す。

以下のコマンドは、それぞれが 1 つのクエリです。コマンドごとに、クエリエディタの表示をクリアし、コマンドをコピーしてクエリエディタに貼り付け、[実行] をクリックします。結果を確認します。

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;

最後のクエリは以下の結果を返します。

368742bde0aa54d6.png

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

理解度を確認する

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

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;

[クエリ結果] 欄で [結果の保存] > [CSV(ローカル ファイル)] > [保存] の順にクリックします。これによってダウンロードが実行され、このクエリが CSV ファイルとして保存されます。ダウンロードされたファイルの場所と名前は、後で使用するためメモしておきます。

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

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

このクエリは、各ステーションを到着地とする乗車件数を含み、件数が降順になっているテーブルを返します。次の出力が表示されます。

814554dc82c60a74.png

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

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

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

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

バケットに一意の名前を入力し、その他の設定は変更せずに [作成] をクリックします。

bucket_details.png

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

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

Cloud Storage バケットを作成する

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

[ファイルをアップロード] をクリックし、start_station_name のデータが含まれる CSV ファイルを選択します。次に [開く] をクリックします。end_station_name のデータについても同様に操作します。

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

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

バケットは以下のようになります。

4ca41c9e381d94f.png

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

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

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

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

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

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

データベース エンジンンの選択で、[MySQL を選択] をクリックします。

インスタンス名(「qwiklabs-demo」など)を入力し、root パスワードの欄に安全なパスワードを入力して(忘れないようにしてください)、[インスタンスを作成] をクリックします。

CreateInstance.png

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

Cloud SQL インスタンスをクリックします。以下のようなページが表示されます。

overview.png

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

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

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

Cloud SQL で新しいクエリを実行する

CREATE キーワード(データベースとテーブル)

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

Google Cloud Shell の有効化

Google Cloud Shell は、デベロッパー ツールと一緒に読み込まれる仮想マシンです。5 GB の永続ホーム ディレクトリが用意されており、Google Cloud で稼働します。Google Cloud Shell では、コマンドラインで GCP リソースにアクセスできます。

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

    Cloud Shell アイコン

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

    cloudshell_continue

環境のプロビジョニングと接続には少し時間がかかります。接続すると、すでに認証されており、プロジェクトは PROJECT_ID に設定されています。例えば:

Cloud Shell 端末

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

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

gcloud auth list

出力:

Credentialed accounts:
- <myaccount>@<mydomain>.com (active)
	

出力例:

Credentialed accounts:
- google1623327_student@qwiklabs.net
	

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

gcloud config list project
	

出力:

[core]
project = <project_ID>
	

出力例:

[core]
project = qwiklabs-gcp-44776a13dea667a6
	

Cloud Shell で以下のコマンドを実行して SQL インスタンスに接続します。インスタンスに qwiklabs-demo 以外の名前を使用した場合はその名前に置き換えます。

gcloud sql connect  qwiklabs-demo --user=root

インスタンスへの接続には 1 分程度かかる場合があります。

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

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

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 494
Server version: 5.7.14-google-log (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 インスタンスにはデータベースが事前に構成されていますが、ここでは独自のデータベースを作成して、ロンドン市内のシェア自転車のデータを格納します。

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

CREATE DATABASE bike;

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

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

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

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

データベースを作成する

次のコマンドを実行して、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 は整数型の列です。

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

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

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

SELECT * FROM london1;
SELECT * FROM london2;

どちらのコマンドも、出力は以下のようになります。

Empty set (0.04 sec)

「Empty set」と出力されるのは、まだデータを読み込んでいないためです。

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

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

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

ImportData

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

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

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

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

SELECT * FROM london1;

出力は、ステーション名ごとに 1 行ずつ、計 881 行になります。出力の形式は以下のようになります。

48c3c74603692827.png

以下のコマンドを実行して、london2 にデータが入力されていることを確認します。

SELECT * FROM london2;

出力は、ステーション名ごとに 1 行ずつ、計 883 行になります。出力の形式は以下のようになります。

85a788ec7971f8a0.png

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」テーブルの末尾に新しい行が追加されていることがわかります。

b067eb36e63b9e68.png

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」列の値を使ってアルファベット降順に並べ替えます。

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

num.png

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

お疲れさまでした

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

quest-badge-two.png Data_Science_125.png cloudsql-quest-badge.png BigQueryBasicsforDataAnalysts-125x135.png MarchMadness02_125.png CloudEngineeringExaPrep_125.pmg Data Catalog Quest Badge.pmg bq_retail_125.png

クエストを完了する

このセルフペース ラボは、Qwiklabs の「Data Science on Google Cloud」、「Scientific Data Processing」、「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 Forcastng for Retail Applications」クエストの一部です。クエストとは学習パスを構成する一連のラボのことで、完了すると成果が認められて上のようなバッジが贈られます。バッジは公開して、オンライン レジュメやソーシャル メディア アカウントにリンクできます。このラボの修了後、次のクエストに登録すれば、すぐにクレジットを受け取ることができます。受講可能なその他の Qwiklabs のクエストもご確認ください

次のステップと詳細情報

引き続き Cloud SQL と BigQuery について学習し、実践練習を積んでください。

Google Cloud Training & Certification

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

マニュアルの最終更新日: 2021 年 6 月 4 日
ラボの最終テスト日: 2021 年 6 月 4 日

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