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 regarding the Firebolt Columns Mapping section, along with a complete list of the Datatypes 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 data will be loaded as an ARRAY with the type that is set in the mapping. For example, if the field data type is a STRING and you select the repeated mode, the data will be loaded into a column of type ARRAY(STRING).

As per the Firebolt documentation, an ARRAY cannot be null, and is 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 data will be loaded as a string, which can then be manipulated with various JSON_EXTRACT functions. Refer to firebolt documentation for more information on JSON functions.

Primary index keys

Defining the table primary index key is supported 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. For more information, refer to Additional information on primary index topic.

Partitions

Defining the partition for a table is supported in Data Integration using the Partition checkbox in the columns mapping. If an elaborate partition expression is required, you can enter the partition expression in the columns mapping near the appropriate column.

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

Expressions

A column may also be a target of an expression value. In that case, use the Expression definition on the right side of the mapping table. When you use an expression, the source column is ignored. The expression can use other columns in the target section of the mapping table, and any valid expression that can be used in a Firebolt SQL clause.