question Re: Why 'count()' query of hive external table has different performance compared to internal table ? in Support Questions

Why 'count()' query of hive external table has different performance compared to internal table ?

forhive — Thu, 28 Nov 2024 17:13:37 GMT

I have create two partitioned tables with same ddl, except for that one is internal table , another is external table 。Both has same file location (oss://xxxx/xxx )。

create table test_analyze( id int, name varchar(20)) partitioned by (dt string) ; create table test_analyze_external( id int, name varchar(20)) partitioned by (dt string) ; msck repair table test_analyze; analyze table test_analyze partition(dt='20241121') compute statistics; msck repair table test_analyze_external; analyze table test_analyze_external partition(dt='20241121') compute statistics;

when I query count() for table 'test_analyze'， then quickly get the result , it seems fetch result from stats。 but when I send the same query to table 'test_analyze_external', it can't return stats result , and active a MR job。And it's sql-explain below：

And I'm sure of having the config " set hive.compute.query.using.stats = true "，so, why???

Who can explain？

Re: Why 'count()' query of hive external table has different performance compared to internal table ?

DianaTorres — Thu, 28 Nov 2024 17:47:37 GMT

@forhive Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive experts @ggangadharan @james_jones who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Re: Why 'count()' query of hive external table has different performance compared to internal table ?

ggangadharan — Fri, 29 Nov 2024 07:47:10 GMT

First of all , It is not recommended to use the same location for both internal and external tables.
Internal tables in Hive are native tables that are fully controlled by Hive itself.
External tables, on the other hand, can be accessed by other components such as Spark, Impala, and File system operations,.....etc.
Since External tables are used by other components, their corresponding locations need to be relied upon.
To read the files and obtain the count, Hive launches a MapReduce job for external tables.
It is recommended to use Managed tables if other components are not utilizing the corresponding table.

Re: Why 'count()' query of hive external table has different performance compared to internal table ?

forhive — Fri, 29 Nov 2024 14:51:21 GMT

Regarding the fact that both external tables and internal tables use the same file location, and please forgive my improper usage. But I'm just testing.

The part that confuses me is that after using the same MSCK REPAIR TABLE + ANALYZE TABLE commands on both tables, when I run the same COUNT() query, why does Hive apply query optimization for one but not for the other? What is the underlying principle behind Hive's internal design that leads to this distinction?

Re: Why 'count()' query of hive external table has different performance compared to internal table ?

forhive — Fri, 29 Nov 2024 14:51:58 GMT

many thanks !