Hive system database tables

SYS db tables

The following list shows the functional groups of tables in the sys database:

• Catalog — DBS, TBLS, PARTITIONS, COLUMNS_V2, TABLE_PARAMS, DATABASE_PARAMS, PARTITION_PARAMS
• Storage — SDS, SERDES, SERDE_PARAMS, SD_PARAMS
• Authorization — TBL_PRIVS, DB_PRIVS, PART_COL_PRIVS
• Transaction — COMPACTION_QUEUE, TXNS, NOTIFICATION_LOG, REPLICATION_METRICS

Metadata query templates

The following query templates demonstrate how to inspect Hive Metastore metadata safely through the sys database:

Retrieve table information

USE sys;  
SELECT   
  d.name AS db_name,   
  t.tbl_name   
FROM DBS d   
JOIN TBLS t   
  ON d.db_id = t.db_id   
WHERE t.tbl_type <> 'VIRTUAL_VIEW';

Fetch table location

USE sys;  
SELECT   
  d.name AS db_name,   
  t.tbl_name,   
  s.location   
FROM DBS d   
JOIN TBLS t   
  ON d.db_id = t.db_id   
JOIN SDS s   
  ON t.sd_id = s.sd_id  
WHERE t.tbl_type <> 'VIRTUAL_VIEW';

Fetch partition location

USE sys;  
SELECT   
  d.name AS db_name,   
  t.tbl_name,   
  p.part_name,   
  s.location   
FROM DBS d   
JOIN TBLS t   
  ON d.db_id = t.db_id   
JOIN PARTITIONS p   
  ON p.tbl_id = t.tbl_id   
JOIN SDS s   
  ON p.sd_id = s.sd_id  
WHERE t.tbl_type <> 'VIRTUAL_VIEW';