こちらをウォークスルーします。
セットアップ
ノートブックを作成して以下を実行します。
%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アナリストの支援になることを見てきました。
ベストなデータウェアハウスはレイクハウスです！
次のステップ: