Redshift Script

• subrahmanyam113

  SSC Enthusiast

  Points: 117

  More actions

  November 1, 2016 at 7:59 am

  #314596

  Hi I am using a script to get details about database name, schema name, table name, row count and size of each table in GB. I would like to add one extra column '% of Disk Utilization' to the table which will calculate the percentage of size utilized by each table. This should be like

  % of Disk Utilization = [(Size of each table)/(Total size of all tables)]*100

  Also, this column values should be sorted out in descending order.

  Can any one help me out please.

  Here is the script that needs to be edited.

  SELECT db_name

  ,schema_name

  ,table_name

  ,row_count

  ,size_in_gb

  FROM (SELECT id table_id

  ,datname db_name

  ,nspname schema_name

  ,relname table_name

  ,SUM(rows) row_count

  FROM stv_tbl_perm

  JOIN pg_class

  ON pg_class.oid = stv_tbl_perm.id

  JOIN pg_namespace

  ON pg_namespace.oid = relnamespace

  JOIN pg_database

  ON pg_database.oid = stv_tbl_perm.db_id

  GROUP BY id, datname, nspname, relname

  ORDER BY id, datname, nspname, relname) tbl_det

  JOIN (SELECT tbl table_id

  ,ROUND(CONVERT(REAL,COUNT(*))/1024,2) size_in_gb

  FROM stv_blocklist bloc

  GROUP BY tbl) tbl_size

  ON tbl_size.table_id = tbl_det.table_id

  ;

• HanShi

  SSC-Dedicated

  Points: 33513

  More actions

  November 1, 2016 at 8:07 am

  #1909872

  Duplicate post, please respond only to the other thread Redshift Script.

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **