Get started using SQL

​

Register with Firebolt

1. Register with Firebolt. Fill in your email, name, choose a password, and select ‘Get Started’.
2. Firebolt will send a confirmation to the address that you provided. To complete your registration, select Verify in the email to take you to Firebolt’s login page.
3. Type in your email and password and select Log In.

​

Create a Database

• A database holds the elements that you need to run queries such as tables, views and information schema.
• An engine provides the compute resources for ingesting data and running queries. For more information on using Firebolt engines and how to select the correct size for your workload, see Operate engines.

1. In the left navigation pane, select the + to the right Databases.
2. Select Create new database.
3. Enter the name for your database in the Database Name field. For this example, use “tutorial_database” as your database name. In Firebolt, the names of engines and databases are case-sensitive. If you are using uppercase characters in their names, enclose their name inside double quotes (“) when you refer to them in SQL.

• Public - A namespace where you can create and manage your database objects including tables, engines and queries. The default schema includes tables, external tables, and views.
• Information_schema - A standardized set of read-only views that provide metadata about database objects including tables, engines, cost information, and queries.

• engine_metering_history - contains information about billing cost in FBU up to the latest second in consumed_fbu.
• engine_billing - contains information about billing cost in US dollars up to the latest day in billed_cost.

SELECT *
FROM information_schema.engine_metering_history

​

Create an Engine

• The database that Script 1 will run using is located directly below the tab name. If you want to change the database, select another database from the drop-down list.
• An engine must be running to process the script in a selected tab. The name and status of the engine that Script 1 uses for computation is located to the right of the current selected database. To change either the engine or the status, select the drop-down arrow next to the engine name. You can select a new engine and change its status from Stopped to Running by selecting Start engine. If you select Run at the bottom of the workspace, the selected engine starts automatically. Select Stop engine to change the status to Stopped. Firebolt automatically stops your engine if it is inactive for 20 minutes.

1. Select the (+) icon next to Databases.
2. Select Create new engine from the drop-down list.
3. Enter the name of your engine in the New engine name text box. For this example, enter “tutorial_engine” as your engine name.

​

Load Data

1. Enter the following into the Script 1 tab to load data using the following steps:

COPY INTO tutorial FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv' WITH HEADER=TRUE;

2. Select Run.
3. In the left navigation pane under the Tutorial_Database, Tables now contains the tutorial table.
4. Expand the drop down menu next to Columns to view the name and data format of each column.
5. Select the More options icon ( ⋮ ) next to the data type of each column name to open a pop-up that allows you to insert the name of the column into your SQL script. You can also select Preview data.
6. To view the contents of the tutorial table, run a SELECT query as shown in the following code example. To run this in a new tab, select the (+) icon next to the Script 1 tab.

SELECT
  *
FROM
  tutorial

1. Select Run. The bottom of your workspace includes information about your processing job in the following tabs:

• The Results tab at the bottom of your Develop Space shows the contents returned by your query. After running the previous SELECT statement, the Results tab should display column names and values for the data in the tutorial.
  • Select the filter icon ( ⫼ ) to change which columns are shown.
  • Select the More options icon ( ⋮ ) to export the contents of the Results tab to a JSON or CSV file.
  • The Statistics tab shows information about running your query including how long it took to run and its status. After running the previous SELECT statement, the Statistics tab shows the status of the statement, its STATUS as having succeeded or failed, how long it took to run the query, the number of rows processed, and the amount of data scanned.
  • Select the More options icon ( ⋮ ) to export the contents of the Statistics tab to a JSON or CSV file.
  • The Query Profile tab contains metrics for each operator used in your query and a Query id. Select an operation to view its metrics. These metrics include the following:
    • The output cardinality - the number of rows that each operator produced.
    • The thread time - the sum of the wall clock time that threads spent to run the selected operation across all nodes.
    • The CPU time - the sum of the time that threads that ran the operator were scheduled on a CPU core.
    • The output types - the data types of the result of the operator.

• The Engine monitoring tab shows monitoring information including the percent CPU, memory, disk use and cache read. Information is shown from the last 5 minutes by default. Select a different time interval from the drop-down menu next to Last 5 minutes. You can also select the Refresh icon next to the drop-down menu to update the graphical information.
• The Query history tab shows detailed information associated with each query, listed by its Query id. This information includes the query status, start time, number of rows and bytes scanned during the load, user and account information. You can choose the following options at the top of the bottom panel:
  • Select the Refresh icon to update the query history and ID.
  • Select the filter icon ( ⫼ ) to remove or add columns to display.
  • Select the More options icon ( ⋮ ) to export the contents of the Query history tab to a JSON or CSV file.

​

Run Query

1. Select the (+) icon next to the Script 2 tab to open a new tab.
2. Enter the following simple query, which fetches a list of databases associated with your account:
   Copy

   Ask AI

   SHOW CATALOGS;
   

3. Select Run to process the query. Firebolt uses the engine listed to the right of your database to run your query and its status of Running or Stopped. You can select a different engine from the dropdown menu next to the engine () icon. If your engine is Stopped, Firebolt may prompt you to start your engine. Select Start Engine. Engine startup typically requires a few moments to complete, as Firebolt prepares your environment for data analysis.

​

Optimize your workflow

• Reduce the amount of data that the query scans.
• Pre-calculate values that are used repeatedly during computations.

​

Primary Indexes

CREATE TABLE IF NOT EXISTS levels (
    "LevelID" INT,
    "Name" TEXT,
    "GameID" INT,
    "LevelType" TEXT,
    "MaxPoints" INT,
    "PointsPerLap" DOUBLE,
    "SceneDetails" TEXT
)
PRIMARY INDEX "LevelID", "Name";

COPY INTO levels
FROM 's3://firebolt-publishing-public/help_center_assets/firebolt_sample_dataset/levels.csv'
WITH TYPE = CSV
HEADER = TRUE;

​

Aggregating Indexes

CREATE AGGREGATING INDEX
  levels_agg_idx
ON tutorial (
  "LevelType"
  , AVG("NumberOfLaps")
  );

​

Warm data and cache eviction

​

Warm data

• If you need access to all the data in a table, use CHECKSUM to warm the entire table as follows:
  Copy

  Ask AI

  SELECT CHECKSUM(*) FROM levels;
  

• If you only need a few columns that meet a certain criteria, filter them before warming the data as shown in the following code example:
  Copy

  Ask AI

  SELECT CHECKSUM("Name", "MaxPoints") FROM levels WHERE "MaxPoints" BETWEEN 100 AND 250;
  

• If you have a large dataset, you can divide the data into smaller segments, and execute the queries in parallel, as shown in the following code example:
  Copy

  Ask AI

  SELECT CHECKSUM("Name", "MaxPoints") FROM levels WHERE "MaxPoints" BETWEEN 0 AND 100;
  SELECT CHECKSUM("Name", "MaxPoints") FROM levels WHERE "MaxPoints" BETWEEN 101 AND 200;
  SELECT CHECKSUM("Name", "MaxPoints") FROM levels WHERE "MaxPoints" > 200;
  

​

Cache eviction

SHOW CACHE;

SELECT * FROM information_schema.tables WHERE table_name = 'levels';

​

Clean up

• Stop any running engines.
• Remove data from storage.

​

Stop any running engines

Firebolt shows you the status of your current engine next to the engines icon () under your script tab as either Stopped or Running. To shut down your engine, select your engine from the drop-down list next to the name of the engine, and then select one of the following:

• Stop engine - Allow all of the currently running queries to finish running and then shut down the engine. Selecting this option will allow the engine to run for as long as it takes to complete all queries running on the selected engine.
• Terminate all queries and stop - Stop the engine and stop running any queries. Selecting this option stops the engine in about 20-30 seconds.

​

Remove data from storage

DROP TABLE levels

• Select the database from the left navigation bar.
• Select the More options ( ⋮ ) icon.
• Select Delete database. Deleting your database will permanently remove your database from Firebolt. You cannot undo this action. Select Delete.

​

Export data

​

Set permissions to write to an AWS bucket

1. AWS access key credentials. The credentials must be associated with a user with permissions to write objects to the bucket. Specify access key credentials using the following syntax:

  CREDENTIALS = (AWS_ACCESS_KEY_ID = '<aws_access_key_id>' AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>')

2. An AWS IAM policy statement attached to a user role. Firebolt requires the following minimum permissions in the IAM policy:
   Copy

   Ask AI

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": [
           "s3:Get*",
           "s3:List*",
           "s3:PutObject",
           "s3:DeleteObject"
         ],
         "Resource": [
           "arn:aws:s3:::my_s3_bucket",
           "arn:aws:s3:::my_s3_bucket/*"
         ]
       }
     ]
   }
   

   For more information about AWS access keys and roles, see Creating Access Key and Secret ID in AWS.

​

Export to an AWS bucket

COPY (SELECT * FROM test_table)
  TO 's3://my-bucket/path/to/data'
  CREDENTIALS =
  (AWS_ROLE_ARN= 'arn:aws:iam::123456789012:role/my-firebolt-role');

s3://my_bucket/my_fb_queries/
16B903C4206098FD_0.csv.gz
16B903C4206098FD_1.csv.gz
16B903C4206098FD_2.csv.gz
16B903C4206098FD_3.csv.gz

​

Next steps