Firebolt columns mapping

Firebolt columns mapping includes some features that make the loading into Firebolt easier, while selecting from the table is quicker and easier.

Here are some of the main features of the Firebolt Columns Mapping section, along with a complete list of the Data types supported in Data Integration.

Supported data type

Data Integration Datatype	Firebolt Datatype	Mode
VARCHAR	STRING	Nullable
TEXT	STRING	Nullable
INT	BIGINT	Nullable
SMALLINT	INTEGER	Nullable
BIGINT	BIGINT	Nullable
DATE	DATE	Nullable
TIME	TIMESTAMP	Nullable
CHAR	STRING	Nullable
JSON	STRING	Nullable
ARRAY	STRING/INTERGE etc'	Repeated
DATETIME	TIMESTAMP	Nullable
TINYINT	INTEGER	Nullable
BOOLEAN	STRING	Nullable
BIT	STRING	Nullable
REAL	DOUBLE	Nullable
FLOAT	DOUBLE	Nullable
NUMBER	BIGINT	Nullable
DOUBLE	DOUBLE	Nullable
OBJECT	STRING	Nullable
TIMESTAMP_LTZ	TIMESTAMP	Nullable
TIMESATMP_NTZ	TIMESTAMP	Nullable
TIMESTAMP_TZ	TIMESTAMP	Nullable
STRING	STRING	Nullable
DECIMAL	DOUBLE	Nullable

Data types

When you select the repeated mode, the platform loads data as an ARRAY with the type set in the mapping. For example, if the field data type is a STRING and you select the repeated mode, the platform loads data into a column of type ARRAY(STRING).

As per the Firebolt specifications, an ARRAY cannot contain null values, and automatically created as not null in Data Integration.

Currently, there is no native support for Record JSON/object sources. The mapping automatically converts each Record into a string column, and the platform loads the data as a string, which manipulated with different JSON_EXTRACT functions. Refer to Firebolt for more information on JSON functions.

Primary index keys

Define the table primary index key in Data Integration using the Primary Index checkbox in the columns mapping. Enabling this checkbox lets you define the order of the primary index key by the number to the right of the checkbox.

Primary indexes are mandatory in FACT tables and optional in DIMENSION tables.

warning

Columns that you specify in a primary index must not contain NULL.

Partitions

You can define the partition for a table in Data Integration using the Partition checkbox in the columns mapping. If you require an elaborate partition expression, you can provide the partfirebolt-columns-mapping.mdtion expression in the columns mapping near the appropriate column.

Partitions are only available for FACT tables. For more information on partitions and supported functions in partition expressions, refer to the Working with partitions topic.

Expressions

A column can also take a value using an expression. In this case, define the value in the Expression field on the right side of the mapping table. When you use an expression, the platform ignores the source column. The expression can reference other columns in the target section of the mapping table, and any valid expression supported in a Firebolt SQL clause.