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
To create an engine:
Go to Project Settings > Engines, then click Add Engine.
In the Add Engine dialog, for Engine Name, enter
(Optional) For Description, enter
This is the engine for Dremio tutorials.
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.
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.
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.
Click Save & Launch.
You’ll see theindicates 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.
Tutorial Engineappears in the Engines list. The animation on the left indicates that the engine is starting 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:
Navigate to Project Settings > Engine Routing, then click Add Rule.
In the New Rule dialog, for Rule Name, enter
For Conditions, copy and paste the condition below.Routing rule
query_type() IN ('JDBC', 'ODBC')
For Action, keep the default of
Route to engine. Select
Tutorial Enginefrom the dropdown.
Click Add. The rule will appear in the Engine Routing list.
Use the handle on the left to drag the
dashboard ruleto 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
Return to your Tableau
.tdsor a Power BI
.pbidsfile. You may need to re-authenticate to Dremio if the session has expired.
Follow the steps for the tool you picked:
SUM(Passenger Count)into the Rows shelf and select Average as the aggregation.
- Power BI
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
- Tableau Add
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 Engineinstead of the
previewengine 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.
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.