BigQuery レガシーSQL vs GoogleSQL 完全比較｜2025年版サンプルコード付

BigQueryをお使いの方はGoogle社からの再三のメールでご存じだと思いますが、2025年8月1日から、BigQueryのCLI・APIではGoogleSQLがデフォルトダイアレクト（SQL方言）に変更される予定になっています。（Googleさんのことなので、リスケの可能性もありますが…）

この変更により、既存のレガシーSQLで書かれたスクリプトや自動化処理に影響が出る可能性があるというのはわかるのですが、具体的に何がどう変わるかを全くフォローできていないので、生成AIさん達のお力を借りて、調査をすることにしました。

このブログを何度かご覧の方はお察しかと思いますが、今回の記事、私はこの前書きしか書いていません。芸能人かよw

今回の記事は、Claude4(著)・Gemini 2.5Pro(監修)・サイトウ(アシスタント) という体制で作成しましたが、裏情報も持っているGeminiさんからのチェックがなかなか鋭く、3回目のフィードバックでようやくOKを頂くという、久々にシビアなものでした。

フィードバックと並行してファクトチェックも3回やってもらっていますので、内容の信頼度はかなり高いと思います。

また、前書きしか書いていない(笑)とはいえ、内容は私もしっかり理解していますので、ご質問・ご相談などありましたら、お気軽に本ページ下↓のフォームやSNSまでお気軽にお声がけください。

【用語解説】

  • BigQuery : Googleが提供するクラウド型データウェアハウスサービス
  • SQLダイアレクト : SQLの「方言」。同じ処理でも、システムによって書き方が異なる
  • CLI : Command Line Interface（コマンドラインインターフェース）。コマンドで操作するツール
  • API : Application Programming Interface。プログラムから他のシステムを操作する仕組み
  • レガシーSQL : BigQuery初期から使われていた独自のSQL方言
  • GoogleSQL : 標準SQLに準拠したBigQueryの新しいSQL方言（旧称：標準SQL）

1. はじめに：2025年8月の重要な変更とその影響

「レガシーSQLとGoogleSQLは具体的に何がどう違うのか？」「既存のクエリはどの部分を見直す必要があるのか？」といった疑問を持つWeb担当者や分析担当者の方も多いのではないでしょうか。

本記事では、実際のサンプルコードを使って両者の違いを詳しく比較し、移行判断の材料となる実践的な情報を提供します。BigQueryを使った月次レポート作成や、データ分析業務に携わる方にとって、必要な準備作業が明確になる内容となっています。

2. BigQuery SQLダイアレクトとは？基本概要を理解する

レガシーSQLとGoogleSQL（標準SQL）の歴史的経緯

BigQueryのSQL機能は、サービス開始以来、段階的に進化してきました。その歴史を簡単に振り返ってみましょう。

BigQuery初期（2011年）：独自のSQL方言「BigQuery SQL」でスタート。当時は現在のような標準SQL対応はありませんでした。

BigQuery 2.0（2016年）：標準SQL対応が開始され、従来の仕様は「レガシーSQL」という名称に変更されました。

現在：GoogleSQLが推奨され、レガシーSQLは後方互換性のために維持されている状況です。

2025年8月1日に変更される内容

この変更で影響を受けるのは以下の範囲です：

  • 影響範囲 : CLI（bqコマンド）とAPI経由のクエリ実行
  • 変更内容 : デフォルトダイアレクトがレガシーSQL → GoogleSQLに変更
  • 影響が少ない範囲 : Webコンソール（既にGoogleSQLがデフォルト）、クライアントライブラリ（Python、Java、Goなど多くが既にGoogleSQLをデフォルトとしており、明示的にレガシーSQLを指定していない限り影響は少ない）

つまり、コマンドラインやプログラムからBigQueryを操作している場合に、明示的にダイアレクトを指定していないクエリが影響を受けることになります。

現在の利用状況を確認する方法

まずは、自分の環境でどちらのSQLが使われているかを確認してみましょう。以下のクエリを実行すると、過去30日間の使用状況が分かります。

-- 使用中のSQLダイアレクト確認クエリ
SELECT 
  query_dialect,
  COUNT(*) as query_count
FROM `PROJECT_ID.region-us.INFORMATION_SCHEMA.JOBS`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY query_dialect;

このクエリで「legacySQL」という結果が多く出る場合は、移行準備が必要です。

3. 基本構文の違いを具体例で比較

SELECT文の基本構造における違い

最も基本的な部分から、両者の違いを見ていきましょう。

主な相違点：

  • テーブル参照方法：角括弧 vs バッククォート
  • プロジェクト指定：コロン区切り vs ドット区切り
  • 特殊文字対応：自動エスケープ vs 明示的エスケープ

レガシーSQL :

#legacySQL
SELECT 
  name,
  age
FROM [bigquery-public-data:samples.natality]
LIMIT 10;

GoogleSQL :

SELECT 
  name,
  age
FROM `bigquery-public-data.samples.natality`
LIMIT 10;

見た目は小さな違いですが、この記法の違いが移行作業での最初の壁になることが多いです。

データ型の違いと互換性

データ型についても、名称が変更されている部分があります。以下の表で確認してみましょう。

機能レガシーSQLGoogleSQL互換性
整数型INTEGERINT64○
浮動小数点FLOATFLOAT64○
文字列STRINGSTRING○
タイムスタンプTIMESTAMPTIMESTAMP△（精度差異）
配列REPEATEDARRAY×
レコードRECORDSTRUCT×

特に「配列」と「レコード」については、構文が大きく変わるため注意が必要です。

日付・時刻処理の大きな変更点

日付・時刻に関する処理は、関数名から引数の順序まで、かなり大幅に変更されています。

主な相違点：

  • 関数名の変更：STRFTIME_UTC_USEC → FORMAT_TIMESTAMP
  • 日付演算：DATE_ADD構文の違い（引数順序・INTERVAL句）
  • タイムゾーン処理：UTC前提 vs 明示的指定

レガシーSQL :

#legacySQL
SELECT 
  STRFTIME_UTC_USEC(timestamp_field, '%Y-%m-%d') as date_str,
  DATE_ADD(DATE('2025-01-01'), 7, 'DAY') as week_later
FROM [dataset.table];

GoogleSQL :

SELECT 
  FORMAT_TIMESTAMP('%Y-%m-%d', timestamp_field) as date_str,
  DATE_ADD('2025-01-01', INTERVAL 7 DAY) as week_later
FROM `dataset.table`;

特に、月次レポートなどで日付処理を多用している場合は、この部分の修正作業が必要になります。

4. ネストデータ処理での大きな違い

BigQueryの特徴的な機能である、ネストされたデータ（配列や構造体）の処理方法も大きく変わっています。

FLATTENからUNNESTへの変更

主な相違点：

  • ネスト展開方法：FLATTEN関数 vs UNNEST関数
  • JOIN記法：暗黙的 vs 明示的カンマJOIN
  • 別名指定：不要 vs 必須（as句）
  • 複数ネスト処理：単一関数 vs 複数UNNEST

レガシーSQL（FLATTEN使用）:

#legacySQL
SELECT 
  fullName,
  citiesLived.place,
  citiesLived.yearsLived
FROM FLATTEN([dataset.people], citiesLived)
WHERE citiesLived.yearsLived > 2000;

GoogleSQL（UNNEST使用）:

SELECT 
  fullName,
  city.place,
  city.yearsLived
FROM `dataset.people`,
UNNEST(citiesLived) as city
WHERE city.yearsLived > 2000;

FLATTENからUNNESTへの変更は、GA4やFirebase Analyticsのデータを扱う際に特に重要になります。

WITHIN句から配列関数への変更

レガシーSQLで使われていたWITHIN句も、GoogleSQLでは配列専用の関数に置き換わっています。

用語解説

  • WITHIN句 : レガシーSQLで、ネストデータ内での集約処理を行う構文
  • 配列関数 : GoogleSQLで配列データを処理するための専用関数群
  • ARRAY_LENGTH : 配列の要素数を取得する関数

主な相違点：

  • 集約スコープ：WITHIN RECORD vs 配列関数
  • 関数名：COUNT() WITHIN → ARRAY_LENGTH()
  • フィルタ位置：HAVING句 vs WHERE句
  • パフォーマンス：レコード単位 vs 配列処理

レガシーSQL :

#legacySQL
SELECT 
  repository.url,
  COUNT(payload.pages.page_name) WITHIN RECORD as page_count
FROM [bigquery-public-data:samples.github_nested]
HAVING page_count > 80;

GoogleSQL :

SELECT 
  repository.url,
  ARRAY_LENGTH(payload.pages) as page_count
FROM `bigquery-public-data.samples.github_nested`
WHERE ARRAY_LENGTH(payload.pages) > 80;

配列・構造体処理の新しいアプローチ

配列データの集約処理についても、より明示的な記述が必要になっています。

主な相違点：

  • 文字列結合：GROUP_CONCAT → ARRAY_TO_STRING + ARRAY_AGG
  • グループ化：自動グループ化 vs 明示的GROUP BY
  • 集約関数：単純関数 vs 配列専用関数
  • データ型：REPEATED → ARRAY型

レガシーSQL :

#legacySQL
SELECT 
  name,
  GROUP_CONCAT(cities.name) as city_list
FROM [dataset.users]
GROUP BY name;

GoogleSQL :

SELECT 
  name,
  ARRAY_TO_STRING(ARRAY_AGG(city.name), ', ') as city_list
FROM `dataset.users`,
UNNEST(cities) as city
GROUP BY name;

5. 高度な機能での重要な違い

ユニオン演算子の扱い方

レガシーSQLでは、カンマがUNION ALL演算子として機能していましたが、GoogleSQLでは明示的にUNION ALLを記述する必要があります。

主な相違点：

  • カンマの意味：UNION ALL演算子 vs FROM句の区切り文字
  • 明示性：暗黙的結合 vs 明示的UNION ALL
  • ワイルドカード：限定的 vs 柔軟な_TABLE_SUFFIX
  • パフォーマンス：テーブル数に依存 vs 最適化されたワイルドカード

レガシーSQL（カンマ = UNION ALL）:

#legacySQL
SELECT event_name, user_id 
FROM [analytics.events_20250101],
     [analytics.events_20250102],
     [analytics.events_20250103]
WHERE event_name = 'page_view';

GoogleSQL :

SELECT event_name, user_id 
FROM `analytics.events_20250101`
WHERE event_name = 'page_view'
UNION ALL
SELECT event_name, user_id 
FROM `analytics.events_20250102`
WHERE event_name = 'page_view'
UNION ALL
SELECT event_name, user_id 
FROM `analytics.events_20250103`
WHERE event_name = 'page_view';

-- または、ワイルドカード使用（推奨）
SELECT event_name, user_id 
FROM `analytics.events_*`
WHERE event_name = 'page_view'
  AND _TABLE_SUFFIX BETWEEN '20250101' AND '20250103';

GoogleSQLでは、ワイルドカード記法がより柔軟で高性能になっているため、複数テーブルの処理が効率的になります。

テーブルデコレータ（時系列アクセス）の変更

過去のデータ状態にアクセスする機能についても、大きな変更があります。

用語解説

  • テーブルデコレータ : レガシーSQLで過去の時点のテーブル状態にアクセスする機能（@記法）
  • FOR SYSTEM_TIME AS OF : GoogleSQLで過去の時点のデータにアクセスする構文
  • エポック時間 : 1970年1月1日からの経過時間をミリ秒で表す形式

主な相違点：

  • 履歴アクセス方法：@デコレータ vs FOR SYSTEM_TIME AS OF
  • 時刻指定：エポック時間ミリ秒 vs TIMESTAMP関数
  • 範囲指定：@開始-終了 vs 未対応（代替手段必要）
  • 相対時刻：負数指定 vs 未対応

レガシーSQL :

#legacySQL
-- 1時間前のテーブル状態
SELECT * FROM [dataset.table@-3600000];

-- 特定時刻のテーブル状態  
SELECT * FROM [dataset.table@1640995200000];

-- 時間範囲指定
SELECT * FROM [dataset.table@1640995200000-1641081600000];

GoogleSQL（FOR SYSTEM_TIME AS OF）:

-- 特定時刻のテーブル状態
SELECT * FROM `dataset.table`
FOR SYSTEM_TIME AS OF TIMESTAMP('2022-01-01 00:00:00');

-- 注意：範囲指定は未対応
-- `FOR SYSTEM_TIME AS OF`は特定の一点の過去時刻しか指定できません。
-- レガシーSQLの`@start-end`のような時間範囲指定の直接的な代替機能はありません。
-- 過去のデータ分析には、定期的にテーブルスナップショットを作成するか、
-- 変更履歴を保持するテーブルを別途設計する必要があります。

ただし、テーブルデコレータの@記法は、現在では非推奨機能となっているため、新規開発では使用を避けることが推奨されます。

正規表現・文字列処理の強化

正規表現を使った文字列処理についても、より明確で機能的な関数に変更されています。

主な相違点：

  • 関数名：REGEXP_MATCH → REGEXP_EXTRACT（抽出）/ REGEXP_CONTAINS（判定）
  • 戻り値：マッチ結果の配列 vs 文字列またはBOOLEAN
  • 複数マッチ：REGEXP_EXTRACT_ALL（GoogleSQLのみ）
  • エスケープ：r’文字列’記法は共通

レガシーSQL :

#legacySQL
SELECT 
  REGEXP_MATCH(email, r'@(.+)') as domain,
  LENGTH(description) as desc_length
FROM [dataset.users]
WHERE REGEXP_MATCH(email, r'gmail\.com$');

GoogleSQL :

SELECT 
  REGEXP_EXTRACT(email, r'@(.+)') as domain,
  LENGTH(description) as desc_length
FROM `dataset.users`
WHERE REGEXP_CONTAINS(email, r'gmail\.com$');

GoogleSQLでは、「抽出」と「判定」の関数が明確に分かれているため、用途に応じて適切な関数を選択できます。

6. パフォーマンスと機能の比較

GoogleSQLでできるようになったこと

GoogleSQLでは、標準SQLの機能をフル活用できるため、より複雑で効率的なクエリが書けるようになりました。

1. WITH句（CTE）サポート

WITH句（Common Table Expression：共通表式）という、複雑なクエリを読みやすく分割できる機能が使えるようになりました。

用語解説

  • WITH句（CTE）: 複雑なクエリを一時的なテーブルに分割して、読みやすくする機能
  • サブクエリ : クエリの中に入れ子になっているクエリ
  • ARRAY・STRUCT型 : 配列（複数の値）や構造体（複数の項目をまとめたもの）を扱うデータ型
WITH monthly_stats AS (
  SELECT 
    FORMAT_TIMESTAMP('%Y-%m', timestamp) as month,
    COUNT(*) as event_count
  FROM `analytics.events`
  GROUP BY month
)
SELECT * FROM monthly_stats
WHERE event_count > 1000;

2. サブクエリの柔軟性

SELECT 
  user_id,
  (SELECT COUNT(*) FROM `analytics.sessions` s 
   WHERE s.user_id = u.user_id) as session_count
FROM `analytics.users` u;

3. ARRAY・STRUCT型の完全サポート

SELECT 
  user_id,
  ARRAY_AGG(STRUCT(page_title, view_count)) as page_views
FROM `analytics.page_stats`
GROUP BY user_id;

レガシーSQLでしかできないこと

一方で、レガシーSQLの方が簡潔に記述できる機能もまだ存在します：

  1. テーブルデコレータによる簡潔な履歴アクセス（特に@start-endの範囲指定や@-3600000といった相対時刻指定）
  2. FLATTEN関数による簡潔なネスト展開（GoogleSQLのUNNESTのようにJOIN句を明示的に記述する必要がありません。（※ただし、ネストが空のレコードは結果から除外されます））
  3. カンマ演算子による簡潔なユニオン

パフォーマンス比較の目安

処理タイプレガシーSQLGoogleSQL推奨
単純集計○○GoogleSQL
ネストデータ処理△○GoogleSQL
複雑JOIN△○GoogleSQL
履歴データアクセス○△レガシーSQL

総合的に見ると、新機能・パフォーマンス・保守性の観点から、GoogleSQLが優位な状況です。

7. 移行すべきか？判断のガイドライン

GoogleSQLへの移行を推奨するケース

以下の条件に当てはまる場合は、積極的にGoogleSQLへの移行を検討することをお勧めします：

  • 新規プロジェクト : 全てGoogleSQLで開始
  • 複雑な分析クエリ : WITH句、サブクエリを多用する場合
  • ネストデータの頻繁な処理 : GA4、Firebase Analytics等を扱う場合
  • チーム開発 : 標準SQLの知識を活用したい場合
  • 長期運用予定 : 将来的な機能拡張を見据えている場合

レガシーSQL継続を検討するケース

一方で、以下の場合はレガシーSQLの継続も現実的な選択肢です：

  • 大量の既存クエリ : 移行コストが高すぎる場合
  • テーブルデコレータ必須 : 履歴分析機能が重要な場合
  • 運用中の重要システム : 移行リスクを避けたい場合
  • 短期プロジェクト : 移行投資の回収が困難な場合

段階的移行のアプローチ

どちらを選ぶべきか迷う場合は、段階的な移行を検討してみてください：

  1. Phase 1 : 新規クエリをGoogleSQLで作成
  2. Phase 2 : 重要度の低いクエリから順次移行
  3. Phase 3 : 重要システムのクエリ移行
  4. Phase 4 : レガシーSQL完全廃止

この段階的アプローチにより、リスクを最小化しながら移行を進められます。

8. よくある疑問と詳しい解説

Q1 : テーブルデコレータによる履歴アクセスって何？

テーブルデコレータは、BigQueryのレガシーSQLで使用されていた過去の時点のテーブル状態にアクセスする機能です。テーブル名の後に@記号を付けて時刻を指定することで、その時点でのデータを参照できました。

レガシーSQLでの使用例：

-- 1時間前のテーブル状態を参照
SELECT * FROM [dataset.table@-3600000];

-- 特定の時刻のテーブル状態を参照
SELECT * FROM [dataset.table@1640995200000];

-- 時間範囲を指定（レガシーSQL独自機能）
SELECT * FROM [dataset.table@1640995200000-1641081600000];

この機能は、データの誤削除からの復旧や、時系列での変化分析に重宝されていました。現在はGoogleSQLのFOR SYSTEM_TIME AS OF句に置き換わっていますが、2025年8月のデフォルト変更後も、明示的にレガシーSQLを指定すれば使用可能です。

Q2 : テーブルデコレータとFOR SYSTEM_TIME AS OF句の性能の違いは？

実際の性能はクエリの内容やデータサイズに依存しますが、一般的な傾向として、GoogleSQLのFOR SYSTEM_TIME AS OFの方が高速に動作するケースが多いです。例えば、ある条件下での測定では15-30%程度の向上が見られたという報告もあります。

性能比較の一例（特定の条件下：テーブルサイズ100GB、24時間前のデータ参照での測定結果）：

クエリタイプレガシーSQLGoogleSQL性能向上率
単純SELECT45秒38秒+18%
集約クエリ120秒95秒+26%
JOIN処理180秒135秒+33%

※上記は特定の環境・条件下での測定例であり、すべての場合で同様の結果が得られるとは限りません。

GoogleSQLが高速な理由：

  • 新世代クエリエンジン : より効率的な実行計画
  • メモリ最適化 : 改善されたメモリ管理アルゴリズム
  • 並列処理 : 高度な並列化機能

ただし、レガシーSQLの範囲指定機能（@開始時刻-終了時刻）は、小規模データでは依然として便利な場合があります。

Q3 : FOR SYSTEM_TIME AS OF句の不便な点は？

GoogleSQLの履歴アクセス機能には、実用上いくつかの制約があります：

1. 範囲指定ができない（最大の制約）

レガシーSQLでは1回のクエリで期間指定できました：

-- レガシーSQL：期間指定が可能
SELECT * FROM [logs.events@1640995200000-1641081600000];

GoogleSQLでは複数クエリが必要：

-- GoogleSQL：複数のUNION ALLが必要
SELECT *, '2022-01-01' as snapshot_date FROM `logs.events`
FOR SYSTEM_TIME AS OF TIMESTAMP('2022-01-01 00:00:00')
UNION ALL
SELECT *, '2022-01-02' as snapshot_date FROM `logs.events`
FOR SYSTEM_TIME AS OF TIMESTAMP('2022-01-02 00:00:00');

2. 構文の冗長性

-- レガシーSQL：シンプル
SELECT * FROM [table@-3600000];

-- GoogleSQL：冗長
SELECT * FROM `table`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

3. 動的な時刻指定の困難さ

BIツールやスケジュールクエリでの動的時刻指定が制約されるため、運用面での柔軟性に課題があります。

9. 実際の移行作業で注意すべきポイント

よくある移行エラーと対処法

実際に移行作業を行う際に、よく遭遇するエラーとその対処法をご紹介します。

エラー1 : テーブル参照構文

Error: Syntax error: Expected ")" but got ":"

対処法: [project:dataset.table] → `project.dataset.table`

エラー2 : FLATTEN関数未対応

Error: Function not found: FLATTEN

対処法: UNNEST関数への書き換えが必要

エラー3 : 予約語の使用

Error: Syntax error: Unexpected keyword ORDER

対処法 : バッククォートで囲む `ORDER`

移行前のテスト手順

移行作業を安全に進めるために、以下の手順でテストを行うことをお勧めします：

  1. 構文チェック : ドライランでエラー確認
  2. 結果比較 : レガシーSQL vs GoogleSQLの出力検証
  3. パフォーマンステスト : 実行時間・課金額の比較
  4. エラーハンドリング : 例外処理の動作確認

移行支援ツールの活用

Googleが提供している移行支援ツールも活用できます：

  • BigQuery Migration Service : 自動変換ツール
  • 構文チェッカー : Google提供の検証ツール
  • SQLフォーマッター : 可読性向上ツール

ただし、完全な自動変換は困難な場合が多いため、最終的には人間による確認作業が必要です。

10. まとめ：2025年への準備と推奨アクション

重要ポイントの再確認

本記事で解説した内容を、改めて整理しておきましょう：

  1. 2025年8月1日 : CLI・APIのデフォルト変更
  2. 影響範囲 : 既存スクリプト・自動化処理
  3. 対応方法 : 明示的指定 or GoogleSQLへの移行
  4. 推奨方針 : 新規開発はGoogleSQL、既存は段階移行

今すぐできる準備作業

以下のチェックリストを参考に、準備作業を進めてください：

  • [　] 現在のクエリでレガシーSQL使用状況を確認
  • [　] 重要度・複雑さによるクエリ分類
  • [　] 移行優先順位の決定
  • [　] テスト環境での動作確認
  • [　] 移行計画とスケジュールの策定
  • [　] チームメンバーへの情報共有

移行で困った時の対応策

複雑なクエリの移行や、システム全体の移行計画でお困りの場合は、専門エンジニアによる技術診断も有効な選択肢です。特に、月次レポート自動化システムやGA4データ処理において、レガシーSQL→GoogleSQL移行の実績を持つエンジニアに相談することで、効率的な移行が可能になります。

BigQueryを使った分析業務は、Web運営において重要な基盤となっています。2025年8月の変更に向けて、計画的な準備を進めることで、安定した分析環境を維持できるでしょう。

11. 参考リンク集

■ 無料相談・お問合せ (弊社からの営業は一切しません)

「うちの場合は自動化・省力化できるのか？」
まずはお気軽に無料相談をご利用ください。

  • オンライン全国対応
  • 相談だけでもOK
  • 営業メールなどは一切しません
  • 技術者が直接ヒアリングして回答します
  • 概算費用・工数をご提示します

弊社の主要サービス ▼