Treasure Data - Support Engineering Team blog

トレジャーデータのサポートエンジニアリングチームのブログです。

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

こんにちは、Arm Treasure Data サポートの伊藤です。

本記事は2回目のエントリーとなります。

前回はData Connectorと呼ばれる、外部サービス(AWS S3やSFTPサーバーなど)からデータをTreasure Data(TD)へインポートする際のナレッジについて説明しましたが、 今回はData Connectorでインポートしたデータを利用し必要なデータを抽出する際に利用するPresto/Hiveに関連するTipsを紹介したいと思います。

Presto、Hiveとは

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

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

TDでSQLを利用するにあたり、テーブルの件数を確認する際に利用する COUNT 関数や、対象カラムの合計を算出する SUM 関数などは他のRDBMSなどでも利用できるため知っている方も多いかと思います。

これらのスタンダードな関数以外にもPresto、Hiveにはそれぞれ便利な関数が実装されており、OSSのPresto/Hiveには実装されていないTD固有の関数もTDでは利用することができます。 ドキュメントを見ることで各関数の挙動を知ることができますが、本番運用前に実際に動作させて確認する方がほとんどではないかと思います。

その際に面倒な点として「テストデータを用意する」ことが挙げられます。

数行のレコードで十分なケースでも空のテーブルを作成して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;

f:id:td-support:20200611170800p:plain
SPLIT関数をTDコンソールで実行

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

いよいよ本丸です。集計するクエリの動作確認をしたい場合は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)  -- テーブル名とカラム名を定義
;

f:id:td-support:20200611175942p:plain
VALUES句によるテストデータ作成

数十行のデータを想定しているのであればテストテーブルを用意したほうが良いかと思いますが、数行で実施できる動作確認であればこちらを利用いただくと簡単にテストすることが可能です。

例えば下記のように集計する際の動作確認も簡単に行えますし、他ユーザーに共有する場合にもクエリ文ごと共有すればテストテーブルを参照できるよう権限付与する必要もないので便利です。

f:id:td-support:20200611180931p:plain
VALUES句を使った集計サンプル

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 -- テーブル名を定義
;

f:id:td-support:20200611184203p:plain
STACKによるテストデータ作成

最後に

本エントリーはいかがでしたか?TDをご利用いただく際に、まずテストデータを用意するところが億劫になってしまうかと思いますのでぜひご利用ください。 サポートはお客様のテーブルの中身やクエリの結果は見ることができないため、お問い合わせされる際にも有効活用いただけると嬉しいです。