You can use an SQL command to route queries directly to specified engines, so that you can isolate jobs from engines being used for other jobs or direct types of jobs to specific engines.
To learn more about managing resources and workloads by routing queries to particular engines through rules, see Engine Routing in the Managing Workloads section of the documentation.
Routing Reflection-Refresh Jobs
Run this SQL command to route refresh jobs for all reflections on a dataset to a specific engine.Syntax
ALTER DATASET "<path>"."<name>" ROUTE REFLECTIONS TO <[DEFAULT ENGINE]|[ENGINE "[<engine_name> |<engine_uuid>]"]>
The context of the table or view. The context of a table is a location in a datasource or within a homespace in Dremio Cloud. The context of a view is a space within Dremio Cloud.
The name of the table or view.
The name of the engine to route jobs to.
The UUID of the engine to route jobs to.
Suppose that you had the following tables, views, and reflections, as shown in the first diagram:
- Table 1 has a reflection on it.
- Views 1 and 2 are derived from Table 1.
- View 1 has two reflections.
- Views 3 and 4 are derived from View 2.
- Views 3 and 4 have one reflection each.
You run these SQL commands to route the reflection-refresh jobs for Table 1, View 1, and View 3 to Engine 1. You let Dremio decide which engine to use for View 4:
ALTER DATASET "Table 1" ROUTE REFLECTIONS TO ENGINE "Engine 1" ALTER DATASET "View 1" ROUTE REFLECTIONS TO ENGINE "Engine 1" ALTER DATASET "View 3" ROUTE REFLECTIONS TO ENGINE "Engine 1"
View 1 and View 3 do not inherit the setting from Table 1. If you want to run refresh jobs for the reflections on those views in Engine 1, you must run the SQL command above on those views and specify Engine 1 before the refresh jobs are triggered.
According to the schedule defined on the Reflection Refresh page of the settings for Table 1, the data in Table 1 is refreshed:
After the refresh of the data in Table 1 is complete, Dremio runs the reflection-refresh jobs for the reflections. The jobs for the reflections for Table 1, View 1, and View 3 run in Engine 1. The job for the reflection on View 4 runs in the engine determined by the routing rules for reflections, which is likely a different engine than the engine for Table 1, View 1, and View 3’s reflection-refresh jobs: