Data Types in BigQuery
BigQuery supports all common data types found in Standard SQL. Google Cloud has verbose documentation, but here it is presented short and sweet:
Most Common
Name | Description | Storage Size | Note |
---|---|---|---|
INT64 (Integer) | A whole number that is not a fraction. | 8 bytes | Range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
NUMERIC | A number with 38 digits of precision, 9 of which can be after the decimal point. | 16 bytes | Range from -99999999999999999999999999999.999999999 to 99999999999999999999999999999.999999999 |
FLOAT64 (Float) | Double precision (approximate) decimal values. | 8 bytes | Wikipedia will take this one. |
BOOL (Boolean) | Represented by keywords TRUE and FALSE | Case insensitive so TRUE or false will work. | |
STRING | Variable-length character (Unicode) data. | Must be quoted with either single (') or double (") quotation marks. Alternatively, can be triple-quoted with groups of three single (''') or three double (""") quotation marks. | |
BYTES | Variable-length binary data. | Not to be used interchangeably with STRING. | |
BYTES operates on raw bytes rather than Unicode characters. | |||
ARRAY | Ordered list of zero or more elements of any non-ARRAY type. |
Time Data Types
Name | Description | Canonical Format | Note |
---|---|---|---|
DATE | Represents a logical calendar date, without time. | YYYY-[M]M-[D]D | BYTES operates on raw bytes rather than Unicode characters. Range from 0001-01-01 to 9999-12-31 |
TIME | Represents a time, independent of a specific date. | [H]H:[M]M:[S]S[.DDDDDD] | Range from 00undefined00 to 23undefined59.999999 |
DATETIME | Represents a year, month, day, hour, minute, second, and subsecond without a timezone. † | YYYY-[M]M-[D]D[(|T)[H]H:[M]M:[S]S[.DDDDDD]] | Range from 0001-01-01 00undefined00 to 9999-12-31 23undefined59.999999 |
TIMESTAMP | Represents an absolute point in time, with microsecond precision with a timezone. | YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone] | Range from 0001-01-01 00undefined00 to 9999-12-31 23undefined59.999999 |
† DATETIME
is seldom used, as it's rare to wish to omit the timezone.
How to read Canonical Format:
YYYY
: Four-digit year[M]M
: One or two digit month[D]D
: One or two digit day( |T)
: A space or aT
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, with two canonical formats:- Time zone name per the tz database
- Offset from Coordinated Universal Time (UTC), or the letter Z for UTC
Less Common
Name | Description | Note |
---|---|---|
STRUCT | A container of ordered fields, each with a type (required) and field name (optional). | Learn more about STRUCT |
GEOGRAPHY | A collection of points, lines, and polygons, which is represented as a point set, or a subset of the surface of the Earth. | Based on the Open Geospatial Consortium’s (OGC) Simple Features specification (SFS) |