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

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

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

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

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みたいに複数レコードを一気にSQL1文で投入可能です)。ちなみに、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 ジオメトリ データの読み込み」も試しましたが、うまくいきませんでした(理由は不明です)。

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

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

前準備(前提条件)

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

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

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

{ "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の2つの項目を持っており、所定の形式に変換するだけです。

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]]]}

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

どうやって変換するか

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

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

'{"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で空間データを可視化するために調べ始めた話ですが、思ったよりも大変でした・・・。

サンプルワークフローダウンロード

今回のワークフローはデータベースへの接続部分を含むため、各自で環境を事前に準備し、ワークフローの該当部分を書き換えて利用してください。

SQLで直接書き込む

緑色のテキストボックスに従って初期セットアップを済ませてください。

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

緑色のテキストボックスに従って初期セットアップを済ませてください。また、GoogleのCLIツールも必要になります。

WTKからSpatial型に変換する

コメント

タイトルとURLをコピーしました