Databricks にて JSON ソースの場合における浮動小数点型のフィールドの値に基づきデータ型推論する際の落とし穴

## 概要

Databricks で JSON 形式のデータをソースとして取り込む際、浮動小数点型（`float`）のフィールドが整数型（`long`）として推論されるケースを確認しました。

Databricks が JSON フィールドのデータ型を推論する際、値が `123` のような整数であれば `long`、`123.12` のように小数部を含む場合は `double` として認識します。

```json
{"id": 1, "temperature": 36.5}
```

```text
root
 |-- id: long (nullable = true)
 |-- temperature: double (nullable = true)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/f6ebb32d-a863-4539-864f-c05ff4fc5bdb.png)

しかし、システムによっては `123.0` のような値から末尾の `0` が自動的に取り除かれ、結果として `123` として送信される場合があります。下記の JavaScript 例でも同様の動作を確認できます。

```javascript
const payload = {
  "id": 2,
  "temperature": 123.0
};

console.log(JSON.stringify(payload, null, 2));
```

```json
{
  "id": 2,
  "temperature": 123
}
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/27b3a6a5-9b6c-4d47-87ff-c69b61d96f47.png)

たとえば初回に `36` という値だけが到着し `bigint` 型でスキーマが固定された状態で、後から `36.1` が届くと取り込みに失敗し、該当レコードは `_rescued_data` 列へ格納されてしまいます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7db2cb24-3252-49ec-b935-2fbfb91bb93e.png)

この問題への対応策は主に次の 2 通りです。可能であればソース側で小数点以下を明示する方法が推奨されます。

1. **ソース側で小数点以下を記述する方法**
2. **文字列として読み取った後にデータ型を変換する方法**
3. **文字列として保持してビューでデータ型を変更する方法**

## 対応方法

### 1. ソース側で小数点以下を記述する方法

小数部が 0 の場合でも `.0` を明示して送信します。

```json
{
  "id": 2,
  "temperature": 123.0
}
```

### 2. 文字列として読み取り、データ型の変更を行う方法

`primitivesAsString` オプションを `true` に設定して文字列として読み込み、あとから `CAST` で型変換します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/1f78cc90-d15c-4273-8eb1-be4a3ec57f87.png)

出所：[JSON Files - Spark 4.0.0 Documentation](https://spark.apache.org/docs/latest/sql-data-sources-json.html)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d9a10bc0-df51-4f8d-bb38-2518dad0ef07.png)

出所：[from\_json 関数 - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/functions/from_json)

LDP（旧 DLT）で `CAST` を行う場合は、`SchemaHints` に変換対象のカラムを指定する必要があります。指定しないと「カラムが存在しない」というエラーとなります。

```python
schema = """
temperature STRING,
id STRING,
_corrupt_record STRING
"""
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/850f00a1-5776-4046-af05-1cf28240274f.png)

### 3. 文字列として保持してビューでデータ型を変更する方法

```sql
%sql
CREATE VIEW IF NOT EXISTS workspace.json_schema_01.v_from_json_test_01
AS
SELECT
  *,
  named_struct(
      'temperature', CAST(value_by_from_json.id AS INT),
      'humidity'   , CAST(value_by_from_json.temperature AS DOUBLE)
  ) AS casted_value_by_from_json
FROM workspace.json_schema_01.from_json_test_01;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6a3475ff-2826-4eb5-aec7-6611bf2f453b.png)


## その他の検討事項

### Schema（SchemaHints）の指定だけではスキーマ拡張時に不適切

`Schema` や `SchemaHints` だけを指定していると、スキーマ拡張を許容した場合にデータ型がソースデータに応じて推論されてしまい、意図しない型になる恐れがあります。

```python
schema = """
id bigint,
temperature double,
_corrupt_record STRING
"""
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/072dbd7b-71d7-4d98-870f-d348d886a320.png)

### 型の拡張（Type widening）では `long` から `double` への自動変換は不可

ドキュメントには次のように記載されています。

> タイプ変更が byte、short、int、または long から decimal または double のいずれでもない。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/ecf2c9fe-e941-45e4-8f3b-0deac9870467.png)

出所：[型の拡張 | Databricks Documentation](https://docs.databricks.com/aws/ja/delta/type-widening#自動スキーマ進化によるタイプの幅を広げる)

### Streaming テーブルでは手動での型の拡張（Type widening）は不可

ALTER STREAMING TABLE 文ではデータ型の変更をサポートしていないため、Streaming テーブルに対する手動での型の拡張（Type widening）は実施不可能です。

```sql
%sql
ALTER STREAMING TABLE workspace.json_schema_01.from_json_test_01
  ALTER COLUMN value_by_from_json.id
  TYPE float;
```

```text
[UNSUPPORTED_ALTER_COLUMN_PARAMETER] Specifying TYPE with ALTER STREAMING TABLE is not supported. SQLSTATE: 0A000
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7655dbf1-40bc-4c9f-94fe-0fcb976cf6f6.png)