Databricks にて jdbc 経由で Azure SQL Database の CHAR 型のカラムに読み込みと書き込みを実施した際のスペースの取り扱いに関する調査結果

## 概要

Databricks を使用して Azure SQL Database の CHAR 型のカラムに対する読み書き操作を行う際のスペースの取り扱いについての調査結果を共有します。

具体的には、以下の4つのシナリオについて検証しています。各シナリオでは、テーブルの作成、データの挿入、データの参照、文字数の算出、データの書き込みなどを行っています。また、各シナリオの結果はPythonとSQLのコードを後述しています。

1. ANSI_PADDING ON 設定での Azure SQL Database テーブの参照検証
2. ANSI_PADDING OFF 設定での Azure SQL Database テーブルの参照検証
3. ANSI_PADDING ON 設定での Azure SQL Database テーブルへの書き込み検証
4. ANSI_PADDING OFF 設定での Azure SQL Database テーブルへの書き込み検証

検証結果としては、ANSI_PADDING が ON の場合に CHAR 型のカラムのデータを取得した際には、指定したバイトに満たない場合にはスペースが埋められて取得されることが確認されました。一方で、ANSI_PADDING が OFF の場合には、指定したバイトに満たない場合にはスペースが埋められずに取得されることが確認されました。Azure SQL Database にて Shift JIS （正確には CP932）で文字を保持する設定にしているため、`あいう`は 6 バイトと判断されて、スペースが 3 つ設定されています。

```text
[
    Row(char_col="abc       ", 文字数=10),
    Row(char_col="あいう    ", 文字数=7),
    Row(char_col="          ", 文字数=10),
    Row(char_col=None, 文字数=None),
]
```

ANSI_PADDING のデフォルト設定値は ON であるため、本記事にて ANSI_PADDING ON 設定で検証を行う際には SET ステートメントにて明示していません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5df127b8-232a-6e4f-ffb7-102a10ff8a44.png)

引用元：[SET ANSI_PADDING (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/ja-jp/sql/t-sql/statements/set-ansi-padding-transact-sql?view=sql-server-ver16&viewFallbackFrom=azuresqldb-current)

Databricks から書きこみを実施する際には、テーブルが再作成されないように、`truncate`オプションを設定することが必要です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/658c2c8b-5fb5-c6d1-b210-1f90ed72979e.png)

引用元：[JDBC To Other Databases - Spark 3.5.0 Documentation (apache.org)](https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html)

## 検証コードと実行結果

### 事前準備

Databricks にて以下のコードを実行。

```python
from pyspark.sql.functions import expr

server = "ads-01"  ## サーバー名
database = "ads-01"  ## データベース名
username = "user"  ## Azure SQL DB のユーザー名
password = "password"  ## Azure SQL DB のパスワード
````

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/db514128-c68d-b87b-d08c-2dbe70e7bc4b.png)

### 1. Azure SQL Database テーブル（`ANSI_PADDING ON`）の参照検証

Azure SQL Database にて以下のコードを実行して、テーブルの作成とデータの挿入を実施

```sql
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[char_test_01]') AND type in (N'U'))
DROP TABLE [dbo].[char_test_01]
GO

CREATE TABLE [dbo].[char_test_01]
(
    [char_col] char(10) COLLATE Japanese_CI_AS
)
GO

INSERT INTO [dbo].[char_test_01]
SELECT 'abc' AS char_col
UNION ALL
SELECT N'あいう' AS char_col
UNION ALL
SELECT '' AS char_col
UNION ALL
SELECT NULL AS char_col
GO

SELECT
	*
	FROM
		[dbo].[char_test_01]
GO
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8b096c44-6ac9-e83a-9dda-c7f523e79846.png)


Databricks にて以下のコードを実行して、Azure SQL Database のテーブルを参照したデータフレームを作成し、文字数を算出して表示。

```python
tgt_tbl_name = "[dbo].[char_test_01]"

sql_db_table_01 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("dbTable", tgt_tbl_name)
    .load()
)
sql_db_table_01.createOrReplaceTempView("tmp_view")
sql_db_table_01 = sql_db_table_01.withColumn("文字数", expr("len(char_col)"))

print(sql_db_table_01.collect())
sql_db_table_01.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/26d2465c-b8a9-6ccc-3d88-0e304fdc9b32.png)

```text
[
    Row(char_col="abc       ", 文字数=10),
    Row(char_col="あいう    ", 文字数=7),
    Row(char_col="          ", 文字数=10),
    Row(char_col=None, 文字数=None),
]
```

### 2. Azure SQL Database テーブル（`ANSI_PADDING OFF`）の参照検証

Azure SQL Database にて以下のコードを実行して、テーブルの作成とデータの挿入を実施

```sql
SET ANSI_PADDING OFF;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[char_test_02]') AND type in (N'U'))
DROP TABLE [dbo].[char_test_02]
GO
CREATE TABLE [dbo].[char_test_02]
(
    [char_col] char(10) COLLATE Japanese_CI_AS
)
GO

INSERT INTO [dbo].[char_test_02]
SELECT 'abc' AS char_col
UNION ALL
SELECT N'あいう' AS char_col
UNION ALL
SELECT '' AS char_col
UNION ALL
SELECT NULL AS char_col
GO

SELECT
	*
	FROM
		[dbo].[char_test_02]
GO
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e68587a8-a753-7281-37cc-be80cf8ab045.png)

Databricks にて以下のコードを実行して、Azure SQL Database のテーブルを参照したデータフレームを作成し、文字数を算出して表示。

```python
tgt_tbl_name = "[dbo].[char_test_02]"

sql_db_table_01 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("dbTable", tgt_tbl_name)
    .load()
)
sql_db_table_01.createOrReplaceTempView("tmp_view")
sql_db_table_01 = sql_db_table_01.withColumn("文字数", expr("len(char_col)"))

print(sql_db_table_01.collect())
sql_db_table_01.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5aa178d8-ce2c-4904-a1d6-36e84ced6bf8.png)

```text
[
    Row(char_col="abc", 文字数=3),
    Row(char_col="あいう", 文字数=3),
    Row(char_col=" ", 文字数=1),
    Row(char_col=None, 文字数=None),
]
```

### 3. Azure SQL Database テーブル（`ANSI_PADDING ON`）への書き込み検証

Azure SQL Database にて以下のコードを実行して、テーブルを作成。

```sql
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[char_test_03]') AND type in (N'U'))
DROP TABLE [dbo].[char_test_03]
GO

CREATE TABLE [dbo].[char_test_03]
(
    [char_col] char(10) COLLATE Japanese_CI_AS
)
GO

SELECT
	*
	FROM
		[dbo].[char_test_03]
GO
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/b0c43af0-644d-14e9-a048-dc4d22c32ef3.png)

Databricks にて以下のコードを実行して、Azure SQL Database のテーブルへデータを書きこみを実施。

```python
src_schema = "char_col string"
src_data = [
    {
        "char_col": "abc",
    },
    {
        "char_col": "あいう",
    },
    {
        "char_col": "",
    },
    {
        "char_col": None,
    },
]
df = spark.createDataFrame(src_data,src_schema)

tgt_tbl_name = "[dbo].[char_test_03]"

(
    df.write.format("sqlserver")
    .mode("overwrite")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("truncate", True)
    .option("dbTable", tgt_tbl_name)
    .save()
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/fa0484e8-d58f-069a-49b8-178ed64ea7ce.png)

Databricks にて以下のコードを実行して、Azure SQL Database のテーブルを参照したデータフレームを作成し、文字数を算出して表示。

```python
tgt_tbl_name = "[dbo].[char_test_03]"

sql_db_table_03 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("dbTable", tgt_tbl_name)
    .load()
)
sql_db_table_03 = sql_db_table_03.withColumn("文字数", expr("len(char_col)"))

print(sql_db_table_03.collect())
sql_db_table_03.display()
```



```text
[
    Row(char_col=None, 文字数=None),
    Row(char_col="          ", 文字数=10),
    Row(char_col="abc       ", 文字数=10),
    Row(char_col="あいう    ", 文字数=7),
]
```

### 4. Azure SQL Database テーブル（`ANSI_PADDING OFF`）への書き込み検証

Azure SQL Database にて以下のコードを実行して、テーブルを作成。

```sql
SET ANSI_PADDING OFF;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[char_test_04]') AND type in (N'U'))
DROP TABLE [dbo].[char_test_04]
GO

CREATE TABLE [dbo].[char_test_04]
(
    [char_col] char(10) COLLATE Japanese_CI_AS
)
GO

SELECT
	*
	FROM
		[dbo].[char_test_04]
GO
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/ff25bfd9-35c9-0ba7-a27e-a8384858d2cc.png)

Databricks にて以下のコードを実行して、Azure SQL Database のテーブルへデータを書きこみを実施。

```python
src_schema = "char_col string"
src_data = [
    {
        "char_col": "abc",
    },
    {
        "char_col": "あいう",
    },
    {
        "char_col": "",
    },
    {
        "char_col": None,
    },
]
df = spark.createDataFrame(src_data,src_schema)

tgt_tbl_name = "[dbo].[char_test_04]"

(
    df.write.format("sqlserver")
    .mode("overwrite")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("truncate", True)
    .option("dbTable", tgt_tbl_name)
    .save()
)
````

Databricks にて以下のコードを実行して、Azure SQL Database のテーブルを参照したデータフレームを作成し、文字数を算出して表示。

```python
tgt_tbl_name = "[dbo].[char_test_04]"

sql_db_table_04 = (
    spark.read.format("sqlserver")
    .option("host", f"{server}.database.windows.net")
    .option("user", username)
    .option("password", password)
    .option("database", database)
    .option("dbTable", tgt_tbl_name)
    .load()
)
sql_db_table_04 = sql_db_table_04.withColumn("文字数", expr("len(char_col)"))

print(sql_db_table_04.collect())
sql_db_table_04.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/62830e31-fbad-739d-bb00-32d189dfefa5.png)

```text
[
    Row(char_col="あいう", 文字数=3),
    Row(char_col="", 文字数=0),
    Row(char_col="abc", 文字数=3),
    Row(char_col=None, 文字数=None),
]
```

### 事後処理

Azure SQL Database にて以下のコードを実行して、Azure SQL Database に作成したテーブルを削除。

```sql
DROP TABLE [dbo].[char_test_01]
GO
DROP TABLE [dbo].[char_test_02]
GO
DROP TABLE [dbo].[char_test_03]
GO
DROP TABLE [dbo].[char_test_04]
GO
````

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/a60a9714-50af-d184-5176-362e8b54da19.png)