Redshiftで圧縮分析・列圧縮エンコーディングの変更をやってみた

nagamasa

2020.09.17

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

今回は稼働しているRedshiftの既存テーブルに対して、圧縮分析・列圧縮エンコーディングの変更をする機会があったのでまとめておきます。

やったこと

既存テーブルの圧縮分析
列圧縮エンコーディングの変更

事前準備

(データが入っている)既存テーブルが必要なため、任意のRedshiftクラスタを作成し、サンプルデータをロードします。サンプルデータは下記ページの一部(lineorderテーブル)を拝借しました。

サンプルテーブルを作成する

テーブルを作成します。
※今回は未圧縮→圧縮の際のデータ容量の変化を確認したいため、未圧縮のエンコーディング(RAW)を指定します。
※テーブル作成の際に列圧縮エンコーディングが未指定の場合、自動的に列圧縮エンコーディングが割り当てられます。割り当ての仕様についてはこちらを参照ください。

CREATE TABLE lineorder 
(
  lo_orderkey          INTEGER NOT NULL ENCODE RAW,
  lo_linenumber        INTEGER NOT NULL ENCODE RAW,
  lo_custkey           INTEGER NOT NULL ENCODE RAW,
  lo_partkey           INTEGER NOT NULL ENCODE RAW,
  lo_suppkey           INTEGER NOT NULL ENCODE RAW,
  lo_orderdate         INTEGER NOT NULL ENCODE RAW,
  lo_orderpriority     VARCHAR(15) NOT NULL ENCODE RAW,
  lo_shippriority      VARCHAR(1) NOT NULL ENCODE RAW,
  lo_quantity          INTEGER NOT NULL ENCODE RAW,
  lo_extendedprice     INTEGER NOT NULL ENCODE RAW,
  lo_ordertotalprice   INTEGER NOT NULL ENCODE RAW,
  lo_discount          INTEGER NOT NULL ENCODE RAW,
  lo_revenue           INTEGER NOT NULL ENCODE RAW,
  lo_supplycost        INTEGER NOT NULL ENCODE RAW,
  lo_tax               INTEGER NOT NULL ENCODE RAW,
  lo_commitdate        INTEGER NOT NULL ENCODE RAW,
  lo_shipmode          VARCHAR(10) NOT NULL ENCODE RAW
);

未圧縮であることを確認します。

dev=# select "column", type, encoding from pg_table_def where tablename = 'lineorder';
       column       |         type          | encoding
--------------------+-----------------------+----------
 lo_orderkey        | integer               | none
 lo_linenumber      | integer               | none
 lo_custkey         | integer               | none
 lo_partkey         | integer               | none
 lo_suppkey         | integer               | none
 lo_orderdate       | integer               | none
 lo_orderpriority   | character varying(15) | none
 lo_shippriority    | character varying(1)  | none
 lo_quantity        | integer               | none
 lo_extendedprice   | integer               | none
 lo_ordertotalprice | integer               | none
 lo_discount        | integer               | none
 lo_revenue         | integer               | none
 lo_supplycost      | integer               | none
 lo_tax             | integer               | none
 lo_commitdate      | integer               | none
 lo_shipmode        | character varying(10) | none
(17 rows)
dev=#

既に用意されてあるサンプルデータをロードします。
※今回は未圧縮→圧縮の際のデータ容量の変化を確認したいため、自動圧縮OFF（compupdate off）を指定してロードしました。

copy lineorder from 's3://awssampledb/load/lo/lineorder-single.tbl000.gz' 
credentials 'aws_access_key_id=<Access-Key-ID>;aws_secret_access_key=<Secret-Access-Key>'
gzip compupdate off region 'us-east-1';

14996590件のデータをロードしました。

dev=# select count(*) from lineorder;
  count   
----------
 14996590
(1 row)

既存テーブルの圧縮分析

(データが入っている)既存テーブルの圧縮分析を行う場合、ANALYZE COMPRESSIONコマンドを使用します。
ANALYZE COMPRESSIONコマンドの詳細については、下記を参照ください。

ANALYZE COMPRESSION

ANALYZE COMPRESSION は排他的テーブルロックを取得し、テーブルに対する同時読み取り書き込みが防止されます。ANALYZE COMPRESSION コマンドは、テーブルがアイドル状態になっている場合にのみ実行してください。

注意点としては、上記の通り分析対象テーブルへの書き込みがされない時間で実行するなどの考慮をする必要があります。

テーブル名指定でANALYZE COMPRESSIONコマンドを実行します。

dev=# analyze compression lineorder;
   Table   |       Column       | Encoding | Est_reduction_pct
-----------+--------------------+----------+-------------------
 lineorder | lo_orderkey        | az64     | 21.88
 lineorder | lo_linenumber      | az64     | 87.50
 lineorder | lo_custkey         | az64     | 28.13
 lineorder | lo_partkey         | az64     | 34.38
 lineorder | lo_suppkey         | az64     | 34.38
 lineorder | lo_orderdate       | zstd     | 99.56
 lineorder | lo_orderpriority   | bytedict | 91.38
 lineorder | lo_shippriority    | zstd     | 99.96
 lineorder | lo_quantity        | az64     | 78.12
 lineorder | lo_extendedprice   | zstd     | 27.02
 lineorder | lo_ordertotalprice | zstd     | 24.91
 lineorder | lo_discount        | az64     | 84.38
 lineorder | lo_revenue         | zstd     | 26.75
 lineorder | lo_supplycost      | az64     | 45.98
 lineorder | lo_tax             | az64     | 84.38
 lineorder | lo_commitdate      | az64     | 69.54
 lineorder | lo_shipmode        | bytedict | 87.93
(17 rows)
dev=#

Encoding(推奨される列圧縮エンコーディング)、Est_reduction_pct(圧縮可能率)が出力されました。
Est_reduction_pct(圧縮可能率)については、下記の記事に非常にわかりやすい説明があり助かりました。

Amazon Redshiftの ANALYZE COMPRESSIONの圧縮可能率に関して

推定値であること
推奨される列圧縮エンコーディングに指定した場合に減少するストレージ容量の割合を示していること
(上記の出力結果よりlo_linenumberカラムの現時点のデータ容量が100MBと仮定した場合、az64で列圧縮エンコーディングを定義することで87.50%のデータ容量が削減され12.50MBになる見積であること)

こちらの記事のSQLを拝借して、現時点での対象テーブルのデータ容量を確認したところ、1290MB ≒ 1.2GBでした。　※size の単位はMB

dev=# select name as tbl_name, cnt as size from
dev-# (
dev(#   select tbl, count(*) as cnt
dev(#   from stv_blocklist
dev(#   where tbl in (
dev(#   select id
dev(#   from stv_tbl_perm)
dev(#   group by tbl
dev(# ) block_list
dev-# inner join stv_tbl_perm
dev-# on block_list.tbl=stv_tbl_perm.id
dev-# group by tbl, stv_tbl_perm.name, block_list.cnt
dev-# order by cnt;
   tbl_name                        | size
-----------------------------------+------
 lineorder                         | 1290
(1 row)
dev=#

列圧縮エンコーディングの変更

(データが入っている)既存テーブルの列圧縮エンコーディングを変更する方法としては、新規で列圧縮エンコーディング定義済みのテーブルを作成しデータを移行させていきます。

この移行作業を一括で実行してくれる「ColumnEncodingUtility」というツールがあるので、今回はこちらを使用して移行します。

amazon-redshift-utils - ColumnEncodingUtility

このツールの使い方や注意点などは、下記の記事で紹介されているのであわせてご確認ください。

Amazon Redshift: テーブル列圧縮定義の診断・テーブル間データ移行ツール『Amazon Redshift Column Encoding Utility』

列圧縮エンコーディングの変更を実施しました。

$ python3 analyze-schema-compression.py \
--db-host XXXXXXXXXXXXXXXXXX.redshift.amazonaws.com \
--db dev \
--db-user XXXXXXX \
--analyze-schema public \
--analyze-table lineorder \
--do-execute true
Password <XXXXXXX>:
-- [94077] [94077] Running set statement_timeout = '1200000'
-- [94077] Success.
-- [94077] [94077] Running set application_name to 'ColumnEncodingUtility-v.9.3.4'
-- [94077] Success.
-- [94077] Connected to XXXXXXXXXXXXXXXXXX.redshift.amazonaws.com:5439:dev as XXXXXXX
-- [94077] Analyzing Table 'lineorder' for Columnar Encoding Optimisations with 1 Threads...
-- [94077] Recommended encoding changes will be applied automatically...
-- [94077] Analyzing 1 table(s) which contain allocated data blocks
-- [94077] Table public.lineorder contains 17 unoptimised columns
-- [94077] Analyzing Table 'public.lineorder'
-- [94077] Column Encoding will be modified for public.lineorder
/* [94077]
[94077] Running begin;
lock table public."lineorder";
create table public."lineorder_$mig"(
"lo_orderkey" integer  NOT NULL encode az64
,"lo_linenumber" integer  NOT NULL encode az64
,"lo_custkey" integer  NOT NULL encode az64
,"lo_partkey" integer  NOT NULL encode az64
,"lo_suppkey" integer  NOT NULL encode az64
,"lo_orderdate" integer  NOT NULL encode zstd
,"lo_orderpriority" varchar(15)  NOT NULL encode bytedict
,"lo_shippriority" varchar(1)  NOT NULL encode zstd
,"lo_quantity" integer  NOT NULL encode az64
,"lo_extendedprice" integer  NOT NULL encode zstd
,"lo_ordertotalprice" integer  NOT NULL encode zstd
,"lo_discount" integer  NOT NULL encode az64
,"lo_revenue" integer  NOT NULL encode zstd
,"lo_supplycost" integer  NOT NULL encode az64
,"lo_tax" integer  NOT NULL encode az64
,"lo_commitdate" integer  NOT NULL encode az64
,"lo_shipmode" varchar(10)  NOT NULL encode bytedict
)
;
*/
-- [94077] Success.
-- [94077] [94077] Running alter table public."lineorder_$mig" owner to awsuser;
-- [94077] Success.
-- [94077] [94077] Running insert into public."lineorder_$mig"  select * from public."lineorder";
-- [94077] Success.
-- [94077] [94077] Running analyze public."lineorder_$mig";
-- [94077] Success.
-- [94077] [94077] Running alter table public."lineorder" rename to "lineorder_20200916_7ABiZovtns_$old";
-- [94077] Success.
-- [94077] [94077] Running alter table public."lineorder_$mig" rename to "lineorder";
-- [94077] Success.
-- [94077] [94077] Running commit;
-- [94077] Success.
begin;
lock table public."lineorder";
create table public."lineorder_$mig"(
"lo_orderkey" integer  NOT NULL encode az64
,"lo_linenumber" integer  NOT NULL encode az64
,"lo_custkey" integer  NOT NULL encode az64
,"lo_partkey" integer  NOT NULL encode az64
,"lo_suppkey" integer  NOT NULL encode az64
,"lo_orderdate" integer  NOT NULL encode zstd
,"lo_orderpriority" varchar(15)  NOT NULL encode bytedict
,"lo_shippriority" varchar(1)  NOT NULL encode zstd
,"lo_quantity" integer  NOT NULL encode az64
,"lo_extendedprice" integer  NOT NULL encode zstd
,"lo_ordertotalprice" integer  NOT NULL encode zstd
,"lo_discount" integer  NOT NULL encode az64
,"lo_revenue" integer  NOT NULL encode zstd
,"lo_supplycost" integer  NOT NULL encode az64
,"lo_tax" integer  NOT NULL encode az64
,"lo_commitdate" integer  NOT NULL encode az64
,"lo_shipmode" varchar(10)  NOT NULL encode bytedict
)
;
alter table public."lineorder_$mig" owner to awsuser;
insert into public."lineorder_$mig"  select * from public."lineorder";
analyze public."lineorder_$mig";
alter table public."lineorder" rename to "lineorder_20200916_7ABiZovtns_$old";
alter table public."lineorder_$mig" rename to "lineorder";
commit;
-- [94077] Performed modification of 1 tables
$

対象テーブルの列圧縮エンコーディング定義が更新されていました。

dev=# select "column", type, encoding from pg_table_def where tablename = 'lineorder';
       column       |         type          | encoding
--------------------+-----------------------+----------
 lo_orderkey        | integer               | az64
 lo_linenumber      | integer               | az64
 lo_custkey         | integer               | az64
 lo_partkey         | integer               | az64
 lo_suppkey         | integer               | az64
 lo_orderdate       | integer               | zstd
 lo_orderpriority   | character varying(15) | bytedict
 lo_shippriority    | character varying(1)  | zstd
 lo_quantity        | integer               | az64
 lo_extendedprice   | integer               | zstd
 lo_ordertotalprice | integer               | zstd
 lo_discount        | integer               | az64
 lo_revenue         | integer               | zstd
 lo_supplycost      | integer               | az64
 lo_tax             | integer               | az64
 lo_commitdate      | integer               | az64
 lo_shipmode        | character varying(10) | bytedict
(17 rows)
dev=#

再度テーブルのデータ容量を確認したところ、対象テーブルのデータ容量が550MB≒0.5GBとなり、約58%(1290MB→550MB)のデータ容量削減が確認できました。　※size の単位はMB

dev=# select name as tbl_name, cnt as size from
dev-# (
dev(#   select tbl, count(*) as cnt
dev(#   from stv_blocklist
dev(#   where tbl in (
dev(#   select id
dev(#   from stv_tbl_perm)
dev(#   group by tbl
dev(# ) block_list
dev-# inner join stv_tbl_perm
dev-# on block_list.tbl=stv_tbl_perm.id
dev-# group by tbl, stv_tbl_perm.name, block_list.cnt
dev-# order by cnt;
tbl_name                            | size
------------------------------------+------
 lineorder                          |  550
 lineorder_20200916_7abizovtns_$old | 1290
(2 rows)
dev=#

移行元のテーブル(lineorder_20200916_7abizovtns_$old)は、バックアップ等の必要がなければ手動で削除します。

再度、対象テーブルを指定してANALYZE COMPRESSIONコマンドを実行してみます。

dev=# analyze compression lineorder;
   Table   |       Column       | Encoding | Est_reduction_pct 
-----------+--------------------+----------+-------------------
 lineorder | lo_orderkey        | az64     | 0.00
 lineorder | lo_linenumber      | az64     | 0.00
 lineorder | lo_custkey         | az64     | 0.00
 lineorder | lo_partkey         | az64     | 0.00
 lineorder | lo_suppkey         | az64     | 0.00
 lineorder | lo_orderdate       | zstd     | 0.00
 lineorder | lo_orderpriority   | bytedict | 0.00
 lineorder | lo_shippriority    | zstd     | 0.00
 lineorder | lo_quantity        | az64     | 0.00
 lineorder | lo_extendedprice   | zstd     | 0.00
 lineorder | lo_ordertotalprice | zstd     | 0.00
 lineorder | lo_discount        | az64     | 0.00
 lineorder | lo_revenue         | zstd     | 0.00
 lineorder | lo_supplycost      | az64     | 0.00
 lineorder | lo_tax             | az64     | 0.00
 lineorder | lo_commitdate      | az64     | 0.00
 lineorder | lo_shipmode        | bytedict | 0.00
(17 rows)

推奨された列圧縮エンコーディングでデータが格納されているため、圧縮可能率(Est_reduction_pct)は全て0.00%となりました！

さいごに

簡単ではございますが、Redshiftの圧縮分析・列圧縮エンコーディングの変更についてまとめました。
今回の記事ではANALYZE COMPRESSIONコマンド実行〜ColumnEncodingUtilityツールの実行までの一連の流れをまとめておきたかったため、1テーブルのみ、かつ未圧縮→圧縮にした場合での検証でした。

一般的に本番環境で稼働しているRedshiftクラスタではテーブルの数、データ件数が膨大かつ既に列圧縮エンコーディングが定義済みかと思われます。ですが長く運用しているRedshiftクラスタこそ、データ状況の変化が大きく、そのデータ状況に適切な列圧縮エンコーディングが推奨されると思われます。また比較的新しく追加された列圧縮エンコーディング(az64,zstd)で定義されてない場合は、それらが推奨される可能性があります。

既に定義されている列圧縮エンコーディングが本当に適切なものなのか、適宜見直す必要があると思います。
必要に応じてRedshiftのダイエットをさせてみてはいかがでしょうか。

どなたかの参考になれば幸いです。以上、データアナリティクス事業本部のナガマサでした。

Redshiftで圧縮分析・列圧縮エンコーディングの変更をやってみた

やったこと

事前準備

既存テーブルの圧縮分析

列圧縮エンコーディングの変更

さいごに

参考記事

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

Redshiftで圧縮分析・列圧縮エンコーディングの変更をやってみた

やったこと

事前準備

既存テーブルの圧縮分析

列圧縮エンコーディングの変更

さいごに

参考記事

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

AWS入門ブログリレー2024〜 Amazon Redshift 編〜

Amazon DataZone の新しい生成 AIベースの新機能「AIリコメンデーション」を試してみる！

Amazon DataZone 初期導入から Amazon Redshift 統合の新機能まで解説する！

クラスメソッド データアナリティクス通信(AWSデータ分析編) – 2024年4月号

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

クラスメソッドデータアナリティクス通信(AWSデータ分析編) – 2024年4月号