SQL Analytics data types
Supported data types
SQL Analytics supports the following data types:
- Numeric types
- ByteType: Represents 1-byte signed integer numbers. The range of numbers is from
-128
to127
. - ShortType: Represents 2-byte signed integer numbers. The range of numbers is from
-32768
to32767
. - IntegerType: Represents 4-byte signed integer numbers. The range of numbers is from
-2147483648
to2147483647
. - LongType: Represents 8-byte signed integer numbers. The range of numbers is from
-9223372036854775808
to9223372036854775807
. - FloatType: Represents 4-byte single-precision floating point numbers.
- DoubleType: Represents 8-byte double-precision floating point numbers.
- DecimalType: Represents arbitrary-precision signed decimal numbers. Backed internally by
java.math.BigDecimal
. ABigDecimal
consists of an arbitrary precision integer unscaled value and a 32-bit integer scale.
- ByteType: Represents 1-byte signed integer numbers. The range of numbers is from
- String type:
- StringType: Represents character string values.
- Binary type:
- BinaryType: Represents byte sequence values.
- Boolean type:
- BooleanType: Represents Boolean values.
- Datetime types
- TimestampType: Represents values comprising values of fields year, month, day, hour, minute, and second, with the session local time-zone. The timestamp value represents an absolute point in time.
- DateType: Represents values comprising values of fields year, month and day, without a time-zone.
- Complex types
- ArrayType(elementType, containsNull): Represents values comprising a sequence of elements with the type of elementType.
containsNull
indicates if elements in a ArrayType value can havenull
values. - MapType(keyType, valueType, valueContainsNull): Represents values comprising a set of key-value pairs. The data type of keys is described by keyType and the data type of values is described by valueType. For a MapType value, keys are not allowed to have
null
values.valueContainsNull
indicates if values of a MapType value can havenull
values. - StructType(fields): Represents values with the structure described by a sequence of StructFields (fields).
- StructField(name, dataType, nullable): Represents a field in a StructType. The name of a field is indicated by
name
. The data type of a field is indicated by dataType.nullable
indicates if values of these fields can havenull
values.
- StructField(name, dataType, nullable): Represents a field in a StructType. The name of a field is indicated by
- ArrayType(elementType, containsNull): Represents values comprising a sequence of elements with the type of elementType.
Language mapping
The following table shows the type names as well as aliases used in SQL Analytics parser for each data type.
Data type | SQL name |
---|---|
BooleanType | BOOLEAN |
ByteType | BYTE, TINYINT |
ShortType | SHORT, SMALLINT |
IntegerType | INT, INTEGER |
LongType | LONG, BIGINT |
FloatType | FLOAT, REAL |
DoubleType | DOUBLE |
DateType | DATE |
TimestampType | TIMESTAMP |
StringType | STRING |
BinaryType | BINARY |
DecimalType | DECIMAL, DEC, NUMERIC |
CalendarIntervalType | INTERVAL |
ArrayType | ARRAY<element_type> |
StructType | STRUCT<field1_name: field1_type, field2_name: field2_type, …> |
MapType | MAP<key_type, value_type> |
Special floating point values
SQL Analytics supports several special floating point values in a case-insensitive manner:
- Inf, +Inf, Infinity, +Infinity: positive infinity
- -Inf, -Infinity: negative infinity
- NaN: not a number
Positive and negative infinity semantics
There is special handling for positive and negative infinity. They have the following semantics:
- Positive infinity multiplied by any positive value returns positive infinity.
- Negative infinity multiplied by any positive value returns negative infinity.
- Positive infinity multiplied by any negative value returns negative infinity.
- Negative infinity multiplied by any negative value returns positive infinity.
- Positive/negative infinity multiplied by 0 returns NaN.
- Positive/negative infinity is equal to itself.
- In aggregations, all positive infinity values are grouped together. Similarly, all negative infinity values are grouped together.
- Positive infinity and negative infinity are treated as normal values in join keys.
- Positive infinity sorts lower than NaN and higher than any other values.
- Negative infinity sorts lower than any other values.
NaN semantics
There is special handling for NaN when dealing with float
or double
types that
do not exactly match standard floating point semantics. Specifically:
- NaN = NaN returns true.
- In aggregations, all NaN values are grouped together.
- NaN is treated as a normal value in join keys.
- NaN values go last when in ascending order, larger than any other numeric value.
Examples
SELECT double('infinity') AS col;
+--------+
| col|
+--------+
|Infinity|
+--------+
SELECT float('-inf') AS col;
+---------+
| col|
+---------+
|-Infinity|
+---------+
SELECT float('NaN') AS col;
+---+
|col|
+---+
|NaN|
+---+
SELECT double('infinity') * 0 AS col;
+---+
|col|
+---+
|NaN|
+---+
SELECT double('-infinity') * (-1234567) AS col;
+--------+
| col|
+--------+
|Infinity|
+--------+
SELECT double('infinity') < double('NaN') AS col;
+----+
| col|
+----+
|true|
+----+
SELECT double('NaN') = double('NaN') AS col;
+----+
| col|
+----+
|true|
+----+
SELECT double('inf') = double('infinity') AS col;
+----+
| col|
+----+
|true|
+----+
CREATE TABLE test (c1 int, c2 double);
INSERT INTO test VALUES (1, double('infinity'));
INSERT INTO test VALUES (2, double('infinity'));
INSERT INTO test VALUES (3, double('inf'));
INSERT INTO test VALUES (4, double('-inf'));
INSERT INTO test VALUES (5, double('NaN'));
INSERT INTO test VALUES (6, double('NaN'));
INSERT INTO test VALUES (7, double('-infinity'));
SELECT COUNT(*), c2 FROM test GROUP BY c2;
+---------+---------+
| count(1)| c2|
+---------+---------+
| 2| NaN|
| 2|-Infinity|
| 3| Infinity|
+---------+---------+