Qlik replicate datatype conversion issue

[vkura]

Hi Team,

The Qlik replicate converting data type from Oracle Timestamp to Snowflake string. why it is converting to string datatype in Snowflake and how to resolve the issue? 

Please refer to the below attachments.

Thank you,

Vinil

[Heinvandenheuvel · Accepted solution]

It's due to the source column datatype being TIMESTAMP(6) WITH LOCAL TIME ZONE

A simple timestamp, without timezone, stays timestamp. but Replicate does not appear to have an internal representation for timestamp with timezone. Oracle and Snowflake do. This would appear to be a reasonable feature request. In the mean time you may want to just force a simple timestamp target datatype in the transform screen, or pre-create the target table with Snowflakes TIMESTAMP_LTZ , TIMESTAMP_NTZ  or TIMESTAMP_TZ datatypes and 'just try'.  (change task settings to 'truncate on reload, not the default drop+recreate.) It may work and re-transform from string to target. Sorry, I have no Snowflake access just now to try.

This is documented in the Replicate User Guide under the Oracle Source Endpoint:

TIMESTAMP -->  DATETIME
TIMESTAMP WITH TIME ZONE  --> STRING (with timestamp_with_timezone indication)

Hein.

[vkura]

@Heinvandenheuvel ,

While replicating the data from Oracle to Snowflake for Timestamp datatypes, we are receiving a hour lag in snowflake when compared to Oracle. Why is that happening?

Please check below screenshots.

[Heinvandenheuvel]

Please consider to AVOID this screenshots and just get the TEXT for columns/values. It is so much quicker, so much easier to compare for the folks trying to help you! Is that TS column on snowflake now a Timestamp, Timestamp with timezone,  or still text as originally shown? When it was text, what did the same row look like? The 10/12 23:10 time could be correct depending on the associated timezone which I fail to see in the provided information.

Is there a 1 hour time difference between the source timezone and the snowflake timezone? Where (timezone) is the Replicate engine? You may have to take pick the source timestamp apart to get the timezone and use a SQLite datetime transformation forcing in a timezone. Fun!

https://www.sqlite.org/lang_datefunc.html : "Formats 2 through 10 may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z". The date and time functions use UTC or "zulu" time internally, and so the "Z" suffix is a no-op. Any non-zero "HH:MM" suffix is subtracted from the indicated date and time in order to compute zulu time. For example, all of the following time values are equivalent"

hth,

Hein

[vkura]

The column currently it is String in Snowflake and Time Stamp LTZ in Oracle. Both Oracle and Snowflake are in same time zone (CST).  For some of the Time stamp columns showing correct data in Snowflake even though it is string data type. Can you please check why it is happening for only SCD_UPDATED_TS column?

Example: 

SCD_UPDATED_TS: 

( Source): 13-OCT-21 12.10.05.000000000 AM (Data type: Timestamp (6) LTZ)

( Target: 2021-10-12 23:10:05.000000000 (Data type: String)

SCD_CREATED_TS: 

( Source): 9-JAN-20 07.32.15.000000000 PM (Data type: Timestamp (6) LTZ)

( Target: 2020-01-09 19:32:15.000000000 (Data type: String)

[Heinvandenheuvel · Accepted solution]

Thanks you for the excellent information presentation. It's very strange how one column appears to be created correctly and the other differently. I kinda expected a timezone indicator in the datetime. The 38 bytes as text certainly allows for it, as the presented timestamp value is 28 bytes.

It must be due to that "LOCAL" in the timezone. As per Oracle manuals "TIMESTAMP WITH LOCAL TIME ZONE does not store time zone information internally, but you can see local time zone information in SQL output if the TZH:TZM or TZR TZD format elements are specified."

I had all sort of trouble with LTZ on my play-database and got rid of them. Specifically I could not change the timezone on my test database until I got rid of all columns with TS-LTZ. I understand why that was, but it sure was frustrating.

Maybe you can test (on the source) with NLS_TIMESTAMP_TZ_FORMAT to make sure nothing is hidden. See also: https://www.oracletutorial.com/oracle-basics/oracle-timestamp-with-time-zone.

See also: https://asktom.oracle.com/pls/apex/asktom.search?tag=timestamp-with-local-time-zone

After you check the source columns in detail, and assuming no further help here, it may well be time to open a  format support case presenting all the information you provided so far to get to the bottom of this.

That's all I have. Maybe others can help more?

Hein.