RedshiftのCOPYコマンドが失敗する

問題

docs.aws.amazon.com

S3からRedshiftにレコードをコピー（insert）できるコマンドCOPYが便利なので

COPY users
FROM 's3://my_bucket/backup.users.csv'
CREDENTIALS 'aws_access_key_id=xxx以下略'
CSV

とかすると

ERROR:  Load into table 'users' failed.  Check 'stl_load_errors' system table for details.

と叱られる

調査

言われた通り、

psql psql -U ${USER} -h ${HOST} -p ${PORT} ${DB_NAME}

で入り、stl_load_errorsというシステムテーブルがあるというので、ちょっと確認

STL_LOAD_ERRORS - Amazon Redshift

my_db=# \d stl_load_errors;
            Table "pg_catalog.stl_load_errors"
     Column      |            Type             | Modifiers
-----------------+-----------------------------+-----------
 userid          | integer                     | not null
 slice           | integer                     | not null
 tbl             | integer                     | not null
 starttime       | timestamp without time zone | not null
 session         | integer                     | not null
 query           | integer                     | not null
 filename        | character(256)              | not null
 line_number     | bigint                      | not null
 colname         | character(127)              | not null
 type            | character(10)               | not null
 col_length      | character(10)               | not null
 position        | integer                     | not null
 raw_line        | character(1024)             | not null
 raw_field_value | character(1024)             | not null
 err_code        | integer                     | not null
 err_reason      | character(100)              | not null

こんな感じで出してみる。（なお、colnameとかerr_reasonとか、固定長なのでTRIMしないと厳しい出力を得る）

SELECT starttime, TRIM(colname), err_code, TRIM(err_reason)
FROM stl_load_errors
ORDER BY starttime DESC LIMIT 2;

         starttime          | btrim  | err_code |                     btrim
----------------------------+--------+----------+------------------------------------------------
 2015-12-22 08:47:17.143206 |  age   |     1207 | Invalid digit, Value 'a', Pos 0, Type: Integer
 2015-12-21 17:15:05.32209  |        |     1202 | Extra column(s) found
(2 rows)

1207 | Invalid digit, Value 'a', Pos 0, Type: Integer

これはロードエラー参照 - Amazon Redshiftによると

期待される 0～9 の範囲外の値がデータに含まれています。

たしかにageカラムはint型だが、aなんて値は入れてない。

csvの内容見ると、

id_str,username,age
xxxx,otiai10,29

となってて、この最初の行のageという文字がひっかかってる気がする。

解決

ためしに、csvにおけるカラム名の行を削除し

xxxx,otiai10,29

というデータを食わせてみると、

INFO:  Load into table 'users' completed, 1 record(s) loaded successfully.
COPY

となった。そうすっと、カラム名と値の対応はどうすんだろうと思ったが、どうやら

my_db=# \d users
         Table "public.users"
   Column   |            Type             | Modifiers
------------+-----------------------------+-----------
 id_str    | character varying(256)      |
 username  | character varying(256)      |
 age       | integer                     |

CRATE TABLEした順、ということなのか？

結論

• COPYコマンドに食わせるCSVに、列名行（最初の1行目）は不要

追記

my_db=# SELECT starttime, trim(colname), trim(err_reason) FROM stl_load_errors ORDER BY starttime DESC LIMIT 2;
         starttime          |   btrim    |                           btrim
----------------------------+------------+-----------------------------------------------------------
 2016-01-04 03:41:26.350018 | created_at | Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SS]
 2016-01-04 03:26:50.634297 | created_at | Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SS]
(2 rows)