Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Count star on hive tables is giving incorrect results

Count star on hive tables is giving incorrect results

New Contributor

Count star on hive tables is giving stale values and the statistics on table are not getting updated with the right values

5 REPLIES 5

Re: Count star on hive tables is giving incorrect results

If you add files outside of Hive ( not through an insert ) the values will not get updated. That is expected. Anything else might be a jira. So how do you run the insert and what version of hadoop are you on?

I assume you ran the analyze statement again?

Re: Count star on hive tables is giving incorrect results

Just as a fyi apart from what pbalasundaram wrote you can also disable the use of statistics for queries by setting hive.compute.query.using.stats to false.

But better to enable autogather and to run statistics

Re: Count star on hive tables is giving incorrect results

New Contributor

Hi,

I am ingesting data through Hive Insert statements. If I do a select count(some_column), then it shows the right value, but if I do a count(*) it shows stale counts.

Shouldn't the statistics on table be updated automatically after every insert script runs?

Thank You.

Re: Count star on hive tables is giving incorrect results

Contributor

Please run analuze statistics on the table to confirm that this changes.

Also check for the value of hive.stats.autogather - to confirm if this is set to true

Highlighted

Re: Count star on hive tables is giving incorrect results

New Contributor

This will happen if you load data outside of Hive DDL / DML commands (for example through 3rd party ETL tools)

To deal with it, set:

hive.compute.query.using.stats=false

globally. This will cause operations like count(*) to be run as full scans. This does not otherwise interfere with Hive's CBO.