Skip to main content
Documentation
Technology areas
close
AI and ML
Application development
Application hosting
Compute
Data analytics and pipelines
Databases
Distributed, hybrid, and multicloud
Generative AI
Industry solutions
Networking
Observability and monitoring
Security
Storage
Cross-product tools
close
Access and resources management
Costs and usage management
Google Cloud SDK, languages, frameworks, and tools
Infrastructure as code
Migration
Related sites
close
Google Cloud Home
Free Trial and Free Tier
Architecture Center
Blog
Contact Sales
Google Cloud Developer Center
Google Developer Center
Google Cloud Marketplace
Google Cloud Marketplace Documentation
Google Cloud Skills Boost
Google Cloud Solution Center
Google Cloud Support
Google Cloud Tech Youtube Channel
/
English
Deutsch
Español – América Latina
Français
Português – Brasil
中文 – 简体
日本語
한국어
Console
Sign in
BigQuery
Guides
Reference
Samples
Resources
Contact Us
Start free
Documentation
Guides
Reference
Samples
Resources
Technology areas
More
Cross-product tools
More
Related sites
More
Console
Contact Us
Start free
Quotas and limits
Quotas and limits reference
Troubleshoot quota errors
BigQuery command-line tool
bq command-line tool reference
SQL in BigQuery
GoogleSQL reference
Query syntax
Pipe syntax
General reference
Data types
Lexical structure and syntax
Conversion rules
Format elements
Collation
Text analysis
Expressions
Function calls
Aggregate function calls
Window function calls
Operators
Conditional expressions
Subqueries
Functions
All functions
AEAD encryption functions
Overview
AEAD.DECRYPT_BYTES
AEAD.DECRYPT_STRING
AEAD.ENCRYPT
DETERMINISTIC_DECRYPT_BYTES
DETERMINISTIC_DECRYPT_STRING
DETERMINISTIC_ENCRYPT
KEYS.ADD_KEY_FROM_RAW_BYTES
KEYS.KEYSET_CHAIN
KEYS.KEYSET_FROM_JSON
KEYS.KEYSET_LENGTH
KEYS.KEYSET_TO_JSON
KEYS.NEW_KEYSET
KEYS.NEW_WRAPPED_KEYSET
KEYS.REWRAP_KEYSET
KEYS.ROTATE_KEYSET
KEYS.ROTATE_WRAPPED_KEYSET
Aggregate functions
Overview
ANY_VALUE
ARRAY_AGG
ARRAY_CONCAT_AGG
AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNTIF
GROUPING
LOGICAL_AND
LOGICAL_OR
MAX
MAX_BY
MIN
MIN_BY
STRING_AGG
SUM
Approximate aggregate functions
Overview
APPROX_COUNT_DISTINCT
APPROX_QUANTILES
APPROX_TOP_COUNT
APPROX_TOP_SUM
Array functions
Overview
ARRAY
ARRAY_CONCAT
ARRAY_FIRST
ARRAY_LAST
ARRAY_LENGTH
ARRAY_REVERSE
ARRAY_SLICE
ARRAY_TO_STRING
GENERATE_ARRAY
GENERATE_DATE_ARRAY
GENERATE_TIMESTAMP_ARRAY
Bit functions
Overview
BIT_COUNT
Conversion functions
Overview
CAST
PARSE_BIGNUMERIC
PARSE_NUMERIC
SAFE_CAST
Date functions
Overview
CURRENT_DATE
DATE
DATE_ADD
DATE_DIFF
DATE_FROM_UNIX_DATE
DATE_SUB
DATE_TRUNC
EXTRACT (date)
FORMAT_DATE
LAST_DAY (date)
PARSE_DATE
UNIX_DATE
Datetime functions
Overview
CURRENT_DATETIME
DATETIME
DATETIME_ADD
DATETIME_DIFF
DATETIME_SUB
DATETIME_TRUNC
EXTRACT (datetime)
FORMAT_DATETIME
LAST_DAY (datetime)
PARSE_DATETIME
Debugging functions
Overview
ERROR
Differentially private aggregate functions
Overview
AVG (DIFFERENTIAL_PRIVACY)
COUNT (DIFFERENTIAL_PRIVACY)
PERCENTILE_CONT (DIFFERENTIAL_PRIVACY)
SUM (DIFFERENTIAL_PRIVACY)
Federated query functions
Overview
EXTERNAL_QUERY
DLP encryption functions
Overview
DLP_DETERMINISTIC_ENCRYPT
DLP_DETERMINISTIC_DECRYPT
DLP_KEY_CHAIN
Geography functions
Overview
ST_ANGLE
ST_AREA
ST_ASBINARY
ST_ASGEOJSON
ST_ASTEXT
ST_AZIMUTH
ST_BOUNDARY
ST_BOUNDINGBOX
ST_BUFFER
ST_BUFFERWITHTOLERANCE
ST_CENTROID
ST_CENTROID_AGG
ST_CLOSESTPOINT
ST_CLUSTERDBSCAN
ST_CONTAINS
ST_CONVEXHULL
ST_COVEREDBY
ST_COVERS
ST_DIFFERENCE
ST_DIMENSION
ST_DISJOINT
ST_DISTANCE
ST_DUMP
ST_DWITHIN
ST_ENDPOINT
ST_EQUALS
ST_EXTENT
ST_EXTERIORRING
ST_GEOGFROM
ST_GEOGFROMGEOJSON
ST_GEOGFROMTEXT
ST_GEOGFROMWKB
ST_GEOGPOINT
ST_GEOGPOINTFROMGEOHASH
ST_GEOHASH
ST_GEOMETRYTYPE
ST_HAUSDORFFDISTANCE
ST_HAUSDORFFDWITHIN
ST_INTERIORRINGS
ST_INTERSECTION
ST_INTERSECTS
ST_INTERSECTSBOX
ST_ISCLOSED
ST_ISCOLLECTION
ST_ISEMPTY
ST_ISRING
ST_LENGTH
ST_LINEINTERPOLATEPOINT
ST_LINELOCATEPOINT
ST_LINESUBSTRING
ST_MAKELINE
ST_MAKEPOLYGON
ST_MAKEPOLYGONORIENTED
ST_MAXDISTANCE
ST_NPOINTS
ST_NUMGEOMETRIES
ST_NUMPOINTS
ST_PERIMETER
ST_POINTN
ST_REGIONSTATS
ST_SIMPLIFY
ST_SNAPTOGRID
ST_STARTPOINT
ST_TOUCHES
ST_UNION
ST_UNION_AGG
ST_WITHIN
ST_X
ST_Y
S2_CELLIDFROMPOINT
S2_COVERINGCELLIDS
Hash functions
Overview
FARM_FINGERPRINT
MD5
SHA1
SHA256
SHA512
HyperLogLog++ functions
Overview
HLL_COUNT.EXTRACT
HLL_COUNT.INIT
HLL_COUNT.MERGE
HLL_COUNT.MERGE_PARTIAL
Interval functions
Overview
EXTRACT (interval)
JUSTIFY_DAYS
JUSTIFY_HOURS
JUSTIFY_INTERVAL
MAKE_INTERVAL
JSON functions
Overview
BOOL
FLOAT64
INT64
JSON_ARRAY
JSON_ARRAY_APPEND
JSON_ARRAY_INSERT
JSON_EXTRACT
JSON_EXTRACT_ARRAY
JSON_EXTRACT_SCALAR
JSON_EXTRACT_STRING_ARRAY
JSON_KEYS
JSON_OBJECT
JSON_QUERY
JSON_QUERY_ARRAY
JSON_REMOVE
JSON_SET
JSON_STRIP_NULLS
JSON_TYPE
JSON_VALUE
JSON_VALUE_ARRAY
LAX_BOOL
LAX_FLOAT64
LAX_INT64
LAX_STRING
PARSE_JSON
STRING (JSON)
TO_JSON
TO_JSON_STRING
KLL functions
Overview
KLL_QUANTILES.EXTRACT_INT64
KLL_QUANTILES.EXTRACT_FLOAT64
KLL_QUANTILES.EXTRACT_POINT_INT64
KLL_QUANTILES.EXTRACT_POINT_FLOAT64
KLL_QUANTILES.INIT_INT64
KLL_QUANTILES.INIT_FLOAT64
KLL_QUANTILES.MERGE_INT64
KLL_QUANTILES.MERGE_FLOAT64
KLL_QUANTILES.MERGE_PARTIAL
KLL_QUANTILES.MERGE_POINT_INT64
KLL_QUANTILES.MERGE_POINT_FLOAT64
Mathematical functions
Overview
ABS
ACOS
ACOSH
ASIN
ASINH
ATAN
ATAN2
ATANH
CBRT
CEIL
CEILING
COS
COSH
COSINE_DISTANCE
COT
COTH
CSC
CSCH
DIV
EXP
EUCLIDEAN_DISTANCE
FLOOR
GREATEST
IS_INF
IS_NAN
LEAST
LN
LOG
LOG10
MOD
POW
POWER
RAND
RANGE
RANGE_BUCKET
ROUND
SAFE_ADD
SAFE_DIVIDE
SAFE_MULTIPLY
SAFE_NEGATE
SAFE_SUBTRACT
SEC
SECH
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC
Navigation functions
Overview
FIRST_VALUE
LAG
LAST_VALUE
LEAD
NTH_VALUE
PERCENTILE_CONT
PERCENTILE_DISC
Net functions
Overview
NET.HOST
NET.IP_FROM_STRING
NET.IP_NET_MASK
NET.IP_TO_STRING
NET.IP_TRUNC
NET.IPV4_FROM_INT64
NET.IPV4_TO_INT64
NET.PUBLIC_SUFFIX
NET.REG_DOMAIN
NET.SAFE_IP_FROM_STRING
Numbering functions
Overview
CUME_DIST
DENSE_RANK
NTILE
PERCENT_RANK
RANK
ROW_NUMBER
ObjectRef functions
Overview
OBJ.FETCH_METADATA
OBJ.GET_ACCESS_URL
OBJ.MAKE_REF
Range functions
Overview
GENERATE_RANGE_ARRAY
RANGE
RANGE_CONTAINS
RANGE_END
RANGE_INTERSECT
RANGE_OVERLAPS
RANGE_SESSIONIZE
RANGE_START
Search functions
Overview
SEARCH
VECTOR_SEARCH
Security functions
Overview
SESSION_USER
Statistical aggregate functions
Overview
CORR
COVAR_POP
COVAR_SAMP
STDDEV
STDDEV_POP
STDDEV_SAMP
VAR_POP
VAR_SAMP
VARIANCE
String functions
Overview
ASCII
BYTE_LENGTH
CHAR_LENGTH
CHARACTER_LENGTH
CHR
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_STRING
COLLATE
CONCAT
CONTAINS_SUBSTR
EDIT_DISTANCE
ENDS_WITH
FORMAT
FROM_BASE32
FROM_BASE64
FROM_HEX
INITCAP
INSTR
LEFT
LENGTH
LOWER
LPAD
LTRIM
NORMALIZE
NORMALIZE_AND_CASEFOLD
OCTET_LENGTH
REGEXP_CONTAINS
REGEXP_EXTRACT
REGEXP_EXTRACT_ALL
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SAFE_CONVERT_BYTES_TO_STRING
SOUNDEX
SPLIT
STARTS_WITH
STRPOS
SUBSTR
SUBSTRING
TO_BASE32
TO_BASE64
TO_CODE_POINTS
TO_HEX
TRANSLATE
TRIM
UNICODE
UPPER
Table functions (built-in)
Overview
EXTERNAL_OBJECT_TRANSFORM
Text analysis functions
Overview
BAG_OF_WORDS
TEXT_ANALYZE
TF_IDF
Time functions
Overview
CURRENT_TIME
EXTRACT (time)
FORMAT_TIME
PARSE_TIME
TIME
TIME_ADD
TIME_DIFF
TIME_SUB
TIME_TRUNC
Time series functions
Overview
APPENDS
CHANGES
DATE_BUCKET
DATETIME_BUCKET
GAP_FILL
TIMESTAMP_BUCKET
Timestamp functions
Overview
CURRENT_TIMESTAMP
EXTRACT (timestamp)
FORMAT_TIMESTAMP
PARSE_TIMESTAMP
STRING (timestamp)
TIMESTAMP
TIMESTAMP_ADD
TIMESTAMP_DIFF
TIMESTAMP_MICROS
TIMESTAMP_MILLIS
TIMESTAMP_SECONDS
TIMESTAMP_SUB
TIMESTAMP_TRUNC
UNIX_MICROS
UNIX_MILLIS
UNIX_SECONDS
Utility functions
Overview
GENERATE_UUID
TYPEOF
Vector index functions
Overview
VECTOR_INDEX.STATISTICS
Window functions
Statements
Data definition language (DDL)
Data manipulation language (DML)
Data control language (DCL)
Procedural language
Export statements
Load statements
Debugging statements
BigQuery ML SQL reference
Creating and training models
CREATE MODEL statement overview
Regression and classification
Linear and logistic regression
Boosted trees
Random forest
Deep neural networks
Wide-and-deep networks
AutoML models
Clustering
K-means
Dimensionality reduction
Principal component analysis
Autoencoder
Recommendation
Matrix factorization
Time series forecasting
Univariate forecasting with ARIMA_PLUS models
Multivariate forecasting with ARIMA_PLUS_XREG models
Importing models
Open Neural Network Exchange (ONNX)
TensorFlow
TensorFlow Lite
XGBoost
Remote models
Foundation models
Google models and partner models
Open models
Cloud AI services
Custom models deployed to Vertex AI
Transform-only
Feature engineering
ML.TRANSFORM
ML.FEATURE_INFO
General functions
ML.IMPUTER
Numerical functions
ML.BUCKETIZE
ML.MAX_ABS_SCALER
ML.MIN_MAX_SCALER
ML.NORMALIZER
ML.POLYNOMIAL_EXPAND
ML.QUANTILE_BUCKETIZE
ML.ROBUST_SCALER
ML.STANDARD_SCALER
Categorical functions
ML.FEATURE_CROSS
ML.HASH_BUCKETIZE
ML.LABEL_ENCODER
ML.MULTI_HOT_ENCODER
ML.ONE_HOT_ENCODER
Text functions
ML.NGRAMS
ML.BAG_OF_WORDS
ML.TF_IDF
Image functions
ML.CONVERT_COLOR_SPACE
ML.CONVERT_IMAGE_TYPE
ML.DECODE_IMAGE
ML.RESIZE_IMAGE
Point-in-time lookup functions
ML.FEATURES_AT_TIME
ML.ENTITY_FEATURES_AT_TIME
Augmented analytics
Contribution analysis
ML.GET_INSIGHTS
Hyperparameter tuning functions
ML.TRIAL_INFO
Evaluation functions
ML.EVALUATE
ML.ROC_CURVE
ML.CONFUSION_MATRIX
ML.ARIMA_EVALUATE
ML.TRAINING_INFO
ML.RECONSTRUCTION_LOSS
ML.HOLIDAY_INFO
Inference functions
ML.PREDICT
ML.FORECAST
ML.RECOMMEND
ML.DETECT_ANOMALIES
Generative AI functions
AI.GENERATE_TABLE
AI.FORECAST
ML.GENERATE_TEXT
ML.GENERATE_EMBEDDING
AI.GENERATE
AI.GENERATE_BOOL
AI.GENERATE_DOUBLE
AI.GENERATE_INT
AI functions
ML.UNDERSTAND_TEXT
ML.TRANSLATE
ML.PROCESS_DOCUMENT
ML.TRANSCRIBE
ML.ANNOTATE_IMAGE
AI Explanation functions
ML.EXPLAIN_PREDICT
ML.EXPLAIN_FORECAST
ML.GLOBAL_EXPLAIN
ML.FEATURE_IMPORTANCE
ML.ADVANCED_WEIGHTS
Model weights functions
ML.WEIGHTS
ML.CENTROIDS
ML.PRINCIPAL_COMPONENTS
ML.PRINCIPAL_COMPONENT_INFO
ML.ARIMA_COEFFICIENTS
Model monitoring functions
ML.DESCRIBE_DATA
ML.VALIDATE_DATA_DRIFT
ML.VALIDATE_DATA_SKEW
ML.TFDV_DESCRIBE
ML.TFDV_VALIDATE
Math utility functions
ML.DISTANCE
ML.LP_NORM
Model management statements
EXPORT MODEL statement
ALTER MODEL statement
DROP MODEL statement
INFORMATION SCHEMA views
Introduction
Access control
OBJECT_PRIVILEGES view
BI Engine
BI_CAPACITIES
BI_CAPACITY_CHANGES
Configurations
EFFECTIVE_PROJECT_OPTIONS view
ORGANIZATION_OPTIONS view
ORGANIZATION_OPTIONS_CHANGES view
PROJECT_OPTIONS view
PROJECT_OPTIONS_CHANGES view
Datasets
SCHEMATA view
SCHEMATA_LINKS view
SCHEMATA_OPTIONS view
SHARED_DATASET_USAGE view
SCHEMATA_REPLICAS view
SCHEMATA_REPLICAS_BY_FAILOVER_RESERVATION view
Jobs
JOBS view
JOBS_BY_USER view
JOBS_BY_FOLDER view
JOBS_BY_ORGANIZATION view
Jobs by timeslice
JOBS_TIMELINE view
JOBS_TIMELINE_BY_USER view
JOBS_TIMELINE_BY_FOLDER view
JOBS_TIMELINE_BY_ORGANIZATION view
Recommendations and insights
INSIGHTS view
RECOMMENDATIONS view
RECOMMENDATIONS_BY_ORGANIZATION view
Reservations
ASSIGNMENTS view
ASSIGNMENT_CHANGES view
CAPACITY_COMMITMENTS view
CAPACITY_COMMITMENT_CHANGES view
RESERVATIONS view
RESERVATION_CHANGES view
RESERVATIONS_TIMELINE view
Routines
PARAMETERS view
ROUTINES view
ROUTINE_OPTIONS view
Search indexes
SEARCH_INDEXES view
SEARCH_INDEX_COLUMNS view
SEARCH_INDEX_COLUMN_OPTIONS view
SEARCH_INDEX_OPTIONS view
SEARCH_INDEXES_BY_ORGANIZATION view
Sessions
SESSIONS_BY_PROJECT view
SESSIONS_BY_USER view
Streaming
STREAMING_TIMELINE view
STREAMING_TIMELINE_BY_FOLDER view
STREAMING_TIMELINE_BY_ORGANIZATION view
Tables
COLUMNS view
COLUMN_FIELD_PATHS view
CONSTRAINT_COLUMN_USAGE view
KEY_COLUMN_USAGE view
PARTITIONS view
TABLES view
TABLE_OPTIONS view
TABLE_CONSTRAINTS view
TABLE_SNAPSHOTS view
TABLE_STORAGE view
TABLE_STORAGE_BY_FOLDER view
TABLE_STORAGE_BY_ORGANIZATION view
TABLE_STORAGE_USAGE_TIMELINE view
TABLE_STORAGE_USAGE_TIMELINE_BY_FOLDER view
TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view
Vector indexes
VECTOR_INDEXES view
VECTOR_INDEX_COLUMNS view
VECTOR_INDEX_OPTIONS view
Views
VIEWS view
MATERIALIZED_VIEWS view
Write API
WRITE_API_TIMELINE view
WRITE_API_TIMELINE_BY_FOLDER view
WRITE_API_TIMELINE_BY_ORGANIZATION view
Legacy SQL reference
Migrating to GoogleSQL
Functions and operators
Data types
Querying nested and repeated fields
User-defined functions
Table decorators
BigQuery DataFrames Python API
BigQuery DataFrames
BigQuery APIs
BigQuery API reference
BigQuery APIs and libraries overview
BigQuery API reference
BigQuery client libraries
BigQuery REST API
REST reference (v2)
REST Resources
datasets
Overview
delete
get
insert
list
patch
undelete
update
jobs
Overview
cancel
delete
get
getQueryResults
insert
list
query
models
Overview
delete
get
list
patch
projects
Overview
getServiceAccount
list
routines
Overview
delete
get
getIamPolicy
insert
list
setIamPolicy
testIamPermissions
update
rowAccessPolicies
Overview
getIamPolicy
list
testIamPermissions
tabledata
Overview
insertAll
list
tables
Overview
delete
get
getIamPolicy
insert
list
patch
setIamPolicy
testIamPermissions
update
Types
ConnectionProperty
DataFormatOptions
DatasetAccessEntry
DmlStats
EncryptionConfiguration
GetPolicyOptions
Job
JobCreationReason
JobReference
Policy
ProjectReference
QueryParameter
RoundingMode
SessionInfo
StandardSqlDataType
StandardSqlField
TableReference
TargetType
TestIamPermissionsResponse
API uploads
BigQuery Data Policy API reference
Data Policy REST reference
v1
REST Resources
projects.locations.dataPolicies
Overview
create
delete
get
getIamPolicy
list
patch
rename
setIamPolicy
testIamPermissions
v1beta1
REST Resources
projects.locations.dataPolicies
Overview
create
delete
get
getIamPolicy
list
patch
setIamPolicy
testIamPermissions
v2
REST Resources
projects.locations.dataPolicies
Overview
addGrantees
create
delete
get
getIamPolicy
list
patch
removeGrantees
setIamPolicy
testIamPermissions
v2beta1
REST Resources
projects.locations.dataPolicies
Overview