topic Re: extra Quote is added into Loaded Snow flake data in Qlik Replicate

extra Quote is added into Loaded Snow flake data

rahulraghavan — Thu, 03 Feb 2022 02:38:34 GMT

Hi All

We are trying to load data from MSSql into AWS Snowflake using Snowflake. Upon checking we found there is an extra quote added in some of the string data

Eg:

Source : abcdef

Target: "abcdef"

when I reload the entire tables I can't see data with Quote and it happens only for the new process data. Is there any way to fix the issue in Qlik Replicate?

Thank you for your help

Re: extra Quote is added into Loaded Snow flake data

Dimitri_Remez — Thu, 03 Feb 2022 15:47:23 GMT

Edited as per @Heinvandenheuvel's suggestion:

Hi Preethish,

Are you using Qlik Replicate May 2021 build 2021.5.1133 or below? If that's the case, I would suggest upgrading either to the latest patch of the 2021.5 version that we have available on our Downloads page (build 2021.5.1272 -> SR 5) or to the latest version of Qlik replicate November 2021 (build 2021.11.165 -> SR 1), as they include a fix to this known product issue 😉

From the Release Notes:

"Jira issue: RECOB-3945

Description: When at least one of the captured tables contained CLOB columns, ambiguous double quotes would be added to some of the data. This would result in character columns being truncated if they were too long. "

Hope this works.

Kind regards,

Dimitri

Re: extra Quote is added into Loaded Snow flake data

Heinvandenheuvel — Thu, 03 Feb 2022 15:23:49 GMT

I assume the 'abcdef' is not the actual value. Normally that's fine, but here the quotes are fairly likely triggered by the specific, exact characters in the string. If you can correlate the occurrence with a source row, then your first step should be to use something like:

SELECT CAST(<problem_column> AS VARBINARY(xxx)) "Hex", 
       '<' || problem_column || '>' "Txt" FROM <problem_table> WHERE ...

What are the characters like? UTF? Anything extra long in there (UTF-4)

You should also try to get a reproducer, possibly using such sample row, and then run a test with the Snowflake endpoint internal parameter "keepCSVFiles " set to true.

Looking into the CSV file, searching for the string should allow you to see where the data gets munged and might take Replicate out of the picture. But it looks like this could be a know and fixed issue according to @Dimitri_Remez . In general double quotes are special, and need to be 'escaped' in general. Maybe something is going wrong with that. Maybe there is a SnowFlake connection property to influence the behavior.

@Dimitri_Remez - Can you perhaps update your reply with a quote from a release note or similar such that the customer can judge whether the fix is likely to match the issue?

Somewhat related Replicate internal parameters with their defaults could be:

replace4BytesChar "0x3F"
replaceChars "0x0D,0x"
replaceInvalidChars "?"

hth,

Hein

Re: extra Quote is added into Loaded Snow flake data

rahulraghavan — Fri, 04 Feb 2022 01:57:54 GMT

Thank you Dimitri for the download page does not have any link for qlik replicate download. Can you please let me know where could I download

Re: extra Quote is added into Loaded Snow flake data

Dimitri_Remez — Fri, 04 Feb 2022 07:36:55 GMT

Hi Preethish,

Yep, sure. Please, follow the steps that I'm highlighting in the screenshots below:

1) After accessing the Downloads page, click on "Qlik Data Integration":

2) Click on "Qlik Replicate" that you'll see on the left side Filters pane:

3) After that, you can continue filtering by choosing one of the "Version No" and "Release Number" that I mentioned in my first reply:

4) Here is an example for the build 2021.5.1272 -> SR 5:

Hope this helps 😊

Thank you!

Dimitri

Re: extra Quote is added into Loaded Snow flake data

rahulraghavan — Fri, 04 Feb 2022 08:06:13 GMT

I could see only options attached attached

Re: extra Quote is added into Loaded Snow flake data

Dimitri_Remez — Fri, 04 Feb 2022 08:18:46 GMT

Thank you for the screenshot Preethish! Could you try to follow the instructions from this article, please? -> How to Access and Filter the Extended Download Page to Download Qlik Products

If this doesn't help, I would suggest opening a case with our Customer Support Team, by choosing these options:

Thank you!

Dimitri