comment trouver la taille de la base de données, schéma, tableau en redshift

les réponses ci-dessus ne donnent pas toujours des réponses correctes pour l'espace de tableau utilisé. Le support AWS a donné cette requête à utiliser:

SELECT   TRIM(pgdb.datname) AS Database,
         TRIM(a.name) AS Table,
         ((b.mbytes/part.total::decimal)*100)::decimal(5,2) AS pct_of_total,
         b.mbytes,
         b.unsorted_mbytes
FROM     stv_tbl_perm a
JOIN     pg_database AS pgdb
  ON     pgdb.oid = a.db_id
JOIN     ( SELECT   tbl,
                    SUM( DECODE(unsorted, 1, 1, 0)) AS unsorted_mbytes,
                    COUNT(*) AS mbytes
           FROM     stv_blocklist
           GROUP BY tbl ) AS b
       ON a.id = b.tbl
JOIN     ( SELECT SUM(capacity) AS total
           FROM   stv_partitions
           WHERE  part_begin = 0 ) AS part
      ON 1 = 1
WHERE    a.slice = 0
ORDER BY 4 desc, db_id, name;

Oui, mo dans votre exemple, 372Mb. Voici ce que j'ai été en utilisant:

select
  cast(use2.usename as varchar(50)) as owner, 
  pgc.oid,
  trim(pgdb.datname) as Database,
  trim(pgn.nspname) as Schema,
  trim(a.name) as Table,
  b.mbytes,
  a.rows
from 
 (select db_id, id, name, sum(rows) as rows
  from stv_tbl_perm a
  group by db_id, id, name
  ) as a
 join pg_class as pgc on pgc.oid = a.id
 left join pg_user use2 on (pgc.relowner = use2.usesysid)
 join pg_namespace as pgn on pgn.oid = pgc.relnamespace 
    and pgn.nspowner > 1
 join pg_database as pgdb on pgdb.oid = a.db_id
 join 
   (select tbl, count(*) as mbytes
    from stv_blocklist
    group by tbl
   ) b on a.id = b.tbl
 order by mbytes desc, a.db_id, a.name; 

Je ne suis pas sûr de regrouper par base de données et scheme, Mais voici un court moyen d'obtenir l'utilisation par table,

SELECT tbl, name, size_mb FROM
(
  SELECT tbl, count(*) AS size_mb
  FROM stv_blocklist
  GROUP BY tbl
)
LEFT JOIN
(select distinct id, name FROM stv_tbl_perm)
ON id = tbl
ORDER BY size_mb DESC
LIMIT 10;

des versions Modifiées de l'une des autres réponses. Ceci inclut le nom de la base de données, le nom du schéma, le nom de la table, le nombre total de lignes, la taille sur le disque et la taille non triée:

-- sort by row count
select trim(pgdb.datname) as Database, trim(pgns.nspname) as Schema, trim(a.name) as Table,
    c.rows, ((b.mbytes/part.total::decimal)*100)::decimal(5,3) as pct_of_total, b.mbytes, b.unsorted_mbytes
    from stv_tbl_perm a
    join pg_class as pgtbl on pgtbl.oid = a.id
    join pg_namespace as pgns on pgns.oid = pgtbl.relnamespace
    join pg_database as pgdb on pgdb.oid = a.db_id
    join (select tbl, sum(decode(unsorted, 1, 1, 0)) as unsorted_mbytes, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl
    join (select id, sum(rows) as rows from stv_tbl_perm group by id) c on a.id=c.id
    join (select sum(capacity) as total from stv_partitions where part_begin=0) as part on 1=1
    where a.slice=0
    order by 4 desc, db_id, name;


-- sort by space used
select trim(pgdb.datname) as Database, trim(pgns.nspname) as Schema, trim(a.name) as Table,
    c.rows, ((b.mbytes/part.total::decimal)*100)::decimal(5,3) as pct_of_total, b.mbytes, b.unsorted_mbytes
    from stv_tbl_perm a
    join pg_class as pgtbl on pgtbl.oid = a.id
    join pg_namespace as pgns on pgns.oid = pgtbl.relnamespace
    join pg_database as pgdb on pgdb.oid = a.db_id
    join (select tbl, sum(decode(unsorted, 1, 1, 0)) as unsorted_mbytes, count(*) as mbytes from stv_blocklist group by tbl) b on a.id=b.tbl
    join (select id, sum(rows) as rows from stv_tbl_perm group by id) c on a.id=c.id
    join (select sum(capacity) as total from stv_partitions where part_begin=0) as part on 1=1
    where a.slice=0
    order by 6 desc, db_id, name;

Vous pouvez consulter ce dépôt, je suis sûr que vous y trouverez des choses utiles.

https://github.com/awslabs/amazon-redshift-utils

pour répondre à votre question, vous pouvez utiliser cette vue: https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_space_used_per_tbl.sql

puis interrogez comme vous voulez. e.g: select * from admin.v_space_used_per_tbl;

Cette requête est beaucoup plus facile:

-- Liste du Top 30 des plus grandes tables sur votre cluster

SELECT 
 "schema"
,"table"  AS table_name
,ROUND((size/1024.0),2) AS "Size in Gigabytes"
,pct_used AS "Physical Disk Used by This Table"
FROM svv_table_info
ORDER BY pct_used DESC
LIMIT 30;

C'est ce que j'utilise(veuillez changer le nom de la base de données de 'mydb' à votre nom de base de données):

SELECT CAST(use2.usename AS VARCHAR(50)) AS OWNER
 ,TRIM(pgdb.datname) AS DATABASE
 ,TRIM(pgn.nspname) AS SCHEMA
 ,TRIM(a.NAME) AS TABLE
 ,(b.mbytes) / 1024 AS Gigabytes
 ,a.ROWS
FROM (
 SELECT db_id
 ,id
 ,NAME
 ,SUM(ROWS) AS ROWS
 FROM stv_tbl_perm a
 GROUP BY db_id
 ,id
 ,NAME
 ) AS a
JOIN pg_class AS pgc ON pgc.oid = a.id
LEFT JOIN pg_user use2 ON (pgc.relowner = use2.usesysid)
JOIN pg_namespace AS pgn ON pgn.oid = pgc.relnamespace
 AND pgn.nspowner > 1
JOIN pg_database AS pgdb ON pgdb.oid = a.db_id
JOIN (
 SELECT tbl
 ,COUNT(*) AS mbytes
 FROM stv_blocklist
 GROUP BY tbl
 ) b ON a.id = b.tbl
WHERE pgdb.datname = 'mydb'
ORDER BY mbytes DESC
 ,a.db_id
 ,a.NAME;

src: https://aboutdatabases.wordpress.com/2015/01/24/amazon-redshift-how-to-get-the-sizes-of-all-tables/