COPYコマンドで関数などを使用してロードする方法をご紹介します。
概要
データをCOPYコマンドを使用してロードする場合、そのままデータをロードするだけでなく、
関数などを使用してデータを編集してロードすることができます。
ただし、関数を使用する場合は、COPYコマンドの中でFILLERを使用する必要があります。
FILLERに関しては下記URLを参照
COPYコマンドでCSVの特定の列をロード対象外とする方法
実行例（１）
年月日がカンマで区切られたCSVのデータを１つの日付型のカラムにロードします。
/* date1テーブルにtimestamp型のカラムが1つ存在します */
dbadmin=> \d date1
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-----------+------+---------+----------+-------------+-------------
public | date1 | col1 | timestamp | 8 | | f | f |
(1 row)
/* CSVファイルは年月日がカンマで区切られています */
$ cat date1.csv
2018,04,01
2018,04,02
2018,04,03
2018,04,04
2018,04,05
/* 年月日がカンマで区切られたCSVのデータをdate1テーブルの1カラムにロードします */
/* 任意の列名で連結しTO_DATE関数を使用し日付型に編集しています */
dbadmin=> COPY date1 (
dbadmin(> a FILLER VARCHAR(4),
dbadmin(> b FILLER VARCHAR(2),
dbadmin(> c FILLER VARCHAR(2),
dbadmin(> col1 AS TO_DATE(a || b || c, 'yyyymmdd')
dbadmin(> )
dbadmin-> FROM '/home/dbadmin/work/date1.csv'
dbadmin-> DELIMITER ','
dbadmin-> DIRECT;
Rows Loaded
-------------
5
(1 row)
/* ロード後の結果を確認します */
dbadmin=> SELECT * FROM date1;
col1
---------------------
2018-04-01 00:00:00
2018-04-02 00:00:00
2018-04-03 00:00:00
2018-04-04 00:00:00
2018-04-05 00:00:00
(5 rows)
/* ★CSVファイルの任意の列名で連結した日付がロードされています */実行例（２）
CSVデータの値を比較して、CSVデータと違う値をロードします。
/* empテーブルに名前、性別、性別コードのカラムが３つ存在します */
dbadmin=> \d emp
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+------------+-------------+------+---------+----------+-------------+-------------
public | emp | name | varchar(20) | 20 | | f | f |
public | emp | seibetu | varchar(10) | 10 | | f | f |
public | emp | seibetu_cd | varchar(1) | 1 | | f | f |
(3 rows)
/* CSVファイルはnameとseibetuだけで、seibetu_cdのデータは存在しません */
$ cat emp.csv
名前1,男
名前2,男
名前3,女
名前4,男
名前5,女
/* CSVのseibetuが"男"であれば"1"、女であれば"2"をseibetu_cdにロードします */
/* 任意の列名の性別を判定するためCASE文をします */
dbadmin=> COPY emp (
dbadmin(> name,
dbadmin(> a FILLER VARCHAR(10),
dbadmin(> seibetu AS a,
dbadmin(> seibetu_cd AS CASE WHEN a = '男' THEN '1' ELSE '2' END
dbadmin(> )
dbadmin-> FROM '/home/dbadmin/work/emp.csv'
dbadmin-> DELIMITER ','
dbadmin-> DIRECT
dbadmin-> ABORT ON ERROR;
Rows Loaded
-------------
5
(1 row)
/* ロード後の結果を確認します */
dbadmin=> SELECT * FROM emp;
name | seibetu | seibetu_cd
-------+---------+------------
名前1 | 男 | 1
名前2 | 男 | 1
名前3 | 女 | 2
名前4 | 男 | 1
名前5 | 女 | 2
(5 rows)
/* ★CSVファイルのseibetuが"男"であれば"1"、女であれば"2"がseibetu_cdにロードされています */補足
関数の処理対象カラムのデータもロードする場合は、FILLERを使わずに処理することが可能です。
もし上述の実行例（１）で「年」、「月」、「日」、「年月日」をロードしたい場合は以下のようにします。
/* date2テーブルに年、月、日、年月日のための4カラムが存在します */
dbadmin=> \d date2
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------------+------+---------+----------+-------------+-------------
public | date2 | col1 | varchar(4) | 4 | | f | f |
public | date2 | col2 | varchar(2) | 2 | | f | f |
public | date2 | col3 | varchar(2) | 2 | | f | f |
public | date2 | col4 | timestamp | 8 | | f | f |
(4 rows)
/* CSVファイルは年月日がカンマで区切られています */
$ cat date2.csv
2018,04,01
2018,04,02
2018,04,03
2018,04,04
2018,04,05
/* 年月日がカンマで区切られたCSVのデータをdate2テーブルの4カラムにロードします */
/* 年をcol1、月をcol2、日をcol3、col1～col3のデータを関数で演算してcol4にロードしています */
dbadmin=> COPY date2 (
dbadmin(> col1,
dbadmin(> col2,
dbadmin(> col3,
dbadmin(> col4 AS TO_DATE(col1 || col2 || col3, 'yyyymmdd')
dbadmin(> )
dbadmin-> FROM '/home/dbadmin/work/date2.csv'
dbadmin-> DELIMITER ','
dbadmin-> DIRECT;
Rows Loaded
-------------
5
(1 row)
/* ロード後の結果を確認します */
dbadmin=> SELECT * FROM date2;
col1 | col2 | col3 | col4
------+------+------+---------------------
2018 | 04 | 01 | 2018-04-01 00:00:00
2018 | 04 | 02 | 2018-04-02 00:00:00
2018 | 04 | 03 | 2018-04-03 00:00:00
2018 | 04 | 04 | 2018-04-04 00:00:00
2018 | 04 | 05 | 2018-04-05 00:00:00
(5 rows)
/* ★CSVファイルの3カラムと、連結した日付がロードされています */検証バージョンについて
この記事の内容はVertica 9.2で確認しています。
更新履歴
2019/07/19 補足を追加
2019/04/15 検証バージョンを9.2に変更
2016/12/19 本記事を公開