簡単にTreasure Data CDPのクエリ(Presto/Hive)の動作確認をする方法

2021年4月14日

Presto、Hiveとは

Treasure Data CDPでは、格納されているデータから条件を指定して必要なデータのみ取得する際にSQL(Structured Query Language)を利用することができます。その際に、クエリエンジンとしてPrestoとHiveの2種類を利用することができます。それぞれメリット・デメリットがあるため、ケースによって使い分ける必要があります(今回は特性の違いなどは説明しません)。

どのようにしてテストデータを用意する？

Treasure Data CDPでSQLを利用するにあたり、テーブルの件数を確認する際に利用するCOUNT関数や、対象カラムの合計を算出するSUM関数などは他のRDBMSなどでも利用できるため知っている方も多いかと思います。これらのスタンダードな関数以外にもPresto、Hiveにはそれぞれ便利な関数が実装されており、OSSのPresto/Hiveには実装されていないTD固有の関数もTreasure Data CDPでは利用することができます。ドキュメントを見ることで各関数の挙動を知ることができますが、本番運用前に実際に動作させて確認する方がほとんどではないかと思います。

その際に面倒な点として「テストデータを用意する」ことが挙げられます。数行のレコードで十分なケースでも空のテーブルを作成してINSERTしたり、Data Connectorでcsvファイルをインポートしてテストデータを投入するという作業は非常に面倒だと思います。また、運用の観点でも関数動作確認用のテーブルが増えていくことは良いとは言えません(テスト完了後に削除するとしても)。

そこで、今回はテストテーブルを作成しなくても簡単に動作確認できる方法を紹介していきます。

テストデータが1行で十分な場合

例えばPrestoのSPLIT関数の動作確認をしたいとしましょう。ドキュメントには

Splits string on delimiter and returns an array.

と書いてありますが、本番用のクエリにいきなり使うのは不安なので動作確認しておきたいですよね。通常であればテストテーブル(table_a)を作成し、テストデータを投入(INSERT文やData Connectorなどで)してから下記クエリを実行することになります。

SELECT SPLIT(col1, ',') as split_col
  FROM table_a
 WHERE col2=100;

これでも良いのですが、関数の挙動確認のためにテーブルを新規作成するのは過剰かと思います。そこで下記のようにカラム名を記載する箇所に文字列を直接入力して動作確認しましょう。FROM句は必須だと思い込みがちですがなくても動作します。下記クエリはHiveでも動作するためよろしければお試しください。

SELECT SPLIT('aaaa,bbbb,cccc,dddd', ',') as split_col;

複数行のテストデータが必要な場合

いよいよ本丸です。集計するクエリの動作確認をしたい場合は1行ではテストになりません。そのため複数行のテストデータを用意する必要があるのですが、そういったケースでテストテーブルを作成せずに実現できる方法を紹介します。

Prestoの場合

Prestoの場合はVALUESを利用します。VALUESの後ろに(1行目), (2行目), …, (n行目)という形でデータを記載し、ASを用いてカラム名を定義します。

SELECT *
  FROM (
    VALUES
      (1, 100, 'test1'),  -- 1行目
      (1, 200, 'test2'),  -- 2行目
      (2, 200, 'test3'),  -- 3行目
      (2, 400, 'test4'),  -- 4行目
      (2, 600, 'test5')   -- 5行目
       ) AS t(col1, col2, col3)  -- テーブル名とカラム名を定義
;

数十行のデータを想定しているのであればテストテーブルを用意したほうが良いかと思いますが、数行で実施できる動作確認であればこちらを利用いただくと簡単にテストすることが可能です。例えば下記のように集計する際の動作確認も簡単に行えますし、他ユーザーに共有する場合にもクエリ文ごと共有すればテストテーブルを参照できるよう権限付与する必要もないので便利です。

Hiveの場合

Hiveの場合はSTACKを利用します。Prestoの VALUESとの違いは、総レコード数を第一引数に指定することと、想定しているレコードごとに丸括弧で囲わない点です。

SELECT *
  FROM (SELECT STACK(5,  -- 合計レコード数
                     1, 100, 'test1',  -- 1行目
                     1, 200, 'test2',  -- 2行目
                     2, 200, 'test3',  -- 3行目
                     2, 400, 'test4',  -- 4行目
                     2, 600, 'test5'  -- 5行目
                    ) AS (col1, col2, col3)  -- カラム名を定義
       ) AS t -- テーブル名を定義
;