Snowflake SQL 変換ガイド

SELECT TOP ...

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)

)

SELECT AS VALUE STRUCT(1, 2, 3)

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

SELECT* FROM table SAMPLE(10)

SELECT* FROM table

TABLESAMPLE

BERNOULLI (0.1 PERCENT)

SELECT * FROM table1 AT(TIMESTAMP => timestamp) SELECT * FROM table1 BEFORE(STATEMENT => statementID)

SELECT * FROM table

FOR SYSTEM_TIME AS OF timestamp

@[namespace]<stage_name>[/path]

BigQuery は、ステージされたファイルの概念をサポートしていません。

SELECT*

FROM table

START WITH predicate

CONNECT BY

[PRIOR] col1 = [PRIOR] col2

[, ...]

...

BigQuery には、Snowflake の CONNECT BY に対応する直接的な代替手段はありません。

SELECT col1, col2 FROM table1, table2 WHERE 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 t1 LEFT JOIN table2 as t2

ON t1.col = t2.col

SELECT col1 as one, col2 as two

FROM table GROUP BY (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY ROLLUP (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY ROLLUP (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY GROUPING SETS (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY GROUPING SETS (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY CUBE (one,2)

SELECT col1 as one, col2 as two

FROM table GROUP 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, col2 FROM table QUALIFY ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) = 1;

FROM ( SELECT col1, col2

ROW NUMBER() OVER (PARTITION BY col1 ORDER by col2) RN FROM 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 DISTINCT FARM_FINGERPRINT(TO_JSON_STRING(t)) AS h

FROM TA AS t

ORDER BY h

LIMIT k),

minhash_B AS (

SELECT DISTINCT FARM_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

CASE COUNT(*)

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

SELECT HLL([DISTINCT] expression1, [,expression2])

[OVER ...]

SELECT HLL_COUNT.EXTRACT(sketch) FROM (

SELECT HLL_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 ...]

SELECT AVG(independent) [OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

REGR_AVGY(dependent, independent)

[OVER ...]

SELECT AVG(dependent) [OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

REGR_COUNT(dependent, independent)

[OVER ...]

SELECT COUNT(*) [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

WHEN VAR_POP(independent) = 0

THEN NULL

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

THEN 1

ELSE POWER(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 ...]

SELECT COUNT(*)*VAR_POP(independent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_SYY(dependent, independent)

[OVER ...]

SELECT COUNT(*)*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 x expression1 & 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 ] BETWEEN lower AND upper

(expression >= lower AND expression <= upper)

BOOLAND(expression1, expression2)

LOGICAL_AND(x)

FROM UNNEST([expression1, expression2]) AS x

expression1 AND expression2

BOOLNOT(expression1)

NOT expression

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

expression1 OR 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 ...

expression1 IS [ NOT ] DISTINCT FROM expression2

expression IS [ NOT ] NULL

expression IS [ 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()

BigQuery で使用されていないコンセプト

CURRENT_DATABASE()

SELECT catalog_name

FROM INFORMATION_SCHEMA.SCHEMATA

プロジェクト名のテーブルが返されます。直接的な比較ではありません。

CURRENT_DATE[()]

CURRENT_DATE([timezone])

CURRENT_REGION()

SELECT location

FROM INFORMATION_SCHEMA.SCHEMATA

CURRENT_ROLE()

BigQuery で使用されていないコンセプト

CURRENT_SCHEMA()

SELECT schema_name

FROM INFORMATION_SCHEMA.SCHEMATA

プロジェクトまたはリージョンで使用可能なすべてのデータセット（スキーマ）のテーブルが返されます。直接的な比較ではありません。

CURRENT_SCHEMAS()

BigQuery で使用されていないコンセプト

CURRENT_SESSION()

BigQuery で使用されていないコンセプト

CURRENT_STATEMENT()

SELECT query

FROM INFORMATION_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

FROM INFORMATION_SCHEMA.JOBS_BY_*

CURRENT_USER[()]

SESSION_USER()

SELECT user_email

FROM INFORMATION_SCHEMA.JOBS_BY_*

CURRENT_VERSION()

BigQuery で使用されていないコンセプト

CURRENT_WAREHOUSE()

SELECT catalg_name

FROM INFORMATION_SCHEMA.SCHEMATA

LAST_QUERY_ID([num])

SELECT job_id

FROM INFORMATION_SCHEMA.JOBS_BY_*

LAST_TRANSACTION()

SELECT job_id

FROM INFORMATION_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: それ以外の場合は FALSE: 0

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

CAST(expression AS BOOL)

• INT64
TRUE: それ以外の場合は 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,

expression1,

expression2

)

DATE_DIFF(

dateExpression1,

dateExpression2,

part

)

DATETIME_DIFF(

datetimeExpression1,

datetimeExpression2,

part

)

TIME_DIFF(

timeExpression1,

timeExpression2,

part

)

TIMESTAMP_DIFF(

timestampExpression1,

timestampExpression2,

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 ... INTERSECT SELECT ...

SELECT ...

SELECT...

SELECT ... MINUS SELECT ...

SELECT ... EXCEPT SELECT …

SELECT ... EXCEPT DISTINCT SELECT ...

SELECT ... UNION SELECT ...

SELECT ... UNION ALL SELECT ...

SELECT ... UNION DISTINCT SELECT ...

SELECT ... UNION ALL SELECT ...

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 table SET 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 target USING source ON target.key = source.key WHEN 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 $$

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.