AWS EC2 から bq コマンドラインツールで BigQuery にアクセス＆操作してみた

こんにちは、みかみです。

GCP ＆ BigQuery 勉強中です。

GUI 経由では少し BigQuery を触ってみましたが、実際のシステムではプログラムからアクセスするケースの方が多いと思います。

弊社、業務では AWS をメインで使用しているため、例えば EC2 上で動くシステムから、BigQuery にアクセスするにはどうすればいいの？

ということで。

やりたいこと

• EC2 から BigQuery にアクセスしたい
• コマンドラインで BigQuery を参照、操作してみたい

やってみた

EC2の準備

EC2（Amazon Linux 2） を立ち上げて、Google Cloud SDK をインストールします。

インストール手順は以下を参照。

• Google Cloud SDK をAmazon Linux 2環境にインストール(スクリプトベース＋検証コード利用) ｜ Developers.IO

無事、SDK がインストールできました。

[ec2-user@ip-172-31-31-170 ~]$ gcloud version
Google Cloud SDK 271.0.0
bq 2.0.50
core 2019.11.08
gsutil 4.46

bq コマンド実行

bq コマンドを使用して、まずは既存のテーブル情報を見てみます。

• bq コマンドライン ツールの使用に関するクイックスタート

[ec2-user@ip-172-31-31-170 ~]$ bq show cm-da-mikami-yuki-258308:test_s3.pref
Table cm-da-mikami-yuki-258308:test_s3.pref

   Last modified         Schema        Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Labels
 ----------------- ------------------ ------------ ------------- ------------ ------------------- ------------------ --------
  11 Nov 17:40:42   |- code: integer   47           902
                    |- name: string

ちゃんとテーブルスキーマが取得できました。

データを select してみます。

[ec2-user@ip-172-31-31-170 ~]$ bq query --use_legacy_sql=false \
> 'SELECT
>    name
>  FROM
>    `cm-da-mikami-yuki-258308`.test_s3.pref
>  WHERE
>    code = 1'
Waiting on bqjob_r74dc3ff5f1f36da0_0000016e6eeece5b_1 ... (0s) Current status: DONE
+------+
| name |
+------+
| 北海道  |
+------+

SQLで、ちゃんとデータも参照できました。

なお、初回のみ、デフォルトプロジェクトの設定を求められました。

[ec2-user@ip-172-31-31-170 ~]$ bq show cm-da-mikami-yuki-258308:test_s3.perf

Welcome to BigQuery! This script will walk you through the
process of initializing your .bigqueryrc configuration file.

First, we need to set up your credentials if they do not
already exist.

Credential creation complete. Now we will select a default project.

List of projects:
  #          projectId               friendlyName
 --- -------------------------- ----------------------
  1   cm-xxxxxxxxxxxxx           cm-xxxxxxxxxxxxx
  2   cm-da-mikami-yuki-258308   cm-da-mikami-yuki
  3   cm-xxxxxxxxxxxxxxxxx       cm-xxxxxxxxxxxxxxxxx
  4   tokyo-xxxxxxxxxxxxxxxx     XXXXXXXXXXXXXXXX
Found multiple projects. Please enter a selection for
which should be the default, or leave blank to not
set a default.

Enter a selection (1 - 4): 2

BigQuery configuration complete! Type "bq" to get started.

EC2 に配置した txt ファイルデータを BigQuery にロードしてみる

BigQuery にロードするデータを、EC2 にダウンロードします。

下記、クイックスタートに URL があった、赤ちゃんの名前のサンプルデータをダウンロードしました。

• bq コマンドライン ツールの使用に関するクイックスタート

[ec2-user@ip-172-31-31-170 ~]$ mkdir data
[ec2-user@ip-172-31-31-170 ~]$ cd data/
[ec2-user@ip-172-31-31-170 data]$ wget http://www.ssa.gov/OACT/babynames/names.zip
--2019-11-15 12:03:08--  http://www.ssa.gov/OACT/babynames/names.zip
Resolving www.ssa.gov (www.ssa.gov)... 137.200.4.16, 2001:1930:e03::aaaa
Connecting to www.ssa.gov (www.ssa.gov)|137.200.4.16|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://www.ssa.gov/OACT/babynames/names.zip [following]
--2019-11-15 12:03:10--  https://www.ssa.gov/OACT/babynames/names.zip
Connecting to www.ssa.gov (www.ssa.gov)|137.200.4.16|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7200451 (6.9M) [application/zip]
Saving to: ‘names.zip’

100%[======================================================================================================>] 7,200,451    348KB/s   in 21s

2019-11-15 12:03:32 (328 KB/s) - ‘names.zip’ saved [7200451/7200451]
[ec2-user@ip-172-31-31-170 data]$ unzip names.zip
Archive:  names.zip
  inflating: yob1880.txt
  inflating: yob1881.txt
  inflating: yob1882.txt
  inflating: yob1883.txt
（省略）

続いて bq コマンドで、BigQuery にデータセットを作成します。

[ec2-user@ip-172-31-31-170 ~]$ bq mk test_ds
Dataset 'cm-da-mikami-yuki-258308:test_ds' successfully created.
[ec2-user@ip-172-31-31-170 ~]$ bq ls
  datasetId
 -----------
  test_ds
  test_s3

今作成した test_ds に、ダウンロードしてきた2018年のアメリカの赤ちゃんの名前データをロードしてみます。

bq コマンドでは、テーブル作成とロードが同時にできてしまうらしい！ 実行してみます。

[ec2-user@ip-172-31-31-170 ~]$ bq load test_ds.names2018 ./data/yob2018.txt name:string,gender:string,count:integer
Upload complete.
Waiting on bqjob_r5b726554ff5cb63b_0000016e6f227990_1 ... (0s) Current status: DONE

ロード完了。

ちゃんとテーブルできてるかな？

[ec2-user@ip-172-31-31-170 ~]$ bq ls test_ds
   tableId    Type    Labels   Time Partitioning   Clustered Fields
 ----------- ------- -------- ------------------- ------------------
  names2018   TABLE

できてます。

2018年、アメリカで最も人気の赤ちゃんの名前を取得してみると・・・

[ec2-user@ip-172-31-31-170 ~]$ bq query --use_legacy_sql=false \
> 'SELECT
>    name,
>    gender
>  FROM
>    `cm-da-mikami-yuki-258308`.test_ds.names2018
>  WHERE
>    count = (
>      SELECT
>        MAX(count)
>      FROM
>        `cm-da-mikami-yuki-258308`.test_ds.names2018
>    )'
Waiting on bqjob_re4905a65d01ef75_0000016e6f2fefec_1 ... (0s) Current status: DONE
+------+--------+
| name | gender |
+------+--------+
| Liam | M      |
+------+--------+

Liam 君だそうです！

コンソールからも、ちゃんとテーブルが作成され、データがロードされているのが確認できました。

まとめ（所感）

bq コマンド、思っていた以上に簡単に使うことができました。

ls や mk など、感覚的に分かりやすいコマンドなのも嬉しいです。

データロード時に一緒にテーブル作成もできるのは、シンプルで非常に使いやすかったです！

クイックスタート のドキュメントも親切で、ほんとにクイックにスタートできました！ｖ