Amazon Redshift SQL 转换指南

本文档详细介绍了 Amazon Redshift 和 BigQuery 在 SQL 语法方面的异同,以帮助您规划迁移。使用批量 SQL 转换来批量迁移 SQL 脚本,或使用交互式 SQL 转换来转换临时查询。

本指南的目标受众是企业架构师、数据库管理员、应用开发者和 IT 安全专家。此外,还假设您熟悉 Amazon Redshift。

数据类型

本部分介绍了 Amazon Redshift 和 BigQuery 中的数据类型之间的等效项。

Amazon Redshift BigQuery 备注
数据类型 别名 数据类型
SMALLINT INT2 INT64 Amazon Redshift 的 SMALLINT 为 2 个字节,而 BigQuery 的 INT64 为 8 个字节。
INTEGER

INT, INT4

INT64 Amazon Redshift 的 INTEGER 为 4 个字节,而 BigQuery 的 INT64 为 8 个字节。
BIGINT INT8 INT64 Amazon Redshift 的 BIGINT 和 BigQuery 的 INT64 均为 8 个字节。
DECIMAL NUMERIC NUMERIC
REAL FLOAT4 FLOAT64 Amazon Redshift 的 REAL 为 4 个字节,而 BigQuery 的 FLOAT64 为 8 个字节。
DOUBLE PRECISION

FLOAT8, FLOAT

FLOAT64
BOOLEAN BOOL BOOL Amazon Redshift 的 BOOLEAN 可以使用 TRUEttrueyyes1 作为 true 的有效字面量值。BigQuery 的 BOOL 数据类型使用不区分大小写的 TRUE
CHAR

CHARACTER, NCHAR, BPCHAR

STRING
VARCHAR

CHARACTER VARYING, NVARCHAR, TEXT

STRING
DATE DATE
TIMESTAMP TIMESTAMP WITHOUT TIME ZONE DATETIME
TIMESTAMPTZ

TIMESTAMP WITH TIME ZONE

TIMESTAMP 注意:在 BigQuery 中,当解析时间戳或设置时间戳格式以用于显示时,系统会使用时区。字符串格式的时间戳可能包含时区,但当 BigQuery 解析字符串时,会采用等效的世界协调时间 (UTC) 存储该时间戳。如果未明确指定时区,则系统会使用默认时区,即世界协调时间 (UTC)。支持时区名称或使用 (-|+)HH:MM 表示相对于世界协调时间 (UTC) 的偏移量,但不支持时区缩写(如 PDT)。
GEOMETRY GEOGRAPHY 支持查询地理空间数据。

BigQuery 还具有以下数据类型,这些数据类型没有直接的 Amazon Redshift 模拟:

隐式转换类型

迁移到 BigQuery 时,您需要将大多数 Amazon Redshift 隐式转换转换为 BigQuery 的显式转换,但 BigQuery 隐式转换的以下数据类型除外。

BigQuery 会对以下数据类型执行隐式转换:

从 BigQuery 类型 到 BigQuery 类型

INT64

FLOAT64

INT64

NUMERIC

NUMERIC

FLOAT64

BigQuery 还会对以下字面量执行隐式转换:

从 BigQuery 类型 到 BigQuery 类型
STRING 字面量
(例如“2008-12-25”)

DATE

STRING 字面量
(例如“2008-12-25 15:30:00”)

TIMESTAMP

STRING 字面量
(例如“2008-12-25T07:30:00”)

DATETIME

STRING 字面量
(例如“15:30:00”)

TIME

显式转换类型

您可以使用 BigQuery 的 CAST(expression AS type) 函数或任何 DATETIMESTAMP 转换函数来转换 BigQuery 不会隐式转换的 Amazon Redshift 数据类型。

迁移查询时,请将 Amazon Redshift CONVERT(type, expression) 函数(或 :: 语法)的任何发生实例更改为 BigQuery 的 CAST(expression AS type) 函数,如 数据类型格式设置函数部分中的表所示。

查询语法

本部分介绍了 Amazon Redshift 和 BigQuery 在查询语法方面的差异。

SELECT 语句

大多数 Amazon Redshift SELECT 语句都与 BigQuery 兼容。下表列出了一些细微差异。

Amazon Redshift BigQuery

SELECT TOP number expression
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


注意:Redshift 支持在同一 SELECT 语句中创建和引用别名。

SELECT
x/total AS probability,
ROUND(100 * (x/total), 1) AS pct
FROM raw_data

BigQuery 还支持 SELECT 语句中的以下表达式,这些表达式没有 Amazon Redshift 等效项:

FROM 子句

查询中的 FROM 子句会列出要从中选择数据的表引用。在 Amazon Redshift 中,可能的表引用包括表、视图和子查询。BigQuery 支持所有这些表引用。

您可以使用以下语句在 FROM 子句中引用 BigQuery 表:

  • [project_id].[dataset_id].[table_name]
  • [dataset_id].[table_name]
  • [table_name]

BigQuery 还支持其他表引用:

JOIN 类型

Amazon Redshift 和 BigQuery 都支持以下联接类型:

  • [INNER] JOIN
  • LEFT [OUTER] JOIN
  • RIGHT [OUTER] JOIN
  • FULL [OUTER] JOIN
  • CROSS JOIN 和等效的隐式逗号交叉联接

下表列出了一些细微差异。

Amazon Redshift BigQuery

SELECT col
FROM table1
NATURAL INNER JOIN
table2

SELECT col1
FROM table1
INNER JOIN
table2
USING (col1, col2 [, ...])


注意:在 BigQuery 中,除非子句是 CROSS JOIN 或其中一个联接表是数据类型或数组中的某个字段,否则 JOIN 子句需要 JOIN 条件。

WITH 子句

BigQuery WITH 子句包含一个或多个已命名的子查询,当后续 SELECT 语句引用这些子查询时,将执行这些子查询。Amazon Redshift WITH 子句的行为与 BigQuery 相同,不同之处在于您可以计算一次子句并重复使用其结果。

集合运算符

Amazon Redshift 集合运算符BigQuery 集合运算符之间存在一些细微差异。但是,Amazon Redshift 中可进行的所有集合运算都在 BigQuery 中可复制。

Amazon Redshift BigQuery

SELECT * FROM table1
UNION
SELECT * FROM table2

SELECT * FROM table1
UNION DISTINCT
SELECT * FROM table2

注意:BigQuery 和 Amazon Redshift 都支持 UNION ALL 运算符。

SELECT * FROM table1
INTERSECT
SELECT * FROM table2

SELECT * FROM table1
INTERSECT DISTINCT
SELECT * FROM table2

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

SELECT * FROM table1
EXCEPT DISTINCT
SELECT * FROM table2

SELECT * FROM table1
MINUS
SELECT * FROM table2

SELECT * FROM table1
EXCEPT DISTINCT
SELECT * FROM table2

SELECT * FROM table1
UNION
SELECT * FROM table2
EXCEPT
SELECT * FROM table3

SELECT * FROM table1
UNION ALL
(
SELECT * FROM table2
EXCEPT
SELECT * FROM table3
)


注意:BigQuery 需要使用英文括号来分隔不同的集合运算。如果重复同一集合运算符,则不需要使用英文括号。

ORDER BY 子句

Amazon Redshift ORDER BY 子句与 BigQuery ORDER BY 子句之间存在一些细微差异。

Amazon Redshift BigQuery
在 Amazon Redshift 中,NULL 默认排在最后(升序)。 在 BigQuery 中,NULL 默认排在最前(升序)。

SELECT *
FROM table
ORDER BY expression
LIMIT ALL

SELECT *
FROM table
ORDER BY expression



注意:BigQuery 不使用 LIMIT ALL 语法,但默认情况下 ORDER BY 会对所有行进行排序,从而导致行为与 Amazon Redshift 的 LIMIT ALL 子句相同。我们强烈建议您在每个 ORDER BY 子句中添加 LIMIT 字句。对所有结果行进行排序会降低查询执行性能,但这并非必要。

SELECT *
FROM table
ORDER BY expression
OFFSET 10

SELECT *
FROM table
ORDER BY expression
LIMIT count OFFSET 10



注意:在 BigQuery 中,OFFSET 必须与 LIMIT count 结合使用。请务必将 count INT64 值设置为所需的已排序行数的下限。对所有结果行进行排序
会不必要地降低查询执行性能。

条件

下表展示了 Amazon Redshift 条件或谓词,它们是 Amazon Redshift 所特有的,必须转换为其 BigQuery 等效项。

Amazon Redshift BigQuery

a = ANY (subquery)

a = SOME (subquery)

a IN subquery

a <> ALL (subquery)

a != ALL (subquery)

a NOT IN subquery

a IS UNKNOWN

expression ILIKE pattern

a IS NULL

LOWER(expression) LIKE LOWER(pattern)

expression LIKE pattern ESCAPE 'escape_char'

expression LIKE pattern


注意:BigQuery 不支持自定义转义字符。您必须将两个反斜杠 \\ 用作 BigQuery 的转义字符。

expression [NOT] SIMILAR TO pattern

IF(
LENGTH(
REGEXP_REPLACE(
expression,
pattern,
''
) = 0,
True,
False
)


注意:如果指定了 NOT,请将上述 IF 表达式封装在 NOT 表达式中,如下所示:

NOT(
IF(
LENGTH(...
)

expression [!] ~ pattern

[NOT] REGEXP_CONTAINS(
expression,
regex
)

函数

以下部分列出了 Amazon Redshift 函数及其 BigQuery 等效项。

聚合函数

下表展示了常见的 Amazon Redshift 聚合、聚合分析和近似聚合函数与其 BigQuery 等效项之间的映射。

Amazon Redshift BigQuery
APPROXIMATE COUNT(DISTINCT expression) APPROX_COUNT_DISTINCT(expression)
APPROXIMATE PERCENTILE_DISC(
percentile
) WITHIN GROUP (ORDER BY expression)
APPROX_QUANTILES(expression, 100)
[OFFSET(CAST(TRUNC(percentile * 100) as INT64))]
AVG([DISTINCT] expression) AVG([DISTINCT] expression)
COUNT(expression) COUNT(expression)
LISTAGG(
[DISTINCT] aggregate_expression
[, delimiter] )
[WITHIN GROUP (ORDER BY order_list)]
STRING_AGG(
[DISTINCT] aggregate_expression
[, delimiter]
[ORDER BY order_list] )
MAX(expression) MAX(expression)
MEDIAN(median_expression) PERCENTILE_CONT( median_expression, 0.5 ) OVER()
MIN(expression) MIN(expression)
PERCENTILE_CONT(
percentile
) WITHIN GROUP (ORDER BY expression)
PERCENTILE_CONT(
median_expression,
percentile
) OVER()


注意:不包含聚合用例。
STDDEV([DISTINCT] expression) STDDEV([DISTINCT] expression)
STDDEV_SAMP([DISTINCT] expression) STDDEV_SAMP([DISTINCT] expression)
STDDEV_POP([DISTINCT] expression) STDDEV_POP([DISTINCT] expression)
SUM([DISTINCT] expression) SUM([DISTINCT] expression)
VARIANCE([DISTINCT] expression) VARIANCE([DISTINCT] expression)
VAR_SAMP([DISTINCT] expression) VAR_SAMP([DISTINCT] expression)
VAR_POP([DISTINCT] expression) VAR_POP([DISTINCT] expression)

BigQuery 还提供以下聚合聚合分析近似聚合函数,这些函数在 Amazon Redshift 中没有直接模拟:

按位聚合函数

下表展示了常见的 Amazon Redshift 按位聚合函数与其 BigQuery 等效项之间的映射。

Amazon Redshift BigQuery
BIT_AND(expression) BIT_AND(expression)
BIT_OR(expression) BIT_OR(expression)
BOOL_AND>(expression) LOGICAL_AND(expression)
BOOL_OR(expression) LOGICAL_OR(expression)

BigQuery 还提供以下按位聚合函数,该函数在 Amazon Redshift 中没有直接模拟:

窗口函数

下表展示了常见的 Amazon Redshift 窗口函数与其 BigQuery 等效项之间的映射。BigQuery 中的窗口函数包括分析聚合函数聚合函数导航函数编号函数


Amazon Redshift BigQuery
AVG(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list frame_clause]
)
AVG(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
COUNT(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
COUNT(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
CUME_DIST() OVER
(
[PARTITION BY partition_expression]
[ORDER BY order_list]
)
CUME_DIST() OVER
(
[PARTITION BY partition_expression]
ORDER BY order_list
)
DENSE_RANK() OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
)
DENSE_RANK() OVER
(
[PARTITION BY expr_list]
ORDER BY order_list
)
FIRST_VALUE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
FIRST_VALUE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
LAST_VALUE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
LAST_VALUE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
LAG(value_expr [, offset]) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
)
LAG(value_expr [, offset]) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
)
LEAD(value_expr [, offset]) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
)
LEAD(value_expr [, offset]) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
)
LISTAGG(
[DISTINCT] expression
[, delimiter]
)
[WITHIN GROUP
(ORDER BY order_list)]
OVER (
[PARTITION BY partition_expression] )
STRING_AGG(
[DISTINCT] aggregate_expression
[, delimiter] )
OVER (
[PARTITION BY partition_list]
[ORDER BY order_list] )
MAX(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
MAX(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
MEDIAN(median_expression) OVER
(
[PARTITION BY partition_expression] )
PERCENTILE_CONT(
median_expression,
0.5
)
OVER ( [PARTITION BY partition_expression] )
MIN(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
MIN(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
NTH_VALUE(expression, offset) OVER ( [PARTITION BY window_partition] [ORDER BY window_ordering frame_clause] ) NTH_VALUE(expression, offset) OVER
(
[PARTITION BY window_partition]
ORDER BY window_ordering
[frame_clause]
)
NTILE(expr) OVER
(
[PARTITION BY expression_list]
[ORDER BY order_list]
)
NTILE(expr) OVER
(
[PARTITION BY expression_list]
ORDER BY order_list
)
PERCENT_RANK() OVER
(
[PARTITION BY partition_expression]
[ORDER BY order_list]
)
PERCENT_RANK() OVER
(
[PARTITION BY partition_expression]
ORDER BY order_list
)
PERCENTILE_CONT(percentile)
WITHIN GROUP (ORDER BY expr) OVER
(
[PARTITION BY expr_list] )
PERCENTILE_CONT(expr, percentile) OVER
(
[PARTITION BY expr_list] )
PERCENTILE_DISC(percentile) WITHIN GROUP (ORDER BY expr) OVER
(
[PARTITION BY expr_list]
)
PERCENTILE_DISC(expr, percentile) OVER
(
[PARTITION BY expr_list] )
RANK() OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
)
RANK() OVER
(
[PARTITION BY expr_list]
ORDER BY order_list
)
RATIO_TO_REPORT(ratio_expression) OVER
(
[PARTITION BY partition_expression] )
ratio_expression SUM(ratio_expression) OVER
(
[PARTITION BY partition_expression] )
ROW_NUMBER() OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
)
ROW_NUMBER() OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
)
STDDEV(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
STDDEV(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause] )
STDDEV_SAMP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list
frame_clause]
)
STDDEV_SAMP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
STDDEV_POP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
STDDEV_POP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause] )
SUM(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
SUM(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VAR_POP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VAR_POP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VAR_SAMP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VAR_SAMP(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VARIANCE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)
VARIANCE(expression) OVER
(
[PARTITION BY expr_list]
[ORDER BY order_list]
[frame_clause]
)

条件表达式

下表展示了常见的 Amazon Redshift 条件表达式与其 BigQuery 等效项之间的映射。

Amazon Redshift BigQuery
CASEexpression
WHEN value THEN result
[WHEN...]
[ELSE else_result]
END
CASE expression
WHEN value THEN result
[WHEN...]
[ELSE else_result]
END
COALESCE(expression1[, ...]) COALESCE(expression1[, ...])
DECODE(
expression,
search1, result1
[, search2, result2...]
[, default]
)
CASE expression
WHEN value1 THEN result1
[WHEN value2 THEN result2]
[ELSE default]
END
GREATEST(value [, ...]) GREATEST(value [, ...])
LEAST(value [, ...]) LEAST(value [, ...])
NVL(expression1[, ...]) COALESCE(expression1[, ...])
NVL2(
expression,
not_null_return_value,
null_return_value
)
IF(
expression IS NULL,
null_return_value,
not_null_return_value
)
NULLIF(expression1, expression2) NULLIF(expression1, expression2)

BigQuery 还提供以下条件表达式,这些条件表达式在 Amazon Redshift 中没有直接模拟:

日期和时间函数

下表展示了常见的 Amazon Redshift 日期和时间函数与其 BigQuery 等效项之间的映射。BigQuery 数据和时间函数包括日期函数日期时间函数时间函数时间戳函数

请注意,Amazon Redshift 和 BigQuery 中看似相同的函数可能会返回不同的数据类型。

Amazon Redshift BigQuery
ADD_MONTHS(
date,
integer
)
CAST( DATE_ADD(
date,
INTERVAL integer MONTH
)
AS TIMESTAMP
)
timestamptz_or_timestamp AT TIME ZONE timezone PARSE_TIMESTAMP(
"%c%z",
FORMAT_TIMESTAMP(
"%c%z",
timestamptz_or_timestamp,
timezone
)
)


注意:在解析时间戳或设置时间戳格式以用于显示时,系统会使用时区。字符串格式的时间戳可能包含时区,但当 BigQuery 解析字符串时,会采用等效的世界协调时间 (UTC) 存储该时间戳。如果未明确指定时区,则系统会使用默认时区,即世界协调时间 (UTC)。支持时区名称相对于世界协调时间 (UTC) 的偏移量 (-HH:MM),但不支持时区缩写(如 PDT)。
CONVERT_TIMEZONE(
[source_timezone],
target_timezone,
timestamp
)
PARSE_TIMESTAMP(
"%c%z",
FORMAT_TIMESTAMP(
"%c%z",
timestamp,
target_timezone
)
)


注意:在 BigQuery 中,source_timezone 为世界协调时间 (UTC)。
CURRENT_DATE

注意:采用当前会话时区(默认为世界协调时间 (UTC))返回当前事务的开始日期。
CURRENT_DATE()

注意:采用世界协调时间 (UTC) 时区返回当前语句的开始日期。
DATE_CMP(date1, date2) CASE
WHEN date1 = date2 THEN 0
WHEN date1 > date2 THEN 1
ELSE -1
END
DATE_CMP_TIMESTAMP(date1, date2) CASE
WHEN date1 = CAST(date2 AS DATE)
THEN 0
WHEN date1 > CAST(date2 AS DATE)
THEN 1
ELSE -1
END
DATE_CMP_TIMESTAMPTZ(date, timestamptz) CASE
WHEN date > DATE(timestamptz)
THEN 1
WHEN date < DATE(timestamptz)
THEN -1
ELSE 0
END
DATE_PART_YEAR(date) EXTRACT(YEAR FROM date)
DATEADD(date_part, interval, date) CAST(
DATE_ADD(
date,
INTERVAL interval datepart
)
AS TIMESTAMP
)
DATEDIFF(
date_part,
date_expression1,
date_expression2
)
DATE_DIFF(
date_expression1,
date_expression2,
date_part
)
DATE_PART(date_part, date) EXTRACT(date_part FROM date)
DATE_TRUNC('date_part', timestamp) TIMESTAMP_TRUNC(timestamp, date_part)
EXTRACT(date_part FROM timestamp) EXTRACT(date_part FROM timestamp)
GETDATE() PARSE_TIMESTAMP(
"%c",
FORMAT_TIMESTAMP(
"%c",
CURRENT_TIMESTAMP()
)
)
INTERVAL_CMP(
interval_literal1,
interval_literal2
)
对于 Redshift 中的间隔,一年中有 360 天。在 BigQuery 中,您可以使用以下用户定义的函数 (UDF) 解析 Redshift 间隔并将其转换为秒。

CREATE TEMP FUNCTION
parse_interval(interval_literal STRING) AS (
(select sum(case
when unit in ('minutes', 'minute', 'm' )
then num * 60
when unit in ('hours', 'hour', 'h') then num
* 60 * 60
when unit in ('days', 'day', 'd' ) then num
* 60 * 60 * 24
when unit in ('weeks', 'week', 'w') then num
* 60 * 60 * 24 * 7
when unit in ('months', 'month' ) then num *
60 * 60 * 24 * 30
when unit in ('years', 'year') then num * 60
* 60 * 24 * 360
else num
end)
from (
select
cast(regexp_extract(value,
r'^[0-9]*\.?[0-9]+') as numeric) num,
substr(value, length(regexp_extract(value,
r'^[0-9]*\.?[0-9]+')) + 1) unit
from
UNNEST(
SPLIT(
replace(
interval_literal, ' ', ''), ',')) value
)));


如需比较间隔字面量,请执行:

IF(
parse_interval(interval_literal1) >
parse_interval(interval_literal2),
1,
IF(
parse_interval(interval_literal1) >
parse_interval(interval_literal2),
-1,
0
)
)
LAST_DAY(date) DATE_SUB(
DATE_ADD(
date,
INTERVAL 1 MONTH
),
INTERVAL 1 DAY
)
MONTHS_BETWEEN(
date1,
date2
)
DATE_DIFF(
date1,
date2,
MONTH
)
NEXT_DAY(date, day) DATE_ADD(
DATE_TRUNC(
date,
WEEK(day)
),
INTERVAL 1 WEEK
)
SYSDATE

注意:采用当前会话时区(默认为世界协调时间 (UTC))返回当前事务的开始时间戳。
CURRENT_TIMESTAMP()

注意:采用世界协调时间 (UTC) 时区返回当前语句的开始时间戳。
TIMEOFDAY() FORMAT_TIMESTAMP(
"%a %b %d %H:%M:%E6S %E4Y %Z",
CURRENT_TIMESTAMP())
TIMESTAMP_CMP(
timestamp1,
timestamp2
)
CASE
WHEN timestamp1 = timestamp2
THEN 0
WHEN timestamp1 > timestamp2
THEN 1
ELSE -1
END
TIMESTAMP_CMP_DATE(
timestamp,
date
)
CASE
WHEN
EXTRACT(
DATE FROM timestamp
) = date
THEN 0
WHEN
EXTRACT(
DATE FROM timestamp) > date
THEN 1
ELSE -1
END
TIMESTAMP_CMP_TIMESTAMPTZ(
timestamp,
timestamptz
)


注意:Redshift 会比较采用用户会话定义的时区的时间戳。默认用户会话时区为世界协调时间 (UTC)。
CASE
WHEN timestamp = timestamptz
THEN 0
WHEN timestamp > timestamptz
THEN 1
ELSE -1
END


注意:BigQuery 会比较采用世界协调时间 (UTC) 时区的时间戳。
TIMESTAMPTZ_CMP(
timestamptz1,
timestamptz2
)


注意:Redshift 会比较采用用户会话定义的时区的时间戳。默认用户会话时区为世界协调时间 (UTC)。
CASE
WHEN timestamptz1 = timestamptz2
THEN 0
WHEN timestamptz1 > timestamptz2
THEN 1
ELSE -1
END


注意:BigQuery 会比较采用世界协调时间 (UTC) 时区的时间戳。
TIMESTAMPTZ_CMP_DATE(
timestamptz,
date
)


注意:Redshift 会比较采用用户会话定义的时区的时间戳。默认用户会话时区为世界协调时间 (UTC)。
CASE
WHEN
EXTRACT(
DATE FROM timestamptz) = date
THEN 0
WHEN
EXTRACT(
DATE FROM timestamptz) > date
THEN 1
ELSE -1
END


注意:BigQuery 会比较采用世界协调时间 (UTC) 时区的时间戳。
TIMESTAMPTZ_CMP_TIMESTAMP(
timestamptz,
Timestamp
)


注意:Redshift 会比较采用用户会话定义的时区的时间戳。默认用户会话时区为世界协调时间 (UTC)。
CASE
WHEN timestamp = timestamptz
THEN 0
WHEN timestamp > timestamptz
THEN 1
ELSE -1
END


注意:BigQuery 会比较采用世界协调时间 (UTC) 时区的时间戳。
TIMEZONE(
timezone,
Timestamptz_or_timestamp
)
PARSE_TIMESTAMP(
"%c%z", FORMAT_TIMESTAMP(
"%c%z",
timestamptz_or_timestamp,
timezone
)
)


注意:在解析时间戳或设置时间戳格式以用于显示时,系统会使用时区。字符串格式的时间戳可能包含时区,但当 BigQuery 解析字符串时,会采用等效的世界协调时间 (UTC) 存储该时间戳。如果未明确指定时区,则系统会使用默认时区,即世界协调时间 (UTC)。支持时区名称相对于世界协调时间 (UTC) 的偏移量 (-HH:MM),但不支持时区缩写(如 PDT)。
TO_TIMESTAMP(timestamp, format) PARSE_TIMESTAMP(
format,
FORMAT_TIMESTAMP(
format,
timestamp
)
)


注意:BigQuery 遵循一组不同的格式元素。在解析时间戳或设置时间戳格式以用于显示时,系统会使用时区。字符串格式的时间戳可能包含时区,但当 BigQuery 解析字符串时,会采用等效的世界协调时间 (UTC) 存储该时间戳。如果未明确指定时区,则系统会使用默认时区,即世界协调时间 (UTC)。格式字符串支持时区名称相对于世界协调时间 (UTC) 的偏移量 (-HH:MM),但不支持时区缩写(如 PDT)。
TRUNC(timestamp) CAST(timestamp AS DATE)

BigQuery 还提供以下日期和时间函数,这些函数在 Amazon Redshift 中没有直接模拟:

数学运算符

下表展示了常见的 Amazon Redshift 数学运算符与其 BigQuery 等效项之间的映射。

Amazon Redshift BigQuery

X + Y

X + Y

X - Y

X - Y

X * Y

X * Y

X / Y


注意:如果运算符
执行整数除法(即 XY 均为整数),则系统会返回整数。如果运算符执行非整数除法,则系统会返回非整数。
如果是整数除法:
CAST(FLOOR(X / Y) AS INT64)

如果不是整数除法:

CAST(X / Y AS INT64)


注意:BigQuery 中的除法会返回非整数。
为防止除法运算出错(除数为零的错误),请使用 SAFE_DIVIDE(X, Y) IEEE_DIVIDE(X, Y)

X % Y

MOD(X, Y)


注意:为防止除法运算出错(除数为零的错误),请使用 SAFE.MOD(X, Y)SAFE.MOD(X, 0) 会导致 0。

X ^ Y

POW(X, Y)

POWER(X, Y)


注意:与 Amazon Redshift 不同,BigQuery 中的 ^ 运算符执行按位异或运算。

| / X

SQRT(X)


注意:为防止平方根运算出错(负输入),请使用 SAFE.SQRT(X)SAFE.SQRT(X) 为负输入会导致 NULL

|| / X

SIGN(X) * POWER(ABS(X), 1/3)


注意:如果 X 是小于 0 的有限值且 Y 是非整数,则 BigQuery 的 POWER(X, Y) 会返回错误。

@ X

ABS(X)

X << Y

X << Y


注意:如果第二个操作数 Y 大于或等于第一个操作数 X 的位长度(例如,如果 X 的类型是 INT64,则其位长度是 64),则此运算符会返回 0 或 b'\x00' 字节序列。如果 Y 为负数,则此运算符会抛出错误。

X >> Y

X >> Y


注意:将第一个操作数 X 向右移位。此运算符不会对带符号的类型执行符号位扩展(它会在左侧的空位上填入 0)。如果第二个操作数 Y 大于或
等于第一个操作数 X 的位长度(例如,如果 X 的类型是 INT64,则其位长度是 64),则此运算符返回 0,或者返回 b'\x00' 字节序列。如果 Y 为负,则此运算符引发一个错误。

X & Y

X & Y

X | Y

X | Y

~X

~X

BigQuery 还提供以下数学运算符,该运算符在 Amazon Redshift 中没有直接模拟:

  • X ^ Y(按位异或)

数学函数

Amazon Redshift BigQuery
ABS(number) ABS(number)
ACOS(number) ACOS(number)
ASIN(number) ASIN(number)
ATAN(number) ATAN(number)
ATAN2(number1, number2) ATAN2(number1, number2)
CBRT(number) POWER(number, 1/3)
CEIL(number) CEIL(number)
CEILING(number) CEILING(number)
CHECKSUM(expression) FARM_FINGERPRINT(expression)
COS(number) COS(number)
COT(number) 1/TAN(number)
DEGREES(number) number*180/ACOS(-1)
DEXP(number) EXP(number)
DLOG1(number) LN(number)
DLOG10(number) LOG10(number)
EXP(number) EXP(number)
FLOOR(number) FLOOR(number)
LNnumber) LN(number)
LOG(number) LOG10(number)
MOD(number1, number2) MOD(number1, number2)
PI ACOS(-1)
POWER(expression1, expression2) POWER(expression1, expression2)
RADIANS(number) ACOS(-1)*(number/180)
RANDOM() RAND()
ROUND(number [, integer]) ROUND(number [, integer])
SIN(number) SIN(number)
SIGN(number) SIGN(number)
SQRT(number) SQRT(number)
TAN(number) TAN(number)
TO_HEX(number) FORMAT('%x', number)
TRUNC(number [, integer])+-+++ TRUNC(number [, integer])

字符串函数

Amazon Redshift BigQuery
string1 || string2 CONCAT(string1, string2)
BPCHARCMP(string1, string2) CASE
WHEN string1 = string2 THEN 0
WHEN string1 > string2 THEN 1
ELSE -1
END
BTRIM(string [, matching_string]) TRIM(string [, matching_string])
BTTEXT_PATTERN_CMP(string1, string2) CASE
WHEN string1 = string2 THEN 0
WHEN string1 > string2 THEN 1
ELSE -1
END
CHAR_LENGTH(expression) CHAR_LENGTH(expression)
CHARACTER_LENGTH(expression) CHARACTER_LENGTH(expression)
CHARINDEX(substring, string) STRPOS(string, substring)
CHR(number) CODE_POINTS_TO_STRING([number])
CONCAT(string1, string2) CONCAT(string1, string2)

注意:BigQuery 的 CONCAT(...) 支持
串联任意数量的字符串。
CRC32 自定义用户定义的函数
FUNC_SHA1(string) SHA1(string)
INITCAP INITCAP
LEFT(string, integer) SUBSTR(string, 0, integer)
RIGHT(string, integer) SUBSTR(string, -integer)
LEN(expression) LENGTH(expression)
LENGTH(expression) LENGTH(expression)
LOWER(string) LOWER(string)
LPAD(string1, length[, string2]) LPAD(string1, length[, string2])
RPAD(string1, length[, string2]) RPAD(string1, length[, string2])
LTRIM(string, trim_chars) LTRIM(string, trim_chars)
MD5(string) MD5(string)
OCTET_LENGTH(expression) BYTE_LENGTH(expression)
POSITION(substring IN string) STRPOS(string, substring)
QUOTE_IDENT(string) CONCAT('"',string,'"')
QUOTE_LITERAL(string) CONCAT("'",string,"'")
REGEXP_COUNT( source_string, pattern
[,position]
)
ARRAY_LENGTH( REGEXP_EXTRACT_ALL(
source_string,
pattern
)
)


如果指定了 position

ARRAY_LENGTH( REGEXP_EXTRACT_ALL(
SUBSTR(source_string, IF(position <= 0, 1, position)),
pattern
)
)


注意:BigQuery 通过 re2 库提供正则表达式支持;请参阅相关文档了解其正则表达式语法。
REGEXP_INSTR(
source_string,
pattern
[,position
[,occurrence]] )
IFNULL( STRPOS(
source_string, REGEXP_EXTRACT(
source_string,
pattern)
),0)


如果指定了 source_string

REGEXP_REPLACE(
source_string,

pattern,
replace_string
)


如果指定了 position

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)


如果指定了 occurrence

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)


注意:BigQuery 通过
re2 库提供正则表达式支持;请参阅相关文档
了解其正则表达式语法
REGEXP_REPLACE( source_string,
pattern
[, replace_string [, position]]
)
REGEXP_REPLACE(
source_string,
pattern,
""
)


如果指定了 source_string

REGEXP_REPLACE(
source_string,

pattern, replace_string
)


如果指定了 position

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_SUBSTR( source_string, pattern
[, position
[, occurrence]] )
REGEXP_EXTRACT(
source_string, pattern
)


如果指定了 position

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

)


如果指定了 occurrence

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


pattern
)[SAFE_ORDINAL(occurrence)]


注意:BigQuery 通过 re2 库提供正则表达式支持;请参阅相关文档了解其正则表达式语法。
REPEAT(string, integer) REPEAT(string, integer)
REPLACE(string, old_chars, new_chars) REPLACE(string, old_chars, new_chars)
REPLICA(string, integer) REPEAT(string, integer)
REVERSE(expression) REVERSE(expression)
RTRIM(string, trim_chars) RTRIM(string, trim_chars)
SPLIT_PART(string, delimiter, part) SPLIT(
string
delimiter
)SAFE_ORDINAL(part)
STRPOS(string, substring) STRPOS(string, substring)
STRTOL(string, base)
SUBSTRING(
string,
start_position, number_characters )
SUBSTR(
string,
start_position, number_characters )
TEXTLEN(expression) LENGTH(expression)
TRANSLATE(
expression,
characters_to_replace, characters_to_substitute )
可以使用 UDF 实现:

CREATE TEMP FUNCTION
translate(expression STRING,
characters_to_replace STRING, characters_to_substitute STRING) AS ( IF(LENGTH(characters_to_replace) < LENGTH(characters_to_substitute) OR LENGTH(expression) <
LENGTH(characters_to_replace), expression,
(SELECT
STRING_AGG(
IFNULL(
(SELECT ARRAY_CONCAT([c],
SPLIT(characters_to_substitute, ''))[SAFE_OFFSET((
SELECT IFNULL(MIN(o2) + 1,
0) FROM
UNNEST(SPLIT(characters_to_replace,
'')) AS k WITH OFFSET o2
WHERE k = c))]
),
''),
'' ORDER BY o1)
FROM UNNEST(SPLIT(expression, ''))
AS c WITH OFFSET o1
))
);
TRIM([BOTH] string) TRIM(string)
TRIM([BOTH] characters FROM string) TRIM(string, characters)
UPPER(string) UPPER(string)

数据类型格式设置函数

Amazon Redshift BigQuery
CAST(expression AS type) CAST(expression AS type)
expression :: type CAST(expression AS type)
CONVERT(type, expression) CAST(expression AS type)
TO_CHAR(
timestamp_expression, format
)
FORMAT_TIMESTAMP(
format,
timestamp_expression
)


注意:BigQuery 和 Amazon Redshift 在为 timestamp_expression 指定格式字符串的方式方面有所不同。
TO_CHAR(
numeric_expression,
format
)
FORMAT(
format,
numeric_expression
)


注意:BigQuery 和 Amazon Redshift 在为 timestamp_expression 指定格式字符串的方式方面有所不同。
TO_DATE(date_string, format) PARSE_DATE(date_string, format)

注意:BigQuery 和 Amazon Redshift 在为 date_string 指定格式字符串的方式方面有所不同。
TO_NUMBER(string, format) CAST(
FORMAT(
format,
numeric_expression
) TO INT64
)


注意:BigQuery 和 Amazon Redshift 在指定数字格式字符串的方式方面有所不同。

BigQuery 还支持 SAFE_CAST(expression AS typename),如果 BigQuery 无法执行类型转换,则会返回 NULL;例如,SAFE_CAST("apple" AS INT64) 返回 NULL

DML 语法

本部分介绍了 Amazon Redshift 和 BigQuery 在数据管理语言语法方面的差异。

INSERT 语句

Amazon Redshift 为列提供可配置的 DEFAULT 关键字。在 BigQuery 中,可为 null 的列的 DEFAULT 值为 NULL,且所需列不支持 DEFAULT。大多数 Amazon Redshift INSERT 语句都与 BigQuery 兼容。下表显示了例外情况。

Amazon Redshift BigQuery
INSERT INTO table (column1 [, ...])
DEFAULT VALUES
INSERT [INTO] table (column1 [, ...])
VALUES (DEFAULT [, ...])
INSERT INTO table (column1, [,...]) VALUES (
SELECT ...
FROM ...
)
INSERT [INTO] table (column1, [,...])
SELECT ...
FROM ...

BigQuery 还支持使用子查询插入值(其中一个值使用子查询计算得出),Amazon Redshift 不支持此操作。例如:

INSERT INTO table (column1, column2)
VALUES ('value_1', (
SELECT column2
FROM table2
))

COPY 语句

Amazon Redshift 的 COPY 命令会将数据从数据文件或 Amazon DynamoDB 表加载到表中。BigQuery 不使用 SQL COPY 命令加载数据,但您可以使用多种非 SQL 工具和选项的任何一种将数据加载到 BigQuery 表中。您还可以使用 Apache SparkApache Beam 中提供的数据流水线接收器将数据写入 BigQuery 中。

UPDATE 语句

大多数 Amazon Redshift UPDATE 语句都与 BigQuery 兼容。下表展示了例外情况。

Amazon Redshift BigQuery
UPDATE table
SET column = expression [,...] [FROM ...]
UPDATE table
SET column = expression [,...]
[FROM ...]
WHERE TRUE


注意:BigQuery 中的所有 UPDATE 语句都需要 WHERE 关键字,后跟一个条件。
UPDATE table
SET column = DEFAULT [,...] [FROM ...]
[WHERE ...]
UPDATE table
SET column = NULL [, ...]
[FROM ...]
WHERE ...


注意:BigQuery 的 UPDATE 命令不支持 DEFAULT 值。

如果 Amazon Redshift UPDATE 语句不包含 WHERE 子句,则 BigQuery UPDATE 语句应具有 WHERE TRUE 条件。

DELETETRUNCATE 语句

DELETETRUNCATE 语句都是在不影响表架构或索引的情况下从表中移除行的方法。

在 Amazon Redshift 中,建议使用 TRUNCATE 语句而不是非限定的 DELETE 语句,因为前者执行速度更快,并且之后不需要 VACUUMANALYZE 操作。不过,您可以使用 DELETE 语句达到相同的效果。

在 BigQuery 中,DELETE 语句必须具有 WHERE 子句。 如需详细了解 BigQuery 中的 DELETE,请参阅 DML 文档中的 BigQuery DELETE 示例

Amazon Redshift BigQuery
DELETE [FROM] table_name

TRUNCATE [TABLE] table_name
DELETE FROM table_name
WHERE TRUE


BigQuery DELETE 语句需要 WHERE 子句。
DELETE FROM table_name
USING other_table
WHERE table_name.id=other_table.id
DELETE FROM table_name
WHERE table_name.id IN (
SELECT id
FROM other_table
)


DELETE FROM table_name
WHERE EXISTS (
SELECT id
FROM other_table
WHERE table_name.id = other_table.id )


在 Amazon Redshift 中,USING 允许在 WHERE 子句中引用附加表。在 BigQuery 中,可以通过在 WHERE 子句中使用子查询来实现此目的。

MERGE 语句

MERGE 语句可以将 INSERTUPDATEDELETE 操作合并为一个 upsert 语句,并以原子方式执行这些操作。对应每个目标行,MERGE 操作必须匹配最多一个源行。

Amazon Redshift 不支持单个 MERGE 命令。但是,您可以通过在事务中执行 INSERTUPDATEDELETE 操作,在 Amazon Redshift 中执行合并操作。

通过替换现有行来合并操作

在 Amazon Redshift 中,可以先使用 DELETE 语句,再使用 INSERT 语句来执行覆盖目标表中所有列的操作。DELETE 语句会移除应更新的行,然后 INSERT 语句会插入更新后的行。BigQuery 表每天最多只能运行 1000 个 DML 语句,因此您应该将 INSERTUPDATEDELETE 语句合并到一个 MERGE 语句中,如下表所示。

Amazon Redshift BigQuery
请参阅通过替换现有行执行合并操作

CREATE TEMP TABLE temp_table;

INSERT INTO temp_table
SELECT *
FROM source
WHERE source.filter = 'filter_exp';

BEGIN TRANSACTION;

DELETE FROM target
USING temp_table
WHERE target.key = temp_table.key;

INSERT INTO target
SELECT *
FROM temp_table;

END TRANSACTION;

DROP TABLE temp_table;
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 TEMP TABLE temp_table;

INSERT INTO temp_table
SELECT *
FROM source
WHERE source.filter = 'filter_exp';

BEGIN TRANSACTION;

UPDATE target SET
col1 = temp_table.col1,
col2 = temp_table.col2
FROM temp_table
WHERE target.key=temp_table.key;

INSERT INTO target
SELECT *
FROM
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

DDL 语法

本部分介绍了 Amazon Redshift 和 BigQuery 在数据定义语言语法方面的差异。

SELECT INTO 语句

在 Amazon Redshift 中,SELECT INTO 语句可用于将查询结果插入到新表中,并合并表创建和插入操作。

Amazon Redshift BigQuery
SELECT expression, ... INTO table
FROM ...
INSERT table
SELECT expression, ...
FROM ...
WITH subquery_table AS ( SELECT ...
)
SELECT expression, ... INTO table
FROM subquery_table
...
INSERT table
WITH subquery_table AS (
SELECT ...
)
SELECT expression, ...
FROM subquery_table
...
SELECT expression
INTO TEMP table
FROM ...

SELECT expression
INTO TEMPORARY table
FROM ...
BigQuery 提供了多种模拟临时表的方法。如需了解详情,请参阅临时表部分。

CREATE TABLE 语句

大多数 Amazon Redshift CREATE TABLE 语句都与 BigQuery 兼容,但以下语法元素在 BigQuery 中不使用:

Amazon Redshift BigQuery
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
)


注意:UNIQUEPRIMARY KEY 限制条件为参考信息,不受 Amazon Redshift 系统强制执行
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
)
where table_constraints are:
[UNIQUE(column_name [, ... ])]
[PRIMARY KEY(column_name [, ...])]
[FOREIGN KEY(column_name [, ...])
REFERENCES reftable [(refcolumn)]


注意:UNIQUEPRIMARY KEY 限制条件为参考信息,不受 Amazon Redshift 系统强制执行
CREATE TABLE table_name
(
col1 data_type1[,...]
)
PARTITION BY column_name
CLUSTER BY column_name [, ...]


注意:BigQuery 不使用 UNIQUEPRIMARY KEYFOREIGN KEY 表限制条件。如需实现这些限制条件在查询执行期间提供的类似优化,请对 BigQuery 表进行分区和聚簇。CLUSTER BY 最多支持 4 列。
CREATE TABLE table_name
LIKE original_table_name
请参考此示例,了解如何使用 INFORMATION_SCHEMA 表将列名称、数据类型和 NOT NULL 限制条件复制到新表。
CREATE TABLE table_name
(
col1 data_type1
)
BACKUP NO


注意:在 Amazon Redshift 中,指定了 BACKUP NO 设置以节省处理时间并减少存储空间。
不使用或不需要 BACKUP NO 表选项,因为 BigQuery 会自动将所有表的历史版本保留长达 7 天,而不会影响处理时间或付费存储空间。
CREATE TABLE table_name
(
col1 data_type1
)
table_attributes
where table_attributes are:
[DISTSTYLE {AUTO|EVEN|KEY|ALL}]
[DISTKEY (column_name)]
[[COMPOUND|INTERLEAVED] SORTKEY
(column_name [, ...])]
BigQuery 支持聚簇,从而能够按排列顺序存储键。
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
...

BigQuery 还支持 DDL 语句 CREATE OR REPLACE TABLE,该语句会覆盖已存在的表。

BigQuery 的 CREATE TABLE 语句还支持以下子句,这些子句没有 Amazon Redshift 等效项:

如需详细了解 BigQuery 中的 CREATE TABLE,请参阅 DML 文档中的 BigQuery CREATE TABLE 示例

临时表

Amazon Redshift 支持临时表,这些表仅在当前会话中可见。您可以通过以下几种方式在 BigQuery 中模拟临时表:

  • 数据集 TTL:创建一个存留时间较短(例如 1 小时)的数据集,这样在该数据集中创建的所有表实际上都是暂时的,因为它们的持续存在时间不会超过该数据集的存留时间。您可以为此数据集的所有表名称添加 temp 前缀,以明确表示这些表是临时表。
  • 表 TTL:使用如下所示的 DDL 语句,创建一个具有表特定的较短存留时间的表:

    CREATE TABLE
    temp.name (col1, col2, ...)
    OPTIONS (expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),
    INTERVAL 1 HOUR));
    

CREATE VIEW 语句

下表展示了 Amazon Redshift 和 BigQuery 中 CREATE VIEW 语句的等效项。

Amazon Redshift BigQuery
CREATE VIEW view_name AS SELECT ...code> CREATE VIEW view_name AS SELECT ...
CREATE OR REPLACE VIEW view_name AS SELECT ... CREATE OR REPLACE VIEW
view_name AS
SELECT ...
CREATE VIEW view_name
(column_name, ...)
AS SELECT ...
CREATE VIEW view_name AS SELECT ...
不受支持。 CREATE VIEW IF NOT EXISTS c view_name
OPTIONS(view_option_list)
AS SELECT …


仅当指定的数据集中不存在要创建的新视图时才创建此视图。
CREATE VIEW view_name
AS SELECT ...
WITH NO SCHEMA BINDING


在 Amazon Redshift 中,需要延迟绑定视图才能引用外部表。
在 BigQuery 中,如需创建视图,所有引用的对象都必须已存在。

BigQuery 允许您查询外部数据源

用户定义的函数 (UDF)

借助 UDF,您可以为自定义操作创建函数。这些函数接受输入列、执行操作,然后以值的形式返回这些操作的结果。

Amazon Redshift 和 BigQuery 都支持使用 SQL 表达式的 UDF。此外,在 Amazon Redshift 中,您可以创建基于 Python 的 UDF;在 BigQuery 中,您可以创建基于 JavaScript 的 UDF

如需了解常见的 BigQuery UDF 库,请参阅 Google Cloud BigQuery 实用程序 GitHub 代码库

CREATE FUNCTION 语法

下表介绍了 Amazon Redshift 和 BigQuery 在 SQL UDF 创建语法方面的差异。

Amazon Redshift BigQuery
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
IMMUTABLE
AS $$
sql_function_definition
$$ LANGUAGE sql
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) AS
sql_function_definition


注意:在 BigQuery SQL UDF 中,返回数据类型是可选的。当查询调用函数时,BigQuery 会通过 SQL 函数体推断出函数的结果类型。
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE } AS $$
sql_function_definition
$$ LANGUAGE sql
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
AS sql_function_definition


注意:在 BigQuery 中,函数易失性不是可配置参数。所有 BigQuery UDF 易失性等效于 Amazon Redshift 的 IMMUTABLE 易失性(即不执行数据库查找,也不使用非直接存在于其参数列表中的信息)。
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
IMMUTABLE
AS $$
SELECT_clause
$$ LANGUAGE sql


注意:Amazon Redshift 仅支持 SQL SELECT 子句作为函数定义。此外,SELECT 子句不能包含任何 FROM, INTO, WHERE, GROUP BY, ORDER BY,LIMIT 子句。
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
AS sql_expression


注意:BigQuery 支持任何 SQL 表达式作为函数定义。但是,不支持引用表、视图或模型。
CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
IMMUTABLE
AS $$
sql_function_definition
$$ LANGUAGE sql
CREATE [OR REPLACE] FUNCTION function_name ([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type AS sql_function_definition

注意:GoogleSQL UDF 中不需要指定语言字面量。默认情况下,BigQuery 会解释 SQL 表达式。此外,Amazon Redshift 美元引用 ($$) is not supported in BigQuery.
CREATE [OR REPLACE] FUNCTION function_name (integer, integer) RETURNS integer IMMUTABLE AS $$ SELECT $1 + $2 $$ LANGUAGE sql CREATE [OR REPLACE] FUNCTION
function_name
(x INT64, y INT64)
RETURNS INT64
AS
SELECT x + y


Note: BigQuery UDFs require all input arguments to be named. The Amazon Redshift argument variables ($1, $2, …) are not supported in BigQuery.
CREATE [OR REPLACE] FUNCTION
function_name
(integer, integer)
RETURNS integer
IMMUTABLE
AS $$
SELECT $1 + $2
$$ LANGUAGE sql


Note: Amazon Redshift does not support ANY TYPE for SQL UDFs. However, it supports using the ANYELEMENT data type in Python-based UDFs.
CREATE [OR REPLACE] FUNCTION
function_name
(x ANY TYPE, y ANY TYPE)
AS
SELECT x + y


Note: BigQuery supports using ANY TYPE as argument type. The function accepts an input of any type for this argument. For more information, see templated parameter in BigQuery.

BigQuery also supports the CREATE FUNCTION IF NOT EXISTS statement, which treats the query as successful and takes no action if a function with the same name already exists.

BigQuery's CREATE FUNCTION statement also supports creating TEMPORARY or TEMP functions, which do not have an Amazon Redshift equivalent.

See calling UDFs for details on executing a BigQuery-persistent UDF.

DROP FUNCTION syntax

The following table addresses differences in DROP FUNCTION syntax between Amazon Redshift and BigQuery.

Amazon Redshift BigQuery
DROP FUNCTION
function_name
( [arg_name] arg_type [, ...] ) [ CASCADE | RESTRICT ]
DROP FUNCTION
dataset_name.function_name


Note: BigQuery does not require using the function's signature for deleting the function. Also, removing function dependencies is not supported in BigQuery.

BigQuery also supports the DROP FUNCTION IF EXISTS statement, which deletes the function only if the function exists in the specified dataset.

BigQuery requires that you specify the project_name if the function is not located in the current project.

UDF components

This section highlights the similarities and differences in UDF components between Amazon Redshift andBigQuery.

Component Amazon Redshift BigQuery
Name Amazon Redshift recommends using the prefix _f for function names to avoid conflicts with existing or future built-in SQL function names. In BigQuery, you can use any custom function name.
Arguments Arguments are optional. You can use name and data types for Python UDF arguments and only data types for SQL UDF arguments. In SQL UDFs, you must refer to arguments using $1, $2, and so on. Amazon Redshift also restricts the number of arguments to 32. Arguments are optional, but if you specify arguments, they must use both name and data types for both JavaScript and SQL UDFs. The maximum number of arguments for a persistent UDF is 256.
Data type Amazon Redshift supports a different set of data types for SQL and Python UDFs.
For a Python UDF, the data type might also be ANYELEMENT.

You must specify a RETURN data type for both SQL and Python UDFs.

See Data types in this document for equivalents between data types in Amazon Redshift and in BigQuery.
BigQuery supports a different set of data types for SQL and JavaScript UDFs.
For a SQL UDF, the data type might also be ANY TYPE. For more information, see templated parameters in BigQuery.

The RETURN data type is optional for SQL UDFs.

See Supported JavaScript UDF data types for information on how BigQuery data types map to JavaScript data types.
Definition For both SQL and Python UDFs, you must enclose the function definition using dollar quoting, as in a pair of dollar signs ($$),用于指示函数语句的开始和结束。

对于 SQL UDF,Amazon Redshift 仅支持 SQL SELECT 子句作为函数定义。此外,SELECT 子句不能包含任何 FROMINTOWHEREGROUP
BYORDER BYLIMIT 子句。

对于 Python UDF,您可以使用 Python 2.7 标准库编写 Python 程序,或者使用 CREATE LIBRARY 命令创建一个自定义模块来导入该自定义模块。
在 BigQuery 中,您需要将 JavaScript 代码用引号引起来。请参阅引用规则了解详情。

对于 SQL UDF,您可以使用任何 SQL 表达式作为函数定义。但是,BigQuery 不支持引用表、视图或模型。

对于 JavaScript UDF,您可以直接使用 OPTIONS 部分加入外部代码库。您还可以使用 BigQuery UDF 测试工具测试您的函数。
语言 您必须使用 LANGUAGE 字面量针对 SQL UDF 将语言指定为 sql 或针对 Python UDF 将语言指定为 plpythonu 您无需为 SQL UDF 指定 LANGUAGE ,但必须针对 JavaScript UDF 将语言指定为 js
状态 Amazon Redshift 不支持创建临时性 UDF。

Amazon Redshift 提供了一个选项,可使用 VOLATILESTABLEIMMUTABLE 字面量定义函数的波动性。它由查询优化器用于进行优化。
BigQuery 同时支持永久性 UDF 和临时性 UDF。您可以在多个查询中重复使用永久性 UDF,但只能在单个查询中使用临时性 UDF。

在 BigQuery 中,函数易失性不是可配置参数。所有 BigQuery UDF 易失性等效于 Amazon Redshift 的 IMMUTABLE 易失性。
安全性和特权 要创建 UDF,您必须拥有 SQL 或 plpythonu (Python) 的语言使用权限。默认情况下,USAGE ON LANGUAGE SQL 会授予 PUBLIC,但您必须将 USAGE ON LANGUAGE PLPYTHONU 明确授予特定用户或群组。
此外,您必须是超级用户才能替换 UDF。
在 BigQuery 中无需授予创建或删除任何类型的 UDF 的明确权限。分配了 BigQuery Data Editor 角色(其中一项权限是 bigquery.routines.* )的任何用户都可以为指定的数据集创建或删除函数。

BigQuery 还支持创建自定义角色。这可以使用 Cloud IAM 进行管理。
限制 请参阅 Python UDF 限制 请参阅对用户定义函数的限制

元数据和事务 SQL 语句

Amazon Redshift BigQuery
SELECT * FROM STL_ANALYZE WHERE name
= 'T';
在 BigQuery 中不使用。您无需收集统计信息即可提高查询性能。如需获取有关数据分布的信息,您可以使用近似聚合函数
ANALYZE [[ table_name[(column_name
[, ...])]]
在 BigQuery 中不使用。
LOCK TABLE table_name; 在 BigQuery 中不使用。
BEGIN TRANSACTION; SELECT ...
END TRANSACTION;
BigQuery 使用快照隔离。如需了解详情,请参阅一致性保证
EXPLAIN ... 在 BigQuery 中不使用。

类似的功能是 BigQuery Google Cloud 控制台和 Cloud Monitoring 的审核日志记录中的查询计划说明
SELECT * FROM SVV_TABLE_INFO WHERE
table = 'T';
SELECT * EXCEPT(is_typed) FROM
mydataset.INFORMATION_SCHEMA.TABLES;


如需了解详情,请参阅 BigQuery INFORMATION_SCHEMA 简介
VACUUM [table_name] 在 BigQuery 中不使用。BigQuery 聚簇表会自动排序

多语句和多行 SQL 语句

Amazon Redshift 和 BigQuery 都支持事务(会话),因此支持用分号分隔并始终一起执行的语句。如需了解详情,请参阅多语句事务

过程 SQL 语句

CREATE PROCEDURE 语句

Amazon Redshift BigQuery
CREATE or REPLACE PROCEDURE CREATE PROCEDURE(如果名称是必需的)。

否则,请将内嵌与 BEGIN 结合使用或在具有 CREATE TEMP FUNCTION 的单行中使用内嵌。
CALL CALL

变量声明和赋值

Amazon Redshift BigQuery
DECLARE DECLARE

声明指定类型的变量。
SET SET

将变量设置为具有提供的表达式的值,或根据多个表达式的结果同时设置多个变量。

错误情况处理程序

在 Amazon Redshift 中,如果在存储过程执行期间遇到错误,则会结束执行流、结束事务并回滚事务。出现这些结果是因为不支持子事务。在 Amazon Redshift 存储过程中,唯一支持的 handler_statementRAISE。在 BigQuery 中,错误处理是主控制流的核心功能,类似于其他语言通过 TRY ... CATCH 块提供的功能。

Amazon Redshift BigQuery
BEGIN ... EXCEPTION WHEN OTHERS THEN BEGIN ... EXCEPTION WHEN ERROR THEN
RAISE RAISE
[ <<label>> ] [ DECLARE declarations ]
BEGIN
statements EXCEPTION
BEGIN
statements
EXCEPTION
WHEN OTHERS THEN
Handler_statements
END;
BEGIN
BEGIN
...
EXCEPTION WHEN ERROR THEN SELECT 1/0;
END;

EXCEPTION WHEN ERROR THEN -- The exception thrown from the inner exception handler lands here. END;

游标声明和操作

由于 BigQuery 不支持游标或会话,因此 BigQuery 中不会使用以下语句:

如果您使用游标返回结果集,则可以使用 BigQuery 中的临时表来实现类似行为。

动态 SQL 语句

BigQuery 中的脚本功能支持下表所示的动态 SQL 语句。

Amazon Redshift BigQuery
EXECUTE EXECUTE IMMEDIATE

控制流语句

Amazon Redshift BigQuery
IF..THEN..ELSIF..THEN..ELSE..END IF IF condition
THEN stmts
ELSE stmts
END IF
name CURSOR [ ( arguments ) ] FOR query BigQuery 中不使用光标或会话。
[< LOOP
sql_statement_list END LOOP;
WHILE condition LOOP stmts END LOOP WHILE condition
DO stmts
END WHILE
EXIT BREAK

一致性保证和事务隔离

Amazon Redshift 和 BigQuery 都具有原子性,也就是说,在多行的每个变更级别都符合 ACID。

交易

默认情况下,Amazon Redshift 支持对事务进行可序列化隔离。Amazon Redshift 可让您指定四个 SQL 标准事务隔离级别中的任何一个,但会将所有隔离级别处理为可序列化。

BigQuery 还支持事务。BigQuery 使用快照隔离帮助确保乐观并发控制(首先提交优先),其中查询在查询开始之前读取最后提交的数据。此方法可保证每行的每个变更和同一 DML 语句中的各行具有相同的一致性级别,同时避免死锁。如果对同一个表进行了多个 DML 更新,BigQuery 会切换为悲观并发控制。加载作业可以完全独立运行并附加到表。

回滚

如果 Amazon Redshift 在运行存储过程时遇到任何错误,则会回滚事务中进行的所有更改。此外,您还可以在存储过程中使用 ROLLBACK 事务控制语句来舍弃所有更改。

在 BigQuery 中,您可以使用 ROLLBACK TRANSACTION 语句

数据库限制

请查看 BigQuery 公开文档,了解最新的配额和限制。可通过联系 Cloud 支持团队来增加大量用户的许多配额。下表展示了 Amazon Redshift 和 BigQuery 数据库限制的比较。

限额 Amazon Redshift BigQuery
每个数据库中适用于大型和超大型集群节点类型的表 9,900 无限制
每个数据库中适用于 8xlarge 集群节点类型的表 20000 无限制
您可以为每个集群创建的用户定义数据库 60 无限制
行大小上限 4 MB 100 MB