[HIVE-16938] INFORMATION_SCHEMA usability: difficult to access # of table records - ASF JIRA

Log work

Agile Board

Rank to Top

Rank to Bottom

Bulk Copy Attachments

Bulk Move Attachments

Voters

Watch issue

Watchers

Create sub-task

Convert to sub-task

Move

Link

Clone

Labels

Update Comment Author

Replace String in Comment

Update Comment Visibility

Delete Comments

XML

Word

Printable

JSON

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 3.0.0
Component/s: None
Labels:
None

Description

~~HIVE-1010~~ adds an information schema to Hive, also taking the opportunity to expose some non-standard but valuable things like statistics in a SYS table.

One common thing users want to know is the number of rows in tables, system wide.

This information is in the table_params table but the structure of this table makes it quite inconvenient to access since it is essentially a table of key-value pairs. More table stats are likely to be added over time, especially because of ACID. It would be a lot better if this were a first class table.

For what it's worth I deal with the current table by pivoting it into something easier to deal with as follows:

create view table_stats as
select
  tbl_id,
  max(case param_key when 'COLUMN_STATS_ACCURATE' then param_value end) as COLUMN_STATS_ACCURATE,
  max(case param_key when 'numFiles' then param_value end) as numFiles,
  max(case param_key when 'numRows' then param_value end) as numRows,
  max(case param_key when 'rawDataSize' then param_value end) as rawDataSize,
  max(case param_key when 'totalSize' then param_value end) as totalSize,
  max(case param_key when 'transient_lastDdlTime' then param_value end) as transient_lastDdlTime
from table_params group by tbl_id;