Managing Workloads
This topic covers how to manage resources and workloads by routing queries to particular engines through rules.
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.
Overview
You can manage Dremio Cloud workloads via routing rules which are evaluated at runtime (before query planning) to decide which engine to use for a given query. In projects with only one engine, all queries share the same execution resources and route to the same single engine. However, when multiple engines are provisioned, rules determine the engine to be used.
You must arrange the rules in the order that you want them to be evaluated. In the case that multiple rules evaluate to true for a given query, the first rule that returns true will be used to select the engine. The following diagram shows a series of rules that are evaluated when a job gets submitted.
- Rule1 routes jobs to Engine1
- Rule2 routes jobs to Engine2
- Rule3 routes jobs to the default engine that was created on project start up
- Rule4 rejects the jobs that evaluate to true
Rules
You can use Dremio SQL syntax to specify rules to target particular jobs.
The following are the types of rules that can be created along with examples.
User
Create a rule that identifies the user that triggers the job.
Create rule that identifies userUSER in ('JRyan','PDirk','CPhillips')
Group Membership
Create a rule that identifies if the user that triggers the job is part of a particular group.
Create rule that identifies whether user belongs to a specified groupis_member('MarketingOps') OR
is_member('Engineering')
Job Type
Create a rule depending on the type of job. The types of jobs can be identified by the following categories:
- Flight
- JDBC
- Internal Preview
- Internal Run
- Metadata Refresh
- ODBC
- Reflections
- REST
- UI Download
- UI Preview
- UI Run
query_type() IN ('JDBC', 'ODBC', 'UI Run', 'Flight')
Query Label
Create a rule that routes queries to specific engines if they are running either of these SQL commands:
COPY INTO
OPTIMIZE TABLE
query_label() IN ('COPY')
query_label() IN ('OPTIMIZATION')
Here is an example of a rule in the New Rule dialog:

Either of these values is allowed:
COPY
for jobs that run theCOPY INTO
SQL commandOPTIMIZATION
for jobs that run theOPTIMIZE TABLE
SQL command
Date and Time
Create a rule that routes a job based on the time it was triggered. Use Dremio SQL Functions.
Create rule that routes jobs based on time triggeredEXTRACT(HOUR FROM CURRENT_TIME)
BETWEEN 9 AND 18
Combined Conditions
Create rules based on multiple conditions.
The following example routes a job depending on user, group membership, query type, and the time of day that it was triggered.
Create rule based on user, group, job type, and time triggered(
USER IN ('JRyan', 'PDirk', 'CPhillips')
OR is_member('superadmins')
)
AND query_type IN ('ODBC')
AND EXTRACT(HOUR FROM CURRENT_TIME)
BETWEEN 9 AND 18
Default Rules
Each Dremio Cloud project has its own set of rules. When a project is created, there are rules that get created for the engines (default and preview) that get created by default. These rules are editable.
Order | Rule Name | Rule | Engine |
---|---|---|---|
1 | UI Previews | query_type() = 'UI Preview' | preview |
2 | Reflections | query_type() = 'Reflections' | default |
3 | All Other Queries | All other queries | default |
Viewing All Rules
To view all rules:
- Click the Project Settings
icon in the side navigation bar.
- Select Engine Routing in the project settings sidebar to see the list of engine routing rules.
Adding a Rule
To add a rule:
On the Engine Routing page, click the Add Rule button at the top-right corner of the screen.
In the New Rule dialog, for Rule Name, enter a name.
For Conditions, enter the routing condition. See Rules for supported conditions.
For Action, complete one of the following options:
a. If you want to route the jobs that meet the conditions to a particular engine, select the Route to engine option. Then use the engine selector to choose the engine.
b. If you want to reject the jobs that meet the conditions, select the Reject option.
Click Add.
Editing a Rule
To edit a rule:
On the Engine Routing page, hover over the rule and click the Edit Rule
icon that appears next to the rule.
In the Edit Rule dialog, for Rule Name, enter a name.
For Conditions, enter the routing condition. See Rules for supported conditions.
For Action, complete one of the following options:
a. If you want to route the jobs that meet the conditions to a particular engine, select the Route to engine option. Then use the engine selector to choose the engine.
b. If you want to reject the jobs that meet the conditions, select the Reject option.
Click Save.
Deleting a Rule
To delete a rule:
On the Engine Routing page, hover over the rule and click the Delete Rule
icon that appears next to the rule.
cautionYou must have at least one rule per project to route queries to a particular engine.
In the Delete Rule dialog, click Delete to confirm.