Data Types

This section lists all pre-defined data types.

Character Strings

Char

Data type of a fixed-length character string.

Declaration

1CHAR
2CHAR(n)

The type can be declared using CHAR(n) where n is the number of code points. n must have a value between 1 and 2,147,483,647 (both inclusive). If no length is specified, n is equal to 1.

JVM Bridge Types

VARCHAR / STRING

Data type of a variable-length character string.

Declaration

1VARCHAR
2VARCHAR(n)
3
4STRING

The type can be declared using VARCHAR(n) where n is the maximum number of code points. n must have a value between 1 and 2,147,483,647 (both inclusive). If no length is specified, n is equal to 1.

STRING is a synonym for VARCHAR(2147483647).

JVM Bridge Types

Binary Strings

BINARY

Data type of a fixed-length binary string (a sequence of bytes).

Declaration

1BINARY
2BINARY(n)

The type can be declared using BINARY(n) where n is the number of bytes. n must have a value between 1 and 2,147,483,647 (both inclusive). If no length is specified, n is equal to 1.

JVM Bridge Types

VARBINARY / BYTES

Data type of a variable-length binary string (a sequence of bytes).

Declaration

1VARBINARY
2VARBINARY(n)
3
4BYTES

The type can be declared using VARBINARY(n) where n is the maximum number of bytes. n must have a value between 1 and 2,147,483,647 (both inclusive). If no length is specified, n is equal to 1.

JVM Bridge Types

Exact Numerics

DECIMAL

Data type of a decimal number with fixed precision and scale.

Declaration

1DECIMAL
2DECIMAL(p)
3DECIMAL(p, s)
4
5DEC
6DEC(p)
7DEC(p, s)
8
9NUMERIC
10NUMERIC(p)
11NUMERIC(p, s)

The type can be declared using DECIMAL(p, s) where p is the number of digits in a number (precision) and s is the number of digits to the right of the decimal point in a number (scale). p must have a value between 1 and 38 (both inclusive). s must have a value between 0 and p (both inclusive). The default value for p is 10.The default value for s is 0.

NUMERIC(p, s) and DEC(p, s) are synonyms for this type.

JVM Bridge Types

TINYINT

Data type of a 1-byte signed integer with values from -128 to 127.

Declaration

1TINYINT

JVM Bridge Types

SMALLINT

Data type of a 2-byte signed integer with values from -32,768 to 32,767.

Declaration

1SMALLINT

JVM Bridge Types

INT

Data type of a 4-byte signed integer with values from -2,147,483,648 to 2,147,483,647.

Declaration

1INT
2INTEGER

INTEGER is a synonym for this type.

JVM Bridge Types

BIGINT

Data type of an 8-byte signed integer with values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Declaration

1BIGINT

JVM Bridge Types

Approximate Numerics

FLOAT

Data type of a 4-byte single precision floating point number. Compared to the SQL standard, the type does not take parameters.

Declaration

1FLOAT

JVM Bridge Types

DOUBLE

Data type of an 8-byte double precision floating point number.

Declaration

1DOUBLE
2DOUBLE PRECISION

DOUBLE PRECISION is a synonym for this type.

JVM Bridge Types

Date and Time

DATE

Data type of a date consisting of year-month-day with values ranging from 0000-01-01 to 9999-12-31. Compared to the SQL standard, the range starts at year 0000.

Declaration

1DATE

JVM Bridge Types

TIME

Data type of a time without time zone consisting of hour:minute:second[.fractional] with up to nanosecond precision and values ranging from 00:00:00.000000000 to 23:59:59.999999999. Compared to the SQL standard, leap seconds (23:59:60 and 23:59:61) are not supported as the semantics are closer to java.time.LocalTime. A time with time zone is not provided.

Declaration

1TIME
2TIME(p)

The type can be declared using TIME(p) where p is the number of digits of fractional seconds (precision). p must have a value between 0 and 9 (both inclusive). If no precision is specified, p is equal to 0.

JVM Bridge Types

TIMESTAMP

Data type of a timestamp without time zone consisting of year-month-day hour:minute:second[.fractional] with up to nanosecond precision and values ranging from 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999. Compared to the SQL standard, leap seconds (23:59:60 and 23:59:61) are not supported as the semantics are closer to java.time.LocalDateTime.

Declaration

1TIMESTAMP
2TIMESTAMP(p)
3
4TIMESTAMP WITHOUT TIME ZONE
5TIMESTAMP(p) WITHOUT TIME ZONE

JVM Bridge Types

The type can be declared using TIMESTAMP(p) where p is the number of digits of fractional seconds (precision). p must have a value between 0 and 9 (both inclusive). If no precision is specified, p is equal to 6.

TIMESTAMP(p) WITHOUT TIME ZONE is a synonym for this type.

TIMESTAMP WITH TIME ZONE

Data type of a timestamp with time zone consisting of year-month-day hour:minute:second[.fractional] zone with up to nanosecond precision and values ranging from 0000-01-01 00:00:00.000000000 +14:59 to 9999-12-31 23:59:59.999999999 -14:59. Compared to the SQL standard, leap seconds (23:59:60 and 23:59:61) are not supported as the semantics are closer to java.time.OffsetDateTime.

Compared to TIMESTAMP WITH LOCAL TIME ZONE, the time zone offset information is physically stored in every datum. It is used individually for every computation, visualization, or communication to external systems.

Declaration

1TIMESTAMP WITH TIME ZONE
2TIMESTAMP(p) WITH TIME ZONE

JVM Bridge Types

TIMESTAMP WITH LOCAL TIME ZONE

Data type of a timestamp with local time zone consisting of year-month-day hour:minute:second[.fractional] zone with up to nanosecond precision and values ranging from 0000-01-01 00:00:00.000000000 +14:59 to 9999-12-31 23:59:59.999999999 -14:59. Leap seconds (23:59:60 and 23:59:61) are not supported as the semantics are closer to java.time.OffsetDateTime.

Compared to TIMESTAMP WITH TIME ZONE, the time zone offset information is not stored physically in every datum. For computation and visualization every datum is interpreted in the local time zone as configured in the deployment specification.

This type fills the gap between time zone free and time zone mandatory timestamp types by allowing the interpretation of UTC timestamps according to the configured local time zone.

Declaration

1TIMESTAMP WITH LOCAL TIME ZONE
2TIMESTAMP(p) WITH LOCAL TIME ZONE

JVM Bridge Types

INTERVAL YEAR TO MONTH

Data type for a group of year-month interval types.

The type must be parameterized to one of the following resolutions:

• interval of years
• interval of years to months
• interval of months.

An interval of year-month consists of +years-months with values ranging from -9999-11 to +9999-11.

The value representation is the same for all types of resolutions. For example, an interval of months of 50 is always represented in an interval-of-years-to-months format (with default year precision): +04-02.

Declaration

1INTERVAL YEAR
2INTERVAL YEAR(p)
3INTERVAL YEAR(p) TO MONTH
4INTERVAL MONTH

The type can be declared using the above combinations where p is the number of digits of years (year precision). p must have a value between 1 and 4 (both inclusive). If no year precision is specified, p is equal to 2.

JVM Bridge Types

INTERVAL DAY TO MONTH

Data type for a group of day-time interval types.

The type must be parameterized to one of the following resolutions with up to nanosecond precision:

• interval of days
• interval of days to hours
• interval of days to minutes
• interval of days to seconds
• interval of hours
• interval of hours to minutes
• interval of hours to seconds
• interval of minutes
• interval of minutes to seconds
• interval of seconds.

An interval of day-time consists of +days hours:months:seconds.fractional with values ranging from -999999 23:59:59.999999999 to +999999 23:59:59.999999999. The value representation is the same for all types of resolutions. For example, an interval of seconds of 70 is always represented in an interval-of-days-to-seconds format (with default precisions): +00 00:01:10.000000.

Declaration

1INTERVAL DAY
2INTERVAL DAY(p1)
3INTERVAL DAY(p1) TO HOUR
4INTERVAL DAY(p1) TO MINUTE
5INTERVAL DAY(p1) TO SECOND(p2)
6INTERVAL HOUR
7INTERVAL HOUR TO MINUTE
8INTERVAL HOUR TO SECOND(p2)
9INTERVAL MINUTE
10INTERVAL MINUTE TO SECOND(p2)
11INTERVAL SECOND
12INTERVAL SECOND(p2)

The type can be declared using the above combinations where p1 is the number of digits of days (day precision) and p2 is the number of digits of fractional seconds (fractional precision). p1 must have a value between 1 and 6 (both inclusive). p2 must have a value between 0 and 9 (both inclusive). If no p1 is specified, it is equal to 2 by default. If no p2 is specified, it is equal to 6 by default.

JVM Bridge Types

Composite Data Types

Array

Data type of an array of elements with same subtype.

Compared to the SQL standard, the maximum cardinality of an array cannot be specified but is fixed at 2,147,483,647. Also, any valid type is supported as a subtype.

Declaration

1ARRAY<t>
2t ARRAY

The type can be declared using ARRAY<t> where t is the data type of the contained elements. t ARRAY is a synonym for being closer to the SQL standard. For example, INT ARRAY is equivalent to ARRAY<INT>.

JVM Bridge Types

MAP

Data type of an associative array that maps keys (including NULL) to values (including NULL). A map cannot contain duplicate keys; each key can map to at most one value.

There is no restriction of element types; it is the responsibility of the user to ensure uniqueness.

The map type is an extension to the SQL standard.

Declaration

1MAP<kt, vt>

The type can be declared using MAP<kt, vt> where kt is the data type of the key elements and vt is the data type of the value elements.

JVM Bridge Types

MULTISET

Data type of a multiset (bag). Unlike a set, it allows for multiple instances for each of its elements with a common subtype. Each unique value (including NULL) is mapped to some multiplicity.

There is no restriction of element types; it is the responsibility of the user to ensure uniqueness.

Declaration

1MULTISET<t>
2t MULTISET

The type can be declared using MULTISET<t> where t is the data type of the contained elements.

t MULTISET is a synonym for being closer to the SQL standard. For example, INT MULTISET is equivalent to MULTISET<INT>.

JVM Bridge Types

ROW

Data type of a sequence of fields.

A field is essentially just a list of field names and their data types along with an optional description. It allows for defining tables that contain arbitrarily nested data.

Compared to the SQL standard, an optional field description simplifies the handling with complex structures.

A row type is similar to the STRUCT type known from other non-standard-compliant frameworks.

Declaration

1ROW<n0 t0, n1 t1, ...>
2ROW<n0 t0 'd0', n1 t1 'd1', ...>
3
4ROW(n0 t0, n1 t1, ...>
5ROW(n0 t0 'd0', n1 t1 'd1', ...)

The type can be declared using ROW<n0 t0 'd0', n1 t1 'd1', ...> where n is the unique name of a field, t is the logical type of a field, and d is the description of a field.

ROW(...) is a synonym for being closer to the SQL standard. For example, ROW(myField INT, myOtherField BOOLEAN) is equivalent to ROW<myField INT, myOtherField BOOLEAN>.

JVM Bridge Types

Other

BOOLEAN

Data type of a boolean with a (possibly) three-valued logic of TRUE, FALSE, and UNKNOWN.

Declaration

1BOOLEAN

JVM Bridge Types

RAW

Data type of an arbitrary serialized type. This type is a black box within the table ecosystem and is only deserialized at the edges, such as user-defined functions.

The raw type is an extension to the SQL standard.

Declaration

1RAW('class', 'snapshot')

The type can be declared using RAW('class', 'snapshot') where class is the originating class and snapshot is the serialized TypeSerializerSnapshot in Base64 encoding. Usually, the type string is not declared directly but is generated while persisting the type.

JVM Bridge Types

NULL

Data type for representing untyped NULL values.

The null type is an extension to the SQL standard. A null type has no other value except NULL, thus, it can be cast to any nullable type similar to JVM semantics.

This type helps bridging to formats such as JSON or Avro that define such a type as well. This type is not very useful in practice and is just mentioned here for completeness.

Declaration

1NULL

JVM Bridge Types

VARIANT

Data type of a flexible container that can hold values of any other supported data type. This type is useful for handling semi-structured data or dynamic schemas.

Declaration

1VARIANT

JVM Bridge Types

JVM Bridge Types

Each type contains a table with one or more JVM Bridge Types, which are only relevant to developers of user-defined functions. A column of that type can be interpreted as any of the classes in that list when developing functions.

For example, the bridge type for BIGINT is java.lang.Long so a UDF that takes a BIGINT and returns that number plus one may look like:

1public class PlusOne extends ScalaFunction {
2  public Long eval(Long number) {
3    return number + 1
4  }
5}

The "To JVM" columns of the JVM Bridge Types tables indicate whether the SQL engine can map from the Java type to its internal representation of the data type. This is relevant for values returned from user-defined functions. The "From JVM" columns of the JVM Bridge Types tables indicate whether the SQL engine can map from its internal representation to the requested Java type, for example when preparing the arguments that are passed to a user-defined function.