Data Types
This section lists all pre-defined data types.
Character Strings
Char
Data type of a fixed-length character string.
Declaration
CHAR
CHAR(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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.String | Y | Y | Default |
byte[] | Y | Y | Assumes UTF-8 encoding. |
VARCHAR / STRING
Data type of a variable-length character string.
Declaration
VARCHAR
VARCHAR(n)
STRING
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).
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.String | Y | Y | Default |
byte[] | Y | Y | Assumes UTF-8 encoding. |
Binary Strings
BINARY
Data type of a fixed-length binary string (a sequence of bytes).
Declaration
BINARY
BINARY(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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
byte[] | Y | Y | Default |
VARBINARY / BYTES
Data type of a variable-length binary string (a sequence of bytes).
Declaration
VARBINARY
VARBINARY(n)
BYTES
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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
byte[] | Y | Y | Default |
Exact Numerics
DECIMAL
Data type of a decimal number with fixed precision and scale.
Declaration
DECIMAL
DECIMAL(p)
DECIMAL(p, s)
DEC
DEC(p)
DEC(p, s)
NUMERIC
NUMERIC(p)
NUMERIC(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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.math.BigDecimal | Y | Y | Default |
TINYINT
Data type of a 1-byte signed integer with values from -128 to 127.
Declaration
TINYINT
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.Byte | Y | Y | Default |
| byte | Y | (Y) | To JVM only if not nullable. |
SMALLINT
Data type of a 2-byte signed integer with values from -32,768 to 32,767.
Declaration
SMALLINT
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.Short | Y | Y | Default |
| short | Y | (Y) | To JVM only if not nullable. |
INT
Data type of a 4-byte signed integer with values from -2,147,483,648 to 2,147,483,647.
Declaration
INT
INTEGER
INTEGER is a synonym for this type.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.Integer | Y | Y | Default |
| int | Y | (Y) | To JVM only if not nullable. |
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
BIGINT
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.Long | Y | Y | Default |
| long | Y | (Y) | To JVM only if not nullable. |
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
FLOAT
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.Float | Y | Y | Default |
| float | Y | (Y) | To JVM only if not nullable. |
DOUBLE
Data type of an 8-byte double precision floating point number.
Declaration
DOUBLE
DOUBLE PRECISION
DOUBLE PRECISION is a synonym for this type.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.Double | Y | Y | Default |
| double | Y | (Y) | To JVM only if not nullable. |
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
DATE
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.time.LocalDate | Y | Y | Default |
| java.sql.Date | Y | Y | N |
| java.sql.Integer | Y | Y | Describes the number of days since epoch. |
| int | Y | (Y) | Describes the number of days since epoch. Output only if not nullable. |
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
TIME
TIME(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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.time.LocalTime | Y | Y | Default |
| java.sql.Time | Y | Y | N |
| java.lang.Integer | Y | Y | Describes the number of milliseconds of the day. |
| int | Y | (Y) | Describes the number of milliseconds of the day. Output only if not nullable. |
| java.lang.Long | Y | Y | Describes the number of nanoseconds of the day. |
| long | Y | (Y) | Describes the number of nanoseconds of the day. Output only if not nullable. |
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
TIMESTAMP
TIMESTAMP(p)
TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP(p) WITHOUT TIME ZONE
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.time.LocalDateTime | Y | Y | Default |
| java.sql.Timestamp | Y | Y | N |
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
TIMESTAMP WITH TIME ZONE
TIMESTAMP(p) WITH TIME ZONE
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.time.OffsetDateTime | Y | Y | Default. |
| java.time.ZonedDateTime | Y | N | Ignores the zone ID. |
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
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP(p) WITH LOCAL TIME ZONE
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.time.Instant | Y | Y | Default. The time is returned in UTC, adjusted from the local timezone. |
| java.lang.Integer | Y | Y | Describes the number of seconds since epoch. The time is returned in UTC, adjusted from the local timezone. |
| int | Y | (Y) | Describes the number of seconds since epoch. The time is returned in UTC, adjusted from the local timezone. Output only if not nullable. |
| java.lang.Long | Y | Y | Describes the number of milliseconds since epoch. The time is returned in UTC, adjusted from the local timezone. |
| long | Y | (Y) | Describes the number of milliseconds since epoch. The time is returned in UTC, adjusted from the local timezone. Output only if not nullable. |
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
INTERVAL YEAR
INTERVAL YEAR(p)
INTERVAL YEAR(p) TO MONTH
INTERVAL 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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.time.Period | Y | Y | Ignores the day part. Default |
| java.lang.Integer | Y | Y | Describes the number of months. |
| int | Y | (Y) | Describes the number of months. Output only if not nullable. |
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
INTERVAL DAY
INTERVAL DAY(p1)
INTERVAL DAY(p1) TO HOUR
INTERVAL DAY(p1) TO MINUTE
INTERVAL DAY(p1) TO SECOND(p2)
INTERVAL HOUR
INTERVAL HOUR TO MINUTE
INTERVAL HOUR TO SECOND(p2)
INTERVAL MINUTE
INTERVAL MINUTE TO SECOND(p2)
INTERVAL SECOND
INTERVAL 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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.time.Duration | Y | Y | Default |
| java.lang.Integer | Y | Y | Describes the number of milliseconds. |
| int | Y | (Y) | Describes the number of milliseconds. Output only if not nullable. |
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
ARRAY<t>
t 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>.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| t[] | (Y) | (Y) | Depends on the subtype. Default |
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
MAP<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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.util.Map<kt, vt> | Y | Y | Default |
| subclass of java.util.Map<kt, vt> | Y | N | N |
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
MULTISET<t>
t 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>.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.util.Map<kt, java.lang.Integer> | Y | Y | Assigns each value to an integer multiplicity. Default |
| subclass of java.util.Map<kt, java.lang.Integer> | Y | N | N |
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
ROW<n0 t0, n1 t1, ...>
ROW<n0 t0 'd0', n1 t1 'd1', ...>
ROW(n0 t0, n1 t1, ...>
ROW(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>.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| org.apache.flink.types.Row | Y | Y | Default |
Other
BOOLEAN
Data type of a boolean with a (possibly) three-valued logic of TRUE, FALSE, and UNKNOWN.
Declaration
BOOLEAN
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.Boolean | Y | Y | Default |
| boolean | Y | (Y) | To JVM only if type is not nullable. |
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
RAW('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.
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| class | Y | Y | Originating class or subclass (for input) or superclass (for output). Default |
byte[] | N | Y | To JVM only if type is not nullable. |
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
NULL
| Java Type | From JVM | To JVM | Remarks |
|---|---|---|---|
| java.lang.Object | Y | Y | Default |
| any class | N | (Y) | Any non-primitive type. |
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:
public class PlusOne extends ScalaFunction {
public Long eval(Long number) {
return number + 1
}
}
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.