Denodo VQL Generation Stored Procedures - User Manual

Last modified on: 10 Sep 2024

You can translate the 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.

The only data types supported are LOCAL, HTTP Client and S3. This also applies if you use templates for the data routes.

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

JARS 'denodo-vqlgeneration';

[ FOLDER = <literal> ]

[ DESCRIPTION = <literal> ]

Virtual DataPort Administration Tool menu

You can also add the stored procedure by clicking on Stored procedure > Java 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_FULL_BASE_VIEW (

data_source_name : text,

route_type: text,

file_path_url: text,

authentication: text,

username: text,

password: text,

encrypted_password: boolean,

folder : text,

database_name : text,

i18n : text,

base_view_name : text,

base_view_folder: text,

aws_access_key : text,

aws_secret_key : text,

aws_encrypted_secret_key : boolean,

aws_iam_role_arn : text,

use_aws_instance_credentials : boolean,

aws_route_pattern : text,

aws_custom_properties : text,

template_datasource: text,

template_database: text,

column_delimiter:text,

eol_delimiter:text,

has_headers:text,

ignore_matching_errors:text

)

data_source_name: name of the data source. Mandatory.
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.
S3: Use this type ofroute to obtain the data from a file or a set of files located in a S3 bucket.

file_path_url: route to the delimited file or files. It can be a file path or a url or S3 path. 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 data source, if the base view folder is not specified, the base view will be stored in the same folder. The result will include the VQL statements to create this folder(s). If null, the VQL will not specify a folder.
database_name: name of the database in which artifacts will be created. If null, the procedure uses the current database.
i18n: i18n of the base view to be created. If null, the i18n of the current database will be assigned.
base_view_name: name of the base view to be created. If null, the name will be auto-generated.
base_view_folder: folder in which to place the base view. The result will include the VQL statements to create this folder(s). If null, the base view will be stored in the same folder as the datasource.
aws_access_key: the AWS access key ID.
aws_secret_key: the AWS secret key.
aws_encrypted_secret_key: whether the AWS secret key has been specified encrypted (as per Denodo VQL).
aws_iam_role_arn: Optionally, you can specify an AWS IAM role ARN to get the necessary privileges to connect to the database.
use_aws_instance_credentials: automatically obtain the credentials of the database from the AWS instance where this Virtual DataPort server is running.
aws_route_pattern: Only for delimited-file data sources: if the path points to a directory and you enter a value in File name pattern, the data source will only process the files whose name matches the regular expression entered in this box. For example, if you only want to process the files with the extension log, enter (.*)\.log.
aws_custom_properties: set the same properties that you would put in the Hadoop configuration files like core-site.xml to configure the S3A Hadoop connector.
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.