Child pages
  • StatsDev
Skip to end of metadata
Go to start of metadata

Statistics in Hive

This document describes the support of statistics for Hive tables (see HIVE-33).

Motivation

Statistics such as the number of rows of a table or partition and the histograms of a particular interesting column are important in many ways. One of the key use cases of statistics is query optimization. Statistics serve as the input to the cost functions of the optimizer so that it can compare different plans and choose among them. Statistics may sometimes meet the purpose of the users' queries. Users can quickly get the answers for some of their queries by only querying stored statistics rather than firing long-running execution plans. Some examples are getting the quantile of the users' age distribution, the top 10 apps that are used by people, and the number of distinct sessions.

Scope

Table and Partition Statistics

The first milestone in supporting statistics was to support table and partition level statistics. Table and partition statistics are now stored in the Hive Metastore for either newly created or existing tables. The following statistics are currently supported for partitions:

  • Number of rows
  • Number of files
  • Size in Bytes

For tables, the same statistics are supported with the addition of the number of partitions of the table.

Version: Table and partition statistics

Table and partition level statistics were added in Hive 0.7.0 by HIVE-1361.

Column Statistics

The second milestone was to support column level statistics. See Column Statistics in Hive in the Design Documents.

Version: Column statistics

Column level statistics were added in Hive 0.10.0 by HIVE-1362.

Top K Statistics

Column level top K statistics are still pending; see HIVE-3421.

Quick overview

DescriptionStored inCollected bySince
Number of partition the dataset consists ofFictional metastore property: numPartitionscomputed during displaying the properties of a partitioned tableHive 2.3
Number of files the dataset consists ofMetastore table property: numFiles Automatically during Metastore operations 
Total size of the dataset as its seen at the filesystem levelMetastore table property: totalSize  
Uncompressed size of the datasetMetastore table property: rawDataSize

Computed, these are the basic statistics. Calculated automatically when Configuration Properties#hive.stats.autogather is enabled.
Can be collected manually by: ANALYZE TABLE ... COMPUTE STATISTICS

Hive 0.8
Number of rows the dataset consist ofMetastore table property: numRows 

Column level statistics

Metastore; TAB_COL_STATS tableComputed, Calculated automatically when Configuration Properties#hive.stats.column.autogather is enabled.
Can be collected manually by: ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS
 

 

Implementation

The way the statistics are calculated is similar for both newly created and existing tables.

For newly created tables, the job that creates a new table is a MapReduce job. During the creation, every mapper while copying the rows from the source table in the FileSink operator, gathers statistics for the rows it encounters and publishes them into a Database (possibly MySQL). At the end of the MapReduce job, published statistics are aggregated and stored in the MetaStore.

A similar process happens in the case of already existing tables, where a Map-only job is created and every mapper while processing the table in the TableScan operator, gathers statistics for the rows it encounters and the same process continues.

It is clear that there is a need for a database that stores temporary gathered statistics. Currently there are two implementations, one is using MySQL and the other is using HBase. There are two pluggable interfaces IStatsPublisher and IStatsAggregator that the developer can implement to support any other storage. The interfaces are listed below:

Usage

Configuration Variables

See Statistics in Configuration Properties for a list of the variables that configure Hive table statistics. Configuring Hive describes how to use the variables.

Newly Created Tables

For newly created tables and/or partitions (that are populated through the INSERT OVERWRITE command), statistics are automatically computed by default. The user has to explicitly set the boolean variable hive.stats.autogather to false so that statistics are not automatically computed and stored into Hive MetaStore.

The user can also specify the implementation to be used for the storage of temporary statistics setting the variable hive.stats.dbclass. For example, to set HBase as the implementation of temporary statistics storage (the default is jdbc:derby or fs, depending on the Hive version) the user should issue the following command:

In case of JDBC implementations of temporary stored statistics (ex. Derby or MySQL), the user should specify the appropriate connection string to the database by setting the variable hive.stats.dbconnectionstring. Also the user should specify the appropriate JDBC driver by setting the variable hive.stats.jdbcdriver.

Queries can fail to collect stats completely accurately. There is a setting hive.stats.reliable that fails queries if the stats can't be reliably collected. This is false by default.

Existing Tables – ANALYZE

For existing tables and/or partitions, the user can issue the ANALYZE command to gather statistics and write them into Hive MetaStore. The syntax for that command is described below:

When the user issues that command, he may or may not specify the partition specs. If the user doesn't specify any partition specs, statistics are gathered for the table as well as all the partitions (if any). If certain partition specs are specified, then statistics are gathered for only those partitions. When computing statistics across all partitions, the partition columns still need to be listed. As of Hive 1.2.0, Hive fully supports qualified table name in this command. User can only compute the statistics for a table under current database if a non-qualified table name is used.

When the optional parameter NOSCAN is specified, the command won't scan files so that it's supposed to be fast. Instead of all statistics, it just gathers the following statistics:

  • Number of files
  • Physical size in bytes

Version 0.10.0: FOR COLUMNS

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)].

Examples

Suppose table Table1 has 4 partitions with the following specs:

  • Partition1: (ds='2008-04-08', hr=11)
  • Partition2: (ds='2008-04-08', hr=12)
  • Partition3: (ds='2008-04-09', hr=11)
  • Partition4: (ds='2008-04-09', hr=12)

and you issue the following command:

then statistics are gathered for partition3 (ds='2008-04-09', hr=11) only.

If you issue the command:

then column statistics are gathered for all columns for partition3 (ds='2008-04-09', hr=11). This is available in Hive 0.10.0 and later.

If you issue the command:

then statistics are gathered for partitions 3 and 4 only (hr=11 and hr=12).

If you issue the command:

then column statistics for all columns are gathered for partitions 3 and 4 only (Hive 0.10.0 and later).

If you issue the command:

then statistics are gathered for all four partitions.

If you issue the command:

then column statistics for all columns are gathered for all four partitions (Hive 0.10.0 and later).

For a non-partitioned table, you can issue the command:

to gather statistics of the table.

For a non-partitioned table, you can issue the command:

to gather column statistics of the table (Hive 0.10.0 and later).

If Table1 is a partitioned table,  then for basic statistics you have to specify partition specifications like above in the analyze statement. Otherwise a semantic analyzer exception will be thrown.

However for column statistics, if no partition specification is given in the analyze statement, statistics for all partitions are computed.

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:

then among the output, the following would be displayed:

If you issue the command:

then among the output, the following would be displayed:

If you issue the command:

then statistics, number of files and physical size in bytes are gathered for partitions 3 and 4 only.

ANALYZE TABLE <table1> CACHE METADATA

When Hive metastore is configured to use HBase, this command explicitly caches file metadata in HBase metastore.  

The goal of this feature is to cache file metadata (e.g. ORC file footers) to avoid reading lots of files from HDFS at split generation time, as well as potentially cache some information about splits (e.g. grouping based on location that would be good for some short time) to further speed up the generation and achieve better cache locality with consistent splits.

See feature details in HBase Metastore Split Cache and (HIVE-12075)

Current Status (JIRA)

Loading
T Key Summary Assignee Reporter P Status Resolution Created Updated Due
Bug HIVE-18611 Avoid memory allocation of aggregation buffer during stats computation Ashutosh Chauhan Ashutosh Chauhan Major Resolved Fixed Feb 02, 2018 Feb 07, 2018
Sub-task HIVE-18454 Incorrect rownum estimation in joins Unassigned Zoltan Haindrich Major Open Unresolved Jan 16, 2018 Jan 16, 2018
Sub-task HIVE-18313 stats are way off in many tests with acid on Unassigned Eugene Koifman Major Open Unresolved Dec 19, 2017 Dec 19, 2017
Bug HIVE-18285 StatsTask uses a cached ql.metadata.Table object Eugene Koifman Eugene Koifman Major Patch Available Unresolved Dec 15, 2017 Dec 22, 2017
Bug HIVE-18279 Incorrect condition in StatsOpimizer Oleksiy Sayankin Oleksiy Sayankin Major Patch Available Unresolved Dec 14, 2017 Dec 19, 2017
Sub-task HIVE-18235 Columnstats gather on mm tables: re-enable disabled test Zoltan Haindrich Zoltan Haindrich Major Resolved Fixed Dec 06, 2017 Feb 13, 2018
Sub-task HIVE-18178 Column stats are not autogathered for materialized views Unassigned Zoltan Haindrich Major Open Unresolved Nov 29, 2017 Nov 29, 2017
Sub-task HIVE-18163 Stats: create materialized view should also collect stats Zoltan Haindrich Zoltan Haindrich Major Resolved Fixed Nov 28, 2017 Dec 01, 2017
Sub-task HIVE-18162 Investigate bucketed table stats Unassigned Zoltan Haindrich Major Open Unresolved Nov 28, 2017 Nov 28, 2017
Sub-task HIVE-18161 Remove hive.stats.atomic Bertalan Kondrat Zoltan Haindrich Major Resolved Fixed Nov 28, 2017 Jan 16, 2018
Sub-task HIVE-18149 Stats: rownum estimation from datasize underestimates in most cases Zoltan Haindrich Zoltan Haindrich Major Resolved Fixed Nov 27, 2017 Jan 14, 2018
Sub-task HIVE-18141 Fix StatsUtils.combineRange to combine intervals Zoltan Haindrich Zoltan Haindrich Major Resolved Fixed Nov 23, 2017 Nov 27, 2017
Sub-task HIVE-18092 Fix exception on tables handled by HBaseHandler if columnsstats are auto-gathered Zoltan Haindrich Zoltan Haindrich Major Resolved Fixed Nov 17, 2017 Nov 18, 2017
Improvement HIVE-18079 Statistics: Allow HyperLogLog to be merged to the lowest-common-denominator bit-size Gopal V Gopal V Major Patch Available Unresolved Nov 16, 2017 Jan 10, 2018
Improvement HIVE-18070 Merge partitions NDV estimators in batches Jesus Camacho Rodriguez Jesus Camacho Rodriguez Major Open Unresolved Nov 15, 2017 Nov 15, 2017
Sub-task HIVE-18062 Revise basic stat states for estimations Unassigned Zoltan Haindrich Major Open Unresolved Nov 14, 2017 Nov 14, 2017
Sub-task HIVE-18036 Stats: Remove usage of clone() methods Bertalan Kondrat Zoltan Haindrich Major Resolved Fixed Nov 09, 2017 Dec 05, 2017
Sub-task HIVE-18015 Consolidate basic stats logic for standalone table / partitioned Zoltan Haindrich Zoltan Haindrich Major Open Unresolved Nov 08, 2017 Nov 20, 2017
Sub-task HIVE-18005 Improve size estimation for array() to be not 0 Zoltan Haindrich Zoltan Haindrich Major Resolved Fixed Nov 07, 2017 Dec 05, 2017
Sub-task HIVE-17971 Possible misuse of getDataSizeFromColumnStats Unassigned Zoltan Haindrich Major Open Unresolved Nov 02, 2017 Nov 09, 2017
Showing 20 out of 226 issues Refresh

  • No labels