INFO-统计hive指定库下表相关信息

适用模块

具体说明

使用示例

1、表进行统计分析
analyze table dbname.tablename compute statistics;
2、统计信息

select
  t.db_t,
  MAX(
    CASE
      WHEN table_params.param_key = 'comment' THEN table_params.param_value
      ELSE NULL
    END
  ) AS comment,
  MAX(
    CASE
      WHEN table_params.param_key = 'numRows' THEN table_params.param_value
      ELSE NULL
    END
  ) AS numRows,
  MAX(
    CASE
      WHEN table_params.param_key = 'totalSize' THEN table_params.param_value / 1024
      ELSE NULL
    END
  ) AS totalSize
from
  (
    select
      TBLS.TBL_ID,
      concat_ws('.', DBS.NAME, TBLS.TBL_NAME) AS db_t
    from
      DBS
      inner join TBLS on DBS.DB_ID = TBLS.DB_ID
    where
      DBS.NAME in ('mammut_qa', 'easyaccess_hivedb')
  ) t
  inner join table_params on t.TBL_ID = table_params.TBL_ID
where
  param_key in ('numRows', 'totalSize', 'comment')
group by
  t.db_t;

作者：林帅