Redshiftで圧縮分析・列圧縮エンコーディングの変更をやってみた
今回は稼働している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コマンドを実行します。
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」というツールがあるので、今回はこちらを使用して移行します。
このツールの使い方や注意点などは、下記の記事で紹介されているのであわせてご確認ください。
列圧縮エンコーディングの変更を実施しました。
$ 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のダイエットをさせてみてはいかがでしょうか。
どなたかの参考になれば幸いです。以上、データアナリティクス事業本部のナガマサでした。
![[アップデート] Amazon Redshift デフォルトの動作変更によりセキュリティを強化](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
