Teradata から BigQuery への移行: クエリ変換のクイックスタート

最終更新: 2020 年 9 月
リリースノート

このドキュメントは、Teradata から Google Cloud の BigQuery へのスキーマとデータの移行について説明するシリーズの一部です。このパートはクイックスタート（概念実証チュートリアル）として、各種の非標準 Teradata SQL ステートメントを BigQuery で使用できる標準 SQL に変換するプロセスを説明しています。

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

スキーマとデータ転送のクイックスタート
クエリ変換の概要
クエリ変換のクイックスタート（このドキュメント）
SQL 変換リファレンス

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

目標

クエリを Teradata SQL から標準 SQL に変換する。
単純なケースに対する手動アプローチを理解してから、テンプレートを使用した自動アプローチを理解する。
クエリのリファクタリングが必要となる、より複雑な変換について探索する。

費用

このクイックスタートでは、課金対象である次の Google Cloud コンポーネントを使用します。

BigQuery: このチュートリアルでは、1 GB 近くのデータを BigQuery に保存し、クエリの 1 回の実行あたり 2 GB 未満のデータを処理します。Google Cloud の無料枠の一部として、BigQuery では特定の上限まで無料でリソースを使用できるようになっています。これらの無料使用枠上限は、無料トライアル期間中だけでなく、期間終了後も適用されます。無料使用枠上限を超えた場合や無料トライアル期間が満了した場合は、BigQuery の料金ページに記載されている料金体系に沿って課金されます。

料金計算ツールを使用すると、予想使用量に基づいて費用の見積もりを作成できます。

始める前に

まず、スキーマとデータ転送のクイックスタートに従って、このクイックスタートで Teradata データベースと BigQuery の両方で必要になるスキーマとデータを作成します。このクイックスタートでは、上述のクイックスタートで使用するのと同じプロジェクトを使用します。
パソコンに Teradata BTEQ がインストールされていること、その BTEQ が Teradata データベースに接続できることを確認します。BTEQ ツールをインストールする必要がある場合は、Teradata ウェブサイトから、このツールを入手できます。BTEQ のインストール、構成、実行の詳細については、システム管理者にお問い合わせください。BTEQ に代わる方法または追加の方法として、次の方法があります。
- DBeaver などのグラフィカルインターフェースを備えたツールをインストールします。
- Teradata で提供している Python モジュールをインストールして、Teradata データベースとのやり取りをスクリプト化するために使用します。
パソコンに Jinja2 をインストールします（Python 環境内に存在していない場合）。Jinja2 は Python 用のテンプレートエンジンです。virtualenvwrapper などの環境マネージャーを使用して、Python 環境を隔離することをおすすめします。
BigQuery コンソールにアクセスできることを確認します。

はじめに

このクイックスタートでは、サンプルクエリを Teradata SQL から標準 SQL に変換して BigQuery で使用できるようにする手順を説明します。まず、単純な検索と置換による変換方法を説明します。次に、スクリプトを使用してクエリを自動的に再構築する方法を説明します。最後に、変換後のクエリで元のクエリと同じセマンティクスが維持されていることを確認するためにドメイン分野の専門家が関与しなければならないような、複雑な変換について説明します。

このクイックスタートは、Teradata SQL から標準 ISO:2011 SQL に準拠したクエリに変換するために実践的な経験を積みたいと思っているデータウェアハウス管理者、デベロッパー、データ使用者を対象としています。

演算子と関数の置換

Teradata SQL は ANSI / ISO SQL に準拠しているため、多くのクエリは最小限の変更で簡単に移行できます。一方、Teradata では非標準 SQL 拡張機能もサポートしています。Teradata で非標準の演算子と関数が使用されているだけの単純なケースでは、通常、検索と置換のプロセスでクエリを変換できます。

まず始めに、たとえば 1994 年の購入額が 10,000 ドルを超える顧客の数を調べるためのクエリを Teradata で実行します。

BTEQ がインストールされているパソコンで、Teradata BTEQ クライアントを開きます。
```
bteq
```
Teradata にログインします。teradata-ip と teradata-user は、実際の環境での対応する値に置き換えます。
```
.LOGON teradata-ip/teradata-user
```

BTEQ プロンプトが表示されたら、次の Teradata SQL クエリを実行します。

SELECT
 COUNT(DISTINCT(O_CUSTKEY)) AS num_customers
FROM
 tpch.orders
WHERE
 O_TOTALPRICE GT 10000
 AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994;

結果は次のようになります。

num_customers
-------------
        86101

同じクエリを、今度は BigQuery で実行します。

BigQuery コンソールに移動します。

BigQuery コンソールに移動
クエリをクエリエディタにコピーします。

演算子 GT（より大きい）は標準 SQL ではないため、クエリエディタに構文エラーのメッセージが表示されます。
GT を > 演算子で置き換えます。
[実行] をクリックします。

数値の結果は Teradata での結果と同じです。

スクリプトを使用して SQL 要素を検索して置換する

先ほど行った変更は小さいものなので、手動で簡単にできます。しかし、大規模な SQL スクリプトまたは多数の SQL スクリプトを処理する必要がある場合は、手動で検索して置換するのは手間がかかり、エラーの原因にもなりがちです。したがって、このタスクを自動化することをおすすめします。それをこのセクションで行います。

Cloud Console で Cloud Shell に移動します。

Cloud Shell に移動
テキストエディタで num-customers.sql という名前の新しいファイルを作成します。たとえば vi を使用する場合は、次のようにファイルを作成します。
```
vi num-customers.sql
```
新しく作成したファイルに、前のセクションで使用した SQL スクリプトをコピーします。
ファイルを保存して閉じます。
GT を > 演算子で置き換えます。
```
sed -i 's/GT/>/' num-customers.sql
```
ファイル内の GT が > に置き換えられていることを確認します。
```
cat num-customers.sql
```

使用した sed スクリプトを一連のファイルに一括で適用できます。また、このスクリプトはファイルごとに多数の置換を処理することもできます。

Cloud Shell で、テキストエディタを使用して num-customers.sql という名前のファイルを開きます。
```
vi num-customers.sql
```
ファイルの内容を次のスクリプトに置き換えます。
```
SELECT
 COUNT(DISTINCT(O_CUSTKEY)) AS num_customers
FROM
 tpch.orders
WHERE
 O_TOTALPRICE GT 10000
 AND O_ORDERPRIORITY EQ '1-URGENT'
 AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994;
```
このスクリプトは前のスクリプトとほぼ同じですが、急を要する注文だけを含めるための 1 行が追加されています。現時点で、SQL スクリプトには GT と EQ という非標準の SQL 演算子が 2 つ含まれています。
ファイルを保存して閉じます。
ファイルのコピーを 99 部作成します。
```
for i in {1..99}; do cp num-customers.sql "num-customers$i.sql"; done
```
このコマンドが完了すると、100 個のバージョンのスクリプトファイルが作成されています。
1 回のオペレーションで、すべてのファイル内の GT を > で置き換えます。
```
sed -i 's/GT/>/g;s/EQ/=/g' *.sql
```
100 個すべてのファイルを変更するスクリプトを使用したほうが、ファイルを 1 つずつ手動で変更するよりもはるかに効率的です。
GT 演算子が含まれているファイルを一覧表示します。
```
grep GT *.sql
```
GT 演算子はすべて > 演算子に置き換えられているため、コマンドは結果を返しません。
いずれかのファイルを選んで、演算子が標準 SQL での対応する演算子で置き換えられていることを確認します。
```
cat num-customers33.sql
```

この検索と置換のアプローチは、たとえば次の場合に適しています。

日付関数を次のように変更する場合:
- DATE + k から DATE_ADD
- TD_DAY_OF_MONTH から EXTRACT
- MONTHS_BETWEEN から DATE_DIFF
文字列関数を次のように変更する場合:
- CHARACTER_LENGTH から CHAR_LENGTH
- INDEX から STRPOS
- LEFT から SUBSTR
数学関数を次のように変更する場合:
- NULLIFZERO から NULLIF
- RANDOM から RAND
- ZEROIFNULL から IFNULL
SEL から SELECT のように略語を変更する場合

一般的な変換を網羅したリストについては、Teradata から BigQuery への SQL 変換リファレンスのドキュメントをご覧ください。

スクリプトを使用して SQL ステートメントとスクリプトを再構築する

これまでのところ、Teradata SQL と標準 SQL の間で 1 対 1 でマッピングされる演算子と関数の置換のみを自動化してきましたが、非標準の関数では SQL 要素の変換がより複雑になってきます。変換スクリプトではキーワードを置換するだけでなく、引数、かっこ、別の関数の呼び出しといった要素も追加または移動しなければならないためです。

このセクションでは、Teradata で、毎月月末にクライアントのグループ別の月間注文金額のうち最も大きい金額を検索するクエリを実行します。

BTEQ がインストールされているパソコンで、BTEQ コマンドプロンプトに切り替えるか、このプロンプトを開きます。BTEQ を閉じた場合は、次のコマンドを実行します。
```
bteq
```
BTEQ プロンプトが表示されたら、次の Teradata SQL クエリを実行します。
```
SELECT
 O_CUSTKEY,
 SUM(O_TOTALPRICE) as total,
 TD_MONTH_END(O_ORDERDATE) as month_end
FROM
 tpch.orders
WHERE
 O_CUSTKEY < 5
GROUP BY
 O_CUSTKEY,
 month_end
ORDER BY
 total DESC;
```
このクエリでは、非標準の TD_MONTH_END Teradata 関数を使用して、注文日に最も近い月末の日付を取得します。たとえば、注文日が 1996-05-16 の場合、TD_MONTH_END は 1996-05-31 を返します。この関数は日付引数を 1 つ取ります。つまり、注文日です。所定の月の所定の顧客についての合計金額を取得するために、結果は月末の日付と顧客キーごとにグループ化されます。

結果は次のようになります。
```
O_CUSTKEY              total  month_end
-----------  -----------------  ---------
         4          379593.37   96/06/30
         4          323004.15   96/08/31
         2          312692.22   97/02/28
         4          311722.87   92/04/30
```

同じ結果を返すクエリを BigQuery で実行するには、非標準の TD_MONTH_END 関数を標準 SQL での対応する関数に置き換える必要がありますが、この関数には 1 対 1 のマッピングがありません。したがって、Jinja2 テンプレートを使用してこのタスクを処理する関数を作成します。

Cloud Shell で、month-end.jinja2 という名前の新しいファイルを作成します。
```
vi month-end.jinja2
```
新しく作成したファイルに、次の SQL スニペットをコピーします。
```
DATE_SUB(
   DATE_TRUNC(
     DATE_ADD(
       {{ date }},
       INTERVAL 1 MONTH
     ),
     MONTH
   ),
   INTERVAL 1 DAY
)
```
このファイルが Jinja2 テンプレートであり、標準 SQL での TD_MONTH_END に対応する関数を表すものです。このファイル内の {{ date }} という名前のプレースホルダは、日付関数（この例の場合は O_ORDERDATE）に置き換えられます。
ファイルを保存して閉じます。
translate-query.py という名前の新しいファイルを作成します。
```
translate-query.py
```
新しく作成したファイルに、次の Python スクリプトをコピーします。
```
"""Translates a sample using a template."""

import re
from jinja2 import Environment
from jinja2 import PackageLoader

env = Environment(loader=PackageLoader('translate-query', '.'))
regex = re.compile(r'(.*)TD_MONTH_END$([A-Z_]+)$(.*)')

with open('month-end.td.sql', 'r') as td_sql:
 with open('month-end.sql', 'w') as std_sql:
   for line in td_sql:
     match = regex.search(line)
     if  match:
       argument = match.group(2)
       template = env.get_template('month-end.jinja2')
       std_sql.write(match.group(1) + template.render(date=argument) \
         + match.group(3) + '\n')
     else:
       std_sql.write(line)
```
この Python スクリプトは、前に作成したファイル（month-end.td.sql）を開き、そこから Teradata SQL を入力として読み取って、それを標準 SQL スクリプトに変換してから month-end.sql ファイルに書き込みます。

以下の点にご注意ください。
- このスクリプトは、入力ファイルから読み取った各行と正規表現 (.*)TD_MONTH_END$([A-Z_]+)$(.*) を照合します。この正規表現によって TD_MONTH_END を見つけて、3 つのグループをキャプチャします。
  1. 関数の前にある任意の文字 (.*) は group(1) としてキャプチャします。
  2. TD_MONTH_END 関数に送信された引数 ([A-Z_]+) は group(2) としてキャプチャします。
  3. 関数の後にある任意の文字 (.*) は group(3) としてキャプチャします。
- 一致が見つかった場合、スクリプトは Jinja2 テンプレート（前のステップで作成した month-end.jinja2）を取得してから、出力ファイルに以下の要素を次の順番で書き込みます。
  1. group(1) で表された文字。
  2. date プレースホルダが Teradata SQL で検出された元の引数 O_ORDERDATE で置換された状態のテンプレート。
  3. group(3) で表された文字。
ファイルを保存して閉じます。
Python スクリプトを実行します。
```
python translate-query.py
```
month-end.sql という名前のファイルが作成されます。
作成されたファイルの内容を表示します。
```
cat month-end.sql
```
このコマンドにより、スクリプトによって標準 SQL に変換されたクエリが表示されます。
```
SELECT
 O_CUSTKEY,
 SUM(O_TOTALPRICE) as total,
 DATE_SUB(
   DATE_TRUNC(
     DATE_ADD(
       O_ORDERDATE,
       INTERVAL 1 MONTH
     ),
     MONTH
   ),
   INTERVAL 1 DAY
) as month_end
FROM
 tpch.orders
WHERE
 O_CUSTKEY < 5
GROUP BY
 O_CUSTKEY,
 month_end
ORDER BY
 total DESC;
```
TD_MONTH_END 関数は出現しなくなりました。この関数は、テンプレートとテンプレート内の適切な位置にある日付引数 O_ORDERDATE に置き換えられています。

この Python スクリプトはすでに、外部 Jinja2 ファイルのテンプレートを使用しています。正規表現にも、これと同じアプローチを適用できます。つまり、ファイルまたは Key-Value ストアから正規表現を読み込むことができます。こうすれば、任意の正規表現とそれに対応する変換テンプレートを処理するスクリプトを生成できます。

最後に、生成されたスクリプトを BigQuery で実行して、その結果が Teradata で返された結果と一致することを確認します。

BigQuery コンソールに移動します。

BigQuery コンソールに移動
前に使用したクエリをクエリエディタにコピーします。
[実行] をクリックします。

結果は Teradata での結果と同じです。

クエリ変換作業をスケールアップする

移行中に、ツールを使用した一連の変換（前に使用したサンプルスクリプトなど）を適用するには、熟練した担当者グループからのサポートが必要です。こうしたスクリプトは移行作業が進むにつれて進化していきます。そのため、スクリプトにソース管理を適用することを強くおすすめします。スクリプトの実行結果は、細心の注意を払ってテストする必要があります。

移行中にサポートを受けるには、Google の Professional Services Organization と Google のパートナーの連絡窓口となる Google のセールスチームにお問い合わせください。

クエリのリファクタリング

前のセクションでは、スクリプトを使用して、Teradata SQL の演算子を検索し、標準 SQL での対応する演算子に置換しました。また、テンプレートを利用して、限定されたクエリの自動再構築も行いました。

一部の Teradata SQL 機能を変換するには、掘り下げた SQL クエリのリファクタリングが必要になります。このセクションでは、2 つの例を調べます。QUALIFY 句を変換する例と、列の相互参照を変換する例です。

このセクションでの例は、手作業でリファクタリングします。実際には、より複雑なリファクタリングは自動化の候補になります。ただし、各種のケースをそれぞれ解析する作業には複雑さが伴うため、自動化してもその報いはない可能性があります。さらに、スクリプトを自動化すると、クエリのセマンティクスを維持する最適なソリューションを見逃す可能性もあります。

QUALIFY 句

Teradata の QUALIFY は、SELECT ステートメントで、計算済みの順序付き分析関数の結果をフィルタリングするために使用される条件句です。順序付き分析関数は一定の範囲の行を処理し、行ごとに結果を生成します。Teradata のユーザーは一般にこの関数を、サブクエリを追加することなく、結果にランクを付けて返すための簡潔な手段として使用しています。

たとえば QUALIFY 句を使用して、顧客ごとに、1994 年に行った注文の中で最も高額の注文を選択できます。

BTEQ がインストールされているパソコンで、BTEQ コマンドプロンプトに切り替えるか、このプロンプトを開きます。BTEQ を閉じた場合は、次のコマンドを実行します。
```
bteq
```
BTEQ プロンプトが表示されたら、次の Teradata SQL クエリをコピーします。
```
SELECT
 O_CUSTKEY,
 O_TOTALPRICE
FROM
 tpch.orders
QUALIFY ROW_NUMBER() OVER (
 PARTITION BY O_CUSTKEY
 ORDER BY O_TOTALPRICE DESC
) = 1
WHERE
 EXTRACT(YEAR FROM O_ORDERDATE) = 1994 AND
 (O_CUSTKEY MOD 10000) = 0;
```
このクエリについては、次の点にご注意ください。
- このクエリはパーティション別に行を分類します。各パーティションは 1 つの顧客キー（PARTITION BY O_CUSTKEY）に対応しています。
- QUALIFY 句は行をフィルタリングして、各パーティションの最初の行（ROW_NUMBER()=1）だけに絞り込みます。
- 各パーティション内の行は、合計注文価格を基準に降順（ORDER BY O_TOTALPRICE DESC）で並んでいるため、最初の行は最も高い注文価格の行に相当します。
- SELECT ステートメントは顧客キーと合計注文価格（O_CUSTKEY、O_TOTALPRICE）をフェッチし、WHERE 句を使用してさらに結果をフィルタリングして、1994 年に行われた注文だけに絞り込みます。
- 剰余演算子（MOD）は、表示目的で行のサブセットのみをフェッチします。このサンプリング方法が SAMPLE 句よりも優先されます。SAMPLE はランダム化されるため、結果を BigQuery と比較できないためからです。

クエリを実行します。

結果は次のようになります。

 O_CUSTKEY       O_TOTALPRICE
-----------  -----------------
     10000          182742.02
     20000           56470.00
     40000          211502.51
     50000           81584.54
     70000           53131.09
     80000           15902.64
    100000          306639.29
    130000          183113.29
    140000          250958.13

2 番目の列は、最初の列でサンプリングされた顧客キーに関する 1994 年の最大合計注文金額です。

同じクエリを BigQuery で実行するには、ANSI / ISO SQL に準拠するように SQL スクリプトを変換する必要があります。

BigQuery コンソールに移動します。

BigQuery コンソールに移動
クエリエディタに、変換後の次のクエリをコピーします。
```
SELECT
 O_CUSTKEY,
 O_TOTALPRICE
FROM (
 SELECT
   O_CUSTKEY,
   O_TOTALPRICE,
   ROW_NUMBER() OVER (
   PARTITION BY O_CUSTKEY
   ORDER BY O_TOTALPRICE DESC
   ) as row_num
 FROM
   tpch.orders
 WHERE
   EXTRACT(YEAR FROM O_ORDERDATE) = 1994 AND
   MOD(O_CUSTKEY, 10000) = 0
 )
WHERE row_num = 1
```
この新しいクエリにはいくつかの変更点がありますが、いずれも単純な検索と置換では不可能なものです。次の点にご注意ください。
- QUALIFY 句は削除されています。分析関数 ROW_NUMBER() は列として SELECT ステートメント内に移動され、別名（as row_num）が割り当てられています。
- 全体を囲むクエリが存在しない場合は作成され、分析値のフィルタ（row_num = 1）を設定した WHERE 条件が追加されます。
- Teradata の演算子 MOD も非標準であるため、MOD() 関数で置き換えられています。
[実行] をクリックします。

列型の結果は、Teradata での結果と同じです。

列の相互参照

Teradata では、同じクエリ内に定義された列間の相互参照をサポートしています。このセクションでは、別名をネストされた SELECT ステートメントに割り当て、その別名を CASE 式で参照するクエリを使用します。

たとえば、所定の年に顧客がアクティブであったかどうかを判別するクエリを実行できます。その年に 1 回でも注文を行った顧客は、アクティブな顧客として判別されます。

BTEQ がインストールされているパソコンで、BTEQ コマンドプロンプトに切り替えるか、このプロンプトを開きます。BTEQ を閉じた場合は、次のコマンドを実行します。
```
bteq
```
BTEQ プロンプトが表示されたら、次の Teradata SQL クエリをコピーします。
```
SELECT
 (
   SELECT
     COUNT(O_CUSTKEY)
   FROM
     tpch.orders
   WHERE
     O_CUSTKEY = 2
     AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994
 ) AS num_orders,
 CASE
   WHEN num_orders = 0 THEN 'INACTIVE'
   ELSE 'ACTIVE'
 END AS status;
```
このクエリについては、次の点に注意してください。
- クエリ内には、1994 年に行われた注文に顧客キー（2）が出現した回数をカウントするクエリがネストされています。このクエリの結果が最初の列に返されて、別名 num_orders が割り当てられます。
- CASE 式は、見つかった注文回数がゼロ以外の場合、2 番目の列に ACTIVE を出力します。そうでない場合は INACTIVE を出力します。CASE 式は内部で、同じクエリの最初の列（num_orders）の別名を使用します。
クエリを実行します。

結果は次のようになります。
```
 num_orders  status
-----------  --------
          3  ACTIVE
```

同じクエリを BigQuery で実行するには、同じクエリ内の列間の相互参照を排除しなければなりません。

BigQuery コンソールに移動します。

BigQuery コンソールに移動
クエリエディタに、変換後の次のクエリをコピーします。
```
SELECT
 customer.num_orders,
 CASE
   WHEN customer.num_orders = 0 THEN 'INACTIVE'
   ELSE 'ACTIVE'
 END AS status
FROM
 (
   SELECT
     COUNT(O_CUSTKEY) AS num_orders
   FROM
     tpch.orders
   WHERE
     O_CUSTKEY = 2
     AND EXTRACT(YEAR FROM O_ORDERDATE) = 1994
 ) customer;
```
元のクエリからの変更点は次のとおりです。
- ネストされたクエリは、全体を囲むクエリの FROM 句に移動されて、別名 customer が割り当てられていますが、この別名によって出力列が定義されることはありません。この別名は SELECT 句ではなく、FROM 句の中にあるためです。
- SELECT 句には 2 つの列があります。
  - 最初の列は、ネストされたクエリ（customer）で定義された注文回数（num_orders）を出力します。
  - 2 番目の列には CASE ステートメントが含まれていて、このステートメントはネストされたクエリで定義された注文回数を参照します。
[実行] をクリックします。

列型の結果は、Teradata での結果と同じです。

クリーンアップ

このチュートリアルで使用したリソースに対する Google Cloud アカウントへの課金を回避するために、リソースを削除してください。

プロジェクトの削除

課金を発生させないようにする最も簡単な方法は、このチュートリアルで作成したプロジェクトを削除することです。

Cloud Console で [リソースの管理] ページに移動します。
[リソースの管理] に移動
プロジェクトリストで、削除するプロジェクトを選択し、[削除] をクリックします。
ダイアログでプロジェクト ID を入力し、[シャットダウン] をクリックしてプロジェクトを削除します。

次のステップ

Teradata から BigQuery への SQL 変換リファレンスで、Teradata SQL と、BigQuery で使用される標準 SQL の違いとマッピングの詳細を確認する。
このシリーズの次のパートであるデータガバナンスに進む。
Google の Professional Services Organization のサービスと、移行パスをサポートできる会社からなる充実したパートナーエコシステムを確認する。
Google Cloud のその他の機能を試す。チュートリアルをご覧ください。