初めに

レガシーシステムのOracleDBデータをBigQueryへETLする必要があり、GCPのDataflowでETLをしましたが、
日本語のデータが文字化けしていたので、その回避した経験を記述します。

簡単に言えば

文字列byte化してクエリ、BigQueryへBYTES型で格納
BigqueryのUDFを設定してBYTEからcp932形式の文字変換

処理順番

Oracle クエリ

SELECT
    UTL_RAW.CAST_TO_RAW(NAME) AS NAME,
    ...
    other_columns
FROM MEMBERS
WHERE condition

Dataflowの設定

Dataflow 提供テンプレートの JDBC to BigQueryで簡単にETLの設定ができる
※ 各設定値はサンプルです。

UDF(user defined function)の設定とクエリ

BigQueryでは、SQL式またはJavaScriptコードを使ってクエリで利用する関数を作成できる。

文字変換ライブラリ iconv-lite

こちらのライブラリはこれは jsファイルを提供してないので webpack でパッケージングする必要がありました。
※ webpackでパッケージングするとき参考にした記事：https://dev.classmethod.jp/articles/bigquery-javascript-udf/
パッケージングしたjsファイルはGCPプロジェクトのクラウドストレージにアップロードします。

UDF定義とクエリ実行

CREATE TEMP FUNCTION conv_test(x ARRAY<INTEGER>)
RETURNS STRING
LANGUAGE js AS """
return iconv_for_udf.decode(x, "CP932")
"""
OPTIONS (
  library = ["gs://project_gcp_storage/iconv-lite_bq-udf.js"]
)
;

select 
    NAME,
    conv_test(TO_CODE_POINTS(MEMBERID)) as converted_name
FROM `project.dataset.MEMBERS`

※ iconv-liteのdecode関数は buffer を引数として扱うのでクエリでBuffer Arrayに変換してUDFを呼び出す必要があった
iconv-liteの例(from iconv-lite github)

var iconv = require('iconv-lite');

// Convert from an encoded buffer to a js string.
str = iconv.decode(Buffer.from([0x68, 0x65, 0x6c, 0x6c, 0x6f]), 'win1251');

// Convert from a js string to an encoded buffer.
buf = iconv.encode("Sample input string", 'win1251');

// Check if encoding is supported
iconv.encodingExists("us-ascii")

結果

まとめ

やり方、色々あると思いますが、チーム内のシステムに適用するための方法を探す中で一番簡単にできそうなやり方でした。
辿り着くまでは大変だったが、完成した流れは簡単でシンプルでした。
レガシーシステムのETLがまた今後あるかは別にして、なかなか良い勉強でした。

OracleDBからBigQueryへのETLで文字化けを解決する

初めに