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>]"]>
Specify an engine to route reflections to either by the name or UUID of the engine. If not specified, the default engine will be used.
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:Route reflection-refresh jobs
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: