この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは、データアナリティクス事業本部のみかみです。
本エントリは、クラスメソッド BigQuery Advent Calendar 2020 の 20 日目のエントリです。 25日のアドベントカレンダー終了まで、弊社クラスメソッド データアナリティクス事業本部のメンバーで、Google BigQuery に関する記事を紡いでいこうと思います。
やりたいこと
- BigQuery から Bigtable データを参照するにはどうすればいいのか知りたい
- Bigtable に格納している緯度経度データを BigQuery Geo Viz で可視化したい
IoT センサーから送られてくる緯度経度情報を、Bigtable に格納しているというシナリオを想定しました。
ため込んだ緯度経度データを SQL で分析したいのですが、NoSQL データベースである Bigtable では SQL を実行できません。 さらに、緯度経度の数値だけだと分かりにくいので、どこの位置だか一目で分かるように地図上に地点をビジュアル表示できたらなおうれしい！ ということで・・・。
Bigtable データを参照するための外部テーブルを BigQuery に作成し、SQL で緯度経度データを取得して BigQuery Geo Viz で可視化してみます。
Bigtable データを準備
BigQuery から参照する、Bigtable のデータを準備します。
以下のサイトから、都道府県の県庁所在の地緯度経度情報をいただいてきました。
2020/12 現在、BigQuery からの Bigtable データ参照がサポートされるリージョンは us-central1 と europe-west1 のみとのことです。
東京（asia-northeast1）リージョンで試してみましたが、BigQuery の外部テーブル作成（後述）時にエラーになり、やはり実行できませんでした。
ということで、us-central1 リージョンに Bigtable インスタンスを作成し、senser_latlon という名前のテーブルを作成して緯度経度データを書込みました。
mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ cbt read senser_latlon
2020/12/17 05:21:33 -creds flag unset, will use gcloud credential
----------------------------------------
r1
cf1:lat @ 2020/12/17-05:02:58.865000
"43.06417"
cf1:lon @ 2020/12/17-05:03:09.669000
"141.34694"
----------------------------------------
r10
cf1:lat @ 2020/12/17-05:07:02.032000
"36.39111"
cf1:lon @ 2020/12/17-05:07:12.385000
"139.06083"
----------------------------------------
（省略）
----------------------------------------
r9
cf1:lat @ 2020/12/17-05:06:42.552000
"36.56583"
cf1:lon @ 2020/12/17-05:06:52.904000
"139.88361"47 都道府県の県庁所在地の緯度経度データが Bigtable に格納できました。
BigQuery の外部テーブルを作成
Bigtable のデータを BigQuery から参照するために、US リージョンのデータセットに外部テーブルを作成します。
2020/12 現在、BigQuery 管理コンソールから Bigtable の外部テーブルを作成することはできないので、Cloud Shell から bq コマンドを実行してテーブルを作成します。
以下のテーブル定義ファイルを準備しました。
{
"sourceFormat": "BIGTABLE",
"sourceUris": [
"https://googleapis.com/bigtable/projects/cm-da-mikami-yuki-258308/instances/advent-calendar/tables/senser_latlon"
],
"bigtableOptions": {
"readRowkeyAsString": "true",
"columnFamilies" : [
{
"familyId": "cf1",
"type": "STRING",
"encoding": "TEXT"
}
]
}
}bq mk コマンドでテーブル定義ファイルを指定して実行します。
mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq mk --external_table_definition=latlon.txt dataset_advent_calendar_us.latlon
Table 'cm-da-mikami-yuki-258308:dataset_advent_calendar_us.latlon' successfully created.無事、外部テーブルが作成できました。
ちゃんとデータが参照できるかどうか確認してみます。
テーブルスキーマでフィールドのタイプが RECORD になっていた通り、Bigtable の列ファミリー（ cf1 ）ごとにネスト構造になったデータが取得できました。
必要なのは cf1.column.cell.value に入っている緯度経度情報なので、以下のクエリで取得してみます。
WITH latitude as (
SELECT rowkey, value FROM (
SELECT rowkey, name, cell FROM dataset_advent_calendar_us.latlon, UNNEST(cf1.column)
), UNNEST(cell)
WHERE
name = 'lat'
),
londitude as (
SELECT rowkey, value FROM (
SELECT rowkey, name, cell FROM dataset_advent_calendar_us.latlon, UNNEST(cf1.column)
), UNNEST(cell)
WHERE
name = 'lon'
)
SELECT
CAST(lat.value AS FLOAT64) as lat,
CAST(lon.value AS FLOAT64) as lon
FROM
latitude as lat
INNER JOIN londitude as lon ON lat.rowkey = lon.rowkey
LIMIT 10
緯度経度データも正常に取得できることが確認できました。
BigQuery Geo Viz で緯度経度データを可視化
BigQuery Geo Viz を使うと、ブラウザから GCP の対象プロジェクトを選択しクエリエディタに SQL を入力して実行するだけで、取得した位置情報を地図上にプロット表示することができます。
本シリーズ BigQuery Advent Calendar 2020 の 3 日目の記事でもご紹介しているので、ぜひご覧ください。
BigQuery Geo Viz から実行するのは以下のクエリです。
WITH latitude as (
SELECT rowkey, value FROM (
SELECT rowkey, name, cell FROM dataset_advent_calendar_us.latlon, UNNEST(cf1.column)
), UNNEST(cell)
WHERE
name = 'lat'
),
londitude as (
SELECT rowkey, value FROM (
SELECT rowkey, name, cell FROM dataset_advent_calendar_us.latlon, UNNEST(cf1.column)
), UNNEST(cell)
WHERE
name = 'lon'
)
SELECT
ST_GeogPoint(lon, lat) AS WKT
FROM (
SELECT
CAST(lat.value AS FLOAT64) as lat,
CAST(lon.value AS FLOAT64) as lon
FROM
latitude as lat
INNER JOIN londitude as lon ON lat.rowkey = lon.rowkey
)エディタに SQL を入力して、「Run」ボタンで実行しました。
よくよく目をこらすと地図上に小さな点が表示されているようないないような。。
「Show Results」ボタンで結果を確認みると、ちゃんと 47 都道府県の位置が取得できているようです。
「Add Styles」ボタンから「fillColor」と「circleRadius」の項目で、点の色と大きさを調整します。
「Apply Style」ボタンで変更を適用してみると
今度ははっきりと地図上で位置が確認できるようになりました！
まとめ（所感）
BigQuery の外部テーブル作成時に指定するテーブル定義ファイルの準備に少し手間取りましたが、他は特に迷うことなく、Bigtable のデータを BigQuery から参照することができました。
現在のところ Bigtable データに対するクエリ実行には、管理コンソールからの外部テーブル作成やスキーマの自動検出ができなかったり、リージョン制限があったりと、少し使いにくい印象ですが、
Cloud Shell を使えば gcloud コマンドをすぐに実行できますし、一度やってみるとすぐに慣れると思います。
今後 GCS や Google ドライブファイルへのクエリのように、管理コンソールの UI 操作＆スキーマ自動検出で Bigtable の外部テーブルも作成できるようになるとなおうれしいですねｖ
SQL ということばでひたすらデータと会話する若干地味なデータ分析という作業ですが、ビジュアライズされた結果が見れるとやはりうれしいものだと思います。 BigQuery Geo Viz、初めてさわってみましたが、面倒な設定や準備など一切必要なく、画面から SQL を実行するだけで位置データを直感的に分かりやすく可視化してくれるので、合わせて使えばより便利だと思いました！
明日 21 日目の BigQuery Advent Calendar 2020 はしんやからお送り予定です。 また、今後のアドベントカレンダーの予定はこちらにも掲載しておりますので、引き続きお楽しみいただけますと幸いです。
3
