Refreshing Reflections

The data in a reflection can become stale and need to be refreshed. The refresh of a reflection causes two updates:

• The data stored in the Apache Iceberg table for the reflection is updated.
• The metadata that stores details about the reflection is updated.

Both of these updates are implied in the term “reflection refresh”.

Types of reflection refresh

There are two ways that the data for reflections can be refreshed:

• Full refresh

  Dremio drops the table in which the data for a reflection is stored, creates a new table, and loads that table. This type of refresh is necessary when a reflection sorts, partitions, or both on one or more fields. It is also possible to use this type when a reflection is not sorted or partitioned.

• Incremental refresh

  Dremio appends data to the existing data for a reflection. Incremental refreshes are faster than full refreshes for large reflections, and are appropriate for reflections that are not sorted, partitioned, or both.

  There are two ways in which Dremio can identify new records:

  • For directory datasets in file-based data sources like S3 and HDFS: Dremio can automatically identify new files in the directory that were added after the prior refresh.
  • For all other datasets (such as Apache Iceberg tables and datasets in relational or NoSQL databases): An administrator specifies a monotonically increasing field, such as an auto-incrementing key, that must be of type BigInt, Int, Timestamp, Date, Varchar, Float, Double, or Decimal. This allows Dremio to find and fetch the records that have been created since the last time the acceleration was incrementally refreshed.

Best practice: Time reflection refreshes to occur after metadata refreshes of tables

Time your refresh reflections to occur only after the metadata for their underlying tables is refreshed. Otherwise, reflection refreshes do not include data from any files that were added to a table since the last metadata refresh, if any files were added.

For example, suppose a data source that is promoted to a table consists of 10,000 files, and that the metadata refresh for the table is set to happen every three hours. Subsequently, reflections are created from views on that table, and the refresh of reflections on the table is set to occur every hour.

Now, one thousand files are added to the table. Before the next metadata refresh, the reflections are refreshed twice, yet the refreshes do not add data from those one thousand files. Only on the third refresh of the reflections does data from those files get added to the reflections.

Setting the Refresh Policy for Reflections

In the settings for a data source, you specify the schedule for refreshes of all reflections that are on the tables in that data source. The default schedule is Never refresh.

In the settings for a table, you specify the type of refresh to use for all reflections that are ultimately derived from the table, and you can specify a schedule for reflection refreshes that overrides the schedule specified in the settings for the table’s data source. The default refresh type is Full refresh, and the default schedule is the schedule set at the source of the table.

Procedures

To set the refresh schedule on a data source:

1. Right-click the data lake or external source.
2. Select Edit Details.
3. In the sidebar of the Edit Source window, select Reflection Refresh.
4. When you are done making your selections, click Save. Your changes go into effect immediately.

To set the refresh type and schedule on a table:

1. Locate the table.
2. Click the gear icon to its right.
3. In the sidebar of the Dataset Settings window, click Reflection Refresh.
4. When you are done making your selections, click Save. Your changes go into effect immediately.

Manually Triggering a Refresh

You can click a button to start the refresh of all of the reflections that are defined on a table or on views derived from that table.

To trigger a refresh:

1. Locate the table.
2. Click the gear icon to its right.
3. In the sidebar of the Dataset Settings window, click Reflection Refresh.
4. Click Refresh Now. The message “All dependent reflections will be refreshed.” appears at the top of the screen.
5. Click Save.

Viewing the Refresh History for Reflections

You can find out whether a refresh job for a reflection has run, and how many times refresh jobs for a reflection have been run.

Procedure

1. Go to the space that lists the table or view from which the reflection was created.
2. Hover over the row for the table or view.
3. In the Actions field, click the gear.
4. In the sidebar of the Dataset Settings window, select Reflections.
5. Click History in the heading for the reflection.

Result

The Jobs page is opened with the ID of the reflection in the search box and only jobs related to that ID listed.

When a reflection is created or refreshed, Dremio runs two jobs by default:

• The first writes the query results as a materialization to the distributed acceleration storage by running a REFRESH REFLECTION statement.
• The second runs a LOAD MATERIALIZATION METADATA statement to create metadata that the query optimizer can use to find out the definition and structure of the reflection.

Setting the Maximum Number of Attempts for Failures to Refresh Reflections

You can specify how many times a job should retry refreshing a reflection after the first attempt fails. Doing so can help keep reflection-refresh jobs moving at an acceptable rate through an engine queue, so that the data in the corresponding reflections does not become too stale.

Procedure

1. Open the Reflections page, hover over the gear in the sidebar, select Project Settings, and then select Reflections in the sidebar of the page that is opened.
2. On the Reflections page, click the gear in the top-right corner and select Acceleration Settings.
3. In the Maximum attempts for reflection job failures field, specify the number of retries to allow.
4. Click Save. The change goes into effect immediately.

Routing Refresh Jobs to Particular Engines

You can use an SQL command to route jobs for refreshing reflections directly to specified engines. See Engine Routing in the SQL reference.