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:
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.
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.
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> ] |
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.
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.
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 ) |
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.
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; |
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.
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' |
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
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> ] |
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.
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.
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 ) |
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.
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' |
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.
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' |
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
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> ] |
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.
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.
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 ) |
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.
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' |
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.
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' |