Data Type Mappings Between SQL and Handler Languages (original) (raw)

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:

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 TIMEZONESnowflake 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 SnowflakeSnowpark 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 returnstatement 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:

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 inSQL-Java Data Type Mappings.