cocone engineering

こんにちは！
開発本部BI室でデータエンジニアを担当しているKです。

初めに

BI室では色んなサービスDBからBigQueryへDWH(Data Ware House)としてETLを行っています。
そのなかで、レガシーシステムのOracleDBデータをBigQueryへETLする必要があり、GCPのDataflowでETLを行った際に日本語の文字列データが文字化けしていた現象があったため、それを回避した経験を記述します。
 

簡単に言えば

文字列をbyte化してクエリ、BigQueryへBYTES型で格納します。
BigQueryではUDFを設定してBYTEからcp932形式で文字変換し、また別のテーブルに格納する処理をしました。
 

処理内容

DataflowのJDBCテンプレートから参照するため、OracleドライバーをGCSにアップロードします。
今回は弊社のDBに合わせて、ojdbc8.jar (Implements JDBC 4.2 spec and certified with JDK8 and JDK11) をダウンロードしました。
 

1. Dataflowの設定

DataflowのJDBC to BigQueryのテンプレートを利用しました。
 

 
テンプレートを選択すると追加のパラメータを入力します。
 

 
こちらで文字化けになっていたコラムのデータはUTL_RAW.CAST_TO_RAWで変換してクエリします。

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

ウェブコンソールではイメージのようですが、実際はAPIを使って複数のテーブルをETLする処理をしています。
 
BigQueryへ格納するデータは次のようにBYTES型で格納されます。
 

 

2. UDF(user defined function)の設定

BigQueryでは、SQL式またはJavaScriptコードを使ってクエリの中の関数として利用できます。
※ JavaScript UDF
https://cloud.google.com/bigquery/docs/user-defined-functions?hl=ja#javascript-udf-structure
 
UDFを作成する前に必要なライブラリも準備します。

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

Pure JS character encoding conversion – https://www.npmjs.com/package/iconv-lite
日本語の色んな文字コードの変換に対応しているJSライブラリですが、
こちらのライブラリは .jsファイルを提供してないので、webpackでパッケージングする必要があります。
※ webpackでパッケージングするとき参考にした記事
https://medium.com/swlh/how-to-package-a-javascript-library-for-use-in-bigquery-2bf91061f66f
パッケージングしたjsファイルは同じGCSにアップロードします。
webpackで設定したlibrary名がそのままUDFでも使えます。
 
次のようにライブラリを使ったUDFを作成しました。

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

引数としてBYTES型を渡し、デコーディングしてくれる簡単な処理です。
 

3. UDF定義とクエリ実行

定義したUDFをDataflowでETLしたテーブルをクエリしてみます。

SELECT 
    NAME,
    conv_test(NAME) AS converted_name
FROM `project.dataset.MEMBERS`

結果

次のように正しく変換されるのが分かります。
 

 

まとめ

やり方、色々あると思いますが、チーム内の既存システムに準拠して適用するための方法を探す中で一番簡単にできそうなやり方でした。
辿り着くまでは大変でしたが、完成した流れは簡単でシンプルでした。