System Engine

Firebolt’s system engine enables running various metadata-related queries without having to start an engine. The system engine is always available for you in all databases to select and use.

The system engine supports running the following commands:

• All access control commands
• All engine commands
• Most data definition commands. The following commands are not supported:
  • ALTER TABLE DROP PARTITION
  • CREATE AGGREGATING INDEX
  • CREATE EXTERNAL TABLE
  • CREATE TABLE AS SELECT
• Most metadata commands. The following commands are not supported:
  • SHOW CACHE
• Non-data-accessing SELECT queries like SELECT CURRENT_TIMESTAMP()
• SELECT queries on some information_schema views:
  • information_schema.accounts
  • information_schema.applicable_roles
  • information_schema.transitive_applicable_roles
  • information_schema.columns
  • information_schema.catalogs
  • information_schema.engines
  • information_schema.indexes
  • information_schema.logins
  • information_schema.network_policies
  • information_schema.service_accounts
  • information_schema.tables
  • information_schema.users
  • information_schema.views

Using the system engine via the Firebolt manager

1. In the Firebolt manager, choose the Databases icon in the navigation pane.
2. Click on the SQL Workspace icon for the desired database. In case you have no database in your account - create one first.
3. From the engine selector in the SQL Workspace, choose System Engine, then run one of the supported queries.

Using the system engine via SDKs

Python SDK

Connect via the connector without specifying the engine_name. Database parameter is optional.

System engine does not need a database defined. If you wish to connect to an existing database and run metadata queries with the system engine, just specify the name of your database.

Example

from firebolt.db import connect
from firebolt.client import DEFAULT_API_URL
from firebolt.client.auth import ClientCredentials

client_id = "<service_account_id>"
client_secret = "<service_account_secret>"
account_name = "<your_account_name>"

with connect(
   database="<any_db_here>", # Omit this parameter if you don't need db-specific operations
   auth=ClientCredentials(client_id, client_secret),
   account_name=account_name,
   api_endpoint=DEFAULT_API_URL,
) as connection:

   cursor = connection.cursor()

   cursor.execute("SHOW CATALOGS")

   print(cursor.fetchall())

Guidance on creating service accounts can be found in the service account section.

Other SDKs

Any other Firebolt connector can also be used similarly, as long as the engine name is omitted.

Known limitations and future release plans

Supported queries for system engine

At this time, the system engine only supports running the metadata-related queries listed above. Additional queries will be supported in future versions.

System engine is currently only available for accounts that have a single region enabled.