[初学者向け]AWS Glue DataBrewで擬似会員のデータクレンジングをやってみた

はじめに

みなさんはAWS Glue DataBrew触ったことありますか？
ビジュアルツールによってデータをわかりやすく視覚化し、ノーコードでデータの事前処理やクレンジングが行える便利なサービスです。
https://aws.amazon.com/jp/glue/features/databrew/

DevelopersIOでもいくつか記事が投稿されていますのでチェックしてみてください。
https://dev.classmethod.jp/tags/glue-databrew/

私自身聞いたことがある程度で実際に触ったことはなかったので、今回は生成AI君に吐き出してもらった擬似的な会員登録データを用いてより業務内容に近いリアルなクレンジング処理がどの程度できるか検証していきたいと思います。

用意したサンプルデータ

データセットはCSVで架空ECサイトの会員登録情報を想定して以下の9つの列で構成しました
今回は概念実証レベルなので25行のレコードを用意しました。

顧客ID,氏名,電話番号,メールアドレス,郵便番号,住所,登録日,最終購入日,会員ランク
1001,山田　太郎,080-1234-5678,yamada.t@example.jp,100-0001,東京都千代田区千代田99-99-99,2020/04/15,2023-01-20,ゴールド
1002,佐藤 花子,09023456789,sato.h@example.com,150-0002,東京都渋谷区渋谷88-88-88,2019-08-23,22/12/05,シルバー
1003,鈴木　一郎,+81 70-3456-7890,suzuki.i@example.ne.jp,220-0003,神奈川県横浜市西区高島77-77-77,21/2/10,2023/02/14,ブロンズ
1004,田中 美咲,080 4567 8901,tanaka.m@example.jp,530-0001,大阪府大阪市北区梅田66-66-66,18-11-30,2023/01/31,ゴールド
1005,佐藤　健太,+8190-5678-9012,sato.k@example.com,060-0001,北海道札幌市中央区北一条55-55-55,2022年06月18日,22-10-22,
1006,伊藤 さくら,070.6789.0123,ito.s@example.jp,810-0001,福岡県福岡市中央区天神44-44-44,20/9/5,2023年2月1日,シルバー
1007,山田　太郎,0807890-1234,yamada.taro@example.ne.jp,980-0001,宮城県仙台市青葉区中央33-33-33,2019-05-12,2022/11/15,ブロンズ
1008,中村 翔太,+81(90)8901-2345,nakamura.s@example.jp,460-0001,愛知県名古屋市中区三の丸22-22-22,July 20, 2021,Jan 5, 2023,シルバー
1009,小林　明美,070-9012-3456,kobayashi.a@example.com,650-0001,兵庫県神戸市中央区加納町11-11-11,2018/03/28,2022/12/24,ゴールド
1010,加藤 大輔,08001234567,kato.d@example.jp,850-0001,長崎県長崎市万才町99-99-99,2022-01-10,2022/09/30,
1011,渡辺　麻衣,+81-90-1234-5678,watanabe.m@example.ne.jp,900-0001,沖縄県那覇市港町88-88-88,11/11/2020,11-Feb-2023,シルバー
1012,斎藤 健,07023456789,saito.k@example.jp,010-0001,秋田県秋田市中通77-77-77,24-12-19,15/08/2022,ブロンズ
1013,高橋　由美,080 3456 7890,takahashi.y@example.com,420-0001,静岡県静岡市葵区追手町66-66-66,2021.03.03,2023.01.23,シルバー
1014,田中 太郎,090-4567-8901,tanaka.t@example.jp,730-0001,広島県広島市中区白島北町55-55-55,7/7/18,12-Dec-22,ゴールド
1015,伊藤　誠,070 5678 9012,ito.m@example.ne.jp,780-0001,高知県高知市本町44-44-44,2022/04/30,2022/11/03,
1016,山本 裕子,+81-80-6789-0123,yamamoto.y@example.jp,930-0001,富山県富山市明輪町33-33-33,8.8.2020,5.2.2023,シルバー
1017,中村　和彦,090.7890.1234,nakamura.k@example.com,320-0001,栃木県宇都宮市馬場通り22-22-22,10-Oct-2019,10/10/2022,ブロンズ
1018,小林 明美,0708901-2345,kobayashi.akemi@example.jp,640-0001,和歌山県和歌山市七番丁11-11-11,2021/01/15,2023-01-18,シルバー
1019,加藤　美咲,+81 80 9012 3456,kato.misaki@example.ne.jp,880-0001,宮崎県宮崎市橘通西99-99-99,20-May-18,30/12/2022,ゴールド
1020,井上 大輔,090-0123-4567,inoue.d@example.jp,020-0001,岩手県盛岡市内丸88-88-88,2022.02.22,2022.09.15,
1021,山田太郎,080-1111-2222,yamada.t2@example.jp,100-0002,東京都千代田区丸の内77-77-77,15/05/2021,25-Jan-23,ゴールド
1022,鈴木一郎,0902222-3333,suzuki.i2@example.com,220-0004,神奈川県横浜市西区みなとみらい66-66-66,Sep 23, 2020,Dec 15, 2022,
1023,佐藤花子,+81-70-3333-4444,sato.h2@example.ne.jp,150-0003,東京都渋谷区恵比寿55-55-55,2022.3.10,2023.2.4,ブロンズ
1024,田中　美咲,080 4444 5555,tanaka.m2@example.jp,530-0002,大阪府大阪市北区曽根崎44-44-44,30/10/19,21/01/2023,ゴールド
1025,渡辺麻衣,+81(90)5555-6666,watanabe.m2@example.com,900-0002,沖縄県那覇市泉崎33-33-33,18-Jul-21,22-Nov-22,

列情報

データ品質の課題

このデータセットには、以下のような一般的なデータ品質の問題を意図的に含めました。

1. 氏名の不統一

   • 全角スペース（「山田　太郎」）と半角スペース（「佐藤 花子」）の混在
   • スペースなしの氏名（「山田太郎」）の存在

2. 電話番号のフォーマット不統一

   • ハイフンあり: 080-1234-5678
   • ハイフンなし: 09023456789
   • 国際形式: +81 70-3456-7890
   • スペース区切り: 080 4567 8901
   • ドット区切り: 070.6789.0123
   • 括弧付き: +81(90)8901-2345
   • 混合形式: 0807890-1234

3. 日付形式の不統一

   • YYYY/MM/DD: 2020/04/15
   • YYYY-MM-DD: 2019-08-23
   • DD/MM/YY: 22/12/05
   • YY/M/D: 21/2/10
   • DD-MM-YY: 18-11-30
   • 日本語表記: 2022年06月18日
   • 英語表記: July 20, 2021
   • ドット区切り: 2021.03.03

4. 欠損値

   • 5件のレコードで会員ランクがNull

5. 重複レコード

   • 同姓同名の顧客が複数存在（異なる住所とメールアドレス）
   • 「山田太郎」が3件（ID: 1001, 1007, 1021）
   • 「小林明美」が2件（ID: 1009, 1018）

検証目標

このデータセットに対してDataBrewを用いて、以下のクレンジングに加えて追加の新指標を算出し目標通りの結果が得られるか検証します。

クレンジング目標

1. 氏名を「姓」と「名」に列分割
2. 電話番号の国際形式（+81）への統一
3. 日付形式のYYYY-MM-DD形式へのフォーマット
4. 会員ランクの欠損値処理 無効な値&Null部分を未設定に変更
5. 重複レコードの検出とフラグ付け

プロジェクトの作成

まずはS3の適当なバケットにサンプルデータセットをアップロードします

続いてAWSマネジメントコンソールのGlue DataBrewのページから「プロジェクト」 > 「プロジェクトを作成」を選択し
以下の設定で作成します。

• プロジェクト名 「CustomerDataCleansing」
• レシピ名 「CustomerDataCleansing-recipe」
• 「新しいデータセット」からデータセット名「sample-customer-data」
• 事前アップロード済みのS3のソースを入力しCSVファイルを参照
• ファイルタイプ「CSV」,「カンマ区切り」,「最初の行をヘッダーとして扱う」にチェック
• 新規作成IAMロール 「DataBrew-CustomerCleansing-Role」

設定を行い「プロジェクトを作成」を押すと、数秒ほどでデータセットが確認できるようになります。

クレンジング処理

以下の簡易クレンジング処理を試してみました。ビジュアルベースでサクッと適用できるので楽ですね。

1. 氏名を「姓」と「名」に列分割

まず「氏名」列から３点リーダーを選択し、「列を分割」 > 「複数の区切り記号」を選択します。

今回は半角スペース、全角スペースで氏名が区切られているので区切り記号を追加して欄にそれぞれを入力します。

入力後のプレビューを確認します。

「半角スペース」,「全角スペース」で区切られた氏名を列分割できていますね。ただ、区切りがない氏名の列分割に関しては「氏名_2」列が「Null」となり、「氏名_1」欄に全文が集約されました。何かしら指標となる区切り文字がないと列分割を行うことは難しそうですね。その後変更を適用し、結果はプレビュー通りとなりました。

2. 電話番号の国際形式（+81）への統一

続いて、電話番号列の国際形式へのフォーマット化を図ってみます。
「電話番号」列から３点リーダーを選択し、「フォーマット」 > 「電話番号」 > 「+CCNNNNNNNNNN」を選択します。
このやり方だと国際形式へのフォーマットしかできないっぽいですね

その後ソース列、列を形式化、電話番号の形式を確認し最後に国を「日本 +81」を選択して変更のプレビューをかけます
※UIが崩れているのはご容赦ください

基本的に 10 or 11桁 の電話番号羅列と認識できるものはうまくフォーマットしてくれていますが「090-0123-4567」だけ変換されませんでした。私も初めて知りましたが、090の後に0が続く電話番号は存在しないらしい(0800はあり)です。なので電話番号と認識されなかったぽいですね。ちなみに「0120」のフリーダイヤルも海外にて認識できないので別データにて試しましたが、フォーマットできませんでした。

3. 日付形式のYYYY-MM-DD形式へのフォーマット一

日付列も同様に「YYYY-MM-DD」形式へフォーマット化してみます。日付に関する列が２つあるので同様の操作を行います。
「登録日」列から３点リーダーを選択し、「フォーマット」 > 「日付時刻の形式」 > 「yyyy-mm-dd」を選択します。

ソース列、列を形式化、日付/時刻形式を選択　欄を確認しすべての行に対して変更後プレビューを確認します。

結果としては日付の方が電話番号より精度が低い印象でした。「/」スラッシュ区切りなどは正しくフォーマットしてくれてますが、2020の下2桁のみ表示(20/9/5)や「.」ドット区切り、また「年月日」など日本語表記等は正しくフォーマットされませんでした。ISO標準なので日本語特有の表記などは難しそうですね。逆にOctやJulyなどは正しく変換してくれていました。　

4. 会員ランクの欠損値処理 Null部分を未設定に変更

今回は会員ランク列に値を設定していない行、会員ランクになぜか「Jun 5」などの日付が入っている行を用意しています。これらの無効値を会員ランク「未設定」になるように変換してみます。列の値変換はエクセルなどと同じように色んなやり方があると思われますが今回は以下のステップを踏むことにします。

1. ブロンズ、シルバー、ゴールド　以外の全ての値を一度「削除」という値に変換します
2. 「削除」という値をnullにします。複数の値を指定して一気にnullにできないためこのフローを踏みます
3. 「欠落した値の削除または入力」から「カスタム値で埋める」を選択し、nullの値を一気に「未設定」に変換します

まずは「Jan 5」や「Dec 15」などの複数の無効値を一気に「削除」という値に置き換えます。本来ならここで「未設定」にすれば良いかもしれませんが、今回は一度全て決められている値(ブロンズ、シルバー、ゴールド)以外を「null」にするという要件もありそうなので、その工程も踏んでみたいと思います。
まず「会員ランク」列の３点リーダーから「クリーン」 > 「値またはパターンの置き換え」を選択します。

その後、「置き換える値」に正規表現を選択しシルバー、ブロンズ、ゴールド以外全ての値が「削除」となるように設定し、プレビューを確認し適用します。

^(?!(ゴールド|シルバー|ブロンズ)$).+

無効な値が「削除」になったので今度は全ての「削除」の値を「null」に設定します。
３点リーダーから「クリーン」 > 「カスタム値」を選択します。

カスタム値から今度は文字列で「削除」を入力しプレビューを表示すると全て「null」になっていることが確認できました。その後適用します。

最後に「null」値を「未設定」という文字列に変換します。３点リーダーから「欠落した値の削除または入力」 > 「カスタム値で埋める」を選択します。

欠落している値(null)が「未設定」という文字列になるように設定すれば適切に変換が実行されることが確認できました。

5. 重複レコードの検出とフラグ付け

クレンジング処理の最後に重複レコードの検出とフラグ付けの一例を行なってみたいと思います。
今回は「氏名」と「電話番号」が同じ場合それは重複している行と定義します。住所や会員ランクが変更され、前のレコードが残っているシチュエーションですね。
今回のデータだと「氏名 = 山田太郎」かつ「電話番号 = +819012345678」が重複しています。重複している行に「true」,「false」列を追加してみます。

今回は選択した任意の複数列を参照し、重複かどうかの定義を行います。ヘッダーの「重複」 > 「重複する行にフラグを付けます」だと全ての列値が重複している場合のみしか判断してくれません。

今回は「氏名_1」「氏名_2」「電話番号」列をマージした新しい列を作成するために　「作成」 > 「関数に基づく」を選択します。
※「マージ」という項目がありますが、これはマージ前の列を削除してしまいます。

ソース列にて「氏名_1」「氏名_2」「電話番号」を選択し、送信先列「Name_Phone」とし(英数字のみしか不可)、変更を適用します。

その後ヘッダーから「作成」 > 「列にフラグを立てる」を選択します。

列オプションに「フラグ値」を設定し、 「列の重複する値を削除」を選択 します。

ソース列を先ほど作成した「Name_Phone」に設定し、フラグ値を「True または False」、送信先列を「Name_Phone_flagged」に設定してプレビューを確認すると、２回目、３回目に重複した行のフラグが「True」になっていることが確認できました。

結果まとめ

1. 氏名を「姓」と「名」に列分割
   • 区切り文字が入っている場合は適切に分割可能
2. 電話番号の国際形式（+81）への統一
   • 国際形式の電話番号として不適当なデータはフォーマット化不可能。「090-0」で始まるものや「0120」など
3. 日付形式のYYYY-MM-DD形式へのフォーマット
   • 日本語特有の「年月日」表記や「. ドット」繋ぎの場合は
4. 会員ランクの欠損値処理 無効な値&Null部分を未設定に変更
   • 欠損値だけなら簡単に変換可能
   • 不正なデータが混入している場合でも変換と置換で対応可能
5. 重複レコードの検出とフラグ付け
   • 全ての列が重複している場合は簡易的に設定可能
   • 任意の複数列などから「重複」を定義する場合は一工夫が必要

データ出力に関して

変換後のデータはCSVとして出力できたり、「ジョブを作成する」からS3バケットに保存したりすることが可能です。

また今まで行った変更手続きをレシピとして出力し同じ一連の手続きを再利用できる仕組みもあります。

ビジュアルエディタ右側のバーからレシピの「発行」を選択します。

今まで「氏名」や「電話番号」に対する変換ステップが表示されていることを確認し発行する

Glue DataBrew コンソール画面の「レシピ」にて発行されていることを確認

最後に

今回はチュートリアル的に疑似会員データのクレンジング処理をビジュアルエディタにて行ってみました。このほかにも個人情報のマスキング処理やデータセットを一元的に統計してくれるプロファイル機能などまだまだ機能が豊富そうです。手動で変換するだけでなくGlue Jobから作成したレシピを用いてデータの事前処理を自動で行ったりなども可能そうなので今後もっと試して行きたいです。今回は以上です。