アイデンティティ、主キー、外部キーを用いたDatabricksのデータウェアハウジング

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

https://www.databricks.com/resources/demos/tutorials/data-warehouse/data-warehousing-with-identity-primary-key-and-foreign-key

# セットアップ

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

```py
%pip install dbdemos
```
```py
import dbdemos
dbdemos.install('identity-pk-fk')
```

ノートブックとクラスターが作成されます。
![Screenshot 2024-02-22 at 16.46.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/f686dfda-79ee-8c32-5f1f-0498202036a8.png)

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

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

<img src="https://github.com/QuentinAmbard/databricks-demo/raw/main/product_demos/uc/UC-FK-PK-crop.png" style="float:right; margin:10px 0px 0px 10px" width="700"/>

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

これらの機能は以下を提供します:
- 自動で増加するアイデンティティカラム。データをインサートするとエンジンが自動でIDをインクリメントします。
- 主キー定義のサポート。
- 外部キー制約定義のサポート。

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

### ユースケース

<img src="https://github.com/QuentinAmbard/databricks-demo/raw/main/product_demos/uc/UC-star-schema.png" style="float:right; margin:10px 0px 0px 10px" width="700"/>

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

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

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

* fact_sales

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


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

```py
%run ./_resources/00-setup
```
![Screenshot 2024-02-22 at 16.51.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/9269bd59-c1d0-8f55-0fd1-e25669f80ceb.png)

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

最初のステップでは、Unity CatalogでDeltaテーブルを作成します([ドキュメント](https://docs.databricks.com/sql/language-manual/sql-ref-syntax-ddl-create-table-using.html)をご覧ください)。

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

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

```sql
-- 店舗ディメンション
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`) |

```sql
DESCRIBE TABLE EXTENDED dim_customer;
```
![Screenshot 2024-02-22 at 16.54.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/9c407ede-021d-0fd0-d308-106a6ee0e814.png)

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

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

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

```sql
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');
```


```sql
SELECT * FROM dim_product;
```
```
product_id	sku	description	category
1	1000001	High Tops	Ladies Shoes
2	7000003	Printed T	Ladies Fashion Tops
```

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

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

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

```sql
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をベースとして、データを取得するためにテーブルへのクエリーを実装できます:

```sql
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/ データエクスプローラにおける主キーと外部キー

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

<img src="https://github.com/althrussell/databricks-demo/raw/main/product-demos/pkfk/images/data_explorer.gif" style="float:right; margin-left:100px" width="700"/>

**関係を表示**をクリックするとER図が表示されます。
![Screenshot 2024-02-22 at 17.15.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/ccf3f569-8078-a136-2c12-27da461d6bdd.png)
![Screenshot 2024-02-22 at 17.17.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/2c8f3a47-9d8a-b733-c462-b33b62e391fe.png)


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

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

<img src="https://github.com/althrussell/databricks-demo/raw/main/product-demos/pkfk/images/code_completion.gif" style="float:center; margin-left:100px" width="700"/>

こんな感じです。便利。
![Screenshot 2024-02-22 at 17.11.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/9aa8968c-a57a-efdb-d10e-6c11a01938fc.png)
![Screenshot 2024-02-22 at 17.11.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/1a2e22d5-55ac-6f01-0592-b600ee944bfe.png)

# サマリー

<img src="https://github.com/QuentinAmbard/databricks-demo/raw/main/product_demos/uc/UC-FK-PK.png" style="float:right; margin-left:10px" width="900"/>

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

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

次のステップ:
- DBSQLのクエリー、[ダッシュボード](https://docs.databricks.com/ja/sql/user/dashboards/index.html)エディタを試す
- これらのテーブルに直接クエリーするためにお使いの[BIツール](https://docs.databricks.com/ja/partner-connect/bi.html)(Tableau, PowerBI ...)を繋ぎましょう！

### はじめてのDatabricks

[はじめてのDatabricks](https://qiita.com/taka_yayoi/items/8dc72d083edb879a5e5d)

### Databricks無料トライアル

[Databricks無料トライアル](https://databricks.com/jp/try-databricks)