Snowflakeの地図データをSupersetで地図に表示させてみた

こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。

先日「Apache Superset」の設定を見直し、地図表示がきちんとできるようになりました。

調べてみると、Apache Supersetでは、地図データはWKTがレコードにあれば表示できるような記述を見かけたので、Snowflakeに地図データを登録して地図表示を試してみたいと思います。

前提

環境は以下で試しています。

• OS
  • macOS Catalina
• Docker Desktop
  • 2.3.0.3

また、下記エントリのようにして、一通りのSupersetのセットアップは実施済みとなっており、Snowflakeへの接続や、基本的な地図表示もできる状態となっています。

地図表示用のデータをロードする

では、まずは地図表示用のデータをSnowflakeへ登録したいと思います。

なにか良いものがないかと探していたところ、下記のブログ記事を見つけました。

この記事では、スピードテストで有名なOoklaの公開データをSnowflakeに登録し、それを元データとして可視化する解説がされていました。

今回、可視化はSupersetで行いますが、途中までは同じ手順で良さそうなのでこちらを参考にすすめてみます。

Ooklaのデータについて

データは下記の記事に記載のとおりですが、モバイルまたは固定回線からのスピードテストの結果データをオープンデータとして非商用利用向け（CC BY-NC-SA 4.0 ライセンス）に公開されているものです。

データはRegistry of Open Data on AWSで公開されており、S3バケットにアクセスすることで参照できます。今回は、ブログ記事にかかれている通り、このS3バケットに対して「外部ステージ」を定義し、S3上のparquetデータをテーブルにCOPYコマンドで登録していきます。

Snowflakeへのデータ登録

ということで、実際にSnowflakeへ以下のクエリを発行してデータ登録していきます。

-- PUBLICスキーマを指定。DBやウェアハウス、ロールも事前に指定。
USE SCHEMA PUBLIC;

-- ステージを作成
CREATE OR REPLACE STAGE ookla url = 's3://ookla-open-data/';

-- 登録先テーブルを作成
CREATE OR REPLACE TABLE OOKLA (
  avg_d_kbps INTEGER,
  avg_u_kpbs INTEGER,
  avg_lat_ms INTEGER,
  tests      INTEGER,
  devices    INTEGER,
  quadkey    VARCHAR,
  tile       GEOGRAPHY
);

-- parquetデータをCOPY
COPY INTO OOKLA FROM (
  SELECT $1:avg_d_kbps::INTEGER,
         $1:avg_u_kpbs::INTEGER,
         $1:avg_lat_ms::INTEGER,
         $1:tests::INTEGER,
         $1:devices::INTEGER,
         $1:quadkey::VARCHAR,
         ST_GEOGRAPHYFROMWKT($1:tile::VARCHAR)
    FROM @ookla/parquet/performance/type=mobile/year=2020/quarter=4/2020-10-01_performance_mobile_tiles.parquet
)
FILE_FORMAT = (TYPE=PARQUET COMPRESSION=SNAPPY);

今回はCOPY対象のデータは2020年第4四半期の「2020-10-01_performance_mobile_tiles.parquet」としました。COPYにはSサイズのウェアハウスで2分半程度かかり、テーブルとしてのデータ量は約420万件（750MB）程度のデータでした。

Supersetで地図データを表示してみる…が

あとは、Supsersetで該当データベース、テーブルへの「Connection」、「Dataset」を作成して「Chart」を作成すれば良いだけだと思ったのですが、「Dataset」の作成時にError while saving dataset:というエラーが起きてしまいました。

一旦、メニューの「SQL Lab > SQL Editor」から調べてみようとしましたが、こちらでもAn error occurred while fetching table metadataというエラーが発生しており、どうも該当テーブルのあるスキーマのテーブルにおいては、メタデータの取得時になんらかの問題が生じているようです。

なお、Snowflake側のクエリ履歴には下記のクエリが実行された履歴があり、特にクエリ自体は問題なく実行できていました。

SELECT /* sqlalchemy:_get_schema_columns */
                   ic.table_name,
                   ic.column_name,
                   ic.data_type,
                   ic.character_maximum_length,
                   ic.numeric_precision,
                   ic.numeric_scale,
                   ic.is_nullable,
                   ic.column_default,
                   ic.is_identity,
                   ic.comment
              FROM information_schema.columns ic
             WHERE ic.table_schema='PUBLIC'
             ORDER BY ic.ordinal_position

明確なエラーログが確認できなかったのですが、どうもGEOGRAPHY型がエラーを引き起こしていそうということが推察されたので、試しに該当カラムであるtileカラムの型をVARCHAR型にしたところ、問題なく動きました。

つまり、2021/02/20現在では「SQL Lab > SQL Editor」で利用したスキーマの配下に1つでもGEOGRAPHY型のカラムが存在するテーブルがあるとエラーになってしまうようです。

Snowflakeへのデータ登録（やり直し）

ということで、Snowflakeへのデータ登録をやり直します。

非常に残念ですが、GEOGRAPHY型を利用するとエラーになってしまうことがわかったので、VARCHAR型に変更します。

また、Supersetの「CHART」の方を事前に調べたところ、WKTではなくGeoHashにしておいたほうが良さそうなことに気づきました。加えて、この元データは「ポリゴン」なのですが、どうも「ポイント」にしてGeoHashに変換しておいたほうが良さそうだったので、COPY時に合わせて変換し、更に日本の範囲に収まるようにデータを絞り込んでビュー化もしておきます。

下記クエリを実行し、データ登録をやり直します。

-- PUBLICスキーマを指定。DBやウェアハウス、ロールも事前に指定。
USE SCHEMA PUBLIC;

-- ステージを作成
CREATE OR REPLACE STAGE ookla url = 's3://ookla-open-data/';

-- 登録先テーブルを作成
CREATE OR REPLACE TABLE OOKLA (
  avg_d_kbps INTEGER,
  avg_u_kpbs INTEGER,
  avg_lat_ms INTEGER,
  tests      INTEGER,
  devices    INTEGER,
  quadkey    VARCHAR,
  tile       VARCHAR -- ←やむなくVARCHARへ
);

-- parquetデータをCOPY
COPY INTO OOKLA FROM (
  SELECT $1:avg_d_kbps::INTEGER,
         $1:avg_u_kpbs::INTEGER,
         $1:avg_lat_ms::INTEGER,
         $1:tests::INTEGER,
         $1:devices::INTEGER,
         $1:quadkey::VARCHAR,
         ST_GEOHASH(ST_CENTROID(ST_GEOGRAPHYFROMWKT($1:tile::VARCHAR))) -- ←GeoHashとしてVARCHAR型カラムへ登録
    FROM @ookla/parquet/performance/type=mobile/year=2020/quarter=4/2020-10-01_performance_mobile_tiles.parquet
)
FILE_FORMAT = (TYPE=PARQUET COMPRESSION=SNAPPY);

-- おおむね日本の範囲に収まるデータでビュー化
CREATE OR REPLACE VIEW OOKLA_VIEW AS
SELECT
  avg_d_kbps,
  avg_u_kpbs,
  avg_lat_ms,
  tests,
  devices,
  quadkey,
  tile
FROM
  OOKLA
WHERE
  ST_COVEREDBY( -- ST_COVERDBY を利用して、概ね日本の範囲のポリゴンでフィルタリング
    ST_GEOGFROMGEOHASH(tile),
    ST_GEOGRAPHYFROMWKT(
      'POLYGON((129.52536923721246 30.05110490208322,
                145.88316101977628 29.129237442025254,
                146.38989739314013 45.85586174372309,
                138.6440699717214 45.55253207846691,
                128.3283652282432 33.39718626663211,
                129.52536923721246 30.05110490208322 
              ))'
    )
  )
;

今度こそSupersetで地図データを表示してみる

再度、Supsersetで該当データベース、テーブルへの「Connection」、「Dataset」を作成してみましたが、今回は何も問題なく進みました！

さっそく「Chart」を作成しましょう。今回のデータは「deck.gl Screen Grid」が合っていそうなので、これを選択して設定してみます。

LONGITUDE & LATITUDE

これは緯度経度なので、tileカラムをGeohashとして設定します。

WEIGHT

Grid表示時の色の濃淡をここで設定できます。今回は「回線速度」によって色を変えたいので、以下のようにMAX(avg_d_kbps)としました。

ROW LIMIT

取得する件数の上限です。5万件としました。

MAP STYPE

これはお好みですね。今回はDarkを選択しています。

プレビュー

画面上部の「RUN」をクリックしてプレビューすると、良い感じに表示されています。

5万件のうち、一番良い値は1,179,866 kbpsですね。大体150 MB/sぐらいでしょうか。元データはモバイルのデータなので、かなり早いですね。

保存

特に問題ないので、画面上部の「SAVE」から名前をつけて保存しておきます。こうしてみると、やはり都内は回線速度が早いですね。

まとめ

以上、Snowflakeの地図データをSupersetで地図に表示させてみました。残念ながらいまのところGEOGRAPHY型には対応出来ていないということも分かりましたが、将来的に対応されたら嬉しいですね。

一方で、うまく扱えるように事前に変換しておけば、地図表示もうまくできることがわかったのでよかったです！

どなたかのお役に立てば幸いです。それでは！