ETLのT(Transformation)実践

最近、データエンジニアリングを改めて勉強中です。

こちらの記事でデータエンジニアリングとは何か、なぜ重要なのかを学び、

こちらの記事では実際にETL処理を実行してみました。

ETLとは

ETLとは、

• Extract(抽出): ソースシステムからのデータの取り込み
• Transformation(変換): ターゲットシステムに投入する前に必要な変換処理を適用
• Load(ロード): ターゲットシステムへのロード

の組み合わせなわけですが、E(抽出)はソースシステムとのインタフェースやフォーマット、取り込み形態(バッチやストリーミング、洗い替えなのか差分取り込みなのか)に注意すれば良いわけで、ロードも同様にどのようにターゲットシステムにロードするのかさえ特定すれば良いわけです(とは言っても、実際に実装する際には悩むことはあります)。

こちらの記事でも説明されています。

そして、個人的に鬼門だと思うのがT(変換)だと思うのです。ターゲットシステムにどのような形態で書き込むのかに応じて要件は千差万別です。

良い資料がないかと探していたら、こちらに辿り着きました。ハイレベルですが、変換処理が網羅的に説明されています。

そこで、この記事ではそれらの変換処理を実際に実行してみます。もちろんDatabricksで。

追記 思った以上に抽出処理も大変だったので記事にしました。

変換処理の実践

データの準備

変換処理を実践しようとした際に意外に課題になるのが、どのようにデータを準備するのかかもしれません。実業務のデータでいきなりトライするのもちょっと怖いですし。そう言った際にまたまたたどり着いたのがこちらのサービス。ダミーデータを簡単に作ることができます。

生成されるSQLはそのままだとDatabricksではエラーになるので、以下のように修正しています。

SQL

CREATE TABLE IF NOT EXISTS takaakiyayoi_catalog.etl_in_action.bronze (
  `name` varchar(255),
  `email` varchar(255),
  `numberrange` INTEGER,
  `postalZip` varchar(10),
  `region` varchar(50),
  `address` varchar(255),
  `phone` varchar(100),
  `text` VARCHAR(255),
  `country` varchar(100),
  `list` varchar(255),
  `alphanumeric` varchar(255),
  `currency` varchar(100)
);
INSERT INTO
  takaakiyayoi_catalog.etl_in_action.bronze (
    `name`,
    `email`,
    `numberrange`,
    `postalZip`,
    `region`,
    `address`,
    `phone`,
    `text`,
    `country`,
    `list`,
    `alphanumeric`,
    `currency`
  )
VALUES
  (
    "Gareth Robbins",
    "[e-mail]",
    3,
    "M2 3PK",
    "Hampshire",
    "6901 Donec Avenue",
    "[phone]",
    "Duis risus odio, auctor vitae, aliquet nec, imperdiet nec, leo.",
    "Netherlands",
    "9",
    "WFC54KYL9VU",
    "$57.96"
  ),
  (
    "Cole Weber",
    "[e-mail]",
    6,
    "982197",
    "Ryazan Oblast",
    "Ap #728-7530 Diam Rd.",
    "[phone]",
    "rutrum urna, nec luctus felis purus ac tellus. Suspendisse sed",
    "Brazil",
    "1",
    "HIA41GBL9RT",
    "$63.15"
  ),
  (
    "Aidan Burch",
    "[e-mail]",
    5,
    "8052",
    "Kiên Giang",
    "1998 Nisl. St.",
    "[phone]",
    "pede. Nunc sed orci lobortis augue scelerisque mollis. Phasellus libero",
    "Mexico",
    "9",
    "WWG84MSH3FO",
    "$22.17"
  ),
  (
    "Portia Solomon",
    "[e-mail]",
    7,
    "50705",
    "Chiapas",
    "726-8739 Tellus, St.",
    "[phone]",
    "ipsum nunc id enim. Curabitur massa. Vestibulum accumsan neque et",
    "Netherlands",
    "5",
    "INJ39LDD8GR",
    "$24.38"
  ),
  (
    "Erasmus Conner",
    "[e-mail]",
    0,
    "32281",
    "Southwestern Tagalog Region",
    "P.O. Box 342, 7732 Nulla. Ave",
    "1-576-377-3268",
    "mi, ac mattis velit justo nec ante. Maecenas mi felis,",
    "Norway",
    "5",
    "CHX09SRV8TO",
    "$0.78"
  );

注意
このクエリーには$が含まれているため、ノートブックで実行すると期待した通りに動作しません。クエリーエディタで実行します。こちらにあるように対応される予定です。

重複レコードを作りたいので、以下のクエリーも実行します。

SQL

INSERT INTO
  takaakiyayoi_catalog.etl_in_action.bronze (
    `name`,
    `email`,
    `numberrange`,
    `postalZip`,
    `region`,
    `address`,
    `phone`,
    `text`,
    `country`,
    `list`,
    `alphanumeric`,
    `currency`
  )
VALUES
  (
    "Gareth Robbins",
    "[e-mail]",
    3,
    "M2 3PK",
    "Hampshire",
    "6901 Donec Avenue",
    "[phone]",
    "Duis risus odio, auctor vitae, aliquet nec, imperdiet nec, leo.",
    "Netherlands",
    "9",
    "WFC54KYL9VU",
    "$57.96"
  );

以下のようにテーブルが作成されました。

変換戦略の立案

手元のテーブルを見ながら、最終的にどのような形式のテーブルにしたいのかを考えます。

ここでは以下の変換をかけます。こちらの記事のいくつかの変換処理を実行します。なお、すべてSQLで実行します。下に行くほど難しいかと。

1. データ重複排除: 重複レコードの排除
2. データクレンジング: 不要列の除外
3. 分割: 姓名を別の列に分離
4. データ形式の改訂: 文字列になっているcurrency列を数値に変換
5. 暗号化: メールアドレスの@の前半を匿名化

重複レコードの排除

意図的にレコードを重複させておいてなんですが、まずはレコードの重複排除から行います。SELECTのDISTINCTを使用します。

SQL

SELECT
  DISTINCT *
FROM
  takaakiyayoi_catalog.etl_in_action.bronze;

重複が排除されました。

不要列の除外

SELECTの後で必要な列のみを指定します。

SQL

SELECT
  DISTINCT name,
  email,
  phone,
  country,
  region,
  address,
  postalZip,
  currency
FROM
  takaakiyayoi_catalog.etl_in_action.bronze;

姓名を別の列に分離

split関数を使います。戻り値が配列になるので、添字を指定して性と名それぞれの列に分割します。

SQL

SELECT
  DISTINCT split(name, ' ') [0] AS first_name,
  split(name, ' ') [1] AS sir_name,
  email,
  phone,
  country,
  region,
  address,
  postalZip,
  currency
FROM
  takaakiyayoi_catalog.etl_in_action.bronze;

文字列になっているcurrency列を数値に変換

$を除外した上で数値型に変換します。先頭文字を除外するのでsubstring関数を使います。戻り値は数値型に変換可能な形になっているので、型のcastを行います。

SQL

SELECT
  DISTINCT split(name, ' ') [0] AS first_name,
  split(name, ' ') [1] AS sir_name,
  email,
  phone,
  country,
  region,
  address,
  postalZip,
  cast(substring(currency, 2) AS DOUBLE) AS currency_num
FROM
  takaakiyayoi_catalog.etl_in_action.bronze;

数値型に変換されました。

メールアドレスの@の前半を匿名化

最後の処理です。正規表現を使うので、regexp_replace関数を使います。

SQL

SELECT
  DISTINCT split(name, ' ') [0] AS first_name,
  split(name, ' ') [1] AS sir_name,
  regexp_replace(email, '.*@', 'xxxxx@') AS anonymized_email,
  phone,
  country,
  region,
  address,
  postalZip,
  cast(substring(currency, 2) AS DOUBLE) AS currency_num
FROM
  takaakiyayoi_catalog.etl_in_action.bronze;

匿名化されました！

シルバーテーブルへのロード

この結果は、メダリオンアーキテクチャにおけるシルバーテーブルに該当するので、テーブルに書き込みます。CTAS(Create Table As Select)を使います。

SQL

CREATE TABLE takaakiyayoi_catalog.etl_in_action.silver AS
SELECT
  DISTINCT split(name, ' ') [0] AS first_name,
  split(name, ' ') [1] AS sir_name,
  regexp_replace(email, '.*@', 'xxxxx@') AS anonymized_email,
  phone,
  country,
  region,
  address,
  postalZip,
  cast(substring(currency, 2) AS DOUBLE) AS currency_num
FROM
  takaakiyayoi_catalog.etl_in_action.bronze;

シルバーテーブルの完成です！

通常はこれよりも多いレコード数になりますので、これらを集計することでゴールドテーブルを作る形となります。

まとめ

• 変換処理を実装する際には、生のデータ(ブロンズテーブル)からロードするデータ(シルバーテーブル)をイメージし、どのような処理が必要なのかを洗い出しましょう。
• いきなりすべての処理を実装するのではなく、ステップバイステップで実装していき動作を確認しましょう。
• すべての変換処理が実装できたら、シルバーテーブルにロードしましょう。
• 必要な処理のほとんどはビルトインの関数で準備されていますので、マニュアルにも目を通しておきましょう。

Databricksクイックスタートガイド

Databricksクイックスタートガイド

Databricks無料トライアル

Databricks無料トライアル