Skip to main content

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')

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.

OrderRule NameRuleEngine
1UI Previewsquery_type() = 'UI Preview'preview
2Reflectionsquery_type() = 'Reflections'default
3All Other QueriesAll other queriesdefault

Viewing All Rules

To view all rules:

  1. Click the Project Settings This is the icon that represents 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 This is the icon that represents the Edit Rule settings. 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 This is the icon that represents the Delete Rule settings. 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.