Creates a new fact or Dimension table in the current database.
Firebolt supports create table as select (CTAS). For more information, see CREATE TABLE AS SELECT(CTAS).
- Syntax–fact table
- Syntax–dimension table
- Column constraints & default expression
CREATE FACT TABLE [IF NOT EXISTS] <table_name> ( <column_name> <column_type> [constraints] [, <column_name2> <column_type2> [constraints] [, ...n]] ) PRIMARY INDEX <column_name>[, <column_name>[, ...n]] [PARTITION BY <column_name>[, <column_name>[, ...n]]]
CREATE DIMENSION TABLE [IF NOT EXISTS] <table_name> ( <column_name> <column_type> [constraints] [, <column_name2> <column_type2> [constraints] [, ...n]] ) [PRIMARY INDEX <column_name>[, <column_name>[, ...n]]] [PARTITION BY <column_name>[, <column_name>[, ...n]]]
| ||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.
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).
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 INT NULL , col2 INT NOT NULL UNIQUE, col3 INT NULL DEFAULT 1, col4 INT 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 required for
FACT tables and optional for
DIMENSION tables. 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.