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

Labels enable rules that route queries running named commands to specific engines. Dremio supports the following query labels:

Query Label	Description
COPY	Assigned to all queries running a COPY INTO SQL command
CTAS	Assigned to all queries running a CREATE TABLE AS SQL command
DML	Assigned to all queries running an INSERT, UPDATE, DELETE, MERGE, or TRUNCATE SQL command
OPTIMIZATION	Assigned to all queries running an OPTIMIZE SQL command

Here are two example routing rules:

Create a routing rule for queries running a COPY INTO command

query_label() IN ('COPY')

Create a routing rule for queries running the DML commands INSERT, UPDATE, DELETE, MERGE, or TRUNCATE

query_label() IN ('DML')

Tag

Create a rule that routes jobs based on a routing tag.

Create rule that routes jobs based on routing tag

tag() = 'ProductionDashboardQueue'

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, query cost, tag, and the time of day that it was triggered.

Create rule based on user, group, job type, query cost, tag, 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.

Connection Tagging and Direct Routing Configuration

Routing tags are configured by setting the ROUTING_TAG = <Tag Name> parameter for a given session to the desired tag name.

JDBC Session Configuration

To configure JDBC sessions add the ROUTING_TAG parameter to the JDBC connection URL. For example: jdbc:dremio:direct=localhost;ROUTING_TAG='TagA'.

ODBC Session Configuration

Configure ODBC sessions as follows:

Windows Sessions

Add the ROUTING_TAG parameter to the AdvancedProperties parameter in the ODBC DSN field.

Mac OS Sessions

1. Add the ROUTING_TAG parameter to the AdvancedProperties parameter in the system odbc.ini file located at /Library/ODBC/odbc.ini. After adding the parameter, an example Advanced Properties configuration might be: AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;NumberOfPrefetchBuffers=5;ROUTING_TAG='TagA';

2. Add the ROUTING_TAG parameter to the AdvancedProperties parameter in the user's DSN located at ~/Library/ODBC/odbc.ini

Best Practices for Workload Management

Because every query workload is different, engine sizing often depends on several factors, such as the complexity of queries, number of concurrent users, data sources, dataset size, file and table formats, and specific business requirements for latency and cost. Workload management (WLM) ensures reliable query performance by choosing adequately sized engines for each workload type, configuring engines, and implementing query routing rules to segregate and route query workload types to appropriate engines.

This section describes best practices for adding and using Dremio engines, as well as configuring WLM to achieve reliable query performance in Dremio Cloud. This section also includes tips for migrating from self-managed Dremio Software to fully managed Dremio Cloud and information about using the system table sys.project.history.jobs, which stores metadata for historical jobs executed in a project, to assess the efficacy of WLM settings and make adjustments.

Setting Up Engines

As a fully managed offering, Dremio Cloud is the best deployment model for Dremio in production because it allows you to achieve high levels of reliability and durability for your queries and maximize resource efficiency with engine autoscaling and does not require you to manually create and manage engines.

Segregating workload types into separate engines is vital for mitigating noisy neighbor issues, which can jeopardize performance reliability. You can segregate workloads by type, such as ad hoc, dashboard, and lakehouse (COPY INTO, DML, and optimization), as well as by business unit to facilitate cost distribution.

Metadata and reflection refresh workloads should have their own engines for executing metadata and reflection refresh queries. These internal queries can use a substantial amount of engine bandwidth, so assigning separate engines ensures that they do not interfere with user-initiated queries. Initial engine sizes should be XSmall and Small, but these sizes may change depending on the number and complexity of reflection refresh and metadata jobs.

Dremio recommends the following engine setup configurations:

• Dremio Cloud offers a range of instance types. Experiment with typical queries, concurrency, and engine sizes to establish the best engine size for each workload type based on your organization's budget constraints and latency requirements.

• Maximum concurrency is the maximum number of jobs that Dremio can execute concurrently on an engine replica. Dremio provides an out-of-the-box value for maximum concurrency based on engine size, but we recommend testing with typical queries directed to specific engines to determine the best maximum concurrency values for your query workloads.

• Dremio Cloud offers autoscaling to meet the demands of dynamic workloads with engine replicas. It is vital to assess and configure each engine's autoscaling parameters based on your organization's budget constraints and latency requirements for each workload type. You can choose the minimum and maximum number of replicas for each engine and specify any advanced configuration as needed. For example, dashboard workloads must meet stringent low latency requirements and are prioritized for performance rather than cost. Engines added and assigned to execute the dashboard workloads may therefore be configured to autoscale using replicas. On the other hand, an engine for ad hoc workloads may have budget constraints and therefore be configured to autoscale with a maximum of one replica.

Routing Workloads

Queries are routed to engines according to routing rules. You may use Dremio's out-of-the-box routing rules that route queries to the preview engines that are established by default, but Dremio recommends creating custom routing rules based on your workloads and business requirements. Custom routing rules can combine factors including User, Group Membership, Job Type, Date and Time, Query Label (for lakehouse queries), and ROUTING_TAG (tags; for JDBC and ODBC connections). Read Rules for examples.

The following table lists example routing rules based on query_type, query_label, and tags:

Order	Rule Name	Rule	Engine
1	Reflections	query_type() = 'Reflections'	Reflection
2	Metadata	query_type() = 'Metadata Refresh'	Metadata
3	Dashboards	tag() = 'dashboard'	Dashboard
4	Ad hoc Queries	query_type() IN ( 'UI Run' , 'REST') OR tag() = 'ad hoc'	Ad hoc
5	Lakehouse Queries	query_label() IN ('COPY','DML','CTAS', 'OPTIMIZATION')	Lakehouse
6	All Other Queries	All other queries	Preview

Migrating from Dremio Software to Cloud

Dremio Cloud is a fully managed service that differs architecturally from Dremio Software. When you migrate to Dremio Cloud, Dremio Software deployments that use queues to route queries to split engine capacity or query costs require WLM changes because Dremio Cloud does not support queues or cost-based query routing. Queues are not necessary because Dremio Cloud provides multiple engines that can split and execute workloads. Query cost, which is calculated by the query planner using estimations to compare two query plans, does not reliably indicate query complexity and is not architecturally viable in Dremio Cloud because queries are routed to engines before the planning process.

To take advantage of Dremio Cloud's multi-engine and autoscaling features, we recommend reviewing different workloads, segregating identical workloads based on business unit, service level agreement (SLA) requirements, and budget constraints, and routing workloads to properly sized engines using routing rules. This allows you to achieve reliability in query performance and reduce total cost of ownership while responding to dynamic workload needs by correctly sizing an engine for a workload and using autoscaling functionality.

Using the sys.project.history.jobs System Table

The sys.project.history.jobs system table contains metadata for recent jobs executed in a project, including time statistics, cost, and other relevant information. You can use the data in the sys.project.history.jobs system table to evaluate the effectiveness of WLM settings and make adjustments based on job metadata.

Using Job Analyzer

Job Analyzer is a package of useful query and view definitions that you may create over the sys.projects.history.jobs system table and use to analyze job metadata. Job Analyzer is available in a public GitHub repository.