Snowflake SQL translation guide
This document details the similarities and differences in SQL syntax between Snowflake and BigQuery to help accelerate the planning and execution of moving your EDW (Enterprise Data Warehouse) to BigQuery. Snowflake data warehousing is designed to work with Snowflake-specific SQL syntax. Scripts written for Snowflake might need to be altered before you can use them in BigQuery, because the SQL dialects vary between the services. Use batch SQL translation to migrate your SQL scripts in bulk, or interactive SQL translation to translate ad-hoc queries.
Data types
This section shows equivalents between data types in Snowflake and in BigQuery.
Snowflake | BigQuery | Notes |
---|---|---|
NUMBER/
DECIMAL/NUMERIC |
NUMERIC |
The NUMBER data type in Snowflake supports 38 digits of precision and 37 digits of scale. Precision and scale can be specified according to the user.The NUMERIC data type in BigQuery supports 38 digits of precision and 9 digits of scale. BigQuery does not support custom digit or scale bounds. |
INT/INTEGER |
INT64 |
|
BIGINT |
INT64 |
|
SMALLINT |
INT64 |
|
TINYINT |
INT64 |
|
BYTEINT |
INT64 |
|
FLOAT/ |
FLOAT64 |
The FLOAT data type in Snowflake establishes 'NaN' as > X, where X is any FLOAT value (other than 'NaN' itself).The FLOAT data type in BigQuery establishes 'NaN' as < X, where X is any FLOAT value (other than 'NaN' itself). |
DOUBLE/ REAL |
FLOAT64 |
The DOUBLE data type in Snowflake is synonymous with the FLOAT data type in Snowflake, but is commonly incorrectly displayed as FLOAT . It is properly stored as DOUBLE . |
VARCHAR |
STRING |
The VARCHAR data type in Snowflake has a maximum length of 16 MB (uncompressed). If length is not specified, the default is the maximum length.The STRING data type in BigQuery is stored as variable length UTF-8 encoded Unicode. The maximum length is 16,000 characters. |
CHAR/CHARACTER |
STRING |
The CHAR data type in Snowflake has a maximum length of 1. |
STRING/TEXT |
STRING |
The STRING data type in Snowflake is synonymous with Snowflake's VARCHAR. |
BINARY |
BYTES |
|
VARBINARY |
BYTES |
|
BOOLEAN |
BOOL |
The BOOL data type in BigQuery can only accept TRUE/FALSE , unlike the BOOL data type in Snowflake, which can accept TRUE/FALSE/NULL. |
DATE |
DATE |
The DATE type in Snowflake accepts most common date formats, unlike the DATE type in BigQuery, which only accepts dates in the format, 'YYYY-[M]M-[D]D'. |
TIME |
TIME |
The TIME type in Snowflake supports 0 to 9 nanoseconds of precision, whereas the TIME type in BigQuery supports 0 to 6 nanoseconds of precision. |
TIMESTAMP |
TIMESTAMP |
|
TIMESTAMP_LTZ |
TIMESTAMP |
|
TIMESTAMP_NTZ/DATETIME |
TIMESTAMP |
|
TIMESTAMP_TZ |
TIMESTAMP |
|
OBJECT |
STRUCT |
The OBJECT type in Snowflake does not support explicitly-typed values. Values are of the VARIANT type. The STRUCT type in BigQuery only supports explicitly-typed values. |
ARRAY |
ARRAY |
The ARRAY type in Snowflake can only support VARIANT types, whereas the ARRAY type in BigQuery can support all data types with the exception of an array itself. The Snowflake ARRAY types can support NULL values via VARIANT types, whereas BigQuery ARRAY types do not. |
BigQuery also has the following data types which do not have a direct Snowflake analogue:
Snowflake also has the following data types which do not have a direct BigQuery analogue:
General reference
This section provides details on general references in Snowflake and their mapping to references in BigQuery.
Query syntax and query operators
This section addresses differences in query syntax between Snowflake and BigQuery.
SELECT statement
Most
Snowflake SELECT
statements
are compatible with BigQuery. The following table contains a list of
minor differences.
Snowflake | BigQuery | |
---|---|---|
|
|
|
Note: Snowflake supports creating and referencing an alias in the same SELECT statement. |
|
|
|
|
Snowflake aliases and identifiers are case-insensitive by default. To preserve case, enclose aliases and identifiers with double quotes (").
BigQuery supports the following expressions in SELECT
statements, which do not have a Snowflake equivalent:
FROM clause
A
FROM
clause
in a query specifies the possible tables, views, subquery, or table functions to
use in a SELECT statement. All of these table references are supported in
BigQuery.
The following table contains a list of minor differences.
Snowflake | BigQuery | |
---|---|---|
|
WITH table1 AS |
|
UNPIVOT
|
BigQuery does not offer a direct alternative to Snowflake's PIVOT/UNPIVOT. |
|
|
|
|
|
Note: BigQuery does not have a direct alternative to Snowflake's BEFORE using a statement ID. The value of timestamp cannot be more than 7 days before the current timestamp. |
|
|
BigQuery does not support the concept of staged files. |
|
|
BigQuery does not offer a direct alternative to Snowflake's |
BigQuery tables can be referenced in the FROM
clause using:
[project_id].[dataset_id].[table_name]
[dataset_id].[table_name]
[table_name]
BigQuery also supports additional table references:
- Historical versions of the table definition and rows using
FOR SYSTEM_TIME AS OF
- Field paths, or any path that resolves to a field within a data type (that
is, a
STRUCT
) - Flattened arrays
WHERE clause
The Snowflake
WHERE
clause and BigQuery
WHERE
clause are identical, except for the following:
Snowflake | BigQuery | |
---|---|---|
|
SELECT col1, col2 Note: BigQuery does not support the (+) syntax for JOIN s |
JOIN types
Both Snowflake and BigQuery support the following types of join:
[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
CROSS JOIN
and the equivalent implicit "comma cross join"
Both Snowflake and BigQuery support theON
andUSING
clause.
The following table contains a list of minor differences.
Snowflake | BigQuery | |
---|---|---|
|
Note: In BigQuery, JOIN clauses require a JOIN condition unless it is a CROSS JOIN or one of the joined tables is a field within a data type or an array. |
|
Note: Unlike the output of a non-lateral join, the output from a lateral join includes only the rows generated from the in-line view. The rows on the left-hand side do not need to be joined to the right hand side because the rows on the left-hand side have already been taken into account by being passed into the in-line view. |
LATERAL JOIN s. |
WITH clause
A BigQuery WITH
clause
contains one or more named subqueries which execute every time a subsequent
SELECT
statement references them. Snowflake
WITH
clauses behave the same as BigQuery with the exception that
BigQuery does not support WITH RECURSIVE
.
GROUP BY clause
Snowflake GROUP BY
clauses support GROUP
BY
,
GROUP BY
ROLLUP
,
GROUP BY GROUPING
SETS
,
and GROUP BY
CUBE
,
while BigQuery GROUP BY
clauses supports GROUP
BY
and
GROUP BY
ROLLUP
.
Snowflake
HAVING
and BigQuery
HAVING
are
synonymous. Note that HAVING
occurs after GROUP BY
and aggregation, and
before ORDER BY
.
Snowflake | BigQuery | |
---|---|---|
|
|
|
|
|
|
Note: Snowflake allows up to 128 grouping sets in the same query block |
BigQuery does not support a direct alternative to Snowflake's GROUP BY GROUPING SETS . |
|
Note: Snowflake allows up to 7 elements (128 grouping sets) in each cube |
BigQuery does not support a direct alternative to Snowflake's GROUP BY CUBE . |
ORDER BY clause
There are some minor differences between
Snowflake ORDER BY
clauses
and
BigQuery ORDER BY
clauses.
Snowflake | BigQuery | |
---|---|---|
In Snowflake, NULL s are ranked last by default (ascending order). |
In BigQuery, NULLS are ranked first by default (ascending order). |
|
You can specify whether NULL values should be ordered first or last using NULLS FIRST or NULLS LAST , respectively. |
There's no equivalent to specify whether NULL values should be first or last in BigQuery. |
LIMIT/FETCH clause
The
LIMIT/FETCH
clause in Snowflake constrains the maximum number of rows returned by a
statement or subquery.
LIMIT
(Postgres syntax) and
FETCH
(ANSI syntax) produce the same result.
In Snowflake and BigQuery, applying a LIMIT
clause to a query does
not affect the amount of data that is read.
Snowflake | BigQuery | |
---|---|---|
Note: NULL , empty string (''), and $$$$ values are accepted and are treated as "unlimited". Primary use is for connectors and drivers. |
Note: BigQuery does not support FETCH . LIMIT replaces FETCH .Note: In BigQuery, OFFSET must be used together with a LIMIT count . Make sure to set the count INT64 value to the minimum necessary ordered rows for best performance. Ordering all result rows unnecessarily will lead to worse query execution performance. |
QUALIFY clause
The
QUALIFY
clause in Snowflake allows you to filter results for window functions similar to
what HAVING
does with aggregate functions and GROUP BY
clauses.
Snowflake | BigQuery | |
---|---|---|
|
The Snowflake QUALIFY clause with an analytics function like ROW_NUMBER() , COUNT() , and with OVER PARTITION BY is expressed in BigQuery as a WHERE clause on a subquery that contains the analytics value.Using ROW_NUMBER() :SELECT col1, col2
Using ARRAY_AGG() , which supports larger partitions:
|
Functions
The following sections list Snowflake functions and their BigQuery equivalents.
Aggregate functions
The following table shows mappings between common Snowflake aggregate, aggregate analytic, and approximate aggregate functions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
Note: DISTINCT does not have any effect |
|
Note: DISTINCT does not have any effect |
Note: BigQuery does not support APPROX_COUNT_DISTINCT with Window Functions |
Note: Snowflake does not have the option to RESPECT NULLS |
Note: BigQuery does not support APPROX_QUANTILES with Window Functions |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
Note: If no number parameter is specified, default is 1. Counters should be significantly larger than number. |
Note: BigQuery does not support APPROX_TOP_COUNT with Window Functions. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
You can use a custom UDF to implement MINHASH with k distinct hash functions. Another approach to reduce the variance in MINHASH is to keepk of the minimum values of one hash function. In this case Jaccard index can be approximated as following:
|
|
It is a synonym for APPROXIMATE_JACCARD_INDEX and can be implemented in the same way. |
|
|
|
Note: BigQuery's AVG does not perform automatic casting on STRING s. |
|
INTEGER . |
|
Note: BigQuery does not implicitly cast character/text columns to the nearest INTEGER . |
|
Note: BigQuery does not implicitly cast character/text columns to the nearest INTEGER . |
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
For numeric expression:
To use OVER you can run the following (boolean example provided):
|
|
|
|
|
|
|
|
|
|
BigQuery does not support a direct alternative to Snowflake's GROUPING . Available through a User-Defined Function. |
|
BigQuery does not support a direct alternative to Snowflake's GROUPING_ID . Available through a User-Defined Function. |
|
SELECT BIT_XOR( FARM_FINGERPRINT( TO_JSON_STRING(t))) [OVER] FROM t |
Note: Snowflake does not allow you to specify precision. |
Note: BigQuery does not support HLL_COUNT… with Window Functions. A user cannot include multiple expressions in a single HLL_COUNT... function. |
Note: Snowflake does not allow you to specify precision. |
HLL_COUNT.INIT (expression [, precision]) |
|
HLL_COUNT.MERGE_PARTIAL (sketch) |
|
|
|
BigQuery does not support a direct alternative to Snowflake's HLL_EXPORT . |
|
BigQuery does not support a direct alternative to Snowflake's HLL_IMPORT . |
|
BigQuery does not support a direct alternative to Snowflake's KURTOSIS . |
|
|
Note: Snowflake does not support ability to IGNORE|RESPECT NULLS and to LIMIT directly in ARRAY_AGG. |
|
|
|
|
You can use a custom UDF to implement MINHASH with k distinct hash functions. Another approach to reduce the variance in MINHASH is to keep k of the minimum values of one hash function: SELECT DISTINCT FARM_FINGERPRINT( TO_JSON_STRING(t)) AS MINHASH
|
|
FROM ( |
|
|
|
You may consider using TO_JSON_STRING to convert a value into JSON-formatted string |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
BigQuery does not support a direct alternative to Snowflake's SKEW . |
|
|
|
|
|
|
|
|
Note: Snowflake supports the ability to cast VARCHAR s to floating point values. |
|
Note: Snowflake supports the ability to cast VARCHAR s to floating point values. |
|
Note: Snowflake supports the ability to cast VARCHAR s to floating point values. |
|
Note: Snowflake supports the ability to cast VARCHAR s to floating point values. |
|
BigQuery also offers the following aggregate, aggregate analytic, and approximate aggregate functions, which do not have a direct analogue in Snowflake:
Bitwise expression functions
The following table shows mappings between common Snowflake bitwise expression functions with their BigQuery equivalents.
If the data type of an expression is not INTEGER
, Snowflake attempts to cast
to INTEGER
. However, BigQuery does not attempt to cast to INTEGER
.
Snowflake | BigQuery |
---|---|
|
|
|
|
|
|
|
|
BITSHIFTRIGHT
|
|
Note: Snowflake does not support DISTINCT. |
|
Conditional expression functions
The following table shows mappings between common Snowflake conditional expressions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
|
|
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
BOOLOR Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
BOOLXOR Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
BigQuery does not support a direct alternative to Snowflake's BOOLXOR. |
|
|
Note: Snowflake requires at least two expressions. BigQuery only requires one. |
|
|
DECODE . User must use IS NULL instead of = NULL to match NULL select expressions with NULL search expressions. |
|
BigQuery does not support a direct alternative to Snowflake's EQUAL_NULL. |
|
|
|
|
|
|
|
|
|
BigQuery does not support a direct alternative to Snowflake's IS [ NOT ] DISTINCT FROM. |
|
|
|
BigQuery does not support VARIANT data types. |
|
|
|
|
|
|
|
|
|
REGR... functions. |
|
Note: BigQuery does not support a direct alternative to Snowflake's REGR... functions. |
|
|
Context functions
The following table shows mappings between common Snowflake context functions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
Note: Not direct comparison. Snowflake returns account ID, BigQuery returns user email address. |
|
Concept not used in BigQuery |
|
|
|
Note: Snowflake does not enforce '()' after CURRENT_DATE command to comply with ANSI standards. |
Note: BigQuery's CURRENT_DATE supports optional time zone specification. |
Note: BigQuery's INFORMATION_SCHEMA.SCHEMATA returns more generalized location references than Snowflake's CURRENT_REGION() . Not a direct comparison. |
|
Concept not used in BigQuery |
|
|
|
Concept not used in BigQuery |
|
Concept not used in BigQuery |
|
Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for queries by job type, start/end type, etc. |
|
Note: Snowflake allows for optional fractional second precision. Valid values range from 0-9 nanoseconds. Default value is 9. To comply with ANSI, this can be called without '()'. |
|
Note: Snowflake allows for optional fractional second precision. Valid values range from 0-9 nanoseconds. Default value is 9. To comply with ANSI, this can be called without '()'. Set TIMEZONE as a session parameter. |
Note: CURRENT_DATETIME returns DATETIME data type (not supported in Snowflake). CURRENT_TIMESTAMP returns TIMESTAMP data type. |
INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc. |
|
Note: Snowflake does not enforce '()' after CURRENT_USER command to comply with ANSI standards. |
|
Concept not used in BigQuery |
|
|
|
|
Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc. |
Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc. |
|
Note: Snowflake does not enforce '()' after LOCALTIME command to comply with ANSI standards. |
|
Note: CURRENT_DATETIME returns DATETIME data type (not supported in Snowflake). CURRENT_TIMESTAMP returns TIMESTAMP data type. |
Conversion functions
The following table shows mappings between common Snowflake conversion functions with their BigQuery equivalents.
Keep in mind that functions that seem identical in Snowflake and BigQuery may return different data types.
Snowflake | BigQuery |
---|---|
|
|
|
|
Note: Snowflake supports HEX , BASE64 , and UTF-8 conversion. Snowflake also supports TO_BINARY using the VARIANT data type. BigQuery does not have an alternative to the VARIANT data type. |
Note: BigQuery's default STRING casting uses UTF-8 encoding. Snowflake does not have an option to support BASE32 encoding. |
Note:
|
Note:
|
Note: Snowflake's format models can be found here. BigQuery does not have an alternative to the VARIANT data type. |
Note: BigQuery's input expression can be formatted using FORMAT_DATE , FORMAT_DATETIME , FORMAT_TIME , or FORMAT_TIMESTAMP . |
Note: Snowflake supports the ability to directly convert INTEGER types to DATE types. Snowflake's format models can be found here. BigQuery does not have an alternative to the VARIANT data type. |
Note: BigQuery's input expression can be formatted using FORMAT , FORMAT_DATETIME , or FORMAT_TIMESTAMP . |
Note: Snowflake's format models for the DECIMAL , NUMBER , and NUMERIC data types can be found here. BigQuery does not have an alternative to the VARIANT data type. |
Note: BigQuery's input expression can be formatted using FORMAT. |
Note: Snowflake's format models for the DOUBLE data types can be found here. BigQuery does not have an alternative to the VARIANT data type. |
Note: BigQuery's input expression can be formatted using FORMAT. |
|
BigQuery does not have an alternative to Snowflake's VARIANT data type. |
|
BigQuery does not have an alternative to Snowflake's VARIANT data type. |
Note: Snowflake's format models for the STRING data types can be found here. BigQuery does not have an alternative to the VARIANT data type. |
Note: BigQuery does not have an alternative to Snowflake's VARIANT data type. BigQuery's input expression can be formatted using FORMAT , FORMAT_DATETIME , FORMAT_TIMESTAMP , or FORMAT_TIME . |
Note: BigQuery does not have an alternative to the VARIANT data type. |
Note: BigQuery's input expression can be formatted using FORMAT , FORMAT_DATE , FORMAT_DATETIME , FORMAT_TIME . Timezone can be included/not included through FORMAT_TIMESTAMP parameters. |
|
BigQuery does not have an alternative to Snowflake's VARIANT data type. |
|
BigQuery does not have an alternative to Snowflake's VARIANT data type. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
BigQuery also offers the following conversion functions, which do not have a direct analogue in Snowflake:
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_STRING
FORMAT
FROM_BASE32
FROM_BASE64
FROM_HEX
SAFE_CONVERT_BYTES_TO_STRING
TO_BASE32
TO_CODE_POINTS
Data generation functions
The following table shows mappings between common Snowflake data generation functions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
|
BigQuery does not support a direct comparison to Snowflake's NORMAL. |
|
Note: BigQuery does not support seeding |
|
BigQuery does not support a direct comparison to Snowflake's RANDSTR. |
SEQ1 / SEQ2 / SEQ4 / SEQ8 |
BigQuery does not support a direct comparison to Snowflake's SEQ_. |
|
Note:Use persistent UDFs to create an equivalent to Snowflake's UNIFORM . Example here. |
UUID_STRING([uuid, name]) Note: Snowflake returns 128 random bits. Snowflake supports both version 4 (random) and version 5 (named) UUIDs. |
Note: BigQuery returns 122 random bits. BigQuery only supports version 4 UUIDs. |
|
BigQuery does not support a direct comparison to Snowflake's ZIPF. |
Date and time functions
The following table shows mappings between common Snowflake date and time functions with their BigQuery equivalents. BigQuery data and time functions include Date functions, Datetime functions, Time functions, and Timestamp functions.
Snowflake | BigQuery |
---|---|
|
|
|
Note: source_timezone is always UTC in BigQuery |
Note: Snowflake supports overflow and negative dates. For example, DATE_FROM_PARTS(2000, 1 + 24, 1) returns Jan 1, 2002. This is not supported in BigQuery. |
|
Note: Snowflake supports the day of week ISO, nanosecond, and epoch second/millisecond/microsecond/nanosecond part types. BigQuery does not. See full list of Snowflake part types here . |
Note: BigQuery supports the week(<weekday>), microsecond, and millisecond part types. Snowflake does not. See full list of BigQuery part types here and here. |
Note: Snowflake supports the nanosecond part type. BigQuery does not. See full list of Snowflake part types here . |
Note: BigQuery supports the week(<weekday>), ISO week, and ISO year part types. Snowflake does not. |
|
|
Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function. |
Note: BigQuery supports the week(<weekday>) and ISO year part types. |
|
|
Note: Snowflake supports the day of week ISO, nanosecond, and epoch second/millisecond/microsecond/nanosecond part types. BigQuery does not. See full list of Snowflake part types here . |
Note: BigQuery supports the week(<weekday>), microsecond, and millisecond part types. Snowflake does not. See full list of BigQuery part types here and here. |
|
|
|
|
|
|
|
Note: dowString might need to be reformatted. For example, Snowflake's 'su' will be BigQuery's 'SUNDAY'. |
|
Note: dowString might need to be reformatted. For example, Snowflake's 'su' will be BigQuery's 'SUNDAY'. |
Note: Snowflake supports overflow times. For example, TIME_FROM_PARTS(0, 100, 0) returns 01:40:00... This is not supported in BigQuery. BigQuery does not support nanoseconds. |
|
|
Note: BigQuery does not support a direct, exact comparison to Snowflake's TIME_SLICE . Use DATETINE_TRUNC , TIME_TRUNC , TIMESTAMP_TRUNC for appropriate data type. |
|
|
Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function. |
Note: BigQuery supports the week(<weekday>) and ISO year part types. |
|
Note: BigQuery requires timestamps be inputted as STRING types. Example: "2008-12-25 15:30:00" |
|
|
Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function. |
Note: BigQuery supports the week(<weekday>) and ISO year part types. |
Note: Snowflake supports the nanosecond part type. BigQuery does not. See full list of Snowflake part types here . |
Note: BigQuery supports the week(<weekday>), ISO week, and ISO year part types. Snowflake does not. |
|
|
BigQuery also offers the following date and time functions, which do not have a direct analogue in Snowflake:
Information schema and table functions
BigQuery does not conceptually support many of Snowflake's information schema and table functions. Snowflake offers the following information schema and table functions, which do not have a direct analogue in BigQuery:
AUTOMATIC_CLUSTERING_HISTORY
COPY_HISTORY
DATA_TRANSFER_HISTORY
DATABASE_REFRESH_HISTORY
DATABASE_REFRESH_PROGRESS, DATABASE_REFRESH_PROGRESS_BY_JOB
DATABASE_STORAGE_USAGE_HISTORY
EXTERNAL_TABLE_FILES
EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY
LOGIN_HISTORY
,LOGIN_HISTORY_BY_USER
MATERIALIZED_VIEW_REFRESH_HISTORY
PIPE_USAGE_HISTORY
REPLICATION_USAGE_HISTORY
STAGE_STORAGE_USAGE_HISTORY
TASK_DEPENDENTS
VALIDATE_PIPE_LOAD
WAREHOUSE_LOAD_HISTORY
WAREHOUSE_METERING_HISTORY
Below is a list of associated BigQuery and Snowflake information schema and table functions.
Snowflake | BigQuery |
---|---|
QUERY_HISTORY QUERY_HISTORY_BY_* |
INFORMATION_SCHEMA.JOBS_BY_* Note: Not a direct alternative. |
TASK_HISTORY |
INFORMATION_SCHEMA.JOBS_BY_* Note: Not a direct alternative. |
BigQuery offers the following information schema and table functions, which do not have a direct analogue in Snowflake:
INFORMATION_SCHEMA.SCHEMATA
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.VIEWS
Numeric functions
The following table shows mappings between common Snowflake numeric functions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: BigQuery's CEIL does not support the ability to indicate precision or scale. ROUND does not allow you to specify to round up. |
|
|
|
|
|
|
|
|
|
|
|
BigQuery does not have a direct alternative to Snowflake's FACTORIAL . Use a user-defined function. |
|
Note: BigQuery's FLOOR does not support the ability to indicate precision or scale. ROUND does not allow you to specify to round up. TRUNC performs synonymously for positive numbers but not negative numbers, as it evaluates absolute value. |
|
Note: Not an exact match, but close enough. |
|
|
|
Note:Default base for LOG is 10. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: BigQuery's returned value must be smaller than the expression; it does not support equal to. |
BigQuery also offers the following mathematical functions, which do not have a direct analogue in Snowflake:
IS_INF
IS_NAN
IEEE_DIVIDE
DIV
SAFE_DIVIDE
SAFE_MULTIPLY
SAFE_NEGATE
SAFE_ADD
SAFE_SUBTRACT
RANGE_BUCKET
Semi-structured data functions
Snowflake | BigQuery |
---|---|
ARRAY_APPEND |
Custom user-defined function |
ARRAY_CAT |
ARRAY_CONCAT |
ARRAY_COMPACT |
Custom user-defined function |
ARRAY_CONSTRUCT |
[ ] |
ARRAY_CONSTRUCT_COMPACT |
Custom user-defined function |
ARRAY_CONTAINS |
Custom user-defined function |
ARRAY_INSERT |
Custom user-defined function |
ARRAY_INTERSECTION |
Custom user-defined function |
ARRAY_POSITION |
Custom user-defined function |
ARRAY_PREPEND |
Custom user-defined function |
ARRAY_SIZE |
ARRAY_LENGTH |
ARRAY_SLICE |
Custom user-defined function |
ARRAY_TO_STRING |
ARRAY_TO_STRING |
ARRAYS_OVERLAP |
Custom user-defined function |
AS_<object_type> |
CAST |
AS_ARRAY |
CAST |
AS_BINARY |
CAST |
AS_BOOLEAN |
CAST |
AS_CHAR , AS_VARCHAR |
CAST |
AS_DATE |
CAST |
AS_DECIMAL , AS_NUMBER |
CAST |
AS_DOUBLE , AS_REAL |
CAST |
AS_INTEGER |
CAST |
AS_OBJECT |
CAST |
AS_TIME |
CAST |
AS_TIMESTAMP_* |
CAST |
CHECK_JSON |
Custom user-defined function |
CHECK_XML |
Custom user-defined function |
FLATTEN |
UNNEST |
GET |
Custom user-defined function |
GET_IGNORE_CASE |
Custom user-defined function |
|
Custom user-defined function |
IS_<object_type> |
Custom user-defined function |
IS_ARRAY |
Custom user-defined function |
IS_BINARY |
Custom user-defined function |
IS_BOOLEAN |
Custom user-defined function |
IS_CHAR , IS_VARCHAR |
Custom user-defined function |
IS_DATE , IS_DATE_VALUE |
Custom user-defined function |
IS_DECIMAL |
Custom user-defined function |
IS_DOUBLE , IS_REAL |
Custom user-defined function |
IS_INTEGER |
Custom user-defined function |
IS_OBJECT |
Custom user-defined function |
IS_TIME |
Custom user-defined function |
IS_TIMESTAMP_* |
Custom user-defined function |
OBJECT_CONSTRUCT |
Custom user-defined function |
OBJECT_DELETE |
Custom user-defined function |
OBJECT_INSERT |
Custom user-defined function |
PARSE_JSON |
JSON_EXTRACT |
PARSE_XML |
Custom user-defined function |
STRIP_NULL_VALUE |
Custom user-defined function |
STRTOK_TO_ARRAY |
SPLIT |
TRY_PARSE_JSON |
Custom user-defined function |
TYPEOF |
Custom user-defined function |
|