Databricks ( Spark ) にて Delta lake 形式のテーブルに設定する TBLPROPERTIES に関するまとめ

## 概要

Databricks ( Spark ) にて Delta lake 形式のテーブルに設定する TBLPROPERTIES を整理します。

## TBLPROPERTIES 概要
### TBLPROPERTIES とは

テーブルに設定するプロパティです。Delta Lake 形式のテーブルでは `delta.` ではじまる TBLPROPERTIES の設定値に応じて処理を実行します。Delta Lake 形式のテーブルの場合には、メタストアに設定値が保持されるのではなく、ディレクトリに設定値が保持されるようです。Delta Lake のバージョンアップに伴い、設定できる値が増えていくため、継続的に設定方針を検討する必要があります。

TBLPROPERTIES は下記のドキュメントにて整理されております。2022年12月5日時点では、日本語版ドキュメントでは表示されません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/403be476-cb6d-0f4a-6205-1338e87b7165.png)

引用元：[Delta table properties reference - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/en-us/azure/databricks/delta/table-properties)

TBLPROPERTIES の設定値の１つである `delta.enableChangeDataFeed` を `true`と設定することで、変更データフィード機能が有効になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6b8f6fa3-cce4-627a-a07d-379e8f355f42.png)

引用元：[Azure Databricks で Delta Lake 変更データ フィードを使用する - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/delta/delta-change-data-feed)

TBLPROPERTIES を設定する方法としては、テーブ作成時（`CREAT TABLE`）や テーブル変更時（`ALTER TABLE`）にて設定が可能です。削除したい場合には、`ALTER TABLE` の `UNSET TBLPROPERTIES clause` を利用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e5532a73-0ec3-d156-c95a-7631760d0086.png)

引用元：[CREATE TABLE USING (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-create-table-using)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/26c04242-8555-dd87-e3b0-835aa7ba890c.png)

引用元：[ALTER TABLE (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-alter-table)

私は、基本的にはテーブル作成後に、`ALTER TABLE` 文で設定できるようにしております。ただし、テーブル作成時点である Delta Lake のバージョン 0 の時点から有効にしたい場合には、テーブル作成時にも設定することを検討してください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/64d5b6e6-bfcc-39a1-d7d4-bd83abb9b4bf.png)


### ドキュメントにて記載されている一般的な TBLPROPERTIES

2022年12月5日時点では、下記のような記載がされています。

> Delta Lake では、次の設定が一般的に使用されます。
> 
> delta.appendOnly: true に設定し、UPDATE と DELETE の操作を無効にします。
> delta.dataSkippingNumIndexedCols: 統計情報を収集して考慮する、先頭の列の数に設定します。
> delta.deletedFileRetentionDuration: VACUUM がファイルを削除できるタイミングを制御する間隔 ('interval 7 days' など) に設定します。
delta.logRetentionDuration: タイム トラベル クエリの履歴を保持する期間を制御する間隔 ('interval 60 days' など) に設定します。

引用元：[ 一般的な TBLPROPERTIES キーと OPTIONS キー (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-tblproperties?view=sqlallproducts-allversions#common-tblproperties-and-options-keys)


### 参考リンク

- 設定値に関するリンク集
    -  [テーブル プロパティとテーブル オプション (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-tblproperties?view=sqlallproducts-allversions)
    -   [Delta table properties reference - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/en-us/azure/databricks/delta/table-properties)
    -   [Azure Databricks での自動最適化 - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/optimizations/auto-optimize)
    -   [Configure Delta Lake to control data file size - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/en-us/azure/databricks/delta/tune-file-size)
    -   [デルタ レイクの Z オーダー インデックスを使用したデータのスキップ - Azure データブリック |マイクロソフト学習 (microsoft.com)](https://learn.microsoft.com/ja-jp/azure/databricks/delta/data-skipping)
    -   [Azure Databricks で Delta Lake 変更データ フィードを使用する - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/delta/delta-change-data-feed)
-  設定のライフサイクル管理に関するリンク集
    -   [CREATE TABLE USING (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-create-table-using)
    -   [ALTER TABLE (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-alter-table)
    -   [SHOW TBLPROPERTIES (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-syntax-aux-show-tblproperties)

## TBLPROPERTIES の設定時の注意事項

### 書き込み、あるいは、読み込みに利用できる Databricks ランタイム（Delta Lake のバージョン）に制約される場合があること

TBLPROPERTIES により特定の機能を有効にすると、過去の Databricks ランタイム（Delta Lake のバージョン）を利用できなくなる場合があるため、設定時に注意してください。変更データフィード機能を有効にすると、Databricks Runtime 8.1 より前のバージョンの Databricks ランタイムでは書き込みが実施できなくなります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8eb14580-1a7e-ece2-506b-6bd52049dd32.png)

引用元：[Azure Databricks で Delta Lake 変更データ フィードを使用する - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/delta/delta-change-data-feed#enable-change-data-feed)

TBLPROPERTIES の設定により、OSS の Delta lake 利用時に影響がある場合もあるようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/fcd7b2c6-5d08-ece3-2154-f5cb165ac97f.png)

引用元：[Manage column-level statistics in checkpoints - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/en-us/azure/databricks/delta/best-practices#column-stats)

### `delta.dataSkippingNumIndexedCols` を設定する際には、カラム順を変更

`delta.dataSkippingNumIndexedCols`では、先頭から指定したカラム数分の統計情報を取得する仕様であるため、`ALTER TABLE`の`ALTER COLUMN clause`によりカラム順を変更する必要があります。

> delta.dataSkippingNumIndexedCols: 統計情報を収集して考慮する、先頭の列の数に設定します。

引用元：[ 一般的な TBLPROPERTIES キーと OPTIONS キー (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-tblproperties?view=sqlallproducts-allversions#common-tblproperties-and-options-keys)

```python
col_reorder_ddls = []
col_reorder_ddls.append(f'''
ALTER TABLE {db_name}.{tbl_name}
  CHANGE COLUMN
    _ingest_timestamp first
'''
)
 
index_col_num = len(col_reorder_ddls)
print(index_col_num)
for ddl in col_reorder_ddls:
    spark.sql(ddl)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/28c4bc0c-d7d3-5821-38de-fbeb097dabff.png)

### 変更データフィードをパイプライン等で利用する場合には `delta.enableChangeDataFeed` をテーブル作成時に設定することが推奨

下記の記事で検証結果を載せているのですが、変更データフィード適用前へのクエリを実行するとエラーとなるため、テーブル作成時（Delta Lake のバージョン 0）から `delta.enableChangeDataFeed` を有効にすることでパイプライン等でのロジックが比較的シンプルとなります。

[Databricks ( Spark )にて変更データフィード（Change Data Feed、CDF） 適用前の時点に対するクエリエラーへの対応方法 - Qiita](https://qiita.com/manabian/items/a6b71c12c5bc2b79622c)

## TBLPROPERTIES の設定値例

### 概要

2022年12月5月時点におけるケース別設定値例を記載します。設定を検討する際には、ドキュメント等で最新情報を確認するようにしてください。

### 基本的に設定すべきテーブルプロパティ

delta.dataSkippingNumIndexedCols を設定する際にはカラム順を適切に設定すること

```sql
delta.dataSkippingNumIndexedCols = 3
,delta.logRetentionDuration = 'interval 30 days'
,delta.deletedFileRetentionDuration= 'interval 7 days'
,delta.enableChangeDataFeed = true
```

### バッチ処理対象のテーブルの場合に設定すべきテーブルプロパティ

```sql
delta.autoOptimize.optimizeWrite = true
,delta.autoOptimize.autoCompact = true
```

参考リンク

- [Enable auto optimize - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/en-us/azure/databricks/optimizations/auto-optimize#enable-auto-optimize)

下記の設定は、OSS の Delta Lake からの参照時の懸念がドキュメントにて記載あり。

```sql
delta.checkpoint.writeStatsAsJson = false
```

参考リンク

- [ Manage column-level statistics in checkpoints - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/en-us/azure/databricks/delta/best-practices#column-stats)


### 数TBを超えるテーブルに設定すべきテーブルプロパティ

```sql
delta.targetFileSize = '3tb'
```

参考リンク

- [ Autotune file size based on table size - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/delta/tune-file-size#autotune-file-size-based-on-table-size)

### Merge 処理が頻繁に実施されるテーブルの場合に設定すべきテーブルプロパティ

```sql
delta.tuneFileSizesForRewrites = true
```

参考リンク

- [Autotune file size based on workload - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/delta/tune-file-size#autotune-file-size-based-on-workload)

%md
### Append 処理のみに制限したい場合に設定すべきテーブルプロパティ

```sql
delta.appendOnly = true
```

### スキーマの展開を許可する場合に設定すべきテーブルプロパティ

下記により設定可能だが、基本的にはデータエンジニアリング時の spark プロパティで制御することが望ましい。

```sql
spark.databricks.delta.schema.autoMerge.enabled = true
```

### 利用方法を検討中のプロパティ

```sql
delta.columnMapping.mode = true
,delta.randomizeFilePrefixes = false
,delta.randomPrefixLength = 2
,delta.isolationLevel = 'WriteSerializable'
,delta.setTransactionRetentionDuration = 'interval 30 days'
```

### プレビュー中のプロパティ

```sql
delta.columnMapping.mode = true
```

参考リンク

- [Azure Databricks での列マッピング - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/delta/delta-column-mapping)