Snowflake の外部ステージからの COPY INTO が遅かった話

Last updated at 2023-10-13Posted at 2023-10-12

Snowflake の COPY INTO クエリが遅かったが、書き方を変えたら速くなった話。

背景

Amazon S3 に以下のような構造でデータがあり、これを Snowflake に取り込みたい。

s3://my-bucket/
  ├ foo/
  │   ├ aaa.csv.gz
  │   ├   :
  │   └ zzz.csv.gz
  ├ bar/
  │   ├ aaa.csv.gz
  │   ├   :
  │   └ zzz.csv.gz
  :
  (略)

foo/ bar/ などの各ブロックごとに数十万ファイルあり、S3 バケット全体では数百万ファイルある。

Snowflake の INTEGRATION や STAGE などの設定は済ませた上で、下記のようなクエリを実行すればテーブル foo にデータを取り込むことができる。

COPY INTO foo FROM @my_stage pattern='foo/.+[.]csv[.]gz';

が、クエリ実行がなんだか遅い。

様子を見ていると「Listing external files...」だけで20分以上かかっている。
ファイル一覧を列挙するだけなのにそんなに時間かかる？

pattern='foo/.+[.]csv[.]gz' と書けばよしなに foo/ 以下だけを見てくれるものと思い込んでいたのだけど、どうやらそんなことはなくて、@my_stage 全体のファイル一覧を取得した上で pattern でフィルタリングをしている模様...。そりゃあ遅い。

最高のパフォーマンスを得るには、多数のファイルをフィルターするパターンを適用しないようにしてください。

一括データロード操作は、 FROM 句の保存場所全体に正規表現を適用します。

ドキュメントにも書かれていた。

FROM 句にパスを指定して絞り込むようにしたところ、爆速になった。

COPY INTO foo FROM @my_stage/foo/ pattern='.+/.+[.]csv[.]gz';

ファイル一覧を取得する時間だけを計測してみると、これくらい爆速になった。

LIST @my_stage pattern='foo/.+[.]csv[.]gz';

→ 24分

LIST @my_stage/foo/ pattern='.+/.+[.]csv[.]gz';

→ 2秒