Getting Started with Firebolt Engines Using SQL

1

Introduction

Efficiently managing and scaling your Firebolt engines is crucial for optimizing performance and resource utilization in your data warehouse. Whether you need to create, start, stop, or resize engines, Firebolt provides both UI and API methods to achieve these tasks. By the end of this article, you will know how to manage Firebolt engines using SQL commands.

TL;DR

  • Create Engines : Use CREATE ENGINE command to set up new engines.
  • Start/Stop Engines : Use START ENGINE and STOP ENGINE commands.
  • Resize Engines : Use ALTER ENGINE command to scale engines up/down or out/in.
  • Concurrency Scaling : Adjust the CLUSTERS attribute for handling concurrency.
  • Automatic Operations : Set engines to automatically start or stop based on activity.

Step-by-Step Guide

All the example SQL code uses the Ultra Fast Gaming data set. To familiarize yourself with this data set, visit this link.

Step 1: Creating Engines

To create an engine using the SQL API, use the CREATE ENGINE command. You can specify the engine type, number of nodes, and clusters.

Example SQL code:

-- Create an engine with one cluster and one 'S' type node
    CREATE ENGINE ultra_fast_gaming_dev_engine;

-- Create an engine with one cluster and two 'M' type nodes
    CREATE ENGINE ultra_fast_gaming_dev_engine WITH TYPE = "M" NODES = 2 CLUSTERS = 1;

Comments : The first command creates an engine with default settings, while the second specifies the node type and quantity.

Step 2: Starting and Stopping Engines

After creating your engine, use the START ENGINE and STOP ENGINE commands, in order to start or stop your engine.

Example SQL code:

-- Start an engine named 'MyEngine'
    START ENGINE ultra_fast_gaming_dev_engine;

-- Stop an engine named 'MyEngine'
    STOP ENGINE ultra_fast_gaming_dev_engine;

Comments : Starting an engine transitions it to a running state, and stopping an engine clears its cache, resulting in a cold start when restarted.

Step 3: Resizing Engines

Scaling your engine up or down involves changing the node type, while scaling out or in involves changing the number of nodes.

Example SQL code:

-- Scale up an engine to 'M' type nodes
    ALTER ENGINE ultra_fast_gaming_dev_engine SET TYPE = "M";

-- Scale out an engine to three nodes
    ALTER ENGINE ultra_fast_gaming_dev_engine SET NODES = 3;

Comments : The first command changes the node type across all clusters, while the second command adjusts the number of nodes per cluster.

Step 4: Handling Concurrency Scaling

Adjust the CLUSTERS attribute to manage concurrency scaling by increasing or decreasing the number of clusters.

Example SQL code:

-- Scale the engine to two clusters
    ALTER ENGINE ultra_fast_gaming_dev_engine SET CLUSTERS = 2;

Comments : This operation helps manage concurrent workloads by distributing the load across multiple clusters.

Step 5: Setting Automatic Start and Stop

When creating or modifying an engine, you can specify whether it should automatically start or stop based on activity.

Example SQL code:

-- Create an engine that does not start immediately, but starts automatically when queried and stops after 10 minutes of idle time
   CREATE ENGINE ultra_fast_gaming_analytics_engine WITH INITIALLY_STOPPED = true, AUTO_START = true, AUTO_STOP = 10;

-- Modify an existing engine to stop after 30 minutes of idle time
   ALTER ENGINE ultra_fast_gaming_analytics_engine SET AUTO_STOP = 30;

Comments : The first command creates an engine that initially remains stopped but starts automatically when a query is sent to it and stops after being idle for 10 minutes. The second command adjusts the idle time setting for an existing engine.

Full Example Code

-- Create an engine with one cluster and two 'M' type nodes
    CREATE ENGINE ultra_fast_gaming_dev_engine WITH TYPE = "M" NODES = 2 CLUSTERS = 1;

-- Start the engine
    START ENGINE ultra_fast_gaming_dev_engine;

-- Stop the engine
    STOP ENGINE ultra_fast_gaming_dev_engine;

-- Scale up the engine to 'M' type nodes
    ALTER ENGINE ultra_fast_gaming_dev_engine SET TYPE = "M";

-- Scale out the engine to three nodes
    ALTER ENGINE ultra_fast_gaming_dev_engine SET NODES = 3;

-- Scale the engine to two clusters
    ALTER ENGINE ultra_fast_gaming_dev_engine SET CLUSTERS = 2;

-- Create an engine that does not start immediately, but starts automatically when queried and stops after 10 minutes of idle time
    CREATE ENGINE ultra_fast_gaming_analytics_engine WITH INITIALLY_STOPPED = true, AUTO_START = true, AUTO_STOP = 10;

-- Modify an existing engine to stop after 30 minutes of idle time
   ALTER ENGINE ultra_fast_gaming_analytics_engine SET AUTO_STOP = 30;