Amazon Redshift データ型と列圧縮タイプのまとめ(データ型＆列圧縮タイプ対応表付)

Amazon Redshift データ型と列圧縮タイプのまとめ(データ型＆列圧縮タイプ対応表付)

#Amazon Redshift
#AWS
しんや

しんや

facebook logohatena logotwitter logo
Clock Icon2014.06.18

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

Amazon Redshiftではテーブルを作成する際の要素として幾つかポイントがあります。1.と5.については一般的なRDBMSでも用いられるような概念ですね。残りの2〜4についてはRedshift特有の設定となります。当エントリではこの中の『データの型』と『列圧縮タイプ』について、その概要と、実践で使いやすい/調べやすい様に諸々個人で整理した情報などを併せて投下したいと思います。

  • 1.データの型(data types)
  • 2.列圧縮タイプ(column compression types)
  • 3.分散キー(distkey)
  • 4.ソートキー(sortkey)
  • 5.制約(constraint)

目次

データ型(Data Types)

テーブルを構成する列(Column)の項目属性を決める情報です。数値型、文字列型、日付型、ブール(真偽)型の4種類があります。

数値型

  • 整数：SMALLINT(INT2)、INT(INT4)、BIGINT(INT8)
    • 数値型によって利用可能な値の範囲が決まっている。また、後述する列圧縮タイプについても、利用可能な値の範囲が増えるに従い、設定可能な要素が増えていく。
  • ユーザー定義精度による値：DECIMAL, NUMERIC
  • 浮動小数点数：REAL(FLOAT4), DOUBLE PRECISION(FLOAT8, FLOAT)

文字型

大別するとCHAR型、VARCHAR型の2つに分類される。

  • CHAR：シングルバイト文字のみ格納可能、桁数に満たない場合は空白で埋められる
  • VARCHAR：可変長の文字列、マルチバイト文字列を格納可能、CHAR型と違い桁数に満たない部分は空白で埋められない。
  • 文字型には、CHAR, VARCHARの他にもNCHAR,VARCHAR,TEXT,BPCHARといった型をテーブル設計時に指定する事は可能。しかし、宣言後の内容はRedshift内部でCHARまたはVARCHAR型に自動で変換されてしまうため、混乱を避けるためにもCREATE TABLEの段階でCHAR/VARCHARを指定した形にしておいた方が良い(ような気がする)。以下はそれら文字型でCREATE TABLE文を実行してみた例。
CREATE TABLE datatypes (
  col1 CHAR(3),
  col2 VARCHAR(20),
  col3 NCHAR,
  col4 NCHAR(3),
  col5 NVARCHAR,
  col6 NVARCHAR(30),
  col7 TEXT,
  col8 BPCHAR
);

# SELECT * FROM pg_table_def WHERE tablename = 'datatypes';
 schemaname | tablename | column |          type          | encoding | distkey | sortkey | notnull 
------------+-----------+--------+------------------------+----------+---------+---------+---------
 public     | datatypes | col1   | character(3)           | none     | f       |       0 | f
 public     | datatypes | col2   | character varying(20)  | none     | f       |       0 | f
 public     | datatypes | col3   | character(1)           | none     | f       |       0 | f
 public     | datatypes | col4   | character(3)           | none     | f       |       0 | f
 public     | datatypes | col5   | character varying(256) | none     | f       |       0 | f
 public     | datatypes | col6   | character varying(30)  | none     | f       |       0 | f
 public     | datatypes | col7   | character varying(256) | none     | f       |       0 | f
 public     | datatypes | col8   | character(256)         | none     | f       |       0 | f
(8 行)

日付型

以下2つの型が指定可能。用途については一般的なRDBMSの日付型と認識は変わらない。

  • DATE
  • TIMESTAMP

ちょっとした注意

  • Amazon Redshiftでは、現状タイムゾーンはUTC固定の模様。
    • このため、タイムゾーンに関する情報を扱う場合は注意が必要。：日付関数 - Amazon Redshift
    • 時間情報を所定のタイムゾーンに変換する関数はある模様。：CONVERT_TIMEZONE 関数 - Amazon Redshift
    • 投入するデータファイル内の日付時刻情報はJST、SQLパラメータ等を渡すサーバ(例:EC2)のタイムゾーンをJSTにしておき、『日本時間』連携を行えばひとまずタイムゾーン問題は回避出来そうだが、データファイル(File:JST) - SQL指示パラメータ生成(EC2:JST) - Redshift内で日付時刻関数を使用(Redshift:UTC)という状況下ではタイムゾーンの相違・ズレが発生してしまうので注意が必要。BIツール等から同様の日付時刻関数を使う場合も注意が必要そうです。

ブール型

BOOLEAN型一択。TRUE/FASLE/NULLが指定可能、1バイト項目。

列圧縮タイプ

項目の型とは別に、Redshiftでは『列圧縮タイプ』という値の設定が可能となっています。用途・効能は公式ドキュメントから一部引用させて頂いたテキストをペタリ。

圧縮は、データの格納時にそのサイズを小さくする列レベルの操作です。圧縮によってストレージスペースが節約され、ストレージから読み込まれるデータのサイズが小さくなり、ディスク I/O の量が減少するので、クエリパフォーマンスが向上します。

以下、ざっくりとそれぞれの項目がどういうものかを見て行きたいと思います。(※詳細は各対応ドキュメントをご参照ください)

raw

圧縮なし。テーブル作成時に指定しなかった場合のデフォルト値はこれになる。

bytedict

列に含まれる一意の値の数が制限されている場合に非常に効果的です。このエンコードは、列のデータドメインが一意の値 256 個未満である場合に最適です。バイトディクショナリエンコードは、列に長い文字列が含まれる場合に特に空間効率が高まります。

上記公式ページから引用。マスタ名称のコード値然り、マスタ名称のコード値に対する名称のような値(個数が限られており、且つ長い文字列に成り得るようなもの)等が適しているのでしょうか。

delta, delta32k

デルタエンコードは、日時列にとって非常に有用です。 デルタエンコードは、列内の連続する値間の差を記録することにより、データを圧縮します。

DATE型/TIMESTAMP型の項目については、あまり深く考える事無く、このタイプで決まりっぽいですね。

lzo

LZO エンコードは、非常に高い圧縮率と良好なパフォーマンスを実現します。 LZO エンコードは、非常に長い文字列を格納する CHAR および VARCHAR 列、 特に製品説明、ユーザーコメント、JSON 文字列などの自由形式テキストに適しています。

割と広範にデータ型をサポートしているlzo。桁数的に一定数を超過するような長めの項目であれば、これも一択で指定可能そうなタイプではあります。

mostly8, mostly16, mostly32

Mostly エンコードは、列のデータ型が、格納された大部分の値で必要なサイズより大きい場合に有用です。 このタイプの列に Mostly エンコードを指定して、列内の大部分の値を、より小さい標準ストレージサイズに 圧縮することができます。圧縮できない残りの値は、raw 形式で格納されます。

数値項目且つ、その数値のバラ付き度合い、値の範囲が所定規模数に収まっている場合に有効な設定値。業務仕様的に『この値は大きくても100までの値しか入らない(→mostly8が適用可能)』、『この値は最大30000まではありえる(→mostly16が適用可能)』というような判断が下せる項目に適用すると良さげな感じですね。

runlength

このエンコードは、データ値が連続して頻繁に繰り返されるテーブル （例えば、テーブルがこれらの値でソートされる場合）に最も適しています。 (中略) ソートキーとして指定された列に、ランレングスエンコードを適用することは推奨されません。

このテキストから読み解くに、ソートキーの項目そのものでは無く、ソートキーに拠ってソートされる列の中で、データが連続して頻繁に繰り返される列に対して指定すべき、という意味なのでしょうか。

text255, text32k

text255 および text32k エンコードは、同じ単語が頻繁に出現する VARCHAR 列を圧縮する場合に有用です。

対象がVARCHAR型を含むという事でbytedictタイプと何が違うの？という感じもしますが、bytedict型は長い文字列に効く、と記載がある一方、こちらのタイプは『VENUE テーブルの VENUENAME 列を考えてみます』と記載があるように、そこまで長い要素を求められてもいなさそう？な感じがします。文字そのもの種類と言うより、その列を構成している要素がある程度限られた(255個だったり)バリエーションである場合、有効となるタイプ、という理解で良いのかな？

列圧縮タイプの分析と『自動圧縮ありのテーブルロード』について

Amazon Redshiftでは、データロード済み、データ投入済のテーブルに対してANALYZE COMPRESSIONというコマンドを実行する事で圧縮タイプを分析する事が可能となっておりますが、テーブルへのデータロード時にも同様の分析作業を行う事が可能となっています。それが『自動圧縮ありのテーブルロード』です。

手法としては、COPY処理実行時にCOMPUPDATEオプション(必須)及びCOMPROWSオプション(任意)を付与します。このオプション付きのCOPY文を実行し、

COPY sample_table FROM 's3://bucketname/foldername/loadtest.csv.gz'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
GZIP CSV QUOTE AS '"'
DELIMITER ','
DATEFORMAT 'YYYY/MM/DD'
IGNOREHEADER  AS 1;
COMPUPDATE ON COMPROWS 50000;

その後にpg_table_defテーブルを参照する事で、Redshift側で自動選択されたエンコーディング情報を見る事が出来ます。ちなみにCOMPROWS無指定時 or デフォルト(推奨)の件数は100000(10万)件となっております。

# SELECT
#   pg_table_def.schemaname,
#   pg_table_def.tablename,
#   pg_table_def.column,
#   pg_table_def.type,
#   pg_table_def.encoding
# FROM
#   pg_table_def
# WHERE
#   tablename = 'sample_table';
 
 schemaname | tablename    |        column         |          type           | encoding 
------------+--------------+-----------------------+-------------------------+----------
 public     | sample_table | column1               | character(200)          | lzo
 public     | sample_table | column2               | character(5)            | bytedict
 public     | sample_table | column3               | integer                 | mostly8
 :
 :

データ型 x 列圧縮タイプ 対応表

以下の表は、個別に列圧縮タイプを選択する際に『あれ、この型にこの圧縮タイプって設定出来たっけ...』と悩まなくて済むように、データ型に対して設定可能・不可能な圧縮タイプを整理してまとめたものです。

おおよその項目については、上記ANALYZE COMPRESSIONであったり、自動圧縮ありのロードである程度目安となる値は割り出せると思います。その中で判別し切れなかったりチューニングの際の変更候補割り出し等で宜しければご参照頂けると幸いです。

数値型
SMALLINT INT BIGINT DECIMAL REAL DOUBLE
無圧縮 raw ◯ ◯ ◯ ◯ ◯ ◯
バイトディクショナリ bytedict ◯ ◯ ◯ ◯ ◯ ◯
デルタ delta ◯ ◯ ◯ ◯ ー ー
delta32k ー ◯ ◯ ◯ ー ー
LZO lzo ◯ ◯ ◯ ◯ ー ー
mostly mostly8 ◯ ◯ ◯ ◯ ー ー
mostly16 ー ◯ ◯ ◯ ー ー
mostly32 ー ー ◯ ◯ ー ー
ランレングス runlength ◯ ◯ ◯ ◯ ◯ ◯
テキスト text255 ー ー ー ー ー ー
text32k ー ー ー ー ー ー
真偽型 文字列型 日付時刻型
BOOLEAN CHAR VARCHAR DATE TIMESTAMP
無圧縮 raw ◯ ◯ ◯ ◯ ◯
バイトディクショナリ bytedict ー ◯ ◯ ◯ ◯
デルタ delta ー ー ー ◯ ◯
delta32k ー ー ー ◯ ◯
LZO lzo ー ◯ ◯ ◯ ◯
mostly mostly8 ー ー ー ー ー
mostly16 ー ー ー ー ー
mostly32 ー ー ー ー ー
ランレングス runlength ◯ ◯ ◯ ◯ ◯
テキスト text255 ー ー ◯ ー ー
text32k ー ー ◯ ー ー

まとめ

以上、Redshiftに於ける『データ型』と『列圧縮タイプ』について、情報整理した内容をご紹介してみました。

『列圧縮タイプ』についてはここで紹介したタイミングの他にも、各種チューニング等で微調整を行う際に変更を行う事もあるかと思います。その際にこの辺りのネタが参考になれば幸いです。

Share this article

facebook logohatena logotwitter logo

関連記事

「AWSにおける生成AI＆データの全体像」というタイトルで 第8回 Classmethod AI Talks(CATs)に登壇しました #catalks

「AWSにおける生成AI＆データの全体像」というタイトルで 第8回 Classmethod AI Talks(CATs)に登壇しました #catalks

User avatar
石川覚
2024.11.21
[アップデート] Amazon Redshift デフォルトの動作変更によりセキュリティを強化

[アップデート] Amazon Redshift デフォルトの動作変更によりセキュリティを強化

User avatar
石川覚
2024.11.20
Amazon Redshift 分散スタイルのテーブル最適化を有効化するビューを作成してみた

Amazon Redshift 分散スタイルのテーブル最適化を有効化するビューを作成してみた

User avatar
石川覚
2024.11.15
Amazon Redshift ソートキーのテーブル最適化を有効化するビューを作成してみた

Amazon Redshift ソートキーのテーブル最適化を有効化するビューを作成してみた

User avatar
石川覚
2024.11.14
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.