OracleのSQLをそのままBigQueryで動かしたいんですよ、絶対に。

【Unit4 ブログリレー9日目】

何言ってるんですかね。でも、見てみたいかも。こんにちは、エムスリーエンジニアリンググループの福林 (@fukubaya) です。

OracleのSQLに手を加えることなくBigQueryで実行させたいことってよくありますよね(ない)。今回はその手法を紹介します。

• 帳票環境のBigQuery移行とチーム横断での取り組み
• なぜSQL変換が必要なのか - bashを駆使してSQLを組み立てる地獄
• 段階的移行のための並行実行環境の構築
  • 並行実行環境のアーキテクチャ
  • SQL変換の課題と初期アプローチ
• SQLをparseして変換する
  • sqlparse
  • ConvertSQLクラスの実装
  • 関数変換の詳細実装
    • 変換処理の全体的な流れ
    • パターンマッチングによる変換
    • 個別変換ルール（スクリプト固有）
    • sqlparseによるトークンベース変換
    • 前処理による構文調整（filter_tokens）
    • トークンベース変換の仕組み
    • 複雑な関数変換の実例
    • 個別関数の変換の例
    • 演算処理の変換の例
    • 特殊な名前・シンボルの変換
  • テーブル名変換の実装
  • 自動変換の限界と手動対応が必要なケース
    • 暗黙の型変換に依存するクエリ
    • 構文の根本的な違い
• まとめ
• We are hiring!

帳票環境のBigQuery移行とチーム横断での取り組み

弊社では、各種レポート生成や調査のための「帳票環境」を運用しています。前回の僕の記事で紹介したように、この環境では本番サービスのレプリカDBを使用していますが、HW保守期限の関係でレプリカ先をBigQueryに移行する必要が生じました。

www.m3tech.blog

レプリカ先をBigQueryに移すこと自体はそれほど難しくありませんが、既存のクエリをBigQuery用に書き換えるのは非常に困難です。帳票環境のDBはOracleであり、現状のクエリはすべてOracleの文法に合わせて書かれています。Oracle独自の文法やルールが前提となっているため、それをそのままBigQueryで実行することはできません。

Unit4(m3.com開発チーム)では、担当するバッチが相対的に少なく、早くからBigQuery化のための環境を整備していたため、担当バッチのほぼすべてでBigQuery移行を完了することができました。

一方、担当領域が広く歴史も長いUnit1(製薬企業向けプラットフォームチーム)では、大量のバッチを抱えているため、移行がなかなか進んでいませんでした。そこで、Unit1のBigQuery移行をお手伝いすることになりました。

www.m3tech.blog

なぜSQL変換が必要なのか - bashを駆使してSQLを組み立てる地獄

帳票環境では、レプリカOracleに対してSQLを実行するための共通コマンド do_sql が用意されています。このコマンドを使用することで、例えば do_sql "select 1 from dual" のように、シンプルにSQLを実行することができます。

しかし、実際のレポート作成では、この do_sql とbashスクリプトを組み合わせた複雑な処理が行われています。bashの変数展開機能を活用して、動的にテーブル名や検索条件を変更したり、日付などのパラメータを埋め込んだりしています。例えば、do_sql "select * from ${TMP_TABLE} where date = '${TODAY}' AND ${SOME_CONDITION}" のように、実行時に変数が展開されて最終的なSQLが生成されます。

さらに複雑なのは、SQLの実行結果を利用して新たなSQLを動的に組み立てたり、一時テーブルを作成してそれを使った次のクエリを実行したりする処理が多数存在することです。このような処理では、単純にbash内の do_sql コマンドをBigQuery用のコマンドに置き換えるだけでは対応できません。SQLそのものの文法がOracleとBigQueryで異なるため、実行されるSQL文を適切に変換する必要があるのです。

段階的移行のための並行実行環境の構築

この課題を解決するため、Unit1では既存のOracleベースの「帳票環境」を維持しながら、BigQueryでも同じ処理を実行できる並行実行環境を構築しました。

並行実行環境のアーキテクチャ

並行実行環境では、既存の「帳票環境」と同様のDocker環境を構築し、重要な変更を1つだけ行いました。それは、do_sqlコマンドを「受け取ったOracleのSQLを自動的にBigQuery用に変換して実行する」新しい実行ファイルに置き換えることです。

この仕組みにより、以下のメリットが得られます：

• 既存スクリプトの保護: bashスクリプトは基本的に変更不要（一部調整は必要）
• 段階的移行: Oracleでの運用を継続しながら、BigQueryでの動作検証が可能
• リスク軽減: 移行に失敗した場合でも、既存環境での運用を継続できる

SQL変換の課題と初期アプローチ

この並行実行環境の核となるのが、OracleのSQLをBigQuery用に自動変換する機能です。当初はUnit1では、文字列のパターンマッチによる変換を試みましたが、SQLの複雑さにより限界が明らかになりました。

例えば、以下のような変換が必要です：

-- Oracle
select nvl(x, 0) from table;

-- BigQuery
select ifnull(x, 0) from table;

しかし、より複雑で関数が入れ子になったようなSQLでは単純なパターンマッチでは対応できません：

-- Oracle
select
     nvl(to_char(date_col, 'YYYY-MM-DD'), '-') as date_str,
     decode(status, 1, 'Active', 0, 'Inactive', '-') as status_str,
     add_months(date_col, -1) as last_month
from table
where trunc(create_date) >= add_months(trunc(sysdate), -3);

-- BigQuery
select
    IFNULL(FORMAT_DATE('%Y-%m-%d', date_col), '-') as date_str,
    CASE WHEN status = 1 THEN 'Active' WHEN status = 0 THEN 'Inactive' ELSE CAST('-' AS STRING) END as status_str,
    DATETIME_ADD(date_col, INTERVAL -1 MONTH) as last_month
from table
    where DATE_TRUNC(datetime(create_date), DAY) >= DATETIME_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -3 MONTH);

このような複雑な変換を正確に行うため、SQLの構文解析に基づいたより高度なアプローチが必要となりました。

SQLをparseして変換する

パターンマッチでは限界があるので、SQLをparseして、その構造に基づいて変換することにしました。

sqlparse

sqlparse は、与えられたSQLをparseして字句解析してくれるpythonパッケージです。これを使って解析していきます。

pypi.org

>>> import sqlparse
>>> parsed = sqlparse.parse("select nvl(x, 0) from table")[0];
>>> parsed.tokens
[<DML 'select' at 0x105966588>, <Whitespace ' ' at 0x1059665E8>, <Function 'nvl(x,...' at 0x10593FDC8>, <Whitespace ' ' at 0x1059668E8>, <Keyword 'from' at 0x105966948>, <Whitespace ' ' at 0x1059669A8>, <Keyword 'table' at 0x105966A08>]

ConvertSQLクラスの実装

実際の変換処理は ConvertSQL クラスで行います。基本的な使い方は以下のようになります：

converter = ConvertSQL().set_sql(oracle_sql)
bigquery_sql = converter.converter().get_sql()

converter() メソッドでは、以下の順序で変換を実行します：

def converter(self):
    self._convert_function()  # 関数の変換
    self._convert_table()     # テーブル名の変換
    return self

関数変換の詳細実装

変換処理の全体的な流れ

関数変換は以下の段階を経て実行されます：

def _convert_function(self):
    # 1. パターンマッチングによる変換（共通変換ルール）
    for f in COMMON_CONVERT_FUNCTIONS:
        self.sql = f(self.sql)
    
    # 2. 個別変換ルール（スクリプト固有）
    specific_pattern = self._read_specific_pattern()
    if self.caller_script in specific_pattern:
        for oracle_pattern, bigquery_pattern in specific_pattern.get(self.caller_script):
            self.sql = re.sub(oracle_pattern, bigquery_pattern, self.sql, flags=re.IGNORECASE)
    
    # 3. sqlparseによるトークンベース変換
    self.sql = replace_functions.replace_functions(self.sql)

この流れでは、まず単純なパターンマッチングによる変換を行い、その後でより複雑なトークンベース変換を実行します。

パターンマッチングによる変換

トークンベース変換の前に、正規表現を使った単純なパターンマッチングによる変換を行います。これは、特定のOracleの構文パターンを効率的にBigQueryの構文に置き換えるために使用されます。

XMLAgg関数の変換例

Oracleでよく使われる文字列連結のパターンを、BigQueryのSTRING_AGG関数に変換します：

def replace_xmlagg(sql):
    # XMLAgg パターンを検索するための正規表現
    pattern = r"replace\s*\(\s*replace\s*\(\s*replace\s*\(\s*XMLAgg\s*\(\s*XMLElement\s*\(\s*[\"']_[\"']\s*,\s*([^)]+)\s*\)\s*order\s+by\s+([^)]+)\s*\)\s*\.getclobval\s*\(\s*\)\s*,\s*[\"']</_><_>[\"']\s*,\s*[\"']([^\"']*)[\"']\s*\)\s*,\s*[\"']</_>[\"']\s*,\s*[\"']([^\"']*)[\"']\s*\)\s*,\s*[\"']<_>[\"']\s*,\s*[\"']([^\"']*)[\"']\s*\)"

    def replace_func(match):
        field = match.group(1)
        sort_clause = match.group(2).strip()
        delimiter = match.group(3)
        end_quote = match.group(4)
        start_quote = match.group(5)

        # BigQueryの集約関数内でNULLS LASTが使えない
        sort_clause = re.sub("nulls last", "", sort_clause, flags=re.IGNORECASE)

        return f"'{start_quote}' || STRING_AGG({field}, '{delimiter}' ORDER BY {sort_clause}) || '{end_quote}'"

    return re.sub(pattern, replace_func, sql, flags=re.IGNORECASE)

この変換により、以下のような複雑なOracleの文字列連結構文が、BigQueryのSTRING_AGG関数に変換されます：

-- Oracle
replace(replace(replace(XMLAgg(XMLElement('_', field_name) order by sort_field nulls last).getclobval(),'</_><_>',','),'</_>',''),'<_>','')

-- BigQuery
'' || STRING_AGG(field_name, ',' ORDER BY sort_field) || ''

個別変換ルール（スクリプト固有）

共通の変換ルールでは対応できない、特定のスクリプト固有の変換パターンを定義できます。これらのルールはspecific_patternディレクトリ内のPythonファイルで管理されます。

各スクリプト固有の変換ルールは、以下の形式で定義されます。

SPECIFIC_TARGET_PATTERN = {
    "/path/to/script.sh": [
        (r"oracle_pattern", r"bigquery_pattern"),
        (r"another_oracle_pattern", r"another_bigquery_pattern"),
    ]
}

スクリプトが"/path/to/script.sh" であったら、リストに定義された変換パターンを順に適用させます。

この仕組みにより、共通の変換ルールでは対応できない特殊なケースも柔軟に対応できます。

sqlparseによるトークンベース変換

パターンマッチングでは対応できない複雑な構文に対して、sqlparseを使ったトークンベース変換を行います：

def replace_functions(ora_sql):
    # 1. SQLをparseしてトークンに分解
    parsed_tokens = sqlparse.parse(ora_sql)[0].tokens
    
    # 2. 前処理でOracleとBigQueryの構文差異を調整
    filtered_tokens = filter_tokens(parsed_tokens)
    
    # 3. トークンベースの変換を実行
    converted_tokens = convert_tokens(filtered_tokens)
    
    # 4. 変換後のトークンからSQLを再構築
    bq_sql = sql.Statement(converted_tokens)
    return str(bq_sql)

前処理による構文調整（filter_tokens）

変換処理の前に、OracleとBigQueryの構文の違いに対処するための前処理を行います：

def filter_tokens(tokens):
    """トークンをフィルタリングする"""
    token_filters = [remove_from_dual, add_all_to_union]
    
    r = tokens
    for f in token_filters:
        r = f(r)
    
    return r

FROM DUAL の除去

OracleではダミーテーブルとしてDUALを使いますが、BigQueryでは不要なので除去します：

-- Oracle
SELECT 1 FROM DUAL;

-- BigQuery (前処理後)
SELECT 1;

UNION への ALL 追加

OracleとBigQueryではUNIONのデフォルト動作が異なるため、明示的にALLを追加します：

-- Oracle
SELECT col1 FROM table1 UNION SELECT col1 FROM table2;

-- BigQuery (前処理後)
SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2;

トークンベース変換の仕組み

前処理が完了したトークンに対して、実際の変換処理を行います。この変換処理では、convert_tokens()とconvert_token()という2つの関数が重要な役割を果たします：

convert_tokens() - 複数トークンの組み合わせ処理

convert_tokens()は、トークンのリスト全体を処理し、複数のトークンを組み合わせた変換を行います。これはmin/max(col) keep(...)のように、1つのトークンだけでは変換できない構文に対処するために必要です：

def convert_tokens(tokens):
    """tokenのリストを変換する
    
    min/max(col) keep(...) のような2トークン以上の組み合わせを変換するのに必要
    """
    new_tokens = []
    it = iter(range(len(tokens)))
    
    for i in it:
        t = tokens[i]
        
        if isinstance(t, sql.Function) and t.get_real_name().lower() in ["max", "min"]:
            # 次のトークンをチェック
            idx, next_token = t.parent.token_next_by(idx=i + 1, i=(sql.Function))
            
            # 次のトークンがKEEP関数の場合、2つのトークンを組み合わせて変換
            if (isinstance(next_token, sql.Function) and 
                next_token.tokens[0].get_real_name().lower() == "keep"):
                
                new_tokens += convert_minmax_keep_tokens(t, next_token, "")
                # 処理した分だけイテレータを進める
                for _ in range(idx - i):
                    next(it, None)
            else:
                new_tokens.append(convert_token(t))
        else:
            new_tokens.append(convert_token(t))
    
    return new_tokens

convert_token() - 単一トークンの変換と再帰処理

convert_token()は、1つのトークンを変換するための関数です。重要な特徴は、トークンがグループ（子トークンを持つ）である場合に、再帰的に子のトークン列に対して変換を適用することです：

def convert_token(token):
    """tokenを変換する"""
    
    if isinstance(token, sql.Operation):
        token = convert_operation(token)
    elif isinstance(token, sql.Function):
        token = convert_function(token)
    elif isinstance(token, sql.Identifier):
        token = convert_identifier(token)
    elif token.ttype is T.Name:
        token = convert_name(token)
    elif token.ttype is T.Literal.String.Symbol:
        token = convert_symbol(token)
    elif isinstance(token, sql.Parenthesis):
        # 括弧内のトークンを再帰的に変換
        token.tokens = convert_tokens(token.tokens)
    elif token.is_group:
        # グループの場合は子トークンを再帰的に変換
        token.tokens = [convert_token(t) for t in token.tokens]

    if token.is_group:
        # グループの場合はvalueを再計算
        token.value = tokens_to_str(token.tokens)

    return token

この再帰的な処理により、関数がネストされている場合でも正しく変換が行われます。例えば：

-- ネストした関数の例
SELECT NVL(TO_DATE(col1, 'YYYY-MM-DD'), SYSDATE) FROM table;

この場合、以下のような順序で変換されます：

1. NVL関数のトークンを処理
2. NVLの引数部分（括弧内）を再帰的に処理
3. TO_DATE関数をPARSE_DATETIMEに変換
4. SYSDATEをCURRENT_DATE('Asia/Tokyo')に変換
5. 最終的にNVLをIFNULLに変換

結果：

SELECT IFNULL(PARSE_DATETIME('%Y-%m-%d', col1), CURRENT_DATE('Asia/Tokyo')) FROM table;

複雑な関数変換の実例

MAX/MIN KEEP句の変換

Oracleの分析関数で使われる複雑な構文も正確に変換できます：

-- Oracle
SELECT max(col) keep (dense_rank first order by order_col) FROM table;

-- BigQuery
SELECT ARRAY_AGG(col IGNORE NULLS ORDER BY order_col LIMIT 1)[OFFSET(0)] FROM table;

実装では、2つのトークン（MAX関数とKEEP関数）を組み合わせて処理します：

def convert_minmax_keep_tokens(min_max_token, keep_token, following_str=""):
    window_func_params = list(get_function_paramers(min_max_token))
    keep_dense_rank_orders = extract_keep_dense_rank_orders(keep_token)
    
    return generate_token_from_expr(
        f"ARRAY_AGG({tokens_to_str(window_func_params)} IGNORE NULLS {keep_dense_rank_orders} LIMIT 1)[OFFSET(0)] {following_str}"
    )

LISTAGG WITHIN GROUP の変換

-- Oracle
select listagg(col, ',') within group (order by order_col nulls first) as x from table； 

-- BigQuery  
select STRING_AGG(col, ',' ORDER BY order_col nulls first) as x from table；

この変換では、LISTAGG関数とWITHIN GROUPキーワードを連続して検出し、一つのSTRING_AGG関数に変換します。

個別関数の変換の例

NVL関数の変換

def convert_function_nvl(f_token):
    """nvl()を変換する"""

    params = list(get_function_paramers(f_token))
    p1 = convert_token(params[0])
    p2 = convert_token(params[1])

    # nvl(x, y) => IFNULL(x, y)
    return generate_token_from_expr(f"IFNULL({tokens_to_str(p1)}, {tokens_to_str(p2)})")

-- Oracle
select nvl(col1, 'default') from table;
                
--- BigQuery
select IFNULL(col1, 'default') from table;

TO_DATE関数の変換

def convert_function_to_date(f_token):
    """to_date()を変換する"""

    # to_date(x, 'ora_format') => PARSE_DATETIME('bq_format', x)
    params = list(get_function_paramers(f_token))
    if len(params) == 2 and params[1].ttype is T.Literal.String.Single:
        bq_format = oradt2bqdt(params[1].value)  # フォーマット変換
        return generate_token_from_expr(f"PARSE_DATETIME({bq_format}, {params[0]})")
    return f_token

日付フォーマットも自動で変換されます：

-- Oracle
select TO_DATE('2023-01-01', 'YYYY-MM-DD') from table;

-- BigQuery
select PARSE_DATETIME('%Y-%m-%d', '2023-01-01') from table;

DECODE関数の変換

def convert_function_decode(f_token):
    """decode()を変換する"""

    params = list(get_function_paramers(f_token))
    contain_string = False
    case_exprs = ["CASE"]
    expr = params[0]
    for i in range(1, len(params) - 1, 2):
        contain_string = params[i + 1].ttype is T.Literal.String.Single
        if params[i].value.lower() == "null":
            # decode(expr, null, result) => CASE WHEN expr IS NULL THEN result
            case_exprs.append(f"WHEN {expr} IS NULL THEN {params[i + 1]}")
        else:
            # decode(expr, search1, result1, ..., searchN, resultN)
            case_exprs.append(f"WHEN {expr} = {params[i]} THEN {params[i+1]}")

    if len(params) >= 3 and len(params) % 2 == 1:
        # デフォルト値がない decode(expr, search1, result1, ..., searchN, resultN)
        # => CASE WHEN expr = search1 THEN result1 WHEN expr = search2 THEN result2 ... END
        case_exprs.append("END")
    elif len(params) >= 3 and len(params) % 2 == 0:
        # デフォルト値がある decode(expr, search1, result1, ..., searchN, resultN, default)
        # => CASE WHEN expr = search1 THEN result1 WHEN expr = search2 THEN result2 ... ELSE default END

        # resultNに文字列リテラルが含まれている場合は、defaultをSTRING型にキャストする
        if contain_string:
            case_exprs.append(f"ELSE CAST({params[-1]} AS STRING) END")
        else:
            case_exprs.append(f"ELSE {params[-1]} END")

    return generate_token_from_expr(f"{' '.join(case_exprs)}")

-- Oracle
select decode(status, 1, 'Active', 'Unknown') from table;
select decode(status, null, 'Active', 'Unknown') from table;
select decode(type, 1, 100, 0) from table;
select decode(status, 1, 'Active', 0, 'Inactive', 'Unknown') from table;

-- BigQuery
select CASE WHEN status = 1 THEN 'Active' ELSE CAST('Unknown' AS STRING) END from table;
select CASE WHEN status IS NULL THEN 'Active' ELSE CAST('Unknown' AS STRING) END from table;
select CASE WHEN type = 1 THEN 100 ELSE 0 END from table;
select CASE WHEN status = 1 THEN 'Active' WHEN status = 0 THEN 'Inactive' ELSE CAST('Unknown' AS STRING) END from table;

演算処理の変換の例

TO_DATE関数と数値の演算があった場合には、DATETIME_ADD に変換します。

-- Oracle
TO_DATE('2023-01-01', 'YYYY-MM-DD') + 7

-- BigQuery
DATETIME_ADD(PARSE_DATETIME('%Y-%m-%d', '2023-01-01'), INTERVAL 7 DAY)

特殊な名前・シンボルの変換

def convert_name(n_token):
    if n_token.value.lower() == "sysdate":
        # sysdate を CURRENT_DATE('Asia/Tokyo') に変換
        n_token = generate_token_from_expr("CURRENT_DATE('Asia/Tokyo')")
    elif ...

テーブル名変換の実装

テーブル名の変換では、FROM句やJOIN句を正確に識別し、WITH句やサブクエリも考慮して変換を行います：

def replace_tables(org_sql, table_mapping):
    # with句で指定されるテーブル名を抽出
    with_tables = extract_with_tables(org_sql)
    
    # トークンを解析してテーブル名を変換
    tokens = sqlparse.parse(org_sql)[0].tokens
    replaced_tokens = replace_tables_in_tokens(tokens, with_tables, table_mapping)
    
    return str(sql.Statement(replaced_tokens))

テーブル名のマッピングは設定ファイルで管理されており、以下のような形式で定義されています：

CONVERT_TABLES = {
    "table1": "`{PROJECT}.dataset.table1`",
    "table2": "`{PROJECT}.dataset.table2`",
    # ... その他のテーブル
}

自動変換の限界と手動対応が必要なケース

SQL変換ツールによる自動変換には限界があり、一部のクエリは手動での書き直しが必要です。これらのケースは主に以下の2つのカテゴリに分類されます。

暗黙の型変換に依存するクエリ

OracleとBigQueryでは型変換の動作が異なるため、Oracleの暗黙の型変換に依存したクエリはBigQueryでエラーになります：

数値と文字列の比較

-- Oracle（動作する）
select * from table where id = 0;  -- idがVARCHAR2でも数値に変換されて比較される

-- 修正版
select * from table where id = '0';  -- 文字列として比較

日付の算術演算

-- Oracle（動作する）
select start_date + 1 from dual;  -- DATE + 1 = 翌日

-- 修正版
select start_date + (INTERVAL '1' DAY) from dual;

CASE文での型不一致

-- Oracle（動作する）
select case when x = 1 then 0 else '1' end from table;  -- 数値と文字列が混在

-- 修正版
select case when x = 1 then '0' else '1' end from table;  -- 統一した型で返す

構文の根本的な違い

一部のOracleの構文はBigQueryに対応するものがないため、同等の結果を得られる別の書き方に変更する必要があります：

MINUS演算子

-- Oracle
select x from table1 minus select x from table2;

-- または、NOT IN / NOT EXISTSを使用
select x from table1 where x not in (select x from table2 where x is not null);

これらの手動対応が必要なケースに対しては、自動変換 → エラー検出 → 手動修正のサイクルで対応しています。 完全な自動変換は困難ですが、大部分のクエリは自動変換でき、手動修正は少数です。

まとめ

本記事では、OracleのSQLをBigQueryのSQLに自動変換するツールの開発と運用について紹介しました。

• sqlparseライブラリを使用したSQL構文解析による高精度な変換
• 3段階の変換処理：パターンマッチング → 個別ルール → トークンベース変換
• 再帰的なトークン処理により、ネストした関数や複雑な構文にも対応
• スクリプト固有の変換ルールで特殊なケースにも柔軟に対応

このツールにより、大規模なSQL変換プロジェクトを効率的に進めることができ、レガシーシステムのクラウド移行を加速できています。

We are hiring!

10年ものから先月できたもの、もちろんこれからできる新たなものまで多様なサービスを一緒に開発してくれる仲間を募集中です。お気軽にお問い合わせください。

jobs.m3.com