Databricksにおけるデータベースおよびテーブル

[Databases and tables \| Databricks on AWS](https://docs.databricks.com/data/tables.html#create-table-ui) [2021/3/22時点]の翻訳です。

:::note alert
**警告！**
こちらの内容は古いものとなっています。[Databricksレイクハウスにおけるデータオブジェクト](https://qiita.com/taka_yayoi/items/e90be9beeb1a744cee0d)をご覧ください。
:::

Databricksのデータベースはテーブルの集合体です。Databricksのテーブルは構造化データの集合体となります。Databricksテーブルに対して、Apache Sparkの[データフレーム(英語)](https://docs.databricks.com/spark/latest/dataframes-datasets/index.html)でサポートされているあらゆる操作、フィルター、キャッシュを行うことができます。[Spark API(英語)](https://docs.databricks.com/spark/latest/dataframes-datasets/index.html)、[Spark SQL(英語)](https://docs.databricks.com/spark/latest/spark-sql/index.html#spark-sql-lang-manual)を用いてテーブルに対してクエリーを実行することができます。

テーブルには2種類あります：グローバルテーブルとローカルテーブルです。グローバルテーブルは全てのクラスターからアクセスすることができます。DatabricksはグローバルテーブルをDatabricksの[Hive](https://hive.apache.org/)メタストア、あるいは[外部のHiveメタストア(英語)](https://docs.databricks.com/data/metastores/index.html#metastores)に登録します。Hiveサポートの詳細は[Apache Hive compatibility(英語)](https://docs.databricks.com/spark/latest/spark-sql/compatibility/hive.html#hive-compatibility)を参照ください。ローカルテーブルはHiveメタストアに登録されず、他のクラスターからはアクセスできません。これは一時ビューとも呼ばれます。

[Create Table UI](#uiによるテーブルの作成)、または、[プログラム](#プログラムでテーブルを作成)からテーブルを作成することができます。[DBFS](https://qiita.com/taka_yayoi/items/e16c7272a7feb5ec9a92)上のファイル、あるいはサポートされている[形式(英語)](https://docs.databricks.com/data/data-sources/index.html)のデータからテーブルにデータを取り込むことができます。

# 要件

データベース、テーブルを作成、参照するためには起動しているクラスターが必要です。

# データベースとテーブルの参照

サイドバーの![](https://docs.databricks.com/_images/data-icon.png)をクリックします。Databricksはあなたがアクセス権を持つクラスターを選択します。`default`データベースが選択された状態でDatabasesフォルダーが表示されます。Tablesフォルダには`default`データベースのテーブル一覧が表示されます。
![](https://docs.databricks.com/_images/default-database.png)

Databasesメニュー、[Create Table UI](#uiによるテーブルの作成)、[View Table UI](#テーブル詳細の参照)からクラスターを変更することができます。例えば、Databasesメニューであれば：

1. Databasesフォルダーの上部の![](https://docs.databricks.com/_images/down-caret.png)をクリックします。
1. クラスターを選択します。
![](https://docs.databricks.com/_images/cluster-select.png)

# データベースの作成

SQLでデータベースを作成するには：

**SQL**

```sql
CREATE DATABASE <database-name> ...
```

他の選択肢に関しては、こちらをご覧ください。

- Databricksランタイム 7.x以上: [CREATE DATABASE(英語)](https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-database.html)
- Databricksランタイム 5.5 LTS、6.x: [Create Database(英語)](https://docs.databricks.com/spark/2.x/spark-sql/language-manual/create-database.html)

# テーブルの作成

UIあるいはプログラムからテーブルを作成できます。

## UIによるテーブルの作成

> **注意**
UIでテーブルを作成する際、以下のことはできません。

> - [High Concurrency cluster(英語)](https://docs.databricks.com/clusters/configure.html#high-concurrency)を使用している際のファイルのアップロード。この場合、Databricksにデータをロードするためには、[Databricks File System (DBFS)](https://qiita.com/taka_yayoi/items/e16c7272a7feb5ec9a92)を使用してください。
> - テーブルのアップデート。この場合、[プログラム](#プログラムでテーブルを作成)からテーブルを作成してください。
 
UIからテーブルを作成するとグローバルテーブルになります。

1. サイドバーの![](https://docs.databricks.com/_images/data-icon.png)をクリックします。Databases、Tablesフォルダーが表示されます。
1. Databasesフォルダーでデータベースを選択します。
1. Tablesフォルダーの上部にある**Add Data**をクリックします。
![](https://docs.databricks.com/_images/add-table-icon.png)
1. データソースを選択し、テーブルを設定する手順に従います。
    - ファイルのアップロードはデフォルトでは有効になっています。Databricksの管理者がこの機能を[無効(英語)](https://docs.databricks.com/administration-guide/workspace/dbfs-ui-upload.html)にした場合、ファイルをアップロードすることはできませんが、他のデータソースにあるファイルを用いてテーブルを作成することはできます。
![](https://docs.databricks.com/_images/import-table-aws.png)
        - **ファイルのアップロード**
            - ファイルを**File**エリアにドラッグするか、**File**エリアをクリックし、ファイルを選択します。アップロード後にそれぞれのファイルパスが表示されます。ファイルパスは`/FileStore/tables/<filename>-<random-number>.<file-type>`と言った形式になりますので、ノートブックでファイルパスを使用してデータを読み込みます。
![](https://docs.databricks.com/_images/data-import-files.png)
            - **Create Table with UI**をクリックします。
            - データを参照するためのクラスターをClusterドロップダウンから選択します。
        - **S3**
            - **Create Table with UI**をクリックします。
            - クラスタードロップダウンからクラスターを選択します。
            - バケット名を入力します。選択したクラスターがS3にアクセスできるようになっている必要があります。
            - **Browse Bucket**をクリックします。
            - ファイルを選択します。

        - **DBFS**
            - ファイルを選択します。
            - **Create Table with UI**をクリックします。
            - クラスタードロップダウンからクラスターを選択します。

1. Preview Tableをクリックしてテーブルを参照します。
1. **Table Name**フィールドで、デフォルトのテーブル名を上書きすることができます。テーブル名には小文字のアルファベット、数字、アンダースコアを含めることができますが、最初の文字は小文字のアルファベットかアンダースコアに限られます。
1. **Create in Database**フィールドで、選択されている`default`データベースを変更することができます。
1. **File Type**で、識別されたファイルタイプを変更することができます。
1. CSVファイルの場合：
    1. **Column Delimiter**フィールドで、識別された区切り文字を変更することができます。
    1. 最初の行がタイトルであるかどうかを指定します。
    1. スキーマを推測するかどうかを指定します。
1. ファイルタイプがJSONの場合、ファイルがマルチラインなのかどうかを指定します。
1. **Create Table**をクリックします。

### ノートブックによるテーブルの作成

Create New Table UIには、あらゆるデータソースに接続できるDatabricksのクイックスタートノートブックを活用することができます。

- S3: **Create Table in Notebook**をクリックします。ノートブックをアタッチするクラスターがS3バケットにアクセスできるようになっている必要があります。
- DBFS: **Create Table in Notebook**をクリックします。
- 他のデータソース: Connectorのドロップダウンからデータソースタイプを選択し、**Create Table in Notebook**をクリックします。

## プログラムでテーブルを作成

本章では、どのようにしてプログラムでグローバルテーブル、ローカルテーブルを作成するのかを説明します。

### グローバルテーブルの作成

SQLでグローバルテーブルを作成するには以下を実行します。

**SQL**

```sql
CREATE TABLE <table-name> ...
```

他のオプションに関しては、Databricksランタイム 5.5 LTS及びDatabricksランタイム 6.4においては[Create Table(英語)](https://docs.databricks.com/spark/2.x/spark-sql/language-manual/create-table.html)、Databricksランタイム 7.1以降においては[CREATE TABLE(英語)](https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-table.html)を参照ください。

PythonやScalaでデータフレームからグローバルテーブルを作成するには以下を実行します。

**Python**

```python
dataFrame.write.saveAsTable("<table-name>")
```

### ローカルテーブルの作成

PythonやScalaでデータフレームからローカルテーブルを作成するには以下を実行します。

**Python**

```python
dataFrame.createOrReplaceTempView("<table-name>")
```

こちらは[Databricks File System \(DBFS\)](https://qiita.com/taka_yayoi/items/e16c7272a7feb5ec9a92)上のファイルから`diamonds`というローカルテーブルを作成する例となります。

**Python**

```python
dataFrame = "/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv"
spark.read.format("csv").option("header","true")\
  .option("inferSchema", "true").load(dataFrame)\
  .createOrReplaceTempView("diamonds")
```

# テーブルへのアクセス

テーブルに対して、詳細の参照、読み込み、アップデート、削除を行うことができます。

## テーブル詳細の参照

テーブル詳細ではテーブルスキーマとサンプルデータを参照できます。

1. サイドバーの![](https://docs.databricks.com/_images/data-icon.png)をクリックします。
1. Databasesフォルダーでデータベースをクリックします。
1. Tablesフォルダーでテーブル名をクリックします。
1. 必要に応じて、テーブルのプレビューをするためのクラスターをドロップダウンから選択します。
![](https://docs.databricks.com/_images/tables-view.png)

> **注意**
テーブルのプレビューを表示するためには、**Cluster**ドロップダウンで選択したクラスターでSpark SQLクエリーが実行されます。当該クラスターで別の処理が行われている場合、プレビューが表示されるまで時間がかかる場合があります。

## テーブルに対するクエリー

以下が、それぞれの言語で`diamonds`テーブルの内容を表示するサンプルになります。

**SQL**

```sql
SELECT * FROM diamonds
```

**Python**

```python
diamonds = spark.sql("select * from diamonds")
display(diamonds.select("*"))

diamonds = spark.table("diamonds")
display(diamonds.select("*"))
```

*R**

```R
diamonds <- sql(sqlContext, "select * from diamonds")
display(diamonds)

diamonds <- table(sqlContext, "diamonds")
display(diamonds)
```

**Scala**

```Scala
val diamonds = spark.sql("select * from diamonds")
display(diamonds.select("*"))

val diamonds = spark.table("diamonds")
display(diamonds.select("*"))
```

# テーブルの更新

テーブルスキーマは不変(immutable)です。ですが、バックエンドにあるファイルを変更することでテーブルを更新することができます。

例えば、S3のディレクトリーから作成されたテーブルにおいては、当該ディレクトリでファイルの追加・削除を行うことでテーブルの中身を更新できます。

バックエンドのファイルを更新した後は、以下のコマンドでテーブルをリフレッシュします。

**SQL**

```sql
REFRESH TABLE <table-name>
```

これにより、バックエンドのファイルで更新があったとしても、テーブルにアクセスする際にSpark SQLで正しい内容を読み取ることができます。

# テーブルの削除

## UIでテーブルを削除

1. サイドバーの![](https://docs.databricks.com/_images/data-icon.png)をクリックします。
1. テーブル名の隣にある![](https://docs.databricks.com/_images/menu-dropdown.png)をクリックし、**Delete**を選択します。

## プログラムでテーブルを削除

**SQL**

```sql
DROP TABLE <table-name>
```

# マネージド、アンマネージドテーブル

全てのSpark SQLテーブルは、スキーマを格納するメタデータ情報とデータ自身を持っています。

マネージドテーブルは、Sparkがメタデータとデータの両方を管理するSpark SQLテーブルです。マネージドテーブルの場合、DatabricksはメタデータとデータをあなたのアカウントのDBFSに格納します。Spark SQLがテーブルを管理するので、`DROP TABLE example_data`はメタデータとデータ両方を削除します。

マネージドテーブルを作成する一般的な方法は以下の通りとなります。

**SQL**

```sql
CREATE TABLE <example-table>(id STRING, value STRING)
```

**Python**

```python
dataframe.write.saveAsTable("<example-table>")
```

もう一つの選択肢は、Spark SQLにはメタデータを管理させ、データの場所はあなたがコントロールするというものです。我々はこれをアンマネージドテーブルと呼びます。Spark SQLはメタデータを管理しますので、`DROP TABLE example_data`を実行した際には、Sparkはメタデータのみを削除します。データ自身は削除しません。このため、あなたが指定した場所にデータは残り続けます。

CassandraやJDBCテーブルなどのデータソースにあるデータからアンマネージドテーブルを作成できます。Databricksでサポートしているデータタイプに関しては[Data sources(英語)](https://docs.databricks.com/data/data-sources/index.html)を参照ください。アンマネージドを作成する一般的な方法は以下の通りです。

**SQL**

```sql
CREATE TABLE <example-table>(id STRING, value STRING) USING org.apache.spark.sql.parquet OPTIONS (PATH "<your-storage-path>")
```

**Python**

```python
dataframe.write.option('path', "<your-storage-path>").saveAsTable("<example-table>")
```

## テーブルコンテンツの置換

テーブルコンテンツを置換する方法には2つあります。シンプルなものと推奨のものです。

### テーブルコンテンツ置換のシンプルな方法

テーブルコンテンツを置換するシンプルな方法は、テーブルのメタデータとデータを削除し、別のテーブルを作成するというものです。

#### マネージドテーブル

**SQL**

```sql
DROP TABLE IF EXISTS <example-table>     // deletes the metadata and data
CREATE TABLE <example-table> AS SELECT ...
```

#### アンマネージドテーブル

**SQL**

```sql
DROP TABLE IF EXISTS <example-table>    // deletes the metadata
```

**Python**

```python
dbutils.fs.rm("<your-s3-path>", true)   // deletes the data
```

**SQL**

```sql
CREATE TABLE <example-table> USING org.apache.spark.sql.parquet OPTIONS (PATH "<your-s3-path>") AS SELECT ...
```

> **警告!**
上記のアプローチは妥当なものですが、S3にデータがある場合には、削除したデータと同じ場所に再作成しようとした際に[S3 eventual consistency issues](https://docs.aws.amazon.com/AmazonS3/latest/dev/Introduction.html#ConsistencyModel)の問題に遭遇する場合があります。一貫性問題に関して詳細を知りたい方は、ブログ記事[S3mper: Consistency in the Cloud](https://medium.com/netflix-techblog/s3mper-consistency-in-the-cloud-b6a1076aa4f8)をご覧ください。

代替策としては、

1. SQL DLLを用いてテーブルを作成する。
`CREATE TABLE <table-name> (id long, date string) USING PARQUET LOCATION "<storage-location>"`

1. 新たな場所`<storage-location>`にデータを格納する。
1. `refresh table <table-name>`を実行する。

### テーブルコンテンツ置換の推奨方法

一貫性問題を回避するために、推奨するアプローチはテーブルを上書きするというものです。

**Python**

```python
dataframe.write.mode("overwrite").saveAsTable("<example-table>") // Managed Overwrite
dataframe.write.mode("overwrite").option("path","<your-s3-path>").saveAsTable("<example-table>")  // Unmanaged Overwrite
```

**SQL**

`insert overwrite`を追加います。この方法はマネージドテーブル、アンマネージドテーブルに適用できます。以下の例はアンマネージドテーブルに対するものです。

```sql
CREATE TABLE <example-table>(id STRING, value STRING) USING org.apache.spark.sql.parquet OPTIONS (PATH "<your-s3-path>")
INSERT OVERWRITE TABLE <example-table> SELECT ...
```

**Scala**

```Scala
dataframe.write.mode(SaveMode.Overwrite).saveAsTable("<example-table>")    // Managed Overwrite
dataframe.write.mode(SaveMode.Overwrite).option("path", "<your-s3-path>").saveAsTable("<example-table>")  // Unmanaged Overwrite
```

# テーブルのパーティション

Spark SQLは、テーブルに対するパーティション列を提供するために、ファイルストレージレベルで動的にパーティションを作成することができます。

## パーティションされたテーブルの作成

以下の例は書き込んだデータをパーティションするものです。Spark SQLはパーティションを検知し、Hiveメタストアに登録します。

**Scala**

```Scala
// Create managed table as select
dataframe.write.mode(SaveMode.Overwrite).partitionBy("id").saveAsTable("<example-table>")

// Create unmanaged/external table as select
dataframe.write.mode(SaveMode.Overwrite).option("path", "<file-path>").saveAsTable("<example-table>")
```

しかし、既存のデータからパーティションテーブルを作成する際は、Spark SQLは自動的にパーティションを検知し、Hiveメタストアに登録しません。この場合、`SELECT * FROM <example-table>`は結果を返しません。パーティションを登録するには、以下を実行してパーティションを作成します：`MSCK REPAIR TABLE "<example-table>"`

**Scala**

```Scala
// Save data to external files
dataframe.write.mode(SaveMode.Overwrite).partitionBy("id").parquet("<file-path>")

// Create unmanaged/external table
spark.sql("CREATE TABLE <example-table>(id STRING, value STRING) USING parquet PARTITIONED BY(id) LOCATION "<file-path>"")
spark.sql("MSCK REPAIR TABLE "<example-table>"")
```

## パーティションの刈り込み(pruning)

テーブルがスキャンされる際には、Sparkは`partitionBy`を含むフィルター条件をプッシュダウンします。この場合、Sparkはこれらの条件に合致しないデータを読みません。例えば、`<date>`でパーティションされたテーブル`<example-data>`がある場合、`SELECT max(id) FROM <example-data> WHERE date = '2010-10-10'`と言ったクエリーは、クエリーで指定された条件と合致する`date`を持つデータファイルのみを読み込みます。

# テーブルのアクセスコントロール

テーブルアクセスコントロールによって、管理者とユーザーは他のユーザーに対してきめ細かいアクセスを設定することができます。詳細は、[Databricksにおけるデータオブジェクトのアクセス権管理](https://qiita.com/taka_yayoi/items/225c4db5dffa51cbbbae)を参照ください。

### Databricks 無料トライアル

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