Amazon Redshift データ型と列圧縮タイプのまとめ(データ型＆列圧縮タイプ対応表付)
この記事は公開されてから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では『列圧縮タイプ』という値の設定が可能となっています。用途・効能は公式ドキュメントから一部引用させて頂いたテキストをペタリ。
以下、ざっくりとそれぞれの項目がどういうものかを見て行きたいと思います。(※詳細は各対応ドキュメントをご参照ください)
raw
圧縮なし。テーブル作成時に指定しなかった場合のデフォルト値はこれになる。
bytedict
上記公式ページから引用。マスタ名称のコード値然り、マスタ名称のコード値に対する名称のような値(個数が限られており、且つ長い文字列に成り得るようなもの)等が適しているのでしょうか。
delta, delta32k
DATE型/TIMESTAMP型の項目については、あまり深く考える事無く、このタイプで決まりっぽいですね。
lzo
割と広範にデータ型をサポートしているlzo。桁数的に一定数を超過するような長めの項目であれば、これも一択で指定可能そうなタイプではあります。
mostly8, mostly16, mostly32
数値項目且つ、その数値のバラ付き度合い、値の範囲が所定規模数に収まっている場合に有効な設定値。業務仕様的に『この値は大きくても100までの値しか入らない(→mostly8が適用可能)』、『この値は最大30000まではありえる(→mostly16が適用可能)』というような判断が下せる項目に適用すると良さげな感じですね。
runlength
このテキストから読み解くに、ソートキーの項目そのものでは無く、ソートキーに拠ってソートされる列の中で、データが連続して頻繁に繰り返される列に対して指定すべき、という意味なのでしょうか。
text255, text32k
対象が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に於ける『データ型』と『列圧縮タイプ』について、情報整理した内容をご紹介してみました。
『列圧縮タイプ』についてはここで紹介したタイミングの他にも、各種チューニング等で微調整を行う際に変更を行う事もあるかと思います。その際にこの辺りのネタが参考になれば幸いです。
