AlteryxでGoogle BigQueryのGeography型にデータをロードする

今回は、AlteryxからGoogle BigQueryに対して空間データをロードすることについて書いてみたいと思います。わざわざ書かなければいけない、ということはデータ出力ツールでさくっと保存できない、ということです。

結論として、Alteryx中心で扱うなら、Alteryxの空間型をセレクトツールでそのまま文字列型（V_WString）に変更して、BigQueryに文字列として保存するのが一番楽です。AlteryxでBigQueryから読み込む場合は、セレクトツールで再度空間型に変えるだけです。

しかしながら、Looker Studioなどで可視化しようとすると、Geography型を要求されます。ということで、Geography型についても考えていきましょう。

Contents

Alteryx中心でしか使わない場合
Geography型にロードする場合
1. SQLで直接書き込む
2. コマンドラインツールで流し込む
WKT型をAlteryxの空間型に変換する方法
まとめ
サンプルワークフローダウンロード

Alteryx中心でしか使わない場合

はじめに書いたように、セレクトツールで変換するだけで入力も出力も事足ります。テキスト形式としてBigQueryに保存し、ロード後もテキストをそのままセレクトツールで空間型に変換して使うことができます。

これにより以下のようにテーブルが作成されます。SpatialObjといいつつテキスト（中身はGeoJSON）で保存されています。

次は、逆にDesignerに読み込んでみましょう。

テスト用に作ったポリゴンが雑ですみません・・・が、正しく読み込めています。

Geography型にロードする場合

この場合、色々と工夫が必要です。まず最初に、データ出力ツールを使ってそのままAlteryxからBigQueryへ空間型をGeography型で保存することはできません。

結論としては、bgのコマンドラインツールを使ってCSVファイル経由でBigQueryに保存するか、SQLで直接流し込むかの二択になります。

また、BigQueryのGeography型はWKTとして保存されているため、Alteryxで読み込んだ場合どうやって空間型に復元するか、という問題も考えなければなりません。

SQLで直接書き込む

AlteryxでSQLを使って書き込みを行いたい場合は、PreSQLの機能を使う必要があります。しかも、書き込みではありますが、データ出力ツールではなく、データ入力ツールを使います。この方法は、「WeeklyAlteryxTips#21　PostgreSQLでストアドプロシージャを呼び出す」で紹介した方法（PreSQLを使う）を使います。

また、SQLを自力で組み立てないといけないので、若干SQLを作るまでが大変です（しかも、どんなテーブルが来ても対応できるように作りたいので、汎用的に作る、ということになると思います）。

とはいえ、SQLさえわかればそれほどハードルは高くありません。基本的には、以下のようなINSERT文を作ります（MySQLのBulk Insertみたいに複数レコードを一気にSQL１文で投入可能です）。ちなみに、SQLでST_GEOGFROMGEOJSON関数を使うことで、ロード時にGeography型に格納できるように変換してくれます。ここがポイントでしょうか。

INSERT `YOUR_PROJECT_NAME.YOUR_DB_NAME.YOUR_TABLE_NAME` (RecordID,geometry,Label)
VALUES(3,ST_GEOGFROMGEOJSON('{"type":"Point", "coordinates":[139.746137,35.662561]}'),'Point1'),
      (3,ST_GEOGFROMGEOJSON('{"type":"Point", "coordinates":[139.746137,35.662561]}'),'Point2');

頭にフィールド名をカッコで入れておき、そのあとVALUESで各レコードが丸括弧内にカンマ区切りで入ったものが、さらにカンマ区切りで続きます。基本的にはそれほど難しくないデータ加工です（といいつつも、転置や集計ツール内でグループ化などを行っているので中級レベルといったところでしょうか）。

なお、BQへのロード部分は以下のようなマクロとしています。これはPreSQLを使ったSQL実行のテクニックを使っています。また、DCMで接続しているため、「接続ID」を書き換えるようにしています。実際にこれを使用する場合は、接続IDをconnection ID欄に入力しておく必要があります。

DCMで接続した場合は以下のように接続IDが出るのでこれをコピーして再利用します（各実行するPCでDCM接続してこの接続IDがPC内に残るようにしておく必要があります）。右クリックでなにげにコピーできます。

最終的なワークフローは以下の通りです。汎用的に作っているのと、一つのSQLで一度にロードするレコード数を制限するように作っているため、少し冗長な作りになっています。

コマンドラインツールで流し込む

データ量が多い場合はSQLで流し込むよりファイルベースで流し込む方が早いでしょう。この場合はコマンドラインツールを使うことになります。

基本的には、ドキュメントの「地理空間データの操作」を見ながら作ることになります。

GCPのコマンドラインツールは、gcloud CLIです。インストールはこちらを参照です。特にハマるポイントもないと思うので、これについては細かくは説明しません。

いくつかBQにGeography型のデータをロードする方法があるようにドキュメントにかかれていますが、色々と制限が多かったです。結局いろいろ試したあげく、できた方法についてここでは紹介します。結論としては「改行区切りの GeoJSON ファイルの読み込み」で行いました。「GeoJSON ジオメトリデータの読み込み」も試しましたが、うまくいきませんでした（理由は不明です）。

これを行うにはいくつかステップがあります。

データを行ごとのGeoJSONに変換し、ファイルに保存する
gcloudコマンドラインツールを使ってBQにファイルをロードする

前準備（前提条件）

空間フィールドは１つしか持てない（はず）
BigQuery側で、ロードするテーブルをあらかじめ作成しておく
BigQuery側の空間フィールドの名前は「geometry」で固定（ここで若干ハマりました）
BigQueryを操作できるサービスアカウントを作成し、JSON形式のキーファイルをダウンロードしておく

データを行ごとのGeoJSONに変換し、ファイルに保存する

通常のGeoJSONファイルは、最初に”FeatureCollection”で始まるようになっていますが、この部分は必要ありません。以下のようなJSON部分が１行になっているデータが必要になります（最後のカンマは不要です）。

{ "type": "Feature",
      "geometry": {"type":"Point", "coordinates":[139.746137,35.662561]},
      "properties": {"RecordID":3,"Label":"Point1","No":1}
      },

つまり、以下のようになります。

{ "type": "Feature","geometry": {"type":"Point", "coordinates":[139.746137,35.662561]},"properties": {"RecordID":3,"Label":"Point1"}}

順を追って説明すると、最初のデータは以下の通りとします。

これを以下のように変形します。

実は、空間データをセレクトツールでテキストに変換し、空間データ以外はpropatiesという形で空間データのJSON内に格納してあげる、ということになります。これは単純なパースで実現できます。

ワークフローとしては以下のとおりです。

ある程度汎用的に動くようにしているため、若干冗長なワークフローになっている部分があります。

gcloudコマンドラインツールを使ってBQにファイルをロードする

次にファイルをBQにロードしましょう。これにはコマンドラインツールを使います。ちなみに、認証部分とBQアップロード部分は別のバッチファイルで対応しています。これは、一つのバッチファイルにまとめた状態だと、認証部分で止まってその先に行かない状況だったからです。ただ、バッチファイルをわけても認証自体は生きているので問題ない、ということになります（まぁ、確かにこれはこれで楽なんですが、APIなどだと認証情報をそのまま使い回すので若干違和感ありますね・・・）。

認証用のコマンドは以下の形でいけました。

gcloud auth activate-service-account --key-file="C:\myprojectname.json" --project=myprojectname

これをするには、任意の場所（ここではC:\myprojectname.json）にサービスアカウントでBQの操作ができるサービスアカウントを作成し、キーファイルをJSON形式でダウンロードしておく必要があります。また、プロジェクト名も控えて置きましょう。

次に、BQのロード用のコマンドです。

bq load --source_format=NEWLINE_DELIMITED_JSON  --json_extension=GEOJSON yourdbname.yourtablename "C:\MyDoc\AYX\GoogleBQ\Geography\json.csv"

一つ前の手順で作成したファイルを任意の場所においておき、これを指定します（ここでは、C:\MyDoc\AYX\GoogleBQ\Geography\json.csv）。また、ロード先のDB名、テーブル名（ここではyourdbname.yourtablename）もコマンドに含める必要があります。

これらをコマンド実行ツールを使って実行します。ワークフローとしては以下のような感じです。

最後に、この2つをまとめたワークフローを作れば完成です。

BQへのロードはマクロにしています。というよりむしろ、全体的にマクロにすべき内容ですね・・・。

WKT型をAlteryxの空間型に変換する方法

Alteryxの空間型に必要な情報はすべてWKT内にあるため、パースしていくだけです（が、結構ややこしいです）。

基本的に、TypeとCoordinatesの２つの項目を持っており、所定の形式に変換するだけです。

WKTの構造

Typeの文字列が最初にきて、丸カッコ内に各ポイントの経度、緯度の組み合わせを持つだけです。緯度経度は空白区切り、ポイントの組み合わせはカンマ区切りで保持します。例えば、一番単純なポイントデータであれば、以下のようになります。

POINT(139.746137 35.662561)

Type文字列は「POINT」で、ポイントデータは一つだけの空白区切りの緯度経度を持つ、という感じです。

ポリゴンの場合は、

POLYGON((139.747639 35.654995, 139.744978 35.651019, 139.749613 35.649206, 139.747639 35.654995))

です。これは3点で構成されたポリゴンとなり、Type文字列は「POLYGON」で、内部に空白区切りの緯度経度のセットを3つ持っています。それぞれ緯度経度のセットはカンマ区切りです。ただし、TYPE文字列以降の実際のデータ部分は2重のカッコで囲まれています。

Alteryxの空間型の構造

一方、Alteryxの空間型、すなわちGeoJSONの場合は、以下のようになります。

ポイントデータは、

{"type":"Point", "coordinates":[139.746137,35.662561]}

です。データの持ち方が変わるだけで、データそのものは何も変わっていません。緯度経度をカンマ区切りで持ち、カギカッコで囲んでいます。Type文字列と緯度経度セットは持ち方がJSONライクになっているだけです。

ポリゴンは、

{"type":"Polygon", "coordinates":[[[139.747639,35.654995], [139.744978,35.651019], [139.749613,35.649206], [139.747639,35.654995]]]}

となっており、基本的には区切り記号が異なるのと、緯度経度の各セットはカギカッコで囲まれている、というのが大きな違いです。そして、緯度経度セットはカギカッコが一つ多いです。また、全体は{}で囲まれています。

どうやって変換するか

とにかく正規表現、置換など駆使すればオッケーです。最後に、セレクトツールで空間型に変換します。

がんばればフォーミュラツール１つでなんとかなるレベルです。元々は細かくフォーミュラを使っていたのですが、一つにまとめてみました。

'{"type":"'+TitleCase(REGEX_Replace([SpatialObj], "([A-Z]+)(.*)", "$1"))+'", "coordinates":'
+
IF REGEX_Replace([SpatialObj], "([A-Z]+)(.*)", "$1")="POINT" THEN Replace(Replace(Replace(Replace(Replace(Replace(Replace(REGEX_Replace([SpatialObj], "([A-Z]+)(.*)", "$2"), ", ", "_"), " ", ","),"_",", "), "), (", "], ["), ", ", "], ["), "(", "["), ")", "]")
ELSE
"["+Replace(Replace(Replace(Replace(Replace(Replace(Replace(REGEX_Replace([SpatialObj], "([A-Z]+)(.*)", "$2"), ", ", "_"), " ", ","),"_",", "), "), (", "], ["), ", ", "], ["), "(", "["), ")", "]")+"]"
ENDIF
+'}'

何かあった際、このフォーミュラ文をデバッグするのは面倒なので、細かく式をわけた状態のままで良いと思います。

まとめ

AlteryxとGoogle BigQueryを組み合わせて使う場合、空間データはテキスト型で扱うと楽です
他のツールやソリューション（例えばLooker Studio）などと組み合わせるためにBQのGeography型を使いたい場合は、SQLでロードするかコマンドラインツール経由でファイルをロードする必要があります
BQのGeography型に格納したデータは、Alteryxで読み込んだ場合、WTKからGeoJSONに変換することで空間型に戻せます

元々Looker Studioで空間データを可視化するために調べ始めた話ですが、思ったよりも大変でした・・・。