スカラー Python UDF を使用した Amazon Redshift クエリー結果の言語固有のソートの設定

Ethan Stark、Amazon Web Services

概要

このパターンは、スカラーの Python UDF (ユーザー定義関数) を使用した Amazon Redshift のクエリ結果について、大文字と小文字を区別しない言語ソートを設定する手順とサンプルコードを提供します。Amazon Redshift はバイナリ UTF-8 ソートに基づいて結果を返し、言語固有のソートをサポートしていないので、スカラー Python UDF を使用する必要があります。Python UDF は Python プログラム基づいた SQL 以外の処理コードで、データウェアハウス内で実行されます。1 つのクエリで SQL ステートメントを使用して Python UDF コードを実行できます。詳細については、Amazon Redshift ビッグデータブログ記事のUDFs の概要 AWS 」を参照してください。

このパターンのサンプルデータは、デモ用にトルコ語のアルファベットに基づいています。このパターンのスカラー Python UDF は、Amazon Redshift のデフォルトのクエリ結果をトルコ語の文字の言語順序に合うように構築されています。詳細については、このパターンの「追加情報」セクションにあるトルコ語の例を参照してください。このパターンのスカラー Python UDF を他の言語に変更できます。

前提条件と制限事項

前提条件

機能制限

このパターンのクエリで使用される言語ソートでは、大文字と小文字は区別しません。

ツール

AWS のサービス

その他のツール

エピック

CREATE TABLE my_table (first_name varchar(30));

INSERT INTO my_table (first_name)
VALUES
    ('ali'),
    ('Ali'),
    ('ırmak'),
    ('IRMAK'),
    ('irem'),
    ('İREM'),
    ('oğuz'),
    ('OĞUZ'),
    ('ömer'),
    ('ÖMER'),
    ('sedat'),
    ('SEDAT'),
    ('şule'),

SELECT first_name FROM my_table ORDER BY first_name;

first_name
---------------
Ali
IRMAK
OĞUZ
SEDAT
ali
irem
oğuz
sedat
ÖMER
ömer
İREM
ırmak
ŞULE
şule

CREATE OR REPLACE FUNCTION collate_sort (value varchar) 
RETURNS varchar 
IMMUTABLE 
AS 
$$    
    def sort_str(val):
        import string
        
        dictionary = {
            'I': 'ı', 
            'ı': 'h~', 
            'İ': 'i', 
            'Ş': 's~', 
            'ş': 's~', 
            'Ğ': 'g~', 
            'ğ': 'g~', 
            'Ü': 'u~', 
            'ü': 'u~', 
            'Ö': 'o~', 
            'ö': 'o~', 
            'Ç': 'c~', 
            'ç': 'c~'
        }
        
        for key, value in dictionary.items():
            val = val.replace(key, value)
        
        return val.lower()
 
    return sort_str(value)
    
$$ LANGUAGE plpythonu;

SELECT first_name FROM my_table ORDER BY collate_sort(first_name);

first_name
---------------
ali
Ali
ırmak
IRMAK
irem
İREM
oğuz
OĞUZ
ömer
Ömer
sedat
SEDAT
şule
ŞULE

関連リソース

追加情報

トルコ語の例

Amazon Redshift は、言語固有のソート順序ではなく、バイナリ UTF-8 の順序に基づいてクエリ結果を返します。つまり、トルコ語の文字を含む Amazon Redshift テーブルに対してクエリを実行した場合、クエリ結果はトルコ語の言語順でソートされません。トルコ語には、ラテン文字にはない 6 つの特殊文字 (、ı、、ö、ü) が含まれています。これらの特殊文字は、次のテーブルに示すように、UTF-8 バイナリの順序に基づいてソートされた結果セットの最後に配置されます。

上の表が示すように、特殊文字 ç はトルコ語の順序では c と d の間ですが、UTF-8 バイナリの順序では z の後に表示されます。このモードのスカラー Python UDF は、トルコ語の特殊文字をラテン語の対応する文字に置き換えるために、次の文字置換辞書を使用します。

スカラー Python UDF 関数を変更する

スカラー Python UDF 関数が locate パラメータを受け入れ、マルチトランザクション辞書をサポートするように、このスキーマに基づいて変更するには、次の SQL コードを使用します：

CREATE OR REPLACE FUNCTION collate_sort (value varchar, locale varchar) 
RETURNS varchar 
IMMUTABLE 
AS 
$$    
    def sort_str(val):
       import string
       # Turkish Dictionary
       if locale == 'tr-TR':
            dictionary = {
               'I': 'ı', 
               'ı': 'h~', 
               'İ': 'i', 
               'Ş': 's~', 
               'ş': 's~', 
               'Ğ': 'g~', 
               'ğ': 'g~', 
               'Ü': 'u~', 
               'ü': 'u~', 
               'Ö': 'o~', 
               'ö': 'o~', 
               'Ç': 'c~', 
               'ç': 'c~'
        }
        # German Dictionary
        if locale == 'de-DE':
            dictionary = {
               ....
               ....
        }
        
        for key, value in dictionary.items():
            val = val.replace(key, value)
        
        return val.lower()
 
    return sort_str(value)
    
$$ LANGUAGE plpythonu;

次のコード例では、変更された Python UDF をクエリする方法を示しています。

SELECT first_name FROM my_table ORDER BY collate_order(first_name, 'tr-TR');