USER MANUALS

Refreshing Views

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.

REFRESH Statement

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:

  1. Truncate the remote table in the data source. If the remote table does not exist, then VDP will create it.

  2. Executes in the VDP Server the data load query that was used to create the remote table.

  3. Inserts into the table the rows obtained in the execution of the query of the previous step.

  4. If the remote table did not exist, then VDP will recreate the indexes on the remote table.

Syntax of the REFRESH statement
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).

Privileges Required

The user needs these privileges:

  • Insert over the view, or Connect and Write over the database where is the view.

  • Execute over the views referenced in the data load query.

Example:

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

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

REFRESH "sales_year";

The REFRESH command does the following steps:

  1. TRUNCATE sales_year

  2. INSERT INTO sales_year SELECT * FROM sales WHERE year = getyear(CURRENT_DATE())

Add feedback