Databricks ( Spark ) にて利用できる文字型である char 型と varchar 型 の検証

概要

Databricks ( Spark ) にて利用できる文字型である char 型 と varchar 型 の検証した結果を共有します。

Spark では、文字型として利用できるデータ型として、string型だけでなく、char型と varchar型があります。char型と varchar型では、string型とは異なり、文字の長さを指定できます。ただし、バイト数を指定する方法はなく、文字数として指定する必要があるようです。

• String type
  - StringType: Represents character string values.
  - VarcharType(length): A variant of StringType which has a length limitation. Data writing will fail if the input string exceeds the length limitation. Note: this type can only be used in table schema, not functions/operators.
  - CharType(length): A variant of VarcharType(length) which is fixed length. Reading column of type CharType(n) always returns string values of length n. Char type column comparison will pad the short one to the longer length.

引用元：Data Types - Spark 3.3.1 Documentation (apache.org)

• 文字列型
  - StringType: 文字列値を表します。
  - VarcharType(length): 長さに制限がある StringType のバリアント。入力文字列が長さ制限を超えると、データの書き込みに失敗します。注: このタイプは、関数/演算子ではなく、テーブル スキーマでのみ使用できます。
  - CharType(length): 固定長の VarcharType(length) のバリアント。 CharType(n) 型の列を読み取ると、常に長さ n の文字列値が返されます。 Char タイプの列の比較では、短い方が長い方にパディングされます。

上記の翻訳

Databricks のドキュメント上ではchar型と varchar型の記載を確認できませんでしたが、動作はしました。

引用元：データ型 (Databricks SQL) - Azure Databricks - Databricks SQL | Microsoft Learn

char型で文字の長さを指定する際には、バイト数を指定する方法ではなく、文字数として指定する方法しなかないことに注意が必要です。オラクルデータベースではバイト単位で指定することが明示されており、そのような仕様をもつ RDB から移行する場合には想定外の仕様となってしまいます。

引用元：Oracleデータ型

char型へのデータ挿入時には、自動でスペース埋めされないような動作をしており、設定した桁数に満たない場合に想定外の動作をしました。下記例では、char_colをchar(4)として定義しており、4文字に満たないaの値を挿入しております。char_col列にて、a、及び、a でフィルター条件としたクエリを発行した際に、データを取得することができませんでした。char型を利用する際には動作を検証してから利用することをおすすめします。

動作検証

1. データベースを作成

%sql
DROP DATABASE IF EXISTS _qiita CASCADE;
CREATE DATABASE _qiita;

2. テーブル作成とテーブル定義を確認

テーブル定義を確認する場合には char 型とvarchar 型になっていますが、Spark データフレームとして読むこむとstring型となりました。

%sql
CREATE OR REPLACE TABLE _qiita.tbl_001
(
  string_col string,
  char_col char(4),
  varchar_col varchar(4)
)
;

%sql
DESC EXTENDED _qiita.tbl_001

tbl_name = '_qiita.tbl_001'
spark.table(tbl_name).printSchema()

3. データを挿入後にデータ取得

char 型のカラムでは、aとa のフィルター条件でデータ抽出ができました。

%sql
INSERT INTO _qiita.tbl_001
SELECT
  'a'
  ,'a   '
  ,'a'

print(spark.table('_qiita.tbl_001').toPandas().to_dict())

%sql
SELECT
  *
  FROM
    _qiita.tbl_001
  WHERE 
    char_col = 'a'

%sql
SELECT
  *
  FROM
    _qiita.tbl_001
  WHERE 
    char_col = 'a   '

%sql
SELECT
  *
  FROM
    _qiita.tbl_001
  WHERE 
    varchar_col = 'a'

%sql
SELECT
  *
  FROM
    _qiita.tbl_001
  WHERE 
    varchar_col = 'a   '

4. 日本語の文字を挿入

日本語の文字を、char(1)のカラムに挿入できることを確認

%sql
CREATE OR REPLACE TABLE _qiita.tbl_002
(
  string_col string,
  char_col char(1),
  varchar_col varchar(1)
)
USING delta
;

%sql
INSERT INTO _qiita.tbl_002
SELECT
  'あ'
  ,'あ'
  ,'あ'
 

%sql
SELECT
  *
  FROM 
    _qiita.tbl_002

4. 指定桁数以上の文字を挿入してエラーであることを確認

SQL でデータ挿入する場合だけでなく、Spark データフレームで書き込む場合にも、想定通りエラーとなりました。

com.databricks.sql.transaction.tahoe.schema.DeltaInvariantViolationException: Exceeds char/varchar type length limitation. Failed check: (isnull('char_col) OR (length('char_col) <= 1)).

%sql
CREATE OR REPLACE TABLE _qiita.tbl_003
(
  string_col string,
  char_col char(4),
  varchar_col varchar(4)
)
USING delta
;

%sql
INSERT INTO _qiita.tbl_003
SELECT
  'a'
  ,'a     '
  ,'a'

%sql
INSERT INTO _qiita.tbl_003
SELECT
  'a'
  ,'a'
  ,'a     '

tbl_name = "`_qiita`.`tbl_002`"
tbl_location = (
    spark.sql(f"DESC EXTENDED {tbl_name}")
    .where("col_name = 'Location'")
    .select("data_type")
    .first()[0]
)

data = [
    {
        "string_col": "a",
        "char_col": "あいうえお",
        "varchar_col": "あいうえ",
    }
]
 
df = spark.createDataFrame(data)

df.write.mode('append').saveAsTable(tbl_name)

df.write.mode('append').save(tbl_location)

data = [
    {
        "string_col": "a",
        "char_col": "あいうえ",
        "varchar_col": "あいうえお",
    }
]
 
df = spark.createDataFrame(data)

df.write.mode('append').saveAsTable(tbl_name)

df.write.mode('append').save(tbl_location)

5. char型のカラムに指定桁数に満たない値を挿入したときの動作を確認

指定桁数に満たないaの値を挿入後、aとa のフィルター条件でデータ抽出ができないという想定外の動作をしました。フィルター条件をカラムの桁数より多いa の値やa の値をフィルター条件にした場合には想定通りの抽出が行えました。桁数が多い場合には、Char type column comparison will pad the short one to the longer length. というドキュメント上の記載の動作をしているようです。

%sql
CREATE OR REPLACE TABLE _qiita.tbl_003
(
  string_col string,
  char_col char(4),
  varchar_col varchar(4)
)
;
INSERT INTO _qiita.tbl_003
SELECT
  'a'
  ,'a'
  ,'a   '

%sql
SELECT
  *
  FROM
    _qiita.tbl_003
  WHERE 
    char_col = 'a'

%sql
SELECT
  *
  FROM
    _qiita.tbl_003
  WHERE 
    char_col = 'a   '
``

```python
%sql
SELECT
  *
  FROM
    _qiita.tbl_003
  WHERE 
    char_col = 'a    '

%sql
SELECT
  *
  FROM
    _qiita.tbl_003
  WHERE 
    char_col = 'a            '

%sql
SELECT
  *
  FROM
    _qiita.tbl_003
  WHERE 
    varchar_col = 'a'

%sql
SELECT
  *
  FROM
    _qiita.tbl_003
  WHERE 
    varchar_col = 'a   '

%sql
SELECT
  *
  FROM
    _qiita.tbl_003
  WHERE 
    varchar_col = 'a           '

6. 本手順で作成したリソースを削除

%sql
DROP DATABASE IF EXISTS _qiita CASCADE;