topic Re: How to handle data type conversion in Qlik Replicate

How to handle data type conversion

jdhruva — Fri, 09 Sep 2022 20:31:47 GMT

Experts need your guidance here. We are migrating from Oracle to Snowflake using QLIK. Part of the replication process we noticed that columns with datatype Number(20,2) in Oracle is transformed into Number(22) in Snowflake using QLIK. Data after the decimal is truncated in snowflake after a full/CDC load. Can you please help us fix this issue? Is there is a setting we need to configure to handle this transformation?

Re: How to handle data type conversion

SwathiPulagam — Fri, 09 Sep 2022 20:48:10 GMT

Hi @jdhruva,

This is not the expected behavior. I just tested and it is transferring the same datatype as it is in Oracle.

Example: Oracle source datatype Number(38,0)

Snowflake also same Number(38,0)

Thanks,

Swathi

Re: How to handle data type conversion

SwathiPulagam — Fri, 09 Sep 2022 20:50:49 GMT

Hi @jdhruva,

I would recommend you to create a support case for doing more analysis.

Thanks,

Swathi

Re: How to handle data type conversion

shashi_holla — Fri, 09 Sep 2022 20:56:28 GMT

Hi @jdhruva

In addition to what @SwathiPulagam said, as per the documentation the behavior that you mentioned can happen only when the scale is between 38 and 127 else it will match the same data type as in the source Oracle. In your case is Number(20,2) should be the same in the target as well.

Re: How to handle data type conversion

jdhruva — Fri, 09 Sep 2022 20:58:58 GMT

Hello Swathi, We I guess the team has already submitted a case. Here is another example, where the conversion didn't happen the right way (Refer Attached Screenshot; it is comparison between SNowflake and Oracle after a full load.Left column is Snowflake and Right column is Oracle)

Re: How to handle data type conversion

SwathiPulagam — Fri, 09 Sep 2022 21:01:11 GMT

Hi @jdhruva ,

Can you share the case number?

Thanks,

Swathi

Re: How to handle data type conversion

SwathiPulagam — Fri, 09 Sep 2022 21:02:31 GMT

Hi @jdhruva ,

Also please check if you enable any Global transformation to convert the datatype.

Thanks,

Swathi

Re: How to handle data type conversion

jdhruva — Fri, 09 Sep 2022 21:06:52 GMT

Yes we have global transformation configured NUMERIC to 15,0

Re: How to handle data type conversion

jdhruva — Fri, 09 Sep 2022 21:08:46 GMT

case# 00051525

Re: How to handle data type conversion

jdhruva — Fri, 09 Sep 2022 21:10:17 GMT

Thanks Shashi. I assume this is already defined QLIK by default

Re: How to handle data type conversion

SwathiPulagam — Fri, 09 Sep 2022 21:12:01 GMT

Hi @jdhruva ,

The case 00051525 is related to parallel load issue which i am currently working on with you.

I will create a new case on behalf of you for this datatype conversion issue.

Thanks,

Swathi

Re: How to handle data type conversion

jdhruva — Fri, 09 Sep 2022 22:19:45 GMT

We are discussing with Swathi through QLIK support. We will update once we have a solution to this issue.

Re: How to handle data type conversion

Heinvandenheuvel — Sun, 11 Sep 2022 14:57:35 GMT

Check your Oracle Source Endpoint - advanced settings -

Expose NUMBER as:

Re: How to handle data type conversion

lyka — Thu, 22 Sep 2022 22:15:25 GMT

Hello,

Are you using logstream? If yes, is the "Expose Number As" option for both source endpoints configured the same?

Also, is the target table created by Replicate?

Thanks

Lyka

Re: How to handle data type conversion

SwathiPulagam — Wed, 09 Nov 2022 13:39:20 GMT

Below is the solution we provided in the case :

Please refer to the Replicate user guide:

"
https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/Oracle/ora_source_data_types.htm#ar_oracleds_385117211_1677127

NUMBER (P,S)

NUMBER according to the "Expose number as" property in the Qlik Replicate Oracle source database settings. "

NUMBER in oracle to Replicate handled as described in the document. There is no internal NUMBER datatype in replicate so we can't handle dynamic changes in the datatype as like Oracle. To Overcome this issue, we introduced "Expose number as" property in Oracle endpoint.

Ex: If NUMBER is defined with specific Precision and Scale on the source then replicate will create same NUMBER(P, S) on target.
If no Precision and Scale is defined on the source then as we explained replicate can't decide what would be the length. You have to review all your NUMBER columns and decide the best value for the "Expose number as" property.
If a different NUMBER column has to be defined with different Precision and Scale then it has to be set at table level transformation.

Please create a feature request with your use case. The product manager will review and prioritize the request.

Thanks,

Swathi