【Snowflake】SnowflakeにShapfileをロードしてみた

※2023/08/22現在、Issue#1006によりSnowflakeFile関数が利用できなくなっているため、関数を使ったロードができない状態なので注意

概要

GIS(地理情報システム)でよく使われるShapefile形式のデータをSnowflakeへロードする。

なにかいい方法がないかと模索していたら、めちゃくちゃ参考になる記事があったのでこれを元に試してみる。

使うデータは国土数値情報データの行政区域(全国)のデータを用いる。

ロード方法

1. 事前準備

Snowflake上でUDFを作成する際に、Anacondaのライブラリを利用する。

ライブラリを利用するにはSnowflakeサードパーティ規約に同意しなくちゃならない。(参考)

まずはSnowsight上のAdmin >> Billing&Termsの画面にいき、AnacondaのEnableのボタンをクリックする。

すると以下のようなポップアップ画面が出てくるので、「Acknowledge & Continue」をクリックして同意する。

同意が完了すると、画面には「Acknowledge」と表示される。

2. UDTFの作成

他にいい方法が思いつかないので、参考記事に記載のUDTFを少し加工して利用する。

(ほぼコピペだけど。。)

-- DBの作成
CREATE DATABASE SAMPLE_DB;
-- スキーマの作成
CREATE SCHEMA SAMPLE_SCHEMA;

-- UDTFの作成
CREATE OR REPLACE FUNCTION LOAD_SHAPEFILE(path_to_file string, filename string)
RETURNS TABLE (wkb binary, properties object)
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
PACKAGES = ('fiona', 'shapely', 'snowflake-snowpark-python')
HANDLER = 'LoadShapefile'
as $$
from shapely.geometry import shape
from snowflake.snowpark.files import SnowflakeFile
from fiona.io import zipmemoryfile

class LoadShapefile:        
    def process(self, path_to_file: str, filename: str): # ファイル名はzipファイルの中のshpファイル名を指定
     with snowflakefile.open(path_to_file, 'rb') as f:
      with zipmemoryfile(f) as zip:
       with zip.open(filename, encoding='shift-jis') as collection: # エンコードはSHIFT-JISを指定
        for record in collection:
         yield ((shape(record['geometry']).wkb, dict(record['properties'])))
$$;

作成した関数はUDTF(user-defined table function)と呼ばれ、普通のUDFとは少し異なるので注意する。

SnowflakeにおいてUDFには2種類あり、一つはスカラー値をリターンするUDFなるものと、もう一つは表形式の値を返すUDTF(user-defined table function)が存在する。

なお、UDTF作成時に注意すべき点は以下のとおり

  • ハンドラー名はクラス名と一致する必要がある
  • ハンドラークラスが必須
  • processという名前のメソッドが必須

上記が満たされてないとエラーになるみたい。詳細は公式ドキュメントを参照

3.データをロードする

今回用いるデータは50MB以上あるためSnowsight上からではなくS3経由でロードする。

まずはS3バケットにデータを格納する。

Snowflake上で必要なオブジェクトを作成していく。

※S3 Storage Integrationの方法は割愛

USE DATABASE SAMPLE_DB;
USE SCHEMA SAMPLE_SCHEMA;

-- 外部ステージの作成
CREATE OR REPLACE STAGE STG_SAMPLE
STORAGE_INTEGRATION = S3_INT
URL = '' # 自身のS3バケットパス
;

-- テーブルにUDTFを使ってデータをロード
CREATE OR REPLACE TABLE TBL_ADMIN_DISTRICT AS
SELECT PROPERTIES, TO_GEOGRAPHY(WKB, TRUE) AS GEOGRAPHY
FROM TABLE(LOAD_SHAPEFILE(BUILD_SCOPED_FILE_URL(@STAGE_MESH, 'N03-20230101_GML.zip'),'N03-23_230101.shp'))
;

ロードが完了した後、中身を確認してみると

うん、うまくロードできてそう。

ちなみにテーブルサイズは約532MB

試しに空間処理のクエリを投げて確かめてみる。

東京ドームのポイントデータ(緯度経度)がどこの行政区域に位置するかクエリを実行してみる。

SELECT * FROM TBL_ADMIN_DISTRICT T1
WHERE ST_INTERSECTS(ST_MAKEPOINT(139.751994, 35.705493), T1.GEOGRAPHY);

東京ドームは文京区に位置するのでロードしたデータは大丈夫そう。

まとめ

Snowflakeにshapefile形式のデータのロードできるか試してみた。

独自の関数を用意する必要があるが、癖のあるshapfileも読み込めるのはGISデータを扱う人にとってはありがたいのかなと思う。

Snowflakeは地理情報関数も豊富だし、Streamlitと組み合わせて可視化もできるのも嬉しい。

次は可視化を試してみたいな。だれかの参考になれればと思います。