Materialized Views

Manual Refresh

A manual refresh of a materialized view can be performed on any materialized view, regardless of operating mode, to update the view's data. If a refresh is already in progress, the manual refresh request will wait until the currently executing refresh is complete before executing.

An example, in Python, of performing a manual refresh of a materialized view (customer_order_count):

1

h_db.alter_table('example.customer_order_count', 'refresh', '')

Manual Mode

In manual mode, the materialized view will only be made fully current by performing a manual refresh of the view. This will result in the immediate rebuilding of any of the view's member tables that require it.

On Change Mode

In on change mode, the materialized view will be kept current by detecting changes in any member tables or input tables and updating any member tables that require it. This behavior means that the returned data will always be current, with no overhead on query, but with potentially significant refresh processing for each change in the supporting data.

On Query Mode

In on query mode, the materialized view will be refreshed upon query. This behavior means that the returned data will always be current, with some overhead on query.

Periodic Mode

In periodic mode, the materialized view will be kept current by refreshing the data at a given interval. This behavior means that the returned data may not be current for a given query, but there will be no query overhead and no refresh waiting time for modification calls as with on change mode.

When the beginning of a period is reached, the time of the following period is calculated and a refresh process initiated. If the refresh process does not complete by that pre-calculated time, another refresh process will be initiated immediately after the first completes. When this next refresh begins, the time of the next period in the sequence that occurs at some time past the current time will be calculated and the cycle repeated. In this manner, only one catch-up refresh will be processed regardless of the number of periods skipped by a long-running refresh.

Examples

The following examples use the Python API to construct a materialized view.

To create a materialized view (recent_orders) on a single table (orders), and have recent_orders show records in orders that were ordered in the last 24 hours (as denoted by the table's o_orderdate column), refreshed every time the data in orders is updated:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

view_id = h_db.create_materialized_view(
    table_name = 'example.recent_orders',
    options = {'refresh_method': 'on_change'}
)['view_id']

retobj = h_db.filter(
    table_name = 'tpch.orders',
    view_name = 'example.recent_orders',
    expression = 'o_orderdate > NOW() - 1',
    options = {'view_id': view_id}
)

To create a materialized view (customer_order_count) across two tables: a base table (orders) and a parent table (customer), and refresh the order total per customer in customer_order_count every hour:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

view_id = h_db.create_materialized_view(
    table_name = 'example.customer_order_count',
    options = {'refresh_method': 'periodic', 'refresh_period': '3600'}
)['view_id']

h_db.create_join_table(
    join_table_name = 'example.customer_order',
    table_names = ['tpch.orders', 'tpch.customer'],
    column_names = ['*'],
    expressions = ['o_custkey = c_custkey'],
    options = {'view_id': view_id}
)

response = h_db.aggregate_group_by(
    table_name = 'example.customer_order',
    column_names = ['c_name', 'count(*) as num_orders'],
    options = {
        'result_table': 'example.customer_order_count',
        'view_id': view_id
    }
)