Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We are replicating from MySQL into Snowflake endpoint. The target endpoint has a view having same name as a table.
A task has failed to load. Having error below due to the table having same name as a view in Snowflake.
00012228: 2024-07-04T20:44:15:581142 [TARGET_LOAD ]E: Failed (retcode -1) to execute statement: CREATE TABLE "SCHEMA"."TABLE20" ( ..............
columns A,B,C,D, etc....
..........
(ar_odbc_stmt.c:5038)
00012228: 2024-07-04T20:44:15:581142 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 42710 NativeError: 1998 Message: SQL compilation error:
Object 'TABLE20' already exists as VIEW [1022502] (ar_odbc_stmt.c:5046)
Once I change the view to another name, I am able to load the table.
Hello Desmond, @desmondchew
Thanks for reaching out to Qlik Community!
Basically there are 2 types of VIEWs in Snowflake:
• Non-materialized views (usually simply referred to as "Views"). it's READ-ONLY.
• Materialized Views (referred to "MV"). it's read-write.
So it's impossible to replicate data into regular VIEW(s). You may try to write to MV by changing the Target Table Preparation to DO NOTHING, screen sample:
hope this helps.
John.
BTW, @desmondchew , please take note that in general the VIEW data is subset of the underlying table, if you replicate data into Materialized Views, maybe some columns in the underlying table(s) are empty/null.
In short, we'd like suggest you to use TABLE as target side object instead of Materialized View.