bq ツールを使用してデータを読み込み、クエリを実行する

bq コマンドライン ツールを使用してデータセットを作成する方法、サンプルデータを読み込む方法、テーブルに対してクエリを実行する方法を学習します。

始める前に

  1. In the Trusted Cloud console, on the project selector page, select or create a Trusted Cloud project.

    Go to project selector

  2. Verify that billing is enabled for your Trusted Cloud project.

  3. このチュートリアルで使用する Trusted Cloud プロジェクトの課金を有効にしない場合は、BigQuery サンドボックスでデータを操作します。BigQuery サンドボックスを使用すると、一部の BigQuery 機能を無料で使用して、BigQuery を試すことができます。

  4. BigQuery API が有効になっていることを確認します。

    API を有効にする

    新しいプロジェクトを作成している場合は、BigQuery API が自動的に有効になっています。

  5. In the Trusted Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Trusted Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  6. ソースの一般公開データファイルをダウンロードする

    1. 赤ちゃんの名前の zip ファイルをダウンロードします。
    2. ZIP ファイルを展開します。このファイルには、データセット スキーマを記述する NationalReadMe.pdf という名前のファイルが含まれています。赤ちゃんの名前のデータセットについて詳細を確認します
    3. yob2010.txt ファイルを開きます。これは、名前、出生時の性別、その名前の子供の人数という 3 つの列を含むカンマ区切り値(CSV)ファイルです。このファイルにはヘッダー行がありません。
    4. ファイルを作業ディレクトリに移動します。
      • Cloud Shell で作業している場合は、 [詳細] > [アップロード] の順にクリックし、[ファイル選択] をクリックして、yob2010.txt ファイルを選択し、[アップロード] をクリックします。
      • ローカルシェルで作業している場合は、bq ツールを実行しているディレクトリにファイル yob2010.txt をコピーまたは移動します。

    データセットを作成する

    1. babynames という名前のデータセットを作成します。

      bq mk babynames
      

      出力は次のようになります。

      Dataset 'myproject:babynames' successfully created.
      

      データセット名は最大 1,024 文字で、A~Z、a~z、0~9、アンダースコアで構成されます。名前の先頭に数字やアンダースコアは使用できません。また、スペースは使用できません。

    2. データセット babynames がプロジェクトに表示されることを確認します。

      bq ls
      

      出力は次のようになります。

        datasetId
      -------------
        babynames
      

    テーブルにデータを読み込む

    1. babynames データセットで、ソースファイル yob2010.txtnames2010 という名前の新しいテーブルに読み込みます。

      bq load babynames.names2010 yob2010.txt name:string,assigned_sex_at_birth:string,count:integer
      

      出力は次のようになります。

      Upload complete.
      Waiting on bqjob_r3c045d7cbe5ca6d2_0000018292f0815f_1 ... (1s) Current status: DONE
      

      デフォルトでは、BigQuery は読み込まれるデータが UTF-8 エンコード データであるものと想定します。ISO-8859-1(Latin-1)でエンコードされたデータがあり、そのデータに問題がある場合は、bq load -E=ISO-8859-1 フラグを使用して、Latin-1 としてデータを処理するように BigQuery に対して指定します。詳細については、エンコードをご覧ください。

    2. テーブル names2010babynames データセットに表示されることを確認します。

      bq ls babynames
      

      出力は次のようになります。出力を簡素化するために、一部の列は省略されています。

        tableId     Type
      ----------- ---------
       names2010    TABLE
      
    3. 新しい names2010 テーブルのテーブル スキーマが name: stringassigned_sex_at_birth: stringcount: integer であることを確認します。

      bq show babynames.names2010
      

      出力は次のようになります。出力を簡素化するために、一部の列は省略されています。

        Last modified        Schema                      Total Rows   Total Bytes
      ----------------- ------------------------------- ------------ ------------
      14 Mar 17:16:45   |- name: string                    34089       654791
                        |- assigned_sex_at_birth: string
                        |- count: integer
      

    テーブルデータをクエリする

    1. データ内の最も人気のある女の子の名前を特定します。

      bq query --use_legacy_sql=false \
          'SELECT
            name,
            count
          FROM
            `babynames.names2010`
          WHERE
            assigned_sex_at_birth = "F"
          ORDER BY
            count DESC
          LIMIT 5;'
      

      出力は次のようになります。

      +----------+-------+
      |   name   | count |
      +----------+-------+
      | Isabella | 22925 |
      | Sophia   | 20648 |
      | Emma     | 17354 |
      | Olivia   | 17030 |
      | Ava      | 15436 |
      +----------+-------+
      
    2. データの中から最も人気の低い男の子の名前を特定します。

      bq query --use_legacy_sql=false \
          'SELECT
            name,
            count
          FROM
            `babynames.names2010`
          WHERE
            assigned_sex_at_birth = "M"
          ORDER BY
            count ASC
          LIMIT 5;'
      

      出力は次のようになります。

      +----------+-------+
      |   name   | count |
      +----------+-------+
      | Aamarion |     5 |
      | Aarian   |     5 |
      | Aaqib    |     5 |
      | Aaidan   |     5 |
      | Aadhavan |     5 |
      +----------+-------+
      

      ソースデータは出現数が 5 未満の名前を除外するので、最小数は 5 です。

    クリーンアップ

    このページで使用したリソースについて、 Trusted Cloud アカウントに課金されないようにするには、リソースを含む Trusted Cloud プロジェクトを削除します。

    プロジェクトの削除

    BigQuery サンドボックスを使用して一般公開データセットにクエリを実行した場合、プロジェクトに対する課金は有効になりません。

    課金をなくす最も簡単な方法は、チュートリアル用に作成したプロジェクトを削除することです。

    プロジェクトを削除するには:

    1. In the Trusted Cloud console, go to the Manage resources page.

      Go to Manage resources

    2. In the project list, select the project that you want to delete, and then click Delete.
    3. In the dialog, type the project ID, and then click Shut down to delete the project.

    リソースを削除する

    既存のプロジェクトを使用した場合は、作成したリソースを削除します。

    1. babynames データセットを削除します。

      bq rm --recursive=true babynames
      

      --recursive フラグは、names2010 テーブルを含むデータセット内のすべてのテーブルを削除します。

      出力は次のようになります。

      rm: remove dataset 'myproject:babynames'? (y/N)
      
    2. 削除コマンドを確定するには、「y」と入力します。

    次のステップ