Data Types

BigQuery supports several data types, some of which are standard (e.g. STRING, INT64), and others that are more complex (e.g. ARRAY, STRUCT).

In general though they easily fall into the following categories or Supertypes:

1. Numeric

1. INT64

1. FLOAT64

1. NUMERIC & BIGNUMERIC

1. Boolean

1. BOOL

1. String

1. STRING

1. Bytes

1. BYTES

1. Date/Time

1. DATE

1. DATETIME

1. TIMESTAMP

1. TIME

1. Array

1. ARRAY

1. Struct

1. STRUCT

1. Geography

1. GEOGRAPHY

SELECT cast('12' as INT64) int

• Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other INT64s

• CAST to: BOOL, INT64, NUMERIC, BIGNUMERIC, FLOAT64, STRING

• Coercion To*: NUMERIC, BIGNUMERIC, FLOAT64

SELECT cast('12.44' as FLOAT64) float

• There are non-numeric values to consider: NaN and +/-inf

• The order of values from least to greatest is:

• NULL

• NaN

• -inf

• Negative numbers

• 0

• Positive numbers

• +inf

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, but NOT PARTITION BY

• ✅ Can be compared to other FLOAT64s

• CAST to: INT64, NUMERIC, BIGNUMERIC, FLOAT64, STRING

• Coercion To*: N/A

SELECT cast('12.4E17' as NUMERIC) numeric, cast('12.4E37' as BIGNUMERIC) bignumeric

• Often used for financial calculations

• NUMERIC

• Precision: 38

• Scale: 0

• Min : -9.9999999999999999999999999999999999999E+29

• Max: 9.9999999999999999999999999999999999999E+29

• BIGNUMERIC

• Precision: 76.76 (the 77th digit is partial)

• Scale: 38

• Min:-5.7896044618658097711785492504343953926634992332820282019728792003956564819968E+38

• Max:5.7896044618658097711785492504343953926634992332820282019728792003956564819968E+38

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other NUMERICSs and BIGNUMERICs

• CAST to: INT64, NUMERIC, BIGNUMERIC, FLOAT64, STRING

• Coercion To*:BIGNUMERIC, FLOAT64

SELECT
  CAST('True' AS BOOL) AS boolean

• Represented by keywords: TRUE and FALSE

• They are case insensitive

• Boolean values are sorted from least to greatest:

• NULL

• FALSE

• TRUE

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other BOOLs

• CAST to: INT64, BOOL, STRING

• Coercion To*: N/A

SELECT
  CAST(123 AS STRING) AS string

• Uses UTF-8 encoding

• All STRING functions operate on Unicode characters rather than bytes

• Most STRING functions are also defined on BYTES

• Casting from STRING-> BYTES does UTF-8 encoding and decoding

• BYTES -> STRING will return in error if BYTES are not valid UTF-8

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other STRINGs

• CAST to: BOOL, INT64, NUMERIC, BIGNUMERIC, FLOAT64,STRING, BYTES, DATE, DATETIME, TIME, TIMESTAMP

• Coercion To*: N/A

SELECT cast('hi' as BYTES) bytes

• Most STRING functions are also defined on BYTES

• Casting from STRING-> BYTES does UTF-8 encoding and decoding

• BYTES -> STRING will return in error if BYTES are not valid UTF-8

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other BYTEs

• CAST to: STRING, BYTES

• Coercion To*: N/A

SELECT
  CAST('2020-08-24' AS DATE) AS date

• Independent of time zone.

• Represents a calendar day, not a specific 24 hour window.

• Range: 0001-01-01 to 9999-12-31.

• Format: YYYY:[M]M:[D]D or 4-digit year : one or two digit month : one or two digit day

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other DATEs

• CAST to: STRING, DATE, DATETIME, TIMESTAMP

• Coercion To*: N/A

SELECT
  CAST('2020-08-24 13:11:05' AS DATETIME) AS datetime

• Independent of time zone.

• Represents a date and time as they would be displayed on a calendar or clock, not a specific 24 hour window.

• Range: 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999

• Format: YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]]

• 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)

• [.DDDDDD]: Up to six fractional digits (microsecond precision)

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other DATETIMEs

• CAST to: STRING, DATE, DATETIME, TIMESTAMP, TIME

• Coercion To*: N/A

SELECT
  CAST('2020-08-24 13:11:05+5:00' AS TIMESTAMP) AS timestamp

• Independent of time zone.

• Represents a date and time as they would be displayed on a calendar or clock, not a specific 24 hour window.

• Range: 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 UTC

• Format: YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][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)

• [.DDDDDD]: Up to six fractional digits (microsecond precision)

• [time zone]: String representing the time zone. When a time zone is not explicitly specified, the default time zone, UTC, is used. See the time zones section for details.

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other TIMESTAMPs

• CAST to: STRING, DATE, DATETIME, TIMESTAMP, TIME

• Coercion To*: N/A

SELECT
  CAST('13:11:05' AS TIME) AS time

• Independent of a specific date.

• Independent of a timezone.

• Range: 00:00:00 to 23:59:59.99999.

• Format: [H]H:[M]M:[S]S[.DDDDDD]

• [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)

• [.DDDDDD]: Up to six fractional digits (microsecond precision)

• ✅ Can be NULL

• ✅ Can be used in an ORDER BY clause

• ✅ Can be used in a GROUP BY , DISTINCT, PARTITION BY

• ✅ Can be compared to other TIME's

• CAST to: STRING, TIME

• Coercion To*: N/A

SELECT CAST([1,2,3] as ARRAY<INT64>) array_

• ARRAYs of ARRAYs are not allowed (use STRUCT instead)

• There are currently 2 limitations with respect to NULLs and ARRAYs:

• BigQuery raises an error if a query result contains ARRAYs which contain NULL elements, although such ARRAYs can be used inside the query.

• BigQuery translates NULL into an empty ARRAY in the query result, although inside the query NULL and empty ARRAYs are two distinct values.

• Declaring an ARRAY type: ARRAY<T>, where the type of elements in the ARRAY are contained inside the < and >.

• ARRAY<INT64> = Simple ARRAY of integers.

• ARRAY<STRUCT<INT64,INT64>> : ARRAY of STRUCTs that contain 2 integers.

• 🚫 Cannot be NULL

• 🚫 Cannot be used in an ORDER BY clause

• 🚫 Cannot be used in a GROUP BY , DISTINCT, PARTITION BY

• 🚫 Can be compared to other ARRAYs

• CAST to: ARRAY

• Coercion To*: N/A

SELECT CAST((1,'a') as STRUCT<INT64,STRING>) struct_

• Declaring a STRUCT type: STRUCT<T>, where the type of elements in the STRUCT are contained inside the < and >.

• STRUCT<INT64> = Simple STRUCT of integers.

• 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.

• Constructing a STRUCT: (expr1, expr2 [, ... ])

• There must be at least 2 expressions specified, otherwise it's indistinguishable from the element that is defined

• If column names are used(e.g. (x, x+y)), the data types are derived from the column types

• Comparing STRUCTs:

• You can use any of the following operators:

• Equal (=)

• Not Equal (!= or <>)

• [NOT] IN

• These compare pairwise in ordinal order so you cannot be sure that you're comparing the same fields. To do that, compare the fields directly.

• ✅ Can be NULL

• 🚫 Cannot be used in an ORDER BY clause

• 🚫 Cannot be used in a GROUP BY , DISTINCT, PARTITION BY

• 🟡 LIMITED comparisons to other STRUCTs

• CAST to: STRUCT

• Coercion To*: N/A

SELECT CAST(ST_GEOGFROMTEXT('POINT(1 1)') as GEOGRAPHY) geography

• Based on the OGC Simple Features specification (SFS).

• A GEOGRAPHY is the result of, or an argument to, a Geography Function.

• To compare GEOGRAPHY values, use ST_Equals

• ✅ Can be NULL

• 🚫 Cannot be used in an ORDER BY clause

• 🚫 Cannot be used in a GROUP BY , DISTINCT, PARTITION BY

• 🟡 LIMITED comparisons to other GEOGRAPHYs

Coercion is an implicit type conversion, which BigQuery performs automatically between certain data types.