CREATE_REMOTE_TABLE¶

Description

The stored procedure CREATE_REMOTE_TABLE is one of the components of the feature remote tables. The section Remote Tables of the Administration Guide explains in detail what remote tables are.

This procedure does the following:

Creates a table in the underlying database of a JDBC data source.
Executes a query in the Virtual DataPort server.
Inserts the result of this query into the table of the database created in step #1.
It creates a base view over the table created in step #1.

To perform steps 1 to 3, this procedure invokes the command CREATE REMOTE TABLE and then, it creates the base view (step #4).

The procedure DROP_REMOTE_TABLE drops the base views created by this procedure, and the table created by it in the underlying databases.

Syntax

CREATE_REMOTE_TABLE(
      remote_table_name : text
    , replace_remote_table_if_exist : boolean
    , query : text
    , datasource_database_name : text
    , datasource_name : text
    , datasource_catalog : text
    , datasource_schema : text
    , base_view_database_name : text
    , base_view_name : text
    , base_view_folder : text
    , replace_base_view_if_exist: boolean
    , options : text
    , create_table_template : text
    , create_table_template_parameters : text
)

remote_table_name: name of the new table in the underlying database of the JDBC data source. It has to be a valid identifier in the target database and it cannot be a reserved word. The first character must be one of A to Z or a to z. The next characters have to be one of a to z, A to Z, numbers or underscores.
replace_remote_table_if_exist (optional): if true and a table with the same name already exists in the database (in the same schema/catalog), the procedure will drop the table and create it again. The default value is false so if the table already exists, the procedure will fail.
query: query executed in the Virtual DataPort server to obtain the data that will be inserted in the new table of the database.
datasource_database_name (optional): database of the JDBC data source in which the new table will be created. If null, it looks for datasource_name on the current database.
datasource_name: JDBC data source that points to the database in which the table will be created.
datasource_catalog: catalog of the database where the table will be created. It is mandatory but if the database does not support catalogs, set this to null.
datasource_schema: schema of the database where the table will be created. It is mandatory but if the database does not support schemas, set this to null.
base_view_database_name (optional): Virtual DataPort database where the base view will be created. If null, the base view is created in the current database.
base_view_name (optional): name of the new base view. If null, the name will be the value of remote_table_name.
base_view_folder (optional): folder in which the base view will be created. If the folder does not exist, the procedure will create it. If null, the base view will be created in the root folder.
replace_base_view_if_exist (optional): if true and a view with the same name already exists in the Virtual DataPort database, the procedure will drop the view and create it again. The default value is false so if the view already exists, the procedure will fail.
options (optional): options to modify the default value of some properties of the CREATE REMOTE TABLE command. Format: 'option1=value1, option2=value2, ...'. See CREATE REMOTE TABLE Command to get information about the options available.
create_table_template (optional): CREATE TABLE command template used by Denodo to create the remote table on the data source. It contains parameters using the syntax @{parameter_name}. See section Table Creation Templates for more information.
create_table_template_parameters (optional): properties used to set the value of the template user-defined parameters. Format: 'parameter_name_1=value_1, parameter_name_2=value_2, ...'. If you export the views created by this procedure using the option export with properties, then Denodo will export the value of these properties in the environment properties file. More info Export to a File with Properties.

Stored Procedure Result

The procedure returns three rows with the status of each phase. The stored procedure result contains the following columns:

Stored Procedure Result (text): summary of the task done in this phase.
phase (int): phase number. There are 3 phases.
remote_table (text): name of the remote table created in the underlying database.
base_view (text): name of the base view created by the procedure.
base_view_database (text): database where the procedure created the base view.
inserted_rows (int): number of rows inserted into the remote table.
error (boolean): true, if there was an error executing the phase, false otherwise.
status (text): state that describe the result of the phase. List of status:

OK, UNKNOWN, INVALID_BASE_VIEW_NAME, VIEW_ALREADY_EXISTS,
CATALOG_SCHEMA_NOT_SPECIFIED, NO_CREATE_FOLDER_PRIVILEGE, INVALID_FOLDER_NAME,
EMPTY_QUERY, MORE_THAN_ONE_QUERY, INVALID_QUERY, DATASOURCE_DOES_NOT_EXIST,
INVALID_CATALOG, INVALID_SCHEMA, REMOTE_TABLE_ALREADY_EXISTS

Example of the CREATE_REMOTE_TABLE stored procedure result:

stored procedure result	phase	remote_table	base_view	database	inserted_rows	error	status
Step 1 of 3: Created remote table ‘<table_name>’ successfully.	1	table_name	NULL	NULL	NULL	false	OK
Step 2 of 3: Inserted 2 rows into remote table ‘<table_name>’.	2	table_name	NULL	NULL	2	false	OK
Step 3 of 3: Created base view ‘<view_name>’ successfully in the ‘<vdp_database>’ database.	3	NULL	view_name	vdp_database	NULL	false	OK

Remarks

If the target database is based on HDFS (Hadoop, Impala, Hive), you have to enable bulk data load on the data source before executing this procedure.

This procedure only works for JDBC data sources whose adapter is supported by the Cache Engine (list of databases supported by the Cache Engine).

When inserting data into Teradata, the execution engine prioritizes the Teradata FastLoad mechanism over the incremental load in Teradata (INSERT INTO <view> ON DUPLICATE KEY UPDATE <select query>). Teradata FastLoad does not allow inserting duplicate rows on tables that have a Primary Index. The execution engin checks if the SELECT query used to create the remote table in Teradata can contain duplicate rows. A query cannot return duplicate rows if it includes de primary key of the view or contains a GROUP BY:

If it cannot return duplicate rows: Creates the remote tables with a Primary Index.
If it can return duplicate rows: Creates the remote tables without a Primary Index.

VDP uses FastLoad in both cases. On the other hand, the incremental load feature only works in the first case (when the table was created using a Primary Index).

Privileges Required

The user needs these privileges:

Connect over the database of the JDBC data source.
Execute over the JDBC data source.
Connect and Create view over the Virtual DataPort database in which the base view will be created.
Create folder over the database where the base view will be created, if the procedure has to create the base view on a folder and the folder does not exist.
Execute over the views referenced in the query.

The user also needs Write privilege over the JDBC data source in case he wants to create the remote table using a custom table creation template.

The user account of the JDBC data source has to be able to create tables in the underlying database.

Example

SELECT *
FROM CREATE_REMOTE_TABLE()
WHERE datasource_database_name = 'common_sources'
  AND datasource_name = 'ds_jdbc_oracle'
  AND datasource_catalog = ''
  AND datasource_schema = 'REPORTING'
  AND remote_table_name = 'reporting_customer360_state_ca'
  AND replace_remote_table_if_exist = true
  AND query = 'SELECT * FROM customer WHERE state = ''CA'''
  AND base_view_database_name = 'customer360'
  AND base_view_name = 'customer360_state_ca'
  AND base_view_folder = '/reporting_customer'
  AND replace_base_view_if_exist = false;

Note that because query is a literal, the single quotes have to be escaped.

This example does the following:

It checks if in the schema “REPORTING”, the database of the data source “ds_jdbc_oracle” has the table “reporting_customer360_state_ca”. If it does, it deletes this table.
In the same schema, it creates the table “reporting_customer360_state_ca” to store the results of “query”.
Executes the query and inserts the result on this table.
Creates the folder “/reporting_customer”.
It creates the JDBC base view “customer360_state_ca” in this folder that queries the table “reporting_customer360_state_ca” of the database of the data source “ds_jdbc_oracle”.

Virtual DataPort Administration Tool

When you open a base view that was created with this procedure, you will see the source query in the box Data load query, at the bottom of the dialog.

Base view created with *CREATE_REMOTE_TABLE* procedure

Also, in the tab Metadata of the Edit dialog of a view.

USER MANUALS

CREATE_REMOTE_TABLE¶