Amazon Redshift: テーブル作成時の列データ型や桁数を検討する為に入力データの最大値・最大桁数を求める

Amazon Redshiftで良く使いそうな便利系SQL

#AWS

#Amazon Redshift

#ビッグデータ

しんや

2016.09.09

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

Amazon Redshiftで入力データを取り込むためにまずはデータが入る『テーブル』を定義する訳ですが、テーブルには『桁数』や『最大値』が存在し、想定されるデータを取り込めるための余裕を持った形で桁数や最大値を考慮し、テーブルを作成(CREATE TABLE)する必要があります。値や桁数の上限についてはデータ型毎に異なります。詳細は以下公式ドキュメントをご参照ください。

データ型 - Amazon Redshift

Redshiftに投入するデータの『出力元』が何らかのデータベースからであれば、出力元データベースでのテーブル定義が活用出来る事でしょう。ゼロから情報を探る必要がなくなるので大幅な作業の効率化が望めます。一方で、そういったテーブル定義が無いような状況、例えばログファイルのような形で『型やフォーマットは決まっているが、最大桁数までは現実のところ何桁位が最大値か不明』という様な状況もあるかも知れません。テーブル定義があった場合でも、元がざっくり大きな桁数で定義されているので実データとしてどんなボリューム、長さのデータが入っているか分かっていない、という事もあるかも知れません。当エントリではそんな状況で『現実のところ、どのような値、桁数のデータが入っているか』というような情報を取得する便利系SQLをご紹介したいと思います。

テーブル設計時に於ける『桁数・サイズ定義』のポイント

Amazon Redshiftではテーブル設計時における『ベストプラクティス』が以下にまとめられています。

Amazon Redshift のテーブル設計のベストプラクティス - Amazon Redshift

ポイントは多岐にわたっているのですが、その中の1つに『最小列サイズの使用』というものがあります。どれ位のデータが入ってくるか分からないからと言って最大桁数・サイズで設定してしまうとパフォーマンスに響くことがあるよ、というものです。(※テキスト量的に短かったので全文引用してみました)

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

便宜上の理由で列の最大サイズを使用しないようにしてください。

代わりに、例えば VARCHAR 列に格納する可能性が高い最大値を考慮し、それに応じて列のサイズを決定します。Amazon Redshift は列データを非常に効果的に圧縮するので、必要なサイズよりかなり大きな列を作成しても、データテーブルのサイズにはあまり影響を与えません。ただし、複雑なクエリの処理中に、一時テーブルに中間クエリ結果を格納しなければならない場合があります。一時テーブルは圧縮されないので、不必要に大きな列がメモリおよび一時ディスク領域を過剰に消費して、クエリパフォーマンスに影響を与える可能性があります。

文字列型の最大値・最大桁数を求める

『文字列型項目の最大桁数を求める』というのは以前にも下記エントリで言及しており、当時は使えそうな関数がRedshiftで扱えない状況となっていてｳﾜｧｧｧｧﾝした経緯がありました。

Amazon Redshiftで良く使いそうなSQL文をスクリプトで作成してみた｜ Developers.IO
- 項目の最大バイト数を求める(代替策)

当時は上記エントリ内容にあるような処置で進めてみていましたが、現在ではPython言語を使ってUDFを作成する事が出来ます。

ユーザー定義関数の作成 - Amazon Redshift

作成したUDFは以下です。PostgreSQLに於けるOCTET_LENGTH関数(引数文字列のバイト数を算出する)に相当するPythonのlenメソッドを使い、シンプルにバイト数を算出させています。引数のVARCHAR型パラメータについては桁数が未指定だと256桁が最大値となってしまうため、ここは一応VARCHAR型最大値の65535を任意指定しています。

# CREATE FUNCTION cm_octet_length (target VARCHAR(65535))
returns integer
STABLE
as $$
return len(target)
$$ language plpythonu;
CREATE FUNCTION

上記作成関数の使用例です。全角文字列を3バイト換算としてバイト数を算出出来ています。

# SELECT CM_OCTET_LENGTH(title) AS bytes_length, title FROM xxxxxxxxxx ORDER BY post_date DESC LIMIT 10;
 bytes_length |                                         title                                         
--------------+---------------------------------------------------------------------------------------
          119 | Amazon ECSを使ってマーケティングオートメーション・ソフトウェアMauticを5分で立ち上げる
           96 | 「クラスメソッドメンバーズ」AWS利用料金がさらにオトクになりました
           53 | IT大国・フィリピンに行ってきました！
           50 | AWS InspectorでWindowsのCVE評価をしてみた
           32 | ansibleでdatadogを導入する
           54 | JavaでS3のフォルダの存在チェックをする
           85 | 【めそ子通信】日本全国を飛び回る！ 秋開催セミナーのご案内
           58 | クラスメソッド2次元社員めそ子のコーナー
          103 | [Java] Java1.8で追加されたDate and Time APIを使用してランダムな日付を生成する。
           60 | 【ほぼ週刊Amazon Web Services】2016/08/29〜2016/09/04
(10 rows)

『テーブル列毎の最大値・最大桁数を求めるSQL』を生成するSQL

で、上記UDFを用いて作ってみたSQLが以下となります。検索パス(search_path)に登録されているスキーマ名の情報を参照するのでクラスタのパラメータグループにスキーマを登録しておくか、クエリ実行前にSETコマンドで事前設定しておく必要があります。<スキーマ名>及び<テーブル名>に情報を取得したいテーブル名の情報を入れる形になります。

SELECT
  'SELECT \'' || for_edit.schemaname || '\' AS target_schema, \''
  || for_edit.tablename || '\' AS target_table, \''
  || for_edit.table_column || '\' AS target_column, \''
  || for_edit.type || '\' AS data_type, '
  || '\'' || info_type || '\' AS info_type, '
  || calc_max_pre || table_column || calc_max_suf || ' AS maxinfo'
  || ' FROM ' || for_edit.schemaname || '.' || for_edit.tablename || ';' AS cheek_sql
FROM
  (SELECT
     base.schemaname,
     base.tablename,
     base.table_column,
     base.type_str,
     base.type,
     CASE base.type_str
       WHEN 'char' THEN 'MAX(CM_OCTET_LENGTH('
       ELSE 'MAX('
     END AS calc_max_pre,
     CASE base.type_str
       WHEN 'char' THEN '))'
       ELSE ')'
     END AS calc_max_suf,
     CASE base.type_str
       WHEN 'char' THEN '最大バイト数'
       ELSE '最大値'
     END info_type 
   FROM
     (SELECT
        pg_table_def.schemaname,
        pg_table_def.tablename,
        pg_table_def."column" AS table_column,
        pg_table_def.type,
        SUBSTRING(pg_table_def.type,0,5) AS type_str
      FROM
        pg_table_def
      WHERE
        pg_table_def.schemaname = '<スキーマ名>'
      AND pg_table_def.tablename = '<テーブル名>') base) for_edit;

以下の様な値を事前登録しておき、

DROP TABLE cmdevio.coltype;
CREATE TABLE cmdevio.coltype (
  smallintval SMALLINT,
  intval INT,
  bigintval BIGINT,
  decimalval DECIMAL(10,5),
  realval REAL,
  doubleval DOUBLE PRECISION,
  boolval BOOLEAN,
  charval CHAR(8),
  varcharval VARCHAR(50),
  dateval DATE,
  tsval TIMESTAMP
);
CREATE TABLE
# INSERT INTO cmdevio.coltype VALUES(32767, 2147483647, 9223372036854775807, 99999.12345, 8888888.123456, 7777777777.123456789012345, false, 'ABCDEFGH', 'クラスメソッド株式会社2016', '2016-09-08', '2016-12-31 23:59:59'); 
INSERT 0 1

上記テーブルの情報を使ってクエリを実行した結果が以下の様になります。

               cheek_sql
--------------------------------------------------------------------------------------
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'smallintval' AS target_column, 'smallint' AS data_type, '最大値' AS info_type, MAX(smallintval) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'intval' AS target_column, 'integer' AS data_type, '最大値' AS info_type, MAX(intval) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'bigintval' AS target_column, 'bigint' AS data_type, '最大値' AS info_type, MAX(bigintval) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'decimalval' AS target_column, 'numeric(10,5)' AS data_type, '最大値' AS info_type, MAX(decimalval) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'realval' AS target_column, 'real' AS data_type, '最大値' AS info_type, MAX(realval) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'doubleval' AS target_column, 'double precision' AS data_type, '最大値' AS info_type, MAX(doubleval) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'boolval' AS target_column, 'boolean' AS data_type, '最大値' AS info_type, MAX(boolval) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'charval' AS target_column, 'character(8)' AS data_type, '最大バイト数' AS info_type, MAX(CM_OCTET_LENGTH(charval)) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'varcharval' AS target_column, 'character varying(50)' AS data_type, '最大バイト数' AS info_type, MAX(CM_OCTET_LENGTH(varcharval)) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'dateval' AS target_column, 'date' AS data_type, '最大値' AS info_type, MAX(dateval) AS maxinfo FROM cmdevio.coltype;
 SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'tsval' AS target_column, 'timestamp without time zone' AS data_type, '最大値' AS info_type, MAX(tsval) AS maxinfo FROM cmdevio.coltype;
(11 rows)

そして生成されたSQLを実行する事で以下の様な結果を得ることが出来ます。(Booleanについては最大値、桁数という概念が無いのでエラーになってますね。まぁこの辺は割と突貫で作ったのもあるし、今回のゴールとは若干ズレた形となるのでご容赦頂ければと思います...)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'smallintval' AS target_column, 'smallint' AS data_type, '最大値' AS info_type, MAX(smallintval) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column | data_type | info_type | maxinfo 
---------------+--------------+---------------+-----------+-----------+---------
 cmdevio       | coltype      | smallintval   | smallint  | 最大値    |   32767
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'intval' AS target_column, 'integer' AS data_type, '最大値' AS info_type, MAX(intval) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column | data_type | info_type |  maxinfo   
---------------+--------------+---------------+-----------+-----------+------------
 cmdevio       | coltype      | intval        | integer   | 最大値    | 2147483647
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'bigintval' AS target_column, 'bigint' AS data_type, '最大値' AS info_type, MAX(bigintval) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column | data_type | info_type |       maxinfo       
---------------+--------------+---------------+-----------+-----------+---------------------
 cmdevio       | coltype      | bigintval     | bigint    | 最大値    | 9223372036854775807
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'decimalval' AS target_column, 'numeric(10,5)' AS data_type, '最大値' AS info_type, MAX(decimalval) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column |   data_type   | info_type |   maxinfo   
---------------+--------------+---------------+---------------+-----------+-------------
 cmdevio       | coltype      | decimalval    | numeric(10,5) | 最大値    | 99999.12345
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'realval' AS target_column, 'real' AS data_type, '最大値' AS info_type, MAX(realval) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column | data_type | info_type |   maxinfo   
---------------+--------------+---------------+-----------+-----------+-------------
 cmdevio       | coltype      | realval       | real      | 最大値    | 8.88889e+06
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'doubleval' AS target_column, 'double precision' AS data_type, '最大値' AS info_type, MAX(doubleval) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column |    data_type     | info_type |     maxinfo      
---------------+--------------+---------------+------------------+-----------+------------------
 cmdevio       | coltype      | doubleval     | double precision | 最大値    | 7777777777.12346
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'boolval' AS target_column, 'boolean' AS data_type, '最大値' AS info_type, MAX(boolval) AS maxinfo FROM cmdevio.coltype;
ERROR:  function max(boolean) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'charval' AS target_column, 'character(8)' AS data_type, '最大バイト数' AS info_type, MAX(CM_OCTET_LENGTH(charval)) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column |  data_type   |  info_type   | maxinfo 
---------------+--------------+---------------+--------------+--------------+---------
 cmdevio       | coltype      | charval       | character(8) | 最大バイト数 |       8
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'varcharval' AS target_column, 'character varying(50)' AS data_type, '最大バイト数' AS info_type, MAX(CM_OCTET_LENGTH(varcharval)) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column |       data_type       |  info_type   | maxinfo 
---------------+--------------+---------------+-----------------------+--------------+---------
 cmdevio       | coltype      | varcharval    | character varying(50) | 最大バイト数 |      37
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'dateval' AS target_column, 'date' AS data_type, '最大値' AS info_type, MAX(dateval) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column | data_type | info_type |  maxinfo   
---------------+--------------+---------------+-----------+-----------+------------
 cmdevio       | coltype      | dateval       | date      | 最大値    | 2016-09-08
(1 row)

# SELECT 'cmdevio' AS target_schema, 'coltype' AS target_table, 'tsval' AS target_column, 'timestamp without time zone' AS data_type, '最大値' AS info_type, MAX(tsval) AS maxinfo FROM cmdevio.coltype;
 target_schema | target_table | target_column |          data_type          | info_type |       maxinfo       
---------------+--------------+---------------+-----------------------------+-----------+---------------------
 cmdevio       | coltype      | tsval         | timestamp without time zone | 最大値    | 2016-12-31 23:59:59
(1 row)
#

まとめ

テーブルに入っているデータの最大値や最大桁数を求める便利系SQLを作ってみた、という内容のご紹介でした。現行取り込んだ事の無いテーブルを準備し、データを取り込む際の手順に関し、Amazon Redshiftに於いては概ね以下の様な形で進めれば良い感じに整うのでは無いかと思います。

0.データの出力元となったテーブル定義等が入手出来るのであれば勿論それらを参考にする
1.とりあえず値が入りそうな桁数、最大値でざっくり定義したテーブルを用意
2.COPYコマンドで対象データを投入
3.上記SQLで現行データの最大値や最大桁数を割り出す
4.現行最大値・最大桁数及び今後の見通し(今の状態を超える様な値が入る可能性があるか、拡張の予定があるか)を踏まえ、適切なデータ型や桁数を検討
- 最小列サイズの使用 - Amazon Redshift
5.また、併せて現行データ内容に基づいた列圧縮タイプ等もこのタイミングで情報取得しておく
6.分散キー／ソートキー／プライマリーキー／NOT NULL制約等についてもこのタイミングで改めて検討
7.上記手順4〜6を踏まえた『より適切な』テーブル定義を再作成、CREATE TABLE／COPYを改めて実行し直す