source information_schema

[simonB2020]

Source Endpoint is AWS Auroa (postgres)
When selecting source tables, I am unable to see/find the information_schema objects.
The endpoint's credentials do have permissions to these in the source system - so wondering if there is a filter behind the scenes that is preventing these from showing ?

Obv if so, is it possible to get past it ?

Thanks

[john_wang · Accepted solution]

Hello @simonB2020 ,

Thanks for opening article in Qlik community!

You are right, Qlik Replicate will filter out the object from schema "information_schema".

"information_schema" exists in every database of PostgreSQL. it contains database objects metadata. By default the user data is not stored under this schema and it's meaningless to replicate objects (tables, views etc) under this schema.

Hope this helps.

Regards,

John.

[simonB2020]

Thanks John.
Possibly 'meaningless' is in the eyes of the beholder ? 😉

One example usage is that it would provide us a very convenient way of comparing the column definitions on the source DB with the column definitions on the target.
To expand further with a use case ...
I have an AWS aurora Source with several data types that do not convert well with Replicate.
JSON type for example. These become varchars in Redshift and are not always made wide enough for the content.  Within m my target I cannot identify these columns as they are simple varchar and get lost with all the other varchars. However, if I have the information_schema.columns table from the source on my target , I could very quickly and easily isolate those columns for further inspection. Such as checking to see if they need a longer length than default provisioned for by QR.

Thanks

[john_wang · Accepted solution]

Hello @simonB2020 ,

I'm not a beholder 🙂 so I researched further today even though I've known about the INFORMATION_SCHEMA in a few databases. Now I learned it exists in many databases, because actually it's a part of the SQL-92 standard. for example (these databases are supported by Qlik Replicate):

• PostgreSQL
• Databricks
• Microsoft SQL Server
• MySQL
• MariaDB
• Redshift
• SingleStore
• Snowflake

As an important database internal usage object, it's maybe a table, or view (depends on db type), it contains metadata of tables, views, procedures, privileges etc, all objects. It should not be updated by an app directly. As a heterogeneous databases replication product, Qlik Replicate is not designed to update INFORMATION_SCHEMA objects. This is why the filter is there. It's by design (hard coded) and I do not think we can past it.

Regarding your use case, I think we do not need a real time replication but you may fresh the information once you need it in the target side. Let's consider defining a VIEW in PostgreSQL source database:

create view info_sche_cols_view as select * from information_schema.columns;

then define ODBC DSN source and run a Full Load ONLY task to replicate this view to target.

Hope this helps.
Regards,
John.

[simonB2020]

I understand that it is not designed to be updated, but that fact should not be a concern for QR because the source system would never allow you to update it - it is by design a read only object.
Agree we do not need real time replication, but QR also offers Full Load tasks - so no reason to deny them the ability to read these types of objects.

Creating a view in the source is theoretically feasible, but in a real life scenario not - developing inside an application's database creates a whole raft of issues where 'any' change has to  be integrated with full scale product management processes (approvals, permissions, testing, change/release; impact analysis etc etc )  And if the source DB is 3rd party then even worse scenario.

Could we add this as a feature request ?

[Heinvandenheuvel]

>> Could we add this as a feature request ?

You could, but I suspect it ain't going to happen.

I think you will have more success creating your own daily or weekly 'user-space' copy of any 'system' information_schema structure.

Just call the copy 'my_information_schema_yyyymmdd' and configure replicate for 'my_information_schema_*'

Replicate will pick them up as they get created. You could possibly rename them on target to a non-date-stamped-simple single name., or use views on target. With a plan like this, you also have the option to audit/check any changes on the source itself. Purge (drop) the user copies as they get a certain age. This is typically the first step in a script which is scheduled to take the next copy.

Hein.

[lyka]

Hello, 

To submit Feature Requests you can go to our  Qlik Community "Product Insight and Ideas" forum .

This forum is closely monitored by our product management team for ideas to improve our product line and is the best place to submit and check status of your ideas going forward.

To get started please see our article: "Getting Started with Ideas": https://community.qlik.com/t5/Qlik-Product-Insight-Blog/Getting-Started-with-Ideas/ba-p/1684011

Thanks

Lyka