Teradata から BigQuery への移行: クエリ変換の概要

このドキュメントは、Teradata から BigQuery へのスキーマとデータの移行について説明するシリーズの一部です。Teradata を対象として記述された SQL ステートメントを BigQuery と互換性を持つステートメントに変換することについて、概要を説明します。

Teradata からの移行の詳細を説明するシリーズは、次のパートで構成されています。

オンプレミスのデータ ウェアハウスから Google Cloud の BigQuery への移行の概要については、データ ウェアハウスの BigQuery への移行: 導入と概要から始まるシリーズをご覧ください。

はじめに

BigQueryTeradata Database はどちらも、ANSI/ISO SQL: 2011 標準に準拠しています。さらに、Teradata 固有の機能を有効にするために、Teradata では SQL 標準に対する拡張機能が追加されています。

それとは対照的に BigQuery では、独自仕様の拡張機能のサポートはありません。したがって、一部のクエリについて、Teradata から BigQuery に移行する際にリファクタリングが必要になる場合があります。BigQuery でサポートされている ANSI/ISO SQL 標準のみで作成されたクエリを使用すれば、ポータビリティが確保され、クエリが元のデータ ウェアハウスに依存しないというメリットが増えます。

このドキュメントでは、SQL クエリを Teradata から BigQuery に移行する際に発生する可能性がある課題について説明します。エンドツーエンドで段階的に移行する際にクエリ変換が適用される状況について説明します。

Teradata SQL との相違点

このセクションでは、Teradata SQL と BigQuery の標準 SQL の間の違いや 2 つのダイアレクトを相互に変換する際の方針について簡単に説明します。なお、このドキュメントに示された相違点のリストは網羅的でないことにご注意ください。詳細については、Teradata から BigQuery への SQL 変換リファレンスをご覧ください。

データ定義言語

データ定義言語(DDL)は、データベース スキーマの定義に使用されます。DDL には、CREATEALTERDROP などの SQL ステートメントのサブセットが含まれます。

ステートメントの大部分は、Teradata SQL と標準 SQL の間で同等です。その中で例外として目立った違いには次のものがあります(網羅リストではない)。

  • CREATE INDEXPRIMARY INDEX などのインデックス操作のオプションは、BigQuery ではサポートされていません。BigQuery では、データをクエリするときにインデックスは使用されません。BigQuery ではその代わり、Dremel を使用した基礎モデル、Capacitor を使用したストレージ技術、および超並列アーキテクチャにより結果が高速に得られます。
  • 制約。個々の列またはテーブル全体がチェックされます。BigQuery は NOT NULL 制約のみをサポートします。
  • MULTISET。Teradata で行の重複を許可するために使用されます。
  • CASESPECIFIC。文字データの比較や照合の際に文字が大文字か小文字かを指定します。

データ型

BigQuery は、Teradata よりも小さいデータ型セットをサポートしています。BigQuery では、Teradata の複数のデータ型が標準 SQL の 1 つのデータ型にマッピングされます。次に例を示します。

  • INTEGERSMALLINTBYTEINT、および BIGINT はすべて、INT64 にマッピングされます。
  • CLOBJSONXMLUDT などの大きい文字フィールドを含むデータ型は、STRING にマッピングされます。
  • BLOBBYTEVARBYTE などのバイナリ情報を含むデータ型は、BYTES にマッピングされます。

日付データ型については、Teradata と BigQuery の主要なデータ型(DATETIME、および TIMESTAMP)は同等です。しかし、Teradata のその他の特殊な日付データ型は次のようにマッピングする必要があります。

  • TIME_WITH_TIME_ZONETIME にマッピング。
  • TIMESTAMP_WITH_TIME_ZONETIMESTAMP にマッピング。
  • INTERVAL_HOURINTERVAL_MINUTE などの INTERVAL_* データ型は BigQuery の INT64 にマッピング。
  • PERIOD(DATE)PERIOD(TIME) などの PERIOD(*) データ型は STRING にマッピング。

BigQuery では、多次元配列の直接サポートはありません。代わりの方法として、各構造体がデータ型 ARRAY のフィールドを持つ構造体の配列を作成します。

SELECT ステートメント

SELECT ステートメントの構文は一般に、Teradata と BigQuery の間で互換性があります。このセクションでは、移行時に対処が必要となることが多い相違点について説明します。

識別子

BigQuery では、識別子としてプロジェクト、データセット、テーブルまたは図、列を使用できます。

サーバーレス プロダクトでは、BigQuery にはクラスタ、環境、または固定エンドポイントの概念がないため、データセットのリソース階層はプロジェクトで指定されます。

Teradata の SELECT ステートメントでは、完全修飾列名を使用できます。BigQuery は、常にテーブルまたはエイリアスから列名を参照し、プロジェクトやデータセットからは参照しません。

たとえば、BigQuery の ID に対応するオプションが用意されています。

暗黙的にテーブルから推測される列:

SELECT
 c
FROM
 project.dataset.table

明示的なテーブル参照を使用することもできます。

SELECT
 table.c
FROM
 project.dataset.table

明示的なテーブル エイリアスを使用することもできます。

SELECT
 t.c
FROM
 project.dataset.table t
エイリアス参照

Teradata の SELECT ステートメントでは、同じクエリ内でエイリアスを定義および参照できます。たとえば次のスニペットで flag は、列のエイリアスとして定義された直後に、CASE ステートメント内で参照されています。

SELECT
 F AS flag,
 CASE WHEN flag = 1 THEN ...

しかし、標準 SQL では同じクエリ内で列間を参照することは許容されていません。変換するには、ネストされたクエリという形のロジックに移行する必要があります。

SELECT
 q.*,
 CASE WHEN q.flag = 1 THEN ...
FROM (
 SELECT
   F AS flag,
   ...
) AS q

このサンプルのプレースホルダ F 自体を、単一の列を返すネストクエリにすることもできます。

LIKE によるフィルタリング

Teradata では、クエリ結果をフィルタして、結果を使用可能なオプションのセットに絞り込む場合に LIKE ANY 演算子を使用します。次に例を示します。

SELECT*
FROM t1
WHERE a LIKE ANY ('string1', 'string2')

この演算子を持つステートメントを標準 SQL に変換するには、ANY の後のリストを OR 述語で複数に分割します。

SELECT*
FROM t1
WHERE a LIKE 'string1' OR a LIKE 'string2'
QUALIFY 句

Teradata の QUALIFY 句は、SELECT ステートメント内の条件句です。ユーザーが指定する検索条件に従って、すでに計算されて順序付けられた分析関数のクエリ結果をフィルタします。その構文は、QUALIFY 句、それに続く ROW_NUMBERRANK などの分析関数、および検索する値から構成されます。

SELECT a, b
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) = 1

Teradata のユーザーは一般にこの関数を、サブクエリを追加することなく、結果にランクを付けて返すための簡潔な手段として使用しています。

QUALIFY 句を BigQuery に変換する際には、囲む側のクエリに WHERE 条件を追加します。

SELECT a, b
FROM (
 SELECT a, b,
 ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) row_num
 FROM t1
) WHERE row_num = 1

実際の例については、関連するクイックスタートをご覧ください。

データ操作言語

データ操作言語(DML)は、データベース内のデータの一覧表示、追加、削除、および変更に使用します。DML には、SELECTINSERTDELETE、および UPDATE ステートメントが含まれます。

これらのステートメントの基本形式は Teradata SQL と標準 SQL で同等ですが、Teradata には非標準句と特別なステートメント構成も含まれているので、移行時に変換が必要になります。次のセクションでは、最も一般的なステートメント、主な相違点、おすすめの変換方法を示します。

INSERT ステートメント

BigQuery は、オンライン分析処理(OLAP)に焦点を当てたエンタープライズ データ ウェアハウスです。多数の INSERT ステートメントを含むスクリプトを実行するなど、BigQuery をオンライン トランザクション処理(OLTP)システムのように扱って、具体的すぎる形で DML ステートメントを使用することは正しいアプローチではありません。

BigQuery DML ステートメントは一括更新を目的としているため、データを変更する各 DML ステートメントは暗黙的にトランザクションを起動します。不要なトランザクションのオーバーヘッドが発生しないように、可能な限り DML ステートメントをグループ化する必要があります。

たとえば、次のような Teradata の一連のステートメントがある場合、BigQuery でこれをそのまま実行するとアンチパターンになります。

INSERT INTO t1 (...) VALUES (...);
INSERT INTO t1 (...) VALUES (...);

上のスクリプトを単一の INSERT ステートメントに変換すれば、一括操作を実行できます。

INSERT INTO t1 VALUES (...), (...)

既存のテーブルからの新規テーブルの作成は、多数の INSERT ステートメントが使用される典型的なシナリオです。BigQuery では、新しいテーブルを作成して行を挿入する際に、複数の INSERT ステートメントを使用するのではなく CREATE TABLE ... AS SELECT ステートメントを使用して 1 つのオペレーションにまとめます。

UPDATE ステートメント

Teradata の UPDATE ステートメントは標準 SQL の UPDATE ステートメントと類似していますが、重要な違いとして次の点が挙げられます。

  • SET 句と FROM 句の順序が逆になる。
  • UPDATE ステートメント内でテーブルのエイリアスとして使用された Teradata の相関名は、すべて削除する必要がある。
  • 標準 SQL では必ず、各 UPDATE ステートメントに WHERE キーワードと条件文を含める必要がある。テーブル内のすべての行を更新するには、WHERE true とします。

次の例は、JOIN を使用する Teradata の UPDATE ステートメントを示しています。

UPDATE t1
FROM t1, t2
SET
 b = t2.b
WHERE a = t2.a;

標準 SQL における同等のステートメントは次のとおりです。

UPDATE t1
SET
 b = t2.b
FROM t2
WHERE a = t2.a;

この場合、前のセクションで述べた、BigQuery での多数の DML ステートメントの実行に関する考慮事項も適用されます。複数の UPDATE ステートメントではなく、単一の MERGE ステートメントを使用することをおすすめします。

DELETE ステートメント

標準 SQL では必ず、DELETE ステートメントに WHERE 句を含める必要があります。Teradata では、テーブル内のすべての行を削除する場合、WHERE 句は DELETE ステートメント内で必須ではありません(特定の行を削除する場合は Teradata の DML でも WHERE 句が必須になります)。変換の際には、不足の WHERE 句をスクリプトに追加する必要があります。この変更は、テーブル内のすべての行が削除される場合にのみ必要となります。

たとえば、Teradata SQL の次のステートメントでは、テーブルからすべての行を削除しています。ここで、ALL 句は省略できます。

DELETE t1 ALL;

標準 SQL に変換すると次のようになります。

DELETE FROM t1 WHERE TRUE;

ストアド プロシージャ

Teradata のストアド プロシージャは、SQL と制御ステートメントの組み合わせです。ストアド プロシージャでは、Teradata Database へのインターフェースをカスタマイズするためにパラメータを利用できます。

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

ただし、他の機能の方が適している場合もあります。利用できる代替手段は、対象となるストアド プロシージャの使用方法によって異なります。次に例を示します。

  • トリガーが定期的なクエリに使用される場合は、スケジュールされたクエリに置き換える。
  • クエリの複雑な実行と相互依存関係を制御するストアド プロシージャは、Cloud Composer で定義されるワークフローに置き換える。
  • ストアド プロシージャがデータ ウェアハウスに対する API として使用される場合は、パラメータ化されたクエリBigQuery API を使用してリファクタリングします。この変更では、Java や Go などの別のプログラミング言語を使用してストアド プロシージャのロジックを再構築してから、そのコードのパラメータを使用した SQL クエリを呼び出します。

ストアド プロシージャに存在するビジネス ロジックのリファクタリングと置換は、データ層とターゲット プラットフォームのアーキテクチャの双方のベスト プラクティスに関する専門知識を必要とするデリケートなタスクです。移行の複雑さに応じて、専門パートナーのサービス契約を選択することもできます。

移行中の変換

移行の一環として、Teradata SQL ステートメントとスクリプトを調べ、Teradata SQL ステートメントを BigQuery で使用される標準 SQL に変換する必要があるかどうかを判断する必要があります。反復的に移行を行うための全体的な推奨事項と同様に、体系的な方法でこのタスクに取り組むことをおすすめします。

ユースケースの選択

以前のチュートリアルではユースケースを、ビジネス バリューを達成するために必要なすべてのデータセット、データ処理、およびシステムとユーザーのインタラクションと定義しました。次の図に示すように、ユースケースには、データ ウェアハウス内のテーブルのグループ、それらのテーブルにデータを供給するアップストリーム プロセス、およびそれらのテーブルからのデータを消費するダウンストリーム プロセスが含まれます。

上流(オンプレミス)から従来のデータ ウェアハウスの下流プロセスへのユースケースのフロー。

アップストリーム データ パイプラインとも呼ばれるアップストリーム プロセスの例には、データレイク、OLTP システム、CRM、およびロギング アプリケーションからのフィードがあります。ダウンストリーム プロセスの例には、ダッシュボード、レポート、別のシステムへのフィード、ビジネス アプリケーション、アナリストが使用するアドホック クエリがあります。

移行のためのユースケースを選択する際には、ダウンストリーム プロセスの主要部分が内部レポートまたは明確に定義されたデータ出力(フィードや API など)であるユースケースを選択します。初期移行の繰り返しでこれらのタイプのユースケースを選択することには、いくつかの利点があります。

  • スタッフが変換作業に慣れること、およびその後の繰り返し時に必要となる労力を見積もることが可能になる。
  • スクリプトを使用して出力を比較できるため、データの正確性の自動テストを簡単に設定できる。
  • その場でのビジュアルな比較が可能になり、生データを出力するよりも技術的な知識のない一般関係者に説明を提示しやすくなります。

変換のステップ

ユースケースを Teradata から BigQuery に移行する際には、スキーマとデータ転送の概要ドキュメントの推奨事項に従ってください。テーブルを BigQuery に移行し、ダウンストリームまたはアップストリーム プロセスのいずれかを変更する必要がある場合は常に、変更に関係するクエリやストアド プロシージャの評価を行い、クエリやストアド プロシージャを変換する必要があるかどうかを判断する必要があります。

変換が必要な場合は、Teradata SQL の違いセクションと Teradata から BigQuery への SQL 変換リファレンスのガイドラインに従って、ISO SQL: 2011 に準拠する標準 SQL を使用して新しいクエリを作成します。

変換先のクエリを使用して、テストと継続的デプロイに関する自社のベスト プラクティスに従って、制御された環境でダウンストリームまたはアップストリーム プロセスをテストします。Google Cloud でソフトウェア リリース パイプラインを作成する方法もご覧ください。

データ ウェアハウスの移行の初期段階から始めて、変換に利用できるさまざまなアセットを作成することをおすすめします。たとえば、ユースケースに合わせて可能な限り共通のクエリ変換を実装するソフトウェア ライブラリの開発を検討してください。そのようなライブラリがあれば、後続する反復実行が容易になります。そうしたライブラリとドキュメント ガイドは、スタッフが標準 SQL に慣れるようにトレーニングしたり、BigQuery にはない Teradata 構文を使用するための最適なオプションを理解したりするために利用できます。

次のステップ