Snowflakeの外部テーブル、完全に理解した

Snowflakeの外部テーブル、よくわかりませんよね。

外部テーブルの概要（公式ドキュメント）

外部テーブルとは、Snowflakeの機能で、 外部ステージ に格納されているデータを、あたかもSnowflakeのテーブル内にあるかのようにクエリすることができます。外部ステージはSnowflakeの一部ではないため、Snowflakeがステージを格納したり、管理したりすることはありません。
外部テーブルを使用すると、ファイル名、バージョン識別子、および関連プロパティを含む特定のファイルレベルのメタデータを（Snowflake内に）格納できます。外部テーブルは、 COPY INTO <テーブル> コマンドがサポートする任意の形式で格納されたデータにアクセスできます。
外部テーブルは読み取り専用です。データ操作言語（DML）の操作は実行できません。ただし、クエリや結合操作に外部テーブルを使用することはできます。また、外部テーブルに対するビューも作成できます。
外部テーブルのデータをクエリすると、Snowflake内のテーブルにネイティブで格納したデータをクエリするよりも遅くなる場合があります。クエリのパフォーマンスを向上させるために、外部テーブルを基にした マテリアライズドビュー を使用することができます。

実体は外部ステージ上のファイルなんだけど、テーブルのようにSelectができるというものと理解しました。
理解しましたと言いつつ実は理解してないので触ってみます。

外部ステージの準備

まずは外部ステージを用意する必要があります。
会社のS3環境にサンプルデータを入れるのはまずそうなので、
「公開されているS3バケットってどこかないかなー」と思ったら、
SnowflakeのQuickStartで使用したことのあるCITIBIKEのS3バケットがありました。
デモとしてSnowflakeの方に見せてもらって、さらに一度自分でやってみたことがあるQuickStartです。

このQuickStartのデータロード部分を、ロードじゃなくて外部テーブルを利用する形で試してみたらいいんじゃなかろうかと。
わかりやすく比較するために
・通常のテーブルにロード
・外部テーブルを利用
の2つやってみます。

どちらでも外部ステージは共通で使用しますので外部ステージを作成します。

外部ステージ作成

TEST_DB.TEST_SCHEMAの中にGUIで外部ステージを作成します。名前はCITIBIKE_STAGEです。
画面ショット内のコメントのところに書いていますが、最後の「/」を忘れないように。（本来はコメント欄に書くことじゃないです。）

外部ステージの確認

作成したらこんな感じでcsvが1つ入っています。
ワークシートに移って、クエリをたたいて確認してみます。

この投稿の時に試した、ディレクトリテーブルのSELECTと、ステージに対するlistの2つを実行しました。

select * from directory(@citibike_stage);

list @citibike_stage;

どちらも1行取得できました。
（なお、外部ステージ作成の際のURLの最後の「/」を忘れると、ディレクトリテーブルのselectだと1件なのにlistコマンドだと12,106件取れてしまい、本題でないところで悩む羽目になります。）
※追記start
↓この投稿で別出しで悩みを書きました。

※追記end

1ファイル存在するのは確認できましたが、まだファイルの中身は見れていません。

さて先ほども書きましたがもう一回書いておきます。
・通常のテーブルにロード
・外部テーブルを利用
の2つやってみます。

通常のテーブルにロード

テーブル作成

ロードするテーブルを作成します。
このクエリはQuickStartのページからのコピペです。

create or replace table trips
(tripduration integer,
starttime timestamp,
stoptime timestamp,
start_station_id integer,
start_station_name string,
start_station_latitude float,
start_station_longitude float,
end_station_id integer,
end_station_name string,
end_station_latitude float,
end_station_longitude float,
bikeid integer,
membership_type string,
usertype string,
birth_year integer,
gender integer);

ファイルフォーマット作成

このクエリもQuickStartのページからのコピペですが名称だけ変えました。

create or replace file format TEST_FILE_FORMAT_CSV type='csv'
  compression = 'auto' field_delimiter = ',' record_delimiter = '\n'
  skip_header = 0 field_optionally_enclosed_by = '\042' trim_space = false
  error_on_column_count_mismatch = false escape = 'none' escape_unenclosed_field = '\134'
  date_format = 'auto' timestamp_format = 'auto' null_if = ('') comment = 'file format for ingesting data for zero to snowflake';

※外部テーブルの方でもこのファイルフォーマットは使用します。

テーブルにロード

このクエリもQuickStartのページからのコピペですが以下2点は変えました。
・外部ステージ名
・ファイルフォーマット名

copy into trips from @citibike_stage file_format=TEST_FILE_FORMAT_CSV PATTERN = '.*csv.*' ;

LOADEDとなってますので取り込まれたようです。
Selectしてみます。

よさそうですね。
自分のSnowflake内のローカルテーブルとして使えるようになりました。

今回は試さないのですが、実業務だとこのデータロード処理を毎日タスクで定時実行するとか、Snowpipeで都度反映する仕組みなどを組み込むことになると思います。

外部テーブルを利用

外部テーブル作成

create external table trips_ext
    with location = @citibike_stage
    file_format = TEST_FILE_FORMAT_CSV;

通常のテーブルをtripsという名前で作りましたので、外部テーブルはtrips_extとしました。

desc table trips_ext;

name	type	kind	null?	default	primary key	unique key	check	expression	comment	policy name	privacy domain
VALUE	VARIANT	COLUMN	Y		N	N			The value of this row

VARIANT型のVALUEというカラムが1つだけあるようです。

外部テーブルを作ったらもうselect可能なのでselectしてみましょう。

VALUEというVARIANT型の1カラムにデータがJSONで入っています。
元はCSVファイルですがJSONになると。

このままだと扱いづらいですね。
見やすくselectします。
外部テーブルはVARIANT型でデータ保持しているので各項目に対してキャストをかけてあげます。
キャストしないと、下記投稿のように値がダブルクォーテーションで囲った形式で出てきます。

上記投稿ではAS_VARCHAR()でキャストしましたが、今回は::varcharでキャストしてみました。

select
  value:c1::integer as tripduration,
  value:c2::timestamp_ntz as starttime,
  value:c3::timestamp_ntz as stoptime,
  value:c4::varchar as start_station_id,
  value:c5::varchar as start_station_name,
  value:c6::double as start_station_latitude,
  value:c7::double as start_station_longitude,
  value:c8::varchar as end_station_id,
  value:c9::varchar as end_station_name,
  value:c10::double as end_station_latitude,
  value:c11::double as end_station_longitude,
  value:c12::varchar as bikeid,
  value:c13::varchar as membership_type,
  value:c14::varchar as usertype,
  value:c15::varchar as birth_year,
  value:c16::varchar as gender
from trips_ext
limit 5;

テーブルのように見ることができました。
これをそのままマテリアライズドビューにしてみましょう。

マテリアライズドビュー化

まずマテリアライズドビューにする理由です。
外部テーブルのselectは、S3にファイルを参照しにいくのでパフォーマンスが良くないです。
パフォーマンスは実テーブルの方が断然いいです。
そこでマテリアライズドビューにすることで、Snowflake内に実データとして持ってきちゃうということをします。
そうすると実テーブルと同様、Snowflake内のデータを見に行くことになるため、パフォーマンスはテーブルを見に行くのと変わりません。
ただし、S3側にファイル変更が発生すると、それをSnowflakeが反映してくれるというサーバーレスのコストがかかってくる点は要注意です。
それから誰かがS3ファイルを消しちゃうとマテビューからもデータが消えます。

ではマテリアライズドビューを作ります。

create or replace materialized view trips_mv as
select
  nullif(value:c1,'')::integer as tripduration,
  nullif(value:c2,'')::timestamp_ntz as starttime,
  nullif(value:c3,'')::timestamp_ntz as stoptime,
  value:c4::varchar as start_station_id,
  value:c5::varchar as start_station_name,
  nullif(value:c6,'')::double as start_station_latitude,
  nullif(value:c7,'')::double as start_station_longitude,
  value:c8::varchar as end_station_id,
  value:c9::varchar as end_station_name,
  nullif(value:c10,'')::double as end_station_latitude,
  nullif(value:c11,'')::double as end_station_longitude,
  value:c12::varchar as bikeid,
  value:c13::varchar as membership_type,
  value:c14::varchar as usertype,
  value:c15::varchar as birth_year,
  value:c16::varchar as gender
from trips_ext;

先ほどselectしたクエリを使ってcreate materialized view しようとするとエラーが出ますので、varchar以外の項目にnullif関数をかましました。

作成したらselectします。

普通のテーブルのようにselectできました。

比較

SELECT * FROM TRIPS;
SELECT * FROM TRIPS_EXT;
SELECT * FROM TRIPS_MV;

• 通常のテーブル：trips
• 外部テーブル:trips_ext
• マテリアライズドビュー：trips_mv
  の3つを同じ条件でSelectしました。（条件も何もselect *ですが。）
  キャッシュを使わないように初めて打つSQLとして大文字で書きました。
  （キャッシュの削除方法がわからなかった。）

↑ 通常のテーブル：tripsは946ms

↑ 外部テーブル:trips_extは2.3s=約2300ms

↑ マテリアライズドビュー：trips_mvは1.0s=約1000ms
という結果になりました。

• 外部テーブルを直接参照は倍の時間がかかるということ
• 通常のテーブルとマテリアライズドビューはほぼ同じパフォーマンスということ
  がこのケースでは観測できました。

まとめ

こんな印象を持ちました。

	メリット	デメリット
通常のテーブルにロード	Snowflake内にデータを持つため、S3側のデータは退避や削除可能	タスクやSnowpipeなどで継続的に取り込むパイプラインの維持管理が必要
外部テーブル＋マテビュー	マテビューが勝手にデータ反映してくれるのでパイプラインの維持管理が不要	S3側のデータを消すとマテビューからもデータが消える／S3側ストレージ料金とSnowflakeマテビューの料金が二重にかかる／VARIANT型からのキャストが面倒

他にもメリットデメリットあればコメントいただけると嬉しいです。