Data Type Mappings Between SQL and Handler Languages¶

A stored procedure or user-defined function you write is called from SQL, and so receives and returns values in SQL data types. However, its underlying handler will use data types from the handler’s language, such as Java, Python, or Scala. At runtime, Snowflake converts between the SQL types and handler types for arguments and return values.

Note that Snowflake makes these conversions the following cases as well:

When dynamically constructing a SQL statement that uses a value in a handler variable.
When binding a handler variable’s value to a prepared statement.

This topic describes valid mappings between SQL data and types and those from the supported handler languages. Use this content to choose data types when writing a handler.

For information about Snowflake SQL data types, see Summary of data types.

SQL-Java Data Type Mappings¶

The table below shows the type mappings between SQL and Java. These mappings generally apply to both the arguments passed to the procedure or function and the values returned from it. However, there are some exceptions, which are listed in footnotes.

Note that some SQL data types (e.g. NUMBER) are compatible with multiple Java data types (e.g. int, long, etc.). In these cases, you can use any Java data type that has enough capacity to hold the actual values that will be passed. If you pass a SQL value to an incompatible Java data type (or vice versa), Snowflake throws an error.

SQL Type	Java Type	Notes
ARRAY	`String[]`	Formats the elements of the array as strings.
ARRAY	`String`	Formats the array as a JSON string (e.g. `[1, "foo", null]`).
BINARY	`byte[]`
BINARY	`String`	Encodes the binary string in hexadecimal. [4]
BINARY	`InputStream`	Exposes the BINARY value as a sequence of bytes.
BOOLEAN	`boolean`	Cannot be null.
BOOLEAN	`Boolean`
BOOLEAN	`String`	[4]
DATE	`java.sql.Date`
DATE	`String`	Formats the date as `YYYY-MM-DD`. [4]
FLOAT	`double`	Cannot be null.
FLOAT	`Double`
FLOAT	`float`	Cannot be null. Might result in precision loss.
FLOAT	`Float`	Might result in precision loss.
FLOAT	`String`	Might result in precision loss (float -> string conversion is lossy).
GEOGRAPHY	`String`	Formats the geography as GeoJSON .
GEOGRAPHY	Geography	[5]
MAP	`Map<String, String>`	The output format is MAP(VARCHAR, VARCHAR).
NUMBER	`short`	Cannot be null. Must fit in the range of short (no fractional part, and integer part cannot exceed the max/min short values).
NUMBER	`Short`	Must fit in the range of short (no fractional part, and integer part cannot exceed the max/min short values).
NUMBER	`int`	Cannot be null. Must fit in the range of int (no fractional part, and integer part cannot exceed the max/min int values).
NUMBER	`Integer`	Must fit in the range of int (no fractional part, and integer part cannot exceed the max/min int values).
NUMBER	`long`	Cannot be null. Must fit in the range of long (no fractional part, and integer part cannot exceed the max/min long values).
NUMBER	`Long`	Must fit in the range of long (no fractional part, and integer part cannot exceed the max/min long values).
NUMBER	`java.math.BigDecimal`
NUMBER	`java.math.BigInteger`	Must fit into the range of BigInteger (no fractional part).
NUMBER	`String`
OBJECT	`Map<String, String>`	The map’s keys are the object’s keys, and the values are formatted as strings.
OBJECT	`String`	Formats the object as a JSON string (e.g. `{"x": 3, "y": true}`).
TIME	`java.sql.Time`	[3]
TIME	`String`	Formats the time as `HH:MI:SS.SSSSSSSSS` where the fractional seconds part depends on the precision of the time. [3]
TIMESTAMP_LTZ	`java.sql.Timestamp`	Must fit in the range of java.sql.Timestamp. [3]
TIMESTAMP_LTZ	`String`	The output format is `DY, DD MON YYYY HH24:MI:SS TZHTZM`. [1] , [3] , [4]
TIMESTAMP_NTZ	`java.sql.Timestamp`	Must fit in the range of java.sql.Timestamp. Treats the wallclock time as an offset from the Unix epoch (imposing a UTC time zone, effectively). [3]
TIMESTAMP_NTZ	`String`	Treats the wallclock time as an offset from the Unix epoch (imposing a UTC time zone, effectively). The output format is `DY, DD MON YYYY HH:MI:SS`. [2] , [3] , [4]
TIMESTAMP_TZ	`java.sql.Timestamp`	Must fit in the range of java.sql.Timestamp. [3]
TIMESTAMP_TZ	`String`	The output format is `DY, DD MON YYYY HH24:MI:SS TZHTZM`. [1] , [3] , [4]
VARCHAR	`String`
VARIANT	Variant	The Variant data type is a class in the Snowpark package. For more information, see Snowpark Package Types Supported for User-Defined Functions. For an example, see Passing a VARIANT value to an in-line Java UDF.

Arrays¶

Java UDFs can receive arrays of any of the following Java data types:

Data Type	Notes
`String`
`boolean`	The Snowflake ARRAY must contain only BOOLEAN elements and must not contain any NULL values.
`double` `float`	The Snowflake ARRAY must contain either of the following, and must not contain any NULL values. FLOAT elements. Fixed-point elements (with any scale).
`int` `long` `short`	The Snowflake ARRAY must contain only fixed-point elements with a scale of 0, and must not contain any NULL values.

NULL Values¶

Snowflake supports two distinct NULL values: SQL NULL and VARIANT’s JSON null. (For information about Snowflake VARIANT NULL, see NULL Values.)

Java supports one null value, which is only for non-primitive data types.

A SQL NULL argument to a Java handler translates to the Java null value, but only for Java data types that support null.

A returned Java null value translates back to SQL NULL.

TIMESTAMP_LTZ Values and Time Zones¶

A Java UDF is largely isolated from the environment in which it is called. However, the timezone is inherited from the calling environment. If the caller’s session set a default time zone before calling the Java UDF, then the Java UDF has the same default time zone. Java UDF uses the same IANA Time Zone Database data as the native TIMEZONE Snowflake SQL uses (i.e. data from release 2021a of the Time Zone Database).

Snowpark Package Types Supported for User-Defined Functions¶

In a user-defined function, you can use a specific subset of types that are included in the Snowflake Snowpark Java package. Although these types are designed for use in Snowpark code, a few are also supported for use in UDFs for the convenience they can provide. (For more about Snowpark, see the Snowpark documentation.)

Note

The Snowpark library is a requirement for stored procedures written in Java, Python, and Scala. As a result, you can use Snowpark types there without restriction.

Snowpark types in the following table are supported in UDF code. You should not use other Snowpark types in UDF code; they are not supported there.

Snowpark Type	Snowpark Version Required	Description
Geography	1.2.0 and later	Represents the Snowflake GEOGRAPHY type. For an example that uses the `Geography` data type, see Passing a GEOGRAPHY value to an in-line Java UDF.
Variant	1.4.0 and later	Represents Snowflake VARIANT data. For an example that uses the `Variant` data type, see Passing a VARIANT value to an in-line Java UDF.

Specifying the Snowpark Package as a Dependency¶

When developing UDF code that uses the Snowpark package, you’ll need to set up your development environment so that you can compile and run code with Snowpark dependencies. For more, see Setting Up Other Development Environments for Snowpark Java.

When deploying a UDF by executing the CREATE FUNCTION statement, you can specify the Snowpark package as a dependency without uploading the JAR file to a stage (the library is already in Snowflake). To do this, specify the package name and version in the PACKAGES clause. For a syntax example, see Passing a GEOGRAPHY value to an in-line Java UDF.

SQL-JavaScript Data Type Mappings¶

The following table shows the Snowflake SQL data types and the corresponding JavaScript data types:

SQL Data Type	JavaScript Data Type	Notes
ARRAY	`JSON`
BOOLEAN	`number`	The values `true` and `false` are represented by `1` and `0` respectively. Note that this behavior may change in future releases, so you should rely on JavaScript truthiness rather than direct value comparisons.
DATE	`date`
GEOGRAPHY, GEOMETRY	`JSON`
REAL, FLOAT, FLOAT8, FLOAT4, DOUBLE, DOUBLE PRECISION	`number`
TIME	`string`
TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ	`date` or `SfDate`	When a timestamp is passed as an argument to a stored procedure, the timestamp is converted to a JavaScript `date` object. In other situations (e.g. when retrieving from `ResultSet`), a timestamp is converted to an `SfDate` object. For more details about the `SfDate` data type, which is not a standard JavaScript data type, see the JavaScript stored procedures API.
VARCHAR, CHAR, CHARACTER, STRING, TEXT	`string`
VARIANT	`JSON`

Notes¶

Not all Snowflake SQL data types have a corresponding JavaScript data type. For example, JavaScript does not directly support the INTEGER or NUMBER data types. In these cases, you should convert the SQL data type to an appropriate alternative data type. For example, you can convert a SQL INTEGER into a SQL FLOAT, which can then be converted to a JavaScript value of data type number.

The table below shows appropriate conversions for the incompatible SQL data types:

Incompatible SQL Data Type	Compatible SQL Data Type
BINARY	Uint8Array
INTEGER	FLOAT
NUMBER, NUMERIC, DECIMAL	FLOAT
OBJECT	Uint8Array

When Returning Values¶

If the return statement in the JavaScript returns a data type different from the stored procedure’s declared return type, the JavaScript value is cast to the SQL data type if possible. For example, if a number is returned, but the stored procedure is declared as returning a string, the number is converted to a string within JavaScript, and then copied to the string returned in the SQL statement. (Keep in mind that some JavaScript programming errors, such as returning the wrong data type, can be hidden by this behavior.)

If no valid cast for the conversion exists, then an error occurs.

When Binding Values¶

When you bind JavaScript variables to SQL statements, Snowflake converts from the JavaScript data types to the SQL data types. You can bind variables of the following JavaScript data types:

number
string
SfDate

For more details about the SfDate data type, which is not a standard JavaScript data type, see the JavaScript stored procedures API.

For more information about binding, including some examples, see Binding variables.

You might also find the following topics helpful:

SQL-Python Data Type Mappings¶

The table below shows the type mappings between SQL and Python. These mappings generally apply to both the arguments passed to the Python handler and the values returned from it.

SQL Type	Python Type	Notes
ARRAY	`list`	When a Python data type is converted to ARRAY, if there is any embedded Python decimal data, the embedded Python decimal will be converted to a String in the ARRAY.
BINARY	`bytes`
BOOLEAN	`bool`
DATE	`datetime.date`
FLOAT	`float`	Floating point operations can have small rounding errors, which can accumulate, especially when aggregate functions process large numbers of rows. Rounding errors can vary each time a query is executed if the rows are processed in a different order. For more information, see Numeric Data Types: Float.
GEOGRAPHY, GEOMETRY	`dict`	Formats the geography as GeoJSON and then converts it to a Python dict.
MAP	`dict`	MAP is not supported as a return type.
NUMBER	`int` or `decimal.Decimal`	If the scale of the NUMBER type is 0 then the int Python type is used. Otherwise decimal.Decimal type is used.
OBJECT	`dict`	When a Python data type is converted to OBJECT, if there is any embedded Python decimal data, the embedded Python decimal will be converted to a String in the OBJECT.
TIME	`datetime.time`	Although Snowflake can store time values with nanosecond precision, the Python datetime.time type maintains only millisecond precision. Conversion between Snowflake and Python data types can reduce effective precision to milliseconds.
TIMESTAMP_LTZ	`datetime.datetime`	Use local timezone to convert internal UTC time to local “naive” datetime. Requires “naive” datetime as return type.
TIMESTAMP_NTZ	`datetime.datetime`	Directly convert to “naive” datetime. Requires “naive” datetime as return type.
TIMESTAMP_TZ	`datetime.datetime`	Convert to “aware” datetime with timezone information. Requires “aware” datetime as return type.
VARCHAR	`str`
VARIANT	`dict`, `list`, `int`, `float`, `str`, or `bool`	Each variant row is converted to a Python type dynamically for arguments and vice versa for return values. The following types are converted to strings rather than native Python types: decimal, binary, date, time, timestamp_ltz, timestamp_ntz, timestamp_tz. When a Python data type is converted to VARIANT, if there is any embedded Python decimal data, the embedded Python decimal will be converted to a String in the VARIANT.
VECTOR	`memoryview`

SQL-Scala Data Type Mappings¶

Snowflake supports the following Scala data types in addition to the Java types listed in SQL-Java Data Type Mappings:

SQL Data Type	Scala Type	Notes
ARRAY	`Array[String]`
BINARY	`Array[Byte]`
BOOLEAN	`Boolean` or `Option[Boolean]`
DOUBLE	`Double` or `Option[Double]`
FLOAT	`Float` or `Option[Float]`
MAP	`Map[String, String]`	The output format is MAP(VARCHAR, VARCHAR).
NUMBER	The following types are supported: `Int` or `Option[Int]` `Long` or `Option[Long]`
OBJECT	`Map[String, String]`
VARCHAR	`String`
VARIANT	`String`	Formats the value depending on the type that is represented. Variant null is formatted as the string “null”.

For DATE and TIMESTAMP, use the Java types listed in SQL-Java Data Type Mappings.