Creates a new table in the current database.
Firebolt supports create table as select (CTAS). For more information, see CREATE TABLE AS SELECT(CTAS).
- Column constraints & default expression
CREATE [FACT|DIMENSION] TABLE [IF NOT EXISTS] <table_name> ( <column_name> <column_type> [constraints] [, <column_name> <column_type> [constraints] [, ...n]] ) [PRIMARY INDEX <column_name>[, <column_name>[, ...k]]] [PARTITION BY <column_name>[, <column_name>[, ...m]]]
| ||An identifier that specifies the name of the table. This name should be unique within the database.|
| ||An identifier that specifies the name of the column. This name should be unique within the table.|
| ||Specifies the data type for the column.|
All identifiers are case insensitive unless double-quotes are used. For more information, please see our identifier requirements page.
Column constraints & default expression
Firebolt supports the column constraints shown below.
<column_name> <column_type> [UNIQUE] [NULL | NOT NULL] [DEFAULT <expr>]
| ||Determines the default value that is used instead of NULL value is inserted.|
| ||Determines if the column may or may not contain NULLs.|| |
| ||This is an optimization hint to tell Firebolt that this column will be queried for unique values, such as through a |
Note that nullable columns can not be used in Firebolt indexes (Primary, Aggregating or Join indexes).
Example–Creating a table with nulls and not nulls
This example illustrates different use cases for column definitions and INSERT statements.
- Explicit NULL insert–a direct insertion of a
NULLvalue into a particular column.
- Implicit NULL insert–an
INSERTstatement with missing values for a particular column.
The example uses a fact table in which to insert different values. The example below creates the fact table
CREATE FACT TABLE t1 ( col1 INTEGER NULL , col2 INTEGER NOT NULL UNIQUE, col3 INTEGER NULL DEFAULT 1, col4 INTEGER NOT NULL DEFAULT 1, col5 TEXT ) PRIMARY INDEX col2;
Once we’ve created the table, we can manipulate the values with different INSERT statements. Following are detailed descriptions of different examples of these:
|INSERT statement||Results and explanation|
| ||1 is inserted into each column|
| ||col1 is |
| ||This is an example of explicit and implicit INSERT statements. col1 is |
| ||The behavior here depends on the column type. For both cases, a “null mismatch” event occurs. |
In the original table creation, col2 receives a
In this particular case, the data type for col4 is
| ||col3 is |
| ||col3 is |
| ||col4 is |
| ||col4 is |
| ||The nullability and default expression for col5 were not specified. In this case, Firebolt treats col5 as |
For the implicit insert, Firebolt resorts to the default, and again, attempts to insert NULL. Similar to the explicit NULL case - an empty value
PRIMARY INDEX is a sparse index containing sorted data based on the indexed field. This index clusters and sorts data as it is ingested, without affecting data scan performance. A
PRIMARY INDEX is optional. For more information, see Using primary indexes.
PRIMARY INDEX <column_name>[, <column_name>[, ...n]]
The following table describes the primary index parameters:
| ||Specifies the name of the column in the Firebolt table which composes the index. At least one column must be used for configuring the index.||Y|
PARTITION BY clause specifies a column or columns by which the table will be split into physical parts. Those columns are considered to be the partition key of the table. Columns must be non-nullable. When the partition key is set with multiple columns, all columns are used as the partition boundaries.
PARTITION BY <column_name>[, <column_name>[, ...n]]
For more information, see Working with partitions.
Firebolt supports two types of tables:
FACTtable - the data is distributed across all nodes of the engine.
DIMENSIONtable - entire table is replicated in every node of the engine.
The default is
DIMENSION tables are useful, when the table is relatively small (up to tens of gigabytes), and used in joins with