Amazon Redshiftの文字列処理における「大文字と小文字を区別しない照合」を試してみた
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(データアナリティクス)事業本部のナガマサでした。
![[アップデート] Amazon Redshift デフォルトの動作変更によりセキュリティを強化](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
