Oracle SQL translation guide
This document details the similarities and differences in SQL syntax between Oracle and BigQuery to help you plan your migration. Use batch SQL translation to migrate your SQL scripts in bulk, or interactive SQL translation to translate ad-hoc queries.
Data types
This section shows equivalents between data types in Oracle and in BigQuery.
Oracle | BigQuery | Notes |
---|---|---|
VARCHAR2
|
STRING
|
|
NVARCHAR2
|
STRING
|
|
CHAR
|
STRING
|
|
NCHAR
|
STRING
|
|
CLOB
|
STRING
|
|
NCLOB
|
STRING
|
|
INTEGER
|
INT64
|
|
SHORTINTEGER
|
INT64
|
|
LONGINTEGER
|
INT64
|
|
NUMBER
|
NUMERIC
|
BigQuery does not allow user specification of custom values for precision or scale. As a result, a column in Oracle may be defined so that it has a bigger scale than BigQuery supports.
Additionally, before storing a decimal number Oracle rounds up if that number has more digits after the decimal point than is specified for the corresponding column. In BigQuery this feature could be implemented using |
NUMBER(*, x)
|
NUMERIC
|
BigQuery does not allow user specification of custom values for precision or scale. As a result, a column in Oracle may be defined so that it has a bigger scale than BigQuery supports.
Additionally, before storing a decimal number Oracle rounds up if that number has more digits after the decimal point than is specified for the corresponding column. In BigQuery this feature could be implemented using |
NUMBER(x, -y)
|
INT64
|
If a user tries to store a decimal number, Oracle rounds it up to a whole number. For BigQuery an attempt to store a decimal number in a column defined as INT64 results in an error. In this case, ROUND() function should be applied.
BigQuery |
NUMBER(x)
|
INT64
|
If a user tries to store a decimal number, Oracle rounds it up to a whole number. For BigQuery an attempt to store a decimal number in a column defined as INT64 results in an error. In this case, ROUND() function should be applied.
BigQuery |
FLOAT
|
FLOAT64 /NUMERIC
|
FLOAT is an exact data type, and it's a NUMBER subtype in Oracle. In BigQuery, FLOAT64 is an approximate data type. NUMERIC may be a better match for FLOAT type in BigQuery.
|
BINARY_DOUBLE
|
FLOAT64 /NUMERIC
|
FLOAT is an exact data type, and it's a NUMBER subtype in Oracle. In BigQuery, FLOAT64 is an approximate data type. NUMERIC may be a better match for FLOAT type in BigQuery.
|
BINARY_FLOAT
|
FLOAT64 /NUMERIC
|
FLOAT is an exact data type, and it's a NUMBER subtype in Oracle. In BigQuery, FLOAT64 is an approximate data type. NUMERIC may be a better match for FLOAT type in BigQuery.
|
LONG
|
BYTES
|
LONG data type is used in earlier versions and is not suggested in new versions of Oracle Database.
|
BLOB
|
BYTES
|
BYTES data type can be used to store variable-length binary data. If this field is not queried and not used in analytics, a better option is to store binary data in Cloud Storage.
|
BFILE
|
STRING
|
Binary files can be stored in Cloud Storage and STRING data type can be used for referencing files in a BigQuery table.
|
DATE
|
DATETIME
|
|
TIMESTAMP
|
TIMESTAMP
|
BigQuery supports microsecond precision (10-6) in comparison to Oracle which supports precision ranging from 0 to 9.
BigQuery supports a time zone region name from a TZ database and time zone offset from UTC.
In BigQuery a time zone conversion should be manually performed to match Oracle's |
TIMESTAMP(x)
|
TIMESTAMP
|
BigQuery supports microsecond precision (10-6) in comparison to Oracle which supports precision ranging from 0 to 9.
BigQuery supports a time zone region name from a TZ database and time zone offset from UTC.
In BigQuery a time zone conversion should be manually performed to match Oracle's |
TIMESTAMP WITH TIME ZONE
|
TIMESTAMP
|
BigQuery supports microsecond precision (10-6) in comparison to Oracle which supports precision ranging from 0 to 9.
BigQuery supports a time zone region name from a TZ database and time zone offset from UTC.
In BigQuery a time zone conversion should be manually performed to match Oracle's |
TIMESTAMP WITH LOCAL TIME ZONE
|
TIMESTAMP
|
BigQuery supports microsecond precision (10-6) in comparison to Oracle which supports precision ranging from 0 to 9.
BigQuery supports a time zone region name from a TZ database and time zone offset from UTC.
In BigQuery a time zone conversion should be manually performed to match Oracle's |
INTERVAL YEAR TO MONTH
|
STRING
|
Interval values can be stored as STRING data type in BigQuery.
|
INTERVAL DAY TO SECOND
|
STRING
|
Interval values can be stored as STRING data type in BigQuery.
|
RAW
|
BYTES
|
BYTES data type can be used to store variable-length binary data. If this field is not queried and used in analytics, a better option is to store binary data on Cloud Storage.
|
LONG RAW
|
BYTES
|
BYTES data type can be used to store variable-length binary data. If this field is not queried and used in analytics, a better option is to store binary data on Cloud Storage.
|
ROWID
|
STRING
|
These data types are used Oracle internally to specify unique addresses to rows in a table. Generally, ROWID or UROWID field should not be used in applications. But if this is the case, STRING data type can be used to hold this data.
|
Type formatting
Oracle SQL uses a set of default formats set as parameters for displaying
expressions and column data, and for conversions between data types. For
example, NLS_DATE_FORMAT
set as YYYY/MM/DD
formats dates as YYYY/MM/DD
by default. You can find more information about the NLS settings in the Oracle
online documentation.
In BigQuery, there are no initialization parameters.
By default, BigQuery expects all source data to be UTF-8 encoded when loading. Optionally, if you have CSV files with data encoded in ISO-8859-1 format, you can explicitly specify the encoding when you import your data so that BigQuery can properly convert your data to UTF-8 during the import process.
It is only possible to import data that is ISO-8859-1 or UTF-8
encoded. BigQuery stores and returns the data as UTF-8 encoded.
Intended date format or time zone can be set in
DATE
and
TIMESTAMP
functions.
Timestamp and date type formatting
When you convert timestamp and date formatting elements from Oracle to
BigQuery, you must pay attention to time zone differences between
TIMESTAMP
and DATETIME
as summarized in the following table.
Notice there are no parentheses in the Oracle formats because the formats
(CURRENT_*
) are keywords, not functions.
Oracle | BigQuery | Notes | |
---|---|---|---|
CURRENT_TIMESTAMP
|
TIMESTAMP information in Oracle can have different time zone
information, which is defined using WITH TIME ZONE in column
definition or setting TIME_ZONE
variable.
|
If possible, use the CURRENT_TIMESTAMP()
function, which is
formatted in ISO format. However, the output format does always show the
UTC time zone. (Internally, BigQuery does not have a time
zone.)
Note the following details on differences in the ISO format:
If you want to use an explicit format, use the |
|
CURRENT_DATE
|
Oracle uses 2 types for date:
SYSDATE or CURRENT_DATE
|
BigQuery has a separate DATE format that always returns a date in ISO 8601 format.
|
|
CURRENT_DATE-3
|
Date values are represented as integers. Oracle supports arithmetic operators for date types. | For date types, use DATE_ADD () or DATE_SUB ().
BigQuery uses arithmetic operators for data types: INT64 , NUMERIC , and FLOAT64 .
|
|
NLS_DATE_FORMAT
|
Set the session or system date format. | BigQuery always uses ISO 8601, so make sure you convert Oracle dates and times. |
Query syntax
This section addresses differences in query syntax between Oracle and BigQuery.
SELECT
statements
Most Oracle SELECT
statements are compatible with BigQuery.
Functions, operators, and expressions
The following sections list mappings between Oracle functions and BigQuery equivalents.
Comparison operators
Oracle and BigQuery comparison operators are ANSI SQL:2011
compliant. The comparison operators in the table below are the same in both
BigQuery and Oracle. You can use
REGEXP_CONTAINS
instead of REGEXP_LIKE
in BigQuery.
Operator | Description |
---|---|
"="
|
Equal |
<>
|
Not equal |
!=
|
Not equal |
>
|
Greater than |
>=
|
Greater than or equal |
<
|
Less than |
<=
|
Less than or equal |
IN ( )
|
Matches a value in a list |
NOT
|
Negates a condition |
BETWEEN
|
Within a range (inclusive) |
IS NULL
|
NULL value
|
IS NOT NULL
|
Not NULL value
|
LIKE
|
Pattern matching with % |
EXISTS
|
Condition is met if subquery returns at least one row |
The operators on the table are the same both in BigQuery and Oracle.
Logical expressions and functions
Aggregate functions
The following table shows mappings between common Oracle aggregate, statistical aggregate, and approximate aggregate functions with their BigQuery equivalents:
Oracle | BigQuery |
---|---|
ANY_VALUE (from Oracle 19c) |
ANY_VALUE |
APPROX_COUNT
|
HLL_COUNT set of functions with specified precision
|
APPROX_COUNT_DISTINCT
|
APPROX_COUNT_DISTINCT
|
APPROX_COUNT_DISTINCT_AGG
|
APPROX_COUNT_DISTINCT
|
APPROX_COUNT_DISTINCT_DETAIL
|
APPROX_COUNT_DISTINCT
|
APPROX_PERCENTILE(percentile) WITHIN GROUP (ORDER BY expression)
|
APPROX_QUANTILES(expression, 100)[ BigQuery doesn't support the rest of arguments that Oracle defines. |
APPROX_PERCENTILE_AGG
|
APPROX_QUANTILES(expression, 100)[
|
APPROX_PERCENTILE_DETAIL
|
APPROX_QUANTILES(expression, 100)[OFFSET(CAST(TRUNC(percentile * 100) as INT64))]
|
APPROX_SUM
|
APPROX_TOP_SUM(expression, weight, number)
|
AVG
|
AVG
|
BIT_COMPLEMENT
|
bitwise not operator: ~ |
BIT_OR
|
BIT_OR, X | Y
|
BIT_XOR
|
BIT_XOR, X ^ Y
|
BITAND
|
BIT_AND, X & Y
|
CARDINALITY
|
COUNT
|
COLLECT
|
BigQuery doesn't support TYPE AS TABLE OF .
Consider using STRING_AGG() or ARRAY_AGG() in
BigQuery
|
CORR/CORR_K/
CORR_S
|
CORR
|
COUNT
|
COUNT
|
COVAR_POP
|
COVAR_POP
|
COVAR_SAMP
|
COVAR_SAMP
|
FIRST
|
Does not exist implicitly in BigQuery. Consider using user-defined functions (UDFs). |
GROUP_ID
|
Not used in BigQuery |
GROUPING
|
Not used in BigQuery |
GROUPING_ID
|
Not used in BigQuery. |
LAST
|
Does not exist implicitly in BigQuery. Consider using UDFs. |
LISTAGG
|
STRING_AGG, ARRAY_CONCAT_AGG(expression [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
|
MAX
|
MAX
|
MIN
|
MIN
|
OLAP_CONDITION
|
Oracle specific, does not exist in BigQuery. |
OLAP_EXPRESSION
|
Oracle specific, does not exist in BigQuery. |
OLAP_EXPRESSION_BOOL
|
Oracle specific, does not exist in BigQuery. |
OLAP_EXPRESSION_DATE
|
Oracle specific, does not exist in BigQuery. |
OLAP_EXPRESSION_TEXT
|
Oracle specific, does not exist in BigQuery. |
OLAP_TABLE
|
Oracle specific, does not exist in BigQuery. |
POWERMULTISET
|
Oracle specific, does not exist in BigQuery. |
POWERMULTISET_BY_CARDINALITY
|
Oracle specific, does not exist in BigQuery. |
QUALIFY
|
Oracle specific, does not exist in BigQuery. |
REGR_AVGX
|
AVG( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, ind_var_expr) )
|
REGR_AVGY
|
AVG( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, dep_var_expr) )
|
REGR_COUNT
|
SUM( IF(dep_var_expr is NULL OR ind_var_expr is NULL, NULL, 1) )
|
REGR_INTERCEPT
|
AVG(dep_var_expr)
|
REGR_R2
|
(COUNT(dep_var_expr) *
|
REGR_SLOPE
|
COVAR_SAMP(ind_var_expr,
|
REGR_SXX
|
SUM(POWER(ind_var_expr, 2)) - COUNT(ind_var_expr) * POWER(AVG(ind_var_expr),2)
|
REGR_SXY
|
SUM(ind_var_expr*dep_var_expr) - COUNT(ind_var_expr) * AVG(ind) * AVG(dep_var_expr)
|
REGR_SYY
|
SUM(POWER(dep_var_expr, 2)) - COUNT(dep_var_expr) * POWER(AVG(dep_var_expr),2)
|
ROLLUP
|
ROLLUP
|
STDDEV_POP
|
STDDEV_POP
|
STDDEV_SAMP
|
STDDEV_SAMP, STDDEV
|
SUM
|
SUM
|
VAR_POP
|
VAR_POP
|
VAR_SAMP
|
VAR_SAMP, VARIANCE
|
WM_CONCAT
|
STRING_AGG
|
BigQuery offers the following additional aggregate functions:
Analytical functions
The following table shows mappings between common Oracle analytic and aggregate analytic functions with their BigQuery equivalents.
Oracle | BigQuery |
---|---|
AVG
|
AVG
|
BIT_COMPLEMENT
|
bitwise not operator: ~ |
BIT_OR
|
BIT_OR, X | Y
|
BIT_XOR
|
BIT_XOR, X ^ Y
|
BITAND
|
BIT_AND, X & Y
|
BOOL_TO_INT
|
CAST(X AS INT64)
|
COUNT
|
COUNT
|
COVAR_POP
|
COVAR_POP
|
COVAR_SAMP
|
COVAR_SAMP
|
CUBE_TABLE
|
Isn't supported in BigQuery. Consider using a BI tool or a custom UDF |
CUME_DIST
|
CUME_DIST
|
DENSE_RANK(ANSI)
|
DENSE_RANK
|
FEATURE_COMPARE
|
Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML |
FEATURE_DETAILS
|
Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML |
FEATURE_ID
|
Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML |
FEATURE_SET
|
Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML |
FEATURE_VALUE
|
Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML |
FIRST_VALUE
|
FIRST_VALUE
|
HIER_CAPTION
|
Hierarchical queries are not supported in BigQuery. |
HIER_CHILD_COUNT
|
Hierarchical queries are not supported in BigQuery. |
HIER_COLUMN
|
Hierarchical queries are not supported in BigQuery. |
HIER_DEPTH
|
Hierarchical queries are not supported in BigQuery. |
HIER_DESCRIPTION
|
Hierarchical queries are not supported in BigQuery. |
HIER_HAS_CHILDREN
|
Hierarchical queries are not supported in BigQuery. |
HIER_LEVEL
|
Hierarchical queries are not supported in BigQuery. |
HIER_MEMBER_NAME
|
Hierarchical queries are not supported in BigQuery. |
HIER_ORDER
|
Hierarchical queries are not supported in BigQuery. |
HIER_UNIQUE_MEMBER_NAME
|
Hierarchical queries are not supported in BigQuery. |
LAST_VALUE
|
LAST_VALUE
|
LAG
|
LAG
|
LEAD
|
LEAD
|
LISTAGG
|
ARRAY_AGG
|
MATCH_NUMBER
|
Pattern recognition and calculation can be done with regular expressions and UDFs in BigQuery |
MATCH_RECOGNIZE
|
Pattern recognition and calculation can be done with regular expressions and UDFs in BigQuery |
MAX
|
MAX
|
MEDIAN
|
PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER()
|
MIN
|
MIN
|
NTH_VALUE
|
NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])
|
NTILE
|
NTILE(constant_integer_expression)
|
PERCENT_RANK
|
PERCENT_RANK
|
PERCENTILE_CONT
|
PERCENTILE_CONT
|
PERCENTILE_CONT
|
PERCENTILE_DISC
|
PRESENTNNV
|
Oracle specific, does not exist in BigQuery. |
PRESENTV
|
Oracle specific, does not exist in BigQuery. |
PREVIOUS
|
Oracle specific, does not exist in BigQuery. |
RANK (ANSI)
|
RANK
|
RATIO_TO_REPORT(expr) OVER (partition clause)
|
expr / SUM(expr) OVER (partition clause)
|
ROW_NUMBER
|
ROW_NUMBER
|
STDDEV_POP
|
STDDEV_POP
|
STDDEV_SAMP
|
STDDEV_SAMP, STDDEV
|
SUM
|
SUM
|
VAR_POP
|
VAR_POP
|
VAR_SAMP
|
VAR_SAMP, VARIANCE
|
VARIANCE
|
VARIANCE()
|
WIDTH_BUCKET
|
UDF can be used. |
Date/time functions
The following table shows mappings between common Oracle date/time functions and their BigQuery equivalents.
Oracle | BigQuery |
---|---|
ADD_MONTHS(date, integer)
|
DATE_ADD(date, INTERVAL integer MONTH), If date is a TIMESTAMP you can use
|
CURRENT_DATE
|
CURRENT_DATE
|
CURRENT_TIME
|
CURRENT_TIME
|
CURRENT_TIMESTAMP
|
CURRENT_TIMESTAMP
|
DATE - k
|
DATE_SUB(date_expression, INTERVAL k DAY)
|
DATE + k
|
DATE_ADD(date_expression, INTERVAL k DAY)
|
DBTIMEZONE
|
BigQuery does not support the database time zone. |
EXTRACT
|
EXTRACT(DATE), EXTRACT(TIMESTAMP)
|
LAST_DAY
|
DATE_SUB(
|
LOCALTIMESTAMP
|
BigQuery doesn't support time zone settings. |
MONTHS_BETWEEN
|
DATE_DIFF(date_expression, date_expression, MONTH)
|
NEW_TIME
|
DATE(timestamp_expression, time zone)
|
NEXT_DAY
|
DATE_ADD(
|
SYS_AT_TIME_ZONE
|
CURRENT_DATE([time_zone])
|
SYSDATE
|
CURRENT_DATE()
|
SYSTIMESTAMP
|
CURRENT_TIMESTAMP()
|
TO_DATE
|
PARSE_DATE
|
TO_TIMESTAMP
|
PARSE_TIMESTAMP
|
TO_TIMESTAMP_TZ
|
PARSE_TIMESTAMP
|
TZ_OFFSET
|
Isn't supported in BigQuery. Consider using a custom UDF. |
WM_CONTAINS WM_EQUALS WM_GREATERTHAN WM_INTERSECTION WM_LDIFF WM_LESSTHAN WM_MEETS WM_OVERLAPS WM_RDIFF |
Periods are not used in BigQuery. UDFs can be used to compare two periods. |
BigQuery offers the following additional date/time functions:
CURRENT_DATETIME
DATE_FROM_UNIX_DATE
DATE_TRUNC
DATETIME
DATETIME_ADD
DATETIME_DIFF
DATETIME_SUB
DATETIME_TRUNC
FORMAT_DATE
FORMAT_DATETIME
String functions
The following table shows mappings between Oracle string functions and their BigQuery equivalents:
Oracle | BigQuery |
---|---|
ASCII
|
TO_CODE_POINTS(string_expr)[OFFSET(0)]
|
ASCIISTR
|
BigQuery doesn't support UTF-16 |
RAWTOHEX
|
TO_HEX
|
LENGTH
|
CHAR_LENGTH
|
LENGTH
|
CHARACTER_LENGTH
|
CHR
|
CODE_POINTS_TO_STRING(
|
COLLATION
|
Doesn't exist in BigQuery. BigQuery doesn't support COLLATE in DML |
COMPOSE
|
Custom user-defined function. |
CONCAT, (|| operator)
|
CONCAT
|
DECOMPOSE
|
Custom user-defined function. |
ESCAPE_REFERENCE (UTL_I18N)
|
Is not supported in BigQuery. Consider using a user-defined function. |
INITCAP
|
Custom user-defined function. |
INSTR/INSTR2/INSTR4/INSTRB/INSTRC
|
Custom user-defined function. |
LENGTH/LENGTH2/LENGTH4/LENGTHB/LENGTHC
|
LENGTH
|
LOWER
|
LOWER
|
LPAD
|
LPAD
|
LTRIM
|
LTRIM
|
NLS_INITCAP
|
Custom user-defined function. |
NLS_LOWER
|
LOWER
|
NLS_UPPER
|
UPPER
|
NLSSORT
|
Oracle specific, does not exist in BigQuery. |
POSITION
|
STRPOS(string, substring)
|
PRINTBLOBTOCLOB
|
Oracle specific, does not exist in BigQuery. |
REGEXP_COUNT
|
ARRAY_LENGTH(REGEXP_EXTRACT_ALL(value, regex))
|
REGEXP_INSTR
|
STRPOS(source_string, REGEXP_EXTRACT(source_string, regexp_string))
Note: Returns first occurrence. |
REGEXP_REPLACE
|
REGEXP_REPLACE
|
REGEXP_LIKE
|
IF(REGEXP_CONTAINS,1,0)
|
REGEXP_SUBSTR
|
REGEXP_EXTRACT, REGEXP_EXTRACT_ALL
|
REPLACE
|
REPLACE
|
REVERSE
|
REVERSE
|
RIGHT
|
SUBSTR(source_string, -1, length)
|
RPAD
|
RPAD
|
RTRIM
|
RTRIM
|
SOUNDEX
|
Isn't supported in BigQuery. Consider using a custom UDF |
STRTOK
|
SPLIT(instring, delimiter)[ORDINAL(tokennum)]
|
SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4
|
SUBSTR
|
TRANSLATE
|
REPLACE
|
TRANSLATE USING
|
REPLACE
|
TRIM
|
TRIM
|
UNISTR
|
CODE_POINTS_TO_STRING
|
UPPER
|
UPPER
|
|| (VERTICAL BARS)
|
CONCAT
|
BigQuery offers the following additional string functions:
BYTE_LENGTH
CODE_POINTS_TO_BYTES
ENDS_WITH
FROM_BASE32
FROM_BASE64
FROM_HEX
NORMALIZE
NORMALIZE_AND_CASEFOLD
REPEAT
SAFE_CONVERT_BYTES_TO_STRING
SPLIT
STARTS_WITH
STRPOS
TO_BASE32
TO_BASE64
TO_CODE_POINTS
Math functions
The following table shows mappings between Oracle math functions and their BigQuery equivalents.
Oracle | BigQuery |
---|---|
ABS
|
ABS
|
ACOS
|
ACOS
|
ACOSH
|
ACOSH
|
ASIN
|
ASIN
|
ASINH
|
ASINH
|
ATAN
|
ATAN
|
ATAN2
|
ATAN2
|
ATANH
|
ATANH
|
CEIL
|
CEIL
|
CEILING
|
CEILING
|
COS
|
COS
|
COSH
|
COSH
|
EXP
|
EXP
|
FLOOR
|
FLOOR
|
GREATEST
|
GREATEST
|
LEAST
|
LEAST
|
LN
|
LN
|
LNNVL
|
use with ISNULL
|
LOG
|
LOG
|
MOD (% operator)
|
MOD
|
POWER (** operator)
|
POWER, POW
|
DBMS_RANDOM.VALUE
|
RAND
|
RANDOMBYTES
|
Isn't supported in BigQuery. Consider using a custom UDF and RAND function |
RANDOMINTEGER
|
CAST(FLOOR(10*RAND()) AS INT64)
|
RANDOMNUMBER
|
Isn't supported in BigQuery. Consider using a custom UDF and RAND function |
REMAINDER
|
MOD
|
ROUND
|
ROUND
|
ROUND_TIES_TO_EVEN
|
ROUND()
|
SIGN
|
SIGN
|
SIN
|
SIN
|
SINH
|
SINH
|
SQRT
|
SQRT
|
STANDARD_HASH
|
FARM_FINGERPRINT, MD5, SHA1, SHA256, SHA512
|
STDDEV
|
STDDEV |
TAN
|
TAN
|
TANH
|
TANH
|
TRUNC
|
TRUNC
|
NVL
|
IFNULL(expr, 0), COALESCE(exp, 0)
|
BigQuery offers the following additional math functions:
Type conversion functions
The following table shows mappings between Oracle type conversion functions and their BigQuery equivalents.
Oracle | BigQuery | |
---|---|---|
BIN_TO_NUM
|
SAFE_CONVERT_BYTES_TO_STRING(value)
|
|
BINARY2VARCHAR
|
SAFE_CONVERT_BYTES_TO_STRING(value)
|
|
CAST
|
CAST(expr AS typename)
|
|
CHARTOROWID
|
Oracle specific not needed. | |
CONVERT
|
BigQuery doesn't support character sets. Consider using custom user-defined function. | |
EMPTY_BLOB
|
BLOB is not used in BigQuery.
|
|
EMPTY_CLOB
|
CLOB is not used in BigQuery.
|
|
FROM_TZ
|
Types with time zones are not supported in BigQuery. Consider using a user-defined function and FORMAT_TIMESTAMP | |
INT_TO_BOOL
|
CAST
|
|
IS_BIT_SET
|
Does not exist implicitly in BigQuery. Consider using UDFs | |
NCHR
|
UDF can be used to get char equivalent of binary | |
NUMTODSINTERVAL
|
INTERVAL data type is not supported in BigQuery
|
|
NUMTOHEX
|
Isn't supported in BigQuery. Consider using a custom UDF and TO_HEX function
|
|
NUMTOHEX2
|
||
NUMTOYMINTERVAL
|
INTERVAL data type is not supported in BigQuery.
|
|
RAW_TO_CHAR
|
Oracle specific, does not exist in BigQuery. | |
RAW_TO_NCHAR
|
Oracle specific, does not exist in BigQuery. | |
RAW_TO_VARCHAR2
|
Oracle specific, does not exist in BigQuery. | |
RAWTOHEX
|
Oracle specific, does not exist in BigQuery. | |
RAWTONHEX
|
Oracle specific, does not exist in BigQuery. | |
RAWTONUM
|
Oracle specific, does not exist in BigQuery. | |
RAWTONUM2
|
Oracle specific, does not exist in BigQuery. | |
RAWTOREF
|
Oracle specific, does not exist in BigQuery. | |
REFTOHEX
|
Oracle specific, does not exist in BigQuery. | |
REFTORAW
|
Oracle specific, does not exist in BigQuery. | |
ROWIDTOCHAR
|
ROWID is Oracle specific type and does not exist in BigQuery. This value should
be represented as string.
|
|
ROWIDTONCHAR
|
ROWID is Oracle specific type and does not exist in BigQuery. This value should
be represented as string.
|
|
SCN_TO_TIMESTAMP
|
SCN is Oracle specific type and does not exist in BigQuery. This value should be represented as timestamp.
|
|
TO_ACLID TO_TIMESTAMP TO_TIMESTAMP_TZ TO_TIME_TZ TO_UTC_TIMEZONE_TZ TO_YMINTERVAL |
CAST(expr AS typename) PARSE_DATE PARSE_TIMESTAMP Cast syntax is used in a query to indicate that the result type of an expression should be converted to some other type. |
|
TREAT
|
Oracle specific, does not exist in BigQuery. | |
VALIDATE_CONVERSION
|
Isn't supported in BigQuery. Consider using a custom UDF | |
VSIZE
|
Isn't supported in BigQuery. Consider using a custom UDF |
JSON functions
The following table shows mappings between Oracle JSON functions and their BigQuery equivalents.
Oracle | BigQuery |
---|---|
AS_JSON
|
TO_JSON_STRING(value[, pretty_print])
|
JSON_ARRAY
|
Consider using UDFs and TO_JSON_STRING function
|
JSON_ARRAYAGG
|
Consider using UDFs and TO_JSON_STRING function
|
JSON_DATAGUIDE
|
Custom user-defined function. |
JSON_EQUAL
|
Custom user-defined function. |
JSON_EXIST
|
Consider using UDFs and JSON_EXTRACT or JSON_EXTRACT_SCALAR
|
JSON_MERGEPATCH
|
Custom user-defined function. |
JSON_OBJECT
|
Is not supported by BigQuery. |
JSON_OBJECTAGG
|
Is not supported by BigQuery. |
JSON_QUERY
|
Consider using UDFs and JSON_EXTRACT or JSON_EXTRACT_SCALAR .
|
JSON_TABLE
|
Custom user-defined function. |
JSON_TEXTCONTAINS
|
Consider using UDFs and JSON_EXTRACT or JSON_EXTRACT_SCALAR .
|
JSON_VALUE
|
JSON_EXTRACT_SCALAR
|
XML functions
BigQuery does not provide implicit XML functions. XML can be loaded to BigQuery as string and UDFs can be used to parse XML. Alternatively, XML processing be done by an ETL/ELT tool such as Dataflow. The following list shows Oracle XML functions:
Oracle | BigQuery |
---|---|
DELETEXML
|
BigQuery UDFs or ETL tool like Dataflow can be used to process XML. |
ENCODE_SQL_XML | |
EXISTSNODE | |
EXTRACTCLOBXML | |
EXTRACTVALUE | |
INSERTCHILDXML | |
INSERTCHILDXMLAFTER | |
INSERTCHILDXMLBEFORE | |
INSERTXMLAFTER | |
INSERTXMLBEFORE | |
SYS_XMLAGG | |
SYS_XMLANALYZE | |
SYS_XMLCONTAINS | |
SYS_XMLCONV | |
SYS_XMLEXNSURI | |
SYS_XMLGEN | |
SYS_XMLI_LOC_ISNODE | |
SYS_XMLI_LOC_ISTEXT | |
SYS_XMLINSTR | |
SYS_XMLLOCATOR_GETSVAL | |
SYS_XMLNODEID | |
SYS_XMLNODEID_GETLOCATOR | |
SYS_XMLNODEID_GETOKEY | |
SYS_XMLNODEID_GETPATHID | |
SYS_XMLNODEID_GETPTRID | |
SYS_XMLNODEID_GETRID | |
SYS_XMLNODEID_GETSVAL | |
SYS_XMLT_2_SC | |
SYS_XMLTRANSLATE | |
SYS_XMLTYPE2SQL | |
UPDATEXML | |
XML2OBJECT | |
XMLCAST | |
XMLCDATA | |
XMLCOLLATVAL | |
XMLCOMMENT | |
XMLCONCAT | |
XMLDIFF | |
XMLELEMENT | |
XMLEXISTS | |
XMLEXISTS2 | |
XMLFOREST | |
XMLISNODE | |
XMLISVALID | |
XMLPARSE | |
XMLPATCH | |
XMLPI | |
XMLQUERY | |
XMLQUERYVAL | |
XMLSERIALIZE | |
XMLTABLE | |
XMLTOJSON | |
XMLTRANSFORM | |
XMLTRANSFORMBLOB | |
XMLTYPE |
Machine learning functions
Machine learning (ML) functions in Oracle and BigQuery are
different.
Oracle requires advanced analytics pack and licenses to do ML on the database.
Oracle uses the DBMS_DATA_MINING
package for ML. Converting Oracle data miner
jobs requires rewriting the code, you can choose from comprehensive Google AI
product offerings such as BigQuery ML,
AI APIs (including Speech-to-Text, Text-to-Speech
, Dialogflow, Cloud Translation, NLP,
Cloud Vision, and Timeseries Insights API,
AutoML,
AutoML Tables or AI Platform. Google
user-managed notebooks can be used as a development
environment for data scientists and Google AI Platform Training
can be used to run training and scoring workloads at scale. The following table
shows Oracle ML functions:
Oracle | BigQuery |
---|---|
CLASSIFIER
|
See BigQuery ML for machine learning classifier and regression options |
CLUSTER_DETAILS
|
|
CLUSTER_DISTANCE
|
|
CLUSTER_ID
|
|
CLUSTER_PROBABILITY
|
|
CLUSTER_SET
|
|
PREDICTION
|
|
PREDICTION_BOUNDS
|
|
PREDICTION_COST
|
|
PREDICTION_DETAILS
|
|
PREDICTION_PROBABILITY
|
|
PREDICTION_SET
|
Security functions
The following table shows the functions for identifying the user in Oracle and BigQuery:
Oracle | BigQuery |
---|---|
UID
|
SESSION_USER
|
USER/SESSION_USER/CURRENT_USER
|
SESSION_USER()
|
Set or array functions
The following table shows set or array functions in Oracle and their equivalents in BigQuery:
Oracle | BigQuery |
---|---|
MULTISET
|
ARRAY_AGG
|
MULTISET EXCEPT
|
ARRAY_AGG([DISTINCT] expression)
|
MULTISET INTERSECT
|
ARRAY_AGG([DISTINCT])
|
MULTISET UNION
|
ARRAY_AGG
|
Window functions
The following table shows window functions in Oracle and their equivalents in BigQuery.
Oracle | BigQuery |
---|---|
LAG
|
LAG (value_expression[, offset [, default_expression]])
|
LEAD
|
LEAD (value_expression[, offset [, default_expression]])
|
Hierarchical or recursive queries
Hierarchical or recursive queries are not used in BigQuery. If the depth of the hierarchy is known similar functionality can be achieved with joins, as illustrated in the following example. Another solution would be to utilize the BigQueryStorage API and Spark.
select
array(
select e.update.element
union all
select c1 from e.update.element.child as c1
union all
select c2 from e.update.element.child as c1, c1.child as c2
union all
select c3 from e.update.element.child as c1, c1.child as c2, c2.child as c3
union all
select c4 from e.update.element.child as c1, c1.child as c2, c2.child as c3, c3.child as c4
union all
select c5 from e.update.element.child as c1, c1.child as c2, c2.child as c3, c3.child as c4, c4.child as c5
) as flattened,
e as event
from t, t.events as e
The following table shows hierarchical functions in Oracle.
Oracle | BigQuery |
---|---|
DEPTH
|
Hierarchical queries are not used in BigQuery. |
PATH
|
|
SYS_CONNECT_BY_PATH (hierarchical)
|
UTL functions
UTL_File
package is mainly used for reading and writing the operating system files from
PL/SQL. Cloud Storage can be used for any kind of raw file staging.
External tables and BigQuery
load and export
should be used to read and write files from and to Cloud Storage. For
more information, see
Introduction to external data sources.
Spatial functions
You can use BigQuery geospatial analytics to replace spatial
functionality. There
are SDO_*
functions and types in Oracle such as SDO_GEOM_KEY
,
SDO_GEOM_MBR
, SDO_GEOM_MMB
. These functions are used for spatial
analysis. You can use geospatial analytics
to do spatial analysis.
DML syntax
This section addresses differences in data management language syntax between Oracle and BigQuery.
INSERT
statement
Most Oracle INSERT
statements are compatible with BigQuery. The
following table shows exceptions.
DML scripts in BigQuery have slightly different consistency
semantics than the equivalent statements in Oracle. For an overview of snapshot
isolation and session and transaction handling, see the CREATE [UNIQUE] INDEX
section
elsewhere in this document.
Oracle | BigQuery |
---|---|
INSERT INTO table VALUES (...);
|
INSERT INTO table (...) VALUES (...);
Oracle offers a
Note: In BigQuery, omitting column names in the |
INSERT INTO table VALUES (1,2,3);
|
INSERT INTO table VALUES (1,2,3),
(4,5,6),
BigQuery imposes DML quotas, which restrict the number of DML statements you can execute daily. To make the best use of your quota, consider the following approaches:
|
UPDATE
statement
Oracle UPDATE
statements are mostly compatible with BigQuery,
however, in BigQuery the UPDATE
statement must have a WHERE
clause.
As a best practice, you should prefer batch DML statements over multiple single
UPDATE
and INSERT
statements. DML scripts in BigQuery have
slightly different consistency semantics than equivalent statements in Oracle.
For an overview on snapshot isolation and session and transaction handling see
the CREATE INDEX
section in this document.
The following table shows Oracle UPD