SQLとMagic ETL DataFlowを使用したデータクリーニング演算

​

はじめに

このトピックでは、SQLとMagic ETL DataFlowで利用可能なデータクリーニング演算の一覧を表示し、演算を使用する前後のデータの見え方を示します。

​

データタイプを変更

例：ID番号のデータタイプを数字からテキストフィールドに変更

MySQLの場合	Magic ETLの場合	前	後
CAST(id AS CHAR) AS id_cast_datatype	［列タイプを設定］タイルを使用します。	1	1（見た目は同じですが、テキストのディメンションを指定します）

​

列を連結して複合フィールドを作成

例：「ファーストネーム」列と「姓」列を連結して「フルネーム」列を作成

MySQLの場合	Magic ETLの場合	前	後
CONCAT(first_name, ’ ’, last_name) AS full_name	［列を結合］タイルを使用します。	‘John’ | ‘Smith’（ファーストネームと姓が2つの独立した列にある）	‘John Smith’ （単一の列が完全な名前を含んでいる）

​

テキスト文字列の一部を抽出

例：ユーザーIDとして使用するメールアドレスの最初の部分を抽出

MySQLの場合	Magic ETLの場合	前	後
SUBSTRING_INDEX(email,’@’, 1) AS user_id	その列に［テキストを置換］タイルを使用し、文字列のどの部分を空の文字列：@.+に置換するかを指定します。	[e-mail]	userid

​

日付を再フォーマット

例：非標準の日付文字列を日付タイプにフォーマットします

MySQLの場合	Magic ETLの場合	前	後
STR_TO_DATE(send_date, ‘%d.%m.%Y’) AS date_formatted	［列タイプを設定］タイルを使用します。	23.01.2017（文字列データタイプ）	01/23/2017（日付データタイプ）

​

日付列から日付属性を取得

例：日付列から曜日を抽出

MySQLの場合	Magic ETLの場合	前	後
DAYNAME(receive_date) AS receive_date_name	［日付の演算］タイルを使用します。	01/23/2017	月曜日

​

1つの列を列内の1つの文字にもとづいて2つの列に分割

例:「状態コード」列を、列内で見つかった / 区切り文字にもとづいて状態コードの部分に分割します

MySQLの場合	Magic ETLの場合	前	後
SUBSTRING_INDEX(status_code, ’/’, 1) AS status_code_p1 , SUBSTRING_INDEX(status_code, ’/’, -1) AS status_code_p2	その列に［テキストを置換］タイルを使用し、文字列のどの部分を空の文字列：最初の部分に対しては /.+ 、2番目の部分に対しては .+/ に置換するかを指定します。	SHI/DELV	SHI | DELV

​

列からエラーとなるスペースをトリミング

例：「Department」列の行頭と行末の空白文字をトリミング

MySQLの場合	Magic ETLの場合	前	後
TRIM(department) AS department_trimmed	［テキストを置換］タイル内で正規表現を使用して、行頭と行末の空白文字を指定して、何もないものに置き換えます。	’ department ’	‘department’

​

全体の列の大文字/小文字の使い方を変更

例：「Category」列を大文字に変更

MySQLの場合	Magic ETLの場合	前	後
UPPER(category) AS category_change_case	［テキストフォーマット］タイル内の すべて大文字 オプションを使用します。	health	HEALTH

​

列の最初の文字を大文字にする

例：「Category」列の最初の単語の最初の文字を大文字にします

MySQLの場合	Magic ETLの場合	前	後
CONCAT(UPPER(LEFT(category, 1)), SUBSTRING(category, 2, LENGTH(category))) AS category_cap_first	［テキストフォーマット］タイル内の 頭文字を大文字にする オプションを使用します。	health	Health

​

特定の列の値にもとづいて行を分類

例：ストア番号にもとづいて各行に地域を割り当てる

MySQLの場合	Magic ETLの場合	前	後
(CASE WHEN store_number IN (‘100’, ‘101’, ‘104’, ‘109’) THEN ‘region_1’ WHEN store_number IN (‘102’, ‘105’, ‘110’) THEN ‘region_2’ WHEN store_number IN (‘103’, ‘106’, ‘107’, ‘108’) THEN ‘region_3’ ELSE ‘no region’ END) AS region	［行をフィルター］ タイルを使ってストア番号にもとづいて別々のグループに分離し、 ［定数を追加］ を使って、各グループにカテゴリーを追加し、 ［行を追加］ を使って、分離されていた行を合せて元に戻します。	store_number = 100	store_number = 100 | region = ‘region_1’

​

特定の列の値にもとづいて行を分類

例：カテゴリーを名前の最初の文字にもとづいた動物名に割り当てます。

MySQLの場合	Magic ETLの場合	前	後
(CASE WHEN animal_names LIKE ‘H%’ THEN ‘Hs’ WHEN animal_names LIKE ‘B%’ THEN ‘Bs’ WHEN (animal_names LIKE ‘A%’ OR animal_names LIKE ‘E%’) THEN ‘AEs’ ELSE ‘another letter’ END) AS animal_names_categories	［テキストを置換］タイル内で正規表現を使用してパターンを識別し、カテゴリーを割り当てます。例えば、 ^H.+ はHで始まる名前を、 ^B.+ はBで始まる名前を、 ^A.+|^E.+ はAまたはEで始まる名前を、 ^[^H,b,a,E]+ はH、B、A、E,で始まらない名前を検索します。	animal_names = ‘Horse’	animal_names = ‘Horse’ | animal_names_categories = ‘Hs’

​

数値のしきい値にもとづいて行を分類

例：給与の額にもとづいて行を分類する

MySQLの場合	Magic ETLの場合	前	後
(CASE WHEN salary < 50000 THEN ’<$50,000’ WHEN salary < 100000 THEN ‘$50,000 - $99,999’ WHEN salary < 150000 THEN ‘$100,000 - $149,999’ WHEN salary < 200000 THEN ‘$150,000 - $199,999’ ELSE ’>$200,000’ END) AS salary_bucket	［行をフィルター］タイルを使ってストア番号にもとづいて別々のグループに分離し、［定数を追加］を使って、各グループにカテゴリーを追加し、［行を追加］を使って、分離されていた行を合せて元に戻します。	salary = $45,000	salary = $45,000 | salary_bucket = ‘<$50,000’

日本語