COPYコマンドで関数などを使用してロードする方法をご紹介します。
概要
データをCOPYコマンドを使用してロードする場合、そのままデータをロードするだけでなく、関数などを使用してデータを編集してロードすることができます。
ただし、関数を使用する場合は、COPYコマンドの中でFILLERを使用する必要があります。
FILLERに関しては下記URLを参照
COPYコマンドでCSVの特定の列をロード対象外とする方法
実行例（１）
年月日がカンマで区切られたCSVのデータを１つの日付型のカラムにロードします。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
/* 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データと違う値をロードします。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
/* 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を使わずに処理することが可能です。もし上述の実行例（１）で「年」、「月」、「日」、「年月日」をロードしたい場合は以下のようにします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
/* 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 本記事を公開
