Replicate Views as Views not as Tables using Qlik Replicate

[satyender]

Greetings!

I tried to replicate views from the Source SQL Server RDS to Snowflake and what I am seeing is that the Views are replicating as Tables.

Can anyone suggest how to replicate Views as Views and not as Tables in the Target?

Thanks!

Satyender

[SwathiPulagam]

Hi @satyender ,

Thank you for contacting the Qlik community. Regarding your question table or view it goes as a table in the target, no way that we can send it as view. The reason is any view should have a physical table. Once replicate create a table then you can create a view on top of it.

Thanks,

Swathi

[satyender]

Thank you @SwathiPulagam  for your response.

The tables have been replicated to the Target. (That means there are tables already in the Target).

It beats me why the views need to be created again. 

There are two disadvantages with this:

1.  The database would be having redundancy of the tables.

2. While working with multi-tables in a view, this may lead to error prone view and also create multiple tables in the target.

3. Again the views need to be created manually which is not recommended on the target.

Instead of that it would be easy to deploy directly on target by getting the DDL from the source. But we don't want anything to be done manually. 

Please suggest.

Thanks!

Satyender

[john_wang]

Hello @satyender ,

Totally agree with @SwathiPulagam . The VIEW behavior is various among different database/data warehouse.

1.  In SQL Server database, the VIEW can be Updatable Views (MS Docs links) or read-only. All the VIEWs (include the UPDATABLE VIEWs and READ-ONLY VIEWs) are based on underlying tables, I'm afraid there is no way to create VIEW without underlying tables.

2.  BTW, VIEW can be replicated by Full Load ONLY task but cannot by regular CDC task, see my comment in article SQL Server does not record VIEW's data into TLOG .

3.  Regarding the target side Snowflake VIEW, there are two type of VIEWs: 

• Non-materialized views (usually simply referred to as "views"). it's READ-ONLY
• Materialized views. it's read-write.

In summary, it's impossible to write to regular VIEW in Snowflake. Even you replicate data to MV in Snowflake, then we come back to underlying table again: Materialized Views contain a copy of a subset of the data in a table. In short, Replicate map VIEW from source to TABLE in target, it's by-design and meet most of the RDBMS usage.

Hope this helps.

Regards,

John.

[satyender]

Thank you @john_wang for your response.

Yes, the tables are already created before replicating the views.

Question is if the tables are not there it would throw an error. That's not an issue. 

The question is why the Views are creating as Tables in the Snowflake while replicating from Qlik Replicate?

Thanks!

Satyender

[john_wang]

Hello @satyender ,

Besides the previous comment explanation, in short is that VIEW based on underlying TABLE always, and sometimes VIEW in read-only. We may let Replicate create the same name TALBE and MATERIALIZED VIEW at same time however I'm not sure if it's allowed in Snowflake. BTW, in Snowflake only Materialized Views can be read-write. Are you able to ensure you want to write to Materialized Views?

Thanks,

John.

[satyender]

Hello @john_wang ,

As I was mentioned earlier, I am concerned about the replication of Views as Tables in the target. 

When I replicated a view from Source to Target using Qlik replicate, the view was replicated as a table in the target instead of view under views folder. 

Why?

[Dana_Baldwin]

Hi @satyender 

Currently Qlik Replicate only supports creating tables in the target. To request our Product Managment team to consider supporting creation of views in the target, please create an enhancement request here: https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Dana

[nareshkumar]

Hi Swathi, 

Does all Oracle versions support  views  for full load replication?

[john_wang]

Hello @nareshkumar ,

Yes, all Oracle versions views can be replicated by Full Load tasks.

thanks,

John.