Managing Workloads

This topic covers how to manage resources and workloads by routing queries to particular engines through rules.

note

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 user

USER 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 group

is_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

Create rule based on type of job

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

Create a rule for the `COPY INTO` SQL command

query_label() IN ('COPY')

Create a rule for the `OPTIMIZE TABLE` SQL command

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 the COPY INTO SQL command
• OPTIMIZATION for jobs that run the OPTIMIZE 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 triggered

EXTRACT(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:

1. Click the Project Settings icon in the side navigation bar.
2. Select Engine Routing in the project settings sidebar to see the list of engine routing rules.

Adding a Rule

To add a rule:

1. On the Engine Routing page, click the Add Rule button at the top-right corner of the screen.

2. In the New Rule dialog, for Rule Name, enter a name.

3. For Conditions, enter the routing condition. See Rules for supported conditions.

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

5. Click Add.

Editing a Rule

To edit a rule:

1. On the Engine Routing page, hover over the rule and click the Edit Rule icon that appears next to the rule.

2. In the Edit Rule dialog, for Rule Name, enter a name.

3. For Conditions, enter the routing condition. See Rules for supported conditions.

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

5. Click Save.

Deleting a Rule

To delete a rule:

1. On the Engine Routing page, hover over the rule and click the Delete Rule icon that appears next to the rule.

   caution

   You must have at least one rule per project to route queries to a particular engine.

2. In the Delete Rule dialog, click Delete to confirm.