Databricks にてテーブルのカラムの値を CSV 形式、あるいは、 json 形式の単一カラムにする方法

## 概要

Databricks にてテーブルのカラムの値を CSV 形式、あるいは、 json 形式の単一カラムにする方法を共有します。

次の関数を利用します。

- [concat_ws 関数 - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/functions/concat_ws)
- [to_json 関数 - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/functions/to_json)

to_json 関数の第二引数にて指定できるオプションについては、 from_json 関数のドキュメントに記載されています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/4ee94cdf-3710-ccdd-e030-0c2fd957ac58.png)

引用元：[from_json 関数 - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/functions/from_json#returns)

記事の投稿後の検証後にいくつかのケースにて想定通りに動作しなかっため、記事投稿時のコードを`当初想定していコード`として残して、実行すべきコードを`検証結果後のコード`として更新することとしました。

## 当初想定していコード

### ベースとなるデータ

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "c" AS str_col,
    CAST(3 AS int) AS int_col,
    CAST('2020-03-01' AS date) AS date_col,
    CAST('2020-03-01' AS TIMESTAMP) AS ts_col
)
SELECT
    *
    FROM
        src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7674cc8f-ce7a-b521-578e-a7407646151d.png)

### CSV 形式での単一カラム化

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "c" AS str_col,
    CAST(3 AS int) AS int_col,
    CAST('2020-03-01' AS date) AS date_col,
    CAST('2020-03-01' AS TIMESTAMP) AS ts_col
)
SELECT
  to_csv(
    named_struct(
      'str_col',
      str_col,
      'int_col',
      int_col,
      'date_col',
      date_col,
      'ts_col',
      ts_col
    ),
    map(
      'sep',
      ',',
      -- ,'quote','"' -- `quote`の指定はできず
      'dateFormat',
      'yyyy-MM-dd',
      'timestampFormat',
      'yyyy-MM-dd\'T\'HH:mm:ss'
    )
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/01b4ea26-c710-fa65-15e2-d23d08ac0774.png)

### json 形式での単一カラム化

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "c" AS str_col,
    CAST(3 AS int) AS int_col,
    CAST('2020-03-01' AS date) AS date_col,
    CAST('2020-03-01' AS TIMESTAMP) AS ts_col
)
SELECT
  to_json(
    named_struct(
      'str_col',
      str_col,
      'int_col',
      int_col,
      'date_col',
      date_col,
      'ts_col',
      ts_col
    ),
    map(
      'dateFormat',
      'yyyy-MM-dd',
      'timestampFormat',
      'yyyy-MM-dd\'T\'HH:mm:ss'
    )
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7b4c6ba5-66aa-8e13-ac39-c6de0e942963.png)

## 検証結果後のコード

### ベースとなるデータ

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    NULL AS str_col,
    CAST(3 AS int) AS int_col,
    CAST(NULL AS date) AS date_col,
    CAST(NULL AS TIMESTAMP) AS ts_col
)
SELECT
    *
    FROM
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d6f10fa5-ca73-a0fe-da38-c7c8c20b999b.png)

### CSV 形式での単一カラム化（引用符を指定しない場合）

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    NULL AS str_col,
    CAST(3 AS int) AS int_col,
    CAST(NULL AS date) AS date_col,
    CAST(NULL AS TIMESTAMP) AS ts_col
)
SELECT
  concat_ws(
    ",",
    ifnull(str_col,""),
    ifnull(int_col,""),
    ifnull(date_col,""),
    ifnull(ts_col ,"")
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/ce9a0a7a-bf34-7e3f-f60e-502682fc112b.png)

### CSV 形式での単一カラム化（引用符を指定する場合）

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    NULL AS str_col,
    CAST(3 AS int) AS int_col,
    CAST(NULL AS date) AS date_col,
    CAST(NULL AS TIMESTAMP) AS ts_col
)
SELECT
  concat_ws(
    ",",
    '"' || ifnull(str_col,"") || '"',
    '"' || ifnull(int_col,"") || '"',
    '"' || ifnull(date_col,"") || '"',
    '"' || ifnull(ts_col ,"") || '"'
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/430163e3-3fbe-6110-ad8e-30472defddc5.png)

### json 形式での単一カラム化（NULL の項目を表示する場合）

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    NULL AS str_col,
    CAST(3 AS int) AS int_col,
    CAST(NULL AS date) AS date_col,
    CAST(NULL AS TIMESTAMP) AS ts_col
)
SELECT
  to_json(
    named_struct(
      'str_col',
      ifnull(str_col, ''),
      'int_col',
      ifnull(int_col, ''),
      'date_col',
      ifnull(date_col, ''),
      'ts_col',
      ifnull(ts_col, '')
    )
    -- `ifnull`関数にて文字列となってしまうためフォーマットの指定が不可であり、`date_format`等を利用する必要あり
    -- ,
    -- 
    -- map(
    --   'dateFormat',
    --   'yyyy-MM-dd',
    --   'timestampFormat',
    --   'yyyy-MM-dd\'T\'HH:mm:ss'
    -- )
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c43d0d27-914f-f412-63f5-fed5b3226a3f.png)

### json 形式での単一カラム化（NULL の項目を表示しない場合）


```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    NULL AS str_col,
    CAST(3 AS int) AS int_col,
    CAST(NULL AS date) AS date_col,
    CAST(NULL AS TIMESTAMP) AS ts_col
)
SELECT
  to_json(
    named_struct(
      'str_col',
      str_col,
      'int_col',
      int_col,
      'date_col',
      date_col,
      'ts_col',
      ts_col
    ),
    map(
      'dateFormat',
      'yyyy-MM-dd',
      'timestampFormat',
      'yyyy-MM-dd\'T\'HH:mm:ss'
    )
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/0e7d31e1-2dae-9fe4-e8c4-4341b7c49b19.png)


## 検証結果

### 1. `to_csv`利用時に NULL が含まれる場合に、項目値が空白ではな、`""`になってしまう

対策として、`to_csv`関数ではなく、`concat_ws`関数に変更することとした。

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    NULL AS str_col,
    CAST(NULL AS int) AS int_col,
    CAST(NULL AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
)
SELECT
  to_csv(
    named_struct(
      'str_col',
      str_col,
      'int_col',
      int_col,
      'date_col',
      date_col,
      'ts_col',
      ts_col
    ),
    map(
      'sep',
      ',',
      'dateFormat',
      'yyyy-MM-dd',
      'timestampFormat',
      'yyyy-MM-dd\'T\'HH:mm:ss'
    )
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7c8923c0-ecba-159a-8da9-0df15091910c.png)

### ~~2. `to_csv`利用時に引用符（`quote`）オプションを指定できない~~

記事投稿当初は`quote`オプションを設定する方法が不明であったが、サポートの方の協力により`quoteAll`オプションをつけることで設定できることが判明。ただし、設定できる文字が1文字しかできないため、`引用符を指定する場合`のコードを追加することした。

`quote`を設定しただけでは引用符を設定することができなかった。

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "b" AS str_col,
    CAST(2 AS int) AS int_col,
    CAST('2020-02-01' AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    "c" AS str_col,
    CAST(3 AS int) AS int_col,
    CAST('2020-03-01' AS date) AS date_col,
    CAST('2020-03-01' AS TIMESTAMP) AS ts_col
)
SELECT
  to_csv(
    named_struct(
      'str_col',
      str_col,
      'int_col',
      int_col,
      'date_col',
      date_col,
      'ts_col',
      ts_col
    ),
    map(
      'quote'
      ,'"'
    )
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/540348ca-e071-e7c2-243f-f8ed2ed3837f.png)

`quote`オプションと`quoteAll`オプションを指定することで引用符を設定することができた。

```python
%sql
WITH src AS (
   SELECT
     "a" AS str_col,
     CAST(1 AS int) AS int_col,
     CAST('2020-01-01' AS date) AS date_col
   UNION ALL
   SELECT
     "b" AS str_col,
     CAST(2 AS int) AS int_col,
     CAST('2020-02-01' AS date) AS date_col
   UNION ALL
   SELECT
     "c" AS str_col,
     CAST(3 AS int) AS int_col,
     CAST('2020-03-01' AS date) AS date_col
)
SELECT
   to_csv(
     named_struct(
       'str_col',
       str_col
       ,'int_col',
       int_col,
       'date_col',
       date_col
     )
     ,
     map(
       "quote", '"'
       ,"quoteAll", "true"
     )
   ) AS col_values
FROM
   src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5919cf1e-bd98-6a54-426e-a1720d94cbc9.png)


`quote`オプションと`quoteAll`オプションについては Spark のドキュメントに記載あり。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d293a2fc-e47d-0d14-2b16-325cf8030a58.png)

引用元：[CSV Files - Spark 3.5.0 Documentation (apache.org)](https://spark.apache.org/docs/latest/sql-data-sources-csv.html#data-source-option)


### 3. `to_json`利用時に NULL が含まれる場合に、項目が表示されない

対策として`NULL の項目を表示する場合`のコードを追加することした。

```python
%sql
WITH src AS (
  SELECT
    "a" AS str_col,
    CAST(1 AS int) AS int_col,
    CAST('2020-01-01' AS date) AS date_col,
    CAST('2020-01-01' AS TIMESTAMP) AS ts_col
  UNION ALL
  SELECT
    NULL AS str_col,
    CAST(NULL AS int) AS int_col,
    CAST(NULL AS date) AS date_col,
    CAST('2020-02-01' AS TIMESTAMP) AS ts_col
)
SELECT
  to_json(
    named_struct(
      'str_col',
      str_col,
      'int_col',
      int_col,
      'date_col',
      date_col,
      'ts_col',
      ts_col
    ),
    map(
      'dateFormat',
      'yyyy-MM-dd',
      'timestampFormat',
      'yyyy-MM-dd\'T\'HH:mm:ss'
    )
  ) AS col_values
FROM
  src
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8b376e38-e03d-2edd-19d3-9cb164b6cf01.png)