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

note

Dremio does not refresh the data that external reflections are mapped to.

Reflection Refresh Types

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

  Use incremental refreshes only for reflections that are based on tables and views that are appended to. If records can be updated or deleted in a table or view, use full refreshes for the reflections that are based on that table or view.

  note

  Dremio always uses full refreshes, rather than incremental refreshes, in these two cases:

  • A reflection is created on a table that was promoted from a file, rather than from a folder, or is created on a view that is based on such a table.
  • A reflection is created from a view that uses nested group-bys, joins, unions, or window functions.

Automatic Reflection Optimization

Dremio automatically optimizes incremental reflection file sizes to improve reflection performance. These additional optimization calls will be routed to the engine configured to process Reflection jobs (query_type() = 'Reflections') in engine routing rules.

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 and hover over the table.
2. Click to the 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 and hover over the table.
2. Click to the 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 .
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 Times to Reattempt Reflection Refreshes

You can specify how many times a job should retry refreshing a reflection after an 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.

• After a failure to refresh a reflection that has never been refreshed before, further attempts are made every 30 minutes for a maximum of n retries, where n is the number that you specified.

• After a failure to refresh a reflection that has been refreshed before at least once, the retry behavior depends on the Reflection Refresh settings on the underlying table:

  • If Never refresh is selected, then the reflection is refreshed only when the Refresh Now button is clicked.

  • If Never refresh is not selected, then the reflection is set to be refreshed automatically at specified intervals. After the current interval elapses, a refresh is attempted again. If this second attempt fails, a third attempt is made at the end of the next interval. After every failure, another attempt is made, up to n retries, where n is the number that you specified.

Procedure

1. Open the Reflections page, hover over 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 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 ROUTE REFLECTIONS TO in ALTER TABLE in the SQL reference.