Denodo VQL Generation Stored Procedures - User Manual

Last modified on: 18 Mar 2022

Overview

This component contains three Stored Procedure that return the VQL statements required to create a Delimited File, XML 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 is a stored procedure for each type of supported data source:

GenerateVQLToCreateDFBaseView
GenerateVQLToCreateXMLBaseView
GenerateVQLToCreateJSONBaseView

You can create a base view from scratch introducing all the parameters, or you can 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.

GenerateVQLToCreateDFBaseView

Installation

Importing the Stored Procedure

In order to run the GenerateVQLToCreateDFBaseView stored procedure you have to load the denodo-storedprocedures-generate-vql-to-create-base-view-{vdpversion}-{version}-jar-with-dependencies.jar file, using the File > Jar Management option of the VDP Administration Tool.

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.GenerateVQLToCreateDFBaseView'

JARS 'denodo-storedprocedures-generate-vql-to-create-base-view-<vdpversion>-jar-with-dependencies';

[ 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-storedprocedures-generate-vql-to-create-base-view-<vdpversion>-<version>, previously added to the Virtual DataPort Server.

Description

The GENERATE_VQL_TO_CREATE_DF_BASE_VIEW 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 spfromdf()

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 spfromdf()

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'

GenerateVQLToCreateXMLBaseView

Installation

Importing the Stored Procedure

In order to run the GenerateVQLToCreateXMLBaseView stored procedure you have to load the denodo-storedprocedures-generate-vql-to-create-base-view-{vdpversion}-{version}-jar-with-dependencies.jar file, using the File > Jar Management option of the VDP Administration Tool

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.xml.GenerateVQLToCreateXMLBaseView'

JARS 'denodo-storedprocedures-generate-vql-to-create-base-view-<vdpversion>-jar-with-dependencies';

[ 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:

Description

The GENERATE_VQL_TO_CREATE_XML_BASE_VIEW stored procedure returns the VQL statements required to create 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_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. 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.

Sample and results

SELECT result FROM spfromxml()

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.

The i18 parameter is also mandatory.

Sample and results

SELECT result FROM spfromxml()

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'

GenerateVQLToCreateJSONBaseView

Installation

Importing the Stored Procedure

In order to run the GenerateVQLToCreateJSONBaseView stored procedure you have to load the denodo-storedprocedures-generate-vql-to-create-base-view-{vdpversion}-{version}-jar-with-dependencies.jar file, using the File > Jar Management option of the VDP Administration Tool

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.json.GenerateVQLToCreateJSONBaseView'

JARS 'denodo-storedprocedures-generate-vql-to-create-base-view-<vdpversion>-jar-with-dependencies';

[ 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:

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. 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_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. 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.

Sample and results

SELECT result FROM spfromjson()

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.

The i18 parameter is also mandatory.

Sample and results

SELECT result FROM spfromjson()

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'