Unity Catalogにおけるテーブル作成

[Create tables \| Databricks on AWS](https://docs.databricks.com/data-governance/unity-catalog/create-tables.html#external-tables) [2023/1/20時点]の翻訳です。

:::note warn
本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。
:::

本書では、Unity Catalogにおける*マネージド*テーブルと*外部*テーブルのコンセプトを紹介し、Unity Catalogでどのようにテーブルを作成するのかを説明します。

:::note
**注意**
テーブルを作成する際、Unity Catalogによって管理されているカタログを参照していることを確認してください。カタログ`hive_metastore`はデータエクスプローラに表示されますが、Unity Catalogによって管理されているとはみなされません。これはDatabricksワークスペースのHiveメタストアによって管理されています。それ以外のカタログはすべてUnity Catalogによって管理されます。

Hiveメタストアに登録されている既存のテーブルをUnity CatalogにアップグレードするためのUnity Catalogテーブルアップグレードインタフェースを活用することができます。[テーブルとビューをUnity Catalogにアップグレードする](https://qiita.com/taka_yayoi/items/5e896b50b915b9de8fb3)をご覧ください。
:::

# マネージドテーブル

マネージドテーブルは、Unity Catalogでテーブルを作成するデフォルトの手法となります。これらは、[Delta](https://docs.databricks.com/delta/index.html)テーブルフォーマットを使用します。

デフォルトでは、メタストアを作成する際に設定したルートストレージロケーションにマネージドテーブルは格納されます。しかし、カタログやスキーマレベルで別のストレージロケーションを指定することができます。マネージドテーブルのデータは、ロケーションが指定された階層構造の最下層のレベルに格納されます。例えば、スキーマのロケーションが指定されるとそれが使用されます。もし、スキーマロケーションが指定され無い場合、カタログのロケーションが使用され、カタログのロケーションが指定されない場合には、デフォルトメタストアのルートロケーションが使用されます。

# 外部テーブル

外部テーブルは、データがルートストレージロケーションの外に格納されるテーブルです。DatabricksクラスターやDatabricks SQLウェアハウスの外のデータに直接アクセスする必要がある場合にのみ外部テーブルを使用してください。

外部テーブルに`DROP TABLE`を実行すると、Unity Catalogは背後のデータは削除しません。マネージドテーブルと同じように、外部テーブルの権限を管理し、クエリーでこれらを使用することができます。SQLで外部テーブルを作成するには、`CREATE TABLE`文で`LOCATION`パスを指定します。外部テーブルでは、以下のファイルフォーマットを使用することができます:

- DELTA
- CSV
- JSON
- AVRO
- PARQUET
- ORC
- TEXT

外部テーブルの背後にあるクラウドストレージへのアクセスを管理するには、[ストレージ資格情報と外部ロケーション](https://qiita.com/taka_yayoi/items/18fee92365eee58e0b94)をセットアップしなくてはなりません。

詳細は、[外部テーブルの作成](#外部テーブルの作成)をご覧ください。

# 要件

テーブルを作成しようとするスキーマに対する`CREATE TABLE`権限と、スキーマに対する`USE SCHEMA`権限、親カタログに対する`USE CATALOG`権限が必要です。

外部テーブルを作成する際には、追加の要件に関して[外部テーブルの作成](#外部テーブルの作成)をご覧ください。

# マネージドテーブルの作成

マネージドテーブルを作成するには以下のSQLコマンドを実行します。また、テーブルを作成するための[サンプルノートブック](#マネージドテーブル作成のサンプルノートブック)を活用することができます。括弧内のアイテムはオプションです。プレースホルダーの値を置き換えてください:

- `<catalog_name>`: カタログ名です。お使いのDatabricksワークスペースに紐付けられるHiveメタストアで自動で作成される`hive_metastore`カタログを指定することはできません。
- `<schema_name>`: スキーマ名です。
- `<table_name>`: テーブル名です。
- `<column_specification>`: カラムごとの名前とデータ型です。

```sql:SQL
CREATE TABLE <catalog_name>.<schema_name>.<table_name>
(
  <column_specification>
);
```

```py:Python
spark.sql("CREATE TABLE <catalog_name>.<schema_name>.<table_name> "
  "("
  "  <column_specification>"
  ")")
```

```r:R
library(SparkR)

sql(paste("CREATE TABLE <catalog_name>.<schema_name>.<table_name> ",
  "(",
  "  <column_specification>",
  ")",
  sep = ""))
```

```scala:Scala
spark.sql("CREATE TABLE <catalog_name>.<schema_name>.<table_name> " +
  "(" +
  "  <column_specification>" +
  ")")
```

また、[Databricks Terraformプロバイダー](https://qiita.com/taka_yayoi/items/691ee4909b0961de5ad1)と[databricks\_table](https://registry.terraform.io/providers/databricks/databricks/latest/docs/resources/table)を用いることでマネージドテーブルを作成することができます。[databricks\_tables](https://registry.terraform.io/providers/databricks/databricks/latest/docs/data-sources/tables)を用いることでテーブルの完全な名称のリストを取得することができます。

例えば、`main.default.department`というテーブルを作成し、5行をインサートします。

```sql:SQL
CREATE TABLE main.default.department
(
  deptcode  INT,
  deptname  STRING,
  location  STRING
);

INSERT INTO main.default.department VALUES
  (10, 'FINANCE', 'EDINBURGH'),
  (20, 'SOFTWARE', 'PADDINGTON'),
  (30, 'SALES', 'MAIDSTONE'),
  (40, 'MARKETING', 'DARLINGTON'),
  (50, 'ADMIN', 'BIRMINGHAM');
```

```py:Python
spark.sql("CREATE TABLE main.default.department "
  "("
  "  deptcode  INT,"
  "  deptname  STRING,"
  "  location  STRING"
  ")"
  "INSERT INTO main.default.department VALUES "
  "  (10, 'FINANCE', 'EDINBURGH'),"
  "  (20, 'SOFTWARE', 'PADDINGTON'),"
  "  (30, 'SALES', 'MAIDSTONE'),"
  "  (40, 'MARKETING', 'DARLINGTON'),"
  "  (50, 'ADMIN', 'BIRMINGHAM')")
```

```r:R
library(SparkR)

sql(paste("CREATE TABLE main.default.department ",
  "(",
  "  deptcode  INT,",
  "  deptname  STRING,",
  "  location  STRING",
  ")",
  "INSERT INTO main.default.department VALUES ",
  "  (10, 'FINANCE', 'EDINBURGH'),",
  "  (20, 'SOFTWARE', 'PADDINGTON'),",
  "  (30, 'SALES', 'MAIDSTONE'),",
  "  (40, 'MARKETING', 'DARLINGTON'),",
  "  (50, 'ADMIN', 'BIRMINGHAM')",
  sep = ""))
```

```scala:Scala
spark.sql("CREATE TABLE main.default.department " +
  "(" +
  "  deptcode  INT," +
  "  deptname  STRING," +
  "  location  STRING" +
  ")" +
  "INSERT INTO main.default.department VALUES " +
  "  (10, 'FINANCE', 'EDINBURGH')," +
  "  (20, 'SOFTWARE', 'PADDINGTON')," +
  "  (30, 'SALES', 'MAIDSTONE')," +
  "  (40, 'MARKETING', 'DARLINGTON')," +
  "  (50, 'ADMIN', 'BIRMINGHAM')")
```

## マネージドテーブル作成のサンプルノートブック

カタログ、スキーマ、マネージドテーブルを作成し、アクセス権を管理するために以下のサンプルノートブックを使うことができます。

### SQLを用いたUnity Catalogにおけるテーブルの作成と管理

https://docs.databricks.com/_extras/notebooks/source/unity-catalog-example-notebook.html

### Pythonを用いたUnity Catalogにおけるテーブルの作成と管理

https://docs.databricks.com/_extras/notebooks/source/unity-catalog-quickstart-python.html

# マネージドテーブルの削除

マネージドテーブルを削除するには、以下のSQLコマンドを実行します。

```sql:SQL
DROP TABLE IF EXISTS catalog_name.schema_name.table_name;
```

マネージドテーブルを削除すると、30日以内にお使いのクラウドテナントから背後のデータが削除されます。

# 外部テーブルの作成

外部テーブルのデータはお使いのクラウドテナントのパスに格納されます。外部テーブルを操作するために、Unity Catalogでは外部クラウドストレージにアクセスし操作するために新たに2つのオブジェクトを導入しています。

- *ストレージ資格情報*には、クラウドストレージロケーションにアクセスするための認証手段が格納されます。ストレージ資格情報には、アクセスを許可するパスへのマッピングは含まれません。ストレージ資格情報は、どのユーザーが資格情報を使用できるのかを制御できるようにアクセスコントロールされます。直接外部のストレージ資格情報を使用するには、SQLコマンドに`WITH <credential_name>`を追加します。
- *外部ロケーション*は、ストレージ資格情報とアクセスを許可するクラウドストレージパスをマッピングします。外部ロケーションは、当該のクラウドストレージパスとコンテンツに対してのみアクセスを許可します。どのユーザーが外部ロケーションを使用できるのかを決定するために、外部ロケーションはアクセスコントロールされます。外部ロケーションは、`LOCATION`句を含み、`WITH <credential_name>`を持たないSQLコマンドで自動で使用されます。

## 要件

- 外部テーブルを作成するには以下の権限が必要となります。
    - 外部テーブルによってアクセスされる`LOCATION`へのアクセスを許可する外部ロケーション、ストレージ資格情報における`CREATE EXTERNAL TABLE`権限。
    - テーブルの親スキーマにおける`USE SCHEMA`権限。
    - テーブルの親カタログにおける`USE CATALOG`権限。
    - テーブルの親スキーマにおける`CREATE TABLE`権限。

外部ロケーションとストレージ資格情報はカタログではなく、メタストアのトップレベルに格納されます。ストレージ資格情報や外部ロケーションを作成するには、メタストア管理者あるいはアカウントレベルの管理者である必要があります。[Unity Catalogにおける外部ロケーションとストレージ認証情報の管理](https://qiita.com/taka_yayoi/items/18fee92365eee58e0b94)をご覧ください。

外部テーブルを作成するには、これらのハイレベルのステップに従ってください。また、ストレージ資格情報、外部ロケーション、外部テーブルを作成し、権限を管理するために、[サンプルノートブック](#外部テーブル作成のサンプルノートブック)を活用することができます。

## 外部テーブルの作成

外部ロケーション(推奨)あるいは直接ストレージ資格情報を用いて外部テーブルを作成することができます。以下のサンプルではプレースホルダーの値を置き換えてください。

- `<catalog>`: テーブルを格納するカタログ名です。
- `<schema>`: テーブルを格納するスキーマ名です。
- `<table_name>`: テーブル名です。
- `<column_specification>`: カラムごとの名称とデータ型です。
- `<bucket_path>`: テーブルが作成されるお使いのクラウドテナント上のパスです。
- `<table_directory>`: テーブルが作成されるディレクトリです。テーブルごとにユニークなディレクトリを使用してください。

:::note warn
**重要！**
パスにテーブルが作成されると、外部ロケーションやストレージ資格情報で権限が付与されていたとしても、ユーザーは当該パスに直接アクセスできなくなります。これは、クラウドテナントで直接ファイルを読み込むことで、適用されているアクセス制御を迂回することを防ぐためです。
:::

### 外部ロケーションを用いた外部テーブルの作成

外部ロケーションを用いて空の外部テーブルを作成するには:

```sql:SQL
CREATE TABLE <catalog>.<schema>.<table_name>
(
  <column_specification>
)
LOCATION 's3://<bucket_path>/<table_directory>';
```

```py:Python
spark.sql("CREATE TABLE <catalog>.<schema>.<table_name> "
  "("
  "  <column_specification>"
  ") "
  "LOCATION 's3://<bucket_path>/<table_directory>'")
```

```r:R
library(SparkR)

sql(paste("CREATE TABLE <catalog>.<schema>.<table_name> ",
  "(",
  "  <column_specification>",
  ") ",
  "LOCATION 's3://<bucket_path>/<table_directory>'",
  sep = ""))
```

```scala:Scala
spark.sql("CREATE TABLE <catalog>.<schema>.<table_name> " +
  "(" +
  "  <column_specification>" +
  ") " +
  "LOCATION 's3://<bucket_path>/<table_directory>'")
```

Unity Catalogは、あなたが以下の権限を有しているかをチェックします。

- 指定したクラウドストレージを参照する外部ロケーションにおける`CREATE EXTERNAL TABLE`権限。
- 親スキーマにおける`CREATE TABLE`権限。
- 親スキーマにおける`USE SCHEMA`権限。
- 親カタログにおける`USE CATALOG`権限。

権限がある場合、外部テーブルが作成されます。そうでない場合、エラーが発生し外部テーブルは作成されません。

:::note
**注意**
データを複製することなしに、Hiveメタストアにある既存外部テーブルをUnity Catalogに移行することができます。[外部テーブルをUnity Catalogにアップグレードする](https://qiita.com/taka_yayoi/items/5e896b50b915b9de8fb3#%E5%A4%96%E9%83%A8%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%82%92unity-catalog%E3%81%AB%E3%82%A2%E3%83%83%E3%83%97%E3%82%B0%E3%83%AC%E3%83%BC%E3%83%89%E3%81%99%E3%82%8B)をご覧ください。
:::

また、[Databricks Terraformプロバイダー](https://qiita.com/taka_yayoi/items/691ee4909b0961de5ad1)と[databricks\_table](https://registry.terraform.io/providers/databricks/databricks/latest/docs/resources/table)を用いることでマネージドテーブルを作成することができます。[databricks\_tables](https://registry.terraform.io/providers/databricks/databricks/latest/docs/data-sources/tables)を用いることでテーブルの完全な名称のリストを取得することができます。

## 外部テーブル作成のサンプルノートブック

### Unity Catalogにおける外部テーブルの作成と管理

https://docs.databricks.com/_extras/notebooks/source/unity-catalog-external-table-example-aws.html

# お使いのクラウドテナントに格納されているファイルからのテーブルの作成

お使いのクラウドテナントに格納されているファイルのレコードを用いてマネージドテーブル、外部テーブルにデータを追加することができます。Unity Catalogは当該ロケーションのファイルを読み込み、そのコンテンツをテーブルにインサートします。Unity Catalogではこれを*パスベースのアクセス*と呼びます。

:::note
**注意**
外部テーブルを作成したストレージパスを、データファイルの読み書きに使用することはできません。
:::

## ファイルの中身の探索

データからテーブルを作成する前に、外部ロケーションに格納されているデータを探索するために、Data Explorerあるいは以下のコマンドを使用することができます。

**アクセス権が必要です:** 当該ロケーションのデータファイルの一覧を取得するには、クラウドストレージパスに関連づけられた外部ロケーションに対する`READ FILES`権限が必要となります。

**SQL**

1. クラウドストレージのファイルの一覧。

    ```sql:SQL
    LIST 's3://<path_to_files>';
    ```

1. 指定されたパスのファイルのデータへのクエリー。

    ```sql:SQL
    SELECT * FROM <format>.'[s3://<path_to_files>';
    ```

**Python**

1. クラウドストレージのファイルの一覧。

    ```py:Python
    display(spark.sql("LIST 's3://<path_to_files>'"))
    ```

1. 指定されたパスのファイルのデータへのクエリー。

    ```py:Python
    display(spark.read.load("s3://<path_to_files>"))
    ```

**R**

1. クラウドストレージのファイルの一覧。

    ```r:R
    library(SparkR)

    display(sql("LIST 's3://<path_to_files>'"))
    ```

1. 指定されたパスのファイルのデータへのクエリー。

    ```r:R
    library(SparkR)

    display(loadDF("s3://<path_to_files>"))
    ```

**Scala**

1. クラウドストレージのファイルの一覧。

    ```scala:Scala
    display(spark.sql("LIST 's3://<path_to_files>'"))
    ```

1. 指定されたパスのファイルのデータへのクエリー。

    ```scala:Scala
    display(spark.read.load("s3://<path_to_files>"))
    ```

## ファイルからテーブルの作成

:::note
**注意**
データを複製することなしに、Hiveメタストアにある既存外部テーブルをUnity Catalogに移行することができます。[外部テーブルをUnity Catalogにアップグレードする](https://qiita.com/taka_yayoi/items/5e896b50b915b9de8fb3#%E5%A4%96%E9%83%A8%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%82%92unity-catalog%E3%81%AB%E3%82%A2%E3%83%83%E3%83%97%E3%82%B0%E3%83%AC%E3%83%BC%E3%83%89%E3%81%99%E3%82%8B)をご覧ください。
:::

新規テーブルを作成し、お使いクラウドテナントのデータファイルのレコードを用いてデータを追加します。

:::note
**重要！**
- この方法でテーブルを作成する際、レコードの複製を防ぐために、このストレージパスは一度のみ読み込まれます。ディレクトリのコンテンツを再読み込みしたい場合、テーブルを削除して再作成しなくてはなりません。既存テーブルに対しては、ストレージパスから[レコードをインサート](#パスから既存のテーブルにレコードをインサート)することができます。
- テーブルを作成したバケットパスは、データファイルの読み書きに使用することはできません。
- 指定されたディレクトリのファイルのみが読み込まれます。再起的な読み込みは行われません。
- 以下の権限が必要です。
    - 親カタログに対する`USE CATALOG`、スキーマに対する`USE SCHEMA`権限。
    - 親スキーマに対する`CREATE TABLE`権限。
    - ファイルが格納されているバケットパスに関連づけられている外部ロケーション、外部ロケーションを使用していない場合はストレージ資格情報に対する`READ FILES`権限。
    - 外部テーブルを作成する際、テーブルが作成されるバケットパスに対する`CREATE EXTERNAL TABLE`権限。
:::

- マネージドテーブルを作成し、バケットパスのレコードを用いてデータを追加します。

```sql:SQL
CREATE TABLE <catalog>.<schema>.<table_name>
USING delta
(
  <column_specification>
)
SELECT * from delta.`s3://<path_to_files>`;
```

```py:Python
spark.sql("CREATE TABLE <catalog>.<schema>.<table_name> "
  "USING delta "
  "( "
  "  <column_specification> "
  ") "
  "SELECT * from delta.`s3://<path_to_files>`")
```

```r:R
library(SparkR)

sql(paste("CREATE TABLE <catalog>.<schema>.<table_name> ",
  "USING delta ",
  "( ",
  "  <column_specification> ",
  ") ",
  "SELECT * from delta.`s3://<path_to_files>`",
  sep = ""))
```

```scala:Scala
spark.sql("CREATE TABLE <catalog>.<schema>.<table_name> " +
  "USING delta " +
  "( " +
  "  <column_specification> " +
  ") " +
  "SELECT * from delta.`s3://<path_to_files>`")
```

- 外部テーブルを作成し、バケットパスのレコードを用いてデータを追加するには`LOCATION`を追加します。

```sql:SQL
CREATE TABLE <catalog>.<schema>.<table_name>
USING delta
(
    <column_specification>
)
LOCATION 's3://<table_location>'
SELECT * from <format>.`s3://<path_to_files>`;
```

```py:Python
spark.sql("CREATE TABLE <catalog>.<schema>.<table_name> "
  "USING delta "
  "( "
  "  <column_specification> "
  ") "
  "LOCATION 's3://<table_location>' "
  "SELECT * from <format>.`s3://<path_to_files>`")
```

```r:R
library(SparkR)

sql(paste("CREATE TABLE <catalog>.<schema>.<table_name> ",
  "USING delta ",
  "( ",
  "  <column_specification> ",
  ") ",
  "LOCATION 's3://<table_location>' ",
  "SELECT * from <format>.`s3://<path_to_files>`",
  sep = ""))
```

```scala:Scala
spark.sql("CREATE TABLE <catalog>.<schema>.<table_name> " +
  "USING delta " +
  "( " +
  "  <column_specification> " +
  ") " +
  "LOCATION 's3://<table_location>' " +
  "SELECT * from <format>.`s3://<path_to_files>`")
```

# パスから既存のテーブルにレコードをインサート

バケットパスにあるレコードを既存テーブルにインサートするには、`COPY INTO`コマンドを使用します。以下のサンプルでは、プレースホルダーの値を置き換えてください。

- `<catalog>`: テーブルの親カタログの名称です。
- `<schema>`: テーブルの親スキーマの名称です。
- `<path_to_files>`: データファイルを格納するバケットパスです。
- `<format>`: ファイルのフォーマットです。
- `<table_location>`: テーブルが作成されるバケットパスです。
- `<storage_credential>`: ストレージ資格情報を直接使用している場合、バケットパスへの読み書きを認証するストレージ資格情報の名前です。

:::note warn
**重要！**
- この方法でテーブルにレコードをインサートする際、レコードの複製を防ぐために、このストレージパスは一度のみ読み込まれます。
- テーブルを作成したバケットパスは、データファイルの読み書きに使用することはできません。
- 指定されたディレクトリのファイルのみが読み込まれます。再起的な読み込みは行われません。
- 以下の権限が必要です。
    - 親カタログに対する`USE CATALOG`、スキーマに対する`USE SCHEMA`権限。
    - 親スキーマに対する`CREATE TABLE`権限。
    - ファイルが格納されているバケットパスに関連づけられている外部ロケーション、外部ロケーションを使用していない場合はストレージ資格情報に対する`READ FILES`権限。
    - 外部テーブルを作成する際、テーブルが作成されるバケットパスに対する`CREATE EXTERNAL TABLE`権限。
:::

- バケットパスのファイルのレコードをマネージドテーブルにインサートする際、バケットパスから読み込みを行うための外部ロケーションを使用します。

```sql:SQL
COPY INTO <catalog>.<schema>.<table>
FROM (
  SELECT *
  FROM 's3://<path_to_files>'
)
FILEFORMAT = <format>;
```

```py:Python
spark.sql("COPY INTO <catalog>.<schema>.<table> "
  "FROM ( "
  "  SELECT * "
  "  FROM 's3://<path_to_files>' "
  ") "
  "FILEFORMAT = <format>")
```

```r:R
library(SparkR)

sql(paste("COPY INTO <catalog>.<schema>.<table> ",
  "FROM ( ",
  "  SELECT * ",
  "  FROM 's3://<path_to_files>' ",
  ") ",
  "FILEFORMAT = <format>",
  sep = ""))
```

```scala:Scala
spark.sql("COPY INTO <catalog>.<schema>.<table> " +
  "FROM ( " +
  "  SELECT * " +
  "  FROM 's3://<path_to_files>' " +
  ") " +
  "FILEFORMAT = <format>")
```

- 外部テーブルにインサートするには、`LOCATION`句を追加します。

```sql:SQL
COPY INTO <catalog>.<schema>.<table>
LOCATION 's3://<table_location>'
FROM (
  SELECT *
  FROM 's3://<path_to_files>'
)
FILEFORMAT = <format>;
```

```py:Python
spark.sql("COPY INTO <catalog>.<schema>.<table> "
  "LOCATION 's3://<table_location>' "
  "FROM ( "
  "  SELECT * "
  "  FROM 's3://<path_to_files>' "
  ") "
  "FILEFORMAT = <format>")
```

```r:R
library(SparkR)

sql(paste("COPY INTO <catalog>.<schema>.<table> ",
  "LOCATION 's3://<table_location>' ",
  "FROM ( ",
  "  SELECT * ",
  "  FROM 's3://<path_to_files>' ",
  ") ",
  "FILEFORMAT = <format>",
  sep = ""))
```

```scala:Scala
spark.sql("COPY INTO <catalog>.<schema>.<table> " +
  "LOCATION 's3://<table_location>' " +
  "FROM ( " +
  "  SELECT * " +
  "  FROM 's3://<path_to_files>' " +
  ") " +
  "FILEFORMAT = <format>")
```

# 次のステップ

- [Manage privileges in Unity Catalog](https://docs.databricks.com/data-governance/unity-catalog/manage-privileges/index.html)

### Databricks 無料トライアル

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