question Viewing Hive Column or Table level Statistics in Support Questions

Viewing Hive Column or Table level Statistics

wfloyd — Thu, 17 Dec 2015 02:56:19 GMT

Does anyone know of a way to view the statistics which are created after the command "analyze table [myTable] compute statistics;" is executed?

Referenced from here: http://hortonworks.com/blog/5-ways-make-hive-queries-run-faster/

Re: Viewing Hive Column or Table level Statistics

pardeep_kumar — Thu, 17 Dec 2015 02:58:24 GMT

@Wes Floyd Here is Hive Stats detail https://cwiki.apache.org/confluence/display/Hive/S...

You can view the stored statistics by issuing the DESCRIBE command. Statistics are stored in the Parameters array. Suppose you issue the analyze command for the whole table Table1, then issue the command:

DESCRIBE EXTENDED TABLE1;

then among the output, the following would be displayed:

... , parameters:{numPartitions=4, numFiles=16, numRows=2000, totalSize=16384, ...}, ....

Re: Viewing Hive Column or Table level Statistics

gbraccialli3 — Thu, 17 Dec 2015 07:34:29 GMT

@Pardeep

See this thread here: https://community.hortonworks.com/questions/4759/hive-explain-says-plan-not-optimized-by-cbo-due-to.html

We couldn't find a way to see "columns" stats (analyze table t compute statistics for columns). I think describe extended shows only table stats.

Also looking for a solution to get rid of warning: Plan not optimized by CBO due to missing statistics. Please check log for more details, from above question.

Re: Viewing Hive Column or Table level Statistics

henrysowell — Fri, 18 Dec 2015 21:55:34 GMT

@Guilherme Braccialli If you've already analyzed the columns you can issue a describe table command to get column stats:

"As of Hive 0.10.0, the optional parameter FOR COLUMNS computes column statistics for all columns in the specified table (and for all partitions if the table is partitioned). See Column Statistics in Hive for details.

To display these statistics, use DESCRIBE FORMATTED [db_name.]table_name column_name [PARTITION (partition_spec)]."

https://cwiki.apache.org/confluence/display/Hive/StatsDev#StatsDev-ExistingTables

Re: Viewing Hive Column or Table level Statistics

gbraccialli3 — Fri, 18 Dec 2015 21:57:50 GMT

Yes, @Neeraj Sabharwal and I tried this few times, but we can't see column statistics, only table level.

Re: Viewing Hive Column or Table level Statistics

aervits — Wed, 03 Feb 2016 10:21:17 GMT

@Wes Floyd has this been resolved? Please provide your solution or accept best answer.

Re: Viewing Hive Column or Table level Statistics

wfloyd — Wed, 03 Feb 2016 10:33:23 GMT

Thank you Pardeep. This helps me understand how to see Table level statistics. Do you have a solution for Column level stats also?

Re: Viewing Hive Column or Table level Statistics

whitsonk — Wed, 27 Jul 2016 23:13:02 GMT

For those interested in viewing column level stats try this...

analyze table orderdetails compute statistics for columns;
describe formatted orderdetails.unitprice;

col_name  data_type min max  num_nulls distinct_count avg_col_len max_col_len num_trues num_falses comment
unitprice double    2.0 26.3 0         127                                                         foo

Re: Viewing Hive Column or Table level Statistics

LesterMartin — Mon, 20 Feb 2017 08:27:27 GMT

I've got a working example at https://github.com/lestermartin/oss-transform-processing-comparison/tree/master/profiling#hive that shows column stats.

Re: Viewing Hive Column or Table level Statistics

carter — Fri, 30 Jun 2017 20:29:34 GMT

For those looking for an easy graphical tool, the Hive View 2.0 (included with Ambari 2.5 and up) has the ability to view table and column level stats, and to compute them if they are missing.

For more info see https://hortonworks.com/blog/3-great-reasons-to-try-hive-view-2-0/

Note that column stats are listed under table stats and you can see the individual column's statistics there.

Re: Viewing Hive Column or Table level Statistics

ekoifman — Thu, 13 Sep 2018 04:23:39 GMT

desc formatted <table> <column>

https://cwiki.apache.org/confluence/display/Hive/StatsDev#StatsDev-Examples