SQL Server 2022 では、Parquet ファイルの取り扱いの柔軟性と Delta Lake サポートが追加されています。
SQLBits 2023 の Bob Ward の 「SQL Server 2022 hidden gems」というセッションの中で、SQL Server 2022 の CETAS を使用してオブジェクトストレージに Parquet ファイルをエクスポートすることでテーブルのアーカイブを行うというシナリオの説明があり、このあたりの動作を一度まとめておきたいと思い、本投稿でまとめてみました。
Contents
ファイルフォーマット
Parquet ファイル
Delta Lake もデータの格納については、Prquet ファイルが使用されており、Parquet / Delta Lake のどちらも基本的なデータフォーマットとして、Parquet ファイルのフォーマットを把握しておく必要があります。
Parquet ファイルのフォーマットについては、以下の情報から確認することができます。
Parquet ファイルは列指向のファイルとなり、ファイルフォーマット としては次のようになります。
SQL Server の 列ストアインデックス の基本的な考え方が踏襲できるかと思います。
特徴的なものとしてはフッター部にファイルメタデータとして列情報が格納されており、ファイル単体でスキーマの情報が内包されていることでしょうか。
列ストアインデックスのデルタストアの領域はないため、Parquet ファイルのデータに対して直接変更はすることができないようです。
Parquet ファイルについてはオープンフォーマットですので、いくつかのビューワーが提供されています。
「1.」の ParquetViewer であれば、ファイル内のメタデータの情報も確認ができ便利かと思います。 
Delta Lake (Delta テーブル)
従来型の Parquet ファイルをストレージに格納しデータレイクとする方法は、大量データの検索は効率的に実施できますが、ACID トランザクションに対しての対応は弱く、格納済みデータに対しての変更や、特定タイミングでのデータ検索については効率が悪いものとなっていました。
そこで、Parquet ファイルを格納しているストレージに対して、トランザクションに対応したワークロードを可能とするために Delta Lake というオープンソースのストレージレイヤーが Databricks から開発されました。
上記のドキュメントは Azure Databricks の Delta Lake の説明となりますが、Delta Lake は OSS で開発されているストレージレイヤー となりますので、Azure 以外でも様々なドキュメントで確認できます。
既存の Parquet ファイルを格納しているストレージを Delta Lake に変換することもできます。
Azure Synapse Analytics でサーバーレス SQL プールを使用して Delta Lake ファイルのクエリを実行する で解説されている convertToDelta を使用することで、Delta Lake 対応の形式に変換することができます。
Delta Lake では「_delta_log」というディレクトリがトランザクションログ (DeltaLog) となり、ストレージに格納されているデータに対してトランザクションのワークロードを提供可能としています。
トランザクションログについては、Delta Lake を深堀り：トランザクションログの解析 がわかりやすかったです。
Delta Lake でもデータの格納は Parquet ファイルがベースとなります。Parquet ファイルは「列圧縮済みデータ」のため、すでに格納されているデータに対して、直接の変更は行うことはできません。
しかし Delta Lake ストレージとして使用する場合には、トランザクションがサポートされ、Table deletes, updates, and merges というような様々なトランザクションがサポートされるようになります。
Azure Databricks のドキュメントでも、通常の RDBMS の DML のドキュメントが公開されていますね。
基本的には Parquet ファイルに対して直接の変更はできないので、データが変更された新しい Parquet ファイルの作成が行われます。元のデータが含まれていた Parquet ファイルもそのまま残っていますので、
- データ変更前の Parquet ファイル
- データ変更後の Parquet ファイル
の 2 種類がストレージ上に存在した状態になり、そのまま Parquet ファイルを読み込んでしまうと、一部のデータが重複して読み込まれてしまいます。
「どの Parquet ファイルを読むことで一貫性のあるデータアクセスを提供できるか」を制御しているのが DeltaLog となり、Delta Lake に対応したデータアクセスを実施するためには、DeltaLog の内容を基にして一貫性のある読み取りを提供できるようにする必要があります。(単純に Parquet ファイルを読むのではなく、DeltaLog のチェックポイント / トランザクションログを解析し、必要となる Parquet ファイルのみ読み込めるようにするのが Delta Lake のサポートとなる)
SQL Server 2022 からのアクセス
サポートされるオブジェクトストレージ
SQL Server 2022 では、PolyBase のオブジェクトストレージアクセスの機能が強化されており、次のオブジェクトストレージのデータアクセス (ならびにバックアップストレージとしての利用) がサポートさるようになりました。
- Azure ストレージ / Azure Data Lake ストレージ Gen2 (ADL Gen2)
- Azure Blob Storage 上の外部データにアクセスするように PolyBase を構成する
- 以前の SQL Server でも Azure ストレージへのアクセスはサポートしていましたが、2022 になり再設計が行われています
- 接続時のプレフィックスが abs / adls に変更されています
- S3 ならびに S3 互換ストレージ
SQL Server 2022 では、これらのストレージに格納されている Parquet ファイル / Delta Lake テーブルに対して、T-SQL からアクセスができるようになりました。オブジェクトストレージに対して、SQL Server をクエリエンジンとして活用するシナリオを実現することができるようになります。
サポートされるフォーマット
SQL Server からオブジェクトストレージのデータにアクセスする際には、CREATE EXTERNAL FILE FORMAT でアクセス対象のデータのフォーマットを登録する必要がありま鵜sが、SQL Server 2022 では、次のフォーマットがサポートされています。
- 区切テキスト
- Parquet
- Delta テーブル
サポートされるアクセス方法
サポートされるオブジェクトストレージ / フォーマットについての情報はここまでで確認できました。これらの対象に対してどのような操作が実施できるかは、PolyBase によるデータ仮想化の概要 に記載されています。
SQL Server 2022 では、オブジェクトストレージ上の「データ読み取り」だけでなく「データ書き込み」もサポートされています。
データの書き込みについては、CREATE EXTERNAL TABLE AS SELECT (CETAS) で実行することができ、外部テーブルを作成する際に SELECT を実行することで、指定したオブジェクトストレージのロケーション (ディレクトリ) にデータをエクスポートすることができます。
書き込みはファイルの格納効率を考慮すると、Parquet ファイルでエクスポートすることが多いかもしれませんね。
データを parquet としてエクスポートして CREATE EXTERNAL TABLE AS SELECT を使う に記載されているのですが、「現在、Delta の形式は読み取り専用としてのみサポートされています。」という制限があります。現状 Delta フォーマットについては、読み取りのみがサポートされており、Delta テーブルでの書き込みはサポートされていません。
また、Delta テーブルでは、タイムトラベル の機能が提供されていますが、試してみた限りでは、SQL Server からのアクセスについてはTIMESTAMP AS OF が指定できないため、「最新のデータ」のみのアクセスとなるかと思います。
文字列データ型の取り扱いの注意点
CREATE EXTERNAL FILE FORMAT にも記載されていますが、基本的に外部ファイルに対してのエンコードについては、 UTF8 になっていると考えたほうが良いかと思います。
SQL Server の PolyBase では、UTF16 でエンコードされたファイルの読み取りはサポートされていません。
SQL Server からデータをエクスポートする際に、文字コードを意識する必要があるのですが、基本的な対応としては次のようになるかと。
- nchar / nvarchar のデータエクスポート
- 自動的に UTF8 に変換されているため意識する必要はない
- varchar / nvarchar のデータエクスポート
- CETAS で SELECT をする際に、「COLLATE Japanese_XJIS_140_CI_AS_UTF8」のような UTF8 サポートの照合順序を指定してエクスポートする
Parquet ファイルの文字列データについては、UTF8 のエンコードが基本となっているかと思いますので、SQL Server からデータをエクスポートする際にも UTF8 を意識しておくとよいかと。
複数ファイルでのエクスポート
SQL Server 内のデータを CETAS でエクスポートする場合、並列クエリとして実行されている場合は、複数の Parquet ファイルとしてエクスポートが行われるようです。
単一の CTEAS でエクスポートされるファイル数を明示的に制御することは難しそうですが、並列クエリとして実行された結果については、複数ファイルとしてエクスポートが行われます。(MAXDOP 1 で実行すれば単一ファイルを強制することはできそうです)
まとめ
SQL Server 2022 ではオブジェクトストレージアクセスの機能が強化されており、SQL Server をオブジェクトストレージに格納されている Parquet ファイル / Delta テーブルに対してのクエリエンジンとして使用することができるようになりました。
単純な SELECT だけでなく、CETAS を使用してオブジェクトストレージに Parquet ファイルをエクスポートして、データをアーカイブ / 他の分析基盤用のデータを生成するということも可能です。
Synapse Analytics の Serverless SQL プールのような使い方を SQL Server 2022 を使用して実現することができるようになりますが、SQL Server では ファイルのメタデータ を使用することができないので、オブジェクトストレージ上のディレクトリ構成や、パーティション構成は、パフォーマンスを意識すると重要となってくるかもしれませんね。