🍮

BigQuery の SQL Translator の YAML 構成ファイルを試してみた

2024/01/22に公開

SQL

Google Cloud

BigQuery

tech

こんにちは！クラウドエース株式会社データソリューション部所属の福家です。

クラウドエースのデータソリューション部は、クラウドエースの IT エンジニアリングを担うシステム開発統括部の中で、特にデータ基盤構築・分析基盤構築からデータ分析までを含む一貫したデータ課題の解決を専門としています。

データソリューション部では活動の一環として、毎週 Google Cloud の新規リリースを調査・発表し、データ領域のプロダクトのキャッチアップをしています。その中でも重要と考えるリリースを本ページ含め記事として公開しています。

今回紹介するリリースは、2023/11/30 にプレビュー段階から一般提供（GA）となった
BigQuery の YAML 構成ファイルを使用した SQL Translator についてです。

はじめに

BigQuery とは

BigQuery はビッグデータの蓄積、分析ができるデータウェアハウスで、Google Cloud を代表するプロダクトです。

機械学習技術などの発展によって、ビッグデータの管理や分析の需要が高まっています。
BigQuery は、数あるデータウェアハウス製品の中でも、処理性能・使い勝手・コストなど多くの点で優れており、
オンプレミスや他のクラウド環境上のデータウェアハウスを BigQuery に移行したい という話はよく聞かれます。

BigQuery への移行をサポートするサービスとして、BigQuery Migration Service があります。

BigQuery Migration Service とは

BigQuery Migration Service は、オンプレミスや他のクラウド環境上のデータウェアハウスを BigQuery に移行するにあたって、さまざまな役立つツールをまとめたサービスです。

データ転送のためのサービス（BigQuery Data Transfer Service）や、 SQL を変換するためのツール（SQL Translator）、移行の計画・評価を手助けするツールなどが含まれています。

SQL Translator について

SQL Translator とは

SQL Translator^[1] は、さまざまなデータベースシステムの SQL 言語（方言）を
BigQuery 用の SQL（GoogleSQL^[2]）に変換してくれるツールです。

2024 年 1 月の時点では、

4 つの SQL 言語（Amazon Redshift など）で一般提供（GA）
9 つの SQL 言語（Azure Synapse, MySQL, PostgreSQL など）でプレビュー段階

となっています。

SQL Translator の種類

SQL Translator には下記の 2 種類があります。

バッチ SQL Translator
インタラクティブ SQL Translator

バッチ SQL Translator を使うと、Cloud Storage バケットにアップロードした複数の SQL ファイルを一括で変換することができます。

一方で、インタラクティブ SQL Translator では、コンソール画面を使って対話的に SQL を変換することができます。

インタラクティブ SQL Translator を試してみた

SQL Translator を実際に試してみます。
今回は、変換の結果をわかりやすく表示するために、インタラクティブ SQL Translator を使います。

interactive sql translator 画面

上の図は、インタラクティブ SQL Translator の画面です。
BigQuery のコンソール画面の SQL 入力画面の「展開」をクリックし、
「SQL 変換を有効にする」をクリックすると開くことができます。

変換元の SQL 言語を選択した上で、左側に変換したい SQL を入力し、
左上の「変換」ボタンをクリックすると、右側に変換後の SQL が表示されます。

今回は Redshift の SQL の変換を試しました。
Redshift では、文字列型は「VARCHAR」、タイムゾーンを持たない日時型は「TIMESTAMP」と表されますが^[3]、
それぞれ GoogleSQL で対応する「STRING」型と「DATETIME」型に変換されました。

注意

公式ドキュメントに下記の記載がありました（2024 年 1 月現在）。

重要: 変換はベストエフォートで行われます。変換が成功するかどうかは、ソーススクリプトの SQL ステートメントの一意性と複雑さに応じて変わります。スクリプトの中には、手動で変換する必要があるスクリプトがあります。

SQL Translator は便利なツールですが、現時点では、入力した SQL を 100 % 正確に変換してくれるものではなさそうです。
そのため、SQL Translator を既存システムの移行に用いる際には、変換後の SQL をある程度チェックすることが必要になりそうです。

YAML 構成ファイルの使用

SQL Translator の YAML 構成ファイル を用いると、SQL の変換をより詳細にカスタマイズすることができます。
YAML 構成ファイルは、バッチ SQL Translator・インタラクティブ SQL Translator どちらでも利用可能です。

具体的にどのようなカスタマイズができるのかについて、公式ドキュメントにいくつか例が記載されています。
2024 年 1 月現在ではプレビュー段階のものが多いのですが、下記のようなカスタマイズの例などが記載されていました。

テーブルのパーティション・クラスタリングを設定する
オブジェクト名の大文字・小文字を変更する（プレビュー）
テーブルを一時テーブルに変更する（プレビュー）
カラム（属性）のデータ型を変更する（プレビュー）

YAML 構成ファイルを試してみた

今回は下記の 2 つについて、公式ドキュメントの例をベースに試してみようと思います。

テーブルのパーティション設定を追加する
大文字・小文字を変更する

なお、検証は引き続きインタラクティブ SQL Translator を使って実施します。
（実際に運用するときは、バッチ SQL Translator を使って複数の SQL ファイルを一括でカスタマイズする方がより効果が得られそうです。）

テーブルのパーティション設定を追加する

下記の SQL の変換を試してみます。

-- Redshift SQL
CREATE TABLE test_table2
(
  start_date DATE,
  name VARCHAR
)
;

テーブルを作成する SQL です。
ここで、テーブルを作成するだけでなく、テーブルにパーティションの設定を追加するようにしてみます。

下記の YAML ファイルを用意しました。

demo.config.yaml

type: object_rewriter
relation:
  - match:
      relation: test_table2
    partition:
      simple:
        add: [ start_date ]

後ろの 3 行で、「start_date」という名前のカラムに対してパーティションを設定する、ということを表しています。

なお、YAML ファイルの名前には最後に .config.yaml を含める必要があります。

作成した YAML ファイルは Cloud Storage バケットへアップロードします。
インタラクティブ SQL Translator の画面の「展開」 → 「変換設定」をクリックし、
YAML ファイルをアップロードした Cloud Storage バケットを指定します。

変換設定画面

「変換設定」画面は上のような画面です。
赤枠部分に Cloud Storage バケットを指定します。
（2024/01/22 時点では、(preview) の記載が残っているようですが GA で利用可能です。）

YAML ファイルの設定が完了したら、SQL の変換を試してみます。

partition 追加

「ログメッセージ」にアップロードした YAML ファイルを検知したことを示すログが表示され、
変換後の SQL に、パーティション設定のための式が追加されました。

大文字・小文字を変更する

次は、下記の SQL の変換を試します。

-- Redshift SQL
CREATE TABLE TEST_TABLE3
(
  ID INTEGER,
  NAME VARCHAR
)
;

テーブル名・カラム名が両方「大文字」で表されています。
これを、「カラム名だけ小文字」にするように設定してみます。

demo.config.yaml

type: object_rewriter
global:
  case:
    relation: UPPERCASE # テーブル名は大文字
    attribute: LOWERCASE # カラム名は小文字

上記の YAML ファイルを作成し、SQL の変換を試してみます。

大文字小文字変換

このように、「カラム名だけ小文字」に変換することができました！
（2024/01/22 時点では、YAML ファイルでのカスタマイズを特に行わなかった場合、テーブル名・カラム名はどちらも小文字に変更されました。）

まとめ

今回は、YAML 構成ファイルを使用した SQL Translator を紹介しました。
SQL の移行をより詳細にカスタマイズする際に利用できそうです。
2024 年 1 月段階ではプレビュー段階の機能が多かったので、今後の機能の充実に期待です。

また、SQL Translator を実際に触ったのは今回が初めてだったのですが、簡単に使えて便利なツールだなと感じました！
BigQuery への移行のためだけでなく、GoogleSQL 以外の SQL 言語に馴染みのある開発者・分析者が BigQuery の SQL 構文を新しく学びたい、というような時にも役に立つかもしれないと思いました。

脚注

日本語版のドキュメントでは「SQL 変換」と書かれていることもありました。 ↩︎
以前は「標準 SQL」と呼ばれていました。 ↩︎
Redshift SQL と GoogleSQL の相違点は公式ドキュメントに詳しい説明があります。 ↩︎