0

I get the correct count after I run the ANALYZE statement. But my problem is, it needs to be run every time of the count is updated. Technically I should be able to update the count for the same partition.

But it returns the same count if I don't execute the ANALYZE statement.

This is the query I execute for the count to be updated.

ANALYZE TABLE bi_events_identification_carrier_sam PARTITION(year, month, day) COMPUTE STATISTICS;

And executing is not convenient at all. any ideas?

Sam
  • 497
  • 1
  • 10
  • 34
  • This is already answered here: https://stackoverflow.com/a/39914232/2700344. You can do autogather during insert overwrite or switch-off statistics usage and table will be scanned each time. – leftjoin Sep 12 '18 at 15:27
  • Possible duplicate of [HIVE select count(\*) non null returns higher value than select count(\*)](https://stackoverflow.com/questions/39912784/hive-select-count-non-null-returns-higher-value-than-select-count) – leftjoin Sep 12 '18 at 15:28
  • `count(*)` is broken in Hive **by design** since V0.14 -- consistent with Facebook motto _"fail fast, fail all the time, fail big"_ > seriously, you can use a trick like `where X is null` when you know that column X is never null (but the SQL optimizer does not) – Samson Scharfrichter Sep 12 '18 at 19:09
  • @Sam What engine are you using to write data - spark/hive? – moriarty007 Sep 13 '18 at 07:15

1 Answers1

2

Your count(*) query is using stats to get the result.

If you are using spark to write data, then you can set spark.sql.statistics.size.autoUpdate.enabled to true. This makes sure that Spark updates the table stats automatically after the write is done.

If you are using Hive, you can set set hive.stats.autogather=true;.

Once you enable these settings, then the write query will automatically update the stats and the subsequent read query will work fine.

moriarty007
  • 2,054
  • 16
  • 20
  • I didnt worked. Its giving the same result and only works if I again use the ANALYZE statement. and im writing from spark. – Sam Sep 18 '18 at 12:47