Amazon Redshift SQL translation guide

This document details the similarities and differences in SQL syntax betweenAmazon Redshift and BigQuery to help you plan your migration. Usebatch SQL translation tomigrate your SQL scripts in bulk, orinteractive SQL translationto translate ad hoc queries.

The intended audience for this guide is enterprise architects, databaseadministrators, application developers, and IT security specialists. Itassumes you are familiar with Amazon Redshift.

Note: In some cases, there is no direct mapping between a SQL element inAmazon Redshift and BigQuery. However, in most cases, youcan achieve the same functionality in BigQuery that you can inAmazon Redshift using alternative means, as shown in the examplesin this document.

Data types

This section shows equivalents between data types in Amazon Redshift and in BigQuery.

Amazon RedshiftBigQueryNotes
Data typeAliasData type
SMALLINTINT2INT64Amazon Redshift'sSMALLINT is 2 bytes, whereas BigQuery'sINT64 is 8 bytes.
INTEGER

INT, INT4

INT64Amazon Redshift'sINTEGER is 4 bytes, whereas BigQuery'sINT64 is 8 bytes.
BIGINTINT8INT64Both Amazon Redshift'sBIGINT and BigQuery'sINT64 are 8 bytes.
DECIMALNUMERICNUMERIC
REALFLOAT4FLOAT64Amazon Redshift'sREAL is 4 bytes, whereas BigQuery'sFLOAT64 is 8 bytes.
DOUBLEPRECISION

FLOAT8, FLOAT

FLOAT64
BOOLEANBOOLBOOLAmazon Redshift'sBOOLEAN can useTRUE,t,true,y,yes, and1 as valid literal values for true. BigQuery'sBOOL data type uses case-insensitiveTRUE.
CHAR

CHARACTER, NCHAR, BPCHAR

STRING
VARCHAR

CHARACTER VARYING, NVARCHAR, TEXT

STRING
DATEDATE
TIMESTAMPTIMESTAMP WITHOUT TIME ZONEDATETIME
TIMESTAMPTZ

TIMESTAMP WITH TIME ZONE

TIMESTAMPNote: In BigQuery,time zones are used when parsingtimestamps or formatting timestamps for display. A string-formattedtimestamp might include a time zone, but when BigQuery parses thestring, it stores the timestamp in the equivalent UTC time. When atime zone is not explicitly specified, the default time zone, UTC, isused.Time zonenames oroffset from UTC using (-|+)HH:MMare supported, but time zone abbreviations such as PDT are notsupported.
GEOMETRYGEOGRAPHYSupport for querying geospatial data.

BigQuery also has the following data types that do not have a direct Amazon Redshiftanalog:

Implicit conversion types

When migrating to BigQuery, you need to convert most of yourAmazon Redshift implicit conversionsto BigQuery's explicit conversions except for the following data types, whichBigQuery implicitly converts.

BigQuery performs implicit conversions for the following data types:

From BigQuery typeTo BigQuery type

INT64

FLOAT64

INT64

NUMERIC

NUMERIC

FLOAT64

BigQuery also performs implicit conversions for the following literals:

From BigQuery typeTo BigQuery type
STRING literal
(e.g. "2008-12-25")

DATE

STRING literal
(e.g. "2008-12-25 15:30:00")

TIMESTAMP

STRING literal
(e.g. "2008-12-25T07:30:00")

DATETIME

STRING literal
(e.g. "15:30:00")

TIME

Explicit conversion types

You can convert Amazon Redshift data types that BigQuery doesn't implicitly convertusing BigQuery'sCAST(expression AS type) functionor any of theDATEandTIMESTAMPconversion functions.

When migrating your queries, change any occurrences of the Amazon RedshiftCONVERT(type, expression)function (or the :: syntax) to BigQuery'sCAST(expression AS type) function,as shown in the table in theData type formatting functions section.

Query syntax

This section addresses differences in query syntax between Amazon Redshift andBigQuery.

SELECT statement

Most Amazon RedshiftSELECTstatements are compatible with BigQuery. The following table contains a list ofminor differences.

Amazon RedshiftBigQuery

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

Note: Redshift supports creating andreferencing an alias in the sameSELECTstatement.

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

BigQuery also supports the following expressions inSELECT statements, which donot have a Amazon Redshift equivalent:

FROM clause

AFROMclause in a query lists the table references that data is selected from. InAmazon Redshift, possible table references include tables, views, and subqueries. Allof these table references are supported in BigQuery.

BigQuery tables can be referenced in theFROMclause using the following:

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

BigQuery also supports additional table references:

JOIN types

Both Amazon Redshift and BigQuery support the following types of join:

The following table contains a list of minor differences.

Amazon RedshiftBigQuery

SELECT col
FROM table1
NATURAL INNER JOIN
table2

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


Note: In BigQuery,JOIN clauses require aJOIN condition unless the clause is aCROSSJOIN or one of the joined tables is a field within a data typeor an array.

WITH clause

A BigQueryWITHclause contains one or more named subqueries that execute when a subsequentSELECT statement references them. Amazon RedshiftWITHclauses behave the same as BigQuery's with the exception that you can evaluatethe clause once and reuse its results.

Set operators

There are some minor differences betweenAmazon Redshift set operatorsandBigQuery setoperators.However, all set operations that are feasible in Amazon Redshift are replicable inBigQuery.

Amazon RedshiftBigQuery

SELECT * FROM table1
UNION
SELECT * FROM table2

SELECT * FROM table1
UNION DISTINCT
SELECT * FROM table2

Note: Both BigQuery and Amazon Redshift support theUNION ALL operator.

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
)


Note: BigQuery requires parentheses to separate different setoperations. If the same set operator is repeated, parentheses are notnecessary.

ORDER BY clause

There are some minor differences between Amazon RedshiftORDER BYclauses and BigQueryORDER BYclauses.

Amazon RedshiftBigQuery
In Amazon Redshift,NULLs are ranked last by default (ascendingorder).In BigQuery,NULLs are ranked first by default(ascending order).

SELECT *
FROM table
ORDER BY expression
LIMIT ALL

SELECT *
FROM table
ORDER BY expression



Note: BigQuery does not use theLIMIT ALL syntax, butORDER BY sorts all rows by default, resulting in thesame behavior as Amazon Redshift'sLIMIT ALL clause. We highlyrecommend including aLIMIT clause with everyORDER BY clause. Ordering all result rows unnecessarilydegrades query execution performance.

SELECT *
FROM table
ORDER BY expression
OFFSET 10

SELECT *
FROM table
ORDER BY expression
LIMITcount OFFSET 10



Note: In BigQuery,OFFSET must be used together with aLIMITcount. Make sure to set thecountINT64 value to the minimum necessary ordered rows.Ordering all result rows
unnecessarily degrades query execution performance.

Conditions

The following tableshowsAmazon Redshift conditions,or predicates, that are specific to Amazon Redshift and must be converted to theirBigQuery equivalent.

Amazon RedshiftBigQuery

a= ANY (subquery)

a= SOME (subquery)

aIN subquery

a<> ALL (subquery)

a != ALL (subquery)

aNOT IN subquery

aIS UNKNOWN

expressionILIKE pattern

aIS NULL

LOWER(expression)LIKE LOWER(pattern)

expressionLIKE pattern ESCAPE 'escape_char'

expressionLIKE pattern


Note: BigQuery does not support custom escape characters. You mustuse two backslashes \\ as escape characters for BigQuery.

expression [NOT]SIMILAR TO pattern

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


Note: IfNOT is specified, wrap the aboveIF expression in aNOT expression as shownbelow:

NOT(
IF(
LENGTH(...
)

expression[!] ~ pattern

[NOT]REGEXP_CONTAINS(
expression,
regex
)

Functions

The following sections list Amazon Redshift functions and their BigQuery equivalents.

Aggregate functions

The following table shows mappings between common Amazon Redshift aggregate, aggregateanalytic, and approximate aggregate functions with their BigQuery equivalents.

Amazon RedshiftBigQuery
APPROXIMATECOUNT(DISTINCT expression)APPROX_COUNT_DISTINCT(expression)
APPROXIMATEPERCENTILE_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()

Note: Does not cover aggregation use cases.
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 also offers the followingaggregate,aggregate analytic,andapproximate aggregatefunctions, which do not have a direct analogue in Amazon Redshift:

Bitwise aggregate functions

The following table shows mappings between common Amazon Redshift bitwise aggregatefunctions with their BigQuery equivalents.

Amazon RedshiftBigQuery
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 also offers the followingbit-wise aggregatefunction, which does not have a direct analogue in Amazon Redshift:

Window functions

The following table shows mappings between common Amazon Redshift window functionswith their BigQuery equivalents. Windowing functions in BigQuery includeanalytic aggregate functions,aggregate functions,navigation functions,andnumbering functions.


Amazon RedshiftBigQuery
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 BYpartition_expression] )		STRING_AGG(
[DISTINCT]aggregate_expression
[, delimiter] )
OVER (
[PARTITION BYpartition_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_orderingframe_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_expressionSUM(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]
)

Conditional expressions

The following table shows mappings between common Amazon Redshift conditionalexpressions with their BigQuery equivalents.

Amazon RedshiftBigQuery
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 also offers the following conditional expressions, which do not have adirect analogue in Amazon Redshift:

Date and time functions

The following table shows mappings between common Amazon Redshift date and timefunctions with their BigQuery equivalents. BigQuery data and time functionsincludedate functions,datetimefunctions,time functions,andtimestamp functions.

Keep in mind that functions that seem identical in Amazon Redshift and BigQuery mightreturn different data types.

Amazon RedshiftBigQuery
ADD_MONTHS(
date,
integer
)		CAST(DATE_ADD(
date,
INTERVALinteger MONTH
)
AS TIMESTAMP
)
timestamptz_or_timestampAT TIMEZONE timezonePARSE_TIMESTAMP(
"%c%z",
FORMAT_TIMESTAMP(
"%c%z",
timestamptz_or_timestamp,
timezone
)
)

Note:Time zones are used when parsingtimestamps or formatting timestamps for display. A string-formattedtimestamp might include a time zone, but when BigQuery parses thestring, it stores the timestamp in the equivalent UTC time. When atime zone is not explicitly specified, the default time zone, UTC, isused.Time zone names oroffset from UTC (-HH:MM) aresupported, but time zone abbreviations (such as PDT) are notsupported.
CONVERT_TIMEZONE(
[source_timezone],
target_timezone,
timestamp
)		PARSE_TIMESTAMP(
"%c%z",
FORMAT_TIMESTAMP(
"%c%z",
timestamp,
target_timezone
)
)

Note:source_timezone is UTC in BigQuery.
CURRENT_DATE

Note: Returns start date for the current transaction in the currentsession time zone (UTC by default).		CURRENT_DATE()

Note: Returns start date for the current statement in the UTC timezone.
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 FROMdate)
DATEADD(date_part,interval, date)CAST(
DATE_ADD(
date,
INTERVALinterval 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 FROMdate)
DATE_TRUNC('date_part',timestamp)TIMESTAMP_TRUNC(timestamp,date_part)
EXTRACT(date_part FROMtimestamp)EXTRACT(date_part FROMtimestamp)
GETDATE()PARSE_TIMESTAMP(
"%c",
FORMAT_TIMESTAMP(
"%c",
CURRENT_TIMESTAMP()
)
)
INTERVAL_CMP(
interval_literal1,
interval_literal2
)		For intervals in Redshift, there are 360 days in a year. In BigQuery, you can use the following user-defined function (UDF) to parse a Redshift interval andtranslate it to seconds.

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
)));

To compare interval literals, perform:

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 1MONTH
),
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

Note: Returns start timestamp for the current transaction in thecurrent session time zone (UTC by default).		CURRENT_TIMESTAMP()

Note: Returns start timestamp for the current statement in the UTC timezone.
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 FROMtimestamp
) = date
THEN 0
WHEN
EXTRACT(
DATE FROMtimestamp) > date
THEN 1
ELSE -1
END
TIMESTAMP_CMP_TIMESTAMPTZ(
timestamp,
timestamptz
)

Note: Redshift compares timestamps in the user session-defined timezone. Default user session time zone is UTC.		CASE
WHEN timestamp = timestamptz
THEN 0
WHEN timestamp > timestamptz
THEN 1
ELSE -1
END

Note: BigQuery compares timestamps in the UTC time zone.
TIMESTAMPTZ_CMP(
timestamptz1,
timestamptz2
)

Note: Redshift compares timestamps in the user session-defined timezone. Default user session time zone is UTC.		CASE
WHEN timestamptz1 = timestamptz2
THEN 0
WHEN timestamptz1 > timestamptz2
THEN 1
ELSE -1
END

Note: BigQuery compares timestamps in the UTC time zone.
TIMESTAMPTZ_CMP_DATE(
timestamptz,
date
)

Note: Redshift compares timestamps in the user session-defined timezone. Default user session time zone is UTC.		CASE
WHEN
EXTRACT(
DATE FROMtimestamptz) = date
THEN 0
WHEN
EXTRACT(
DATE FROMtimestamptz) > date
THEN 1
ELSE -1
END

Note: BigQuery compares timestamps in the UTC time zone.
TIMESTAMPTZ_CMP_TIMESTAMP(
timestamptz,
Timestamp
)

Note: Redshift compares timestamps in the user session-defined timezone. Default user session time zone is UTC.		CASE
WHEN timestamp = timestamptz
THEN 0
WHEN timestamp > timestamptz
THEN 1
ELSE -1
END

Note: BigQuery compares timestamps in the UTC time zone.
TIMEZONE(
timezone,
Timestamptz_or_timestamp
)		PARSE_TIMESTAMP(
"%c%z",FORMAT_TIMESTAMP(
"%c%z",
timestamptz_or_timestamp,
timezone
)
)

Note:Time zones are used when parsingtimestamps or formatting timestamps for display. A string-formattedtimestamp might include a time zone, but when BigQuery parses thestring, it stores the timestamp in the equivalent UTC time. When atime zone is not explicitly specified, the default time zone, UTC, isused.Time zone names oroffset from UTC (-HH:MM) aresupported but time zone abbreviations (such as PDT) are notsupported.
TO_TIMESTAMP(timestamp,format)PARSE_TIMESTAMP(
format,
FORMAT_TIMESTAMP(
format,
timestamp
)
)

Note: BigQuery follows a different set offormat elements.Time zones are used when parsingtimestamps or formatting timestamps for display. A string-formattedtimestamp might include a time zone, but when BigQuery parses thestring, it stores the timestamp in the equivalent UTC time. When atime zone is not explicitly specified, the default time zone, UTC, isused.Time zone names oroffset from UTC (-HH:MM) aresupported in the format string but time zone abbreviations (such asPDT) are not supported.
TRUNC(timestamp)CAST(timestamp ASDATE)

BigQuery also offers the following date and time functions, which do not have adirect analogue in Amazon Redshift:

Mathematical operators

The following table shows mappings between common Amazon Redshift mathematicaloperators with their BigQuery equivalents.

Amazon RedshiftBigQuery

X + Y

X + Y

X - Y

X - Y

X * Y

X * Y

X / Y


Note: If the operator is
performing integer division (in other words, ifX andY are bothintegers), an integer is returned. If the operator is performingnon-integer division, a non-integer is returned.		If integer division:
CAST(FLOOR(X / Y) AS INT64)

If not integer division:

CAST(X / Y AS INT64)


Note: Division in BigQuery returns a non-integer.
To prevent errors from a division operation (division by zero error),useSAFE_DIVIDE(X, Y)orIEEE_DIVIDE(X, Y).

X % Y

MOD(X, Y)


Note: To prevent errors from a division operation (division by zeroerror), useSAFE.MOD(X, Y).SAFE.MOD(X, 0) results in 0.

X ^ Y

POW(X, Y)

POWER(X, Y)


Note: Unlike Amazon Redshift, the^ operator inBigQuery performs Bitwisexor.

| / X

SQRT(X)


Note: To prevent errors from a square root operation (negativeinput), useSAFE.SQRT(X). Negative inputwithSAFE.SQRT(X) results inNULL.

|| / X

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


Note: BigQuery'sPOWER(X, Y) returns anerror ifX is a finite value less than 0 andY is a noninteger.

@ X

ABS(X)

X<< Y

X<< Y


Note: This operator returns 0 or a byte sequence of b'\x00' if thesecond operandY is greater than or equal to the bit length of thefirst operandX (for example, 64 ifX has the type INT64). Thisoperator throws an error ifY is negative.

X >> Y

X>> Y


Note: Shifts the first operandX to the right. This operator does notdo sign bit extension with a signed type (it fills vacant bits on theleft with 0). This operator returns 0 or a byte sequence of
b'\x00' if the second operandY is greater than or equal to the bitlength of the first operandX (for example, 64 ifX has the typeINT64). This operator throws an error ifY is negative.

X & Y

X& Y

X | Y

X| Y

~X

~X

BigQuery also offers the following mathematical operator, which does not have adirect analog in Amazon Redshift:

Math functions

Amazon RedshiftBigQuery
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)
PIACOS(-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])

String functions

Amazon RedshiftBigQuery
string1|| string2CONCAT(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)

Note: BigQuery'sCONCAT(...) supports
concatenating any number of strings.
CRC32Custom user-defined function
FUNC_SHA1(string)SHA1(string)
INITCAPINITCAP
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 INstring)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
)
)

Ifposition is specified:

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

Note: BigQuery provides regular expression support using there2 library; see thatdocumentation for its regular expression syntax.
REGEXP_INSTR(
source_string,
pattern
[,position
[,occurrence]] )		IFNULL(STRPOS(
source_string,REGEXP_EXTRACT(
source_string,
pattern)
),0)

Ifsource_string is specified:

REGEXP_REPLACE(
source_string,

pattern,
replace_string
)

Ifposition is specified:

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)

Ifoccurrence is specified:

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)

Note: BigQuery provides regular expression
support using there2library; see that
documentation for its regular expression
syntax.
REGEXP_REPLACE(source_string,
pattern
[, replace_string [, position]]
)		REGEXP_REPLACE(
source_string,
pattern,
""
)

Ifsource_string is specified:

REGEXP_REPLACE(
source_string,

pattern, replace_string
)

Ifposition is specified:

CASE
WHEN position >LENGTH(source_string) THENsource_string
WHEN position <= 0 THENREGEXP_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
)

Ifposition is specified:

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

)

Ifoccurrence is specified:

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


pattern
)[SAFE_ORDINAL(occurrence)]

Note: BigQuery provides regular expression support using there2 library; see thatdocumentation for its regular expression syntax.
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 )		Can be implemented using UDFs:

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 FROMstring)TRIM(string, characters)
UPPER(string)UPPER(string)

Data type formatting functions

Amazon RedshiftBigQuery
CAST(expression AS type)CAST(expression AS type)
expression::typeCAST(expression AS type)
CONVERT(type, expression)CAST(expression AS type)
TO_CHAR(
timestamp_expression, format
)		FORMAT_TIMESTAMP(
format,
timestamp_expression
)

Note: BigQuery and Amazon Redshift differ in how to specify a format string fortimestamp_expression.
TO_CHAR(
numeric_expression,
format
)		FORMAT(
format,
numeric_expression
)

Note: BigQuery and Amazon Redshiftdiffer in how to specify a format string fortimestamp_expression.
TO_DATE(date_string, format)PARSE_DATE(date_string, format)

Note: BigQuery and Amazon Redshift differ in how to specify a format string fordate_string.
TO_NUMBER(string, format)CAST(
FORMAT(
format,
numeric_expression
) TO INT64
)

Note: BigQuery and Amazon Redshift differ in how to specify a numeric format string.

BigQuery also supportsSAFE_CAST(expressionAS typename), which returnsNULL if BigQuery is unable to perform a cast; forexample,SAFE_CAST("apple"AS INT64) returnsNULL.

DML syntax

This section addresses differences in data management language syntax betweenAmazon Redshift and BigQuery.

INSERT statement

Amazon Redshift offers a configurableDEFAULT keyword for columns. InBigQuery, theDEFAULT value for nullable columns isNULL,andDEFAULT is not supported forrequired columns. MostAmazon RedshiftINSERT statementsare compatible with BigQuery. The following table shows exceptions.

Amazon RedshiftBigQuery
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 also supports inserting values using a subquery (where one of thevalues is computed using a subquery), which is not supported in Amazon Redshift. Forexample:

INSERTINTOtable(column1,column2)VALUES('value_1',(SELECTcolumn2FROMtable2))

COPY statement

Amazon Redshift'sCOPYcommandloads data into a table from data files or from an Amazon DynamoDB table.BigQuery does not use theSQL COPY command to load data,but you can use any of several non-SQL tools and options toload data into BigQuery tables.You can also use data pipeline sinks provided inApache SparkorApache Beamto write data into BigQuery.

UPDATE statement

Most Amazon RedshiftUPDATE statements are compatible with BigQuery. The followingtable shows exceptions.

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

Note: AllUPDATE statements in BigQuery require aWHERE keyword,followed by a condition.
UPDATE table
SET column = DEFAULT [,...] [FROM ...]
[WHERE ...]		UPDATE table
SET column = NULL [, ...]
[FROM ...]
WHERE ...

Note: BigQuery'sUPDATE command does not supportDEFAULT values.

If the Amazon RedshiftUPDATE statement does not include aWHERE clause, theBigQueryUPDATE statement should be conditionedWHERE TRUE.

DELETE andTRUNCATE statements

TheDELETE andTRUNCATE statements are both ways to remove rows from a tablewithout affecting the table schema or indexes.

In Amazon Redshift, theTRUNCATE statement is recommended over anunqualifiedDELETE statement because it isfaster and does not requireVACUUM andANALYZE operations afterward.However, you can useDELETE statements to achieve the same effect.

In BigQuery, theDELETE statement must have aWHERE clause. For moreinformation aboutDELETE in BigQuery, see theBigQueryDELETE examplesin the DML documentation.

Amazon RedshiftBigQuery
DELETE[FROM] table_name

TRUNCATE[TABLE] table_name		DELETE FROM table_name
WHERE TRUE

BigQueryDELETE statements require aWHERE clause.
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 )

In Amazon Redshift,USING allows additional tables to be referenced in theWHERE clause. This can be achieved in BigQuery by using a subquery intheWHERE clause.

MERGE statement

TheMERGE statement can combineINSERT,UPDATE,andDELETE operations into asingle upsert statement and perform the operations atomically. TheMERGEoperation must match at most one source row for each target row.

Amazon Redshift does not support a singleMERGE command. However, amerge operationcan be performed in Amazon Redshift by performingINSERT,UPDATE, andDELETE operationsin a transaction.

Merge operation by replacing existing rows

In Amazon Redshift, an overwrite of all of the columns in the target table can beperformed using aDELETE statement and then anINSERT statement. TheDELETEstatement removes rows that should be updated, and then theINSERT statementinserts the updated rows. BigQuery tables are limited to1,000 DML statementsper day, so you should consolidateINSERT,UPDATE, andDELETEstatements into asingleMERGE statement as shown in the following table.

Amazon RedshiftBigQuery
SeePerforming amerge operation byreplacingexisting rows.

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

Note: All columns must be listed if updating all columns.
SeePerforming amerge operation byspecifying acolumn list.

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 syntax

This section addresses differences in data definition language syntax betweenAmazon Redshift and BigQuery.

SELECT INTO statement

In Amazon Redshift, theSELECT INTO statement can be used to insert the results of aquery into a new table, combining table creation and insertion.

Amazon RedshiftBigQuery
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 offers several ways to emulate temporary tables. See thetemporary tables section for more information.

CREATE TABLE statement

Most Amazon RedshiftCREATE TABLEstatements are compatible with BigQuery, except for the following syntax elements, whichare not used in BigQuery:

Amazon RedshiftBigQuery
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
)

Note:UNIQUE andPRIMARY KEY constraints areinformational andare notenforced by the Amazon Redshiftsystem.		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)]

Note:UNIQUE andPRIMARY KEY constraints are informational andare notenforced by the Amazon Redshift system.		CREATE TABLE table_name
(
col1 data_type1[,...]
)
PARTITION BY column_name
CLUSTER BY column_name [, ...]

Note: BigQuery does not useUNIQUE,PRIMARY KEY, orFOREIGN KEY tableconstraints. To achieve similar optimization that these constraints provide during query execution,partition and cluster your BigQuery tables.CLUSTER BY supports up to4 columns.
CREATE TABLE table_name
LIKE original_table_name		Referencethis example to learn how to usetheINFORMATION_SCHEMA tables to copy column names, data types, andNOT NULL constraints to a new table.
CREATE TABLE table_name
(
col1 data_type1
)
BACKUP NO

Note: In Amazon Redshift, theBACKUPNO setting is specified to save processing time and reducestorage space.		TheBACKUP NO table option is not used or needed because BigQueryautomatically keeps up to 7 days of historical versions of all ofyour tables with no effect on processing time or billed storage.
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 supports clustering, which allows storing keys in sortedorder.
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 also supports the DDL statementCREATE OR REPLACE TABLE,which overwrites a table if it already exists.

BigQuery'sCREATE TABLE statement also supports the following clauses, which donot have an Amazon Redshift equivalent:

For more information aboutCREATE TABLE in BigQuery, see theBigQueryCREATE TABLE examplesin the DML documentation.

Temporary tables

Amazon Redshift supports temporary tables, which are only visible within the currentsession. There are several ways to emulate temporary tables in BigQuery:

  • Dataset TTL: Create a dataset that has a short time to live (forexample, one hour) so that any tables created in the dataset areeffectively temporary because they won't persist longer than the dataset'stime to live. You can prefix all of the table names in this dataset withtemp to clearly denote that the tables are temporary.

  • Table TTL: Create a table that has a table-specific short time to liveusing DDL statements similar to the following:

    CREATETABLEtemp.name(col1,col2,...)OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL1HOUR));

CREATE VIEW statement

The following table shows equivalents between Amazon Redshift and BigQuery for theCREATE VIEW statement.

Amazon RedshiftBigQuery
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...
Not supported.CREATE VIEW IF NOT EXISTS cview_name
OPTIONS(view_option_list)
AS SELECT …

Creates a new view only if the view does not exist in the specified dataset.
CREATE VIEW view_name
AS SELECT ...
WITH NOSCHEMA BINDING

In Amazon Redshift, a late binding view is required in order to reference anexternal table.		In BigQuery, to create a view, all referenced objects must alreadyexist.

BigQuery allows you toquery external data sources.

User-defined functions (UDFs)

A UDF lets you create functions for custom operations. These functions acceptcolumns of input, perform actions, and return the result of those actions as avalue.

Both Amazon Redshift and BigQuery support UDFs using SQL expressions. Additionally, inAmazon Redshift you can create aPython-based UDF,and in BigQuery you can create aJavaScript-based UDF.

Refer to theGoogle Cloud BigQuery utilities GitHub repository for a library of common BigQuery UDFs.

CREATE FUNCTION syntax

The following table addresses differences in SQL UDF creation syntax betweenAmazon Redshift and BigQuery.

Amazon RedshiftBigQuery
CREATE [ORREPLACE] 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

Note: In a BigQuerySQL UDF, a return data type isoptional. BigQuery infers the result type of the function from theSQL function body when a query calls the function.
CREATE [ORREPLACE] 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

Note: Function volatility is not a configurable parameter inBigQuery. All BigQuery UDF volatility is equivalent to Amazon Redshift'sIMMUTABLE volatility (that is, it does not do database lookups orotherwise use information not directly present in its argument list).
CREATE [ORREPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
IMMUTABLE
AS $$
SELECT_clause
$$ LANGUAGE sql

Note: Amazon Redshift supports only aSQL SELECT clause as functiondefinition. Also, theSELECT clause cannot include any of theFROM,INTO, WHERE, GROUP BY, ORDER BY, andLIMIT clauses.		CREATE [OR REPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
AS sql_expression

Note: BigQuery supports any SQL expressions as function definition.However, referencing tables, views, or models is not supported.
CREATE [ORREPLACE] FUNCTION
function_name
([sql_arg_name sql_arg_data_type[,..]]) RETURNS data_type
IMMUTABLE
AS $$
sql_function_definition
$$ LANGUAGE sql		CREATE [OR REPLACE] FUNCTIONfunction_name ([sql_arg_name sql_arg_data_type[,..]]) RETURNSdata_type AS sql_function_definition

Note: Language literal need not be specified in a GoogleSQL UDF.BigQuery interprets the SQL expression by default. Also, the Amazon Redshiftdollar quoting ($$) is not supported in BigQuery.
CREATE [ORREPLACE] FUNCTION function_name (integer, integer) RETURNSinteger IMMUTABLE AS $$ SELECT $1 + $2 $$ LANGUAGE sqlCREATE [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. TheAmazon Redshift argument variables ($1,$2, …) are not supported inBigQuery.
CREATE [ORREPLACE] FUNCTION
function_name
(integer, integer)
RETURNS integer
IMMUTABLE
AS $$
SELECT $1 + $2
$$ LANGUAGE sql

Note: Amazon Redshift does not supportANY TYPE for SQL UDFs. However, itsupports using theANYELEMENT 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 usingANY TYPE as argument type. The functionaccepts an input of any type for this argument. For more information,seetemplated parameter in BigQuery.

BigQuery also supports theCREATE FUNCTION IF NOT EXISTS statement, whichtreats the query as successful and takes no action if a function with the samename already exists.

BigQuery'sCREATE FUNCTION statement also supports creatingTEMPORARYorTEMP functions, which do not have an Amazon Redshift equivalent.

Seecalling UDFsfor details on executing a BigQuery-persistent UDF.

DROP FUNCTION syntax

The following table addresses differences inDROP FUNCTION syntax betweenAmazon Redshift and BigQuery.

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

Note: BigQuery does not require using the function's signature fordeleting the function. Also, removing function dependencies is notsupported in BigQuery.

BigQuery also supports theDROP FUNCTION IF EXISTS statement,which deletes the function only if the function exists in the specifieddataset.

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

UDF components

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

ComponentAmazon RedshiftBigQuery
NameAmazon Redshiftrecommends using the prefix_ffor function names to avoid conflicts with existingor future built-in SQL function names.In BigQuery, you can use any custom function name.
ArgumentsArguments are optional. You can use name and data types for PythonUDF 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 alsorestrictsthe number of arguments to 32.Arguments are optional, but if you specify arguments, they must useboth name and data types for both JavaScript and SQL UDFs. Themaximum number of arguments for a persistent UDF is 256.
Data typeAmazon Redshift supports a different set of data types forSQLandPython UDFs.
For a Python UDF, the data type might also beANYELEMENT.

You must specify aRETURNdata type for both SQL andPython UDFs.

SeeData types in this document for equivalents between datatypes in Amazon Redshift and in BigQuery.		BigQuery supports a different set of data types forSQL andJavaScript UDFs.
For a SQL UDF, the data type might also beANY TYPE. Formore information, seetemplated parameters inBigQuery.

TheRETURNdata type is optional for SQL UDFs.

SeeSupported JavaScript UDF data typesfor information on how BigQuery data types map to JavaScript datatypes.
DefinitionFor both SQL and Python UDFs, you must enclose the functiondefinition using dollar quoting, as in a pair of dollar signs($$), to indicate the start and end of the functionstatements.

ForSQLUDFs, Amazon Redshift supports only a SQLSELECTclauseas the function definition. Also, theSELECT clausecannot include any of theFROM,INTO,WHERE,GROUP
BY,ORDER BY, andLIMITclauses.

ForPythonUDFs, you can write a Python program using thePython 2.7 StandardLibrary or import your custom modules by creating one using theCREATELIBRARYcommand.		In BigQuery, you need to enclose the JavaScript code in quotes. SeeQuotingrules for moreinformation.

ForSQL UDFs, you can use any SQLexpressions as the function definition. However, BigQuery doesn'tsupport referencing tables, views, or models.

ForJavaScript UDFs, you canincludeexternal code libraries directlyusing theOPTIONSsection. You can also use theBigQuery UDFtest tool to test your functions.
LanguageYou must use theLANGUAGEliteral to specify thelanguage as eithersqlfor SQL UDFs orplpythonufor Python UDFs.You need not specifyLANGUAGEfor SQL UDFs but mustspecify the language asjsfor JavaScript UDFs.
StateAmazon Redshift does not support creating temporary UDFs.

Amazon Redshift provides an option to define thevolatilityof a function usingVOLATILE,STABLE,orIMMUTABLEliterals. This is used for optimization bythe query optimizer.		BigQuery supports both persistent and temporary UDFs. You can reusepersistent UDFs across multiple queries, whereas you can only usetemporary UDFs in a single query.

Function volatility is not a configurable parameter in BigQuery. AllBigQuery UDF volatility is equivalent to Amazon Redshift'sIMMUTABLEvolatility.
Security and privilegesTo create a UDF, you must havepermissionfor usage on language for SQL or plpythonu (Python). By default,USAGE ON LANGUAGE SQLis granted toPUBLIC,but you must explicitly grantUSAGE ON LANGUAGE PLPYTHONUto specific users or groups.
Also, you must be a superuser to replace a UDF.		Granting explicit permissions for creating or deleting any type ofUDF is not necessary in BigQuery. Any user assigned a role ofBigQuery Data Editor (havingbigquery.routines.*as one of the permissions)can create or delete functions for the specified dataset.

BigQuery also supports creating custom roles. This can be managedusingCloud IAM.
LimitsSeePython UDFlimits.Seelimits on user-defined functions.

Metadata and transaction SQL statements

Amazon RedshiftBigQuery
SELECT * FROMSTL_ANALYZE WHERE name
= 'T';		 Not used in BigQuery. You don't need to gather statistics in orderto improve query performance. To get information about your datadistribution, you can useapproximate aggregate functions.
ANALYZE[[ table_name[(column_name
[, ...])]]		Not used in BigQuery.
LOCKTABLE table_name;Not used in BigQuery.
BEGINTRANSACTION; SELECT ...
ENDTRANSACTION;		 BigQuery uses snapshot isolation. For details, seeConsistency guarantees.
EXPLAIN...Not used in BigQuery.

Similar features are thequery plan explanation in the BigQuery Google Cloud console, and inaudit logging in Cloud Monitoring.
SELECT * FROMSVV_TABLE_INFO WHERE
table = 'T';		SELECT * EXCEPT(is_typed) FROM
mydataset.INFORMATION_SCHEMA.TABLES;

For more information seeIntroduction to BigQueryINFORMATION_SCHEMA.
VACUUM[table_name]Not used in BigQuery. BigQueryclustered tables are automatically sorted.

Multi-statement and multi-line SQL statements

Both Amazon Redshift and BigQuery support transactions (sessions)and therefore support statements separated by semicolons that are consistentlyexecuted together. For more information, seeMulti-statement transactions.

Procedural SQL statements

CREATE PROCEDURE statement

Amazon RedshiftBigQuery
CREATE orREPLACE PROCEDURECREATE PROCEDURE if a name isrequired.

Otherwise, use inline withBEGIN or in a single line withCREATE TEMP FUNCTION.
CALLCALL

Variable declaration and assignment

Amazon RedshiftBigQuery
DECLAREDECLARE

Declares a variable of the specified type.
SETSET

Sets a variable to have the value of the provided expression, or setsmultiple variables at the same time based on the result of multipleexpressions.

Error condition handlers

In Amazon Redshift, an error encountered during the execution of a stored procedureends the execution flow, ends the transaction, and rolls back the transaction.These results occur because subtransactions are not supported. In anAmazon Redshift-stored procedure, the only supportedhandler_statementisRAISE. In BigQuery, errorhandling is a core feature of the main control flow, similar to what otherlanguages provide withTRY ... CATCH blocks.

Amazon RedshiftBigQuery
BEGIN ...EXCEPTION WHEN OTHERSTHENBEGIN ... EXCEPTION WHEN ERRORTHEN
RAISERAISE
[ <<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 innerexception handler lands here. END;

Cursor declarations and operations

Because BigQuery doesn't support cursors or sessions, the following statementsaren't used in BigQuery:

If you're using the cursor to return a result set, you can achieve similarbehavior usingtemporary tablesin BigQuery.

Dynamic SQL statements

Thescripting featurein BigQuery supports dynamic SQL statements like those shown in the followingtable.

Amazon RedshiftBigQuery
EXECUTEEXECUTE IMMEDIATE

Flow-of-control statements

Amazon RedshiftBigQuery
IF..THEN..ELSIF..THEN..ELSE..ENDIFIF condition
THEN stmts
ELSE stmts
END IF
name CURSOR[ ( arguments ) ] FOR query Cursors or sessions are not used in BigQuery.
[<LOOP
sql_statement_list END LOOP;
WHILEcondition LOOP stmts END LOOPWHILE condition
DO stmts
END WHILE
EXITBREAK

Consistency guarantees and transaction isolation

Both Amazon Redshift and BigQuery are atomic—that is, ACID-compliant on a per-mutationlevel across many rows.

Transactions

Amazon Redshift supportsserializable isolationby default for transactions. Amazon Redshift lets youspecifyany of the four SQL standard transaction isolation levels but processes allisolation levels as serializable.

BigQuery alsosupports transactions.BigQuery helps ensureoptimistic concurrency control(first to commit has priority) withsnapshotisolation,in which a query reads the last committed data before the query starts. Thisapproach guarantees the same level of consistency on a per-row, per-mutationbasis and across rows within the same DML statement, yet avoids deadlocks. Inthe case of multiple DML updates against the same table, BigQuery switches topessimistic concurrency control.Load jobs can run completely independently and append to tables.

Rollback

If Amazon Redshift encounters any error while running a stored procedure, it rollsback all changes made in a transaction. Additionally, you can use theROLLBACKtransaction control statement in a stored procedure to discard all changes.

In BigQuery, you can use theROLLBACK TRANSACTION statement.

Database limits

Check theBigQuery public documentationfor the latest quotas and limits. Many quotas for large-volume users can beraised by contacting the Cloud support team. The following table shows acomparison of the Amazon Redshift and BigQuery database limits.

LimitAmazon RedshiftBigQuery
Tables in each database for large and xlarge cluster node types9,900Unrestricted
Tables in each database for 8xlarge cluster node types20,000Unrestricted
User-defined databases you can create for each cluster60Unrestricted
Maximum row size4 MB100 MB

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.