On this page

    Engine Routing

    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 Engine Routing 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.

    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.

    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
    query_type() IN ('JDBC', 'ODBC', 'UI Run', 'Flight')
    

    Date and Time

    Create a rule that routes a job based on the time it was triggered. Use Dremio SQL Functions.

    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.

    (
    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. In the Dremio Cloud UI, click the Settings (gear) icon that is towards the bottom of the left sidebar. Click Project Settings from the menu.
    2. Click Engine Routing from the sidebar menu to see the list of engine routing rules.

    Adding a Rule

    To add a rule:

    1. In the Engines Routing UI, click the Add Rule button.
    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, select Assign an engine if you want to route the jobs that meet the condition to a particular engine. If you want to reject the jobs that meet the condition, select the Reject option.
    5. If you selected Assign an engine in the previous step, for Engine select the engine that jobs meeting the condition should be routed to.
    6. Click Save.

    Editing a Rule

    To edit a rule:

    1. In the Engines Routing UI, in the Actions column, click the Edit button for the rule that you want to edit.
    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, select Assign an engine if you want to route the jobs that meet the condition to a particular engine. If you want to reject the jobs that meet the condition, select the Reject option.
    5. If you selected Assign an engine in the previous step, for Engine select the engine that jobs meeting the condition should be routed to.
    6. Click Save.

    Deleting a Rule

    To delete a rule:

    1. In the Engine Routing UI, in the Actions column, click the Delete button for the rule that you want to delete.

    warning:

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

    1. Confirm that you want to delete the rule.