Data Types

Supported Data Types

Spark SQL and DataFrames support the following data types:

• Numeric types
  • ByteType: Represents 1-byte signed integer numbers. The range of numbers is from -128 to 127.
  • ShortType: Represents 2-byte signed integer numbers. The range of numbers is from -32768 to 32767.
  • IntegerType: Represents 4-byte signed integer numbers. The range of numbers is from -2147483648 to 2147483647.
  • LongType: Represents 8-byte signed integer numbers. The range of numbers is from -9223372036854775808 to 9223372036854775807.
  • 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. A BigDecimal consists of an arbitrary precision integer unscaled value and a 32-bit integer scale.
• String type
  • StringType: Represents character string values.
• Binary type
  • BinaryType: Represents byte sequence values.
• Boolean type
  • BooleanType: Represents boolean values.
• Datetime type
  • 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 is used to indicate if elements in a ArrayType value can have null 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 is used to indicate if values of a MapType value can have null 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 is used to indicate if values of these fields can have null values.

import org.apache.spark.sql.types._

from pyspark.sql.types import *

Floating Point Special Values

Spark SQL supports several special floating point values in a case-insensitive manner:

• Inf/+Inf/Infinity/+Infinity: positive infinity
  • FloatType: equivalent to Scala Float.PositiveInfinity.
  • DoubleType: equivalent to Scala Double.PositiveInfinity.
• -Inf/-Infinity: negative infinity
  • FloatType: equivalent to Scala Float.NegativeInfinity.
  • DoubleType: equivalent to Scala Double.NegativeInfinity.
• NaN: not a number
  • FloatType: equivalent to Scala Float.NaN.
  • DoubleType: equivalent to Scala Double.NaN.

Positive/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 not-a-number (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|
+---------+---------+