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の別テーブルに書き出すことができる。
この機能を使って、アップロードされたデータに対してクレンジングする。
クエリは下記のクエリとなる．

```sql
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 HH:mm:ss', 'JST')
    - TreasureDataの独自関数
    - http://docs.treasuredata.com/articles/udfs
4. timeを最後に付与しているのは、TreasureDataではtimeをキーとして扱っているため、分散処理を効率的に行うにはtimeを時間単位で散けさせることが良いため。


# 結論

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

以上。