You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "László Bodor (Jira)" <ji...@apache.org> on 2024/11/13 10:36:00 UTC
[jira] [Commented] (HIVE-28620) Query result is cached in case of IOWD if the subquery is not trivial
[ https://issues.apache.org/jira/browse/HIVE-28620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17897881#comment-17897881 ]
László Bodor commented on HIVE-28620:
-------------------------------------
merged to master, thanks a lot for the reviews: [~dkuzmenko], [~kkasa]
> Query result is cached in case of IOWD if the subquery is not trivial
> ---------------------------------------------------------------------
>
> Key: HIVE-28620
> URL: https://issues.apache.org/jira/browse/HIVE-28620
> Project: Hive
> Issue Type: Bug
> Security Level: Public(Viewable by anyone)
> Reporter: László Bodor
> Assignee: László Bodor
> Priority: Major
> Labels: pull-request-available
> Fix For: 4.1.0
>
>
> HIVE-25907 fixed a case when an insert overwrite directory didn't write the data to the final path when the query results cache was enabled. The solution was implemented by checking the QB.parseInfo at a certain point in the semantic analysis and writing/reading a flag to a QB instance. The problem is that depending on the query (which produces the data to be inserted) can be of different complexity, and HIVE-25907 only took care of queries like:
> {code}
> INSERT OVERWRITE DIRECTORY "<destination directory>" SELECT * FROM iowd;
> {code}
> but for queries like this:
> {code}
> EXPLAIN EXTENDED INSERT OVERWRITE DIRECTORY '/tmp' select a13.CATEGORY_ID CATEGORY_ID, max(a14.CATEGORY_DESC) CATEGORY_DESC,a12.SUBCAT_ID SUBCAT_ID,max(a13.SUBCAT_LONG_DESC) SUBCAT_DESC,avg((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) WJXBFS1,sum((a11.QTY_SOLD * a11.UNIT_COST)) WJXBFS2 from ORDER_DETAIL a11 join LU_ITEM a12 on (a11.ITEM_ID = a12.ITEM_ID) join LU_SUBCATEG a13 on (a12.SUBCAT_ID = a13.SUBCAT_ID) join LU_CATEGORY a14 on (a13.CATEGORY_ID = a14.CATEGORY_ID) group by a13.CATEGORY_ID, a12.SUBCAT_ID;
> {code}
> it doesn't work. The root cause is that the flag is set in [one QB instance|https://github.com/apache/hive/blob/883d5dfe25929ba5dcac635e752adf5561d28402/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java#L1836], but during the plan generation, genPlan is recursively called, moreover, this.qb doesn't refer to the main/root QB all the time, as [setQB|https://github.com/apache/hive/blob/883d5dfe25929ba5dcac635e752adf5561d28402/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java#L12435] is called at every step, so when iterating and reaching the file sink generation plan, the QB instance is used is not the same as the one on which the isInsertOverwriteDirectory flag was set.
> When this issue is present, an EXPLAIN EXTENDED can reveal the strange strategy of a query when result cache is enabled, here is a snippet:
> {code}
> | Reducer 5 |
> ...
> | File Output Operator |
> | bucketingVersion: 2 |
> | compressed: false |
> | GlobalTableId: 0 |
> | directory: file:/efs/tmp/hive/_resultscache_/results-c7e3efee-ce91-41f5-9ce3-f95ec4d23f66/0de7c837-2868-4441-90ac-40c35a2f1d8d/.hive-staging_hive_2024-11-09_16-27-23_402_2565121622084463581-8/-ext-10000 |
> | NumFilesPerFileSink: 1 |
> | Statistics: Num rows: 3892258870 Data size: 46678382057 Basic stats: COMPLETE Column stats: NONE |
> | Stats Publishing Key Prefix: file:/efs/tmp/hive/_resultscache_/results-c7e3efee-ce91-41f5-9ce3-f95ec4d23f66/0de7c837-2868-4441-90ac-40c35a2f1d8d/.hive-staging_hive_2024-11-09_16-27-23_402_2565121622084463581-8/-ext-10000/ |
> | table: |
> | input format: org.apache.hadoop.mapred.TextInputFormat |
> | output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
> | properties: |
> | bucketing_version -1 |
> | columns _col0,_col1,_col2,_col3,_col4,_col5 |
> | columns.types int:string:int:string:double:double |
> | serialization.format 1 |
> | serialization.lib org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
> | serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
> | TotalFiles: 1 |
> | GatherStats: false |
> | MultiFileSpray: false |
> | |
> | Stage: Stage-2 |
> | Dependency Collection |
> | |
> | Stage: Stage-0 |
> | Move Operator |
> | files: |
> | hdfs directory: true |
> | source: file:/efs/tmp/hive/_resultscache_/results-c7e3efee-ce91-41f5-9ce3-f95ec4d23f66/0de7c837-2868-4441-90ac-40c35a2f1d8d/.hive-staging_hive_2024-11-09_16-27-23_402_2565121622084463581-8/-ext-10000 |
> | destination: file:/efs/tmp/hive/_resultscache_/results-c7e3efee-ce91-41f5-9ce3-f95ec4d23f66/0de7c837-2868-4441-90ac-40c35a2f1d8d |
> | |
> +----------------------------------------------------+
> {code}
> as in compile-time the IOWD query was considered cacheable, the FileSinkOperator ended up writing to the query result cache, then the MoveTask moved to the final path, which is also in the query cache, instead of the expected folder '/tmp' in this example
> so not only does the output hit the wrong location, but all the IOWD data ends up placed and moved on the query result cache's filesystem (which is revealed as a serious performance regression on cloud storage when hitting some throughput limits).
> whatever the solution is, it needs to bypass the query result cache for IOWD, so the the original behavior is restored and files go to the right place
> {code}
> | Stage: Stage-0 |
> | Move Operator |
> | files: |
> | hdfs directory: true |
> | source: s3a://somebucket/user/hive/.hive-staging_hive_2024-11-09_16-28-27_281_5911898841946485672-9/-ext-10000 |
> | destination: s3a://somebucket/tmp|
> | |
> +----------------------------------------------------+
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)