Oracle SQL 変換ガイド

このドキュメントでは、移行の計画に役立つ Oracle と BigQuery の間の SQL 構文の類似点と相違点について詳しく説明します。バッチ SQL 変換を使用して SQL スクリプトを一括で移行することも、インタラクティブ SQL 変換を使用してアドホック クエリを変換することもできます。

データ型

以下では、Oracle と BigQuery の間のデータ型の対応について説明します。

Oracle BigQuery
VARCHAR2 STRING
NVARCHAR2 STRING
CHAR STRING
NCHAR STRING
CLOB STRING
NCLOB STRING
INTEGER INT64
SHORTINTEGER INT64
LONGINTEGER INT64
NUMBER NUMERIC BigQuery では、精度またはスケールにユーザー指定のカスタム値を使用できません。結果として、Oracle の列に、BigQuery でサポートされるより大きいスケールが指定される可能性があります。

さらに、Oracle では、10 進数を格納する前に、その値の小数点以下の桁数が対応する列に指定されている桁数よりも大きい場合、切り上げ処理を行います。BigQuery では、この機能を ROUND() 関数で実装できます。

NUMBER(*, x) NUMERIC BigQuery では、精度またはスケールにユーザー指定のカスタム値を使用できません。結果として、Oracle の列に、BigQuery でサポートされるより大きいスケールが指定される可能性があります。

さらに、Oracle では、10 進数を格納する前に、その値の小数点以下の桁数が対応する列に指定されている桁数よりも大きい場合、切り上げ処理を行います。BigQuery では、この機能を ROUND() 関数で実装できます。

NUMBER(x, -y) INT64 Oracle では、10 進数を保存しようとすると整数への切り上げが行われます。BigQuery では、INT64 として定義された列に 10 進数を格納しようとすると、エラーが発生します。この場合、ROUND() 関数を適用する必要があります。

BigQuery の INT64 データ型に指定できる最大精度は 18 桁です。数値フィールドが 18 桁を超える場合、BigQuery では FLOAT64 データ型を使用する必要があります。

NUMBER(x) INT64 Oracle では、10 進数を保存しようとすると整数への切り上げが行われます。BigQuery では、INT64 として定義された列に 10 進数を格納しようとすると、エラーが発生します。この場合、ROUND() 関数を適用する必要があります。

BigQuery の INT64 データ型に指定できる最大精度は 18 桁です。数値フィールドが 18 桁を超える場合、BigQuery では FLOAT64 データ型を使用する必要があります。

FLOAT FLOAT64/NUMERIC FLOAT は完全一致のデータ型で、Oracle では NUMBER のサブタイプです。BigQuery では、FLOAT64 は近似のデータ型です。NUMERIC は BigQuery の FLOAT 型に適している可能性があります。
BINARY_DOUBLE FLOAT64/NUMERIC FLOAT は完全一致のデータ型で、Oracle では NUMBER のサブタイプです。BigQuery では、FLOAT64 は近似のデータ型です。NUMERIC は BigQuery の FLOAT 型に適している可能性があります。
BINARY_FLOAT FLOAT64/NUMERIC FLOAT は完全一致のデータ型で、Oracle では NUMBER のサブタイプです。BigQuery では、FLOAT64 は近似のデータ型です。NUMERIC は BigQuery の FLOAT 型に適している可能性があります。
LONG BYTES LONG データ型は Oracle Database の以前のバージョンで使用されていましたが、新しいバージョンでは推奨されていません。

BigQuery で LONG データを保持する必要がある場合は、BigQuery の BYTES データ型を使用できます。バイナリ オブジェクトを Cloud Storage に配置し、BigQuery で参照を保持することをおすすめします。

BLOB BYTES BYTES データ型を使用して、可変長のバイナリデータを格納できます。このフィールドに対するクエリを行わず、分析でも使用しない場合は、Cloud Storage にバイナリデータを保存することをおすすめします。
BFILE STRING バイナリ ファイルは Cloud Storage に格納できます。また、STRING データ型は BigQuery テーブルでのファイル参照に使用できます。
DATE DATETIME
TIMESTAMP TIMESTAMP Oracle では 0~9 の精度がサポートされているのに対し、BigQuery ではマイクロ秒の精度(10-6)がサポートされています。

BigQuery では、TZ データベースからのタイムゾーン リージョン名と UTC からのタイムゾーン オフセットがサポートされています。

BigQuery では、Oracle の TIMESTAMP WITH LOCAL TIME ZONE 機能に合わせてタイムゾーン変換を手動で行う必要があります。

TIMESTAMP(x) TIMESTAMP Oracle では 0~9 の精度がサポートされているのに対し、BigQuery ではマイクロ秒の精度(10-6)がサポートされています。

BigQuery では、TZ データベースからのタイムゾーン リージョン名と UTC からのタイムゾーン オフセットがサポートされています。

BigQuery では、Oracle の TIMESTAMP WITH LOCAL TIME ZONE 機能に合わせてタイムゾーン変換を手動で行う必要があります。

TIMESTAMP WITH TIME ZONE TIMESTAMP Oracle では 0~9 の精度がサポートされているのに対し、BigQuery ではマイクロ秒の精度(10-6)がサポートされています。

BigQuery では、TZ データベースからのタイムゾーン リージョン名と UTC からのタイムゾーン オフセットがサポートされています。

BigQuery では、Oracle の TIMESTAMP WITH LOCAL TIME ZONE 機能に合わせてタイムゾーン変換を手動で行う必要があります。

TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP Oracle では 0~9 の精度がサポートされているのに対し、BigQuery ではマイクロ秒の精度(10-6)がサポートされています。

BigQuery では、TZ データベースからのタイムゾーン リージョン名と UTC からのタイムゾーン オフセットがサポートされています。

BigQuery では、Oracle の TIMESTAMP WITH LOCAL TIME ZONE 機能に合わせてタイムゾーン変換を手動で行う必要があります。

INTERVAL YEAR TO MONTH STRING 間隔値は、BigQuery では STRING データ型として格納できます。
INTERVAL DAY TO SECOND STRING 間隔値は、BigQuery では STRING データ型として格納できます。
RAW BYTES BYTES データ型を使用して、可変長のバイナリデータを格納できます。このフィールドに対するクエリを行わず、分析でも使用しない場合は、Cloud Storage にバイナリデータを格納することをおすすめします。
LONG RAW BYTES BYTES データ型を使用して、可変長のバイナリデータを格納できます。このフィールドに対するクエリを行わず、分析でも使用しない場合は、Cloud Storage にバイナリデータを格納することをおすすめします。
ROWID STRING これらのデータ型は、テーブル内の行に一意のアドレスを指定するために Oracle で内部的に使用されます。一般に、ROWID または UROWID フィールドはアプリケーションで使用するべきではありません。ただし、使用する場合は、STRING データ型を使用してこのデータを保持できます。

タイプ形式

Oracle SQL では、式と列データの表示とデータ型の変換時に、パラメータとして設定されたデフォルト形式のセットを使用します。たとえば、YYYY/MM/DD として設定された NLS_DATE_FORMAT は、デフォルトで日付を YYYY/MM/DD としてフォーマットします。詳しくは、Oracle のオンライン ドキュメントの NLS 設定に関する説明をご覧ください。BigQuery には初期化パラメータはありません。

デフォルトでは、BigQuery は読み込み時にすべてのソースデータが UTF-8 でエンコードされていると想定します。CSV ファイルに ISO-8859-1 形式でエンコードされたデータが含まれる場合は、BigQuery がインポート処理中にデータを UTF-8 に正しく変換できるよう、データをインポートするときに明示的にエンコーディングを指定できます。

現時点では、ISO-8859-1 または UTF-8 でエンコードされたデータのみをインポートできます。BigQuery では、UTF-8 でエンコードされているデータを格納し、返します。 必要な日付形式またはタイムゾーンは、DATE 関数と TIMESTAMP 関数で設定できます。

タイムスタンプと日付型形式

タイムスタンプと日付形式要素を Oracle から BigQuery に変換する場合は、次の表に示すように、TIMESTAMPDATETIME のタイムゾーンの違いに注意する必要があります。

Oracle の形式ではかっこを使用しないことに注意してください。この形式(CURRENT_*)はキーワードであり、関数ではありません。

Oracle BigQuery メモ
CURRENT_TIMESTAMP Oracle の TIMESTAMP 情報は、異なるタイムゾーン情報を含むことができます。これらの情報は、列定義で WITH TIME ZONE を使用するか、 TIME_ZONE 変数を設定します。 可能であれば、ISO 形式の CURRENT_TIMESTAMP() 関数を使用してください。ただし、出力形式は常に UTC タイムゾーンです(内部的には、BigQuery にはタイムゾーンがありません)。

ISO 形式の次の違いに注意してください。

DATETIME は出力チャネルの規則に基づいて形式が設定されます。BigQuery のコマンドライン ツールと BigQuery コンソールでは、DATETIME は RFC 3339 に従い T 区切りを使って設定されます。ただし Python と Java JDBC では区切り文字としてスペースが使用されます。

明示的な形式を使用する場合は、FORMAT_DATETIME() 関数を使用します。これにより、文字列が明示的にキャストされます。たとえば、次の式は常にスペース区切りを返します。 CAST(CURRENT_DATETIME() AS STRING)

CURRENT_DATE
SYSDATE
Oracle では日付に次の 2 つのタイプを使用します。
  • タイプ 12
  • タイプ 13
Oracle では、日付の保存時にタイプ 12 が使用されます。内部的には、これらは固定長の数値です。SYSDATE or CURRENT_DATE によって返される場合、Oracle はタイプ 13 を使用します。
BigQuery には、日付を常に ISO 8601 形式で返す別の DATE 形式があります。

DATE_FROM_UNIX_DATE は 1970 年をベースにしているため使用できません。

CURRENT_DATE-3 日付値は整数で表現されます。Oracle では日付型の算術演算子がサポートされています。 日付型の場合は、DATE_ADD() または DATE_SUB() を使用します。BigQuery では、データ型に INT64NUMERICFLOAT64 の算術演算子を使用します。
NLS_DATE_FORMAT セッションまたはシステムの日付形式を設定します。 BigQuery では常に ISO 8601 が使用されるため、Oracle の日付と時刻を必ず変換してください。

クエリ構文

ここでは、Oracle と BigQuery のクエリ構文の違いについて説明します。

SELECT ステートメント

Oracle の SELECT ステートメントの大部分は、BigQuery と互換性があります。

関数、演算子、式

以下のセクションでは、Oracle の関数とそれに対応する BigQuery の機能のマッピングを示します。

比較演算子

Oracle も BigQuery も比較演算子は ANSI SQL:2011 準拠です。次の表の比較演算子は BigQuery と Oracle とで同一です。BigQuery では、REGEXP_LIKE の代わりに REGEXP_CONTAINS を使用できます。

オペレーター 説明
"=" 等しい
<> 等しくない
!= 等しくない
> 次より大きい
>= 以上
< 次より小さい
<= 以下
IN ( ) リスト内の値と一致
NOT 条件を否定
BETWEEN 範囲内(両端を含む)
IS NULL NULL
IS NOT NULL NULL 値でない
LIKE % を含むパターン マッチング
EXISTS サブクエリで 1 行以上返される場合、条件に合致

表の比較演算子は BigQuery と Oracle とで同一です。

論理式と論理関数

Oracle BigQuery
CASE CASE
COALESCE COALESCE(expr1, ..., exprN)
DECODE CASE.. WHEN.. END
NANVL IFNULL
FETCH NEXT> LIMIT
NULLIF NULLIF(expression, expression_to_match)
NVL IFNULL(expr, 0), COALESCE(exp, 0)
NVL2 IF(expr, true_result, else_result)

集計関数

次の表に、Oracle の一般的な集計関数、統計集計関数、近似集計関数とそれに対応する BigQuery の機能のマッピングを示します。

Oracle BigQuery
ANY_VALUE
(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)[
OFFSET(CAST(TRUNC(percentile * 100) as INT64))]

Oracle で定義されている引数の残りは、BigQuery ではサポートされていません。
<codeAPPROX_PERCENTILE_AGG APPROX_QUANTILES(expression, 100)[
OFFSET(CAST(TRUNC(percentile * 100) as INT64))]
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 ビット NOT 演算子: ~
BIT_OR BIT_OR, X | Y
BIT_XOR BIT_XOR, X ^ Y
BITAND BIT_AND, X & Y
CARDINALITY COUNT
COLLECT BigQuery は TYPE AS TABLE OF をサポートしません。BigQuery で STRING_AGG() または ARRAY_AGG() の使用を検討する
CORR/CORR_K/ CORR_S CORR
COUNT COUNT
COVAR_POP COVAR_POP
COVAR_SAMP COVAR_SAMP
FIRST 暗黙的に BigQuery には存在しません。ユーザー定義関数(UDF)の使用を検討してください。
GROUP_ID BigQuery では使用されません
GROUPING BigQuery では使用されません
GROUPING_ID BigQuery では使用されません。
LAST 暗黙的に BigQuery には存在しません。UDF の使用を検討してください。
LISTAGG STRING_AGG, ARRAY_CONCAT_AGG(expression [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
MAX MAX
MIN MIN
OLAP_CONDITION Oracle 固有で、BigQuery に存在しない。
OLAP_EXPRESSION Oracle 固有で、BigQuery に存在しない。
OLAP_EXPRESSION_BOOL Oracle 固有で、BigQuery に存在しない。
OLAP_EXPRESSION_DATE Oracle 固有で、BigQuery に存在しない。
OLAP_EXPRESSION_TEXT Oracle 固有で、BigQuery に存在しない。
OLAP_TABLE Oracle 固有で、BigQuery に存在しない。
POWERMULTISET Oracle 固有で、BigQuery に存在しない。
POWERMULTISET_BY_CARDINALITY Oracle 固有で、BigQuery に存在しない。
QUALIFY Oracle 固有で、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)
- AVG(ind_var_expr)
* (COVAR_SAMP(ind_var_expr,dep_var_expr)
/ VARIANCE(ind_var_expr)
)
REGR_R2 (COUNT(dep_var_expr) *
SUM(ind_var_expr * dep_var_expr) -
SUM(dep_var_expr) * SUM(ind_var_expr))
/ SQRT(
(COUNT(ind_var_expr) *
SUM(POWER(ind_var_expr, 2)) *
POWER(SUM(ind_var_expr),2)) *
(COUNT(dep_var_expr) *
SUM(POWER(dep_var_expr, 2)) *
POWER(SUM(dep_var_expr), 2)))
REGR_SLOPE COVAR_SAMP(ind_var_expr,

dep_var_expr)

/ VARIANCE(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 では、次の追加の集計関数も使用できます。

分析関数

次の表に、Oracle の一般的な分析関数、集計分析関数とそれに対応する BigQuery の機能のマッピングを示します。

Oracle BigQuery
AVG AVG
BIT_COMPLEMENT ビット NOT 演算子: ~
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 BigQuery ではサポートされていません。BI ツールまたはカスタムの UDF の使用を検討してください。
CUME_DIST CUME_DIST
DENSE_RANK(ANSI) DENSE_RANK
FEATURE_COMPARE 暗黙的に BigQuery には存在しません。UDF と BigQuery ML の使用を検討してください。
FEATURE_DETAILS 暗黙的に BigQuery には存在しません。UDF と BigQuery ML の使用を検討してください。
FEATURE_ID 暗黙的に BigQuery には存在しません。UDF と BigQuery ML の使用を検討してください。
FEATURE_SET 暗黙的に BigQuery には存在しません。UDF と BigQuery ML の使用を検討してください。
FEATURE_VALUE 暗黙的に BigQuery には存在しません。UDF と BigQuery ML の使用を検討してください。
FIRST_VALUE FIRST_VALUE
HIER_CAPTION 階層クエリは、BigQuery ではサポートされていません。
HIER_CHILD_COUNT 階層クエリは、BigQuery ではサポートされていません。
HIER_COLUMN 階層クエリは、BigQuery ではサポートされていません。
HIER_DEPTH 階層クエリは、BigQuery ではサポートされていません。
HIER_DESCRIPTION 階層クエリは、BigQuery ではサポートされていません。
HIER_HAS_CHILDREN 階層クエリは、BigQuery ではサポートされていません。
HIER_LEVEL 階層クエリは、BigQuery ではサポートされていません。
HIER_MEMBER_NAME 階層クエリは、BigQuery ではサポートされていません。
HIER_ORDER 階層クエリは、BigQuery ではサポートされていません。
HIER_UNIQUE_MEMBER_NAME 階層クエリは、BigQuery ではサポートされていません。
LAST_VALUE LAST_VALUE
LAG LAG
LEAD LEAD
LISTAGG ARRAY_AGG
STRING_AGG
ARRAY_CONCAT_AGG
MATCH_NUMBER BigQuery では、パターン認識と計算は正規表現と UDF を使用して実行できます。
MATCH_RECOGNIZE BigQuery では、パターン認識と計算は正規表現と UDF を使用して実行できます。
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_RANKM
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
PERCENTILE_CONT
PERCENTILE_CONT
PERCENTILE_DISC
PERCENTILE_DISC
PRESENTNNV Oracle 固有で、BigQuery に存在しない。
PRESENTV Oracle 固有で、BigQuery に存在しない。
PREVIOUS Oracle 固有で、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 を使用できます。

日付 / 時間関数

次の表に、Oracle の一般的な日時関数とそれに対応する BigQuery の機能のマッピングを示します。

Oracle BigQuery
ADD_MONTHS(date, integer) DATE_ADD(date, INTERVAL integer MONTH),
日付が TIMESTAMP の場合は、次を使用できます

EXTRACT(DATE FROM TIMESTAMP_ADD(date, INTERVAL integer MONTH))

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 では、データベースのタイムゾーンはサポートされていません。
EXTRACT EXTRACT(DATE), EXTRACT(TIMESTAMP)
LAST_DAY DATE_SUB(
  DATE_TRUNC(
    DATE_ADD(
      date_expression,
      INTERVAL 1 MONTH
    ),
  MONTH
  ),
INTERVAL 1 DAY
)
LOCALTIMESTAMP BigQuery では、タイムゾーン設定はサポートされていません。
MONTHS_BETWEEN DATE_DIFF(date_expression, date_expression, MONTH)
NEW_TIME DATE(timestamp_expression, time zone)
TIME(timestamp, time zone)
DATETIME(timestamp_expression, time zone)
NEXT_DAY DATE_ADD(
  DATE_TRUNC(
    date_expression,
    WEEK(day_value)
  ),
  INTERVAL 1 WEEK
)
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 BigQuery ではサポートされていません。カスタムの UDF の使用を検討してください。
WM_CONTAINS
WM_EQUALS
WM_GREATERTHAN
WM_INTERSECTION
WM_LDIFF
WM_LESSTHAN
WM_MEETS
WM_OVERLAPS
WM_RDIFF
期間は BigQuery では使用されません。UDF を使用して、2 つの期間を比較できます。

BigQuery では、次の追加の日時関数も使用できます。

文字列関数

次の表に、Oracle の文字列関数とそれに対応する BigQuery の機能のマッピングを示します。

Oracle BigQuery
ASCII TO_CODE_POINTS(string_expr)[OFFSET(0)]
ASCIISTR BigQuery では、UTF-16 はサポートされていません。
RAWTOHEX TO_HEX
LENGTH CHAR_LENGTH
LENGTH CHARACTER_LENGTH
CHR CODE_POINTS_TO_STRING(
[mod(numeric_expr, 256)]
)
COLLATION BigQuery にはありません。BigQuery では、DML の COLLATE はサポートされていません。
COMPOSE カスタムのユーザー定義関数。
CONCAT, (|| operator) CONCAT
DECOMPOSE カスタムのユーザー定義関数。
ESCAPE_REFERENCE (UTL_I18N) BigQuery ではサポートされていません。ユーザー定義関数の使用を検討してください。
INITCAP INITCAP
INSTR/INSTR2/INSTR4/INSTRB/INSTRC カスタムのユーザー定義関数。
LENGTH/LENGTH2/LENGTH4/LENGTHB/LENGTHC LENGTH
LOWER LOWER
LPAD LPAD
LTRIM LTRIM
NLS_INITCAP カスタムのユーザー定義関数。
NLS_LOWER LOWER
NLS_UPPER UPPER
NLSSORT Oracle 固有で、BigQuery に存在しない。
POSITION STRPOS(string, substring)
PRINTBLOBTOCLOB Oracle 固有で、BigQuery に存在しない。
REGEXP_COUNT ARRAY_LENGTH(REGEXP_EXTRACT_ALL(value, regex))
REGEXP_INSTR STRPOS(source_string, REGEXP_EXTRACT(source_string, regexp_string))

注: 最初の一致が返されます。

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 BigQuery ではサポートされていません。カスタムの UDF の使用を検討してください。
STRTOK SPLIT(instring, delimiter)[ORDINAL(tokennum)]

Note: The entire delimiter string argument is used as a single delimiter. The default delimiter is a comma.

SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4 SUBSTR
TRANSLATE REPLACE
TRANSLATE USING REPLACE
TRIM TRIM
UNISTR CODE_POINTS_TO_STRING
UPPER UPPER
|| (VERTICAL BARS) CONCAT

BigQuery では、次の追加の文字列関数も使用できます。

数学関数

次の表に、Oracle の数学関数とそれに対応する BigQuery の機能のマッピングを示します。

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 ISNULL と一緒に使用します。
LOG LOG
MOD (% operator) MOD
POWER (** operator) POWER, POW
DBMS_RANDOM.VALUE RAND
RANDOMBYTES BigQuery ではサポートされていません。カスタムの UDF と RAND 関数の使用を検討してください。
RANDOMINTEGER CAST(FLOOR(10*RAND()) AS INT64)
RANDOMNUMBER BigQuery ではサポートされていません。カスタムの UDF と RAND 関数の使用を検討してください。
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 では、次の追加の数学関数も使用できます。

タイプ変換関数

次の表に、Oracle のタイプ変換関数とそれに対応する BigQuery の機能のマッピングを示します。

Oracle BigQuery
BIN_TO_NUM SAFE_CONVERT_BYTES_TO_STRING(value)

CAST(x AS INT64)

BINARY2VARCHAR SAFE_CONVERT_BYTES_TO_STRING(value)
CAST
CAST_FROM_BINARY_DOUBLE
CAST_FROM_BINARY_FLOAT
CAST_FROM_BINARY_INTEGER
CAST_FROM_NUMBER
CAST_TO_BINARY_DOUBLE
CAST_TO_BINARY_FLOAT
CAST_TO_BINARY_INTEGER
CAST_TO_NUMBER
CAST_TO_NVARCHAR2
CAST_TO_RAW
>CAST_TO_VARCHAR
CAST(expr AS typename)
CHARTOROWID Oracle 固有です。BigQuery では不要です。
CONVERT BigQuery では、文字セットはサポートされていません。カスタムのユーザー定義関数の使用を検討してください。
EMPTY_BLOB BLOB は BigQuery では使用されません。
EMPTY_CLOB CLOB は BigQuery では使用されません。
FROM_TZ タイムゾーンを使用するタイプは、BigQuery ではサポートされていません。ユーザー定義関数と FORMAT_TIMESTAMP の使用を検討してください。
INT_TO_BOOL CAST
IS_BIT_SET 暗黙的に BigQuery には存在しません。UDF の使用を検討してください。
NCHR UDF を使用して、バイナリに相当する文字を取得できます。
NUMTODSINTERVAL INTERVAL データ型は、BigQuery ではサポートされていません。
NUMTOHEX BigQuery ではサポートされていません。カスタムの UDF と TO_HEX 関数の使用を検討してください。
NUMTOHEX2
NUMTOYMINTERVAL INTERVAL データ型は、BigQuery ではサポートされていません。
RAW_TO_CHAR Oracle 固有で、BigQuery に存在しない。
RAW_TO_NCHAR Oracle 固有で、BigQuery に存在しない。
RAW_TO_VARCHAR2 Oracle 固有で、BigQuery に存在しない。
RAWTOHEX Oracle 固有で、BigQuery に存在しない。
RAWTONHEX Oracle 固有で、BigQuery に存在しない。
RAWTONUM Oracle 固有で、BigQuery に存在しない。
RAWTONUM2 Oracle 固有で、BigQuery に存在しない。
RAWTOREF Oracle 固有で、BigQuery に存在しない。
REFTOHEX Oracle 固有で、BigQuery に存在しない。
REFTORAW Oracle 固有で、BigQuery に存在しない。
ROWIDTOCHAR ROWID は Oracle 固有の型です。BigQuery に存在しません。この値は文字列として表す必要があります。
ROWIDTONCHAR ROWID は Oracle 固有の型です。BigQuery に存在しません。この値は文字列として表す必要があります。
SCN_TO_TIMESTAMP SCN は Oracle 固有の型です。BigQuery に存在しません。この値はタイムスタンプとして表す必要があります。
TO_ACLID
TO_ANYLOB
TO_APPROX_COUNT_DISTINCT
TO_APPROX_PERCENTILE
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_BLOB
TO_CHAR
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_RAW
TO_SINGLE_BYTE
TO_TIME

TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_TIME_TZ
TO_UTC_TIMEZONE_TZ
TO_YMINTERVAL
CAST(expr AS typename)
PARSE_DATE
PARSE_TIMESTAMP
キャスト構文は、式の結果のタイプを他のタイプに変換する必要があることを示すために、クエリで使用されます。
TREAT Oracle 固有で、BigQuery に存在しない。
VALIDATE_CONVERSION BigQuery ではサポートされていません。カスタムの UDF の使用を検討してください。
VSIZE BigQuery ではサポートされていません。カスタムの UDF の使用を検討してください。

JSON 関数

次の表に、Oracle の JSON 関数とそれに対応する BigQuery の機能のマッピングを示します。

Oracle BigQuery
AS_JSON TO_JSON_STRING(value[, pretty_print])
JSON_ARRAY UDF と TO_JSON_STRING 関数の使用を検討する
JSON_ARRAYAGG UDF と TO_JSON_STRING 関数の使用を検討する
JSON_DATAGUIDE カスタムのユーザー定義関数。
JSON_EQUAL カスタムのユーザー定義関数。
JSON_EXIST UDF と、JSON_EXTRACT または JSON_EXTRACT_SCALAR の使用を検討する
JSON_MERGEPATCH カスタムのユーザー定義関数。
JSON_OBJECT BigQuery ではサポートされていません。
JSON_OBJECTAGG BigQuery ではサポートされていません。
JSON_QUERY UDF と、JSON_EXTRACT または JSON_EXTRACT_SCALAR の使用を検討してください。
JSON_TABLE カスタムのユーザー定義関数。
JSON_TEXTCONTAINS UDF と、JSON_EXTRACT または JSON_EXTRACT_SCALAR の使用を検討してください。
JSON_VALUE JSON_EXTRACT_SCALAR

XML 関数

BigQuery では暗黙的な XML 関数は提供されていません。XML を文字列として BigQuery に読み込んだ後、UDF を使用して解析できます。また、Dataflow などの ETL / ELT ツールを使用して XML を処理することもできます。次のリストに、Oracle の XML 関数を示します。

Oracle BigQuery
DELETEXML BigQuery UDF または DataFlow などの ETL ツールを使用して 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

ML 関数

Oracle と BigQuery では機械学習(ML)関数が異なります。Oracle でデータベースに対して ML を実行するには、Advanced Analytics パックとライセンスが必要です。Oracle では ML に DBMS_DATA_MINING パッケージを使用します。Oracle データ マイニング ジョブの変換にはコードの書き換えが必要です。BigQuery ML、AI API などの包括的な Google AI プロダクトから選択できます。たとえば、Speech-to-TextText-to-SpeechDialogflowCloud TranslationNLPCloud VisionTimeseries Insights APIAutoMLAutoML Tables または AI Platform などです。データ サイエンティスト向けの開発環境として、Google の ユーザー管理ノートブックを使用できます。また、Google の AI Platform トレーニングを使用して、トレーニングと大規模なワークロードのスコア付けを行うことができます。次の表に、Oracle の ML 関数を示します。

Oracle BigQuery
CLASSIFIER 機械学習の分類機能と回帰オプションについては、BigQuery ML をご覧ください。
CLUSTER_DETAILS
CLUSTER_DISTANCE
CLUSTER_ID
CLUSTER_PROBABILITY
CLUSTER_SET
PREDICTION
PREDICTION_BOUNDS
PREDICTION_COST
PREDICTION_DETAILS
PREDICTION_PROBABILITY
PREDICTION_SET

セキュリティ関数

次の表に、ユーザーを識別するための Oracle と BigQuery の関数を示します。

Oracle BigQuery
UID SESSION_USER
USER/SESSION_USER/CURRENT_USER SESSION_USER()

SET 関数または配列関数

次の表に、Oracle の設定または配列関数とそれに対応する BigQuery の機能を示します。

Oracle BigQuery
MULTISET ARRAY_AGG
MULTISET EXCEPT ARRAY_AGG([DISTINCT] expression)
MULTISET INTERSECT ARRAY_AGG([DISTINCT])
MULTISET UNION ARRAY_AGG

ウィンドウ関数

次の表に、Oracle のウィンドウ関数とそれに対応する BigQuery の機能を示します。

Oracle BigQuery
LAG LAG (value_expression[, offset [, default_expression]])
LEAD LEAD (value_expression[, offset [, default_expression]])

階層クエリまたは再帰クエリ

階層クエリ、または再帰クエリは BigQuery では使用されません。次の例に示すように、階層の深さがわかっている場合は、結合を使用して同様の機能を実現できます。他に、BigQueryStorage API 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

次の表に、Oracle の階層関数を示します。

Oracle BigQuery
DEPTH 階層クエリは BigQuery では使用されません。
PATH
SYS_CONNECT_BY_PATH (hierarchical)

UTL 関数

UTL_File パッケージは、主に PL/SQL との間でオペレーティング システム ファイルの読み取りと書き込みを行うために使用されます。Cloud Storage は、どのような種類の未加工ファイルのステージングにも使用できます。Cloud Storage との間でファイルを読み書きするには、外部テーブルと BigQuery の読み込みおよびエクスポートを使用する必要があります。詳細については、外部データソースの説明をご覧ください。

空間関数

空間関数の代わりに BigQuery の地理空間分析を使用できます。Oracle には SDO_GEOM_KEYSDO_GEOM_MBRSDO_GEOM_MMB などの SDO_* 関数とタイプがあります。これらの関数は、空間分析に使用されます。空間分析を行うには、地理空間分析を使用します。

DML 構文

ここでは、Oracle と BigQuery のデータ管理言語の構文の違いについて説明します。

INSERT ステートメント

Oracle の INSERT ステートメントの大部分は、BigQuery と互換性があります。次の表に例外を示します。

BigQuery の DML スクリプトは、それに対応する Oracle のステートメントとは整合性セマンティクスが少し異なります。スナップショット分離とセッションとトランザクションの処理の概要については、このドキュメントの CREATE [UNIQUE] INDEX section ご覧ください。

Oracle BigQuery
INSERT INTO table VALUES (...); INSERT INTO table (...) VALUES (...);

Oracle では、null 値が許可されない列に DEFAULT キーワードを使用できます。

注: BigQuery では、ターゲット テーブル内のすべての列の値が元の順序位置に基づいて昇順で挿入されている場合にのみ、INSERT ステートメントで列名を省略できます。

INSERT INTO table VALUES (1,2,3);
INSERT INTO table VALUES (4,5,6);
INSERT INTO table VALUES (7,8,9);
INSERT ALL
INTO table (col1, col2) VALUES ('val1_1', 'val1_2')
INTO table (col1, col2) VALUES ('val2_1', 'val2_2')
INTO table (col1, col2) VALUES ('val3_1', 'val3_2')
.
.
.
SELECT 1 FROM DUAL;
INSERT INTO table VALUES (1,2,3), (4,5,6),
(7,8,9);

BigQuery では、1 日に実行できる DML ステートメント数を制限する、DML 割り当てが課されます。割り当てを最大限に利用するには、次の方法を検討してください。

  • INSERT オペレーションごとに 1 行ではなく、1 つの INSERT ステートメントで複数の行を組み合わせてください。
  • MERGE ステートメントを使用して、複数の DML ステートメント(INSERT を含む)を組み合わせる。
  • CREATE TABLE ... AS SELECT を使用して、新しいテーブルを作成してデータを入力する。

UPDATE ステートメント

Oracle の UPDATE ステートメントの大部分は BigQuery と互換性がありますが、BigQuery では UPDATE ステートメントに WHERE 句を指定する必要があります。

ベスト プラクティスとして、複数の単一 UPDATE ステートメントと INSERT ステートメントではなく、バッチ DML ステートメントを使用することをおすすめします。BigQuery の DML スクリプトは、それに対応する Oracle のステートメントとは整合性セマンティクスが少し異なります。スナップショット分離とセッションとトランザクションの処理の概要については、このドキュメントの CREATE INDEX セクションをご覧ください。

次の表に、同じタスクを実行する Oracle の UPDATE ステートメントと BigQuery のステートメントを示します。

BigQuery では、UPDATE ステートメントには WHERE 句が必要です。BigQuery の UPDATE の詳細については、DML ドキュメントの BigQuery UPDATE の例をご覧ください。

DELETE および TRUNCATE ステートメント

DELETE ステートメントと TRUNCATE ステートメントは、どちらもテーブル スキーマに影響を与えることなくテーブルから行を削除する方法です。BigQuery では、TRUNCATE は使用されません。ただし、DELETE ステートメントを使用しても結果は同じです。

BigQuery では、DELETE ステートメントには WHERE 句が必要です。BigQuery の DELETE の詳細については、DML ドキュメントの BigQuery DELETE の例 をご覧ください。

Oracle BigQuery
DELETE database.table; DELETE FROM table WHERE TRUE;

MERGE ステートメント

MERGE ステートメントは、INSERTUPDATE、および DELETE 操作を組み合わせて 1 つの UPSERT ステートメントにし、複数の操作を自動的に実行することができます。MERGE 操作では、1 つのターゲット行ごとに 1 つまでのソース行を対応させる必要があります。BigQuery も Oracle も ANSI 構文に準拠します。

ただし、BigQuery の DML スクリプトは、それに対応する Oracle のステートメントとは整合性セマンティクスが少し異なります。

DDL 構文

ここでは、Oracle と BigQuery のデータ定義言語の構文の違いについて説明します。

CREATE TABLE ステートメント

Oracle の CREATE TABLE ステートメントの大部分は BigQuery と互換性があります。例外として以下の制約と構文要素がありますが、これらは BigQuery では使用されません。

  • STORAGE
  • TABLESPACE
  • DEFAULT
  • GENERATED ALWAYS AS
  • ENCRYPT
  • PRIMARY KEY (col, ...)。詳細については、CREATE INDEX をご覧ください。
  • UNIQUE INDEX。詳細については、CREATE INDEX をご覧ください。
  • CONSTRAINT..REFERENCES
  • DEFAULT
  • PARALLEL
  • COMPRESS

BigQuery の CREATE TABLE の詳細については、BigQuery の CREATE TABLE の例をご覧ください。

列のオプションと属性

バージョン 12c の Oracle で、列の自動インクリメントを有効にする Identity 列が導入されました。これは BigQuery では使用されませんが、この機能は次のバッチ方法で実現できます。サロゲートキーと変化が緩やかなディメンション(SCD)について詳しくは、次のガイドをご覧ください。

Oracle BigQuery
CREATE TABLE table (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(30)
);
INSERT INTO dataset.table SELECT
  *,
  ROW_NUMBER() OVER () AS id
FROM dataset.table

列に関するコメント

Oracle では Comment 構文を使用して列にコメントを追加します。BigQuery では、次の表に示すように列の説明を使用することで、同様の機能を実装できます。

Oracle BigQuery
Comment on column table is 'column desc'; CREATE TABLE dataset.table (
   col1 STRING
OPTIONS(description="column desc")
);

一時テーブル

Oracle では、一時テーブルがサポートされています。これは、スクリプトに中間結果を格納するためによく使用されます。一時テーブルは BigQuery でもサポートされています。

Oracle BigQuery
CREATE GLOBAL TEMPORARY TABLE
temp_tab
    (x INTEGER,
    y VARCHAR2(50))
  ON COMMIT DELETE ROWS;
COMMIT;
CREATE TEMP TABLE temp_tab
(
  x INT64,
  y STRING
);
DELETE FROM temp_tab WHERE TRUE;

次の Oracle 要素は BigQuery では使用されません。

  • ON COMMIT DELETE ROWS;
  • ON COMMIT PRESERVE ROWS;

さらに、BigQuery で一時テーブルをエミュレートする方法が他にもあります。

  • データセット TTL: 短い有効期間(たとえば 1 時間)のデータセットを作成します。このデータセット内に作成されたすべてのテーブルは、データセットの有効期間を超えて持続することがないため、事実上一時的なものになります。このデータセット内のすべてのテーブルは、名前の先頭に temp と付けることで、一時的なものであることを明示できます。
  • テーブル TTL: 次のような DDL ステートメントを使用して、テーブル固有の短い有効期間のテーブルを作成します。

    CREATE TABLE temp.name (col1, col2, ...)
    OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR));
  • WITH: 一時テーブルが同じブロック内でのみ必要な場合は、WITH ステートメントまたはサブクエリを使用した一時的な結果を使用します。

CREATE SEQUENCE ステートメント

シーケンスは BigQuery では使用されません。この機能は次のバッチ方法で実現できます。サロゲートキーと変化が緩やかなディメンション(SCD)について詳しくは、次のガイドをご覧ください。

INSERT INTO dataset.table
    SELECT *,
      ROW_NUMBER() OVER () AS id
      FROM dataset.table

CREATE VIEW ステートメント

次の表は、Oracle と BigQuery で CREATE VIEW ステートメントに相当するものを示しています。

Oracle BigQuery
CREATE VIEW view_name AS SELECT ... CREATE VIEW view_name AS SELECT ...
CREATE OR REPLACE VIEW view_name AS SELECT ... CREATE OR REPLACE VIEW view_name AS SELECT ...
サポート対象外。 CREATE VIEW IF NOT EXISTS view_name OPTIONS(view_option_list) AS SELECT ... 指定されたデータセット内に現在ビューが存在しない場合にのみ、新しいビューを作成します。

CREATE MATERIALIZED VIEW ステートメント

BigQuery では、マテリアライズド ビューの更新オペレーションが自動的に実行されます。BigQuery で、commit 時やスケジュール時などの更新オプションを指定する必要はありません。詳細については、マテリアライズド ビューの概要をご覧ください。

ベーステーブルが追記のみによって変更され続ける場合、マテリアライズド ビューを使用するクエリは、ビューが明示的に参照されているかクエリ オプティマイザによって選択されたかに関係なく、すべてのマテリアライズド ビューと、前回のビュー更新以降のベース・テーブルの差分をスキャンします。つまり、クエリをより高速かつ低コストで実行できます。

一方、前回のビュー更新以降にベーステーブル内で更新(DML UPDATE、MERGE)または削除(DML DELETE、切り捨て、パーティションの有効期限切れ)があった場合、マテリアライズド ビューはスキャンされず、クエリは保存された内容を次のビューの更新まで取得しません。基本的に、ベーステーブル内の更新または削除はマテリアライズド ビューの状態を無効にします。

また、ベーステーブルのストリーミング バッファからのデータはマテリアライズド ビューに保存されません。マテリアライズド ビューが使用されているかどうかにかかわらず、ストリーミング バッファは引き続き完全にスキャンされます。

次の表は、Oracle と BigQuery で CREATE MATERIALIZED VIEW ステートメントに相当するものを示しています。

Oracle BigQuery
CREATE MATERIALIZED VIEW view_name
REFRESH FAST NEXT sysdate + 7
AS SELECT … FROM TABLE_1
CREATE MATERIALIZED VIEW
view_name AS SELECT ...

CREATE [UNIQUE] INDEX ステートメント

ここでは、BigQuery で Oracle のインデックスと同様の機能を作成するための方法を示します。

パフォーマンス向上のためのインデックス作成

BigQuery は明示的なインデックスを必要としません。クエリとストレージの最適化を備えた列指向のデータベースだからです。BigQuery では、パーティショニングとクラスタリング、さらにネストされたフィールドなどの機能を使用できます。これらを使用してデータの格納方法を最適化することで、クエリの効率とパフォーマンスが向上します。

整合性向上のためのインデックス作成(UNIQUE、PRIMARY INDEX)

Oracle では、一意のインデックスを使用して、一意でないキーを持つ行がテーブル内に存在しないようにできます。インデックス内にすでに存在する値を持つデータを挿入または更新しようとすると、オペレーションはインデックス違反で失敗します。

BigQuery は明示的なインデックスを提供しないため、代わりに MERGE ステートメントを使用して、ステージング テーブルからターゲット テーブルに一意のレコードのみを挿入すると同時に、重複レコードを破棄できます。ただし、編集権限を持つユーザーが重複レコードを挿入しないようにする方法はありません。

BigQuery で重複レコードのエラーを生成するには、次の例に示すようにステージング テーブルから MERGE ステートメントを使用します。

Oracle BigQuery
CREATE [UNIQUE] INDEX name; MERGE `prototype.FIN_MERGE` t \
USING `prototype.FIN_TEMP_IMPORT` m \
ON t.col1 = m.col1 \
  AND t.col2 = m.col2 \
WHEN MATCHED THEN \
  UPDATE SET t.col1 = ERROR(CONCAT('Encountered Error for ', m.col1, ' ', m.col2)) \
WHEN NOT MATCHED THEN \
  INSERT (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES(col1,col2,col3,col4,col5,col6, CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());

ダウンストリーム システムのエラーを見つけるために、ユーザーが個別に重複を排除することを選ぶ場合が多いです。

BigQuery は DEFAULT 列と IDENTITY(シーケンス)列をサポートしていません。

ロック

BigQuery には Oracle のようなロック メカニズムはなく、割り当てに達するまで同時実行クエリを行うことができます。ただし、DML ステートメントだけには一定の同時実行制限があり、場合によっては実行中のテーブルロックが必要になります。

手続き型 SQL ステートメント

ここでは、ストアド プロシージャ、関数、トリガーで使用される手続き型 SQL ステートメントを Oracle から BigQuery に変換する方法について説明します。

CREATE PROCEDURE ステートメント

ストアド プロシージャは、BigQuery スクリプトのベータ版の一部としてサポートされています。

Oracle BigQuery メモ
CREATE PROCEDURE CREATE PROCEDURE Oracle と同様に、BigQuery は IN, OUT, INOUT 引数モードをサポートしています。その他の構文仕様は BigQuery ではサポートされていません。
CREATE OR REPLACE PROCEDURE CREATE OR REPLACE PROCEDURE
CALL CALL

以下のセクションでは、Oracle の既存の手続き型ステートメントを、同様の機能を持つ BigQuery のスクリプト ステートメントに変換する方法について説明します。

CREATE TRIGGER ステートメント

トリガーは BigQuery では使用されません。行ベースのアプリケーション ロジックはアプリケーション レイヤで処理する必要があります。トリガー機能を実現するには、取り込み中に取り込みツール、Pub/Sub、Cloud Run の関数を使用したり、定期的なスキャンを使用します。

変数宣言と割り当て

次の表に、Oracle の DECLARE ステートメントとそれに対応する BigQuery のステートメントを示します。

Oracle BigQuery
DECLARE
  L_VAR NUMBER;
BEGIN
  L_VAR := 10 + 20;
END;
DECLARE L_VAR int64;
BEGIN
  SET L_VAR = 10 + 20;
  SELECT L_VAR;
END
SET var = value; SET var = value;

カーソル宣言と演算子

BigQuery ではカーソルがサポートされないため、次のステートメントは BigQuery では使用されません。

動的 SQL ステートメント

次の Oracle 動的 SQL ステートメントについては、BigQuery に同等の機能があります。

Oracle BigQuery
EXECUTE IMMEDIATE sql_str

[USING IN OUT [, ...]];

EXECUTE IMMEDIATE

sql_expression [INTO variable[, ...]]

[USING identifier[, ...]];

;

フロー制御ステートメント

次の表に、Oracle のフロー制御ステートメントとそれに対応する BigQuery の機能を示します。

Oracle BigQuery
IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;
IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;
SET SERVEROUTPUT ON;
DECLARE
x INTEGER DEFAULT 0;
y INTEGER DEFAULT 0;
BEGIN
LOOP
  IF x>= 10 THEN
    EXIT;
  ELSIF x>= 5 THEN
     y := 5;
  END IF;
  x := x + 1;
END LOOP;
dbms_output.put_line(x||','||y);
END;
/
DECLARE x INT64 DEFAULT 0;
DECLARE y INT64 DEFAULT 0;
LOOP
  IF x>= 10 THEN
     LEAVE;
  ELSE IF x>= 5 THEN
    SET y = 5;
    END IF;
  END IF;
  SET x = x + 1;
END LOOP;
SELECT x,y;
LOOP
  sql_statement_list
END LOOP;
LOOP
  sql_statement_list
END LOOP;
WHILE boolean_expression DO
  sql_statement_list
END WHILE;
WHILE boolean_expression DO
  sql_statement_list
END WHILE;
FOR LOOP FOR LOOP は BigQuery では使用されません。他の LOOP ステートメントを使用します。
BREAK BREAK
CONTINUE CONTINUE
CONTINUE/EXIT WHEN IF 条件とともに CONTINUE を使用します。
GOTO BigQuery には、GOTO ステートメントは存在しません。IF 条件を使用します。

メタデータおよびトランザクション SQL ステートメント

Oracle BigQuery
GATHER_STATS_JOB 今のところ BigQuery では使用されていません。
LOCK TABLE table_name IN [SHARE/EXCLUSIVE] MODE NOWAIT; 今のところ BigQuery では使用されていません。
Alter session set isolation_level=serializable; /

SET TRANSACTION ...

BigQuery では、常にスナップショット分離が使用されます。詳しくは、このドキュメントの整合性の保証とトランザクション分離をご覧ください。
EXPLAIN PLAN ... BigQuery では使用されません。

類似の機能には、BigQuery のウェブ UI にあるクエリプランの説明、スロット割り当て、Stackdriver にある監査ロギングがあります。

SELECT * FROM DBA_[*];

(Oracle DBA_/ALL_/V$ ビュー)

SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLES;

詳しくは、BigQuery INFORMATION_SCHEMA の概要をご覧ください。

SELECT * FROM GV$SESSION;

SELECT * FROM V$ACTIVE_SESSION_HISTORY;

BigQuery には従来型のセッションのコンセプトがありません。UI でクエリジョブを表示するか、BigQuery に Stackdriver 監査ログをエクスポートして、BigQuery ログを分析することでジョブの分析ができます。詳細については、ジョブの詳細を表示するをご覧ください。
START TRANSACTION;

LOCK TABLE table_A IN EXCLUSIVE MODE NOWAIT;

DELETE FROM table_A;

INSERT INTO table_A SELECT * FROM table_B;

COMMIT;

テーブルの内容をクエリ出力に置換する処理は、トランザクションに相当します。これは、クエリまたはコピー オペレーションで実行できます。

クエリを使用する場合:

bq query --replace --destination_table table_A 'SELECT * FROM table_B';

コピーを使用する場合:

bq cp -f table_A table_B

複数ステートメントおよび複数行 SQL ステートメント

Oracle と BigQuery はどちらも、トランザクション(セッション)をサポートしているため、一貫して一緒に実行されるセミコロンで区切られたステートメントをサポートしています。詳細については、マルチステートメント トランザクションをご覧ください。

エラーコードとメッセージ

Oracle のエラーコードBigQuery のエラーコードは異なります。アプリケーション ロジックで現在エラーが検出されている場合は、エラーの原因を取り除いてください。BigQuery は同じエラーコードを返しません。

整合性の保証とトランザクション分離

Oracle と BigQuery はどちらもアトミックです。つまり、多数の行にわたってミューテーション単位で ACID に準拠しています。たとえば MERGE オペレーションは、複数の値を挿入、更新していても、アトミックです。

トランザクション

Oracle では、commit された読み取りまたはシリアル化可能のトランザクション分離レベルが用意されています。デッドロックが発生する可能性があります。Oracle の挿入追記ジョブは個別に実行されます。

また、BigQuery はトランザクションをサポートしています。BigQuery では、スナップショット分離を使用して、楽観的同時実行制御(最初の commit が優先)が確実に実行されるようにします。この場合、クエリは、クエリが開始される前に最後に commit されたデータを読み取ります。この方法により、行ごと、ミューテーションごと、同じ DML ステートメント内の行全体で、同じレベルの整合性が保証され、デッドロックも回避されます。同じテーブルに対する複数の UPDATE ステートメントの場合、BigQuery は悲観的同時実行制御に切り替え、複数の UPDATE ステートメントをキューに入れて、競合が発生した場合に自動的に再試行します。INSERT DML ステートメントと読み込みジョブは、同時かつ独立して実行され、テーブルへの連結を実行できます。

ロールバック

Oracle ではロールバックがサポートされています。BigQuery には明示的なトランザクション境界がないため、BigQuery には明示的なロールバックのコンセプトはありません。この問題を回避するには、テーブル デコレータを使用するか、FOR SYSTEM_TIME AS OF を使用します。

データベースに関する制限

BigQuery の最新の割り当てと上限を確認します。大量のユーザーに対する割り当ての多くは、Cloud カスタマーケアに連絡して増やすことができます。次の表に、Oracle と BigQuery のデータベースに関する上限の比較を示します。

上限 Oracle BigQuery
データベースあたりのテーブル数 制限なし 制限なし
テーブルあたりの列数 1000 10,000
最大行数 無制限(列のタイプによります) 100 MB
列とテーブルの名前の長さ If v12.2>= 128 バイト

それ以外は 30 バイト

16,384 Unicode 文字
テーブルあたりの行数 無制限 無制限
SQL リクエストの最大長 無制限 1 MB(未解決の GoogleSQL クエリの最大長)

12 MB(解決済みの以前のクエリと GoogleSQL クエリの最大長)

ストリーミング:

  • 10 MB(HTTP リクエスト サイズの上限)
  • 10,000(1 リクエストあたりの最大行数)
リクエストおよびレスポンスの最大サイズ 無制限 10 MB(リクエスト)と 10 GB(レスポンス)、ページ分割または Cloud Storage API を使用する場合は事実上無制限
同時実行セッションの最大数 セッションまたはプロセスのパラメータによって制限されます 100 同時実行クエリ(スロット予約で増やすことができます)、1 ユーザーあたり 300 同時実行 API リクエスト
同時実行(高速)読み込みの最大数 セッションまたはプロセスのパラメータによって制限されます 同時実行の制限はありません。ジョブはキューに格納されます。プロジェクトごとに 1 日あたり 100,000 読み込みジョブ

他の Oracle データベースの上限には、データ タイプの上限物理データベースの上限論理データベースの上限プロセスとランタイムの上限などがあります。