You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Butao Zhang (Jira)" <ji...@apache.org> on 2024/06/21 03:15:00 UTC
[jira] [Commented] (HIVE-27099) Iceberg: select count(*) from table queries all data
[ https://issues.apache.org/jira/browse/HIVE-27099?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17856649#comment-17856649 ]
Butao Zhang commented on HIVE-27099:
------------------------------------
For iceberg table without delete file, we can optimize the count(*) by using iceberg metadata file. Like HIVE-28268 wants to do.
> Iceberg: select count(*) from table queries all data
> ----------------------------------------------------
>
> Key: HIVE-27099
> URL: https://issues.apache.org/jira/browse/HIVE-27099
> Project: Hive
> Issue Type: Improvement
> Reporter: Rajesh Balamohan
> Priority: Major
> Labels: performance
>
> select count is scanning all data. Though it has complete basic stats, it launched tez job which wasn't needed. Second issue is, it ended up scanning ENTIRE 148 GB dataset which is completely not required. It should have got the data from parq files itself. Ideal situation is getting entire records from manifest itself.
> Data is stored in parquet format in external tables. This may be broken for parquet, as for ORC it is able to read less data (footer info).
> 1. Consider fixing count( * ) for parq
> 2. Check if it is possible to read stats from iceberg manifests after #1.
> {noformat}
> explain select count(*) from store_sales;
> Explain
> STAGE DEPENDENCIES:
> Stage-1 is a root stage
> Stage-0 depends on stages: Stage-1
> STAGE PLANS:
> Stage: Stage-1
> Tez
> DagId: hive_20230223031934_2abeb3b9-8c18-4ff7-a8f9-df7368010189:5
> Edges:
> Reducer 2 <- Map 1 (CUSTOM_SIMPLE_EDGE)
> DagName: hive_20230223031934_2abeb3b9-8c18-4ff7-a8f9-df7368010189:5
> Vertices:
> Map 1
> Map Operator Tree:
> TableScan
> alias: store_sales
> Statistics: Num rows: 2879966589 Data size: 195666988943 Basic stats: COMPLETE Column stats: COMPLETE
> Select Operator
> Statistics: Num rows: 2879966589 Data size: 195666988943 Basic stats: COMPLETE Column stats: COMPLETE
> Group By Operator
> aggregations: count()
> minReductionHashAggr: 0.5
> mode: hash
> outputColumnNames: _col0
> Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
> Reduce Output Operator
> null sort order:
> sort order:
> Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
> value expressions: _col0 (type: bigint)
> Execution mode: vectorized
> Reducer 2
> Execution mode: vectorized
> Reduce Operator Tree:
> Group By Operator
> aggregations: count(VALUE._col0)
> mode: mergepartial
> outputColumnNames: _col0
> Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
> File Output Operator
> compressed: false
> Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
> table:
> input format: org.apache.hadoop.mapred.SequenceFileInputFormat
> output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
> serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
> Stage: Stage-0
> Fetch Operator
> limit: -1
> Processor Tree:
> ListSink
> 53 rows selected (1.454 seconds)
> 0: jdbc:hive2://ve0:218> select count(*) from store_sales;
> INFO : Query ID = hive_20230223031940_9ff5d61d-1fe2-4476-a561-7820e4a3a5f8
> INFO : Total jobs = 1
> INFO : Launching Job 1 out of 1
> INFO : Starting task [Stage-1:MAPRED] in serial mode
> INFO : Subscribed to counters: [] for queryId: hive_20230223031940_9ff5d61d-1fe2-4476-a561-7820e4a3a5f8
> INFO : Session is already open
> INFO : Dag name: select count(*) from store_sales (Stage-1)
> INFO : Status: Running (Executing on YARN cluster with App id application_1676286357243_0061)
> ----------------------------------------------------------------------------------------------
> VERTICES MODE STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
> ----------------------------------------------------------------------------------------------
> Map 1 .......... container SUCCEEDED 767 767 0 0 0 0
> Reducer 2 ...... container SUCCEEDED 1 1 0 0 0 0
> ----------------------------------------------------------------------------------------------
> VERTICES: 02/02 [==========================>>] 100% ELAPSED TIME: 54.94 s
> ----------------------------------------------------------------------------------------------
> INFO : Status: DAG finished successfully in 54.85 seconds
> INFO :
> INFO : Query Execution Summary
> INFO : ----------------------------------------------------------------------------------------------
> INFO : OPERATION DURATION
> INFO : ----------------------------------------------------------------------------------------------
> INFO : Compile Query 1.42s
> INFO : Prepare Plan 0.18s
> INFO : Get Query Coordinator (AM) 0.01s
> INFO : Submit Plan 0.37s
> INFO : Start DAG 0.08s
> INFO : Run DAG 54.84s
> INFO : ----------------------------------------------------------------------------------------------
> INFO :
> INFO : Task Execution Summary
> INFO : ----------------------------------------------------------------------------------------------
> INFO : VERTICES DURATION(ms) CPU_TIME(ms) GC_TIME(ms) INPUT_RECORDS OUTPUT_RECORDS
> INFO : ----------------------------------------------------------------------------------------------
> INFO : Map 1 41326.00 4,280,860 28,890 2,879,966,589 1,332
> INFO : Reducer 2 26123.00 4,750 34 767 0
> INFO : ----------------------------------------------------------------------------------------------
> INFO :
> INFO : org.apache.tez.common.counters.DAGCounter:
> INFO : NUM_SUCCEEDED_TASKS: 768
> INFO : TOTAL_LAUNCHED_TASKS: 768
> INFO : DATA_LOCAL_TASKS: 677
> INFO : RACK_LOCAL_TASKS: 90
> INFO : AM_CPU_MILLISECONDS: 101240
> INFO : AM_GC_TIME_MILLIS: 156
> INFO : File System Counters:
> INFO : FILE_BYTES_READ: 2820
> INFO : FILE_BYTES_WRITTEN: 46020
> INFO : HDFS_BYTES_WRITTEN: 110
> INFO : HDFS_READ_OPS: 2
> INFO : HDFS_WRITE_OPS: 2
> INFO : HDFS_OP_CREATE: 1
> INFO : HDFS_OP_GET_FILE_STATUS: 2
> INFO : HDFS_OP_RENAME: 1
> INFO : OFS_BYTES_READ: 148270746936
> INFO : OFS_READ_OPS: 11058
> ...
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)