Extract qlik sense DDL

[FreyInt]

Hello,

I am a data engineering and in our team with have qlik sense developpers.

It is connected to databases like hive, mysql and Oracle.

I would like to extract the name of the databases and tables on which qlik is connected

it could be a simple list or all the DDL stuff.

Do you know if there is a way to achive this ?

Thanks in advance for your help

Regards

[ArnadoSandoval]

Hi @FreyInt 

I am not 100% sure what you mean by your question, but QlikSense shows you all the objects available to your DB connection in the database, then you select the tables-views you need for your solution; or perhaps you want to experiment by connecting to the appropriated  System-Objects table in your database, like discussed at this threads

• how to pull all the tables at a time from database 
• Load script - loop through all tables in Access database 

 

Hope this helps,

[FreyInt]

Hello Arnaldo,

Thanks a lot for your reply.

Well it is not exactly what I am looking for.

On the screenshot you showed we can load data, and what I would like to have is the list af all the databases and tables we load into qlik.

I think the form you showed generate DDL inside qlik, somes queries for remove databases to load. When I said DDL it is what I meant, the Qlik SQL scripts used by Qlik to query remote db.

Kind Regards

[rwunderlich]

For a commercial solution, take a look at NodeGraph Data Atlas. It will automatically collect and show you all your tables and SQL queries in a nice interface. 

For an open source or free approach, I'm not sure if one of the tools like https://diagnostictoolkit.qlik-poc.com/#applicationMetadataAnalyzer might provide this. 

Lastly, you can write a bit of code yourself to get the Lineage data elements. The Lineage elements may not contain all the SQL dependent on how your Qlik scripts are structured.  https://help.qlik.com/en-US/sense-developer/April2020/APIs/EngineAPI/services-Doc-GetLineage.html

-Rob

[ArnadoSandoval]

@FreyInt 

Rob's suggestions are great, but there are things you could do right now, each QVD has builtin 3 XML header files with plenty of information about who created the QVD, its fields (columns), back-end source and QVD, this article Qlik QVD XML Header Viewer (the article was written at the time QVDs had two built-in XML header tables, they now have 3).

I found an old Qlik Application I wrote in 2017 consuming these QVD's XML data; I was a developer at the time and the Qlik applications name were Qlik Server internal Id (those huge string seemingly unrelated to the name we gave the application); I attached this application, so you can study the code (please do not run it, as you will wipe out its data)

I wrote a new version for this reply, it just retrieve a single QVD XML header, and its UI shows how meaningful this information is.

I did not find the advance version of the application written in 2017, that one was retrieving the XML headers of the QVDs used to build the target QVD, allowing us to trace (lineage) of the data consumed by the Qlik Applications and its origin.

You may also download QlikEye;  It is a good tools to open any QVD, export it to Excel, explore the QVD's header; a bit clumsy at time as well.

Hope this helps,

[rwunderlich]

@ArnadoSandoval Note your suggestion only applies to QVDs, which will not include queries issued from applications.  

If you want to explore QVDs, I of course recommend the QViewer tool. V4 with the Folder View will likely include the lineage info soon.  Of course, like reading the QVDs yourself,  this only covers QVDs. 

-Rob