データ分析基盤を構築する際に、ETL処理は欠かせないものです。ETL処理とは、Extract（抽出）・Transform（変換・加工）・Load（保管）の略で、データが使いやすいように加工する一連の処理を指します。データ分析基盤の構築の中で、実はこのETL処理の開発が一番時間が掛かるといっても過言ではありません。また、一度作ったらお終いではなく、分析する対象・方法が変わるたびに修正を加える必要があります。そのため、ETL処理は人にお任せではなく、自前で作れるようにした方が良いです。
ETLとELT
先程、自分の組織でETL処理を作れるようにするのが大事と言いましたが、そのためにはETL処理を一部の人間ではなく、できるだけ多くの人で修正できるようにすることが大切です。そのため、GUIよりETL処理を行えるようなツールも使うのも選択肢の一つだと思います。それ以外にお勧めとしては、ELT処理というのものがあります。ELT処理はExtract⇒Load⇒Transformと処理の順番を変えた略語ですが、特にBigQueryやRedshiftなどのDWHと併用することが多いです。最初にDWHにデータをロードして、SQL処理でTransformするという形です。
ここで敢えて順番を変えて、Transformを最後に持っていくのでしょうか？実はETL処理の実装コストの中で、Transformが大部分を占めます。通常のETLの実装では、Transformをプログラムで実装することになります。これに対して、ELTはSQLで実装することが多いです。プログラムの習得はなかなか難しいですが、SQLであれば専門の開発者じゃなくてもなんとかなる場合が多いです。また開発者がいるとしても、複雑な処理をしないのであれば、プログラムの実装よりSQLで処理をする方が楽です。ということで、データ変換処理の実装処理としてELTはなかなかお勧めです。
UPSERTとは？
ELT処理を実装していく上で、頻出するのがUPSERT処理です。これは造語なのですが、既存のテーブルに、既にデータがある場合はUpdateで更新、データがない場合はInsertで新規追加という処理です。MySQLで言うところの「INSERT ... ON DUPLICATE KEY UPDATE 構文」や、PostgreSQLの 「ON CONFLICT (id) DO UPDATE SET構文」です。Merge ステートメントとも呼ばれるようです。これが使えるとELT処理はかなり楽になります。
DWHとして利用される事が多いBigQueryでは、MERGEステートメントが使えます。Redshiftの場合、中間テーブルを使ってInsert処理とUpsert処理を分けることで代替ができます。問題となるのが、Athenaです。AthenaはETLの専用サービスであるGlueと統合できます。Athenaを使ってELT処理を実現するのは結構良い選択肢と思っています。簡単な処理であるうちはAthenaで行い、処理が複雑になってきた場合はGlueでSparkやPython Shellに実装しなおすというのも手です。
Athenaは、Insert機能は実装されたものの、Update機能はサポートされていません。Update機能がサポートされていないので、当然Upsert機能もありません。では、どうしたらいいのでしょうか？
AthenaにおけるUpsert的な機能の実装方法
実装方法については、いろいろあります。例えば、既存テーブルと更新データがあるとして、主キーを元に操作する方法です。 ①更新データに含まれていないデータのみをSelectし、既存テーブルから新規テーブルにInsertする ②新規テーブルに更新データをInsertする
テーブル名が同じ必要であるのであれば、最後に既存テーブルのデータを全部削除し、新規テーブルの内容を既存テーブルにInsertしなおすといった方法です。
この辺りのロジックはいろいろ方法ありますので、データ量などを考えて最適な処理を選ぶといいでしょう。なんかCobolのアーキテクチャの更新処理を考えているようで結構楽しくなることは必定です。処理課金であるAthenaのコスト面が気になるかもしれませんが、データ量にもよりますが、Glueで処理つくるのとあまり変わらない場合も多いです。
まとめ
データ分析基盤の実装の肝は、ETL処理です。素直にプログラムで実装するのも一つの手ですが、SQL的な処理で実装するのも検討してみましょう。そのような処理は、一部界隈ではELTと呼ばれています。そして、ELTの実装方法として、S3のデータを直接SQLとして扱えるAthenaは中々おすすめです。ただ、AthenaはUpdateをサポートしていないので、Upsert的な処理を実装するには知恵と勇気が必要です。そこは腕の見せどころなので、ぜひお試しあれ。 ※そのうちUpdate処理もできるようになるのではと期待しています。
