Snowflake Data Type Conversions
This guide covers the Snowflake data types that are supported by RelationalAI and shows how they are represented.
Introduction
A RAI data stream synchronizes your data from a Snowflake database to a RAI database. It originates from a Snowflake object, which can be a table (opens in a new tab) or a view (opens in a new tab), and targets a base relation in a RAI database.
Most data types in Snowflake (opens in a new tab) directly map to their corresponding data types in RelationalAI.
For instance, VARCHAR
in Snowflake is mapped to String
in RelationalAI.
A summary of the supported Snowflake data types is given below.
For details on Snowflake data types that are not supported in RelationalAI, see Unsupported Snowflake Data Types.
See the example below to learn more about the data type conversions.
Supported Snowflake Data Types
The following table shows which Snowflake data types are supported in RelationalAI:
Category | Snowflake Data Type | RelationalAI Data Type | Details |
---|---|---|---|
String | Varchar | String | - |
Binary | Binary | String | Snowflake Binary type does not match to any RelationalAI data type. Therefore, the system converts it to String . |
Numeric | Number | Int or FixedDecimal | See Numeric Data Types for details on how Snowflake precision matches RelationalAI bit sizes. |
Numeric | Float | Float | By default, Float bit width is 64 bits. |
Logical | Boolean | Boolean | Represented as boolean_true or boolean_false . |
Date | Date | Date | - |
Time | Time | String | Snowflake Time type does not match to any RelationalAI data type. Therefore, the system converts it to String . See Time Periods to learn more about RelationalAI time-related data types. |
Time | Timestamp_ltz | DateTime | - |
Time | Timestamp_ntz | DateTime | - |
Time | Timestamp_tz | DateTime | - |
Unsupported Snowflake Data Types
Semi-structured data types (opens in a new tab) and geospatial data types (opens in a new tab) are not currently supported and cannot be synchronized with RelationalAI.
These Snowflake data types are not currently supported in RelationalAI:
Category | Snowflake Data Type |
---|---|
Semi-structured | Variant |
Semi-structured | Object |
Semi-structured | Array |
Geospatial | Geography |
Geospatial | Geometry |
Attempting to synchronize a Snowflake object that contains any of these unsupported types results in an error like the following:
Numeric Data Types
The Snowflake data type Number
encompasses all numeric fixed-point data types.
It provides optional precision and scale parameters.
See Snowflake’s Data Types for Fixed-point Numbers (opens in a new tab) for more details.
The following table summarizes how the Snowflake numeric data types for fixed-point numbers are represented in RelationalAI:
Snowflake Data Type | RelationalAI Data Type | Details |
---|---|---|
Number , Int , Integer , Bigint , Smallint , Tinyint , and Byteint | Int | By default, Int bit width is 64 bits. |
Number(precision, scale) , Decimal , Dec , and Numeric | FixedDecimal | Up to 38 decimals using 128 bit width. |
Depending on the precision value of Number
, these are its conversions to FixedDecimal
:
Number Precision | FixedDecimal Bit Size | FixedDecimal Decimals |
---|---|---|
0 - 2 | 8 | 0 - 2 |
3 - 4 | 16 | 0 - 4 |
5 - 9 | 32 | 0 - 9 |
10 - 18 | 64 | 0 - 18 |
19 - 38 | 128 | 0 - 38 |
Only the precision parameter of Number
is used for its conversion. The scale parameter is not involved.
Here’s an example of a Snowflake table containing data types for fixed-point numbers:
CREATE OR REPLACE TABLE numeric_types(
num1 NUMBER,
num2 NUMBER(10,1),
num3 DECIMAL(20,2),
num4 NUMERIC(30,3),
num5 INT,
num6 INTEGER
)
AS SELECT * FROM VALUES(
12345, 1.1, 2.22, 3.333, 4, 5
);
You can create a RAI data stream to synchronize this Snowflake table numeric_types
with a base relation rai_numeric_types
within your working RAI database rai_db
:
CALL RAI.create_data_stream('sf_db.sf_schema.numeric_types', 'rai_db', 'rai_numeric_types');
After the data stream is fully synchronized, querying rai_numeric_types
in RelationalAI gives this output:
// read query
def output = ::std::display::table[rai_numeric_types]
Note that the row ID is a SHA1
data type.
Example
Consider the following Snowflake table containing all the supported data types:
CREATE OR REPLACE TABLE data_types(
v VARCHAR,
bin BINARY,
n NUMBER,
f FLOAT,
b BOOLEAN,
d DATE,
t TIME,
ts1 TIMESTAMP_LTZ,
ts2 TIMESTAMP_NTZ,
ts3 TIMESTAMP_TZ
)
You can insert some data:
INSERT INTO data_types VALUES(
'text',
TO_BINARY('bin'::STRING, 'UTF8'),
10,
10.10,
true,
'2023-10-12',
'10:00:00',
'2023-10-12 10:00:00',
'2023-10-12 10:00:00',
'2023-10-12 10:00:00'
);
Now, you can create a RAI data stream to synchronize this Snowflake table data_types
with a base relation rai_data_types
within your working RAI database rai_db
:
CALL RAI.create_data_stream('sf_db.sf_schema.data_types', 'rai_db', 'rai_data_types');
After the RAI data stream is fully synchronized, querying rai_data_types
in RelationalAI gives this output:
// read query
def output = ::std::display::table[rai_data_types]
Note that the row ID is a SHA1
data type.
See also
For more information on managing data using the RelationalAI Integration Services, see the RAI Data Streams guide.