Zenn
DATUM STUDIOPublicationへの投稿
🏃‍♂️

dbtで大量のカラムの値をunicode正規化する

yskin
2024/12/10に公開
Snowflake
DataEngineering
dbt
tech

はじめに

源泉システムからデータをDWHに投入後、まずクレンジング処理をすることが多いことと思われます。
そこでDWHやBI内での検索性向上に向けて、行いたいとあげられることの１つとしてカラムの値の半角統一や正規化があげられます。

具体的には

  • 半角統一
    • 文字：例 ＡＢＣ→ABC
    • 記号：例 （）→()
  • 正規化(下の例はunicode正規化)
    • 例：ｶﾀｶﾅ→カタカナ
      といったところでしょうか？
      カラムに日本語の値が入ってこない場合は半角統一を、日本語の値が入ってくる場合はunicode正規化を実施されるケースも多いでしょう。

この記事では半角統一や正規化をする１例を見ていきます。基本的にはdbtのSQLのみで解決するのが理想と考えています。UDF等dbtで管理できないものを使うと、terraformや手動でのデプロイが発生し、リポジトリの構成次第ではやや面倒な形になります。dbtのSQLで解決できない場合は他の手段を使うという方針で考えております。（ここで紹介する他にもやり方はあるかと思います～）

1. 半角統一

やや古典的なやり方ですが、以下のようなマクロを作成します。
アルファベットと記号、数字は網羅できているでしょう。

{%- macro convert_normalize(column_name) -%}
    {%- set src = "！＂＃＄％＆＇（）＊＋，－．／０１２３４５６７８９：；＜＝＞？＠ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ［＼］＾＿｀ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ｛｜｝～　" -%}
    {%- set tgt = "!\"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~ " -%}
    trim(translate("{{ column_name }}", $${{ src }}$$, $${{ tgt }}$$ ))
{%- endmacro -%}

これを以下のように使用できます。

select
    {{ convert_normalize(column_name) }} as column_name
from
    {{ ref('table_a') }}

この方法以外にも下記の例であるようなUDFをつくる方法もあるかと思いますが、
なるべくdbt(SQL)のみで完結させようとなるとこの方法が一番良いでしょう。

2. カタカナonly全角統一

もし半角カタカナ→全角カタカナの変換だけを実施したい場合は、上のマクロを改変する形で以下のようなものはいかがでしょうか？

{%- macro convert_katakana_normalization(column_name) -%}
    {%- set src = "ｧｱｨｲｩｳｪｴｫｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝﾞﾟ" -%}
    {%- set tgt = "ァアイィイゥウェエォオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲン゛゜" -%}
    translate({{ column_name }}, $${{ src }}$$, $${{ tgt }}$$)
{%- endmacro -%}

ちょっと見苦しいですが、やりたいことはできそうです。ただ数字や記号、アルファベットを含めた、unicode正規化をやりたいという場合、この方針でsrcやtgtを足していくとやや可読性も厳しくなるでしょう。

3. unicode正規化その1 (Python UDF)

これもよくあるやり方かなと思いますが、SnowflakeではPython UDFが扱えるので
事前にSnowflake側でudfを定義しておきます。

create or replace function unicode_normalize("x" varchar(16777216))
returns varchar(16777216)
language python
runtime_version = '3.11'
handler = 'norm'
as '
import unicodedata

def norm(x):
    if x is NONE:
        return NONE
    return unicodedata.normalize("NFKC",x)
';

そしてdbtで呼び出します。ここではとりあえず複数カラムに対して正規化を実行したかったのでdbtのマクロdbt_utilsのget_columns_in_relation関数を用いて、for文で回すことにしました。

{% set import_cols = dbt_utils.get_filtered_columns_in_relation(from=ref('table_a'), except=[]) %}

select
{% for col_name in import_cols -%}
    unicode_normalize({{ col_name }}) as {{ col_name }}
    {% if not loop.last %} , {%- endif -%}
{% endfor %}
from
    {{ ref('table_a') }}

カラム数の多くないテーブルだと特にエラーがなく終了したのですが、、、
カラム数が100を超えるような場合だと以下のようなエラーがでました。

too many python functions in the query

具体何個以上の関数の実行がNGといった数字はドキュメントには示されていませんが、限界が来てしまったようです。

ちなみにadapter.get_columns_in_relationを用いて、inputテーブルのカラムの型を取得し、文字型のみに適用することも試し、udfを実行する回数を減らしてもダメでした。

4. unicode正規化その2 (Pythonモデル for 大量カラム)

幸いdbtではpythonモデルが使用出来るので、python関数を定義したうえ、dbtモデルを実行することが可能です。
以下は文字型カラムに対して正規化するコードです。

import unicodedata
import pandas as pd

def normalize_column(value, form="NFKC"):
    if value is None:
        return None
    return unicodedata.normalize(form, value)

def model(dbt, session):
    input_df = dbt.ref("source_table").to_pandas()

    # 文字型（文字列）のカラムのみ正規化処理を実施
    for col in normalized_df.select_dtypes(include=[object]).columns:
        normalized_df[col] = normalized_df[col].apply(normalize_column)

    return normalized_df

こちらだと先ほどエラーが出たようなカラムの大きなテーブルでもエラーとなりませんでした。
Snowflakeの裏側に都度複数のPython環境を立ち上げる3のやり方に比べ、１つのprocedureを作成し実行するだけのPythonモデルの処理の負荷が軽いのはなっとくいきますね。

なお上記例ではPandasでよしなに型を判別しています。これは意図しない結果を生む可能性もあるので、注意が必要です。（この件についてはこちらの記事がわかりやすかったです）

終わりに

本記事ではカラムの値を半角統一や正規化をする方法を紹介しました。
ここまで読んでくださりありがとうございました。

DATUM STUDIO
DATUM STUDIOPublication

Discussion