• User Manuals »
  • Denodo VQL Generation Stored Procedures - User Manual

Denodo VQL Generation Stored Procedures - User Manual

Download original document


Overview

This component contains three Stored Procedure that return the VQL statements required to create a Delimited File, XML, EXCEL or JSON Base View. These statements will include the creation of all components related with the base view such as data source, wrapper, complex types, tables and/or folder.

There are several procedures for each type of supported data source. It depends on the stored procedure type: you can create a data source, a base view, or both of them. You can also use an existing data source as a template from which all connection parameters will be copied.

Note: There is a similar stored procedure in VDP that returns the VQL statements required to create a JDBC base view. Follow this link for more information.

Also note that these procedures do not create the data sources or base views themselves, they just return the VQL statements to do so.

Denodo VQL Generation Stored Procedure version 20221006 or later requires VDP 8.0 update 20220815.
If your
VDP is earlier than 20220815 you can download the version 20220829 of Denodo VQL Generation Stored Procedure.

DF

Installation

Importing the Stored Procedure

In order to run the GenerateVQLForDFFullDataSourceBaseViewStoredProcedure stored procedure you have to load the denodo-vqlgeneration.jar file, using the File > Jar Management option of the VDP Administration Tool.

The class GenerateVQLToCreateDFBaseViewStoredProcedure is deprecated.

Adding the Stored Procedure

VQL Shell

You can add the stored procedure programmatically with a CREATE PROCEDURE statement:

CREATE [OR REPLACE] PROCEDURE <name:identifier> CLASSNAME='com.denodo.connect.storedprocedure.df.GenerateVQLForFullDataSourceBaseViewStoredProcedure'

JARS 'denodo-vqlgeneration';

        [ FOLDER = <literal> ]

        [ DESCRIPTION = <literal> ]

Virtual DataPort Administration Tool menu

You can also add the stored procedure by clicking on Stored procedure on the menu File > New:

You must input a name in the Name field and select the Select Jars checkbox in order to use the Jar file, denodo-vqlgeneration, previously added to the Virtual DataPort Server.

Description

The GENERATE_VQL_TO_CREATE_DF_DS_BV stored procedure returns the VQL statements required to create a Delimited File base view from a delimited file. The stored procedure allows you to use an existing data source as a template to copy the configuration of the route, minus the field path or the url. Note that it does not actually create the view, it only returns the VQL statements to do so.

Syntax

GENERATE_VQL_TO_CREATE_DF_BASE_VIEW (

     data_source_name : text,

     database_name : text,

     base_view_name : text,

     route_type: text,

     file_path_url: text,

     authentication: text,

     username: text,

     password: text,

     encrypted_password: text,

     folder : text,

     i18n : text,

     template_datasource: text,

     template_database: text,

     ignore_matching_errors:text,

     column_delimiter:text,

     eol_delimiter:text:

     has_headers:text

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • base_view_name: name of the base view to be created. If null, the name will be auto-generated.
  • route_type: specify the types of paths to access the file/s. Mandatory no template is specified.
  • LOCAL: the file or set of files to be used are located in the server machine.
  • HTTP: the data to be used has to be obtained by sending an HTTP request to a server.
  • file_path_url: route to the delimited file or files. It can be a file path or a url. Mandatory.
  • authentication: authentication type (OFF|BASIC). Mandatory no template is specified.
  • username: HTTP user.
  • password: HTTP password.
  • encrypted_password: whether the password has been specified encrypted (as per Denodo VQL).
  • folder: folder in which to place the created structures. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.
  • i18n: i18n of the base view to be created. If null, the i18n of the current database will be assigned.
  • template_database: name of the database where the template data source lives. If null and a template data source is specified, the current database will be assumed to contain it.
  • template_datasource: name of the data source from which the route configuration will be copied in the VQL. This parameter is mutually exclusive with authentication, username, password and  encrypted_password. The route path will be modified by the field file_path_url.
  • ignore_matching_errors: whether the wrapper should ignore lines of this data file(s) that do not have the expected structure (or else fail).
  • column_delimiter: character that separates the values of a row.
  • eol_delimiter: character string used as data tuple separator in the delimited file (\n line feeds will be used by default).
  • has_headers: If true, it is assumed that the first tuple extracted from the file data area contains the names of the fields. These names will be used to create the attributes of the base relation for Virtual DataPort.

Execution

Execute as a SELECT statement in the VQL Shell without template

In order to obtain the VQL statements you have to specify the datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

All the parameters related with the datasource have to be introduced: route_type, authentication and  file_path_url. Authentication parameters username, password and encrypted_password may be necessary also.

In addition, DF base views require the following parameters: i18, column_delimiter, eol_delimite, has_headers and ignore_matching_errors.

Sample and results

SELECT result FROM GENERATE_VQL_TO_CREATE_DF_BASE_VIEW()

WHERE datasource_name = 'gen_test_df'

and route_type = 'HTTP'

and file_path_url = 'http://localhost:8082/test/user.csv'

and authentication = 'BASIC'

and username = 'tomcat'

and password = '0IgHioLFJfQ76X5sTbsHBinjsqQxUQas7qhjEyxCfZKwxn'

and encrypted_password = true

and folder = NULL

and database_name = 'sps'

and i18 = NULL

and base_view_name = 'test34'

and column_delimiter = ';'

and eol_delimiter= '\n'

and has_headers = TRUE

and ignore_matching_errors = true;

Execute as a SELECT statement in the VQL Shell using a template


In order to obtain the VQL statements you have to specify the
datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

If you use another data source as a template in order to copy the route configuration you will need to introduce the name of the database (template_database) and the name of the template data source (template_datasource). This way parameters route_type, authentication,  username, password and encrypted_password will be imported from the template. But you have to introduce the file_path_url parameter, which is mandatory as the file path of the template data source will be overwritten.

Note that despite using the template, parameters corresponding with the base view (and not the data source) are still mandatory: i18, column_delimiter, eol_delimiter, has_headers and ignore_matching_errors.

Sample and results

SELECT result FROM GENERATE_VQL_TO_CREATE_DF_BASE_VIEW()

WHERE datasource_name = 'gen_test_df'

 and file_path_url = 'http://localhost:8082/test/user.csv'

 and folder = '/testds'

 and database_name = 'admin'

 and i18 = NULL

 and column_delimiter = ';'

 and eol_delimiter = '\n'

 and has_headers = TRUE

 and ignore_matching_errors = true

 and base_view_name = 'test49'  

 and template_datasource = 'spfromds_test_df'

 and template_database = 'admin'

XML

There are 4 stored procedures to create XML data sources or XML base Views.

GenerateVQLForXMLFullDataSourceBaseViewStoredProcedure

GenerateVQLForXMLDataSourceBaseViewStoredProcedure

GenerateVQLForXMLDataSourceStoredProcedure
GenerateVQLForXMLBaseViewStoredProcedure

 The different stored procedures will be explained below.

Installation

Importing the Stored Procedure

In order to run the GenerateVQLToCreateXMLBaseView stored procedure you have to load the denodo-vqlgeneration.jar file, using the File > Jar Management option of the VDP Administration Tool.

The class GenerateVQLToCreateXMLBaseViewStoredProcedure is deprecated.

Adding the Stored Procedure

VQL Shell

You can add the stored procedure programmatically with a CREATE PROCEDURE statement, you have to select the class, depending on the stored procedure you use.

.

CREATE [OR REPLACE] PROCEDURE <name:identifier> CLASSNAME='com.denodo.connect.storedprocedure.xml.GenerateVQLForXMLDataSourceBaseViewStoredProcedure'

JARS 'denodo-vqlgeneration';

        [ FOLDER = <literal> ]

        [ DESCRIPTION = <literal> ]

Virtual DataPort Administration Tool menu

You can also add the stored procedure by clicking on Stored procedure on the menu File > New:

You must input a name in the Name field and select the Select Jars checkbox in order to use the Jar file, denodo-vqlgeneration, previously added to the Virtual DataPort Server. And select the Class name in the drop-down.

GENERATE_VQL_TO_CREATE_XML_FULL_DATA_SOURCE_BASE_VIEW

Description

The GENERATE_VQL_TO_CREATE_XML_FULL_DATA_SOURCE_BASE_VIEW stored procedure returns the VQL statements required to create a XML data source and a XML base view from an XML file.The stored procedure allows you to use an existing data source as a template to copy the configuration of the route, minus the field path or the url. Note that it does not actually create the view, it only returns the VQL statements to do so.

Syntax

GENERATE_VQL_TO_CREATE_XML_FULL_DATA_SOURCE_BASE_VIEW (

     data_source_name : text,

     database_name : text,

     base_view_name : text,

     route_type: text,

     file_path_url: text,

     authentication: text,

     username: text,

     password: text,

     encrypted_password: text,

     folder : text,

     i18n : text,

     template_datasource: text,

     template_database: text

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • base_view_name: name of the base view to be created. If null, the name will be auto-generated.
  • route_type: specify the types of paths to access the file/s. Mandatory no template is specified.
  • LOCAL: the file or set of files to be used are located in the server machine.
  • HTTP: the data to be used has to be obtained by sending an HTTP request to a server.
  • file_path_url: route to the delimited file or files. It can be a file path or a url. If the route_type is http ,it is data source’s URL, it can be base url or absolute url including the filename. Mandatory.
  • authentication: authentication type (OFF|BASIC). Mandatory no template is specified.
  • username: HTTP user.
  • password: HTTP password.
  • encrypted_password: whether the password has been specified encrypted (as per Denodo VQL).
  • folder: folder in which to place the created structures. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.
  • i18n: i18n of the base view to be created. If null, the i18n of the current database will be assigned.
  • template_database: name of the database where the template data source lives. If null and a template data source is specified, the current database will be assumed to contain it.
  • template_datasource: name of the data source from which the route configuration will be copied in the VQL. This parameter is mutually exclusive with authentication, username, password and  encrypted_password. The route path will be modified by the field file_path_url.

Execution

Execute as a SELECT statement in the VQL Shell without template


In order to obtain the VQL statements you have to specify the
datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

All the parameters related with the datasource have to be introduced: route_type, authentication and  file_path_url. Authentication parameters username, password and encrypted_password may be necessary also.

Sample and results

SELECT result FROM GENERATE_VQL_TO_CREATE_XML_FULL_DATA_SOURCE_BASE_VIEW()

WHERE datasource_name = 'gen_test_xml'

and route_type = 'LOCAL'

and file_path_url = 'C:/Users/denodo/Downloads/testsimple.xml'

and authentication = 'OFF'

and username = null

and password = null

and encrypted_password = false

and folder = NULL

and database_name = 'sps'

and i18 = NULL

and base_view_name = 'test136sps'

Execute as a SELECT statement in the VQL Shell using a template


In order to obtain the VQL statements you have to specify the
datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

If you use another data source as a template in order to copy the route configuration you will need to introduce the name of the database (template_database) and the name of the template data source (template_datasource). This way parameters route_type, authentication,  username, password and encrypted_password will be imported from the template. But you have to introduce the file_path_url parameter, which is mandatory as the file path of the template data source will be overwritten.

The i18 parameter is also mandatory.

Sample and results

SELECT result FROM GENERATE_VQL_TO_CREATE_XML_FULL_DATA_SOURCE_BASE_VIEW()

WHERE datasource_name = 'gen_test_xml'

and file_path_url = 'C:/Users/denodo/Downloads/simple.xml'

and folder = '/testxml'

and database_name = 'admin'

and i18 = NULL

and base_view_name = 'test40'

and template_datasource = 'spfromds_test_xml_2'

and template_database = 'sps'

GENERATE_VQL_TO_CREATE_XML_DATA_SOURCE_BASE_VIEW

Description

The GENERATE_VQL_TO_CREATE_XML_DATA_SOURCE_BASE_VIEW stored procedure returns the VQL statements required to create a XML data source and a XML base view from an XML file. With this stored procedure if the route_type is HTTP, using relative_url parameter, we can differentiate the data source URL and base view URL, the base URL(data source) will be concatened to base view's relative URL. Note that it does not actually create the view, it only returns the VQL statements to do so. This stored procedure requires VDP 8.0 update 20220815 or later.

Syntax

GENERATE_VQL_TO_CREATE_XML_BASE_VIEW (

     data_source_name : text,

     database_name : text,

     base_view_name : text,

     route_type: text,

     file_path_url: text,

     relative_url: text,

     authentication: text,

     username: text,

     password: text,

     encrypted_password: text,

     folder : text,

     i18n : text

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • base_view_name: name of the base view to be created. If null, the name will be auto-generated.
  • route_type: specify the types of paths to access the file/s. Mandatory no template is specified.
  • LOCAL: the file or set of files to be used are located in the server machine.
  • HTTP: the data to be used has to be obtained by sending an HTTP request to a server.
  • file_path_url: route to the delimited file or files. It can be a file path or a url. If the route_type is http ,it is data source’s URL, it can be base url or absolute url including the filename. Mandatory.
  • relative_url: Only it is used when the route_type is HTTP, and it is optional.  Base view’s relative URL (for example: “/books.xml”), this URL will be concatenated to the Base URL when executing the base view (the resulting URL will be http://www.test.com/books.xml).
  • authentication: authentication type (OFF|BASIC). Mandatory no template is specified.
  • username: HTTP user.
  • password: HTTP password.
  • encrypted_password: whether the password has been specified encrypted (as per Denodo VQL).
  • folder: folder in which to place the created structures. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.
  • i18n: i18n of the base view to be created. If null, the i18n of the current database will be assigned.

Execution


In order to obtain the VQL statements you have to specify the
datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

All the parameters related with the datasource have to be introduced: route_type, authentication and  file_path_url. Authentication parameters username, password and encrypted_password may be necessary also.

Belonging to the base view there is the optional i18n parameter. And if the data source has a base URL,  relative_url parameter allows having a relative URL in the base view.

Sample

SELECT result FROM generate_vql_to_create_xml_data_source_base_view()

WHERE datasource_name='test_xml_datasource'

and route_type='HTTP'

and file_path_url='https://www.w3schools.com/xml'

and authentication='OFF'

and folder='/testsd'

and database_name='admin'  

and base_view_name='test_xml_datasource_all_base_view'

and relative_url='cd_catalog.xml'

GENERATE_VQL_TO_CREATE_XML_DATA_SOURCE

Description

The GENERATE_VQL_TO_CREATE_XML_DATA_SOURCE stored procedure returns the VQL statements required to create a XML data source from an XML file. Note that it does not actually create the view, it only returns the VQL statements to do so.  This stored procedure requires VDP 8.0 update 20220815 or later.

Syntax

GENERATE_VQL_TO_CREATE_XML_DATA_SOURCE (

     data_source_name : text,

     database_name : text,

     route_type: text,

     file_path_url: text,

     authentication: text,

     username: text,

     password: text,

     encrypted_password: text,

     folder : text

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • route_type: specify the types of paths to access the file/s. Mandatory no template is specified.
  • LOCAL: the file or set of files to be used are located in the server machine.
  • HTTP: the data to be used has to be obtained by sending an HTTP request to a server.
  • file_path_url: route to the delimited file or files. It can be a file path or a url. If the route_type is http ,it is data source’s URL, it can be base url or absolute url including the filename. Mandatory.
  • authentication: authentication type (OFF|BASIC). Mandatory no template is specified.
  • username: HTTP user.
  • password: HTTP password.
  • encrypted_password: whether the password has been specified encrypted (as per Denodo VQL).
  • folder: folder in which to place the created structures. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.

Execution


In order to obtain the VQL statements you have to specify the
datasource_name. And also choose the database where the base view would be stored, and a folder if it is the case.

All the parameters related with the datasource have to be introduced: route_type, authentication and  file_path_url. Authentication parameters username, password and encrypted_password may be necessary also.

Sample

SELECT result FROM generate_vql_to_create_xml_data_source()

WHERE datasource_name='test_xml_datasource'

and route_type='HTTP'

and file_path_url='https://www.w3schools.com/xml'

and authentication='OFF'

and folder='/testsd'

and database_name='admin'

GENERATE_VQL_TO_CREATE_XML_BASE_VIEW

Description

The GENERATE_VQL_TO_CREATE_XML_BASE_VIEW stored procedure returns the VQL statements required to create an XML base view from an XML file, over a existing data source. If the route type of the data source is HTTP, and the path is not a complete URL, the relative_url would be added to the base URL. Note that it does not actually create the view, it only returns the VQL statements to do so.  This stored procedure requires VDP 8.0 update 20220815 or later.

Syntax

GENERATE_VQL_TO_CREATE_XML_BASE_VIEW (

     data_source_name : text,

     database_name : text,

     base_view_name : text,

     file_path_url: text,

     relative_url: text,  

     i18n : text

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • base_view_name: name of the base view to be created. If null, the name will be auto-generated.
  • i18n: i18n of the base view to be created. If null, the i18n of the current database will be assigned.
  • relative_url: Only it is used when the route_type is HTTP, and it is optional.  Base view’s relative URL (for example: “/books.json”), this URL will be concatenated to the Data Source URL when executing the base view.

Execution


In order to obtain the VQL statements you have to specify the
datasource_name and the base_view_name. And also choose the database where the data source is stored.In addition there is the optional i18n parameter.

Sample

SELECT result FROM GENERATE_VQL_TO_CREATE_XML_BASE_VIEW()

WHERE datasource_name='test_xml_datasource'

and database_name='admin'

and base_view_name='test_xml_datasource_bv'

and relative_url='cd_catalog.xml'

JSON

Installation

Importing the Stored Procedure

In order to run the GenerateVQLToCreateJSONBaseView stored procedure you have to load the denodo-vqlgeneration.jar file, using the File > Jar Management option of the VDP Administration Tool.
The class
GenerateVQLToCreateJSONBaseViewStoredProcedure is deprecated.

Adding the Stored Procedure

VQL Shell

You can add the stored procedure programmatically with a CREATE PROCEDURE statement, you have to select the class, depending on the stored procedure you use.

CREATE [OR REPLACE] PROCEDURE <name:identifier> CLASSNAME='com.denodo.connect.storedprocedure.json.GenerateVQLForJSONDataSourceBaseViewStoredProcedure'

JARS 'denodo-vqlgeneration';

        [ FOLDER = <literal> ]

        [ DESCRIPTION = <literal> ]

Virtual DataPort Administration Tool menu

You can also add the stored procedure by clicking on Stored procedure on the menu File > New:

You must input a name in the Name field and select the Select Jars checkbox in order to use the Jar file, denodo-vqlgeneration, previously added to the Virtual DataPort Server.  And select the Class name that you need, in the drop-down.

GENERATE_VQL_TO_CREATE_JSON_FULL_DATA_SOURCE_BASE_VIEW

Description

The GENERATE_VQL_TO_CREATE_JSON_FULL_DATA_SOURCE_BASE_VIEW stored procedure returns the VQL statements required to create a JSON data source and a JSON base view from a JSON file. The stored procedure allows you to use an existing data source as a template to copy the configuration of the route, minus the field path or the url. Note that it does not actually create the view, it only returns the VQL statements to do so.

Syntax

GENERATE_VQL_TO_CREATE_JSON_FULL_DATA_SOURCE_BASE_VIEW (

     data_source_name : text,

     database_name : text,

     base_view_name : text,

     route_type: text,

     file_path_url: text,    

     authentication: text,

     username: text,

     password: text,

     encrypted_password: text,

     folder : text,

     i18n : text,

     template_datasource: text,

     template_database: text

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • base_view_name: name of the base view to be created. If null, the name will be auto-generated.
  • route_type: specify the types of paths to access the file/s. Mandatory no template is specified.
  • LOCAL: the file or set of files to be used are located in the server machine.
  • HTTP: the data to be used has to be obtained by sending an HTTP request to a server.
  • file_path_url: route to the delimited file or files. It can be a file path or a url. If the route_type is http ,it is data source’s URL, it can be base url or absolute url including the filename. Mandatory.
  • authentication: authentication type (OFF|BASIC). Mandatory no template is specified.
  • username: HTTP user.
  • password: HTTP password.
  • encrypted_password: whether the password has been specified encrypted (as per Denodo VQL).
  • folder: folder in which to place the created structures. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.
  • i18n: i18n of the base view to be created. If null, the i18n of the current database will be assigned.
  • template_database: name of the database where the template data source lives. If null and a template data source is specified, the current database will be assumed to contain it.
  • template_datasource: name of the data source from which the route configuration will be copied in the VQL. This parameter is mutually exclusive with authentication, username, password and  encrypted_password. The route path will be modified by the field file_path_url.

Execution

Execute as a SELECT statement in the VQL Shell without template

In order to obtain the VQL statements you have to specify the datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

All the parameters related with the datasource have to be introduced: route_type, authentication and  file_path_url. Authentication parameters username, password and encrypted_password may be necessary also.

Sample and results

SELECT result FROM GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE_BASE_VIEW()

WHERE datasource_name='gen_test_json'

and route_type='LOCAL'

and file_path_url='C:/Users/denodo/Downloads/test.json'

and authentication='OFF'

and username=NULL

and password=NULL

and encrypted_password=false

and folder='/testspjson'

and database_name='admin'

and i18=NULL

and base_view_name='test41'

Execute as a SELECT statement in the VQL Shell using a template


In order to obtain the VQL statements you have to specify the
datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

If you use another data source as a template in order to copy the route configuration you will need to introduce the name of the database (template_database) and the name of the template data source (template_datasource). This way parameters route_type, authentication,  username, password and encrypted_password will be imported from the template. But you have to introduce the file_path_url parameter, which is mandatory as the file path of the template data source will be overwritten.

The i18 parameter is also mandatory.

Sample and results

SELECT result FROM GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE_BASE_VIEW()

WHERE datasource_name = 'gen_test_json'

and file_path_url = 'http://localhost:8082/test/test.json'

and folder = '/testspjson'

and database_name = 'admin'

and i18 = NULL

and base_view_name = 'test46'

and template_datasource = 'spfromjson_test'

and template_database = 'admin'

GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE_BASE_VIEW

Description

The GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE_BASE_VIEW stored procedure returns the VQL statements required to create a JSON data source and a JSON base view from a JSON file. With this stored procedure if the route_type is HTTP, using relative_url parameter, we can differentiate the data source URL and base view URL, the base URL(data source) will be concatened to base view's relative URL. Note that it does not actually create the view, it only returns the VQL statements to do so. This stored procedure requires VDP 8.0 update 20220815 or later.

Syntax

GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE_BASE_VIEW (

     data_source_name : text,

     database_name : text,

     base_view_name : text,

     route_type: text,

     file_path_url: text,  

     relative_url: text,  

     authentication: text,

     relative_url: text,

     username: text,

     password: text,

     encrypted_password: text,

     folder : text,

     i18n : text

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • base_view_name: name of the base view to be created. If null, the name will be auto-generated.
  • route_type: specify the types of paths to access the file/s. Mandatory no template is specified.
  • LOCAL: the file or set of files to be used are located in the server machine.
  • HTTP: the data to be used has to be obtained by sending an HTTP request to a server.
  • file_path_url: route to the delimited file or files. It can be a file path or a url. If the route_type is http ,it is data source’s URL, it can be base url or absolute url including the filename. Mandatory.
  • relative_url: Only it is used when the route_type is HTTP, and it is optional.  Base view’s relative URL (for example: “/books.json”), this URL will be concatenated to the Base URL when executing the base view (the resulting URL will be http://www.test.com/books.json).
  • authentication: authentication type (OFF|BASIC). Mandatory no template is specified.
  • username: HTTP user.
  • password: HTTP password.
  • encrypted_password: whether the password has been specified encrypted (as per Denodo VQL).
  • folder: folder in which to place the created structures. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.
  • i18n: i18n of the base view to be created. If null, the i18n of the current database will be assigned.

Execution

In order to obtain the VQL statements you have to specify the datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

All the parameters related with the datasource have to be introduced: route_type, authentication and  file_path_url. Authentication parameters username, password and encrypted_password may be necessary also.

Belonging to the base view there is the optional i18n parameter. And if the data source has a base URL,  relative_url parameter allows having a relative URL in the base view.

Sample

SELECT result FROM GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE_BASE_VIEW()

WHERE datasource_name='spfromds_test_2'

and route_type='HTTP'

and file_path_url='https://filesamples.com/samples/code/json'

and authentication='OFF'

and folder='/testspjson'

and database_name='admin'

and base_view_name='spfromds_testbv_2'

and relative_url= 'sample4.json'

GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE

Description

The GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE stored procedure returns the VQL statements required to create a JSON data source.. Note that it does not actually create the view, it only returns the VQL statements to do so.  This stored procedure requires VDP 8.0 update 20220815 or later.

Syntax

GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE (

     data_source_name : text,

     database_name : text,

     route_type: text,

     file_path_url: text,    

     authentication: text,

     username: text,

     password: text,

     encrypted_password: text,

     folder : text  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • route_type: specify the types of paths to access the file/s. Mandatory no template is specified.
  • LOCAL: the file or set of files to be used are located in the server machine.
  • HTTP: the data to be used has to be obtained by sending an HTTP request to a server.
  • file_path_url: route to the delimited file or files. It can be a file path or a url. If the route_type is http ,it is data source’s URL, it can be base url or absolute url including the filename. Mandatory.
  • authentication: authentication type (OFF|BASIC). Mandatory no template is specified.
  • username: HTTP user.
  • password: HTTP password.
  • encrypted_password: whether the password has been specified encrypted (as per Denodo VQL).
  • folder: folder in which to place the created structures. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.

Execution

In order to obtain the VQL statements you have to specify the datasource_name And also choose the database where the base view would be stored, and a folder if it is the case.

All the parameters related with the datasource have to be introduced: route_type, authentication and  file_path_url. Authentication parameters username, password and encrypted_password may be necessary also.

Sample

SELECT result FROM GENERATE_VQL_TO_CREATE_JSON_DATA_SOURCE_BASE_VIEW()

WHERE datasource_name='spfromds_test'

and route_type='HTTP'

and file_path_url='https://filesamples.com/samples/code/json'

and authentication='OFF'

and folder='/testspjson'

and database_name='admin'

GENERATE_VQL_TO_CREATE_JSON_BASE_VIEW

Description

The GENERATE_VQL_TO_CREATE_JSON_BASE_VIEW stored procedure returns the VQL statements required to create a JSON base view from a JSON file. If the route type of the data source is HTTP, and the path is not a complete URL, the relative_url would be added to the base URL. Note that it does not actually create the view, it only returns the VQL statements to do so.   This stored procedure requires VDP 8.0 update 20220815 or later.

Syntax

GENERATE_VQL_TO_CREATE_JSON_BASE_VIEW (

     data_source_name : text,

     database_name : text,

     base_view_name : text,

     relative_url: text,  

     i18n : text

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • base_view_name: name of the base view to be created. If null, the name will be auto-generated.
  • relative_url: Only it is used when the route_type is HTTP, and it is optional.  Base view’s relative URL (for example: “/books.json”), this URL will be concatenated to the Base URL when executing the base view (the resulting URL will be http://www.test.com/books.json).
  • i18n: i18n of the base view to be created. If null, the i18n of the current database will be assigned

Execution

In order to obtain the VQL statements you have to specify the datasource_name and the base_view_name. And also choose the database where the dat source is stored.In addition there is the optional i18n parameter.

Sample

SELECT result FROM GENERATE_VQL_TO_CREATE_JSON_BASE_VIEW()

WHERE datasource_name='spfromds_test'

and database_name='admin'

and base_view_name='spfromds_testbv'

and relative_url= 'sample4.json'

Excel

Installation

Importing the Stored Procedure

In order to run the GenerateVQLForExcelFullDataSourceBaseView stored procedure you have to load the denodo-vqlgeneration.jar file, using the File > Jar Management option of the VDP Administration Tool.

The class GenerateVQLToCreateExcelBaseViewStoredProcedure is deprecated.

Adding the Stored Procedure

VQL Shell

You can add the stored procedure programmatically with a CREATE PROCEDURE statement:

CREATE [OR REPLACE] PROCEDURE <name:identifier> CLASSNAME='com.denodo.connect.storedprocedure.excel.GenerateVQLForExcelFullDataSourceBaseView'

JARS 'denodo-vqlgeneration';

        [ FOLDER = <literal> ]

        [ DESCRIPTION = <literal> ]

Virtual DataPort Administration Tool menu

You can also add the stored procedure by clicking on Stored procedure on the menu File > New:

You must input a name in the Name field and select the Select Jars checkbox in order to use the Jar file, denodo-vqlgeneration, previously added to the Virtual DataPort Server.

Description

The GENERATE_VQL_FOR_EXCEL_FULL_DATA_SOURCE_BASE_VIEW stored procedure returns the VQL statements required to create a Excel base view from a Excel file. The stored procedure allows you to use an existing data source as a template to copy the configuration of the route, minus the field path or the url. Note that it does not actually create the view, it only returns the VQL statements to do so.

Syntax

GENERATE_VQL_FOR_EXCEL_FULL_DATA_SOURCE_BASE_VIEW (

     data_source_name : text,

     database_name : text,

     base_view_name : text,

     route_type: text,

     file_path_url: text,

     authentication: text,

     username: text,

     password: text,

     encrypted_password: text,

     folder : text,

     i18n : text,

     template_datasource: text,

     template_database: text,

     type_of_file:text,

     worksheets:text,

     extract_data_from_all_sheets:boolean,

     extract_sheet_name_as_a_new_column:boolean,

     start_cell:text,

     end_cell:text,

     has_headers:boolean,

     stream_tuples:boolean

  )

  • data_source_name: name of the data source. Mandatory.
  • database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
  • base_view_name: name of the base view to be created. If null, the name will be auto-generated.
  • route_type: specify the types of paths to access the file/s. Mandatory no template is specified.
  • LOCAL: the file or set of files to be used are located in the server machine.
  • HTTP: the data to be used has to be obtained by sending an HTTP request to a server.
  • file_path_url: route to the delimited file or files. It can be a file path or a url. Mandatory.
  • authentication: authentication type (OFF|BASIC). Mandatory no template is specified.
  • username: HTTP user.
  • password: HTTP password.
  • encrypted_password: whether the password has been specified encrypted (as per Denodo VQL).
  • folder: folder in which to place the created structures. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.
  • i18n: i18n of theE base view to be created. If null, the i18n of the current database will be assigned.
  • template_database: name of the database where the template data source lives. If null and a template data source is specified, the current database will be assumed to contain it.
  • template_datasource: name of the data source from which the route configuration will be copied in the VQL. This parameter is mutually exclusive with authentication, username, password and  encrypted_password. The route path will be modified by the field file_path_url.
  • type_of_file: Depending on the version of the file:
            "Excel 97-2003 (*.xls)"
            "Excel 2007 or later (*.xlsx)"

The option “Stream tuples” will be enabled or not (see below).

  • worksheets: (optional). Name of the worksheet that you want to extract data from.

If left blank, the first sheet of the file is used by default.

  • extract_data_from_all_sheets: if true, the base view will retrieve data from all sheets,
  • extract_sheet_name_as_a_new_column: boolean,if you want the base view to have an extra column that will contain the name of the worksheet from which each row is obtained from. The default name of this extra column is “sheet_name”.
  • start_cell,end_cell: (optional): first and last cell of the sheet that will be processed.

If “StartCell” is B2 and “EndCell” is Z99, the view will return the data in the range B2:Z99.

  • has_headers: If true, it is assumed that the first tuple extracted from the file data area contains the names of the fields. These names will be used to create the attributes of the base relation for Virtual DataPort.
  • stream_tuples: if selected, the file will be read using a SAX parser (only available for Excel 2007 files (*.xslx)). If cleared, it will be read using a DOM parser.

Execution

Execute as a SELECT statement in the VQL Shell without template

In order to obtain the VQL statements you have to specify the datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

All the parameters related with the datasource have to be introduced: route_type, authentication and  file_path_url. Authentication parameters username, password and encrypted_password may be necessary also.

In addition, Excel base views require the following parameter: type_of_file. And  also other optional parameters: worksheets, extract_data_from_all_sheets, extract_sheet_name_as_a_new_column, ignore_matching_errors, start_cell, end_cell, has_headers and stream_tuples.

Sample and results

SELECT result FROM GENERATE_VQL_FOR_EXCEL_FULL_DATA_SOURCE_BASE_VIEW() WHERE datasource_name = 'gen_test_excel38'

and route_type = 'HTTP'

and file_path_url = 'http://localhost:8082/test/booksales.xlsx'

and authentication = 'BASIC'

and username = 'tomcat'

and password = '0IgHioLFJfQ76X5sTbsHBinjsqQxUQaxuChJdmQpDo/hmBy8NggPyrpRroXkCliK21JyCjwylWyJ/8CuU3Z0fGZ0Aw5HGQiWgJoYx6p2oRaiHQhCas7qhjEyxCfZKwxn'

and encrypted_password = true

and folder = NULL

and database_name = 'sps'

and i18 = NULL

and base_view_name = 'test38'

and type_of_file='Excel 2007 or later (*.xlsx)'

and has_headers='true'

and stream_tuples='true'

Execute as a SELECT statement in the VQL Shell using a template


In order to obtain the VQL statements you have to specify the
datasource_name and the base_view_name. And also choose the database where the base view would be stored, and a folder if it is the case.

If you use another data source as a template in order to copy the route configuration you will need to introduce the name of the database (template_database) and the name of the template data source (template_datasource). This way parameters route_type, authentication,  username, password and encrypted_password will be imported from the template. But you have to introduce the file_path_url parameter, which is mandatory as the file path of the template data source will be overwritten.

Note that despite using the template, parameters corresponding with the base view (and not the data source) can be used: type_of_file(mandatory), worksheets, extract_data_from_all_sheets, extract_sheet_name_as_a_new_column, ignore_matching_errors, start_cell, end_cell, has_headers and stream_tuples.  

Sample and results

SELECT result FROM GENERATE_VQL_FOR_EXCEL_FULL_DATA_SOURCE_BASE_VIEW() WHERE

datasource_name = 'gen_test_template_excel38'

and file_path_url = 'http://localhost:8082/test/users.xlsx'

and template_datasource='gen_test_excel38'

and template_database='sps'

and folder = NULL

and database_name = 'sps'

and i18 = NULL

and base_view_name = 'exceltemplate38'

and type_of_file='Excel 2007 or later (*.xlsx)'

and has_headers='false'

and stream_tuples='false'