MySQL、PostgreSQLのdump csv/tsvを読み書き出来るgemを作った
久々の投稿ですね。
もう気力もだいぶ落ちてきたので投稿も無いかと思ったらネタができたので。
仕事で何をしているのか
まずは仕事内容から。gemも仕事で必要性があって書いたコードなのでここの説明は必要でしょう。
さて、ここ何年も複数の仕事で不定期で解析基盤に関わってきました。
現在の仕事でも0から基盤作りを担当しています(ちょとお休み中だけど)。
環境はWeb側がMySQL、DWH側はRedshiftという構成。
基盤の開発は当然ローカルで行いますが、互換のDBとしてPostgreSQLになります。RedshiftはPostgreSQL互換を謳っているので妥当な選択でしょう。
まあ今時ならよくありそうな構成でしょうか。
問題
ここで問題になるのがデータの移行。MySQLからPostgreSQLへのデータ移行。
まず、標準のcsv/tsvと異なるのでRubyの標準添付のCSVライブラリがそもそも使えません。例外が発生して処理が一切できません。
もう少し言えばRFCでCSVのフォーマットが一応定義されていますが、Wikipediaにあるようにそもそも自然発生的に現れた物なので本来は標準の仕様というのが決めづらいフォーマットでもあります。RubyのCSVライブラリで処理できないcsv/tsvは多数あるでしょう。さらに文字コードから改行コード等考えるべき事は多数あります。
RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files
Comma-Separated Values - Wikipedia
次の問題はMySQLとPostgreSQLのdumpしたcsv,tsvのフォーマットが違うこと。
MySQL
MySQLはきちんとした仕様を探せませんでしたが、このURLにある程度まとまっています
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.6 LOAD DATA INFILE 構文
また、実験をしていて気づいたのですがdump方法によってもフォーマットが異なるようです。
例えば `mysql -e'select * ...'` とした場合、
`LOAD DATA...` でdumpする場合は
と言った具合です。
基本的には`LOAD DATA...` でdump/loadを行うのが仕様的に安全な様に見えます。
また確認はしていないですがバージョンや特殊な型(例えばgeometry等の型では確認をしていません)によっても多少の変化がある可能性はあります。
PostgreSQL
PostgreSQLの場合はこのURLが良い資料でしょう。
https://www.postgresql.jp/document/9.4/html/sql-copy.html
これを読むとMySQLとは異なり基本的にはRFCに従うのが原則のようです。
ただし、RFCではカバーしきれない `NULL` 等の特殊な場合の対応として独自対応が入るようです。
また、MySQLとは異なり `\0` はおそらく扱えない様です。
PostgreSQLでは `COPY` でdump/loadをするのが安全なようです。
その他問題
MySQLとPostgreSQLのテーブル名に使える文字列の最大長が異なる(他のDBの種類でも違います)、仕様上の問題からあるDBにはinsertできるデータが他のDBにはinsertがそもそもできない、等の複雑な問題があるようです。
つまりは完璧にデータ移行自体が不可能な場合が仕様上ありえます。
上記問題をはじめは知らず、sed等で頑張って変換を行おうと頑張りましたが失敗をし、色々なテストや調査から真面目に変換が必要と判断して、仕方なく変換ライブラリを作成するに至りました。
ちなみに既存のツールを探すのも当然行いましたが、これもどうもこれと言ったツールがないようでした
このページが良くまとまっていますが、これといったツールがなかったり、調査だけでも非常に多くのコストが必要と判断し諦めました。
Converting from other Databases to PostgreSQL - PostgreSQL wiki
変換方法
基本的にはRubyで1文字づつ読み込みstackにためて判定を行うparserの作成を行いました。
とは言え仕様を完全に把握もできず仕事の範囲内で問題が出ないレベルにとどめています。
またPureRubyなコードの為どうしても動作は重めです。
レポジトリ
レポジトリはこちら。下手な英語ですがサンプルコードがあるのでまあなんとなく分かるかと思います。
仕事のコードからはクラス名等いくつか細かい変更を行っていますが動作は同じです。興味があれば使ってもらえればと。
