Failed to output to Redshift due to "Invalid Date Format" #227
Comments
|
Thank you for the report. I confirmed that "OK Pattern 3" also doesn't work at embulk 0.8.39. In your case, your configuration enables I also confirmed that OK pattern 3 doesn't work at embulk 0.8.20. One strange point is that "raw_field_value" in stl_load_errors doesn't match the actual value. But, raw_field_value returns "999-12-26". It seems there are 2 issues. 1 - date type in embulk-output-redshift doesn't support yyy-MM-dd |
|
Hello, @kyoshidajp Thank you for the report. I confirmed it on this test. |
|
While we still need to investigate this embulk-output-jdbc, I just confirmed that the Embulk core itself parses and formats FYI: just as a general advice, I'd basically suggest to use
As a result, Embulk internally converts the |
|
embulk-output-jdbc converts embulk Timestamp value to java.util.Date before inserting into DB. I found it is not easy to convert date value before 1582. |
|
A bit different in embulk-output-postgresql, but the cause is similar. We could reproduce this easily. As @hito4t mentioned, the root cause looks like a problem of Gregorian calendar and Julius calendar. There actually are no "consistent" methods to handle old dates before 1582. We need to everywhere specify how to handle. Please note that dates before 1582 can be differently handled. But here, the problem is not only different dates, but Exception. I guess the direct cause of the Exception may be that the lengths of the strings (original: |
|
@dmikurube embulk-output-redshift might be able to insert - {name: birthday, type: string}
|
|
@hito4t Thanks. Sounds correct, but I was not sure why |
|
@dmikurube @hito4t JYI In postgres I needed set in:
columns:
- {name: name, type: string}
- {name: mail, type: string}
- {name: birthday, type: string }
out:
type: postgresql
column_options:
birthday: { type: date, value_type: string }
# birthday: { type: date, value_type: date } <-- Can't insert DATE with this config. |
|
Can you try this configuration?, It worked well in my PostgreSQL database. in:
type: file
path_prefix: /path/to/users.csv
parser:
charset: UTF-8
newline: LF
type: csv
delimiter: ','
quote: '"'
trim_if_not_quoted: true
skip_header_lines: 1
allow_extra_columns: false
allow_optional_columns: false
default_timezone: 'Asia/Tokyo'
columns:
- {name: name, type: string}
- {name: mail, type: string}
- {name: birthday, type: string } # <-- Change here
out:
type: redshift
host: XXXX.ap-northeast-1.redshift.amazonaws.com
database: XXXX
user: XXXX
password: XXXX
iam_user_name: XXXX
aws_access_key_id: XXXX
aws_secret_access_key: XXXX
s3_bucket: XXXX
s3_key_prefix: XXXX
port: XXXX
table: users
mode: insert
options: {loglevel: 1}
default_timezone: 'Asia/Tokyo'
column_options:
name: {type: 'varchar(80)'}
email: {type: 'varchar(80)'}
birthday: { type: date, value_type: string } #<-- change here |
|
Thanks, everyone 🙇 @hiroyuki-sato Could you give me some time to run? I will probably be able to try it, 2-3 hours later. |
|
@hiroyuki-sato Sorry, I'm late. It was a success by using your configuration. |
|
Thank you for reporting the result. Please close this issue when you have a time. |
|
Thanks so much! |
|
I've create the new issue #229 . |
Issue Summary
Failed to output CSV data to Redshift with
org.postgresql.util.PSQLException: ERROR.Situation details
Table Scheme:
Input CSV: (users.csv)
Configuration: (
conf/csv_to_redshift_users.yml)Run command:
Error message:
Technical details
My embulk version is v0.8.39.
The error in
stl_load_errorsand the query by using SQLWorkbench/J is the following.Query:
Result:
When I change data of CSV, then output successfully.
OK Pattern1:
OK Pattern2:
OK Pattern3:
Also, inserted successfully by using SQLWorkbench/J these data with SQL.
The text was updated successfully, but these errors were encountered: