Tables are stuck in loading & Data isn't being copied from stage to table in Snowflake

[NakulanR]

Hi Qlik Support,

We are seeing an issue where when loading data from Oracle to Snowflake, tables get stuck in loading. The Replicate logs indicate that the load is finished with the message "Load finished for table..." and there is a follow up message to say that the file was removed successfully from Snowflake stage (see below), however we don't see any data in the Snowflake target tables.

In Snowflake's monitoring view, we can see the PUT command being run, however there is no COPY command to move the data from the Snowflake stage to the target table. Is it Replicate's job to issue the COPY command once the file has been put into the Snowflake stage?

Additionally, after the task has been in a stale running state (where the table is still stuck in loading) for about 1.5 - 2 hrs, the task throws an error and stops. The error is "Failed to execute statement: PUT..." with the full load CSV file path. Full error message below:

[TARGET_LOAD ]T: Failed (retcode -1) to execute statement: PUT 'file://D:\\Qlik\\data\\tasks\\Test_Oracle_to_Snowflake\\cloud\\1\\LOAD00000001.csv' @"DEV_AN"."PUBLIC"."ATTREP_IS_DEV_AN_cc4bc441_1fff_cc4f_8dfe_08873ca8387b"/1/ AUTO_COMPRESS = TRUE SOURCE_COMPRESSION = NONE ; [1022502] (ar_odbc_stmt.c:5082)
00007352: 2024-06-21T17:29:12:173307 [TARGET_LOAD ]T: RetCode: SQL_ERROR SqlState: HY000 NativeError: 40 Message: [Snowflake][Snowflake] (40)
Error encountered when executing file transfer: Failed to upload file D:\Qlik\data\tasks\Test_Oracle_to_Snowflake\cloud\1\LOAD00000001.csv.
[1022502] (ar_odbc_stmt.c:5090)

I'd like to understand potential reasons as to why the files may not be uploading to Snowflake, and why Replicate initially reports the data as being loaded into Snowflake. Any feedback into the issue is greatly appreciated, and apologies if this has already been covered in another community post.

*Using Replicate v2023.11.0.282, Snowflake ODBC Driver 2.25.12, Oracle Instant Client 19

Thanks,

Nak

[DesmondWOO]

Hi @NakulanR ,

Thank you for reaching out to the Qlik Community.

The message "Load finished for table..." indicates that Replicate has finished loading data to the CSV file. Replicate then attempted to upload the CSV file to the SnowFlake, but encountered a network issue.

Replicate threw an error and stopped after 1.5~2 hours. It may be due to TCP KeepAlive setting. For example, the default value for Windows is 2 hours.

If problem persists, please create a support ticket.

Regards,
Desmond

[john_wang · Accepted solution]

Hello Nak, @NakulanR 

Several factors may cause upload failures, such as:

1- Duplicate CSV filenames in the target folder: Please check the target folder and remove the unnecessary files

2- Connection issue between Qlik Replicate and Snowflake server: test the connectivity using SnowSQL

3- Endpoint settings: We ever experienced that reducing the max file size in the endpoint settings for Snowflake down to 25mb did the trick, see here.

4- Additional troubleshooting steps: if the problem persists, you may add an internal parameter keepCSVfiles in Snowflake endpoint, after the task failure, you can try to upload the saved CSV files manually to see if upload works.

Hope this helps.

John.

[NakulanR]

Hi @john_wang & @DesmondWOO,

Thanks for the suggestions, we will start looking into those potential root causes.

With regard to the PUT command, is there any reason that the Snowflake monitor reports the PUT command being successful even though Replicate hasn't sent any data into Snowflake?

Regards,

Nak

[john_wang]

Hello Nak, @NakulanR 

Thanks for the update.

Are you meaning the PUT command failed in deed (as no CSV file was uploaded to Snowflake at all) however in the Snowflake monitor it shows PUT command was executed successfully? Can we reproduce the behavior manually by command or script?

Looks to me  the PUT command should return a status indicating whether the file upload was successful or not. However, there can be situations where the reported success might not align with the actual status of the file upload. Worth to have a deeper investigation.

Regards,

John.

[NakulanR]

Hi @john_wang & @DesmondWOO,

We managed to find the root cause of the issue. There was a security program on the network which blocked file transfers across the network. Hence, the CSV files generated by Replicate were unable to be PUT into the Snowflake stage, but the usual commands (e.g. Drop and Create table, Show tables like *) were able to be run in Snowflake. Appreciate your help and feedback on the issue.

Thanks,

Nak

[john_wang]

Thank you so much for your great support and valuable feedback! @NakulanR