table row count from hive metastore

[#1]

Hi, is it possible to find out the number of rows in a table from the hive metastore?

I don't want to run "select count(*) from <table>" from the hive prompt. Rather I am looking to find that count from the hive metadata.

Appreciate any insights.

[#2]

Hi @n c

Hcatalog holds metadata of a table details like Schema, Index, Roles, Structure, Bucketing, Partitions keys, Columns, privileges, When the table was created, By whom it was created etc. But It doesn't contain any details about how many records are stored in each table.

[#3]

Alternatively analyze table tbl_name compute statistics; will give you no of files, no of records and its size.

Hope it helps.

[#4]

If table statistics are updated you can run DESC FORMATTED <table_name>. This will display row count.

[#5]

As of the HDP 2.6.1 release here is a query that I use to find row counts on a specific partitioned table:

SELECT * FROM hive.PARTITION_PARAMS AS A, hive.PARTITIONS AS B WHERE A.PARAM_KEY='numRows' and A.PART_ID=B.PART_ID and A.PART_ID IN ( SELECT PART_ID FROM hive.PARTITIONS WHERE TBL_ID=(SELECT A.TBL_ID FROM hive.TBLS AS A, hive.DBS AS B WHERE A.DB_ID=B.DB_ID AND B.NAME='DATABASE_NAME' AND A.TBL_NAME='TABLE_NAME'))

This specific query gives me row counts for each partition of a specific table. You need to replace DATABASE_NAME and TABLE_NAME with the one you are interested in.

MySQL metastore but should work with others.

[#6]

@n c

It works after I set this in hive-site.xml

I am not sure the reason, but it was forced to run mapreduce

hive.compute.query.using.stats=false