On this page

    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.

    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.

    warning:

    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.

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

    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.