Amazon Redshift：TO_TIMESTAMP関数が使えるようになっていました

超小ネタです。

更にはタイトルで言いたい事を言い切ってしまった感があり、これ以上の内容は特に無いのですが、これだけで終わるのもアレなので少し内容を付け加えてみたいと思います。

元々、RedshiftではTO_TIMESTAMP関数は存在していなかった

過去のエントリを遡ってみると、2015年12月時点では文字列型のデータをTIMESTAMP型に変換する、所謂『TO_TIMESTAMP』な関数は存在していませんでした。下記エントリでは当時辺りから使えるようになっていたUDFを使って、その処理を実現しています。

• Amazon Redshift: UDFを使って日付・時刻の文字列をタイムスタンプ型に変換してみる ｜ Developers.IO

2016年06月には、以下のエントリがTO_TIMESTAMP絡みで投稿されています。UDFを使うよりもパフォーマンス効率が良く、RedshiftでTO_TIMESTAMPな処理を実現する場合はパフォーマンスの観点からこれがベターなのでは...という風に現状把握しておりました。

• Amazon RedshiftでTO_TIMESTAMP関数を使いたい場合の対応方法 ｜ Developers.IO

...でしたが、気付けばTO_TIMESTAMP関数、使えるようになっているでわないですか！？ いつから使えるようになってたんだろう...

• TO_TIMESTAMP 関数 - Amazon Redshift

UDFとTO_TIMESTAMPを比較してみる

使えるんなら使った方が良いんじゃね？とは思うものの、UDFとはどれ位性能差があるんだろう...という事で、若干雑ではありますが両者のパフォーマンス比較をしてみたいと思います。

以下の様な形で、タイムスタンプを構成する要素をそれぞれ文字列型で定義し、データを何件か登録。

CREATE TABLE public.to_timestamp_test (
  years CHAR(4),
  months CHAR(2),
  days CHAR(2),
  hh24miss CHAR(8)
);

# INSERT INTO public.to_timestamp_test VALUES('2001','01','01','01:01:01');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2002','02','02','02:02:02');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2003','03','03','03:03:03');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2004','04','04','04:04:04');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2005','05','05','05:05:05');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2006','06','06','06:06:06');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2007','07','07','07:07:07');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2008','08','08','08:08:08');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2009','09','09','09:09:09');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2010','10','10','10:10:10');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2011','11','11','11:11:11');
INSERT 0 1
# INSERT INTO public.to_timestamp_test VALUES('2012','12','12','12:12:12');
INSERT 0 1
#

そしてこれも雑ですが、再帰的にデータを増やしていってまぁまぁそれなりの、速攻で結果が返ってくる訳ではないが、そこまで長く掛からないであろう件数にまで増やしてみます。

# INSERT INTO public.to_timestamp_test (SELECT * FROM public.to_timestamp_test);
INSERT 0 12
# INSERT INTO public.to_timestamp_test (SELECT * FROM public.to_timestamp_test);
INSERT 0 24
# INSERT INTO public.to_timestamp_test (SELECT * FROM public.to_timestamp_test);
INSERT 0 48
# INSERT INTO public.to_timestamp_test (SELECT * FROM public.to_timestamp_test);
INSERT 0 96
:
:
# INSERT INTO public.to_timestamp_test (SELECT * FROM public.to_timestamp_test);
INSERT 0 3145728
Time: 1438.878 ms
# INSERT INTO public.to_timestamp_test (SELECT * FROM public.to_timestamp_test);
INSERT 0 6291456
Time: 2366.276 ms
# SELECT COUNT(*) FROM public.to_timestamp_test;                                                                                                                                                         
  count   
----------
 12582912
(1 row)

Time: 272.390 ms

そしてこれらのデータに対して、比較対象となる方法で各々検索を掛けてみます。SELECT文を実行し、結果の先頭が表示されるところまでの時間を計測してみました。(Redshiftクラスタはdc1.large 2ノード構成)

まずはUDFでTO_TIMESTAMP関数と同じような作りとなるものを作った場合。下記エントリで作成したものを流用しました。1200万件超のデータに対して150秒少々で実行出来ました。

• Amazon Redshift: UDFを使って日付・時刻の文字列をタイムスタンプ型に変換してみる ｜ Developers.IO

SELECT
  cm_to_timestamp(
    public.to_timestamp_test.years || public.to_timestamp_test.months || public.to_timestamp_test.days,
    ' ',
    hh24miss,
    '%Y%m%d %H:%M:%S'
  )
FROM
  public.to_timestamp_test;

   cm_to_timestamp   
---------------------
 2001-01-01 01:01:01
 2003-03-03 03:03:03
 2006-06-06 06:06:06
 2008-08-08 08:08:08
 2009-09-09 09:09:09
 :
 :

 2009-09-09 09:09:09
 2004-04-04 04:04:04
 2002-02-02 02:02:02
 2002-02-02 02:02:02
Time: 153606.258 ms

次いでTO_TIMESTAMP関数。こちらは何と同じ件数に対して20秒少々で実行出来ました。全然早いですね。(※UDFが遅いというべきか)

SELECT
  TO_TIMESTAMP(
    public.to_timestamp_test.years || 
    public.to_timestamp_test.months || 
    public.to_timestamp_test.days || 
    ' ' || 
    public.to_timestamp_test.hh24miss,
    'YYYYMMDD HH24:MI:SS'
  ) AS to_timestamp_value
FROM
  public.to_timestamp_test
;

   to_timestamp_value   
------------------------
 2007-07-07 07:07:07+00
 2007-07-07 07:07:07+00
 2011-11-11 11:11:11+00
 2006-06-06 06:06:06+00
 :
 :
 2004-04-04 04:04:04+00
 2007-07-07 07:07:07+00
 2003-03-03 03:03:03+00

Time: 22536.551 ms

ちなみに下記エントリで紹介されている方法、文字列結合させてからTIMESTAMP型にキャストする方式はどれ位掛かるのでしょうか....あれれ？遜色無い感じですね。(※手元の環境ではむしろ早い時もあったりしました。TO_TIMESTAMPが一番効率良いと思ってたのでちょっと意外...w)

• Amazon RedshiftでTO_TIMESTAMP関数を使いたい場合の対応方法 ｜ Developers.IO

# SELECT
  (public.to_timestamp_test.years || 
   public.to_timestamp_test.months || 
   public.to_timestamp_test.days || 
   ' ' || 
   public.to_timestamp_test.hh24miss
  )::TIMESTAMP
FROM
  public.to_timestamp_test;

      timestamp      
---------------------
 2001-01-01 01:01:01
 2003-03-03 03:03:03
 2006-06-06 06:06:06
 2008-08-08 08:08:08
 :
 :
 2004-04-04 04:04:04
 2002-02-02 02:02:02
 2002-02-02 02:02:02
 2010-10-10 10:10:10
Time: 23871.706 ms

まとめ

という訳で、Redshift環境でも実はTO_TIMESTAMP使えるようになっているよ、というお知らせエントリでした。