TreasureData上で日付と時刻が別々のカラムにあるときのクエリ

発生シチュエーション

古くからあるDB等では、時間を表すカラムに対して，日付型を使わずに、日付と時刻を別々に格納する設計をしていることがある。

何も考えずにそのままDumpすると下表のデータが出力されてしまう。
これでは、start_dateとstart_timeというカラムを見ても、何を意味しているのかが分かりにくい。

start_date	start_time	id
20140525	100021	abc
20140524	001002	dfg

さて、このデータをCSVファイルでdumpし，アップロードする際に、各カラムの意味も知らずに、TreasureDataへアップロードしてしまうと、更に大変なことになる。

どんな問題が発生するか？

TreasureDataのテーブルには、下表のデータが入る。
start_timeカラムには、データがlong型で入っているために先頭の'00'が抜けていることが分かる。

start_date:long	start_time:long	id:string	time:long
20140525	100021	abc	0
20140524	1002	dfg	0

後でこれを見たデータ分析担当者は、start_timeが一体何を表すのか分からず、頭を抱えることになる。

何が原因か？

BulkImportの自動で型推定を行っている。
その際に、start_timeをlongとして推定されて型変換が行われ、先頭の文字列が除去されていること。

対応策は？

Dumpする前にデータを日付型にして、出力する

これが一番自然．
つまり，RDBからファイルに出力されるときにクレンジングすること．

例としては，下表のように出力する．

start_datetime	id
2014-05-25 10:00:21	abc
2014-05-24 00:10:02	dfg

BulkImportツール

コマンドオプションで，start_timeのスキーマをstring型として指定する．
これにより，TreasureData上のデータには，下表のようになる．(テーブルのスキーマは，後でstring型に指定する)

start_date:long	start_time:string	id:string	time:long
20140525	100021	abc	0
20140524	001002	dfg	0

コマンド例

一番楽なのは、--all-stringオプションを付与。これで全カラムStringとして変換される。
もう少し頑張ると、--column start_time:string とオプションを付与してもよい。
http://docs.treasuredata.com/articles/bulk-import-from-csv

クエリを使って気合でデータのクレンジングをする

TreasureDataには、クエリの実行結果をTreasureDataの別テーブルに書き出すことができる。
この機能を使って、アップロードされたデータに対してクレンジングする。
クエリは下記のクエリとなる．

SELECT
  id,
  start_datetime,
  TD_TIME_PARSE(start_datetime, 'JST') AS time,
FROM
(
  SELECT
    id,
    CASE LENGTH(start_time)
      WHEN 1 THEN TD_TIME_FORMAT(
        UNIX_TIMESTAMP(CONCAT(start_date, '00000', start_time), 'yyyyMMddHHmmss') - 32400,
        'yyyy-MM-dd HH:mm:ss', 'JST')
      WHEN 2 THEN TD_TIME_FORMAT(
        UNIX_TIMESTAMP(CONCAT(start_date, '0000', start_time), 'yyyyMMddHHmmss') - 32400,
        'yyyy-MM-dd HH:mm:ss', 'JST')
      WHEN 3 THEN TD_TIME_FORMAT(
        UNIX_TIMESTAMP(CONCAT(start_date, '000', start_time), 'yyyyMMddHHmmss') - 32400,
        'yyyy-MM-dd HH:mm:ss', 'JST')
      WHEN 4 THEN TD_TIME_FORMAT(
        UNIX_TIMESTAMP(CONCAT(start_date, '00', start_time), 'yyyyMMddHHmmss') - 32400,
        'yyyy-MM-dd HH:mm:ss', 'JST')
      WHEN 5 THEN TD_TIME_FORMAT(
        UNIX_TIMESTAMP(CONCAT(start_date, '0', start_time), 'yyyyMMddHHmmss') - 32400,
        'yyyy-MM-dd HH:mm:ss', 'JST')
      WHEN 6 THEN TD_TIME_FORMAT(
        UNIX_TIMESTAMP(CONCAT(start_date, start_time), 'yyyyMMddHHmmss') - 32400,
        'yyyy-MM-dd HH:mm:ss', 'JST')
    ELSE NULL
    END start_datetime,
  FROM table
) pre

クエリの説明

1. CASE LENGTH(start_time)
   • 時刻の文字列の長さを測る
   • 時刻の先頭'0'が抜けるとすると、'0'~'235959'までの範囲が想定される
2. UNIX_TIMESTAMP(CONCAT(start_date, start_time), 'yyyyMMddHHmmss') - 32400
   • CONCATで文字列結合し、'20140527234411'といった文字列にある
   • UNIX_TIMESTANPで任意のフォーマットからUnixtimeへの変換
   • 32400を減算しているのは、UNIX_TIMESTAMPはUTCとして時刻を変換してしまうので、文字列がJSTの時刻の場合にはタイムゾーン分ずれてしまうため。
3. TD_TIME_FORMAT(~, 'yyyy-MM-dd HHss', 'JST')
   • TreasureDataの独自関数
   • http://docs.treasuredata.com/articles/udfs
4. timeを最後に付与しているのは、TreasureDataではtimeをキーとして扱っているため、分散処理を効率的に行うにはtimeを時間単位で散けさせることが良いため。

結論

データの一つ一つのカラムには、意味がある。
データ分析の前に、所有しているデータそれぞれがどんな意味を持ったデータなのかを確認してみる。
特に、テキストファイルとしてアウトプットされたデータは，全てが只の文字列であり，そこにlong型もint型も無い．
そのため、データをアップロードするときに，本当に数値型でアップロードしてよいのかをチェックしなければならない．
そして、アップロード前に，データの前処理について考えることで，よりスムーズにデータ分析を行えるようにしましょう。

以上。