❄️

システム定義関数と自作定義関数(JavaScriptUDF)どちらが速いのか 🤔

2021/12/21に公開

Snowflake

Data Warehouse

tech

この投稿は Snowflake Advent Calendar 2021 の21日目の記事です。

※例により、内容は個人の見解であり所属する組織の見解ではありません。

突然ですが、Snowflakeで同じ結果を出力するSQLを書く場合、システム定義関数と自作定義関数どちらが速いと思いますか？

自分にはわかりませんでした。

大規模データの処理を実行する場合、小さな差でも積もり積もって大きなコスト差になり得ます。

わからなければ実証だ!
ということで、アクセス分析を目的とした実装の場合、「システム定義関数」「自作定義関数(JavaScriptUDF)」どちらが速いか検証してみました。
(先日開催されたSnowDayにてお話させていただいたセッションのセクション:point2.JavaScriptUDF実装部分の検証です cf. https://www.slideshare.net/kazuhiromitsuhashi5/snowflake-snowday-20211208)

◎比較実装

非構造化データ(正規表現抽出)処理+半構造化データ(json)処理

○アクセス分析要件

サーバログ(jsonl)からnginxアクセスログ部分(デフォルトなスペース区切り)のip, request, statusを取り出す。

○システム定義関数のSQL

SELECT ip::text, request::text, status::int
FROM (
  select 
  REGEXP_SUBSTR (nginx:log, '[^ ]+', 1, 1, 'e') AS src,
  REGEXP_SUBSTR (nginx:log,'\\b\\d{1,3}\.\\d{1,3}\.\\d{1,3}\.\\d{1,3}\\b') AS ip,
  REGEXP_SUBSTR (nginx:log,'([\\w:\/]+\\s[+\-]\\d{4})') AS date,
  REGEXP_SUBSTR (nginx:log,'\"((\\S+) (\\S+) (\\S+))\"', 1, 1, 'e') AS request,
  REGEXP_SUBSTR (nginx:log,'(\\d{3}) \\d+', 1, 1, 'e') as status,
  REGEXP_SUBSTR (nginx:log,'\\d{3} (\\d+)', 1, 1, 'e') as size,
  REGEXP_SUBSTR (nginx:log,' \"([^\"]*?)\" ', 1, 2, 'e') as refefer,
  REGEXP_SUBSTR (nginx:log,' \"([^\"]*?)\" ', 1, 4, 'e') as ua
  from weblog_pipe
);

cf.ページ最下部にアクセスログの抽出例。

○自作定義関数(JavaScriptUDF)のSQL

-- execute
SELECT nginx_json:ip::text AS ip, nginx_json:request::text AS request, nginx_json:status::int AS status
FROM (
  select parse_json(parse_nginxlog(nginx:log))::variant as nginx_json
  from weblog_pipe
  );
  
--- create javascriptudf
CREATE OR REPLACE FUNCTION parse_nginxlog(LOG TEXT)
RETURNS TEXT
LANGUAGE JAVASCRIPT
AS $$
  try {
      const [src,loc,ip,time,request,status,size,ref,ua,other] = LOG.match(/^(.+) - (.+) - - \[(.+)\] "([^\"]+)" (\d+) (\d+) "([^\"]+)" "([^\"]+)" (.+)/);
      var m = {'ip':ip,'time':time,'request':request,'status':status }
      return JSON.stringify(m)
  } catch (err) {
      return JSON.stringify("Error: " + err);
  }
$$;

○サーバログ(例：一行分)

サーバログ(例)

○サーバログ蓄積table(半構造データ型)

snowpipeで随時ロードされる実践的な運用を想定。

CREATE TABLE weblog_pipe(
  nginx variant
);

○さて、どちらが速いと思われるでしょうか？

※厳密には同じとは言えない気がしますが、明確な差がついたため細かいところは気にせずにいただけると 🙏

point

システム定義関数のSQL
重そうな正規表現の部分抽出関数が何回も呼ぶ形になっているが処理効率悪くならないのか🤔
自作定義関数(JavaScriptUDF)のSQL
正規表現抽出はサーバログ一行に付き一回の適用、Javascript実行のオーバヘッドが影響するか🤔

結果

「サーバログ(例)」で示した形の本番ログ2億行分(10GB)にて、上記SQLを実行し速度比較しました。

SQLタイプ	Warehouseタイプ	処理時間
自作定義関数(JavaScriptUDF)	XS	12m50s
自作定義関数(JavaScriptUDF)	M	3m20s
システム定義関数	XS	6m20s
システム定義関数	M	1m30s

Warehouseタイプ切り替えつつ複数回実行しましたが、処理時間が約2倍と明確な差がつきました。

さいごに

自分の事前予想では、重そうな正規表現の部分抽出処理が１回で済む自作定義関数(JavascriptUDF)の方が早いかな🤔と思っていましたが逆の結果となりました😓 実証して良かったです。
システム定義関数を使うとマテリアライズドビューも実装できるので、この分析要件の実装は「システム定義関数」が正しい選択ですね。

snowflakeはやはり優秀でした。半構造化データの処理、非構造化データの処理でも、この例のようにシステム定義関数で対応できる場合が多いと思います。システム定義関数は内部で最適に処理されています。

これからは、UDFのような自作関数実装は最後の手段として、先ずsnowflakeに任せる形のシステム定義関数で実装できないかを探す方針で行こうと思いました😃