データエンジニア部の城内です。
今回の記事では、SnowflakeにParquetファイル形式のデータをロードしてくる時、StageからロードするよりExternal Tableからロードした方が早くなるかを検証します。
Parquetファイル形式を扱う External Table はベクトル化スキャナーと呼ばれる仕組みによりクエリのパフォーマンスが高くなるよう最適化されています。これは2021年1月のリリースノートに導入済み機能としてひっそりと情報公開されていました。

参考資料
https://docs.snowflake.com/ja/release-notes/2021-01.html#external-tables-general-availability

External TableとStageについて

External Tableとは、データを見る時に毎回Snowflake外にあるファイルを見に行くテーブルです。ロードする時間がかかる一方、ストレージ代がかからないテーブルになります。
StageとはSnowflake外にあるファイルの場所を指定するオブジェクトであり、ロードをする時に使用します。ファイルを読み込む時に権限が必要になる場合、Integrationも合わせて使うことがあります。

ロードに使用するTable、External Table、Integration、Stageの用意

今回使用するデータはSnowflakeの練習でもよく使われるcitibike-tripsになります。
citibike-tripsはcsvデータなため、一回snowflakeにロードし、parquetファイルにしてAWSのS3にアンロードしてからロード速度を比較します。
それではロード速度を比較するためのオブジェクトを作成していきます。

まず、citibike-tripsデータを入れる先のテーブルtripsを作成します。

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
);

次に、citibike-tripsデータが置いてあるS3のStageを作成し、tripsテーブルにロードします。

create or replace stage citibike_trips
url= 's3://snowflake-workshop-lab/citibike-trips';

copy into trips from @citibike_trips
file_format = (
  type = 'csv'
  field_optionally_enclosed_by = '"'
  null_if = '');

そして、parquet形式に変換したcitibike-tripsデータを置く先であるS3のStageとそこにアクセスするために必要な情報を置く場所となるIntegrationを作成し、データをS3にアンロードします。

AWSのRoleとPolicyの設定には以下のリンクをご参照ください。
https://docs.snowflake.com/ja/user-guide/data-load-s3-config-storage-integration.html

create storage integration test_storage_integration
type = external_stage
storage_provider = s3
storage_aws_role_arn = 'arn:aws:iam::1234567891011:role/snowflake_role'
enabled = true
storage_allowed_locations = ('s3://test);

create stage s3_test
url = 's3://test/'
storage_integration = test_storage_integration;

copy into @s3_test/trips/ from trips 
file_format = (type=parquet)
header=true
max_file_size = 262144000;

最後にExternal Tableを用意します。

create or replace external table external_trips
with location = @s3_test/trips/
file_format = (type = parquet);

StageとExternal Tableのロード速度の比較
それではStageとExternal Tableから実際にロードしてみて、速度を比較してみます。

copy into trips(
  tripduration , 
  starttime , 
  stoptime , 
  start_station_id , 
  start_station_name , 
  start_station_latitude , 
  start_station_longitude , 
  end_station_id , 
  end_station_name , 
  end_station_latitude , 
  end_station_longitude , 
  bikeid , 
  membership_type , 
  usertype , 
  birth_year , 
  gender 
)
from(
select 
  $1:TRIPDURATION::NUMBER(38,0), 
  $1:STARTTIME::TIMESTAMP, 
  $1:STOPTIME::TIMESTAMP, 
  $1:START_STATION_ID::NUMBER(38,0), 
  $1:START_STATION_NAME::VARCHAR(16777216), 
  $1:START_STATION_LATITUDE::FLOAT, 
  $1:START_STATION_LONGITUDE::FLOAT, 
  $1:END_STATION_ID::NUMBER(38,0), 
  $1:END_STATION_NAME::VARCHAR(16777216), 
  $1:END_STATION_LATITUDE::FLOAT, 
  $1:END_STATION_LONGITUDE::FLOAT, 
  $1:BIKEID::NUMBER(38,0), 
  $1:MEMBERSHIP_TYPE::VARCHAR(16777216), 
  $1:USER_TYPE::VARCHAR(16777216),
  $1:BIRTH_YEAR::NUMBER(38,0),
  $1:GENDER::NUMBER(38,0)
from
  @s3_test/trips/)
file_format = (type=parquet);


insert into trips(
  tripduration , 
  starttime , 
  stoptime , 
  start_station_id , 
  start_station_name , 
  start_station_latitude , 
  start_station_longitude , 
  end_station_id , 
  end_station_name , 
  end_station_latitude , 
  end_station_longitude , 
  bikeid , 
  membership_type , 
  usertype , 
  birth_year , 
  gender 
)
select
  $1:TRIPDURATION::NUMBER(38,0), 
  $1:STARTTIME::TIMESTAMP, 
  $1:STOPTIME::TIMESTAMP, 
  $1:START_STATION_ID::NUMBER(38,0), 
  $1:START_STATION_NAME::VARCHAR(16777216), 
  $1:START_STATION_LATITUDE::FLOAT, 
  $1:START_STATION_LONGITUDE::FLOAT, 
  $1:END_STATION_ID::NUMBER(38,0), 
  $1:END_STATION_NAME::VARCHAR(16777216), 
  $1:END_STATION_LATITUDE::FLOAT, 
  $1:END_STATION_LONGITUDE::FLOAT, 
  $1:BIKEID::NUMBER(38,0), 
  $1:MEMBERSHIP_TYPE::VARCHAR(16777216), 
  $1:USER_TYPE::VARCHAR(16777216),
  $1:BIRTH_YEAR::NUMBER(38,0),
  $1:GENDER::NUMBER(38,0)
from
  external_trips;

それぞれの速度の結果は以下になりました。
Stage：1m38s
External Table：34s

External Tableの方が早くなっています。今回は速度が1/3になるという結果が出ていますが、TBサイズのデータで試したところ、大体3/4ぐらいになりました。
今回はS3へデータを置いてからExternal Tableを作成したのですが、External Tableを作成した後にS3へデータを置くと`ALTER REFLESH`を実行してS3においてあるファイルをS3に認識させる必要があります。今回のデータでの`ALTER REFLESH`の速度は1.4sになりました。

最後に

今回の記事では、SnowflakeにParquetファイル形式のデータをロードする時、StageからロードするよりExternal Tableからロードした方が早くなるかを検証しました。
その結果、External Tableの方が早いという結果になりました。
Snowflakeへのデータのロードについてご相談がございましたら、DATUM STUDIOまでお気軽にご連絡ください。