はじめに
改行データをロードする場合は、COPYコマンドのパラメータでレコード終端文字を指定する必要があります。指定したレコード終端文字がテーブルのデータ内に含まれている場合はロードエラーになるため、データに含まれない文字を指定する必要があります。
しかし、実運用で利用しているテーブルのデータ件数は多く、様々なデータ(「”」、「,」、「;」、「TAB」…)が含まれている可能性があり、レコード終端文字にどの文字を指定すべきか判断が難しい場合があります。
#例：
dbadmin=> SELECT * FROM test1;
col1 | col2
------+----------------------
1 | a b
2 | a,b
3 | ab <=★改行コードがデータとして存在
c
4 | abc
5 | ",";?1!$%&`:{}_?><?/
6 | "'"本記事では、上記データを例に挙げて、改行データのロード方法をご紹介します。
改行データのロード方法
区切り文字、レコード終端文字に制御コードを使用し、囲み文字は使わずにデータ抽出、データロードを行います。
手順/コマンド
○使用する制御コード
\001：SOH（ヘッダ開始）
\002：STX（テキスト開始）
○データ抽出コマンド
$ vsql -w <パスワード> -F $'\001' -R $'\002' -P null='MYNULL' -AXtnqc "<SQL文>" -o <出力ファイル名>
nullデータについては、"MYNULL"という文字列として出力しています。○データロードコマンド
$ vsql -w <パスワード>
SQL> COPY <テーブル名> FROM '<ファイル名>' delimiter E'\001' record terminator E'\002' null 'MYNULL' DIRECT;実行例
#抽出対象データ
dbadmin=> SELECT * FROM test1;
col1 | col2
------+----------------------
1 | a b
2 | a,b
3 | ab <=★改行コードがデータとして存在
c
4 | abc
5 | ",";?1!$%&`:{}_?><?/
6 | "'"
#データ抽出
$ vsql -w password -F $'\001' -R $'\002' -P null='MYNULL' -AXtnqc "SELECT * FROM test1;" -o /tmp/test1.csv
#抽出データ確認
$ cat /tmp/test1.csv
1a b2a,b3ab
c4abc5",";¥1!$%&`:{}_?><?/6"'"
⇒実際にはレコードの区切りや終端文字として制御コードが挿入されています。
#データロード
$ vsql -w password
dbadmin=> COPY new_test1 FROM '/tmp/test1.csv' DELIMITER E'\001' RECORD TERMINATOR E'\002' NULL 'MYNULL' DIRECT;
Rows Loaded
-------------
6
#ロードデータ確認
dbadmin=> SELECT * FROM new_test1;
col1 | col2
------+---------------------
1 | a b
2 | a,b
3 | ab
c
4 | abc
5 | ",";1!$%&`:{}_?><?/
6 | "'"注意点
区切り文字、レコード終端文字（改行コード）の代わりに制御コードを使用するため、抽出したファイルは全データが1行で表示されます。そのため、ロードエラーが起きた際などは、抽出ファイルから該当箇所の確認がしにくいといった点が注意事項として挙げられます。該当箇所を確認する際は、出力ファイルの制御コードを置換するなどの対処が必要となります。（Linuxの場合は、OSコマンドのsedやtrを利用）
参考情報
・改行をデータとしてロードする方法
https://www.ashisuto.co.jp/cm/analytics-database/record-teminator.html
・各データ移行方法のメリットとデメリットについて
https://www.ashisuto.co.jp/cm/analytics-database/data-migration-method.html
検証バージョンについて
この記事の内容はVertica 10.1で確認しています。
更新履歴
2021/06/25 本記事を公開