With Virtual DataPort, you can refresh the data of a base view. The
REFRESH command refreshes the data stored in the underlying data source. First, it deletes the current data stored in the table, and then it inserts new data. The data is obtained executing the data load query that was used to create the remote table. The command is supported only by the base views created using the CREATE_REMOTE_TABLE stored procedure.
The REFRESH statement allows refreshing the data of a view. This command updates the data of the underlying data source. It does the following steps:
Truncate the remote table in the data source. If the remote table does not exist, then VDP will create it.
Executes in the VDP Server the data load query that was used to create the remote table.
Inserts into the table the rows obtained in the execution of the query of the previous step.
If the remote table did not exist, then VDP will recreate the indexes on the remote table.
REFRESH <crt_base_view:identifier> [ OPTIONS ( <option information> [ , <option information> ]* ) ] [ CONTEXT ( <context information> [, <context information> ]* ) ] [ TRACE ]
<crt_base_view> ::= Base view created with the CREATE_REMOTE_TABLE procedure or with the Create Remote Table form (see Basic elements of VQL statements).
<option information> := Optional parameters to configure the refresh. See CREATE REMOTE TABLE Command to get information about the options available.
<context information> ::= Clause used to modify certain configuration preferences to execute a query without overriding the values configured by default (see Syntax of the CONTEXT clause).
The user needs these privileges:
Insertover the view, or
Writeover the database where is the view.
Executeover the views referenced in the data load query.
The following example refreshes a base view that was created using the CREATE_REMOTE_TABLE stored procedure.
Command to create the remote table
sales_current_year and the base view
CALL CREATE_REMOTE_TABLE('sales_current_year', false, 'SELECT * FROM sales WHERE year = getyear(CURRENT_DATE())', 'data_source_database_name', 'data_source_name', null, 'schema_name', 'denodo_database_name', 'sales_year', '/', false)
Command to refresh the data of the base view
The REFRESH command does the following steps:
INSERT INTO sales_year SELECT * FROM sales WHERE year = getyear(CURRENT_DATE())