Dataflow SQL data types

Cloud Dataflow supports simple data types such as integers, as well as more complex types such as ARRAY and STRUCT. This page provides an overview of each data type, including allowed values. For information on data type literals and constructors, see Lexical Structure and Syntax.

Data type properties

The following table contains data type properties and the data types that each property applies to:

Property Description Applies To
Nullable NULL is a valid value. All data types, with the following exceptions:
  • ARRAYs cannot be NULL.
  • NULL ARRAY elements cannot persist to a table.
  • Queries cannot handle NULL ARRAY elements.
Orderable Can be used in an ORDER BY clause. All data types except for:
  • ARRAY
  • STRUCT
Groupable Can generally appear in an expression following
GROUP BY, DISTINCT, or PARTITION BY.
However, PARTITION BY expressions cannot include
the floating point types FLOAT64 and DOUBLE.


All data types except for:
  • ARRAY
  • STRUCT
  • FLOAT64
Comparable Values of the same type can be compared to each other. All data types, with the following exceptions: ARRAY comparisons are not supported.

Equality comparisons for STRUCTs are supported field by field, in field order. Field names are ignored. Less than and greater than comparisons are not supported.



All types that support comparisons can be used in a JOIN condition. See JOIN Types for an explanation of join conditions.

Numeric types

Numeric types include integer types and floating point types.

Integer type

Integers are numeric values that do not have fractional components.

Name Storage Size Range
INT64 8 bytes -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Decimal type

Decimal type values are numeric values with fixed decimal precision and scale. Precision is the number of digits that the number contains. Scale is how many of these digits appear after the decimal point.

This type can represent decimal fractions exactly, and is suitable for financial calculations.

Name Precision, Scale, and Range
NUMERIC
DECIMAL
Precision: 38
Scale: 9
Min: -9.9999999999999999999999999999999999999E+28
Max: 9.9999999999999999999999999999999999999E+28

Floating point type

Floating point values are approximate numeric values with fractional components.

Name Storage Size Description
FLOAT64 8 bytes Double precision (approximate) decimal values.

Boolean type

Name Description
BOOL Boolean values are represented by the keywords TRUE and FALSE (case insensitive).

String type

Name Description
STRING Variable-length character (Unicode) data.

Input STRING values must be UTF-8 encoded and output STRING values will be UTF-8 encoded. Alternate encodings like CESU-8 and Modified UTF-8 are not treated as valid UTF-8.

All functions and operators that act on STRING values operate on Unicode characters rather than bytes. For example, when functions like SUBSTR and LENGTH are applied to STRING input, the functions count Unicode characters, not bytes. Comparisons are defined on Unicode characters. Comparisons for less than and ORDER BY compare character by character, and lower unicode code points are considered lower characters.

Bytes type

Name Description
BYTES Variable-length binary data.

STRING and BYTES are separate types that cannot be used interchangeably. Casts between STRING and BYTES enforce that the bytes are encoded using UTF-8.

Date type

Name Description Range
DATE Represents a logical calendar date, independent of time zone. 0001-01-01 to 9999-12-31.

A date represents a logical calendar date, independent of time zone. A date value does not represent a specific 24-hour time period. Rather, a given date value represents a different 24-hour period when interpreted in different time zones, and might represent a shorter or longer day during Daylight Savings Time transitions. To represent an absolute point in time, use a timestamp.

Canonical format

YYYY-[M]M-[D]D
  • YYYY: Four-digit year
  • [M]M: One or two digit month
  • [D]D: One or two digit day

Datetime type

Name Description Range
DATETIME Represents a date and time, as they might be displayed on a calendar or clock, independent of time zone. 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999999

A datetime object represents a date and time, as they might be displayed on a calendar or clock, independent of time zone. It includes the year, month, day, hour, minute, second, and subsecond. The range of subsecond precision is determined by the SQL engine. To represent an absolute point in time, use a timestamp.

Canonical format

YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.F]]
  • YYYY: Four-digit year
  • [M]M: One or two digit month
  • [D]D: One or two digit day
  • ( |T): A space or a T separator
  • [H]H: One or two digit hour (valid values from 00 to 23)
  • [M]M: One or two digit minutes (valid values from 00 to 59)
  • [S]S: One or two digit seconds (valid values from 00 to 59)
  • [.F]: Up to nine fractional digits (nanosecond precision)

Time type

Name Description Range
TIME Represents a time, as might be displayed on a watch, independent of a specific date and time zone. 00:00:00 to 23:59:59.999999999

A time object represents a time, as might be displayed on a watch, independent of a specific date and timezone. The range of subsecond precision is determined by the SQL engine. To represent an absolute point in time, use a timestamp.

Canonical format

[H]H:[M]M:[S]S[.DDDDDD|.F]
  • [H]H: One or two digit hour (valid values from 00 to 23)
  • [M]M: One or two digit minutes (valid values from 00 to 59)
  • [S]S: One or two digit seconds (valid values from 00 to 59)
  • [.F]: Up to nine fractional digits (nanosecond precision)

Timestamp type

Name Description Range
TIMESTAMP Represents an absolute point in time, with millisecond precision. 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999 UTC.

A timestamp represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time.

Canonical format

YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDD]][time zone]
  • YYYY: Four-digit year
  • [M]M: One or two digit month
  • [D]D: One or two digit day
  • ( |T): A space or a T separator
  • [H]H: One or two digit hour (valid values from 00 to 23)
  • [M]M: One or two digit minutes (valid values from 00 to 59)
  • [S]S: One or two digit seconds (valid values from 00 to 59)
  • [.DDD]: Up to three fractional digits (i.e. up to millisecond precision)
  • [time zone]: String representing the time zone. See the time zones section for details.

Time zones are used when parsing timestamps or formatting timestamps for display. The timestamp value itself does not store a specific time zone. A string-formatted timestamp may include a time zone. When a time zone is not explicitly specified, the default time zone, UTC, is used.

Time zones

Time zones are represented by strings in one of these two canonical formats:

  • Offset from Coordinated Universal Time (UTC), or the letter Z for UTC
  • Time zone name from the tz database

Offset from Coordinated Universal Time (UTC)

Offset Format
(+|-)H[H][:M[M]]
Z
Examples
-08:00
-8:15
+3:00
+07:30
-7
Z

When using this format, no space is allowed between the time zone and the rest of the timestamp.

2014-09-27 12:30:00.45-8:00
2014-09-27T12:30:00.45Z

Time zone name

Time zone names are from the tz database. For a less comprehensive but simpler reference, see the List of tz database time zones on Wikipedia.

Format
continent/[region/]city
Examples
America/Los_Angeles
America/Argentina/Buenos_Aires

When using a time zone name, a space is required between the name and the rest of the timestamp:

2014-09-27 12:30:00.45 America/Los_Angeles

Note that not all time zone names are interchangeable even if they do happen to report the same time during a given part of the year. For example, America/Los_Angeles reports the same time as UTC-7:00 during Daylight Savings Time, but reports the same time as UTC-8:00 outside of Daylight Savings Time.

If a time zone is not specified, the default time zone value is used.

Leap seconds

A timestamp is simply an offset from 1970-01-01 00:00:00 UTC, assuming there are exactly 60 seconds per minute. Leap seconds are not represented as part of a stored timestamp.

If your input contains values that use ":60" in the seconds field to represent a leap second, that leap second is not preserved when converting to a timestamp value. Instead that value is interpreted as a timestamp with ":00" in the seconds field of the following minute.

Leap seconds do not affect timestamp computations. All timestamp computations are done using Unix-style timestamps, which do not reflect leap seconds. Leap seconds are only observable through functions that measure real-world time. In these functions, it is possible for a timestamp second to be skipped or repeated when there is a leap second.

Array type

Name Description
ARRAY Ordered list of zero or more elements of any non-ARRAY type.

An ARRAY is an ordered list of zero or more elements of non-ARRAY values. ARRAYs of ARRAYs are not allowed. Queries that would produce an ARRAY of ARRAYs will return an error. Instead a STRUCT must be inserted between the ARRAYs using the SELECT AS STRUCT construct.

An empty ARRAY and a NULL ARRAY are two distinct values. ARRAYs can contain NULL elements.

Declaring an ARRAY type

ARRAY types are declared using the angle brackets (< and >). The type of the elements of an ARRAY can be arbitrarily complex with the exception that an ARRAY cannot directly contain another ARRAY.

Format

ARRAY<T>

Examples

Type Declaration Meaning
ARRAY<INT64> Simple ARRAY of 64-bit integers.
ARRAY<STRUCT<INT64, INT64>> An ARRAY of STRUCTs, each of which contains two 64-bit integers.
ARRAY<ARRAY<INT64>>
(not supported)
This is an invalid type declaration which is included here just in case you came looking for how to create a multi-level ARRAY. ARRAYs cannot contain ARRAYs directly. Instead see the next example.
ARRAY<STRUCT<ARRAY<INT64>>> An ARRAY of ARRAYS of 64-bit integers. Notice that there is a STRUCT between the two ARRAYs because ARRAYs cannot hold other ARRAYs directly.

Struct type

Name Description
STRUCT Container of ordered fields each with a type (required) and field name (optional).

Declaring a STRUCT type

STRUCT types are declared using the angle brackets (< and >). The type of the elements of a STRUCT can be arbitrarily complex.

Format

STRUCT<T>

Examples

Type Declaration Meaning
STRUCT<INT64> Simple STRUCT with a single unnamed 64-bit integer field.
STRUCT<x STRUCT<y INT64, z INT64>> A STRUCT with a nested STRUCT named x inside it. The STRUCT x has two fields, y and z, both of which are 64-bit integers.
STRUCT<inner_array ARRAY<INT64>> A STRUCT containing an ARRAY named inner_array that holds 64-bit integer elements.

Limited comparisons for STRUCT

STRUCTs can be directly compared using equality operators:

  • Equal (=)
  • Not Equal (!= or <>)
  • [NOT] IN

Notice, though, that these direct equality comparisons compare the fields of the STRUCT pairwise in ordinal order ignoring any field names. If instead you want to compare identically named fields of a STRUCT, you can compare the individual fields directly.