Extract the name and status of the default engine of a database

Help Center
1

The Problem

There are cases when we need to extract the name of the default engine and check for its status (Running/Stopped).

Default engines are marked as <engine name> (default) in the attached_engines field on the information_schema.databases table. When more than one engine is attached to the database, we will see a comma-delimited list of engine names.

The Solution

To extract just the name of default engines, we can use both the UNNEST (see Firebolt documentation reference) and SPLIT (see Firebolt documentation reference) commands with UNNEST (SPLIT(',',attached_engines) default_engine_names).

Examples

We can use different variations to get a specific result based on the above. Run the following queries on any running engine (or the system engine).

Example 1

Get the default engine of each DB 

SELECT database_name , region , default_engine_names, created_on , created_by , errors FROM information_schema.databasesUNNEST (SPLIT(',',attached_engines) default_engine_names)WHERE default_engine_names ILIKE '%default%'

Example 2

Check the default engine status of a specific DB 

SELECT engine_name , status FROM information_schema.enginesWHERE engine_name IN (SELECT SUBSTRING(engines_names, 1, (strpos(engines_names,'(default)')-3)) FROM information_schema.databasesUNNEST (SPLIT(',',attached_engines) engines_names)WHERE engines_names ILIKE '%default%'AND database_name = '<db_name>')

Example 3

Get a unified view of both default engines, their status and additional information

WITH default_engine_table AS (    SELECT      database_name,                region,                SUBSTRING(default_engines_names, 1, (strpos(default_engines_names,'(default)')-3)) as engine_name,                attached_engines,                created_on,                created_by,                errors    FROM        information_schema.databases UNNEST(SPLIT (',', attached_engines) default_engines_names)    WHERE       default_engines_names ILIKE '%default%')SELECT      database_name,            d.region,            d.engine_name,            CONCAT(e.spec, ' with ', e.scale, ' nodes') AS engine_specs_and_scale,            e.status,            created_on,            created_by,            errorsFROM        default_engine_table dLEFT JOIN   information_schema.engines e USING(engine_name)