Redshift Spectrumでparquetを扱う、timestampに苦労した話

概要

  • 元データがtsv, csvなものをpythonを利用してparquetに変換して、spectrumで利用するためにやったこと
    • timestampなカラム周りでかなり苦労したのでせっかくなので書く
    • 結果的にかなり単純にできるんだけど、あまりやり方とかなかったのでせっかくだし書いた

一行でまとめると

  • fastparquetのwrite関数でtimesオプションにint96をつけろ!

いろいろ

tsvをparquetに変換すること自体はかなり容易で、fastparquetやpyarrowを利用することで数行で実現できます。

blog.amedama.jp

などのように単純にできます。このとき、parquetのschemaはpandasのdataframeをもとに設定されていきます。 文字列や数字のカラムだったら基本的にそのまま変換して、そのまま外部テーブルとして読み込ませることが可能なのですが、問題はtimestampなカラムです。
pandasで、 read_csv などで読み込む場合、例えば 2019-03-30 12:30:12 のようなdb上ではtimestampとして扱われるカラムは何もしないと当然文字列として扱われてしまいます。
Redshift spectrumの外部テーブルもschemaを設定し、parquet側もschemaを持ちます。テーブル上ではtimestampで、parquetでは文字列な場合どうなるでしょうか?
答えは、クエリ時にエラーが出ます。

では、timestampのカラムをそのように変換すればよいのではないか?と思ったりします。こんな風に

import pandas as pd
from fastparquet import write

df = pd.read_csv("path/to/csv")
df["created_at"] = pd.to_datetime(df["created_at"])
write("output.parquet", df)

はい、これでparquet上でもtimestampとして出力できます。 実際に、parquet-toolsと呼ばれるparquetファイルのschemaだったりレコードを見れるツールで確認することで、timestampとして認識されていることがわかります。

parquet-mr/parquet-tools at master · apache/parquet-mr · GitHub

しかし、これで変換したとしてもダメなのです。 下記のissueのように、クエリの結果が全く異なったり、そもそもtimestampなデータとして認識してくれなかったりします。

Add ability to output Hive/Impala compatible timestamps · Issue #82 · dask/fastparquet · GitHub

これは推測なのですが、Redshift spectrumはint96なtimestampしか対応しておらず、int64なtimestampは受け付けてないのかなと。(正確には、Redshift spectrumの内部エンジン(prestoなのかimpalaなのかhiveわからない)が)

実際に今回模索していくなかで、hiveより出力したparquetファイルをそのままRedshift spectrumに読ませるとtimestampは正しく解釈され、parquetファイルのtimestampの型はint96となっておりました。

じゃあint96なformatとして出力すればよいわけで、じゃあどうすればいいかというとoptionを指定すればいいだけです。

import pandas as pd
from fastparquet import write

df = pd.read_csv("path/to/csv")
df["created_at"] = pd.to_datetime(df["created_at"])
write("output.parquet", df, times="int96") # timesにint96指定するだけ!

これだけでRedshift spectrumでもちゃんとtimestampを解釈することができます。なんと簡単。 実は上記のissueに答えは最初から書いてあったのですねー、悲しい。

まとめにならないまとめ

わかればとても単純な話ですが、これだけに半日くらい使ったのでその記録として残しておきます。
判明する過程として、hiveを立てたり様々なことをしました、今思うと遠回りしましたがそういうことってよくありますよね。。。辛い。
もっとデータとかコマンド履歴とかちゃんと丁寧に書いたほうがいいと思うのですが、やろうとするとこの記事を上げられないと思うので、もう雑記事なままえいやっです。