サーバから掃き出されたデータをIICSを使ってデータウェアハウスに蓄積する業務を行っています。その中で発生した「カンマを含むデータを数値としてデータウェアハウスへ蓄積する方法」をまとめます。
前提：データ処理の流れ
データ処理の流れは、上図の通りです。
- オンプレミスのサーバから掃き出されたtabファイルをフラットファイル接続で取得
- IICS上でデータを加工
- Snowflake上に設計したテーブルに蓄積する処理を行いました。
本記事では、IICS上での実装で大変だったことを整理します。
tabファイルの構造
本処理で扱ったデータファイルの形式は「tabファイル」です。カンマ区切りのデータファイル形式であるcsvファイルのように、タブ区切りでデータが記録されているファイル形式です。
業務で扱うデータを示すのはここでは適切ではないので、本記事では「神奈川県の2023年1月-2月の1時間ごとの天気」をtabファイルとして出力したものを示します
IICSで実装したデータ処理マッピング
IICSのマッピングタスクで、ソーストランスフォーメーション→式フォーメーション→ターゲットフォーメーションをつなげてデータ処理タスクを設計しました。
障害：実際に起こったトラブル
数値データに想定よりも小さい値が入っている
tabファイルからデータウェアハウスへNUMBER型として蓄積するカラムでは、想定よりも小さい値が取り込まれていました。
左：蓄積対象のtabファイル
右：蓄積先のデータウェアハウスのテーブル
※tabファイル(左)と蓄積先のデータウェアハウスのテーブル(右)を見比べてみると、生データのカンマ以降のデータが"丸められ"ていることがわかる
解決策：カンマを含む数値データを丸められることなく蓄積する方法
カンマを含む数値データを丸められることなく蓄積する方法2つを紹介します。
- ソーストランスフォーメーションでカラムの型を変更する
- 式トランスフォーメーションでカンマを抜く
1. ソーストランスフォーメーションでカラムの型を変更する
この方法は、後述の"ある操作"を行うと再度データが"丸められ"ることがあります。数値データを"丸められ"ることなく蓄積する方法を示すとともに、"ある操作"によって再度データが丸められる原因も記述します。
手っ取り早く、安定的にデータ蓄積をしたい人は 2.式トランスフォーメーションでカンマを抜く をお読みください
手順1)「桁区切り記号」で「カンマ」を選択する
ソーストランスフォーメーション>プロパティ>ソース>2番目の「詳細」にて
「桁区切り記号」のプルダウンから「カンマ」を選択する
手順2)ソースフィールドで当該カラムの「ネイティブタイプ」をnumberに変更する
ソーストランスフォーメーション>プロパティ>フィールドにて
「オプション」のプルダウンから「メタデータの編集」にチェックを入れる
元々、ソーストランスフォーメーションは接続先と対象のインプットデータが指定されると、ソースフィールドが自動で読み込まれる仕様になっていますが、「メタデータの編集」にチェックを入れると、フィールドの名前や型、スケールが手動で変更できるようになります。
この編集機能を使って、当該カラムのネイティブタイプをnumberに変更します。
(数値データの精度とスケールに応じて、適宜「ネイティブ精度」と「ネイティブスケール」の値を手動で変更してください)
手順1, 2によって、Snowflakeへカラム以降の桁落ちを回避することができました。
設定が解除される"ある操作"
以下の操作を行うと手動で変更したカラムの型がリセットされます。
ソーストランスフォーメーションにて、対象のフラットファイルを指定し直すなどの操作を行うと、前述の 手順2) で変更したソースフィールドのデータ型が解除される。
こうしたカラムの型のリセットはあくまで一例です。ソーストランスフォーメーション上で他にも意図しない操作を行うことによって、ソースフィールドのカラムの型がリセットされることもあるかもしれません。
2.式トランスフォーメーションでカンマを抜く
式トランスフォーメーション中の式を活用して、データの文字列からカンマのみを取り除いて、カンマを抜く方法を紹介します。
以下の手順を踏む前に、先述の 1. ソーストランスフォーメーションでカラムの型を変更する で変更したソーストランスフォーメーションでの設定はデフォルトに戻っていることを確認してください。
手順1) 式トランスフォーメーションでREPLASESTRを使う
REPLASESTR関数は"文字列内の文字を1文字、複数の文字または文字なしに置換"する関数です。
REPLASESTR関数でカンマを抜く処理と、TO_DECIMAL関数(String文字列をDecimal型に型変換する関数)を使って、対象のカラムの型変換を以下のように記述しました。
TO_DECIMAL(REPLACESTR(1,pressure,',',''), 1)
式トランスフォーメーションでの処理によって、Snowflakeへカラム以降の桁落ちを回避することができました。
定常的に桁落ちを回避してデータを蓄積するためには、2.式トランスフォーメーションでカンマを抜く の方が最適な解決策と言えます。