Expose entire database schema/model via denodo data catalog

Hello, for a certain use case we would like to expose all tables in a SQL server database schema via Denodo (because of the rights management & catalog functionality). Is there a recommended way (VQL) to import with associations & descritpion all tables from a database schema? More or less the way it is done via the VDP Admin create base views, by clicking multiple tables in a JDBC data source and selecting the detect associations checkbox. But then automated so it can be scheduled as a job.
user
24-07-2019 03:06:19 -0400

3 Answers

Hi, In Denodo, you can utilize the inbuilt Stored procedure [GET_JDBC_DATASOURCE_TABLES](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/get_jdbc_datasource_tables#get-jdbc-datasource-tables) to return the list of tables in the JDBC data sources and [GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/generate_vql_to_create_jdbc_base_view) returns the VQL Statements for creating a JDBC base view for a given table. By combining both the stored procedures you can create base view and then schedule the job to execute the base view using[ Denodo Scheduler](https://community.denodo.com/docs/html/browse/7.0/scheduler/administration/creating_and_scheduling_jobs/creating_and_scheduling_jobs) periodically. This will automate the process of creating a base view for all the tables/views of a source database. As of now, it is only possible to import association by right click base view> Discover associations or while introspecting the tables during “Create Selected” and choosing the “create associations from foreign keys” option. For more information, you can refer to the document [Creating an Association](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/restful_architecture/associations/creating_an_association#creating-associations-for-jdbc-base-views-automatically) of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
24-07-2019 08:29:08 -0400
Hello, I was able to create a query that generates the VQL statements using the following query: ```SQL SELECT creation_vql FROM GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW() AS A INNER JOIN GET_JDBC_DATASOURCE_TABLES() AS B ON A.data_source_name = B.input_datasource_name AND A.table_name = B.table_name AND A.catalog_name = B.input_catalog_name and A.schema_name = B.input_schema_name WHERE B.input_datasource_name = '<JDBC Source>' AND B.input_catalog_name = 'Catalog' AND B.input_schema_name = 'Schema' AND B.input_type = 'TABLE' ``` This results in a list of VQL statements to create the base views. How can I have this VQL statements generated from these queries execute automatically, copying and pasting the output VQL (to a scheduled VDP job or the VQL shell) does not seem a good option als the list of tables in the database might have changes regularly. i.e. something like ```SQL EXECUTE() WHERE creation_vql IN (... the SELECT query from above ...) ```
user
24-07-2019 09:55:06 -0400
Hi, To automate VQL statements generated from the above queries, I would do the following steps * Create a custom stored procedure with a string parameter to execute DDL command for automating the creation of the base views. * To execute the DDL command you can use [executeVqlCommand](https://community.denodo.com/docs/html/browse/7.0/vdp/javadoc/com/denodo/vdb/engine/storedprocedure/DatabaseEnvironment.html#executeVqlCommand-java.lang.String-) because you cannot use the regular ExcuteQuery to run DDL command. * I have seen that [Denodo 4E plugin](https://community.denodo.com/docs/html/browse/7.0/denodo4e/index) helps developers to easily create, deploy and more importantly, debug the custom extensions for the Denodo Platform. * Then, deploy in the custom stored procedure in the Virtual Dataport. For creating a custom stored procedure, you can have a look at section [Developing Stored Procedures](https://community.denodo.com/docs/html/browse/7.0/vdp/developer/developing_extensions/developing_stored_procedures/developing_stored_procedures) of the Virtual DataPort Developer Guide. * After creating a Stored procedure, create a base view on top of the Stored procedure with interpolation variable. * Create a VDP job in Denodo Scheduler. * In Extraction section, call the base view in parameterized query with parameter > SELECT * FROM <BASEVIEW_NAME> WHERE <interpolation _variable> = @<parameter> And pass the select statement in the query (non-parameterized). * if you execute that job, it will automatically create the base view in Virtual Dataport. For more information, you can refer to the Tutoria[l How to implement a Stored Procedure](https://community.denodo.com/tutorials/browse/customcomponents/3storedprocedure). Hope this helps!
Denodo Team
31-07-2019 07:49:35 -0400
You must sign in to add an answer. If you do not have an account, you can register here