Manage Engines & Workloads

Engines and routing rules can be used to dedicate resources based on the role assigned to a user, the interface from which the query is triggered, system jobs that run to refresh data and reflections, and more.

In this module, you will create a new engine and use a routing rule to route JDBC queries to the new engine. This will ensure that the JDBC queries have dedicated compute resources and your dashboard loads quickly and predictably each time.

Step 1: Create an Engine

Engines are the compute resources that run your queries. Dremio Cloud automatically scales the engine up and down based on the workload that is running on the engine.

To create an engine:

1. Go to Project Settings > Engines, then click Add Engine.

2. In the Add Engine dialog, for Engine Name, enter Tutorial Engine.

3. (Optional) For Description, enter This is the engine for Dremio tutorials.

4. For Size, the default is 2XSmall(1 m5d.4xlarge node) on AWS and 2XSmall(1 STANDARD_D16D_V5) on Azure, which means that for every replica of this engine that is running, there is one virtual machine of the specified type running in your cloud account.

   You can select a different engine size if you’d like, though note that larger engines will provide higher performance but also incur a higher cost. Sonar performance scales linearly, so you can choose where you want to be on the performance-cost spectrum.

5. For Min Replicas, enter 1, which means that one replica of the engine is always running so queries that are triggered run immediately.

If this is set to 0 then when queries are done running, all engines (virtual machine instances) will be stopped and when the next query is submitted, the engine will take about 2 minutes to start up again.

6. For Max Replicas, leave the default of 1.

This means that this engine will not scale past 1; setting max replicas to a number greater than 1 will allow the engine to scale automatically when the first replica is fully consumed.

7. Click Save & Launch.

You’ll see the Tutorial Engine appears in the Engines list. The animation on the left indicates that the engine is starting up.

indicates that the engine is ready to run queries. If a query is submitted to the engine before it’s ready, the job will be queued until the engine is up.

The engine will take about 2 minutes to start up. You can move to the next step while that's in progress.

Step 2. Create an Engine Routing Rule

Routing rules can be used to distribute jobs across multiple engines to isolate jobs or a certain type or from a certain group of users. For example, if you have a critical dashboard that is frequently used, you would want sub-second load time regardless of what other workloads are running in Dremio.

Let’s assume that the queries from the dashboard you worked with earlier should always run in sub-seconds regardless of other running queries. Create a routing rule to route all JDBC queries to the Tutorial Engine you created.

To create a routing rule:

1. Navigate to Project Settings > Engine Routing, then click Add Rule.

2. In the New Rule dialog, for Rule Name, enter dashboard rule.

3. For Conditions, copy and paste the condition below.

   Routing rule

   query_type() IN ('JDBC', 'ODBC')

4. For Action, keep the default of Route to engine. Select Tutorial Engine from the dropdown.

5. Click Add. The rule will appear in the Engine Routing list.

6. Use the handle on the left to drag the dashboard rule to the top of the list so that it is the first rule that is evaluated when queries are triggered.

Step 3. Verify that Jobs Route to the New Engine

Now that you have a new engine and routing rule configured, let's go back to the Tableau or Power BI file you downloaded in the Visualize Data Using Tableau or Power BI to verify that the jobs triggered via JDBC/ODBC from these tools will route to the Tutorial Engine.

1. Return to your Tableau .tds or a Power BI .pbids file. You may need to re-authenticate to Dremio if the session has expired.

2. Follow the steps for the tool you picked:

   • Tableau Add SUM(Passenger Count) into the Rows shelf and select Average as the aggregation.
   • Power BI Add SUM(Passenger Count) under Y-axis and select Average as the measure.

   The Dremio jobs that are triggered by this update, will route to the Tutorial Engine.

3. To verify that the rule is routing jobs to the Tutorial Engine, navigate to the Jobs page in the Dremio Console.

In the Jobs table, you’ll see the queries that were triggered from the changes you made in Tableau or Power BI. Notice that the Engine column indicates that they were redirected to the Tutorial Engine instead of the preview engine where the other jobs from your changes have been running.

note

The Jobs page tracks all the jobs that have run in the project. For additional details, you can click into a job to get a visual representation of the execution plan and a breakdown of how long each phase of job execution took.

To learn more about jobs and using the information to troubleshoot jobs, see Viewing Jobs and Job Details.

caution

If you're done with this module, you can delete the Tutorial Engine so you don't incur unnecessary cost. Setting the Min Replicas to 0 also stops the active engine replica.

• To delete the engine: Navigate to Project Settings > Engines, hover over Tutorial Engine and click , then confirm that you want to remove the engine.

• To remove the routing rule: Navigate to Project Settings > Engine Routing, hover over dashboard rule and click , then confirm that you want to remove the rule.

Next: Add Users & Assign Roles