Tweet

Pocket

前回無事にデータを移行できたのですが、COPYコマンドの実行時にも苦戦していました。
その内容についても残しておこうと思います。

結論から

PostgreSQLのCOPYコマンドでJSON形式のデータを含むcsvファイルをインポートする場合には次の内容をチェックしましょう。

1. JSONデータは引用符で囲んであるか
   • これはNG {"Key": "Value"}
   • これはOK "{"Key": "Value"}"
2. JSONデータのエスケープ文字は設定してあるか
   • たとえばこう "{""Key"": ""Value""}"
   • これとかも "{\"Key\": \"Value\"}"
3. JSONデータのエスケープ文字とCOPYコマンドのESCAPEオプションの文字は一致しているか

JSON形式のデータを含むcsv形式のデータサンプル

サンプル1

JSONデータ："{""Key1"": ""Value"", ""Key2"": 1}"
COPYコマンド： \COPY スキーマ名.テーブル名 form 'ファイル名.csv' csv

サンプル2

JSONデータ："{\"Key1\": \"Value\", \"Key2\": 1}"
COPYコマンド： \COPY スキーマ名.テーブル名 form 'ファイル名.csv' csv ESCAPE '\'

ここから下はデータ移行する際の試行錯誤の内容です。
前回に引き続きとても酷い内容なので注意して読んでください。

移行するデータについて

移行するデータにはJSON形式のデータga
含まれています。

インポート先のテーブルはこんな感じです。

スキーマ：public
テーブル名：table

インポートするデータはこんな感じです。

csv形式はこんな感じです。

1,Sample,null,null
2,null,1,null
3,null,null,{"ID": "ID-001", "Type": 1}

COPYコマンド実行！

踏み台サーバーにSSHでログインして…

scpコマンドでローカルPCからcsvファイルを転送して…

そしてPostgreSQLにアクセスして…

COPYコマンドはcsvを指定して、nullの置換はせずにnullを指定します。

=# \copy public.table from "export1.csv" csv null 'null'

エラーです。

ERROR:  extra data after last expected column
CONTEXT:  COPY Sample, line 3: "3,null,null,{"ID": "ID-001", "Type": 1}"

カラム数が違う…？

あー、JSON内部の “,” がcsvの区切りになっているのかな。
JSON形式を文字列として認識させる必要がありそう。

どのみち置換しなきゃいけないのか…えーと…

sed -i 's/,{/,\"{/g' export1.csv
sed -i 's/,}\n,}\"\n/g' export1.csv

sedコマンドで置換しました。

1,Sample,null,null
2,null,1,null
3,null,null,"{"ID": "ID-001", "Type": 1}"

よし、実行

ERROR:  invalid input syntax for type json
DETAIL:  Token "ID" is invalid.
CONTEXT:  JSON data, line 1: {ID...
COPY Sample, line 3, column data3: "{ID: ID-001, Type: 1}"

ん？JSON内の引用符であるダブルクォーテーションがない…？

あ～～エスケープしなきゃいけないのか

sedコマンドで置換しました。

1,Sample,null,null
2,null,1,null
3,null,null,"{\"ID\": \"ID-001\", \"Type\": 1}"

再び実行

ERROR:  invalid input syntax for type json
DETAIL:  Token "\" is invalid.
CONTEXT:  JSON data, line 1: {\...
COPY Sample, line 3, column data3: "{\ID\: \ID-001\, \Type\: 1}"

ええ？ \ が invalid だって…？

ここでやっと公式のドキュメントを読み込みます。すると…

QUOTE

データ値を引用符付けする際に使用される引用符用文字を指定します。 デフォルトは二重引用符です。 これは単一の1バイト文字でなければなりません。 このオプションはCSV書式を使用する場合のみで許されます。

ESCAPE

QUOTEの値がデータ内の文字と一致した場合に、その前に現れなければならない文字を指定します。 デフォルトはQUOTEの値です（このためデータ内に引用符用文字があれば二重になります）。 これは単一の1バイト文字でなければなりません。 このオプションはCSV書式を使用する場合のみ許されます。

ふむ、QUOTEで引用符の文字を指定出来て、ESCAPEでエスケープ文字を指定できるのか。 そしてESCAPEではデフォルトではQUOTEの値とあるから、 \" ではなく "" としておけばよかったのか。

置換してしまったし、ESCAPEを \ に指定して実行してみよう。

=# \copy public.table from "export1.csv" csv null 'null' escape '\'

成功しました！

ちなみにESCAPEをデフォルトのままで実行出来るようにJSONを次のように変更して、

1,Sample,null,null
2,null,1,null
3,null,null,"{""ID"": ""ID-001"", ""Type"": 1}"

ESCAPEの指定なしで実行してみました。

=# \copy public.table from "export1.csv" csv null 'null'

成功しました！

まとめ！

• 公式ドキュメントはよく読もう！
• まずはテスト用の環境で動作を確認してみよう！

う、うーん…我ながらこれは酷い…
おまえは何年やってんだって話ですね？いやー恥ずかしい限りです…

Tweet

Pocket