Transcription
Amazon Redshift to BigQuerySQL translation reference
About this SQL translation reference3Data types3Implicit conversion types5Explicit conversion types5Query syntax5SELECT statement6FROM clause6JOIN types7WITH clause7Set operators7ORDER BY clause8Conditions9Functions10Aggregate functions10Bitwise aggregate functions11Window functions11Conditional expressions15Date and time functions16Mathematical operators22Math functions24String functions25Data type formatting functions30DML syntax31INSERT statement31COPY statement31UPDATE statement32DELETE, TRUNCATE statements32MERGE statement33Merge operation by replacing existing rowsDDL syntax3335SELECT INTO statement35CREATE TABLE statement35Temporary tables37CREATE VIEW statement37User-defined functions (UDFs)381
CREATE FUNCTION syntax38DROP FUNCTION syntax40UDF components41Metadata and transaction SQL statementsMulti-statement and multi-line SQL statementsProcedural SQL statements434344CREATE PROCEDURE statement44Variable declaration and assignment44Error condition handlers44Cursor declarations and operations45Dynamic SQL statements45Flow-of-control statements45Consistency guarantees and transaction isolation46Transactions46Rollback46Database limits462
About this SQL translation referenceThis document details the similarities and differences in SQL syntax between Redshift andBigQuery. The document can help accelerate the planning and execution of moving yourenterprise data warehouse (EDW) to BigQuery. Redshift data warehousing is designed to workwith Redshift-specific SQL syntax. Scripts written for Redshift might need to be alteredbefore you can use them in BigQuery because the SQL dialects vary between the services.Note: In some cases, there is no direct mapping between a SQL element in Redshift andBigQuery. However, in most cases, you can achieve the same functionality in BigQuery thatyou can in Redshift using alternative means, as shown in the examples in this document.This document is part of a series that discusses migrating data from Redshift to BigQuery. It isa companion to the following document: Amazon Redshift to BigQuery migration guideHighlightsPurposeTo detail common similarities and differences in SQL syntaxbetween Redshift and BigQuery and help accelerate the planningand execution of moving your enterprise data warehouse (EDW) toBigQuery.Intended audienceEnterprise architects, DBAs, application developers, and ITsecurity.Key assumptionsThat the audience is familiar with Redshift and is looking forguidance on transitioning to BigQuery.Data typesThis section shows equivalents between data types in Redshift and in BigQuery.RedshiftData s S MALLINT is 2 bytes, whereasBigQuery’s I NT64 is 8 bytes.3
INTEGERINT ,INT4INT64Redshift’s I NTEGER is 4 bytes, whereasBigQuery’s I NT64 is 8 bytes.BIGINTINT8INT64Both Redshift’s B IGINT and BigQuery’s INT64are 8 PRECISIONFLOAT8 ,FLOATFLOAT64BOOLEANBOOLBOOLCHARCHARACTER ,NCHAR ,BPCHARSTRINGVARCHARCHARACTER VARYING , STRINGNVARCHAR ,TEXTDATERedshift’s R EAL is 4 bytes, whereas BigQuery’sFLOAT64 is 8 bytes.Redshift’s B OOLEAN can use TRUE , t ,t rue ,y , yes ,and 1 as valid literal values for t rue . BigQuery’sBOOL data type uses case-insensitive T RUE .DATETIMESTAMPTIMESTAMP WITHOUTTIME ZONETIMESTAMPTZ TIMESTAMP WITHTIME ZONEGEOMETRYDATETIMETIMESTAMPNote: In BigQuery, time zones are used whenparsing timestamps or formatting timestampsfor display. A string-formatted timestampmight include a time zone, but when BigQueryparses the string, it stores the timestamp in theequivalent UTC time. When a time zone is notexplicitly specified, the default time zone, UTC,is used. Time zone names or offset from UTCusing ( - )HH:MM are supported, but timezone abbreviations such as PDT are notsupported.GEOGRAPHY Support for querying geospatial data.BigQuery also has the following data types that do not have a direct Redshift analog: ARRAYBYTESTIMESTRUCT4
Implicit conversion typesWhen migrating to BigQuery, you need to convert most of your Redshift implicit conversionsto BigQuery’s explicit conversions except for the following data types, which BigQueryimplicitly converts.BigQuery performs implicit conversions for the following d ata types :From BigQuery typeTo BigQuery typeINT64FLOAT64INT64NUMERICNUMERICFLOAT64BigQuery also performs implicit conversions for the following l iterals :From BigQuery typeTo BigQuery typeSTRING literal(e.g. "2008-12-25")DATESTRING literal(e.g. "2008-12-25 15:30:00")TIMESTAMPSTRING literal(e.g. "2008-12-25T07:30:00")DATETIMESTRING literal(e.g. "15:30:00”)TIMEExplicit conversion typesYou can convert Redshift data types that BigQuery doesn’t implicitly convert usingBigQuery’s C AST(expression AS type) function or any of the DATE and TIMESTAMPconversion functions .When migrating your queries, change any occurrences of the Redshift C ONVERT(type,expression) function (or the : : syntax) to BigQuery’s C AST(expression AS type) function,as shown in the table in the D ata type formatting functions section .Query syntaxThis section addresses differences in query syntax between Redshift and BigQuery.5
SELECT statementMost R edshift S ELECT statements are compatible with BigQuery. The following table containsa list of minor differences.RedshiftBigQuerySELECT TOP number expressionFROM tableSELECT expressionFROM tableORDER BY expression DESCLIMIT numberSELECTx/total AS probability,ROUND(100 * probability, 1) AS pctFROM raw dataSELECTx/total AS probability,ROUND(100 * (x/total), 1) AS pctFROM raw dataNote: Redshift supports creating andreferencing an alias in the same SELECTstatement.BigQuery also supports the following expressions in S ELECT statements, which do not have aRedshift equivalent: EXCEPTREPLACEFROM clauseA FROM clause in a query lists the table references that data is selected from. In Redshift,possible table references include tables, views, and subqueries. All of these table referencesare supported in BigQuery.BigQuery tables can be referenced in the FROM clause using the following: [project id].[dataset id].[table name][dataset id].[table name][table name]BigQuery also supports a dditional table references : Historical versions of the table definition and rows using F OR SYSTEM TIME AS OF .Field paths , or any path that resolves to a field within a data type (such as a S TRUCT ).Flattened arrays .6
JOIN typesBoth Redshift and BigQuery support the following types of join: [INNER] JOINLEFT [OUTER] JOINRIGHT [OUTER] JOINFULL [OUTER] JOINCROSS JOIN and the equivalent i mplicit comma cross joinThe following table contains a list of minor differences.RedshiftBigQuerySELECT col1FROM table1NATURAL INNER JOINtable2SELECT col1FROM table1INNER JOINtable2USING (col1, col2 [, .])Note : In BigQuery, J OIN clauses require a J OINcondition unless the clause is a CROSS JOIN or oneof the joined tables is a field within a data type or anarray.WITH clauseA BigQuery WITH clause contains one or more named subqueries that execute when asubsequent S ELECT statement references them. R edshift WITH clauses behave the same asBigQuery’s with the exception that you can evaluate the clause once and reuse its results.Set operatorsThere are some minor differences between R edshift set operators and BigQuery setoperators . However, all set operations that are feasible in Redshift are replicable in BigQuery.RedshiftBigQuerySELECT * FROM table1UNIONSELECT * FROM table2SELECT * FROM table1UNION DISTINCTSELECT * FROM table2Note: Both BigQuery and Redshift support theUNION ALL operator.SELECT * FROM table1INTERSECTSELECT * FROM table2SELECT * FROM table1INTERSECT DISTINCTSELECT * FROM table27
SELECT * FROM table1EXCEPTSELECT * FROM table2SELECT * FROM table1EXCEPT DISTINCTSELECT * FROM table2SELECT * FROM table1MINUSSELECT * FROM table2SELECT * FROM table1EXCEPT DISTINCTSELECT * FROM table2SELECT * FROM table1UNIONSELECT * FROM table2EXCEPTSELECT * FROM table3SELECT * FROM table1UNION ALL(SELECT * FROM table2EXCEPTSELECT * FROM table3)Note : BigQuery requires parentheses toseparate different set operations. If the sameset operator is repeated, parentheses are notnecessary.ORDER BY clauseThere are some minor differences between R edshift ORDER BY clauses and BigQuery ORDERBY clauses .RedshiftBigQueryIn Redshift, NULLS are rankedlast by default (ascendingorder).In BigQuery, N ULLS are ranked first by default (ascendingorder).SELECT *FROM tableORDER BY expressionLIMIT ALLSELECT *FROM tableORDER BY expressionSELECT *FROM tableORDER BY expressionOFFSET 10SELECT *FROM tableORDER BY expressionLIMIT count OFFSET 10Note: BigQuery does not use the LIMIT ALL syntax, butORDER BY sorts all rows by default, resulting in the samebehavior as Redshift’s LIMIT ALL clause. We highlyrecommend including a LIMIT clause with every O RDER BYclause. Ordering all result rows unnecessarily degrades queryexecution performance.Note: In BigQuery, OFFSET must be used together with aLIMIT count . Make sure to set the count INT64 value to the8
minimum necessary ordered rows. Ordering all result rowsunnecessarily degrades query execution performance.ConditionsThe following table shows R edshift conditions , or predicates, that are specific to Redshift andmust be converted to their BigQuery equivalent.RedshiftBigQuerya ANY (subquery)a IN subquerya SOME (subquery)a ALL (subquery)a NOT IN subquerya ! ALL (subquery)a I S UNKNOWNa IS NULLexpression ILIKE patternLOWER (expression) LIKE LOWER (pattern)expression LIKE pattern ESCAPE'escape char'expression L IKE patternexpression [NOT] S IMILAR TOpatternIF(LENGTH(REGEXP REPLACE(expression,pattern,'') 0,True,False)Note: BigQuery does not support custom escapecharacters. You must use two backslashes \\ asescape characters for BigQuery.Note: If NOT is specified, wrap the above I Fexpression in a NOT expression as shown below:NOT (IF(LENGTH(.)expression [!] pattern[ NOT ] REGEXP CONTAINS (expression,regex)9
FunctionsThe following sections list Redshift functions and their BigQuery equivalents.Aggregate functionsThe following table shows mappings between common Redshift aggregate, aggregateanalytic, and approximate aggregate functions with their BigQuery equivalents.RedshiftBigQueryAPPROXIMATE 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 (STRING AGG ([DISTINCT] aggregate expression[DISTINCT] aggregate expression[, delimiter][, delimiter]) [WITHIN GROUP (ORDER BY order list)][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: D oes 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)10
BigQuery also offers the following a ggregate , a ggregate analytic , and a pproximate aggregatefunctions, which do not have a direct analogue in Redshift: ANY VALUEAPPROX TOP COUNTAPPROX TOP SUMARRAY AGGARRAY CONCAT AGGCOUNTIFCORRCOVAR POPCOVAR SAMPBitwise aggregate functionsThe following table shows mappings between common Redshift bitwise aggregate functionswith their BigQuery equivalents.RedshiftBigQueryBIT AND (expression)BIT ADD (expression)BIT OR (expression)BIT OR (expression)BOOL AND (expression)LOGICAL AND (expression)BOOL OR (expression)LOGICAL OR (expression)BigQuery also offers the following b it-wise aggregate function, which does not have a directanalogue in Redshift: BIT XORWindow functionsThe following table shows mappings between common Redshift window functions with theirBigQuery equivalents. Windowing functions in BigQuery include a nalytic aggregate functions ,aggregate functions , n avigation functions , and n umbering functions .RedshiftBigQueryAVG (expression) OVER([PARTITION BY expr list][ORDER BY order listframe clause])AVG (expression) OVER([PARTITION BY expr list][ORDER BY order list][frame clause])11
COUNT (expression) OVER([PARTITION BY expr list][ORDER BY order listframe 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 listframe 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 listframe clause])LAST VALUE (expression) OVER([PARTITION BY expr list][ORDER BY order listframe 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]STRING AGG ([DISTINCT] aggregate expression[, delimiter]12
) [WITHIN GROUP (ORDER BY order list)] ) OVEROVER(([PARTITION BY partition list][PARTITION BY partition expression][ORDER BY order list]))MAX (expression) OVER([PARTITION BY expr list][ORDER BY order listframe 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 listframe 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)13
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 ratio expression/ SUM (ratio expression)(OVER[PARTITION BY partition expression] ()[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 listframe 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 listframe clause])STDDEV SAMP (expression) OVER([PARTITION BY expr list][ORDER BY order list][frame clause])14
STDDEV POP (expression) OVER([PARTITION BY expr list][ORDER BY order listframe 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 listframe clause])SUM (expression) OVER([PARTITION BY expr list][ORDER BY order list][frame clause])VAR SAMP (expression) OVER([PARTITION BY expr list][ORDER BY order listframe clause])VAR SAMP (expression) OVER([PARTITION BY expr list][ORDER BY order list][frame clause])VAR POP (expression) OVER([PARTITION BY expr list][ORDER BY order listframe clause])VAR POP (expression) OVER([PARTITION BY expr list][ORDER BY order list][frame clause])VARIANCE (expression) OVER([PARTITION BY expr list][ORDER BY order listframe clause])VARIANCE (expression) OVER([PARTITION BY expr list][ORDER BY order list][frame clause])Conditional expressionsThe following table shows mappings between common Redshift conditional expressions withtheir BigQuery equivalents.RedshiftBigQueryCASE expressionWHEN value THEN result[WHEN.][ELSE else result]ENDCASE expressionWHEN value THEN result[WHEN.][ELSE else result]ENDCOALESCE (expression1[, .])COALESCE (expression1[, .])DECODE (CASE expression15
expression,search1, result1[, search2, result2.][, default]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 a directanalogue in Redshift: IFIFNULLDate and time functionsThe following table shows mappings between common Redshift date and time functions withtheir BigQuery equivalents. BigQuery data and time functions include d ate functions , datetimefunctions , t ime functions , and t imestamp functions .Keep in mind that functions that seem identical in Redshift and BigQuery might returndifferent data types.RedshiftBigQueryADD MONTHS (date, integer)CAST ( DATE ADD (date,INTERVAL integer MONTH) AS TIMESTAMP)16
timestamptz or timestamp A T TIMEZONE timezonePARSE TIMESTAMP ("%c%z", FORMAT TIMESTAMP ("%c%z", timestamptz or timestamp , timezone))Note: Time zones are used when parsing timestamps orformatting timestamps for display. A string-formattedtimestamp might include a time zone, but whenBigQuery parses the string, it stores the timestamp inthe equivalent UTC time. When a time zone is notexplicitly specified, the default time zone, UTC, is used.Time zone names or offset from UTC ( -HH:MM ) aresupported, but time zone abbreviations (such as PDT)are not supported.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 DATECURRENT DATE ()Note: Returns start date for the currenttransaction in the current session timezone (UTC by default).Note: Returns start date for the current statement inUTC time zone.DATE CMP (date1, date2)CASEWHEN date1 date2 THEN 0WHEN date1 date2 THEN 1ELSE -1END17
DATE CMP TIMESTAMP (date1, date2)CASEWHEN date1 CAST(date2 AS DATE)THEN 0WHEN date1 CAST(date2 AS DATE)THEN 1ELSE -1ENDDATE CMP TIMESTAMPTZ (date, timestamptz) CASE WHEN date DATE (timestamptz) THEN 1WHEN date D ATE (timestamptz) THEN -1ELSE 0ENDDATE 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)For intervals in Redshift, there are 360 days in a year. InBigQuery, you can use the following UDF to parse aRedshift interval and translate it to seconds.CREATE TEMP FUNCTIONparse interval(interval literal STRING) AS (18
(select sum(casewhen unit in ('minutes', 'minute', 'm' )then num * 60when unit in ('hours', 'hour', 'h') then num* 60 * 60when unit in ('days', 'day', 'd' ) then num* 60 * 60 * 24when unit in ('weeks', 'week', 'w') then num* 60 * 60 * 24 * 7when unit in ('months', 'month' ) then num *60 * 60 * 24 * 30when unit in ('years', 'year') then num * 60* 60 * 24 * 360else numend)from (selectcast(regexp extract(value,r' [0-9]*\.?[0-9] ') as numeric) num,substr(value, length(regexp extract(value,r' [0-9]*\.?[0-9] ')) 1) unitfrom 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 1 MONTH),INTERVAL 1 DAY)19
MONTHS BETWEEN (date1, date2)DATE DIFF (date1,date2,MONTH)NEXT DAY (date, day)DATE ADD ( DATE TRUNC (date,WEEK(day)),INTERVAL 1 WEEK)SYSDATECURRENT TIMESTAMP ()Note: Returns start timestamp for thecurrent transaction in the current sessiontime zone (UTC by default).Note: Returns start timestamp for the currentstatement in UTC time zone.TIMEOFDAY ()FORMAT TIMESTAMP ("%a %b %d %H:%M:%E6S %E4Y%Z", CURRENT TIMESTAMP ())TIMESTAMP CMP (timestamp1,timestamp2)CASEWHEN timestamp1 timestamp2THEN 0WHEN timestamp1 timestamp2THEN 1ELSE -1ENDTIMESTAMP CMP DATE (timestamp,date)CASEWHEN E XTRACT (DATE FROM timestamp) dateTHEN 0WHEN E XTRACT (DATE FROM timestamp) dateTHEN 1ELSE -1ENDTIMESTAMP CMP TIMESTAMPTZ (timestamp,timestamptz)CASEWHEN timestamp timestamptzTHEN 0WHEN timestamp timestamptzTHEN 1ELSE -1Note: R edshift compares timestamps inENDthe user session defined time zone. Defaultuser session time zone is UTC.TIMESTAMPTZ CMP (Note: BigQuery compares timestamps in UTC timezone.CASE20
timestamptz1,timestamptz2)Note: R edshift compares timestamps inthe user session defined time zone. Defaultuser session time zone is UTC.WHEN timestamptz1 timestamptz2THEN 0WHEN timestamptz1 timestamptz2THEN 1ELSE -1ENDNote: BigQuery compares timestamps in UTC timezone.TIMESTAMPTZ CMP DATE (timestamptz,date)CASEWHEN E XTRACT (DATE FROM timestamptz) dateTHEN 0WHEN E XTRACT (DATE FROM timestamptz) dateTHEN 1ELSE -1Note: R edshift compares timestamps inENDthe user session defined time zone. Defaultuser session time zone is UTC.Note: BigQuery compares timestamps in UTC timezone.TIMESTAMPTZ CMP TIMESTAMP (timestamptz,Timestamp)CASEWHEN timestamp timestamptzTHEN 0WHEN timestamp timestamptzTHEN 1ELSE -1Note: R edshift compares timestamps inENDthe user session defined time zone. Defaultuser session time zone is UTC.TIMEZONE (timezone,T imestamptz or timestamp)Note: BigQuery compares timestamps in UTC timezone.PARSE TIMESTAMP ("%c%z", FORMAT TIMESTAMP ("%c%z", timestamptz or timestamp , timezone))Note: Time zones are used when parsing timestamps orformatting timestamps for display. A string-formattedtimestamp might include a time zone, but whenBigQuery parses the string, it stores the timestamp inthe equivalent UTC time. When a time zone is notexplicitly specified, the default time zone, UTC, is used.Time zone names or offset from UTC ( -HH:MM ) are21
supported but time zone abbreviations (such as PDT)are not supported.PARSE TIMESTAMP ( format , FORMAT TIMESTAMP ( format , timestamp))TO TIMESTAMP (timestamp, format)TRUNC (timestamp)Note: BigQuery follows a different set of formatelements . Time zones are used when parsingtimestamps or formatting timestamps for display. Astring-formatted timestamp might include a time zone,but when BigQuery parses the string, it stores thetimestamp in the equivalent UTC time. When a timezone is not explicitly specified, the default time zone,UTC, is used. Time zone names or offset from UTC( -HH:MM ) are supported in the format string but timezone abbreviations (such as PDT) are not supported.CAST (timestamp AS DATE)BigQuery also offers the following date and time functions, which do not have a directanalogue in Redshift: EXTRACTDATEDATE SUBDATE ADD (returning DATEdata type) DATE FROM UNIX DATEFORMAT DATEPARSE DATEUNIX DATEDATETIME DATETIME ADDDATETIME SUBDATETIME DIFFDATETIME TRUNCFORMAT DATETIMEPARSE DATETIMECURRENT TIMETIMETIME ADDTIME SUB TIME DIFFTIME TRUNCFORMAT TIMEPARSE TIMETIMESTAMP SECONDSTIMESTAMP MILLISTIMESTAMP MICROSUNIX SECONDSUNIX MILLISUNIX MICROSMathematical operatorsThe following table shows mappings between common Redshift mathematical operators withtheir BigQuery equivalents.22
RedshiftBigQueryX YX YX - YX - YX * YX * YX / YIf integer division:Note: If the operator isperforming integer division (inother words, if X and Y are bothintegers), an integer is returned.If the operator is performingnon-integer division, anon-integer is returned.X % YCAST ( 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 zeroerror), use S AFE DIVIDE (X, Y) or IEEE DIVIDE (X, Y) .MOD (X, Y)Note: To prevent errors from a division operation (division byzero error), use S AFE . MOD (X, Y) . SAFE . MOD (X, 0) results in 0.X YPOW (X, Y)POWER (X, Y)Note: Unlike Redshift, the operator in BigQuery performsBitwise xor. / XSQRT (X)Note: To prevent errors from a square root operation (negativeinput), use S AFE . SQRT (X) . Negative input with S AFE . SQRT (X)results in N ULL . / XSIGN (X) * P OWER ( ABS (X), 1/3)Note: BigQuery’s POWER (X, Y) returns an error if X is a finitevalue less than 0 and Y is a noninteger.@ XABS (X)X YX Y23
Note: This operator returns 0 or a byte sequence of b'\x00' ifthe second operand Y is greater than or equal to the bit length ofthe first operand X (for example, 64 if X has the type I NT64 ). Thisoperator throws an error if Y is negative.X YX YNote: Shifts the first operand X to the right. This operator doesnot do sign bit extension with a signed type (it fills vacant bits onthe left with 0 ). This operator returns 0 or a byte sequence ofb'\x00' if the second operand Y is greater than or equal to thebit length of the first operand X (for example, 64 if X has the typeINT64 ). This operator throws an error if Y is negative.X & YX & YX YX Y X XBigQuery also offers the following mathematical operator, which does not have a directanalog in Redshift: X Y (Bitwise xor)Math functionsRedshiftBigQueryABS (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)24
DLOG10 (number)LOG10 (number)EXP (number)EXP (number)FLOOR (number)FLOOR (number)LN (number)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 functionsRedshiftBigQuerystring1 string2CONCAT (string1, string2)BPCHARCMP (string1, string2)CASEWHEN string1 string2 THEN 0WHEN string1 string2 THEN 1ELSE -1ENDBTRIM (string [, matching string])TRIM (string [, matching string])BTTEXT PATTERN CMP (string1, string2)CASEWHEN string1 string2 THEN 0WHEN string1 string2 THEN 1ELSE -1ENDCHAR LENGTH (expression)CHAR LENGTH (expression)CHARACTER LENGTH (expression)CHARACTER LENGTH (expression)CHARINDEX (substring, string)STRPOS (string, substring)CHR (number)CODE POINTS TO STRING ([number])25
CONCAT (string1, string2)CONCAT (string1, string2)Note: BigQuery’s CONCAT (.) supportsconcatenating any number of strings.CRC32Custom user-defined functionFUNC SHA1 (string)SHA1 (string)INITCAPCustom user-defined functionLEFT (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 (string1, 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))If position is specified:ARRAY LENGTH ( REGEXP EXTRACT ALL ( SUBSTR (source string, IF (position 0, 1, position)),pattern))Note: BigQuery provides regular expressionsupport using the re2 library; see thatdocumentation for its regular expressionsyntax.26
IFNULL ( STRPOS (source string, REGEXP EXTRACT (s ource strin
INT64 Redshift's I NTEGER is 4 bytes, whereas BigQuery's I NT64 is 8 bytes. BIGINT INT8 INT64 Both Redshift's B IGINT and BigQuery's I NT64 are 8 bytes. DECIMAL NUMERIC NUMERIC REAL FLOAT4 FLOAT64 Redshift's R EAL is 4 bytes, whereas BigQuery's FLOAT64 is 8 bytes. DOUBLE PRECISION
