View Issue Details

IDProjectCategoryView StatusLast Update
0004595SymmetricDSBugpublic2022-01-13 13:22
Reporterivcontre Assigned To 
Priorityhigh 
Status newResolutionopen 
Product Version3.11.0 
Summary0004595: Parameter 'date format' is fixed in COPY Redshift command
DescriptionI'm using redshift bulk to copy data from MSSQL to Redshift, but I need the COPY command to have 'timeformat' as a parameter and not 'dateformat' ('dateformat' is fixed in the code). I tried using redshift.append.to.copy.command = timeformat 'YYYY-MM-DD HH: MI: SS', but this only adds parameters and thus the COPY command will have the 'dateformat' and the 'timeformat'. Was anyone able to fix this problem?
Additional InformationGithub link of the code: https://github.com/JumpMind/symmetric-ds/blob/ec91ce7036f588bc9b94b6d9394e01a9f21e2eda/symmetric-client/src/main/java/org/jumpmind/symmetric/io/RedshiftBulkDatabaseWriter.java
Tagsconfiguration, dialect: redshift

Activities

ivcontre

2020-10-16 16:10

reporter  

josh-a-hicks

2020-10-22 18:23

developer   ~0001809

What was the error you received? According to the AWS documentation, the default date, and time formats are already set to what you are trying to do.

https://docs.aws.amazon.com/redshift/latest/dg/r_DATEFORMAT_and_TIMEFORMAT_strings.html

ivcontre

2020-10-22 19:07

reporter   ~0001810

The problem with this DATEFORMAT 'YYYY-MM-DD HH: MI: SS' parameter is that when there is a table that has a date field, it does not include the time and therefore the execution of the COPY command throws an error. There are no problems in synchronizing tables with timestamp. We manually load with TIMEFORMAT and we do not present this problem.

The COPY comand that generates the symmetry is:

COPY replica.public.delta_bookingpassenger (
  passengerid,
  createduserid,
  createdutc,
  modifieduserid,
  modifiedutc,
  dob,
  paxtype,
  gender,
  weightcategory,
  title,
  firstname,
  middlename,
  lastname,
  suffix,
  bookingsearchnameid,
  nationality,
  residentcountry,
  discountcode,
  infant,
  bookingid,
  customernumber,
  familynumber,
  totalcost,
  balancedue
)
FROM
  's3://<BUCKET_NAME>/delta_BookingPassenger498.csv'
CREDENTIALS ''
REGION 'us-east-2'
DELIMITER '|'
CSV
DATEFORMAT 'YYYY-MM-DD HH:MI:SS'

The data of MSSQL is the image(7).png
The images image (4).png, image (5).png and image (6).png is of table STL_LOAD_ERRORS of Redshift
image (4).png (16,740 bytes)   
image (4).png (16,740 bytes)   
image (6).png (7,254 bytes)   
image (6).png (7,254 bytes)   
image (5).png (9,234 bytes)   
image (5).png (9,234 bytes)   
image (7).png (25,717 bytes)   
image (7).png (25,717 bytes)   

Issue History

Date Modified Username Field Change
2020-10-16 16:10 ivcontre New Issue
2020-10-16 16:10 ivcontre Tag Attached: configuration
2020-10-16 16:10 ivcontre Tag Attached: copy
2020-10-16 16:10 ivcontre Tag Attached: dateformat
2020-10-16 16:10 ivcontre Tag Attached: redshift
2020-10-16 16:10 ivcontre Tag Attached: timeformat
2020-10-16 16:10 ivcontre File Added: Captura de pantalla de 2020-10-16 13-07-48.png
2020-10-22 18:23 josh-a-hicks Note Added: 0001809
2020-10-22 19:07 ivcontre Note Added: 0001810
2020-10-22 19:07 ivcontre File Added: image (4).png
2020-10-22 19:07 ivcontre File Added: image (6).png
2020-10-22 19:07 ivcontre File Added: image (5).png
2020-10-22 19:07 ivcontre File Added: image (7).png
2022-01-13 13:22 admin Tag Attached: dialect: redshift
2022-01-13 13:22 admin Tag Detached: copy
2022-01-13 13:22 admin Tag Detached: dateformat
2022-01-13 13:22 admin Tag Detached: redshift
2022-01-13 13:22 admin Tag Detached: timeformat