こちらをウォークスルーします。

セットアップ

ノートブックを作成して以下を実行します。

%pip install dbdemos

import dbdemos
dbdemos.install('identity-pk-fk')

ノートブックとクラスターが作成されます。

Unity Catalog : アイデンティティカラム、主キー + 外部キーの制約

ノートブック00-Identity_PK_FKを実行していきます。

SQLオペレーションをシンプルにし、オンプレミスや他のウェアハウスからのマイグレーションをサポートするために、Databricksレイクハウスでは、維持や進化のメンテナンスがシンプルなER図を構築するための便利な手段を提供します。

これらの機能は以下を提供します:

自動で増加するアイデンティティカラム。データをインサートするとエンジンが自動でIDをインクリメントします。
主キー定義のサポート。
外部キー制約定義のサポート。

現時点では、主キーと外部キーは情報提供のためのものであり、強制はされないことに注意して下さい。

ユースケース

PKとFKを定義することで、BIアナリストがエンティティのリレーションシップや、どのようにテーブルを結合するのかを理解する助けとなります。また、更なる最適化を実行するために、これを活用できるBIツールに更なる情報を提供します。

ここでは、以下のスタースキーマを定義します:

dim_store
dim_product
dim_customer

そして、我々のディメンションテーブルをポイントするセールス情報を含むファクトテーブルを定義します:

fact_sales

要件:

PK/FKには、Unity Catalogを有効化する必要があります (HiveメタストアではFK/PKをサポートしていません)
DBR 11.1

以下のセルを実行するとユーザー名を含むカタログとデータベースが作成されます。

%run ./_resources/00-setup

1/ Unity Catalogでディメンションテーブルとファクトテーブルを作成

最初のステップでは、Unity CatalogでDeltaテーブルを作成します(ドキュメントをご覧ください)。

マルチ言語サポートを説明するために、ここではこれをSQLで実行します(Pythonでも可能です):

CREATE TABLEコマンドの使用
GENERATED ALWAYS AS IDENTITYを用いて生成アイデンティティカラムの追加
PRIMARY KEYを用いてPKを定義
FOREIGN KEY REFERENCESを用いて外部キーを定義

-- 店舗ディメンション
CREATE OR REPLACE  TABLE dim_store(
  store_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  store_name STRING,
  address STRING
);

-- 製品ディメンション
CREATE OR REPLACE  TABLE dim_product(
  product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku STRING,
  description STRING,
  category STRING
);

-- 顧客ディメンション
CREATE OR REPLACE  TABLE dim_customer(
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 0 INCREMENT BY 10) PRIMARY KEY,
  customer_name STRING,
  customer_profile STRING,
  address STRING
);

CREATE OR REPLACE TABLE fact_sales(
  sales_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id BIGINT NOT NULL CONSTRAINT dim_product_fk FOREIGN KEY REFERENCES dim_product,
  store_id BIGINT NOT NULL CONSTRAINT dim_store_fk FOREIGN KEY REFERENCES dim_store,
  customer_id BIGINT NOT NULL CONSTRAINT dim_customer_fk FOREIGN KEY REFERENCES dim_customer,
  price_sold DOUBLE,
  units_sold INT,
  dollar_cost DOUBLE
);

2/ DIM_CUSTOMERのテーブル定義を見てみましょう

最初のステップで DESCRIBE TABLE EXTENDED を実行します。

結果の最後に制約が表示されます:

col_name	data_type
# Constraints
dim_customer_pk	PRIMARY KEY (`customer_id`)

DESCRIBE TABLE EXTENDED dim_customer;

3/ ディメンションテーブルにいくつかデータを追加しましょう

マルチ言語サポートを説明するためにこれをSQLで実行します:

テーブルにいくつかの行を追加するために INSERT INTO コマンドを使用します
auto-incrementでエンジンによって値が生成されるので、IDの値を指定しないことに注意してください

INSERT INTO
  dim_store (store_name, address)
VALUES
  ('City Store', '1 Main Rd, Whoville');
  
INSERT INTO
  dim_product (sku, description, category)
VALUES
  ('1000001', 'High Tops', 'Ladies Shoes'),
  ('7000003', 'Printed T', 'Ladies Fashion Tops');
  
INSERT INTO
  dim_customer (customer_name, customer_profile, address)
VALUES
  ('Al', 'Al profile', 'Databricks - Queensland Australia'),
  ('Quentin', 'REDACTED_PROFILE', 'Databricks - Paris France');

SELECT * FROM dim_product;

product_id	sku	description	category
1	1000001	High Tops	Ladies Shoes
2	7000003	Printed T	Ladies Fashion Tops

4/ ファクトテーブルにいくつか値を追加しましょう

マルチ言語サポートを説明するために、ここではこれをSQLで実行します:

テーブルにいくつかの行を追加するために INSERT INTO コマンドを使用します

INSERT INTO
  fact_sales (product_id, store_id, customer_id, price_sold, units_sold, dollar_cost)
VALUES
  (1, 1, 0, 100.99, 2, 2.99),
  (2, 1, 0, 10.99, 2, 2.99),
  (1, 1, 0, 100.99, 2, 2.99),
  (1, 1, 10, 100.99, 2, 2.99),
  (2, 1, 10, 10.99, 2, 2.99);

データをjoinしてテーブルをクエリー

これで、FKをベースとして、データを取得するためにテーブルへのクエリーを実装できます:

SELECT * FROM fact_sales
  INNER JOIN dim_product  USING (product_id)
  INNER JOIN dim_customer USING (customer_id)
  INNER JOIN dim_store    USING (store_id)

store_id	customer_id	product_id	sales_id	price_sold	units_sold	dollar_cost	sku	description	category	customer_name	customer_profile	address	store_name	address1
1	0	1	1	100.99	2	2.99	1000001	High Tops	Ladies Shoes	Al	Al profile	Databricks - Queensland Australia	City Store	1 Main Rd, Whoville
1	0	2	2	10.99	2	2.99	7000003	Printed T	Ladies Fashion Tops	Al	Al profile	Databricks - Queensland Australia	City Store	1 Main Rd, Whoville
1	0	1	3	100.99	2	2.99	1000001	High Tops	Ladies Shoes	Al	Al profile	Databricks - Queensland Australia	City Store	1 Main Rd, Whoville
1	10	1	4	100.99	2	2.99	1000001	High Tops	Ladies Shoes	Quentin	REDACTED_PROFILE	Databricks - Paris France	City Store	1 Main Rd, Whoville
1	10	2	5	10.99	2	2.99	7000003	Printed T	Ladies Fashion Tops	Quentin	REDACTED_PROFILE	Databricks - Paris France	City Store	1 Main Rd, Whoville

5/ データエクスプローラにおける主キーと外部キー

データエクスプローラでテーブルにアクセスすると、主キーや外部キーをアイコンで確認できます。

関係を表示をクリックするとER図が表示されます。

6/ DBSQLにおける主キーと外部キー - コードコンプリート

主キーと外部キーを定義しておくと、SQLエディタでクエリーを記述する際に結合すべきカラムがON句とともに提案されます。

こんな感じです。便利。

サマリー

主キーと外部キーによって、エンティティリレーションシップやテーブルのjoinの方法を理解し、さらにはあなたのためにjoinのコードコンプリートを活用でき、BIアナリストの支援になることを見てきました。

ベストなデータウェアハウスはレイクハウスです！

次のステップ:

DBSQLのクエリー、ダッシュボードエディタを試す
これらのテーブルに直接クエリーするためにお使いのBIツール(Tableau, PowerBI ...)を繋ぎましょう！

はじめてのDatabricks

はじめてのDatabricks

Databricks無料トライアル

Databricks無料トライアル