Snowflake NotebooksでGEOGRAPHY/GEOMETRY型を使うときに注意すること

## Snowflake Notebooks

[Snowflake Notebooks](https://docs.snowflake.com/en/user-guide/ui-snowsight/notebooks)はSnowflakeのWeb UIであるSnowSight上で提供されている開発環境です。SQL、Python、Markdownを一つのノート上に混在して記述できるのが特徴です。  
雰囲気的にはJupyter Notebookのようなサービスになっています。  

![Snowflake Notebooks](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917580/6c3b6253-d6a3-0755-a2d4-1fd38048f813.png)

2024/8/4現在、Public Preview (PuPr)中です。  

https://docs.snowflake.com/en/user-guide/ui-snowsight/notebooks

## GEOGRAPHY/GEOMETRY型

GEOGRAPHY/GEOMETRY型はそれぞれSQLで地理空間情報を扱うためのデータ型です。  
GEOGRAPHY型は地球を球体として扱ったもので、GEOMETRY型は平面として扱ったものです。

https://docs.snowflake.com/ja/sql-reference/data-types-geospatial

## なんか変だぞ

他のセルで定義されたGEOGRAPHY/GEOMETRY型のデータに対して地理空間情報を処理するための[地理空間関数](https://docs.snowflake.com/sql-reference/functions-geospatial)(ST_で始まる関数)が使えないことに気付きました。  
なんで…？

![error](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917580/367c3d3f-621a-4aaa-3e36-818cd5f7580c.png)

## 原因を調べてみる

それぞれデータ型を確認してみると、SELECTした直後と他のセルから参照したときで変化していることが分かります。  
SELECTした直後は```VARIANT[LOB]```で、他のセルから参照したときは```OBJECT[LOB]```になっています。  

![typeof](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917580/50deac58-645d-d2e5-5ad3-a55ac9a3a305.png)

どうやら他のセルから参照したときは半構造化データ、つまり単なるJSON Objectになっているようです。  
そのため、キーでアクセスできるようになっています。  

![JSON Object](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917580/971a8a8e-ab68-2599-b9bf-da59dde75983.png)

なお、GEOGRAPHY型は以下のような半構造化データに、

```json
{
  "coordinates": [-122.35, 37.55],
  "type": "Point"
}
```

GEOMETRY型は以下のような半構造化データになっています。  

```json
{
  "coordinates": [-1.223500000000000e+02, 3.755000000000000e+01],
  "type": "Point"
}
```

これらはGeoJSONフォーマットそのままだと思われます。  

## どうすればいいのか

セルを跨ぐたびに```TO_GEOGRAPHY```あるいは```TO_GEOMETRY```で明示的に型変換することで回避可能です。  
それぞれの関数に```<variant_expression>```を与えたのと同じ挙動をするようです。  

https://docs.snowflake.com/sql-reference/functions/to_geography

https://docs.snowflake.com/sql-reference/functions/to_geometry

![success](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917580/959605fb-9330-bfdb-0573-c081eaa0e8d6.png)

## 根本的な原因はRESULT_SCANにあり？

[Snowflake Squad](https://www.snowflake.com/en/snowflake-squad/)[^1]の[前田健太郎さん](https://x.com/rakudeji)に教えていただいたのですが、Snowflake Notebooksのセル結果参照は```RESULT_SCAN```関数で実装されているとのことです。  
「キャッシュを永続化するときに型情報が削除されることはありそう」とのことで、そんな感じはしますよね…ということで簡単に検証してみます。  

[^1]: Snowflake社から技術力や発信力を認められたSnowflakeアンバサダーのこと

SQL Worksheetで以下のようなコードを書いて実行してみます。  

```sql:geo_result_scan.sql
-- とりあえずGEOGRAPHY型のデータを作ってみる
SELECT ST_GEOGRAPHYFROMWKT('POINT(-122.35 37.55)') AS POINT;
-- クエリIDを取得
SELECT LAST_QUERY_ID();
-- 以降、クエリIDが01b61af4-0001-9a90-0000-afcd0026418eだったとする
-- 参照できることを確認
SELECT POINT FROM TABLE(RESULT_SCAN('01b61af4-0001-9a90-0000-afcd0026418e'));
-- 型情報を確認
SELECT SYSTEM$TYPEOF(POINT) FROM TABLE(RESULT_SCAN('01b61af4-0001-9a90-0000-afcd0026418e'));
-- 地理空間関数が使えないことを確認
SELECT ST_CENTROID(POINT) FROM TABLE(RESULT_SCAN('01b61af4-0001-9a90-0000-afcd0026418e'));
-- TO_GEOGRAPHYで明示的に型変換すれば地理空間関数が使えることを確認
SELECT ST_CENTROID(TO_GEOGRAPHY(POINT)) FROM TABLE(RESULT_SCAN('01b61af4-0001-9a90-0000-afcd0026418e'));
-- TO_GEOGRAPHYで明示的に型変換した後の型情報を確認
SELECT SYSTEM$TYPEOF(TO_GEOGRAPHY(POINT)) FROM TABLE(RESULT_SCAN('01b61af4-0001-9a90-0000-afcd0026418e'));
```

同様の挙動を再現することができました。  

![worksheets](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917580/e57787c8-c253-ef0a-7a5d-6124421868c4.png)

## まとめ

Snowflake Notebooksのセル結果参照や```RESULT_SCAN```は一見効率がよさそうに思えるのですが、```RESULT_SCAN```の以下の記載の通り、大規模なデータを扱うときには性能的に不利になることもあります。  

> 結果セットにはメタデータが関連付けられていないため、大きな結果の処理は、実際のテーブルをクエリする場合よりも遅くなる可能性があります。

https://docs.snowflake.com/ja/sql-reference/functions/result_scan

Notebook系のサービスを使うときにデータ全体から使うデータを徐々に絞り込んでいきたいシーンがしばしばありますが、共通テーブル式(CTE)などでクエリそのものを修正していくことが有効なこともあるので、挙動を理解して柔軟に対応していきましょう！  
もちろん、可読性が損なわれなければ都度```TO_GEOGRAPHY```あるいは```TO_GEOMETRY```を呼び出すのもありだと思います。