trdsqlはCSV,LTSV,JSONをDB(PostgreSQL,MySQL)にインポートするツールです

trdsqlはCSV,LTSV,JSONに対してSQLを実行するツールと書いてあって、「PostgreSQLやMySQLに接続できるため、データをインポートする方法もある」と書いてあるため、オマケ的に捉えられているかもしれませんが、専用ツールを上回るくらい便利に使えます。

データをインポートしたい時に、そもそも候補としてすら挙がっておらず、寂しい気持ちがあるのですが、それよりも何よりも、それtrdsqlでサクッと出来ちゃうのに...という場面を度々目にするので、ここで紹介させていただきます。

そもそものtrdsqlの動作

そもそもtrdsqlの動作は、（デフォルトでSQLite3ですが）データベースに、テキストファイルを読み込んで、インポートし、SQLの処理をデータベースにお任せして、処理結果をテキストのいろんな形式に変換する。という動作になってます。

つまり、毎回データベースにテキストファイルをインポートをしているツールなんです。
そして、SQLite3のみを使用する他のツールと違い、データベースとしてPostgreSQLとMySQLも選択可能になっています。

SQLite3は（デフォルト）メモリデータベースですが、全てのデータベースでTEMPORARYテーブルにインポートしています。TEMPORARYテーブルではなく通常のテーブルにすれば、通常の消えないテーブルが作成できます。

必ずTEMPORARYテーブルにインポートする訳

そのためTEMPORARYではなく通常のテーブルを作成するオプションを用意すれば、インポートツールになるわけですが、trdsqlには、そのようなオプションは用意されていません。

通常のテーブルにインポートすると言っても、schema指定、既に作成済みのテーブル、既にデータが入っているテーブル、と様々な条件があり、カラム名やカラムの型がファイルとテーブルで整合を取らないとなりません（そして、それはデータベースにより少しづつ異なります）。

CLIのツールでインポートを実現するためには、そのための様々な条件に対応するためのオプションが必要になり、覚えきれないどころか全部読むのも大変なhelpが必要になります。

元々データベースにはSELECTの結果からテーブルを作成、インポートする機能が備わっていて、そのデータベースに応じた、様々なが条件に対応する方法が存在しています。
trdsqlはCLIツールとして、通常のテーブルにインポートするにはSQLを使ってもらうほうが有益と判断した訳です。

そのためtrdsqlは、なるべくいろんなSQLを書かれても邪魔せず必要なところだけ書き換えるという方針を取ってます。

ということで、trdsqlで通常のテーブルにインポートするには以下のようにします。

trdsql -ih "CREATE TABLE テーブル AS SELECT * FROM ファイル.csv"

これは「ファイル.csv」をTEMPORARYテーブルにインポートして、そのSELECTの結果を通常のテーブルにインポートする。ということになります。

2回インポートする問題

trdsqlでは、2回（TEMPORARYと通常のテーブルに）インポートしているため、1回でテーブルにインポートするよりも非行率で遅くなると感じられるかもしれません。

これはその通りで、最速の方法には成りえません。ただ、多くの場合で十分なパフォーマンスで処理できると考えています。

PostgreSQLでCSVファイルをインポートするのに（異論はあるかもしれませんが、特殊な条件を除けば）最速と言えるのがpsqlの\copyを使用する方法です。

単純にworldcitiespop.csvという3,173,958件あるCSVファイルをインポートしてみます。手元の計測ですが、そこまで精度を求めずにtotalだけ見ていきます。

psqlの\copyの例です。あらかじめテーブルを作成しておく必要があります。

time psql -c "\copy world from worldcitiespop.csv with csv header"
0.58s user 0.11s system 15% cpu 4.397 total

trdsqlの例です。テーブルは実行すると作られます。

time trdsql -ih "CREATE TABLE world2 AS SELECT * FROM worldcitiespop.csv"
7.30s user 0.50s system 73% cpu 10.650 total

これを2回のインポートではなくSELECT COUNT(*)の結果だけ求めた場合は、「7.102 total」でした。つまり一回のインポートで、約7秒かかり、2回目のインポートで約3.5秒かかってます。'\copy'でかかった時間の倍以上ではありますが、2回目のインポート分はデータベース内で完結しているため'\copy'よりは速いと考えて良いでしょう。

ちなみにPostgreSQLにCSVやJSONをインポートできるpgfutterというツールでは以下のようになりました。

time pgfutter --schema public --table world3 csv worldcitiespop.csv
7 columns
[Country City AccentCity Region Population Latitude Longitude]
 144.47 MiB / 144.47 MiB [============================================================================================] 100.00% 54s
3173958 rows imported into public.world3
62.88s user 1.66s system 117% cpu 54.859 total

54秒かかっているツールが候補になるなら、10秒で処理できるツールも候補として考えていただきたいと思います:smile:

実はtrdsqlは、内部的にはCOPYを使用（ファイルを直接COPYしているわけではなく、CSV、LTSV、JSONをパースして、COPYの形式に合わせて送信してます）しているため、高速です。

MySQLのLOAD DATA INFILE（セキュリティ上設定で無効になっていたり、条件が厳しくなっているため）は使用しておらずBULK INSERTを使用しているため、LOAD DATA INFILEを使用した場合と比べてパフォーマンスはだいぶ劣ります。それでも1件づつINSERTする場合よりは早くインポートできます。

SQLを使用した柔軟なインポート

前述した通り、インポートのためにtrdsqlの独自のオプションを覚える必要なくSQLの構文によりインポートできます。それにより専用のインポートツールよりもいろんな条件に対応できます。

例えば、既にテーブルが存在していてデータを追加したい場合は、CREATE TABLE 〜 ASをINSERT INTO 〜に変更すれば可能です。

trdsql -ih "INSERT INTO world SELECT * FROM worldcitiespop.csv"

SELECT *だと列がテーブルと同じでないとならないので、列名を指定した方が良いでしょう。CSVファイル側の列名はtrdsql -ih　-aを使用すると便利です。

$ trdsql -ih -a worldcitiespop.csv
he table name is worldcitiespop.csv.
The file type is CSV.

Data types:
+----------------+------+
|  column name   | type |
+----------------+------+
| \"Country\"    | text |
| \"City\"       | text |
| \"AccentCity\" | text |
| \"Region\"     | text |
| \"Population\" | text |
| \"Latitude\"   | text |
| \"Longitude\"  | text |
+----------------+------+

（略）

Examples:
trdsql -ih "SELECT \"Country\", \"City\", \"AccentCity\", \"Region\", \"Population\", \"Latitude\", \"Longitude\" FROM worldcitiespop.csv"
（略）

SQLの実行例が出ているので、

trdsql -ih "SELECT \"Country\", \"City\", \"AccentCity\", \"Region\", \"Population\", \"Latitude\", \"Longitude\" FROM worldcitiespop.csv"
            ↓
trdsql -ih "INSERT INTO world SELECT \"Country\", \"City\", \"AccentCity\", \"Region\", \"Population\", \"Latitude\", \"Longitude\" FROM worldcitiespop.csv"

のようにINSERT INTO テーブルを追加すれば簡単です。もしCSVファイルの列とテーブルの列順や列数が違う場合は、さらにSQL側で指定できます。

trdsql -ih "INSERT INTO world (country, city, accentcity) SELECT \"Country\", \"City\", \"AccentCity\" FROM worldcitiespop.csv"

CSVファイル等のテキストデータはデータ型が書かれていないので、データベースにインポートする時に指定するのが大変になります。そのため、あらかじめテーブルを作成しておく手順が普通ですが、SQLを使用する場合は、SELECT側でキャストすることで、作成するテーブルの型指定できます（キャストの仕方はデータベースによって異なるので、データベースのマニュアルを参照して下さい）。

trdsql -ih "CREATE TABLE f AS SELECT id::int, name FROM testdata/header.csv"

trdsqlでは、まず最初にTEMPORARYテーブルにインポートしてしまうので、データベース側がCREATE TABLE 〜 AS SELECTや INSERT INTO 〜 SELECTでサポートしている構文がそのまま使えることになります。

当然ながら、SELECTのWHERE句により全行ではなく該当する行のみをINSERTすることも簡単です。

さらにデータベース側がサポートしていれば例えば、デーブルが無い場合のみ新しく作成したり、

trdsql -ih "CREATE TABLE IF NOT EXISTS f AS SELECT id::int, name FROM testdata/header.csv"

主キーを指定した後のテーブルで同じ行のインポートでエラーになってしまう行を無視したりできます。

ALTER TABLE f ADD CONSTRAINT f_pkey PRIMARY KEY(id);

trdsql -ih "INSERT INTO f SELECT id::int, name FROM testdata/header.csv ON CONFLICT DO NOTHING"

まとめ

trdsql側にインポートするためのオプションがなくても、SQLの様々な実行を妨げないようになっているため、十分にインポートツールとして使えることがわかって頂けたと思います。