メインコンテンツへスキップ

​
はじめに

このトピックでは、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 その列に［テキストを置換］タイルを使用し、文字列のどの部分を空の文字列：@.+に置換するかを指定します。 userid@email.com 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’
日本語