Amazon Redshift: 『パフォーマンスチューニングテクニック Top 10』手順の実践(5).とても大きなVARCHAR列を含む表

当エントリは先日投稿したシリーズ『『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』手順実践』の第5弾です。課題#5の『とても大きなVARCHAR列を含む表』について内容を見て行きたいと思います。

• AWS Solutions Architect ブログ: Amazon Redshiftのパフォーマンスチューニングテクニック Top 10

『課題 #5: とても大きなVARCHAR列を含む表』を実践してみる

Amazon Redshiftでテーブル定義を行なう際、可変長の文字列データを格納する任意の項目の桁数をどうすべきか、という部分について検討するケースは往々にしてあると思います。テーブル定義が既に存在している場合であればその情報を元に所定の桁数を定める事が出来ますが、ログデータ等『ファイルベース』のデータを格納する時等は『この項目値は最大桁数どれ位あるんだ？』となる場合も当然出てくるでしょう。その際『ひとまず桁数エラーにならない程度にざっくり大きな桁数設定しておこうか』という事は極初期の頃ではやるかも知れません。しかし、下記ドキュメントでも言及されている様に、必要以上に大きな桁数を持つカラムがあると、パフォーマンスの面で影響を受けてしまいます。実際に入るデータの情報を元に、適切な桁数を設定して行く事がパフォーマンスの向上・改善に繋がります。

• 最小列サイズの使用 - Amazon Redshift

公式ブログエントリに記載されている内容では、以下のSQLが紹介されています。svv_table_infoに格納されているmax_varcharという項目、これはテーブルにおける最大桁数を保持する項目の桁数が設定されています。その桁数が150を超えるものを一覧表示させています。

SELECT
  database,
  schema || '.' || "table" AS "table",
  max_varchar 
FROM
  svv_table_info 
WHERE
  max_varchar > 150 
ORDER BY
  2;

テーブル単位でこのテーマの改善を行なう際、テーブル毎の文字列項目(CHAR及びVARCHAR型)の桁数を抽出するSQL文を作ってみました。スキーマ名とテーブル名を指定する事で、所定のカラムの桁数が取得出来ます。

SELECT
  varchar_info.schemaname,
  varchar_info.tablename,
  varchar_info.column,
  varchar_info.char_length
FROM
  (SELECT
    char_types_info.schemaname,
    char_types_info.tablename,
    char_types_info.column,
    char_types_info.col_type_and_len,
    CAST(
      SUBSTRING(
        char_types_info.col_type_and_len,
        STRPOS(char_types_info.col_type_and_len,'/')+1,
        LENGTH(char_types_info.col_type_and_len)
      ) AS SMALLINT
    ) AS char_length
  FROM
    (SELECT
      pg_table_def.schemaname,
      pg_table_def.tablename,
      pg_table_def.column,
      /**
      character(10) -> CHAR/10
      character varying(100) -> VARCHAR/300
      */
      REPLACE(
        REPLACE(
          REPLACE(pg_table_def.type,'character(','CHAR/'),
          'character varying(','VARCHAR/'
        ),
        ')',
        ''
      ) AS col_type_and_len
    FROM
      pg_table_def
    WHERE
        type like'%character%' AND type like'%(%') char_types_info
  ) varchar_info
WHERE
  varchar_info.schemaname = 'xxxxxxx'
  AND varchar_info.tablename = 't_max_char_length';

 schemaname |      tablename    |   column   | char_length 
------------+--------------------------------+------------+-------------
 xxxxxxx    | t_max_char_length | col_aaa    |        2000
 xxxxxxx    | t_max_char_length | col_bbb    |         100
 xxxxxxx    | t_max_char_length | col_ccc    |         100
 xxxxxxx    | t_max_char_length | col_ddd    |          50
 xxxxxxx    | t_max_char_length | col_eee    |         100
(5 rows)

上記SQLでの実行結果は『カラム毎の現在の桁数』に関する定義でした。これに対し"『任意の項目に格納されているデータの最大桁数はどうなっているのか』について洗い出すSQL"を生成するSQL文を以下にまとめてみました。割と雑なSQLなので生成されたSQLを一部書き換える必要があります(最終行のUNION ALLを削って末尾にカンマ付与)が、一度に対象項目のデータ最大桁数を得る事が出来るようになるので幾らかは作業効率も軽減出来るのではと思います。

SELECT
  'SELECT '
    || '''' || varchar_info.schemaname || '.' || varchar_info.tablename || '.' || varchar_info.column || ''' AS colname, '
    || 'MAX(LENGTH(' || varchar_info.schemaname || '.' || varchar_info.tablename || '.' || varchar_info.column || ')) maxlen FROM ' || varchar_info.schemaname || '.' || varchar_info.tablename || ' UNION ALL '
FROM
  (SELECT
    char_types_info.schemaname,
    char_types_info.tablename,
    char_types_info.column
  FROM
    (SELECT
      pg_table_def.schemaname,
      pg_table_def.tablename,
      pg_table_def.column
    FROM
      pg_table_def
    WHERE
        type like'%character%' AND type like'%(%') char_types_info
  ) varchar_info
WHERE
  varchar_info.schemaname = 'xxxxxxx'
  AND varchar_info.tablename = 't_max_char_length';
  
                                    sql                                                                                                 
-------------------------------------------------------------------------------------
 SELECT 'xxxxxxx.t_max_char_length.col_aaa' AS colname, MAX(LENGTH(xxxxxxx.t_max_char_length.col_aaa)) maxlen FROM xxxxxxx.t_max_char_length UNION ALL 
 SELECT 'xxxxxxx.t_max_char_length.col_bbb' AS colname, MAX(LENGTH(xxxxxxx.t_max_char_length.col_bbb)) maxlen FROM xxxxxxx.t_max_char_length UNION ALL 
 SELECT 'xxxxxxx.t_max_char_length.col_ccc' AS colname, MAX(LENGTH(xxxxxxx.t_max_char_length.col_ccc)) maxlen FROM xxxxxxx.t_max_char_length UNION ALL 
 SELECT 'xxxxxxx.t_max_char_length.col_ddd' AS colname, MAX(LENGTH(xxxxxxx.t_max_char_length.col_ddd)) maxlen FROM xxxxxxx.t_max_char_length UNION ALL 
 SELECT 'xxxxxxx.t_max_char_length.col_eee' AS colname, MAX(LENGTH(xxxxxxx.t_max_char_length.col_eee)) maxlen FROM xxxxxxx.t_max_char_length UNION ALL 
(5 rows)

まとめ

以上、『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』トピック5つめ、"とても大きなVARCHAR列を含む表"に関する対処方法のご紹介でした。6つ目以降のトピックについても、こんな感じで読み解きつつ実践して行きたいと思います。