SELECTTOP ...

FROM table

SELECT expression

FROM table

ORDER BY expression DESC

LIMIT number

SELECT

x/total AS probability,

ROUND(100 * probability, 1) AS pct

FROM raw_data

SELECT

x/total AS probability,

ROUND(100 * (x/total), 1) AS pct

FROM raw_data

SELECT *FROM (

VALUES (1), (2), (3)

)

SELECTAS VALUE STRUCT(1, 2, 3)

SELECT $1, $2 FROM(VALUES (1, 'one'), (2, 'two'));

SELECT*FROM tableSAMPLE(10)

SELECT*FROM table

TABLESAMPLE

BERNOULLI (0.1 PERCENT)

SELECT * FROM table1AT(TIMESTAMP => timestamp)SELECT * FROM table1BEFORE(STATEMENT => statementID)

SELECT * FROM table

FOR SYSTEM_TIME AS OF timestamp

@[namespace]<stage_name>[/path]

BigQuery does not support the concept of staged files.

SELECT*

FROM table

START WITH predicate

CONNECT BY

[PRIOR] col1 = [PRIOR] col2

[, ...]

...

BigQuery does not offer a direct alternative to Snowflake'sCONNECT BY.

SELECT col1, col2FROM table1, table2WHERE col1 = col2(+)

SELECT col1

FROM table1

NATURAL JOIN

table2

SELECT col1

FROM table1

INNER JOIN

table2

USING (col1, col2 [, ...])

SELECT ...FROM table1 AS t1,LATERAL ( SELECT*

FROM table2 AS t2

WHERE t1.col = t2.col)

SELECT ...FROM table1 as t1LEFT JOIN table2 as t2

ON t1.col = t2.col

SELECTcol1 as one, col2 as two

FROM tableGROUP BY (one, 2)

SELECT col1 as one, col2 as two

FROM tableGROUP BY (one, 2)

SELECTcol1 as one, col2 as two

FROM tableGROUP BY ROLLUP (one, 2)

SELECT col1 as one, col2 as two

FROM tableGROUP BY ROLLUP (one, 2)

SELECTcol1 as one, col2 as two

FROM tableGROUP BY GROUPING SETS(one, 2)

SELECT col1 as one, col2 as two

FROM tableGROUP BY GROUPING SETS (one, 2)

SELECTcol1 as one, col2 as two

FROM tableGROUP BY CUBE(one,2)

SELECT col1 as one, col2 as two

FROM tableGROUP BY CUBE (one, 2)

SELECT col1, col2

FROM table

ORDER BY col1

LIMIT count OFFSET start

SELECT ...

FROM ...

ORDER BY ...

OFFSET start {[ROW | ROWS]}FETCH {[FIRST | NEXT]} count

{[ROW | ROWS]} [ONLY]

SELECT col1, col2

FROM table

ORDER BY col1

LIMIT count OFFSET start

SELECT col1, col2FROM tableQUALIFY ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) = 1;

FROM (SELECT col1, col2

ROW NUMBER() OVER (PARTITION BY col1 ORDER by col2) RNFROM table) WHERE RN = 1;

SELECT result.*FROM (SELECT ARRAY_AGG(table ORDER BY table.col2 DESC LIMIT 1) [OFFSET(0)]FROM table

GROUP BY col1) AS result;

ANY_VALUE([DISTINCT] expression)[OVER ...]

ANY_VALUE(expression)[OVER ...]

APPROX_COUNT_DISTINCT([DISTINCT] expression) [OVER ...]

APPROX_COUNT_DISTINCT(expression)

APPROX_PERCENTILE(expression, percentile) [OVER ...]

APPROX_QUANTILES([DISTINCT] expression,100)[OFFSET((CAST(TRUNC(percentile * 100) as INT64))]

APPROX_PERCENTILE_ACCUMULATE(expression)

APPROX_PERCENTILE_COMBINE(state)

APPROX_PERCENTILE_ESTIMATE(state, percentile)

APPROX_TOP_K(expression, [number [counters]]

APPROX_TOP_COUNT(expression, number)

APPROX_TOP_K_ACCUMULATE(expression,counters)

APPROX_TOP_K_COMBINE(state, [counters])

APPROX_TOP_K_ESTIMATE(state, [k])

APPROXIMATE_JACCARD_INDEX([DISTINCT] expression)

WITH

minhash_A AS (

SELECT DISTINCTFARM_FINGERPRINT(TO_JSON_STRING(t)) AS h

FROM TA AS t

ORDER BY h

LIMIT k),

minhash_B AS (

SELECT DISTINCTFARM_FINGERPRINT(TO_JSON_STRING(t)) AS h

FROM TB AS t

ORDER BY h

LIMIT k)

SELECT

COUNT(*) / k AS APPROXIMATE_JACCARD_INDEX

FROM minhash_A

INNER JOIN minhash_B

ON minhash_A.h = minhash_B.h

APPROXIMATE_SIMILARITY([DISTINCT] expression)

ARRAY_AGG([DISTINCT] expression1)[WITHIN GROUP (ORDER BY ...)]

[OVER ([PARTITION BY expression2])]

Note: Snowflake does not support ability to IGNORE|RESPECT NULLS and to LIMIT directly in ARRAY_AGG.

ARRAY_AGG([DISTINCT] expression1

[{IGNORE|RESPECT}] NULLS][ORDER BY ...]LIMIT ...])

[OVER (...)]

AVG([DISTINCT] expression)[OVER ...]

AVG([DISTINCT] expression)[OVER ...]

BITAND_AGG(expression)

[OVER ...]

BIT_AND(expression)[OVER ...]

BITOR_AGG(expression)

[OVER ...]

BIT_OR(expression)

[OVER ...]

BITXOR_AGG([DISTINCT] expression)[OVER ...]

BIT_XOR([DISTINCT] expression)[OVER ...]

BOOLAND_AGG(expression)[OVER ...]

LOGICAL_AND(expression)

[OVER ...]

BOOLOR_AGG(expression)

[OVER ...]

LOGICAL_OR(expression)

[OVER ...]

BOOLXOR_AGG(expression)

[OVER ([PARTITION BY <partition_expr> ])

SELECT

CASECOUNT(*)

WHEN 1 THEN TRUE

WHEN 0 THEN NULL

ELSE FALSE

END AS BOOLXOR_AGG

FROM T

WHERE expression != 0

SELECT

CASE COUNT(expression) OVER (PARTITION BY partition_expr)

WHEN 0 THEN NULL

ELSE

CASE COUNT(

CASE expression

WHEN TRUE THEN 1

END) OVER (PARTITION BY partition_expr)

WHEN 1 THEN TRUE

ELSE FALSE

END

END AS BOOLXOR_AGG

FROM T

CORR(dependent, independent)

[OVER ...]

CORR(dependent, independent)

[OVER ...]

COUNT([DISTINCT] expression [,expression2])[OVER ...]

COUNT([DISTINCT] expression [,expression2])[OVER ...]

COVAR_POP(dependent, independent)[OVER ...]

COVAR_POP(dependent, independent)[OVER ...]

COVAR_SAMP(dependent, independent)

[OVER ...]

COVAR_SAMP(dependent, independent)

[OVER ...]

GROUPING(expression1, [,expression2...])

GROUPING_ID(expression1, [,expression2...])

HASH_AGG([DISTINCT] expression1, [,expression2])

[OVER ...]

SELECTHLL([DISTINCT] expression1, [,expression2])

[OVER ...]

SELECTHLL_COUNT.EXTRACT(sketch)FROM (

SELECTHLL_COUNT.INIT(expression)

AS sketch FROM table)

HLL_ACCUMULATE([DISTINCT] expression)

HLL_COMBINE([DISTINCT] state)

HLL_ESTIMATE(state)

HLL_COUNT.EXTRACT(sketch)

HLL_EXPORT(binary)

HLL_IMPORT(object)

KURTOSIS(expression)

[OVER ...]

LISTAGG(

[DISTINCT] aggregate_expression

[, delimiter]

)

[OVER ...]

STRING_AGG(

[DISTINCT] aggregate_expression

[, delimiter]

)

[OVER ...]

MEDIAN(expression)[OVER ...]

PERCENTILE_CONT(

value_expression,

0.5

) OVER()

MAX(expression)[OVER ...]

MIN(expression)[OVER ...]

MAX(expression)[OVER ...]

MIN(expression)[OVER ...]

MINHASH(k, [DISTINCT] expressions)

FROM t

ORDER BY MINHASH

LIMIT k

MINHASH_COMBINE([DISTINCT] state)

MODE(expr1)

OVER ( [ PARTITION BY <expr2> ] )

SELECT expr1

FROM (

SELECT

expr1,

ROW_NUMBER() OVER (

PARTITION BY expr2

ORDER BY cnt DESC) rn

FROM (

SELECT

expr1,

expr2,

COUNTIF(expr1 IS NOT NULL) OVER

(PARTITION BY expr2, expr1) cnt

FROM t))

WHERE rn = 1

OBJECT_AGG(key, value)[OVER ...]

PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY value_expression)

[OVER ...]

PERCENTILE_CONT(

value_expression,

percentile

) OVER()

PERCENTILE_DISC(percentile) WITHIN GROUP (ORDER BY value_expression)

[OVER ...]

PERCENTILE_DISC(

value_expression,

percentile

) OVER()

REGR_AVGX(dependent, independent)

[OVER ...]

SELECTAVG(independent) [OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

REGR_AVGY(dependent, independent)

[OVER ...]

SELECTAVG(dependent) [OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

REGR_COUNT(dependent, independent)

[OVER ...]

SELECTCOUNT(*) [OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

REGR_INTERCEPT(dependent, independent)

[OVER ...]

SELECT

AVG(dependent) -

COVAR_POP(dependent,independent)/

VAR_POP(dependent) *

AVG(independent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_R2(dependent, independent)

[OVER ...]

SELECT

CASE

WHENVAR_POP(independent) = 0

THEN NULL

WHEN VAR_POP(dependent) = 0 AND VAR_POP(independent) != 0

THEN 1

ELSEPOWER(CORR(dependent, independent), 2)

END AS ...

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_SLOPE(dependent, independent)

[OVER ...]

SELECT

COVAR_POP(dependent,independent)/

VAR_POP(dependent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_SXX(dependent, independent)

[OVER ...]

SELECTCOUNT(*)*VAR_POP(independent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_SYY(dependent, independent)

[OVER ...]

SELECTCOUNT(*)*VAR_POP(dependent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

SKEW(expression)

STDDEV([DISTINCT] expression)

[OVER ...]

STDDEV([DISTINCT] expression)

[OVER ...]

STDDEV_POP([DISTINCT] expression)

[OVER ...]

STDDEV_POP([DISTINCT] expression)

[OVER ...]

STDDEV_SAMP([DISTINCT] expression)

[OVER ...]

STDDEV_SAMP([DISTINCT] expression)

[OVER ...]

SUM([DISTINCT] expression)

[OVER ...]

SUM([DISTINCT] expression)

[OVER ...]

VAR_POP([DISTINCT] expression)

[OVER ...]

VAR_POP([DISTINCT] expression)

[OVER ...]

VARIANCE_POP([DISTINCT] expression)

[OVER ...]

VAR_POP([DISTINCT] expression)

[OVER ...]

VAR_SAMP([DISTINCT] expression)

[OVER ...]

VAR_SAMP([DISTINCT] expression)

[OVER ...]

VARIANCE([DISTINCT] expression)

[OVER ...]

VARIANCE([DISTINCT] expression)

[OVER ...]

BITAND(expression1, expression2)

BIT_AND(x)FROM UNNEST([expression1, expression2]) AS xexpression1 & expression2

BITNOT(expression)

~ expression

BITOR(expression1, expression2)

BIT_OR(x)FROM UNNEST([expression1, expression2]) AS x

expression1| expression2

BITSHIFTLEFT(expression, n)

expression<< n

(expression, n)

expression>> n

BITXOR(expression, expression)

BIT_XOR([DISTINCT] x)FROM UNNEST([expression1, expression2]) AS x

expression ^ expression

expression[ NOT ] BETWEENlower AND upper

(expression>= lowerAND expression<= upper)

BOOLAND(expression1, expression2)

LOGICAL_AND(x)

FROM UNNEST([expression1, expression2]) AS x

expression1AND expression2

BOOLNOT(expression1)

NOT expression

LOGICAL_OR(x)FROM UNNEST([expression1, expression2]) AS x

expression1OR expression2

CASE [expression] WHEN condition1 THEN result1 [WHEN condition2 THEN result2]

[...]

[ELSE result3]

END

CASE [expression] WHEN condition1 THEN result1 [WHEN condition2 THEN result2]

[...]

[ELSE result3]

END

COALESCE(expr1, expr2, [,...])

COALESCE(expr1, [,...])

DECODE(expression, search1, result1,[search2, result2...][,default])

CASE [expression] WHEN condition1 THEN result1 [WHEN condition2 THEN result2]

[...]

[ELSE result3]

END

EQUAL_NULL(expression1, expression2)

GREATEST(expression1, [,expression2]...)

GREATEST(expression1, [,expression2]...)

IFF(condition, true_result, false_result)

IF(condition, true_result, false_result)

IFNULL(expression1, expression2)

IFNULL(expression1, expression2)

[ NOT ] IN ...

[ NOT ] IN ...

expression1IS [ NOT ] DISTINCT FROM expression2

expressionIS [ NOT ] NULL

expressionIS [ NOT ] NULL

IS_NULL_VALUE(variant_expr)

LEAST(expression,...)

LEAST(expression,...)

NULLIF(expression1,expression2)

NULLIF(expression1,expression2)

NVL(expression1, expression2)

IFNULL(expression1,expression2)

NVL2(expr1,expr2,expr2)

IF(expr1 IS NOT NULL,expr2,expr3)

REGR_VALX(expr1,expr2)

IF(expr1 IS NULL, NULL, expr2)

REGR_VALY(expr1,expr2)

IF(expr2 IS NULL, NULL, expr1)

ZEROIFNULL(expression)

IFNULL(expression,0)

CURRENT_ACCOUNT()

SESSION_USER()

CURRENT_CLIENT()

Concept not used in BigQuery

CURRENT_DATABASE()

SELECT catalog_name

FROMINFORMATION_SCHEMA.SCHEMATA

This returns a table of project names. Not a direct comparison.

CURRENT_DATE[()]

CURRENT_DATE([timezone])

CURRENT_REGION()

SELECT location

FROMINFORMATION_SCHEMA.SCHEMATA

CURRENT_ROLE()

Concept not used in BigQuery

CURRENT_SCHEMA()

SELECT schema_name

FROMINFORMATION_SCHEMA.SCHEMATA

This returns a table of all datasets (also called schemas) available in the project or region. Not a direct comparison.

CURRENT_SCHEMAS()

Concept not used in BigQuery

CURRENT_SESSION()

Concept not used in BigQuery

CURRENT_STATEMENT()

SELECT query

FROMINFORMATION_SCHEMA.JOBS_BY_*

CURRENT_TIME[([frac_sec_prec])]

CURRENT_TIME()

CURRENT_TIMESTAMP[([frac_sec_prec])]

CURRENT_DATETIME([timezone])CURRENT_TIMESTAMP()

CURRENT_TRANSACTION()

SELECT job_id

FROMINFORMATION_SCHEMA.JOBS_BY_*

CURRENT_USER[()]

SESSION_USER()

SELECT user_email

FROMINFORMATION_SCHEMA.JOBS_BY_*

CURRENT_VERSION()

Concept not used in BigQuery

CURRENT_WAREHOUSE()

SELECT catalg_name

FROMINFORMATION_SCHEMA.SCHEMATA

LAST_QUERY_ID([num])

SELECT job_id

FROMINFORMATION_SCHEMA.JOBS_BY_*

LAST_TRANSACTION()

SELECT job_id

FROMINFORMATION_SCHEMA.JOBS_BY_*

LOCALTIME()

CURRENT_TIME()

LOCALTIMESTAMP()

CURRENT_DATETIME([timezone])CURRENT_TIMESTAMP()

CAST(expression AS type)

expression :: type

CAST(expression AS type)

TO_ARRAY(expression)

[expression]

ARRAY(subquery)

TO_BINARY(expression[, format])

TO_HEX(CAST(expression AS BYTES))TO_BASE64(CAST(expression AS BYTES))

CAST(expression AS BYTES)

TO_BOOLEAN(expression)

• INT64
TRUE:otherwise,FALSE: 0

• STRING
TRUE: "true"/"t"/"yes"/"y"/"on"/"1", FALSE: "false"/"f"/"no"/"n"/"off"/"0"

CAST(expression AS BOOL)

• INT64
TRUE:otherwise,FALSE: 0

• STRING
TRUE: "true", FALSE: "false"

TO_CHAR(expression[, format])

TO_VARCHAR(expression[, format])

CAST(expression AS STRING)

TO_DATE(expression[, format])

DATE(expression[, format])

CAST(expression AS DATE)

TO_DECIMAL(expression[, format]

[,precision[, scale]]

TO_NUMBER(expression[, format]

[,precision[, scale]]

TO_NUMERIC(expression[, format]

[,precision[, scale]]

ROUND(CAST(expression AS NUMERIC)

, x)

TO_DOUBLE(expression[, format])

CAST(expression AS FLOAT64)

TO_JSON(variant_expression)

TO_OBJECT(variant_expression)

TO_TIME(expression[, format])

TIME(expression[, format])

CAST(expression AS TIME)

TO_TIMESTAMP(expression[, scale])

TO_TIMESTAMP_LTZ(expression[, scale])

TO_TIMESTAMP_NTZ(expression[, scale])

TO_TIMESTAMP_TZ(expression[, scale])

CAST(expression AS TIMESTAMP)

TO_VARIANT(expression)

TO_XML(variant_expression)

TRY_CAST(expression AS type)

SAFE_CAST(expression AS type)

TRY_TO_BINARY(expression[, format])

TO_HEX(SAFE_CAST(expression AS BYTES))TO_BASE64(SAFE_CAST(expression AS BYTES))

SAFE_CAST(expression AS BYTES)

TRY_TO_BOOLEAN(expression)

SAFE_CAST(expression AS BOOL)

TRY_TO_DATE(expression)

SAFE_CAST(expression AS DATE)

TRY_TO_DECIMAL(expression[, format]

[,precision[, scale]]

TRY_TO_NUMBER(expression[, format]

[,precision[, scale]]

TRY_TO_NUMERIC(expression[, format]

[,precision[, scale]]

ROUND(

SAFE_CAST(expression AS NUMERIC)

, x)

TRY_TO_DOUBLE(expression)

SAFE_CAST(expression AS FLOAT64)

TRY_TO_TIME(expression)

SAFE_CAST(expression AS TIME)

TRY_TO_TIMESTAMP(expression)

TRY_TO_TIMESTAMP_LTZ(expression)

TRY_TO_TIMESTAMP_NTZ(expression)

TRY_TO_TIMESTAMP_TZ(expression)

SAFE_CAST(expression AS TIMESTAMP)

NORMAL(mean, stddev, gen)

RANDOM([seed])

IF(RAND()>0.5, CAST(RAND()*POW(10, 18) AS INT64),

(-1)*CAST(RAND()*POW(10, 18) AS

INT64))

RANDSTR(length, gen)

UNIFORM(min, max, gen)

CAST(min + RAND()*(max-min) AS INT64)

GENERATE_UUID()

ZIPF(s, N, gen)

ADD_MONTHS(date, months)

CAST(

DATE_ADD(

date,

INTERVAL integer MONTH

) AS TIMESTAMP

)

CONVERT_TIMEZONE(source_tz, target_tz, source_timestamp)

CONVERT_TIMEZONE(target_tz, source_timestamp)

PARSE_TIMESTAMP(

"%c%z",

FORMAT_TIMESTAMP(

"%c%z",

timestamp,

target_timezone

)

)

DATE_FROM_PARTS(year, month, day)

DATE(year, month, day)

DATE(timestamp_expression[, timezone])

DATE(datetime_expression)

DATE_PART(part, dateOrTime)

EXTRACT(part FROM dateOrTime)

DATE_TRUNC(part, dateOrTime)

DATE_TRUNC(date, part)

DATETIME_TRUNC(datetime, part)

TIME_TRUNC(time, part)

TIMESTAMP_TRUNC(timestamp,part[, timezone])

DATEADD(part, value, dateOrTime)

DATE_ADD(date, INTERVAL value part)

DATEDIFF(

part,

start_date_or_time,

end_date_or_time

)

DATE_DIFF(

end_date,

start_date,

part

)

DATETIME_DIFF(

end_datetime,

start_datetime,

part

)

TIME_DIFF(

start_time,

end_time,

part

)

TIMESTAMP_DIFF(

end_timestamp,

start_timestamp,

part

)

DAYNAME(dateOrTimestamp)

FORMAT_DATE('%a', date)

FORMAT_DATETIME('%a', datetime)

FORMAT_TIMESTAMP('%a', timestamp)

EXTRACT(part FROM dateOrTime)

EXTRACT(part FROM dateOrTime)

[HOUR, MINUTE, SECOND](timeOrTimestamp)

EXTRACT(part FROM timestamp [AT THE ZONE timezone])

LAST_DAY(dateOrTime[, part])

DATE_SUB( DATE_TRUNC(

DATE_ADD(date, INTERVAL

1 part),

part),

INTERVAL 1 DAY)

MONTHNAME(dateOrTimestamp)

FORMAT_DATE('%b', date)

FORMAT_DATETIME('%b', datetime)

FORMAT_TIMESTAMP('%b', timestamp)

NEXT_DAY(dateOrTime, dowString)

DATE_ADD(

DATE_TRUNC(

date,

WEEK(dowString)),

INTERVAL 1 WEEK)

PREVIOUS_DAY(dateOrTime, dowString)

DATE_TRUNC(

date,

WEEK(dowString)

)

TIME_FROM_PARTS(hour, minute, second[, nanosecond)

TIME(hour, minute, second)

TIME(timestamp, [timezone])

TIME(datetime)

TIME_SLICE(dateOrTime, sliceLength,part[, START]

TIME_SLICE(dateOrTime, sliceLength,part[, END]

DATE_TRUNC(

DATE_SUB(CURRENT_DATE(),

INTERVAL value MONTH),

MONTH)

DATE_TRUNC(

DATE_ADD(CURRENT_DATE(),

INTERVAL value MONTH),

MONTH)

TIMEADD(part, value, dateOrTime)

TIME_ADD(time, INTERVAL value part)

TIMEDIFF(

part,

expression1,

expression2,

)

DATE_DIFF(

dateExpression1,

dateExpression2,

part

)

DATETIME_DIFF(

datetimeExpression1,

datetimeExpression2,

part

)

TIME_DIFF(

timeExpression1,

timeExpression2,

part

)

TIMESTAMP_DIFF(

timestampExpression1,

timestampExpression2,

part

)

TIMESTAMP_[LTZ, NTZ, TZ _]FROM_PARTS(year, month, day, hour, second[, nanosecond][, timezone])

TIMESTAMP(

string_expression[, timezone] | date_expression[, timezone] |

datetime_expression[, timezone]

)

TIMESTAMPADD(part, value, dateOrTime)

TIMESTAMPADD(timestamp,INTERVAL value part)

TIMESTAMPDIFF(

part,

expression1,

expression2,

)

DATE_DIFF(

dateExpression1,

dateExpression2,

part

)

DATETIME_DIFF(

datetimeExpression1,

datetimeExpression2,

part

)

TIME_DIFF(

timeExpression1,

timeExpression2,

part

)

TIMESTAMP_DIFF(

timestampExpression1,

timestampExpression2,

part

)

TRUNC(dateOrTime, part)

DATE_TRUNC(date, part)

DATETIME_TRUNC(datetime, part)

TIME_TRUNC(time, part)

TIMESTAMP_TRUNC(timestamp,part[, timezone])

[YEAR*, DAY*, WEEK*, MONTH, QUARTER](dateOrTimestamp)

EXTRACT(part FROM timestamp [AT THE ZONE timezone])

ABS(expression)

ABS(expression)

ACOS(expression)

ACOS(expression)

ACOSH(expression)

ACOSH(expression)

ASIN(expression)

ASIN(expression)

ASINH(expression)

ASINH(expression)

ATAN(expression)

ATAN(expression)

ATAN2(y, x)

ATAN2(y, x)

ATANH(expression)

ATANH(expression)

CBRT(expression)

POW(expression, ⅓)

CEIL(expression [, scale])

CEIL(expression)

COS(expression)

COS(expression)

COSH(expression)

COSH(expression)

COT(expression)

1/TAN(expression)

DEGREES(expression)

(expression)*(180/ACOS(-1))

EXP(expression)

EXP(expression)

FACTORIAL(expression)

FLOOR(expression [, scale])

FLOOR(expression)

HAVERSINE(lat1, lon1, lat2, lon2)

ST_DISTANCE(ST_GEOGPOINT(lon1, lat1),

ST_GEOGPOINT(lon2, lat2)

)/1000

LN(expression)

LN(expression)

LOG(base, expression)

LOG(expression [,base])

LOG10(expression)

MOD(expression1, expression2)

MOD(expression1, expression2)

PI()

ACOS(-1)

POW(x, y)

POWER(x, y)

POW(x, y)

POWER(x, y)

RADIANS(expression)

(expression)*(ACOS(-1)/180)

ROUND(expression [, scale])

ROUND(expression, [, scale])

SIGN(expression)

SIGN(expression)

SIN(expression)

SIN(expression)

SINH(expression)

SINH(expression)

SQRT(expression)

SQRT(expression)

SQUARE(expression)

POW(expression, 2)

TAN(expression)

TAN(expression)

TANH(expression)

TANH(expression)

TRUNC(expression [, scale])

TRUNCATE(expression [, scale])

TRUNC(expression [, scale])

GET_PATH , :

string1 || string2

CONCAT(string1, string2)

TO_CODE_POINTS(string1)[OFFSET(0)]

SAFE_CONVERT_BYTES_TO_STRING(

FROM_BASE64(<bytes_input>)

)

SAFE_CONVERT_BYTES_TO_STRING(

FROM_BASE64(<string1>)

)

TO_BASE64(

SAFE_CAST(<string1> AS BYTES)

)

BYTE_LENGTH * 8

CHARINDEX(substring, string)

STRPOS(string, substring)

CODE_POINTS_TO_STRING([number])

CONCAT(string1, string2)

CONCAT(string1, string2)

SAFE_CONVERT_BYTES_TO_STRING(

FROM_HEX(<string1>)

SAFE_CONVERT_BYTES_TO_STRING(

FROM_HEX(<string1>)

TO_HEX(

SAFE_CAST(<string1> AS BYTES))

LENGTH(expression)

LOWER(string)

LPAD(string1, length[, string2])

LTRIM(string1, trim_chars)

MD5,MD5_HEX

MD5(string)

STRPOS(string, substring)

REPEAT(string, integer)

REPLACE(string1, old_chars, new_chars)

number_characters

)

REVERSE(expression)

RTRIM(string, trim_chars)

SHA1(string)

SPLIT(instring, delimiter)[ORDINAL(tokennum)]

SUBSTR(string, 0, integer)

SUBSTR(string, -integer)

LENGTH(expression)

UPPER

IF(REGEXP_CONTAINS,1,0)=1

ARRAY_LENGTH(

REGEXP_EXTRACT_ALL(

source_string,

pattern

)

)

ARRAY_LENGTH(

REGEXP_EXTRACT_ALL(

SUBSTR(source_string, IF(position<= 0, 1, position)),

pattern

)

)

IFNULL(

STRPOS(

source_string,

REGEXP_EXTRACT(

source_string,

pattern)

), 0)

IFNULL(

STRPOS(

SUBSTR(source_string, IF(position<= 0, 1, position)),

REGEXP_EXTRACT(

SUBSTR(source_string, IF(position<= 0, 1, position)),

pattern)

) + IF(position<= 0, 1, position) - 1, 0)

IFNULL(

STRPOS(

SUBSTR(source_string, IF(position<= 0, 1, position)),

REGEXP_EXTRACT_ALL(

SUBSTR(source_string, IF(position<= 0, 1, position)),

pattern

)[SAFE_ORDINAL(occurrence)]

) + IF(position<= 0, 1, position) - 1, 0)

REGEXP_LIKE

IF(REGEXP_CONTAINS,1,0)=1

REGEXP_REPLACE(

source_string,

pattern,

""

)

REGEXP_REPLACE(

source_string,

pattern,

replace_string

)

CASE

WHEN position > LENGTH(source_string) THEN source_string

WHEN position<= 0 THEN

REGEXP_REPLACE(

source_string,

pattern,

""

)

ELSE

CONCAT(

SUBSTR(

source_string, 1, position - 1),

REGEXP_REPLACE(

SUBSTR(source_string, position),

pattern,

replace_string

)

)

END

REGEXP_EXTRACT(

source_string,

pattern

)

REGEXP_EXTRACT(

SUBSTR(source_string, IF(position<= 0, 1, position)),

pattern

)

REGEXP_EXTRACT_ALL(

SUBSTR(source_string, IF(position<= 0, 1, position)),

pattern

)[SAFE_ORDINAL(occurrence)]

IF(REGEXP_CONTAINS,1,0)=1

(Unary) (+'5')

CAST("5" AS NUMERIC)

a + b

a + b

(Unary) (-'5')

(-1) * CAST("5" AS NUMERIC)

a - b

a - b

date1 - date2

date1 - 365

DATE_DIFF(date1, date2, date_part)DATE_SUB(date1, date2, date_part)

a * b

a * b

a / b

a / b

a % b

MOD(a, b)

SELECT ...INTERSECTSELECT ...

SELECT ...

SELECT...

SELECT ...MINUSSELECT ...

SELECT ...EXCEPTSELECT …

SELECT ...EXCEPT DISTINCTSELECT ...

SELECT ...UNIONSELECT ...

SELECT ...UNION ALLSELECT ...

SELECT ...UNION DISTINCTSELECT ...

SELECT ...UNION ALLSELECT ...

SELECT ...FROM ...WHERE col <operator>ALL …SELECT ...FROM ...WHERE col <operator>ANY ...

SELECT ...FROM ...

WHERE[NOT] EXISTS...

SELECT ...FROM ...

WHERE[NOT] EXISTS...

SELECT ...FROM ...

WHERE[NOT] IN...

SELECT ...FROM ...

WHERE[NOT] IN...

SELECT * FROM table1

UNION

SELECT * FROM table2

EXCEPT

SELECT * FROM table3

SELECT * FROM table1

UNION ALL

(

SELECT * FROM table2

EXCEPT

SELECT * FROM table3

)

INSERT [OVERWRITE] INTO table

VALUES [... | DEFAULT | NULL] ...

INSERT [INTO] table (column1 [, ...])

INSERT INTO table (column1 [, ...])SELECT...FROM ...

INSERT [INTO] table (column1, [,...])

SELECT ...

FROM ...

INSERT [OVERWRITE] ALL <intoClause>...INSERT [OVERWRITE] {FIRST | ALL}{WHEN condition THEN <intoClause>}

[...]

[ELSE <intoClause>]

UPDATE tableSET col = value [,...][FROM ...][WHERE ...]

UPDATE table

SET column = expression [,...]

[FROM ...]

WHERE TRUE

DELETE FROM table_name[USING ...]

[WHERE ...]

TRUNCATE [TABLE] [IF EXISTS] table_name

DELETE [FROM] table_name [alias]

WHERE ...

MERGE INTO targetUSING sourceON target.key = source.keyWHEN MATCHED AND source.filter ='Filter_exp' THEN

UPDATE SET target.col1 = source.col1, target.col1 = source.col2,

...

MERGE target

USING source

ON target.key = source.key

WHEN MATCHED AND source.filter = 'filter_exp' THEN

UPDATE SET

target.col1 = source.col1,

target.col2 = source.col2,

...

CREATE DATABASE <name>

bq mk <name>

CREATE OR REPLACE DATABASE <name>

CREATE TRANSIENT DATABASE <name>

CREATE DATABASE IF NOT EXISTS <name>

CREATE DATABASE <name>

CLONE <source_db>

[ { AT | BEFORE }

( { TIMESTAMP => <timestamp> |

OFFSET => <time_difference> |

STATEMENT => <id> } ) ]

CREATE DATABASE <name>

DATA_RETENTION_TIME_IN_DAYS = <num>

CREATE DATABASE <name>

DEFAULT_DDL_COLLATION = '<collation_specification>'

CREATE DATABASE <name>

COMMENT = '<string_literal>'

bq mk \

--description "<string_literal>" \

<name>

CREATE DATABASE <name>

FROM SHARE <provider_account>.<share_name>

CREATE DATABASE <name>

AS REPLICA OF

<region>.<account>.<primary_db_name>

AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = { TRUE | FALSE }

bq mk --transfer_config \

--target_dataset = <name> \

--data_source = cross_region_copy \ --params='

{"source_dataset_id":"<primary_db_name>"

,"source_project_id":"<project_id>"

,"overwrite_destination_table":"true"}'

ALTER DATABASE [ IF EXISTS ] <name> RENAME TO <new_db_name>

ALTER DATABASE <name>

SWAP WITH <target_db_name>

ALTER DATABASE <name>

SET

[DATA_RETENTION_TIME_IN_DAYS = <num>]

[ DEFAULT_DDL_COLLATION = '<value>']

ALTER DATABASE <name>

SET COMMENT = '<string_literal>'

bq update \

--description "<string_literal>" <name>

ALTER DATABASE <name>

ENABLE REPLICATION TO ACCOUNTS <snowflake_region>.<account_name>

[ , <snowflake_region>.<account_name> ... ]

ALTER DATABASE <name>

DISABLE REPLICATION [ TO ACCOUNTS <snowflake_region>.<account_name>

[ , <snowflake_region>.<account_name> ... ]]

ALTER DATABASE <name>

SET AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = { TRUE | FALSE }

ALTER DATABASE <name> REFRESH

ALTER DATABASE <name>

ENABLE FAILOVER TO ACCOUNTS <snowflake_region>.<account_name>

[ , <snowflake_region>.<account_name> ... ]

ALTER DATABASE <name>

DISABLE FAILOVER [ TO ACCOUNTS <snowflake_region>.<account_name>

[ , <snowflake_region>.<account_name> ... ]]

ALTER DATABASE <name>

PRIMARY

DROP DATABASE [ IF EXISTS ] <name>

[ CASCADE | RESTRICT ]

bq rm -r -f -d <name>

Where

-f is to skip confirmation for execution

SHOW DATABASES

bq show <dataset_name>

SHOW TERSE DATABASES

SHOW DATABASES HISTORY

[LIKE '<pattern>']

[STARTS WITH '<name_string>']

LIMIT <rows> [FROM '<name_string>']

bq ls \

--max_results <rows>

CREATE TABLE table_name

(

col1 data_type1 NOT NULL,

col2 data_type2 NULL,

col3 data_type3 UNIQUE,

col4 data_type4 PRIMARY KEY,

col5 data_type5

)

CREATE TABLE table_name

(

col1 data_type1 NOT NULL,

col2 data_type2,

col3 data_type3,

col4 data_type4,

col5 data_type5,

)

CREATE TABLE table_name

(

col1 data_type1[,...]

table_constraints

)

[UNIQUE(column_name [, ... ])]

[PRIMARY KEY(column_name [, ...])]

[FOREIGN KEY(column_name [, ...])

REFERENCES reftable [(refcolumn)]

CREATE TABLE table_name

(

col1 data_type1[,...]

)

PARTITION BY column_name

CLUSTER BY column_name [, ...]

CREATE TABLE table_name

LIKE original_table_name

CREATE TABLE table_name

(

col1 data_type1

)

BACKUP NO

CREATE TABLE table_name

(

col1 data_type1

)

table_attributes

[DISTSTYLE {AUTO|EVEN|KEY|ALL}]

[DISTKEY (column_name)]

[[COMPOUND|INTERLEAVED] SORTKEY

(column_name [, ...])]

CREATE TABLE table_name

AS SELECT ...

CREATE TABLE table_name

AS SELECT ...

CREATE TABLE IF NOT EXISTS table_name

...

CREATE TABLE IF NOT EXISTS table_name

...

ALTER TABLE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER TABLE [IF EXISTS] <name>

SET OPTIONS (friendly_name="<new_name>")

ALTER TABLE <name>

SWAP WITH <target_db_name>

ALTER TABLE <name>

SET

[DEFAULT_DDL_COLLATION = '<value>']

ALTER TABLE <name>

SET

[DATA_RETENTION_TIME_IN_DAYS = <num>]

ALTER TABLE [IF EXISTS] <name>

SET OPTIONS (expiration_timestamp=<timestamp>)

ALTER TABLE <name>

SET

COMMENT = '<string_literal>'

ALTER TABLE [IF EXISTS] <name>

SET OPTIONS (description='<string_literal>')

DROP TABLE [IF EXISTS] <table_name>

[CASCADE | RESTRICT]

bq rm -r -f -d <dataset_name>.<table_name>

Where

UNDROP TABLE <table_name>

bq cp \ <dataset_name>.<table_name>@<unix_timestamp> <dataset_name>.<new_table_name>

table

((<col_name> <col_type> AS <expr> )

| (<part_col_name> <col_type> AS <part_expr>)[ inlineConstraint ]

[ , ... ] )

LOCATION = externalStage

FILE_FORMAT =

({FORMAT_NAME='<file_format_name>'

|TYPE=source_format [formatTypeOptions]})

Where:

externalStage = @[namespace.]ext_stage_name[/path]

[1]bq mk \

--external_table_definition=definition_file \

dataset.table

OR

[2]bq mk \

--external_table_definition=schema_file@source_format={Cloud Storage URI | drive_URI} \

dataset.table

OR

[3]bq mk \

--external_table_definition=schema@source_format = {Cloud Storage URI | drive_URI} \

dataset.table

CREATE [OR REPLACE] EXTERNAL TABLE [IF EXISTS]

<table_name>

((<col_name> <col_type> AS <expr> )

[ , ... ] )

[PARTITION BY (<identifier>, ...)]

LOCATION = externalStage

[REFRESH_ON_CREATE = {TRUE|FALSE}]

[AUTO_REFRESH = {TRUE|FALSE}]

[PATTERN = '<regex_pattern>']

FILE_FORMAT = ({FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET} [ formatTypeOptions]})

[COPY GRANTS]

[COMMENT = '<string_literal>']

bq mk \

--external_table_definition=definition_file \

dataset.table

CREATE VIEW view_name AS SELECT ...

CREATE VIEW view_name AS SELECT ...

CREATE OR REPLACE VIEW view_name AS SELECT ...

view_name AS SELECT ...

CREATE VIEW view_name

(column_name, ...)

AS SELECT ...

CREATE VIEW view_name

AS SELECT ...

view_name

OPTIONS(view_option_list)

AS SELECT ...

CREATE VIEW view_name

AS SELECT ...

WITH NO SCHEMA BINDING

CREATE [ OR REPLACE ] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

s

CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS TABLE (col_name, col_data_type[,..])

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

CREATE [SECURE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

CREATE FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[VOLATILE | IMMUTABLE]

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS [' | $$]

sql_function_definition

[' | $$]

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[COMMENT = '<string_literal>']

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION function_name

(x integer, y integer)

RETURNS integer

AS $$

SELECT x + y

$$

CREATE [OR REPLACE] FUNCTION function_name

(x ANY TYPE, y ANY TYPE)

AS

SELECT x + y

DROP FUNCTION [IF EXISTS]

function_name

([arg_data_type, ... ])

DROP FUNCTION [IF EXISTS] dataset_name.function_name

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

AS procedure_definition;

procedure_name

([arg_mode arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;

arg_mode: IN | OUT | INOUT

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

AS

$$

javascript_code

$$;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

statement_list

END;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[{CALLED ON NULL INPUT | {RETURNS NULL ON NULL INPUT | STRICT}}]

AS procedure_definition;

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[VOLATILE | IMMUTABLE]

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[COMMENT = '<string_literal>']

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[EXECUTE AS { CALLER | OWNER }]

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;

DROP PROCEDURE [IF EXISTS]

procedure_name

([arg_data_type, ... ])

DROP PROCEDURE [IF EXISTS] dataset_name.procedure_name

BEGIN [ { WORK | TRANSACTION } ] [ NAME <name> ];START_TRANSACTION [ name <name> ];

COMMIT;

ROLLBACK;

SHOW LOCKS [ IN ACCOUNT ];SHOW TRANSACTIONS [ IN ACCOUNT ];Note: If the user has the ACCOUNTADMIN role, the user can see locks/transactions for all users in the account.