Amazon Redshiftの文字列処理における「大文字と小文字を区別しない照合」を試してみた

#Amazon Redshift

nagamasa

2021.07.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Redshift(以下、Redshift)で、文字列処理における「大文字と小文字を区別しない照合」がサポートされていたので、簡単な動作を確認してみました。

はじめに

文字列処理における「大文字と小文字を区別する/しない」とはどういうことか、下記を確認していただくとイメージしやすいかと思います。

大文字と小文字を区別する場合

select * from collation_test;
 id | name
----+-------
  1 | apple
  2 | apPLE
  3 | APPLE
(3 rows)

select * from collation_test where name = 'apple';
 id | name
----+-------
  1 | apple
(1 row)

select * from collation_test where name = 'APPLE';
 id | name
----+-------
  3 | APPLE
(1 row)

上記のように大文字と小文字を区別する(別の値とみなす)ので、小文字の値で検索した場合は小文字の値のみが検索対象となり、大文字の値で検索した場合は大文字の値のみが対象となります。

大文字と小文字を区別しない場合

select * from collation_test2;
 id | name
----+-------
  1 | apple
  2 | apPLE
  3 | APPLE
(3 rows)

select * from collation_test2 where name = 'apple';
 id | name
----+-------
  1 | apple
  2 | apPLE
  3 | APPLE
(3 rows)

select * from collation_test2 where name = 'APPLE';
 id | name
----+-------
  1 | apple
  2 | apPLE
  3 | APPLE
(3 rows)

上記のように大文字も小文字を区別しない(同じ値とみなす)ので、小文字の値で検索をしても大文字の値も対象となります。逆に大文字の値で検索しても、小文字の値も検索対象となります。

これまでは、文字列処理における大文字と小文字を区別する(別の値とみなす)照合が設定されていたのですが、「大文字と小文字を区別しない照合」をデータベースやテーブル作成時にオプションとして指定できるようになっていたので、本ブログにて確認してみます。

少し似ている内容になるのですが、データベース・テーブル・列の名前などの識別子について、大文字と小文字を区別する方法は下記のブログをご確認ください。

試してみた

環境

Redshift クラスターバージョン： 1.0.28422
クライアント： psql (PostgreSQL) 12.4

データベース作成時に「大文字と小文字を区別する/しない」を指定する

新しいデータベースを作成するときに、「大文字と小文字を区別する/しない」をデータベースレベルのデフォルトの照合として指定することが可能です。

COLLATE CASE_SENSITIVE : 大文字と小文字を区別する
COLLATE CASE_INSENSITIVE : 大文字と小文字を区別しない

CREATE DATABASE database_name [ WITH ]
[ COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE ]

現時点でのデータベースレベルでの照合を確認したい場合は、下記のクエリで確認することができます。

select db_collation();
   db_collation
------------------
 case_insensitive
(1 row)

照合を変更する場合は、下記の構文を使用します。

ALTER DATABASE database_name
COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE

データベースレベルの照合を変更する場合は、データベースが空の(テーブルが存在しない)場合しか使用できません。
データベースが空でない場合に、変更しようとすると下記のエラーとなります。

alter database test01 collate case_insensitive;
ERROR:  Cannot change case sensitivity: User created table found in the current database.

試しにcase_insensitive(大文字と小文字を区別しない)を設定したデータベースで、任意のテーブルを作成して確認してみます。

create table cm_ohama.tbl_1 (
  col_1 BIGINT ENCODE AZ64,
  col_2 VARCHAR(20) ENCODE ZSTD,
  col_3 CHAR(10) ENCODE ZSTD
);

VARCHAR列とCHAR列が対象となります。

select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_1' order by 2;
 table_name | column_name |     data_type     |  collation_name
------------+-------------+-------------------+------------------
 tbl_1      | col_1       | bigint            |
 tbl_1      | col_2       | character varying | case_insensitive
 tbl_1      | col_3       | character         | case_insensitive
 (3 rows)

col_2(VARCHAR列) と col_3(CHAR列) のcollation_nameの値がcase_insensitive(大文字と小文字を区別しない)となっていることが確認できました。

テーブル作成時に「大文字と小文字を区別する/しない」を指定する

新しいテーブルを作成するときに、VARCHAR列とCHAR列の照合を指定して、データベースレベルの照合を上書きすることができます。
試しにデータベース作成時はcase_sensitive(大文字と小文字を区別する)、テーブル作成時に任意のカラムにcase_insensitive(大文字と小文字を区別しない)を指定して確認してみます。

データベース作成の際に、COLLATE未指定で作成します。(未指定の場合は、case_sensitiveが適用されます。)

CREATE DATABASE test02;

select db_collation();
  db_collation
----------------
 case_sensitive
(1 row)

col_2のみ、case_insensitive(大文字と小文字を区別しない)を指定して、テーブルを作成します。

create table cm_ohama.tbl_2 (
  col_1 BIGINT ENCODE AZ64,
  col_2 VARCHAR(20) COLLATE CASE_INSENSITIVE ENCODE ZSTD,
  col_3 CHAR(10) ENCODE ZSTD
);

col_2のみ、データベースレベルの照合(case_sensitive)を上書きして、case_insensitive(大文字と小文字を区別しない)となっていることが確認できました。

select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_2' order by 2;
 table_name | column_name |     data_type     |  collation_name
------------+-------------+-------------------+------------------
 tbl_2      | col_1       | bigint            |
 tbl_2      | col_2       | character varying | case_insensitive
 tbl_2      | col_3       | character         | case_sensitive
(3 rows)

一応、データも投入して確認してみます。

insert into cm_ohama.tbl_2 values(1,'redshift','dwh');
insert into cm_ohama.tbl_2 values(2,'Redshift','dWh');
insert into cm_ohama.tbl_2 values(3,'REDSHIFT','DWH');

select * from cm_ohama.tbl_2 order by col_1;
 col_1 |  col_2   |   col_3
-------+----------+------------
     1 | redshift | dwh
     2 | Redshift | dWh
     3 | REDSHIFT | DWH
(3 rows)

上記のデータを踏まえて、はじめにcol_2を検索条件に指定して、小文字の値で検索してみます。

select * from cm_ohama.tbl_2 where col_2 = 'redshift' order by col_1;
 col_1 |  col_2   |   col_3
-------+----------+------------
     1 | redshift | dwh
     2 | Redshift | dWh
     3 | REDSHIFT | DWH
(3 rows)

col_2 は、case_insensitive(大文字と小文字を区別しない)なので、大文字のデータも対象となりました。テーブル作成時のカラムごとに指定した照合(case_insensitive)がデータベース作成時の照合(case_sensitive)を上書きして、想定していたデータを抽出できています。

次に、col_3を検索条件に指定して、大文字の値で検索してみます。

select * from cm_ohama.tbl_2 where col_3 = 'DWH' order by col_1;
 col_1 |  col_2   |   col_3
-------+----------+------------
     3 | REDSHIFT | DWH
(1 row)

col_3 は、データベース作成時に指定されていた照合(case_sensitive)が適用されたままのため、大文字のデータのみ対象となりました。

COLLATE関数を用いて「大文字と小文字を区別する/しない」を指定する

SQLクエリでCOLLATE関数を用いて、VARCHAR列またはCHAR列の照合を指定することが可能です。
またデータベース作成時、テーブル作成時で指定した照合を上書きします。

select db_collation();
  db_collation
----------------
 case_sensitive
(1 row)

select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_2' order by 2;
 table_name | column_name |     data_type     |  collation_name
------------+-------------+-------------------+------------------
 tbl_2      | col_1       | bigint            |
 tbl_2      | col_2       | character varying | case_insensitive
 tbl_2      | col_3       | character         | case_sensitive
(3 rows)

select * from cm_ohama.tbl_2 order by col_1;
 col_1 |  col_2   |   col_3
-------+----------+------------
     1 | redshift | dwh
     2 | Redshift | dWh
     3 | REDSHIFT | DWH
(3 rows)

上記のデータを踏まえて、COLLATE関数にて「大文字と小文字を区別しない照合」が上書きされていることを確認します。

select * from cm_ohama.tbl_2 where collate(col_3, 'case_insensitive') = 'DWH'　order by col_1;
 col_1 |  col_2   |   col_3
-------+----------+------------
     1 | redshift | dwh
     2 | Redshift | dWh
     3 | REDSHIFT | DWH

(3 rows)

select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_2' order by 2;
 table_name | column_name |     data_type     |  collation_name
------------+-------------+-------------------+------------------
 tbl_2      | col_1       | bigint            |
 tbl_2      | col_2       | character varying | case_insensitive
 tbl_2      | col_3       | character         | case_sensitive
(3 rows)

col_3 のcollation_nameがcase_sensitive(大文字と小文字を区別する)ではありますが、COLLATE関数を用いてcase_insensitive(大文字と小文字を区別しない)でデータを抽出できました。

ちなみに(ILIKE演算子について)

対象のデータベース、列の照合がcase_sensitive(大文字と小文字を区別する)の場合でも、ILIKE演算子を用いて、大文字と小文字を区別しないパターン一致を確認することはできます。

select db_collation();
  db_collation
----------------
 case_sensitive
(1 row)

select table_name, column_name, data_type, collation_name from svv_columns where table_schema = 'cm_ohama' and table_name = 'tbl_2' order by 2;
 table_name | column_name |     data_type     |  collation_name
------------+-------------+-------------------+------------------
 tbl_2      | col_1       | bigint            |
 tbl_2      | col_2       | character varying | case_insensitive
 tbl_2      | col_3       | character         | case_sensitive
(3 rows)

select * from cm_ohama.tbl_2 where col_3 ilike '%dWh%';
 col_1 |  col_2   |   col_3
-------+----------+------------
     1 | redshift | dwh
     3 | REDSHIFT | DWH
     2 | Redshift | dWh
(3 rows)

注意事項

下記の注意事項などがあります。さらに詳しい内容については、データベースの作成などをご確認ください。

PGカタログテーブルとAmazonRedshiftシステムテーブルを含むすべてのシステムテーブルまたはビューでは、大文字と小文字は区別される
Amazon Redshift SpectrumやAmazon Aurora PostgreSQLフェデレーションクエリなどの外部クエリの場合、VARCHAR列およびCHAR列の照合は現在のデータベースレベルの照合が適用される

大文字と小文字を区別しないデータベースでのSUPER列は使用できない(※テーブル作成時に、case_sensitive指定でも使用できない)

SELECT db_collation();
   db_collation
------------------
 case_insensitive
(1 row)

create table cm_ohama.tbl_4 (col_1 super);
ERROR:  SUPER column is not supported in case insensitive database.

create table cm_ohama.tbl_4 (col_1 super collate case_sensitive);
ERROR:  SUPER column is not supported in case insensitive database.

おわりに

簡単ではありますが、Redshiftの文字列処理における「大文字と小文字を区別しない照合」の設定および上書きの方法などについて確認しました。
「大文字と小文字を区別する/しない」は、各DBや各DWHによって仕様の違いがあると思うので、Redshiftへのデータ移行の際は改めて注意した上で活用していきたいと思います。

以上、DA(データアナリティクス)事業本部のナガマサでした。