![[新機能] Amazon RedshiftがCase-Insensitive CollationのデータベースでSUPERデータ型をサポートしました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift?w=3840&fm=webp)
[新機能] Amazon RedshiftがCase-Insensitive CollationのデータベースでSUPERデータ型をサポートしました
クラウド事業本部の石川です。Amazon Redshiftは、Case-Insensitive（大文字小文字を区別しない）データベースでJSON形式のデータを扱うSUPER型が使えませんでしたが、ついにサポートされました。柔軟なデータ構造を持つSUPER型の利点と、大文字小文字を意識しない検索・集計の両立が可能になります。今回は、case-insensitiveデータベースを作成し、COLLATE関数でcase-sensitive指定を行った場合と行わない場合の動作を検証してみます。
Case-Insensitive Collationのデータベースとは？
Amazon RedshiftにおけるCase-Insensitive Collation（大文字・小文字を区別しない照合順序）のデータベースとは、その名の通り、文字列データの検索や比較において、アルファベットの大文字（'A'）と小文字（'a'）を「同じもの」として扱う設定がされたデータベースのことです。
通常、Redshiftのデフォルト設定では「Case-Sensitive（区別する）」となっており、Appleとappleは別の文字列として扱われますが、この設定を有効にするとそれらが等価（Equal）として処理されます。
最もわかりやすい違いは、SELECT文での検索やGROUP BYでの集計時の挙動です。
| 操作 | デフォルト (Case-Sensitive) | Case-Insensitive (CI) |
|---|---|---|
比較 (=) |
'Apple' = 'apple' は False (不一致) |
'Apple' = 'apple' は True (一致) |
| 検索 | WHERE name = 'bob' は 'Bob' にヒットしない |
WHERE name = 'bob' は 'Bob' にヒットする |
並び替え (ORDER BY) |
大文字が先、小文字が後になる | 大文字小文字を区別せず、辞書順に近い並びになる |
集計 (GROUP BY) |
'Apple'と'apple'は別のグループになる |
'Apple'と'apple'は1つのグループにまとめられる |
検証用サンプルデータ
今回は、prettify形式のサンプルデータを用意しました。nameやemailの大文字・小文字が異なるデータです。
{"id": 1, "data": {"name": "ALICE", "email": "ALICE@example.com", "age": 10, "created_at": "2025-01-15T10:30:00Z"}}
{"id": 2, "data": {"name": "Alice", "email": "Alice@example.com", "age": 20, "created_at": "2025-01-16T14:20:00Z"}}
{"id": 3, "data": {"name": "alice", "email": "alice@example.com", "age": 30, "created_at": "2025-01-17T09:15:00Z"}}
Case-Insensitive Collationのデータベース作成
case_insensitiveなデータベースcic_dbを作成します。
devio=# CREATE DATABASE cic_db COLLATE case_insensitive;
CREATE DATABASE
cic_dbに切り替えて、db_collationを確認します。
cic_db=# SELECT db_collation();
db_collation
----------------
case_insensitive
(1 row)
SUPER型のテーブル作成
cic_db=# CREATE TABLE hybrid_tbl (
cic_db(# id INTEGER,
cic_db(# data SUPER
cic_db(# ) SORTKEY(id);
CREATE TABLE
cic_db=# COPY public.hybrid_tbl
cic_db-# FROM 's3://cm-datalake-20251123-vg/'
cic_db-# IAM_ROLE 'arn:aws:iam::123456789012:role/AmazonRedshiftSpectrumRole'
cic_db-# FORMAT AS JSON 'auto';
INFO: Load into table 'hybrid_tbl' completed, 3 record(s) loaded successfully.
COPY
cic_db=# SELECT
id,
data.name AS name
FROM hybrid_tbl;
id | name
----+---------
1 | "ALICE"
2 | "Alice"
3 | "alice"
(3 rows)
動作検証
データの確認
大文字のみ、大文字小文字混在、小文字のみのデータが入っていることを確認できます。
cic_db=# SELECT data.name FROM hybrid_tbl;
name
---------
"ALICE"
"Alice"
"alice"
(3 rows)
COLLATE関数（case_sensitive）によるフィルタ
data.nameカラムをCOLLATE(data.name, 'case_sensitive')のように指定することで、大文字小文字区別して検索可能になります。以下は、大文字小文字区別して'alice'を指定した場合です。
cic_db=# SELECT data.name FROM hybrid_tbl
cic_db-# WHERE COLLATE(data.name, 'case_sensitive') = 'alice';
name
---------
"alice"
(1 row)
もちろん、大文字小文字区別ありで'ALICE'を指定した場合も結果は同様です。
cic_db=# SELECT data.name FROM hybrid_tbl
WHERE COLLATE(data.name, 'case_sensitive') = 'ALICE';
name
---------
"ALICE"
(1 row)
COLLATE関数（case_sensitive）によるソート
大文字小文字区別するのでASCとDESCで並びが逆になりました。
cic_db=# SELECT data.name FROM hybrid_tbl
cic_db-# ORDER BY data.name DESC;
name
---------
"ALICE"
"Alice"
"alice"
(3 rows)
cic_db=# SELECT data.name FROM hybrid_tbl
cic_db-# ORDER BY COLLATE(data.name, 'case_sensitive') DESC;
name
---------
"alice"
"Alice"
"ALICE"
(3 rows)
cic_db=# SELECT data.name FROM hybrid_tbl
cic_db-# ORDER BY COLLATE(data.name, 'case_sensitive') ASC;
name
---------
"ALICE"
"Alice"
"alice"
(3 rows)
COLLATE関数（case_sensitive）による集計
大文字小文字区別してそれぞれ集計していることがわかります。
cic_db=# SELECT data.name, count(*) AS num
cic_db-# FROM hybrid_tbl a
cic_db-# GROUP BY data.name;
name | num
---------+-----
"ALICE" | 3
(1 row)
cic_db=# SELECT COLLATE(data.name, 'case_sensitive'), count(*) AS num
cic_db-# FROM hybrid_tbl
cic_db-# GROUP BY COLLATE(data.name, 'case_sensitive');
collate | num
---------+-----
"ALICE" | 1
"Alice" | 1
"alice" | 1
(3 rows)
COLLATE関数（case_sensitive）による結合
大文字小文字区別しているため、'alice'と同じレコードと結合されていることが確認できます。
cic_db=# SELECT data.name
cic_db-# FROM hybrid_tbl a
cic_db-# INNER JOIN (SELECT 'alice' AS name) b
cic_db-# ON a.data.name = b.name;
name
---------
"ALICE"
"Alice"
"alice"
(3 rows)
cic_db=# SELECT data.name
cic_db-# FROM hybrid_tbl a
cic_db-# INNER JOIN (SELECT 'alice' AS name) b
cic_db-# ON COLLATE(a.data.name, 'case_sensitive') = b.name;
name
---------
"alice"
(1 row)
COLLATE関数（case_sensitive）による比較
大文字小文字区別するため、data.nameが完全一致する比較のみT(一致)となります。
cic_db=# SELECT
cic_db-# data.name,
cic_db-# data.name = 'ALICE' AS uppercase,
cic_db-# data.name = 'Alice' AS camelcase,
cic_db-# data.name = 'alice' AS lowercase
cic_db-# FROM hybrid_tbl;
name | uppercase | camelcase | lowercase
---------+-----------+-----------+-----------
"ALICE" | t | t | t
"Alice" | t | t | t
"alice" | t | t | t
(3 rows)
cic_db=# SELECT
cic_db-# data.name,
cic_db-# COLLATE(data.name, 'case_sensitive') = 'ALICE' AS uppercase,
cic_db-# COLLATE(data.name, 'case_sensitive') = 'Alice' AS pascalcase,
cic_db-# COLLATE(data.name, 'case_sensitive') = 'alice' AS lowercase
cic_db-# FROM hybrid_tbl;
name | uppercase | pascalcase | lowercase
---------+-----------+------------+-----------
"ALICE" | t | f | f
"Alice" | f | t | f
"alice" | f | f | t
(3 rows)
最後に
今回の検証で、Case-InsensitiveデータベースでSUPER型を使用する際の動作と、COLLATE関数による細かな制御方法を確認できました。特にデフォルトでは大文字小文字を区別しない動作をしつつ、必要に応じてCOLLATE関数でcase_sensitiveを指定することで、部分的に大文字小文字を区別した処理が可能な点です。
例えばECサイトで商品検索は大文字小文字を区別せず、一方でユーザーIDのような厳密な識別子は区別するといった、柔軟な実装が可能になります。既存システムの移行や、多様なデータソースを統合する際にも、この新機能は大きな力を発揮するでしょう。ぜひ実際のプロジェクトでの活用を検討してみてください。
