fbpx
HOW TO TD(User Engagement)Treasure Data User Engagement

ちょっとしたSQL(Presto)チューニング豆知識

ホーム » ちょっとしたSQL(Presto)チューニング豆知識

はじめに

Treasure Data CDPにはSQLエンジンとしてPrestoがあります。
SQLに関してはこちらに記載されております。

本稿ではSQLに正規表現を使用することができる「REGEXP_LIKE」と「(多数の)OR連結」でのデータ抽出の時間を比較します。
尚、ご契約されているTreasure Data CDPの環境により抽出時間は前後いたしますのであくまでも目安であるとご理解いただければと思います。

サンプルSQLの概要

WITH句にて動的に800万件のデータテーブルを作成。
そして下記の2パターンにてデータ抽出を行います。

  1. REGEXP_LIKE
  2. ORを複数個

抽出するデータ件数はともに500行となります。

サンプルソース

抽出対象のデータを一時的に作成するWITH句です。

-- テスト用テーブル(8,000,000件)を動的に作成
WITH tmp AS(
  -- SQLでマッチするパターンのデータ(500件)を作成
  SELECT
    *
  FROM(
    SELECT
      time
      ,'test_data_a_' || cast(ROW_NUMBER() OVER(PARTITION BY time ORDER BY time) AS varchar) AS test_data
    FROM sample_datasets.nasdaq
    LIMIT 500
  )
  UNION ALL
  -- SQLでマッチしないパターンのデータ(7,999,500件)を作成
  SELECT
    *
  FROM(
    SELECT
      time
      ,'test_data_b_' || cast(ROW_NUMBER() OVER(PARTITION BY time ORDER BY time) AS varchar) AS test_data
    FROM sample_datasets.nasdaq
    LIMIT 7999500
  )
)

テーブル内は下記のようなイメージです。

そして、WITH句のテーブルからデータを抽出するSQLです。

1.REGEXP_LIKE

SELECT
  *
FROM tmp
WHERE 
-- 1 REGEXP_LIKEで検索させる * 1.2どちらもマッチする件数はおなじ
REGEXP_LIKE(test_data, 'test_data_a_')

2.ORを複数個

SELECT
  *
FROM tmp
WHERE 
-- 2 orを500個連結させて検索させる  * 1.2どちらもマッチする件数はおなじ
test_data = 'test_data_a_1'
or test_data = 'test_data_a_2'
or test_data = 'test_data_a_3'
or test_data = 'test_data_a_4'
or test_data = 'test_data_a_5'
or test_data = 'test_data_a_6'
or test_data = 'test_data_a_7'
or test_data = 'test_data_a_8'
or test_data = 'test_data_a_9'
or test_data = 'test_data_a_10'
           ・
           ・
           ・
or test_data = 'test_data_a_500'

サンプルSQL実行時間

結果として

1と2の結果としては大きく違いがあることがおわかりいただけたかと思います。抽出対象のScanが完了後に下記のような処理が行われるイメージとなります。

  1. REGEXP_LIKE(正規表現) は1回だけ抽出処理が行われる
  2. ORを複数個の場合、今回は500個(回)抽出処理が行われる

結果としては想定の内容を想像されているかと思われます。一方、業務で利用されているSQLにて多数の「OR」を使用している場合もあるかと思われます。
ちょっとしたTipsではございますが、このようにSQLパフォーマンスに影響する場合がありますので頭の片隅に少しでも入れていただければ幸いです。

おまけ

今回は「ORを500個」使用した場合のサンプルを記載いたしましたが、仮に「ORを500個以上」使用したSQLはどのような結果になるかを簡単に共有いたします。

結果としては下記となります。

「500個までのORは許容されていますがそれ以上はエラー」となります。
そのようなSQLを記載することはまず無いとは思いますがこのような仕様になってますのでご理解いただければと思います。

UserEngagement事務局

ユーザーの皆さまへのお知らせや、Treasure Data UserEngagementのサイト運営を担当する事務局アカウント。
Back to top button