How to Identity Columns to Generate Surrogate Keys in the Databricks Lakehouse - The Databricks Blogの翻訳です。
本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
Identity列とは何ですか？
Identity列は、新規のデータレコードに対してユニークなIDナンバーを自動で生成するデータベースの列です。この数は行の中身には関係がありません。
Identity列はサロゲートキーの一形態です。データウェアハウスにおいては、時間と共に変化していくデータを追跡するために、それぞれの行を一位に識別するためにサロゲートキーと呼ばれる追加のキーを用いることが一般的です。さらに、自然なキーよりもサロゲートキーを用いることが推奨されています。サロゲートキーはシステムによって生成され、行の一意性を特定するために複数のフィールドに依存しません。
このため、データウェアハウスとデータマートにおけるディメンジョナルモデルにおいて、主キーと外部キーとして利用されるサロゲートキーを作成するためにIdentity列が活用されます。以下に示しているように、これらのキーはスタースキーマのような従来のディメンジョナルモデルにおいて、別々のテーブルを接続するカラムとなっています。
スタースキーマの例
データレイクでサロゲートキーを生成する従来のアプローチ
多くのビッグデータ技術はパフォーマンスを改善するために、並列度を活用するか、同時に計算を行えるようにタスクを小さなものに分割する機能を活用しています。データレイクの初期段階においては、マシンのグループに対してユニークな連番を作成することは容易ではありませんでした。このため、何人かのデータエンジニアは以下のように、適切な機能なしにサロゲートキーを生成する若干信頼性に欠ける方法を用いることになりました。
- monotonically_increasing_id()
- row_number()
- Rank OVER
- ZipWithIndex()
- ZipWithUniqueIndex()
- Row Hash with hash()
- Row Hash with md5()
特定の環境下ではこれらの関数はうまく動作することができましたが、多くの場合、欠けた連番、パフォーマンスの問題、同時トランザクションの問題に関する数多くの警告や忠告に恐れ慄くことになりました。
データベースは初期段階から、集中管理されたトランザクションマネージャの助けを借りて、ユニークなデータ行を特定するためのサロゲートキーを作成するためのシーケンスを生成することができました。しかし、典型的な実装においては、管理が困難となり得るロックやトランザクションのコミットを必要となります。
Delta LakeのIdentity列によってサロゲートキーを容易に生成できます
Identity列は上述の問題を解決し、サロゲートキーを生成するためのシンプルかつ高性能なソリューションを提供します。Delta Lakeはサロゲートキーの生成にIdentity列を活用できる初めてのデータレイクプロトコルです。
今ではDelta Lakeでは、新たな行がロードされた際にユニークかつ自動で増加するIDナンバーが自動で生成されるIDENTITY列の作成をサポートしています。これらのIDナンバーは連続していない場合がありますが、Deltaは可能な限りギャップが小さくなるようにベストエフォートで対応します。データウェアハウスのワークロードを容易にするために、サロゲートキーを作成するためにこの機能を活用することができます。
SQLとDelta Lakeを用いたIdentity列を用いたサロゲートキーの作成方法
[推奨] Generate Always As Identity
SQLでIdentity列を作成することは、Delta Lakeテーブルを作成するのと同じくらいシンプルです。カラムを宣言する際にidというカラム、あるいは任意の名前のBIGINTの型のカラムを追加し、GENERATED ALWAYS AS IDENTITYを入力するだけです。
これで、このテーブルに対してデータをインサートする時(IDENTITY列はインサートから除外します)には、Delta LakeはDelta Lakeテーブルにインサートされた行のIDENTITYカラムに対してユニークな値を自動で生成します。
Delta LakeにおけるIdentity列の使い方のシンプルな例を以下に示します。
CREATE OR REPLACE TABLE demo (
id BIGINT GENERATED ALWAYS AS IDENTITY,
product_type STRING,
sales BIGINT
);
そして、テーブルに新規の行をインサートする際は常にidというIdentity列は自動でインクリメントします。
INSERT INTO demo (product_type, sales)
VALUES ("Batteries", 150000);
INSERTの部分でidというサロゲートキーの列が含まれていないことに注意してください。Delta Lakeはクラウドオブジェクトストレージ(例: AWS S3, Azure Data Lake Storage, Google Cloud Storage)にテーブルを書き込む際にサロゲートキーを割り当てます。詳細はドキュメントをご覧ください。
DEFAULTによる生成
また、Identity列のインサートによる上書きを許可するGENERATED BY DEFAULT AS IDENTITYオプションもあります。なお、ALWAYSオプションでは上書きは許可されません。
新機能を導入する際には注意すべきことがいくつかあります。既存テーブルにIdentity列を追加することはできません。新たなIdentity列が追加されたテーブルを再作成する必要があります。これを行うためには、Identity列を伴う新たなテーブルDDLを作成し、既存の列を新規テーブルにインサートすることで、新規テーブル上にサロゲートキーが生成されます。
Databricks SQLとDelta Lakeを用いてIdentity列を使い始める
Identity列はDatabricksランタイム10.4以降、Databricks SQL 2022.17移行で正式提供(GA)となっています。Identity列を用いることで、Photonによって高速化されているレイクハウスアーキテクチャの全てのメリットを享受しながら、データウェアハウスの全てのワークロードを実現することができます。Databricks SQLでIdentity列を試してみてください。