ETLのT(Transformation)実践

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

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

https://qiita.com/taka_yayoi/items/cba8021d5f368b3c77bb

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

https://qiita.com/taka_yayoi/items/22e0aa3194848d596198

# ETLとは

ETLとは、

- **E**xtract(抽出): ソースシステムからのデータの取り込み
- **T**ransformation(変換): ターゲットシステムに投入する前に必要な変換処理を適用
- **L**oad(ロード): ターゲットシステムへのロード

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

![](https://www.databricks.com/wp-content/uploads/2021/05/ETL-Process.jpg)

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

https://www.databricks.com/jp/glossary/extract-transform-load

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

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

https://aws.amazon.com/jp/what-is/etl/?nc1=h_ls

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

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

https://qiita.com/taka_yayoi/items/08aeeac8f63a2f8158eb

# 変換処理の実践

## データの準備

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

https://generatedata.com/

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

```sql: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",
    "ultricies@yahoo.net",
    3,
    "M2 3PK",
    "Hampshire",
    "6901 Donec Avenue",
    "(777) 977-8928",
    "Duis risus odio, auctor vitae, aliquet nec, imperdiet nec, leo.",
    "Netherlands",
    "9",
    "WFC54KYL9VU",
    "$57.96"
  ),
  (
    "Cole Weber",
    "etiam.laoreet.libero@hotmail.com",
    6,
    "982197",
    "Ryazan Oblast",
    "Ap #728-7530 Diam Rd.",
    "(835) 954-8753",
    "rutrum urna, nec luctus felis purus ac tellus. Suspendisse sed",
    "Brazil",
    "1",
    "HIA41GBL9RT",
    "$63.15"
  ),
  (
    "Aidan Burch",
    "lacus.etiam.bibendum@outlook.net",
    5,
    "8052",
    "Kiên Giang",
    "1998 Nisl. St.",
    "(383) 937-3521",
    "pede. Nunc sed orci lobortis augue scelerisque mollis. Phasellus libero",
    "Mexico",
    "9",
    "WWG84MSH3FO",
    "$22.17"
  ),
  (
    "Portia Solomon",
    "amet@yahoo.edu",
    7,
    "50705",
    "Chiapas",
    "726-8739 Tellus, St.",
    "(766) 881-5712",
    "ipsum nunc id enim. Curabitur massa. Vestibulum accumsan neque et",
    "Netherlands",
    "5",
    "INJ39LDD8GR",
    "$24.38"
  ),
  (
    "Erasmus Conner",
    "nibh.sit.amet@yahoo.couk",
    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"
  );
```

:::note
**注意**
このクエリーには`$`が含まれているため、ノートブックで実行すると期待した通りに動作しません。クエリーエディタで実行します。[こちら](https://community.databricks.com/t5/data-engineering/databricks-sql-unable-to-escape-dollar-sign-in-column-name/td-p/8608)にあるように対応される予定です。
:::

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

```sql:SQL
INSERT INTO
  takaakiyayoi_catalog.etl_in_action.bronze (
    `name`,
    `email`,
    `numberrange`,
    `postalZip`,
    `region`,
    `address`,
    `phone`,
    `text`,
    `country`,
    `list`,
    `alphanumeric`,
    `currency`
  )
VALUES
  (
    "Gareth Robbins",
    "ultricies@yahoo.net",
    3,
    "M2 3PK",
    "Hampshire",
    "6901 Donec Avenue",
    "(777) 977-8928",
    "Duis risus odio, auctor vitae, aliquet nec, imperdiet nec, leo.",
    "Netherlands",
    "9",
    "WFC54KYL9VU",
    "$57.96"
  );
```
以下のようにテーブルが作成されました。
![Screenshot 2023-11-25 at 19.08.17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/b000fe04-3e45-6f94-5e9d-7c8fbd3b1239.png)

## 変換戦略の立案

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

ここでは以下の変換をかけます。[こちらの記事](https://aws.amazon.com/jp/what-is/etl/?nc1=h_ls)のいくつかの変換処理を実行します。なお、すべてSQLで実行します。下に行くほど難しいかと。

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

## 重複レコードの排除

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

```sql:SQL
SELECT
  DISTINCT *
FROM
  takaakiyayoi_catalog.etl_in_action.bronze;
```
重複が排除されました。
![Screenshot 2023-11-25 at 19.14.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/577ded0f-7eb5-75a5-11f4-604f4839d552.png)

## 不要列の除外

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

```sql:SQL
SELECT
  DISTINCT name,
  email,
  phone,
  country,
  region,
  address,
  postalZip,
  currency
FROM
  takaakiyayoi_catalog.etl_in_action.bronze;
```
![Screenshot 2023-11-25 at 19.16.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/d5de25b9-d6b3-dfdd-3411-d8164311ef6d.png)

## 姓名を別の列に分離

[split関数](https://docs.databricks.com/ja/sql/language-manual/functions/split.html)を使います。戻り値が配列になるので、[添字を指定](https://ganeshchandrasekaran.com/databricks-how-to-split-a-string-using-spark-sql-ada34ec2de82)して性と名それぞれの列に分割します。

```sql: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;
```
![Screenshot 2023-11-25 at 19.21.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/6e621b8c-ca38-d68b-5a5e-47e8f1e0b474.png)

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

`$`を除外した上で数値型に変換します。先頭文字を除外するので[substring関数](https://docs.databricks.com/ja/sql/language-manual/functions/substring.html)を使います。戻り値は数値型に変換可能な形になっているので、型の[cast](https://docs.databricks.com/ja/sql/language-manual/functions/cast.html)を行います。

```sql: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;
```

数値型に変換されました。
![Screenshot 2023-11-25 at 19.29.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/3649121c-fb70-d625-02c7-c1c7a5a91628.png)

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

最後の処理です。正規表現を使うので、[regexp_replace関数](https://docs.databricks.com/ja/sql/language-manual/functions/regexp_replace.html)を使います。

```sql: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;
```

匿名化されました！
![Screenshot 2023-11-25 at 19.34.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/123725de-7711-ee34-388e-816a64f77c8d.png)

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

この結果は、[メダリオンアーキテクチャ](https://www.databricks.com/jp/glossary/medallion-architecture)におけるシルバーテーブルに該当するので、テーブルに書き込みます。CTAS(Create Table As Select)を使います。

```sql: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;
```
シルバーテーブルの完成です！
![Screenshot 2023-11-25 at 19.36.50.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/0fb2eb54-bffa-9d52-b8c9-81ca886f7128.png)

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

# まとめ

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


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

[Databricksクイックスタートガイド](https://www.amazon.co.jp/dp/B09V1YXFVQ/)


### Databricks無料トライアル

[Databricks無料トライアル](https://databricks.com/jp/try-databricks)