Support Questions

Find answers, ask questions, and share your expertise

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Cloudera Community
- :
- Support
- :
- Support Questions
- :
- Re: Viewing Hive Column or Table level Statistics

Announcements

Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Labels:

Contributor

Created 12-16-2015 06:56 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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/

1 ACCEPTED SOLUTION

Accepted Solutions

Guru

Created 12-16-2015 06:58 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

@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`

`, ...}, ....`

10 REPLIES 10

Guru

Created 12-16-2015 06:58 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

@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

Guru

Created 12-16-2015 11:34 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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

New Contributor

Created 12-18-2015 01:55 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

@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

Guru

Created 12-18-2015 01:57 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Viewing Hive Column or Table level Statistics

Guru

Created 02-20-2017 12:27 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Viewing Hive Column or Table level Statistics

Contributor

Created 02-03-2016 02:33 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Re: Viewing Hive Column or Table level Statistics

Mentor

Created 02-03-2016 02:21 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Re: Viewing Hive Column or Table level Statistics

New Contributor

Created 07-27-2016 04:13 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

Re: Viewing Hive Column or Table level Statistics

New Contributor

Created 06-30-2017 01:29 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Coming from Hortonworks? Activate your account here