topic Re: Replicate Views as Views not as Tables using Qlik Replicate in Qlik Replicate

Replicate Views as Views not as Tables using Qlik Replicate

satyender — Thu, 09 Feb 2023 19:37:34 GMT

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

Re: Replicate Views as Views not as Tables using Qlik Replicate

SwathiPulagam — Thu, 09 Feb 2023 19:42:41 GMT

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

Re: Replicate Views as Views not as Tables using Qlik Replicate

satyender — Thu, 09 Feb 2023 19:53:52 GMT

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

Re: Replicate Views as Views not as Tables using Qlik Replicate

john_wang — Fri, 10 Feb 2023 13:41:54 GMT

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.

Re: Replicate Views as Views not as Tables using Qlik Replicate

satyender — Fri, 10 Feb 2023 21:47:21 GMT

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

Re: Replicate Views as Views not as Tables using Qlik Replicate

john_wang — Sat, 11 Feb 2023 04:55:00 GMT

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.

Re: Replicate Views as Views not as Tables using Qlik Replicate

satyender — Thu, 16 Feb 2023 22:29:58 GMT

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?

Re: Replicate Views as Views not as Tables using Qlik Replicate

Dana_Baldwin — Thu, 16 Feb 2023 23:26:38 GMT

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

Re: Replicate Views as Views not as Tables using Qlik Replicate

nareshkumar — Mon, 15 Jul 2024 19:22:59 GMT

Hi Swathi,

Does all Oracle versions support views for full load replication?

Re: Replicate Views as Views not as Tables using Qlik Replicate

john_wang — Mon, 15 Jul 2024 22:46:42 GMT

Hello @nareshkumar ,

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

thanks,

John.