DatabricksにてCTAS（CREATE TABLE AS SELECT）を利用する方法

## 概要
DatabricksのドキュメントにてCTAS（CREATE TABLE AS SELECT）が利用可能であるとの記載を見つけたため検証内容を共有します。一度作成したテーブルは、"CREATE OR REPLACE TABLE"で再作成が可能です。

```python
%sql
create or replace table ctas.ctas_flights_summary_data
  using delta
  location "dbfs:/ctas/ctas_flights_summary_data"
  AS select count(*) as count from ctas.flights_summary_data;
```

なお、CTAS相当のことを、データフレームで上書きしたとしても、下記のようなシンプルなコードで済みます。

```python
df_update = spark.sql("""
select count(*) as count from ctas.flights_summary_data
""")

df_update.write.format("delta").mode("overwrite").saveAsTable("ctas.ctas_flights_summary_data")
```

## 手順
### 1. データの準備
```python
# データの準備
filepath = "/databricks-datasets/learning-spark-v2/flights/summary-data/csv/*"

schema = """
  DEST_COUNTRY_NAME STRING
  ,ORIGIN_COUNTRY_NAME STRING
  ,count INT
"""

df = (spark
        .read
        .format("csv")
        .schema(schema)
        .option("header", "true")
        .option("inferSchema", "False")
        .load(filepath)
     )

df.write.format("delta").mode("overwrite").save("dbfs:/ctas/flights_summary_data")
```

```python
%sql
create database if NOT EXISTS  ctas;
create table if NOT EXISTS ctas.flights_summary_data
  using delta
  location "dbfs:/ctas/flights_summary_data";
```

```python
%sql
-- データ確認
select count(*) as count from ctas.flights_summary_data
```

### 2. CTASのテーブルを作成
```python
%sql
create table if not exists ctas.ctas_flights_summary_data
  using delta
  location "dbfs:/ctas/ctas_flights_summary_data"
  AS select count(*) as count from ctas.flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/660092c1-9fdd-a301-5704-87fa7c9f3db8.png)

念のため、データの確認。

```python
%sql
select * from ctas.ctas_flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/33e3d278-5047-eb0d-e429-cfbc5cbd270d.png)


### 3. REFRESHにより更新されないことの確認
CTASテーブルの元テーブルのデータを削除して更新を実施。

```python
%sql
truncate table ctas.flights_summary_data;
select count(*) as count from ctas.flights_summary_data;
```

この時点では、CTASのテーブルはデータ削除前のデータを保持。

```python
%sql
-- データ確認
select * from ctas.ctas_flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d831849e-434c-b01b-5b43-793877dc4129.png)

CTASテーブルの更新処理を実施。

```python
%sql
refresh table ctas.ctas_flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a1613c9f-b6c8-2c58-a53a-091f2324d5c6.png)

データが更新されていないことを確認。

```python
%sql
-- データ確認
select * from ctas.ctas_flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c33198eb-f8d6-3d0c-3276-dd4025544e58.png)

### 4. DROPとCREATEだけではエラー
先ほどのCTASテーブルと同様のディレクトリにデータがある場合に、CTASを実施するとエラーとなるようです。

```python
%sql
drop table if exists ctas.ctas_flights_summary_data;
create table if not exists ctas.ctas_flights_summary_data
  using delta
  location "dbfs:/ctas/ctas_flights_summary_data"
  AS select count(*) as count from ctas.flights_summary_data;
```
> 
Error in SQL statement: AnalysisException: Cannot create table ('`ctas`.`ctas_flights_summary_data`'). The associated location ('dbfs:/ctas/ctas_flights_summary_data') is not empty but it's not a Delta table

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c2c6a21b-0d44-d15b-6f5c-cefc13c1b515.png)


### 5. ディレクトリのデータを削除してから、DROPとCREATEを実施することでCTASテーブルが更新される

```python
dbutils.fs.rm("dbfs:/ctas/ctas_flights_summary_data", True)
```

```python
%sql
drop table if exists ctas.ctas_flights_summary_data;
create table if not exists ctas.ctas_flights_summary_data
  using delta
  location "dbfs:/ctas/ctas_flights_summary_data"
  AS select count(*) as count from ctas.flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c88bf521-90db-4bdb-1665-c1f69c6b01e8.png)

0と表示されて、データが更新されたことを確認。

```python
%sql
-- データ確認
select * from ctas.ctas_flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a421a5cd-2d9b-bd79-9b26-0aa4e3e7e833.png)

### 6. CREATE OR REPLACE TABLEを実施することでCTASテーブルが更新される
```python
%sql
create or replace table ctas.ctas_flights_summary_data
  using delta
  location "dbfs:/ctas/ctas_flights_summary_data"
  AS select count(*) as count from ctas.flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/110f2d5d-4ef0-a80a-ffbb-34b5a6ac5dee.png)

0と表示されて、データが更新されたことを確認。

```python
%sql
-- データ確認
select * from ctas.ctas_flights_summary_data;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d01a1a95-3760-9c6e-108d-fb48093653b4.png)


### 7. 本検証で利用したリソースを削除

```python
dbutils.fs.rm("/ctas", True)
```

```python
%sql
drop database ctas CASCADE;
```


## 関連ドキュメント
- [-Azure Databricks を使用した CREATE TABLE - Workspace | Microsoft Docs](https://docs.microsoft.com/ja-jp/azure/databricks/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-table-datasource)
- [テーブルの更新-Azure Databricks - Workspace | Microsoft Docs](https://docs.microsoft.com/ja-jp/azure/databricks/spark/latest/spark-sql/language-manual/sql-ref-syntax-aux-cache-refresh-table)

## 更新内容
記事投稿後、Delta Lake形式であれば、"CREATE OR REPLACE TABLE"によりCTASテーブルの再作成が実施できたため、記事の内容の一部を修正しました。