概要
ここではIBM Puredataのデータクレンジング手法について扱っています。
今回はNVARCHAR文字列からVARCHAR文字列に変換可能なレコードを見つけ出す方法です。
動作環境
IBM Puredata 7.0.4
背景
IBM PuredataはDWHのため、様々な形式のデータソースの受け皿に使われがちです。
今回は実際にあったデータクレンジング要件の中から比較的簡単に実現できる例を紹介します。
ざっくり言いたいこと
NVARCHARカラムからVARCHARに置き換えられるレコードを見つける
それによって。。
- 不要レコードが削減できる（テーブル容量の節約）
- 型が小さくなることでクエリコストも削減できる
要件
あるNVARCHARで定義されたカラムがありました。
それには全角半角問わず、様々な文字列が入っていたのですが、それによって文字化けおよびバッチ処理等に不具合を起こしていました。
そこで、処理上必要なものはVARCHARで表現できる文字列（半角英数および記号）だけだったため、それらだけに絞るような処理を考えました。
ちなみにIBM Puredataはデフォルトのエンコード形式がUTF-8です。
また、IBM Puredataの場合NVARCHARですと1文字あたり4byte消費とコストが高く
それに対しVARCHARは1byte計算となるため、単純計算で同じ集計処理に4倍もの差が生まれることが予想されます。（細かい演算結果については触れません）
そのため、型変換を行うことで集計処理等のコストをグッと下げる効果もあります。（DWHとしてここ大事）
＊予想される入力値に対して最適な型を選定するのはどのDBも変わりませんが。。
解決方法について
3つ考えました。
- 既存の関数でどうにかする
- 正規表現を使用する
- SQLで工夫する
1.既存の関数でどうにかする
IBM Puredataには文字列を評価する関数がありませんでした。。
2.正規表現を使用する
実はIBM Puredataには正規表現関数がいくつか用意されているのですが、なぜか
デフォルトではサポートされておらず、別途SQL Extensions KitをDBにインストールする必要があります。
環境によっては導入できない場合もあるかと思うので今回は無視します。
3.SQLで工夫する
今回はSQLだけでゴリ押すことにしました。
以下のようなテーブルがあったとします。
CREATE TABLE TABLE_A (
TEXT NVARCHAR(100) NOT NULL
) DISTRIBUTE ON (TEXT)
;
例えばこんなデータがあったとして、2番目のデータを不要なものとして扱いたいとします。
| No | NVARCHAR値 | VARCHARに値をそのまま入れた場合 | 評価 | ポイント |
|---|---|---|---|---|
| 1 | example@sample.com | example@sample.com | OK | 全て半角英数記号で構成されているためVARCHAR変換できる・変換すればバイト数も節約できるので一石二鳥 |
| 2 | あいうえお@sample.com | ?????@sample.com | NG | 正しく変換できない、そもそもこんなデータは扱いたくない |
やり方は簡単です。
NVARCHAR版の文字列とVARCHAR版の文字列を用意し
WHERE句で比較結果がTRUEのものに絞るだけです。
SELECT
VARCHAR_TEXT
FROM (
SELECT
TEXT AS NVARCHAR_TEXT
,CAST(TEXT AS VARCHAR(100)) AS VARCHAR_TEXT
FROM
TABLE_A
WHERE
NVARCHAR_TEXT = VARCHAR_TEXT /* 無変換の文字列とCASTした文字列が一致 */
) AS GET_VARCHAR_TEXT
;
余談
CASTの方法はIBM Puredataの場合
CAST(COLUMN_A AS VARCHAR(100))
または
COLUMN_A::VARCHAR(100)
と2パターンできます。お好みでどうぞ。