REFRESH_BASE_VIEW¶

Description

The stored procedure REFRESH_BASE_VIEW detects the differences between the schema of a base view and its underlying data source and applies them according to the input parameters.

For example, for a JDBC base view it detects the differences between the schema of the table in the database and the schema of the base view.

Syntax

REFRESH_BASE_VIEW (
      db_name : text
    , table_name : text
    , apply_pk_changes : boolean
    , apply_type_changes : boolean
    , apply_nullability_changes : boolean
    , apply_field_description_changes : boolean
    , apply_view_description_changes : boolean
    , apply_when_affected_elements : boolean
    , apply_when_invalid_views : boolean
    , sample_size : integer
    , read_from : timestamp
    , propagate_views : array
    , propagate_ws_ops : array
)

• db_name: name of the Denodo database to which table_name belongs. If null, it looks for table_name in the current database.

• table_name: name of the base view.

• apply_pk_changes: (true by default) apply changes on the primary key.

• apply_type_changes: (true by default) apply changes on the columns types.

• apply_nullability_changes: (true by default) apply changes on the nullability flag.

• apply_field_description_changes: (true by default) apply changes on the columns descriptions.

• apply_view_description_changes: (true by default) apply changes on the view description.

• apply_when_affected_elements: (false by default) persist changes even if there are affected elements (in valid state).

• apply_when_invalid_views: (false by default) persist changes even if it implies that some derived views will become invalid.

• sample_size: (optional, only applies to views created from Object Storage Data in Parquet, Delta and Iceberg format) establishes the maximum number of data files that will be analyzed to handle Schema Evolution.

• read_from: (optional, only applies to views created from Object Storage Data in Parquet, Delta and Iceberg format) allows to analyze the files from the desired timestamp (in UTC). Source files will be analyzed from the specified timestamp from older to newest until reaching the file limit set by the parameter sample_size. If no value is supplied to this parameter, source data files will be processed from newest to oldest until reaching the mentioned file limit.

• propagate_views: (optional) list of views to propagate whenever possible. The syntax is {ROW('<db_name>.<view_name>')}. As an example let us suppose that p_internet_inc_1 and p_internet_inc_2 are derived views from internet_inc in the database admin, the syntax would be {ROW('admin.p_internet_inc_1'), ROW('admin.p_internet_inc_2')}

• propagate_ws_ops: (optional) list of web service operations to propagate whenever possible. The syntax is {ROW('<db_name>.<web_service_name>/<operation_name>')}. As an example let us suppose that op_internet_inc is an operation of the web service ws_internet_inc in the database admin, the syntax would be {ROW('admin.ws_internet_inc/op_internet_inc')}

The output schema of this procedure has the following fields:

• detected_changes: list of detected changes. The procedure returns a row for each field of the view and also a row for each field that is present in the source, but not in the base view. If a field is an array or a register, it also returns one row for each one of its subfields. The output schema of this field is:

  • field: name of the field in the view. If the row represents a new field, it is the name of the field in the source.

  • new_value: represents the new value of the field modification. If the field has not changed or has been removed from the source, this value is empty.

  • old_value: represents the old value of the field modification. If the field has not changed or has been added from the source, this value is empty.

  • modification: the possible values of this field are:

    • “” (an empty string): the field has not changed.

    • “New field”: when the row represents a field that has been added from the source.

    • “Deleted field”: when the row represents a field that has been deleted from the source.

    • “Type has changed”: the type of the field has changed.

    • “Property has changed”: one “source type properties” of the field has changed.

    • “Description has changed”: the description of the field has changed.

    • “Compound structure has changed”: one of the subfields of this field has changed.

    • If the table description has changed, a new row will be displayed containing a field = Table description and comparing the wrapper and source description differences.

  • depth: for first-depth fields, the value is 1.

  • applied: whether the change was applied or not.

• affected_views: list of all views affected by the changes. The output schema of this field is:

  • database_name: name of the Denodo database to which view_name belongs.

  • view_name: name of the view affected by the changes.

• affected_ws_ops: list all ws_ops affected by the changes. The output schema of this field is:

  • database_name: name of the Denodo database to which web_service_name belongs.

  • web_service_name: name of the web service affected by the changes.

  • operation_name: name of the operation affected by the changes.

• erroneous_views: list of views that become invalid because of the refresh. The output schema of this field is:

  • database_name: name of the Denodo database to which view_name belongs.

  • view_name: name of the view that become invalid.

Remarks

• This procedure returns an error if table_name is not a base view.

• This procedure returns an error if db_name or table_name do not exist.

Privileges Required

The following users can execute this procedure:

• Administrators or administrators of this database.

• Users that have the Connect and Create privilege on this database, Execute privilege on the data source and Metadata and Write privilege on the base view.

Example with type change

Let us suppose that internet_inc is a JDBC base view of the database admin and that the current schema of its source table in the source database has changed since the base view was created:

• The type of the field ttime has changed from date to text.

Example of the query with the default parameters

SELECT detected_changes
FROM REFRESH_BASE_VIEW()
WHERE db_name = 'admin'
   AND table_name = 'internet_inc';

The result of invoking the previous query will be the following:

field	new_value	old_value	modification	depth	applied
iinc_id	long	long		1	false
summary	text	text		1	false
ttime	text	date	Type has changed	1	true
taxid	text	text		1	false
specific_field	text	text		1	false

The result is that the type of the field ttime on the view internet_inc was changed to text.

Example of the query with apply_type_changes disable

SELECT detected_changes
FROM REFRESH_BASE_VIEW()
WHERE db_name = 'admin'
   AND table_name = 'internet_inc'
   AND apply_type_changes = false;

The result of invoking the previous query will be the following:

field	new_value	old_value	modification	depth	applied
iinc_id	long	long		1	false
summary	text	text		1	false
ttime	text	date	Type has changed	1	false
taxid	text	text		1	false
specific_field	text	text		1	false

The result is that the view internet_inc was not modified.

Example with a derived view and new fields

Let us suppose that internet_inc is a JDBC base view of the database admin and that the current schema of its source table in the source database has changed since the base view was created:

• The table has a new field customer_id.

Also, let us suppose that p_internet_inc is a derived view from internet_inc in the database admin whose VQL is:

CREATE VIEW p_internet_inc AS SELECT iinc_id, summary FROM internet_inc;

Example of the query with the default parameters

SELECT detected_changes, affected_views
FROM REFRESH_BASE_VIEW()
WHERE db_name = 'admin'
   AND table_name = 'internet_inc';

The result of detected_changes of invoking the previous query will be the following:

field	new_value	old_value	modification	depth	applied
iinc_id	long	long		1	false
summary	text	text		1	false
ttime	date	date		1	false
taxid	text	text		1	false
specific_field	text	text		1	false
customer_id	long		New field	1	false

The result of affected_views of invoking the previous query will be the following:

affected_view_database	affected_view
admin	p_internet_inc

The result is that neither of the two views were modified.

Example of the query with apply_when_affected_elements enable

SELECT detected_changes, affected_views
FROM REFRESH_BASE_VIEW()
WHERE db_name = 'admin'
   AND table_name = 'internet_inc'
   AND apply_when_affected_elements = true;

The result of detected_changes of invoking the previous query will be the following:

field	new_value	old_value	modification	depth	applied
iinc_id	long	long		1	false
summary	text	text		1	false
ttime	date	date		1	false
taxid	text	text		1	false
specific_field	text	text		1	false
customer_id	long		New field	1	true

The result of affected_views of invoking the previous query will be the following:

affected_view_database	affected_view
admin	p_internet_inc

The result is that the new field customer_id was only added to the view internet_inc.

Example of the query with apply_when_affected_elements enable and the view in propagate_views

SELECT detected_changes, affected_views
FROM REFRESH_BASE_VIEW()
WHERE db_name = 'admin'
   AND table_name = 'internet_inc'
   AND apply_when_affected_elements = true
   AND propagate_views = {ROW('admin.p_internet_inc')};

The result of detected_changes of invoking the previous query will be the following:

field	new_value	old_value	modification	depth	applied
iinc_id	long	long		1	false
summary	text	text		1	false
ttime	date	date		1	false
taxid	text	text		1	false
specific_field	text	text		1	false
customer_id	long		New field	1	true

The result of affected_views of invoking the previous query will be the following:

affected_view_database	affected_view
admin	p_internet_inc

The result is that the new field customer_id was added to both views.

The new VQL of the view p_internet_inc is:

CREATE VIEW p_internet_inc AS SELECT iinc_id, summary, customer_id FROM internet_inc;

Example with a derived view and less fields

Let us suppose that internet_inc is a JDBC base view of the database admin and that the current schema of its source table in the source database has changed since the base view was created:

• The field specific_field was removed from the table.

Also, let us suppose that p_internet_inc is a derived view from internet_inc in the database admin whose VQL is:

CREATE VIEW p_internet_inc AS SELECT iinc_id, specific_field FROM internet_inc;

Example of the query with the default parameters

SELECT detected_changes, erroneous_views
FROM REFRESH_BASE_VIEW()
WHERE db_name = 'admin'
   AND table_name = 'internet_inc';

The result of detected_changes of invoking the previous query will be the following:

field	new_value	old_value	modification	depth	applied
iinc_id	long	long		1	false
summary	text	text		1	false
ttime	date	date		1	false
taxid	text	text		1	false
specific_field		text	Deleted field	1	false

The result of erroneous_views of invoking the previous query will be the following:

erroneous_view_database	erroneous_view
admin	p_internet_inc

The result is that neither of the two views were modified.

Example of the query with apply_when_invalid_views enable

SELECT detected_changes, erroneous_views
FROM REFRESH_BASE_VIEW()
WHERE db_name = 'admin'
   AND table_name = 'internet_inc'
   AND apply_when_invalid_views = true;

The result of detected_changes of invoking the previous query will be the following:

field	new_value	old_value	modification	depth	applied
iinc_id	long	long		1	false
summary	text	text		1	false
ttime	date	date		1	false
taxid	text	text		1	false
specific_field		text	Deleted field	1	true

The result of erroneous_views of invoking the previous query will be the following:

erroneous_view_database	erroneous_view
admin	p_internet_inc

The result is that the field specific_field in view internet_inc was removed and the view p_internet_inc was invalidated.