tooptoop4 opened a new issue #839: hive - select Count(*) is higher than number of rows returned from select * URL: https://github.com/apache/incubator-hudi/issues/839 issue described on https://groups.google.com/forum/#!topic/hoodie-users/4dgwvQPEtNA I have created a test set and ran multiple updates over the same dataset to check if the results are returning fine. When we do a select from the hoodie table, it gives the exact snapshot after the incrmental UPSERT. But when we do aggregation on top of the table, I see that aggregation provides the result of all the previous values as well. How can I limit the aggregations to run atop only the current snapshot. As of now I am having the default compaction configurations. Any configurations which I need to tinker around ? Example shown below. hive> select * from test_table_hoodie_outout where transaction_day=20180501;; OK _hoodie_commit_time _hoodie_commit_seqno _hoodie_record_key _hoodie_partition_path _hoodie_file_name recordid recname amountts transaction_day 20180508110546 20180508110546_0_1 2 20180501 0673ca13-e6a5-4ce3-adfa-2ee2b6fab51f_0_20180508110546.parquet 2 TestRow2 767 2018-05-01 00:00:01 20180501 20180508110546 20180508110546_0_2 3 20180501 0673ca13-e6a5-4ce3-adfa-2ee2b6fab51f_0_20180508110546.parquet 3 TestRow3 948 2018-05-01 00:00:04 20180501 20180508110546 20180508110546_0_3 1 20180501 0673ca13-e6a5-4ce3-adfa-2ee2b6fab51f_0_20180508110546.parquet 1 TestRow1 756 2018-05-01 00:00:00 20180501 20180508110546 20180508110546_0_4 7 20180501 0673ca13-e6a5-4ce3-adfa-2ee2b6fab51f_0_20180508110546.parquet 7 TestRow10 748 2018-05-01 00:00:03 20180501 20180508110546 20180508110546_0_5 8 20180501 0673ca13-e6a5-4ce3-adfa-2ee2b6fab51f_0_20180508110546.parquet 8 TestRow10 748 2018-05-01 00:00:03 20180501 20180508110546 20180508110546_0_6 9 20180501 0673ca13-e6a5-4ce3-adfa-2ee2b6fab51f_0_20180508110546.parquet 9 TestRow10 748 2018-05-01 00:00:03 20180501 Time taken: 0.742 seconds, Fetched: 6 row(s) hive>select count(*),sum(amount) from test_table_hoodie_outout where transaction_day=20180501; OK _c0 _c1 36 28090 Sorry for the delay. My guess is count(*) uses statistics from underlying parquet file directly without actually calling HoodieInputFormat::listStatus() .. Can you see if the HoodieInputFormat is even invoked? We upstreamed some patches to Hive to not do this. I can check them out, if you confirm this is the case. You can also try doing SparkSQL to compare if this is a Hive issue or data issue? I did check the logs of the mappers and found that HoodieInputFormat was invoked. 2018-05-16 18:56:21,705 INFO [main] org.apache.hadoop.mapred.MapTask: Processing split: Paths:/user/myuser/testHoodieInsert/20180501/0673ca13-e6a5-4ce3-adfa-2ee2b6fab51f_0_20180508070817.parquet:0+434559InputFormatClass: com.uber.hoodie.hadoop.HoodieInputFormat Also this is not the case with count aggregate alone, I am seeing this for sum as well. Do let me know if I am using a deprecated version. This issue does not play in Spark-SQL , it happens only in hive. I did confirm the Hive patch around updating statistics did not make it to OSS.. Spark & Presto properly list the files using InputFormat and hence work well. Hive tends to few code paths that don't honor the input format, which results in issues like this.
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
