ablog

不器用で落着きのない技術者のメモ

Redshift のテーブル一覧とサイズを確認する

AWS Redshift

Amazon Redshift のテーブル一覧とサイズを AWS Labs の table_info.sql で確認する。

% git clone https://github.com/awslabs/amazon-redshift-utils.git
% cd src/AdminScripts
% psql "host=rs-spectrum01.******.us-east-1.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
Password:
psql (9.6.2, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)
Type "help" for help.

mydb=# \i table_info.sql
 schema |  table   | tableid | distkey |  skew  | sortkey | #sks |  rows   | mbytes | enc |        pct_enc        | pct_of_total | pct_stats_off | pct_unsorted
--------+----------+---------+---------+--------+---------+------+---------+--------+-----+-----------------------+--------------+---------------+--------------
 public | customer |  139204 | EVEN    | 1.0000 |         |    0 | 3000000 |    188 | Y   | 52.941176470588235200 |         0.04 |          0.00 |
 public | dwdate   |  139206 | EVEN    | 1.0000 |         |    0 |    2556 |     40 | Y   | 69.230769230769230700 |         0.01 |          0.00 |
(2 rows)

mydb=# select name, count(*) as MB  from svv_diskusage
group by name
order by name;

                                   name                                   |  mb
--------------------------------------------------------------------------+-------
 customer                                                                 |   188
 dwdate                                                                   |    40
 lineorder                                                                | 34280
 part2                                                                    |    74
(4 rows)

mydb=# select name, slice, count(*) as MB  from svv_diskusage
group by name, slice
order by name, slice;

                                   name                                   | slice |  mb
--------------------------------------------------------------------------+-------+-------
 customer                                                                 |     0 |    94
 customer                                                                 |     1 |    94
 dwdate                                                                   |     0 |    20
 dwdate                                                                   |     1 |    20
 lineorder                                                                |     0 | 17139
 lineorder                                                                |     1 | 17141
 part2                                                                    |     0 |    37
 part2                                                                    |     1 |    37
(8 rows)

参考