こんにちは。
データエンジニアのKotaです。
初投稿ですが、お手柔らかにお願いします。
普段僕は自社プロダクトのデータ分析チームで、
ビッグデータ分析基盤の要件定義~運用まで一気通貫で作らせて頂いてます。
そこで今回議論したいのが、表題の件。
現場によっても、下手すれば現場内のDBによってもバラバラの型をしている日付カラム。
皆さんお持ちのDBでは、どちらの型になっているでしょうか？？
#議論したいと思ったきっかけ
かくいう僕も、この問題で先日上司と軽くバトりました。
まずはその話から簡単に触れます。ちなみにエンジンはMysqlです。
ある日、別の分析基盤を作っている後輩のDB設計書をレビューする機会がありました。メンバーは後輩・僕・上司の3人。
そこで後輩が、分析用一時テーブルの日付列のデータ型を以下の様にしていました。
| カラム名 | データ型 |
|---|---|
| CREATE_TIME(作成日) | varchar(14) |
| UPDATE_TIME(更新日) | varchar(14) |
恐らく一部マスタ系のテーブル以外はほぼ持っているであろうこのカラム。
これに対し、語気を強めてツッコミを入れたのが先輩です。
「ここの2つさ、datetime型にしてよ!!!」
お？そんな強く言うこと？
いや、てか別にvarcharで良くね？
むしろvarcharが良くね？と思ったのが僕。
その旨上司に伝えてみると、
**「いやいや絶対datetime型!!」**の一点張り。
その場で議論が始まるも、平行線。
ちなみに既存の分析基盤はdatetimeだったので、
しょうがなく僕が折れる形でdatetimeに決着しました。
#僕の言い分
事実だけ伝えられても「そっか...」って感じだと思うので、
お互いの主張の理由を見てみましょう。
まずは僕から。僕の主張の根拠は以下3つです。
①チューニングのしやすさ
②業務用DBとのフォーマット合わせ
①
僕が重視したのはこれです。
検索の際に関数を指定しなくて良くなるので非常に楽。
特に既存の分析用SQLなんかは、
件数が億に迫るテーブルから、1日分のデータを以下のように算出していました。
SELECT ...
FROM ...
WHERE DATE(RPAD(UPDATE_TIME,14,0)) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND CURRENT_DATE();
ちなみにこれ以外に条件はありません。中にはこれに加え、結合を行っているものも複数あります。
WHERE条件で使用しているUPDATE_TIMEをdate型に変換+謎のRPAD関数と、
関数を2ネストかましています。(恐らく不良データ対策でしょう)
当然関数を使ってるのでインデックスは効きません。
それどころかこのテーブル、UPDATE_TIMEを第一項目としたインデックスすら付与していない始末。
既に件数が億近いテーブルからこの条件でデータ抽出。よく今までやってたな...って感じです。
UPDATE_TIMEがvarcharになれば、不必要にdate変換なんてしなくて良いので、
インデックス効くようになるし。RPADは知らん。作った人辞めてて理由は誰も知らん。地獄って感じ。
②こちらの理由もあります。うちの現場、UPDATE_TIMEの扱いがDBによって違います。
★業務用DB
| カラム名 | データ型 |
|---|---|
| CREATE_TIME(作成日) | varchar(14) |
| UPDATE_TIME(更新日) | varchar(14) |
★分析用DB
| カラム名 | データ型 |
|---|---|
| CREATE_TIME(作成日) | datetime |
| UPDATE_TIME(更新日) | datetime |
★分析結果可視化用DB
| カラム名 | データ型 |
|---|---|
| CREATE_TIME(作成日) | datetime |
| UPDATE_TIME(更新日) | datetime |
何で違うんでしょうね。作った人辞めてて理由は誰も知らん。地獄って感じ。
恐らく可視化の際に、BIツールでのデータソース読み込みで都合が悪そうだからdatetimeにしてるのかな...って感じはします。
今回後輩君はこの理由でvarcharにしたそうです。不良データなら可視化DBに移送する際にチェックできるし。だそうです。その通りだと思います。
#上司の言い分
一方、上司の言い分です。
①現行踏襲
②不良データへのリスク
③設計書見て、「日付入れてるんだな」って分かりやすい
①、③は聴き流してました。
②が彼のメイン主張。
僕のチューニングに対する言い分を聞いた上で、
「運用乗ってるし、何か変わったときに変なデータ入って処理が落ちるリスクがある」んだそう。
その辺の検証をする時間的余裕はない！って言われてしまったので、
それ以上何も言い返せず、この場は終わりました。
#モヤモヤする...。
あまりにモヤっとなのでここに書かせて頂きました。
またハードウェアに優しくない激重SQLが1つ出来上がってしまうのかと思うと...。
僕が経験してきた現場でも、ここの型にはバラツキがあったので気になります。どっちがより良いんでしょうか。
皆さんの現場ではここのデータ型、何にしていますか？
どんな理由があってその型にしていますか？
ぜひご教授頂けたら、とても嬉しいです。
最後まで見て頂いて、ありがとうございました。