Why Does a Hive `COUNT(*)` with a Non-Null Condition Return a Higher Count Than a Simple `COUNT(*)`?

*Hive count () exception: SELECT with non-null condition returns higher count**

In Hive, a special behavior was observed: the row count with non-empty condition returned a higher value than the count of all rows. Let's investigate this anomaly.

The table named "mytable" contains a field named "master_id". Using the count(*) function, we get:

<code class="language-sql">select count(*) as c from mytable;
c
1129563</code>

However, when adding a condition to count only non-null values ​​of "master_id", we get a much larger count:

<code class="language-sql">select count(*) as c from mytable where master_id is not null;
c
1134041</code>

Surprisingly, a separate query shows that "master_id" has no null values:

<code class="language-sql">select count(*) as c from mytable where master_id is null;
c
0</code>

Why does adding a non-null condition produce a higher count when there are no null values?

The most likely explanation lies in Hive usage statistics. By default, Hive uses statistics to optimize query planning by estimating the number of rows. However, these statistics are not always accurate.

To solve this problem, disable the usage of statistics by setting the parameter:

<code class="language-sql">set hive.compute.query.using.stats=false;</code>

Re-executing the query should now produce consistent results.

Alternatively, you can calculate statistics manually using ANALYZE TABLE syntax.

Additionally, setting hive.stats.autogather=true will automatically collect statistics during INSERT OVERWRITE operations, ensuring more accurate statistics and preventing such anomalies.

The above is the detailed content of Why Does a Hive `COUNT(*)` with a Non-Null Condition Return a Higher Count Than a Simple `COUNT(*)`?. For more information, please follow other related articles on the PHP Chinese website!