Kettle ツールの使い方

最近、ETL ツールの Kettle を使って、複数データソース間のデータ同期、移行、変換、修正といった機能を調べながら実際に使ってみました。

業務システムを作っていると、こんな要件によく出会います。旧システムのデータを新システムに移したいがテーブル構造が両者で異なる、あるいは複数のデータベースのデータをレポート用データベースに集約したいがフィールド名、ステータスコード、データ型がまるで一致しない、といったケースです。この種の作業をすべて手書きのスクリプトでこなすと、SQL とインポート・エクスポートのプログラムを大量に書くことになり、要件が変わるたびにコードを直す羽目になって保守が大変です。ETL ツールが解決するのはまさにこの問題で、抽出（Extract）、変換（Transform）、ロード（Load）の三つのステップをフロー化・可視化します。Kettle はこの種のツールの中でも代表的なオープンソース実装の一つです。

Kettle は「やかん」という意味で、その名の通り、さまざまなデータソースのテーブルデータを水の流れに見立て、複数の流れを合流・分流・解析するツールです。オープンソースのデータ統合ツールであり、データの抽出、変換、ロード（ETL）など豊富なデータ処理機能を提供します。Kettle の中核は GUI ベースのデザインツールで、ユーザーは簡単なドラッグ&ドロップと接続操作だけでデータ処理フローを構築できます。また、強力なデータ処理エンジンを備えており、マルチスレッドと分散処理をサポートし、大規模なデータを効率よく処理できます。同時に、リレーショナルデータベース、ファイル、Web サービスなど多様なデータソースとターゲットをサポートしており、各種データソースと手軽に統合できます。さらに豊富なプラグイン機構も用意されており、ユーザーが独自のプラグインを開発して Kettle の機能を拡張することも可能です。要するに、Kettle は機能が強力で使いやすく拡張性にも優れたデータ統合ツールであり、データウェアハウス、BI、データ分析などの分野で広く使われています。

動作の仕組み

Kettle には二つの基本概念があります。変換（Transformation）と作業（Job）です。変換は具体的なデータフロー処理を担当し、一つ一つのステップ（Step）をホップ（Hop）と呼ばれる接続線でつないだもので、データは行単位でステップ間を流れていきます。作業はより上位のスケジューリング単位で、複数の変換を順序や条件に従って組織して実行できます。GUI デザイナーで描いたフローは、保存すると一つの XML 記述ファイルになり、デザイナー上で直接実行することも、コマンドラインツールに渡してサーバー上で定期実行することもできます。

各ステップは実行時に独立したスレッドとして動き、上流のステップが一行のデータを生成するたびに下流へ一行ずつ押し出していきます。フロー全体はパイプライン方式で、前のステップの処理がすべて終わるのを待つ必要はありません。これが比較的大きなデータ量を処理できる理由の一つで、データを一度にすべてメモリに載せることがないのです。

プログラミングは一切不要で、コンポーネントを手動でドラッグして設定するだけで、複雑なデータ処理機能を実現できます。CDC の観点で言うと、Kettle はクエリベースの方式でデータの読み取りと変換を行うため、一回限りのデータ移行・変換に適しています。リアルタイム性の要求が高い場面には使えません。

ここで少し補足します。CDC（Change Data Capture、変更データキャプチャ）の実装には大きく二つの考え方があります。一つはログベースで、例えば MySQL の binlog を解析する方式です。データベースで変更が発生するたびに、ほぼリアルタイムでキャプチャできます。もう一つはクエリベースで、定期的に SQL を実行し、タイムスタンプや自動採番主キーを比較して変化したデータを見つけ出す方式です。Kettle は後者に属し、取得できるのはクエリを実行した瞬間のスナップショットです。二回のクエリの間にあった中間状態は感知できず、削除操作も発見しにくいという特性があります。そのため、一回限りの移行や定期的なバッチ同期といった場面に適しており、リアルタイム同期をしたいならログベースの方式に切り替える必要があります。

データ移行の小さな例

Kettle にはかなり多くのコンポーネントが用意されており、さまざまな場面でのデータ転送、インポート、エクスポート、値マッピングなどの機能に対応できます。データを Excel ファイルとしてエクスポートすることも可能です。上図は典型的な移行フローです。ソースデータベースからデータを取得し、途中のいくつかのステップでクレンジング・変換を経て、最後にターゲットデータベースへ書き込みます。以下、フローに登場する順にこれらのよく使うコンポーネントを説明します。

テーブル入力：データベースで SQL を実行して、インポートするデータを取得します。これはフロー全体の起点です。SELECT 文を一つ書けば、クエリ結果の各行がデータフローの一行として下流に渡されていきます。SQL 内では変数によるパラメータ化ができるため、同じ変換を異なる環境で再利用しやすくなります。

テーブル出力：Kettle の実行で得られた最終結果セットをテーブルに出力します。フローの終点で、流れ込んできた各行のデータをターゲットテーブルに INSERT します。バッチコミットの件数を設定でき、一件ずつコミットするよりバッチ書き込みのほうがずっと高速です。

フィールド名の整備：データ列の絞り込みや、列のエイリアス設定などができます。ソーステーブルとターゲットテーブルでフィールド名が一致しないことはよくあります。例えば旧データベースでは user_name、新データベースでは username という場合、このステップで名前を統一しておけば、後続のステップはソーステーブルの命名を気にしなくて済みます。不要な列もここで直接破棄でき、後続ステップの処理量を減らせます。

ソート：データのフィールドに基づいてソートできます。単独ではあまり用途がありませんが、多くの場合は次のステップのためのものです。Kettle のマージ系コンポーネントは通常、二系統の入力が結合キーでソート済みであることを要求するため、マージの前には一般にそれぞれソートしておく必要があります。

データマージ：異なるソースからの二つのデータを結合するもので、MySQL の join 機能に似ています。二つのテーブル入力からのデータフローを指定フィールドで関連付けられるため、別々のデータベースにあるテーブル同士でも「join」できます。これは純粋な SQL ではできないことです。前提は先に述べた通り、両系統のデータをあらかじめ結合キーでソートしておくことです。

値マッピング：多くのデータベースでステータス値 1、2、3 だったステータスコードが、新しいデータベースでは 4、5、6 になっている場合、値マッピングで値の置換ができます。本質的にはコンポーネント内に設定する対照表で、ソース値とターゲット値が一対一に対応し、マッチしなかった場合のデフォルト値も設定できます。

フィールド修正：データソースのフィールド名とデータ型を修正し、新しいデータソースへの移行をしやすくします。典型的なのは、旧データベースでは日付を文字列で保存していたが新データベースでは datetime 型である、あるいは数値の精度を調整する必要がある、といった場面です。すべてこのステップで変換しておけば、ターゲットデータベースへの書き込み時に型エラーが出るのを避けられます。

新規追加・更新：ターゲットデータソースに対して新規追加を実行し、対応する id がすでに存在する場合は更新を行います。いわゆる upsert です。指定したキーフィールドでターゲットテーブルを検索し、見つからなければ挿入、見つかれば更新します。増分同期ではテーブル出力の代わりにこれを使えば、変換を繰り返し実行しても重複データが発生しません。

ハマりどころと注意点

1）中国語の文字化け：データベース接続の文字セットはデータベース自体と一致させる必要があります。MySQL の接続パラメータでは明示的にエンコーディングを指定するのが望ましく、そうしないと移行後に中国語がすべて「?」になっていて、やり直しになります。

2）マージ前のソート忘れ：データマージ系のコンポーネントは入力がソート済みであることに依存します。ソートステップが抜けていても必ずしもエラーにはなりませんが、結合結果が正しくなくなります。この種の問題はエラーより発見しにくいので、必ず結果の行数を照合してください。

3）バッチコミットとトランザクション：テーブル出力のデフォルトのコミット件数は大きくすれば高速化できますが、失敗したときにどうするかを考えておく必要があります。途中でエラーが起きても、すでにコミットされたデータはロールバックされません。再実行する前にターゲットテーブルをクリアするか、「新規追加・更新」コンポーネントに切り替えて冪等性を保証しましょう。

4）大きなテーブルの移行：条件なしの SELECT による全件取得はソースデータベースへの負荷が小さくありません。できるだけ業務のオフピーク時間帯に実行するか、主キーや時刻でバッチに分けて実行しましょう。

まとめ

Kettle をうまく使えば、データベースのデータ管理を簡素化できます。プロジェクトの大きなバージョン変更で、データベース構造や新旧データの互換処理が必要になるとき、Kettle は良い選択肢の一つです。その位置づけは明確で、バッチ処理型のデータ搬送とクレンジングです。GUI によるフローが保守コストを下げ、この種の一回限り・あるいは周期的なデータタスクのために大量の使い捨てスクリプトを書かずに済むようにしてくれます。リアルタイム同期の要件については、ログベースの CDC ソリューションに任せましょう。ツールにはそれぞれの持ち場があります。