个人中心
文章发布
退出
作者热门文章
- html - 出于某种原因，IE8 对我的 Sass 文件中继承的 html5 CSS 不友好？
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景？
29
4
有什么方法可以提高HQL性能吗？我有一个这样的查询:
with
tmp_a as (
SELECT * FROM `zhihu.answer` where ym in (select distinct(ym) from zhihu.answer_increment)
),
-- the result of subquery select distinct(ym) from zhihu.answer_increment is 201806
-- the rows of tmp_a are 1,790,000
tmp1 as (
select a.* from tmp_a a
LEFT JOIN `zhihu.answer_increment` b
ON a.answer_id = b.answer_id
AND a.insert_time = b.insert_time
WHERE b.answer_id IS NULL)
insert overwrite table zhihu.answer partition(ym)
select * from tmp1
UNION ALL
SELECT *
FROM `zhihu.answer_increment` t
表格信息:
Rows of `zhihu.answer` are about 10 millions. and was partitioned by `ym`, that means year and month,
and was clustered by `answer_id` into 256 buckets.
Rows of `zhihu.answer_increment` are about 100 thousands.
the data structure of these two tables are the same.
上述查询的目的是将新创建的数据(answer_increment表)与历史数据(answer表)合并，并保存到Hive中。
问题是整个过程运行了大约2个小时，上面的HQL有没有优化？
我尝试为表创建索引，但不确定这是否有帮助:
CREATE INDEX insert_time_index ON TABLE zhihu.answer (insert_time) AS 'COMPACT'
WITH DEFERRED REBUILD;
我的 Hive 设置:
set hive.auto.convert.join=true;
我的 HQL 解释:
Explain
STAGE DEPENDENCIES:
Stage-5 is a root stage
Stage-11 depends on stages: Stage-5 , consists of Stage-13, Stage-1
Stage-13 has a backup stage: Stage-1
Stage-10 depends on stages: Stage-13
Stage-9 depends on stages: Stage-1, Stage-10 , consists of Stage-12, Stage-2
Stage-12 has a backup stage: Stage-2
Stage-8 depends on stages: Stage-12
Stage-3 depends on stages: Stage-2, Stage-8
Stage-0 depends on stages: Stage-3
Stage-4 depends on stages: Stage-0
Stage-2
Stage-1
STAGE PLANS:
Stage: Stage-5
Map Reduce
Map Operator Tree:
TableScan
alias: zhihu_answer_increment
filterExpr: ym is not null (type: boolean)
Statistics: Num rows: 101549 Data size: 21426839 Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: ym (type: string)
outputColumnNames: ym
Statistics: Num rows: 101549 Data size: 21426839 Basic stats: COMPLETE Column stats: COMPLETE
Group By Operator
keys: ym (type: string)
mode: hash
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
key expressions: _col0 (type: string)
sort order: +
Map-reduce partition columns: _col0 (type: string)
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
Execution mode: vectorized
Reduce Operator Tree:
Group By Operator
keys: KEY._col0 (type: string)
mode: mergepartial
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
Group By Operator
keys: _col0 (type: string)
mode: hash
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
File Output Operator
compressed: false
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
Stage: Stage-11
Conditional Operator
Stage: Stage-13
Map Reduce Local Work
Alias -> Map Local Tables:
_u1-subquery1:tmp1:a:$INTNAME
Fetch Operator
limit: -1
Alias -> Map Local Operator Tree:
_u1-subquery1:tmp1:a:$INTNAME
TableScan
HashTable Sink Operator
keys:
0 ym (type: string)
1 _col0 (type: string)
Stage: Stage-10
Map Reduce
Map Operator Tree:
TableScan
alias: zhihu.zhihu_answer
filterExpr: ym is not null (type: boolean)
Statistics: Num rows: 76466394 Data size: 16134409134 Basic stats: COMPLETE Column stats: PARTIAL
Map Join Operator
condition map:
Left Semi Join 0 to 1
keys:
0 ym (type: string)
1 _col0 (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27
Statistics: Num rows: 4024547 Data size: 7356871916 Basic stats: COMPLETE Column stats: PARTIAL
File Output Operator
compressed: false
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
Local Work:
Map Reduce Local Work
Stage: Stage-9
Conditional Operator
Stage: Stage-12
Map Reduce Local Work
Alias -> Map Local Tables:
_u1-subquery1:tmp1:b
Fetch Operator
limit: -1
Alias -> Map Local Operator Tree:
_u1-subquery1:tmp1:b
TableScan
alias: b
Statistics: Num rows: 101549 Data size: 2741823 Basic stats: COMPLETE Column stats: NONE
HashTable Sink Operator
keys:
0 _col3 (type: string), _col15 (type: string)
1 answer_id (type: string), insert_time (type: string)
Stage: Stage-8
Map Reduce
Map Operator Tree:
TableScan
Map Join Operator
condition map:
Left Outer Join0 to 1
keys:
0 _col3 (type: string), _col15 (type: string)
1 answer_id (type: string), insert_time (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27, _col31
Statistics: Num rows: 4427001 Data size: 8092559283 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: _col31 is null (type: boolean)
Statistics: Num rows: 2213500 Data size: 4046278727 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: _col0 (type: boolean), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: smallint), _col12 (type: boolean), _col13 (type: int), _col14 (type: string), _col15 (type: string), _col16 (type: boolean), _col17 (type: boolean), _col18 (type: boolean), _col19 (type: boolean), _col20 (type: string), _col21 (type: string), _col22 (type: string), _col23 (type: string), _col24 (type: int), _col25 (type: int), _col26 (type: int), _col27 (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27
Statistics: Num rows: 2213500 Data size: 4046278727 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
Local Work:
Map Reduce Local Work
Stage: Stage-3
Map Reduce
Map Operator Tree:
TableScan
Union
Statistics: Num rows: 2315049 Data size: 4067705566 Basic stats: COMPLETE Column stats: PARTIAL
Reduce Output Operator
sort order:
Map-reduce partition columns: _col3 (type: string)
Statistics: Num rows: 2315049 Data size: 4067705566 Basic stats: COMPLETE Column stats: PARTIAL
value expressions: _col0 (type: boolean), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: smallint), _col12 (type: boolean), _col13 (type: int), _col14 (type: string), _col15 (type: string), _col16 (type: boolean), _col17 (type: boolean), _col18 (type: boolean), _col19 (type: boolean), _col20 (type: string), _col21 (type: string), _col22 (type: string), _col23 (type: string), _col24 (type: int), _col25 (type: int), _col26 (type: int), _col27 (type: string)
TableScan
alias: t
Statistics: Num rows: 101549 Data size: 21426839 Basic stats: COMPLETE Column stats: PARTIAL
Select Operator
expressions: admin_closed_comment (type: boolean), answer_content (type: string), answer_created (type: string), answer_id (type: string), answer_updated (type: string), author_headline (type: string), author_id (type: string), author_name (type: string), author_type (type: string), author_url_token (type: string), avatar_url (type: string), badge_num (type: smallint), can_comment (type: boolean), comment_count (type: int), gender (type: string), insert_time (type: string), is_advertiser (type: boolean), is_collapsed (type: boolean), is_copyable (type: boolean), is_org (type: boolean), question_created (type: string), question_id (type: string), question_title (type: string), question_type (type: string), reward_member_count (type: int), reward_total_money (type: int), voteup_count (type: int), ym (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27
Statistics: Num rows: 101549 Data size: 21426839 Basic stats: COMPLETE Column stats: PARTIAL
Union
Statistics: Num rows: 2315049 Data size: 4067705566 Basic stats: COMPLETE Column stats: PARTIAL
Reduce Output Operator
sort order:
Map-reduce partition columns: _col3 (type: string)
Statistics: Num rows: 2315049 Data size: 4067705566 Basic stats: COMPLETE Column stats: PARTIAL
value expressions: _col0 (type: boolean), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: smallint), _col12 (type: boolean), _col13 (type: int), _col14 (type: string), _col15 (type: string), _col16 (type: boolean), _col17 (type: boolean), _col18 (type: boolean), _col19 (type: boolean), _col20 (type: string), _col21 (type: string), _col22 (type: string), _col23 (type: string), _col24 (type: int), _col25 (type: int), _col26 (type: int), _col27 (type: string)
Reduce Operator Tree:
Select Operator
expressions: VALUE._col0 (type: boolean), VALUE._col1 (type: string), VALUE._col2 (type: string), VALUE._col3 (type: string), VALUE._col4 (type: string), VALUE._col5 (type: string), VALUE._col6 (type: string), VALUE._col7 (type: string), VALUE._col8 (type: string), VALUE._col9 (type: string), VALUE._col10 (type: string), VALUE._col11 (type: smallint), VALUE._col12 (type: boolean), VALUE._col13 (type: int), VALUE._col14 (type: string), VALUE._col15 (type: string), VALUE._col16 (type: boolean), VALUE._col17 (type: boolean), VALUE._col18 (type: boolean), VALUE._col19 (type: boolean), VALUE._col20 (type: string), VALUE._col21 (type: string), VALUE._col22 (type: string), VALUE._col23 (type: string), VALUE._col24 (type: int), VALUE._col25 (type: int), VALUE._col26 (type: int), VALUE._col27 (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27
Statistics: Num rows: 2315049 Data size: 425969016 Basic stats: COMPLETE Column stats: PARTIAL
File Output Operator
compressed: false
Statistics: Num rows: 2315049 Data size: 425969016 Basic stats: COMPLETE Column stats: PARTIAL
table:
input format: org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat
output format: org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat
serde: org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
name: zhihu.zhihu_answer
Stage: Stage-0
Move Operator
tables:
partition:
ym
replace: true
table:
input format: org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat
output format: org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat
serde: org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
name: zhihu.zhihu_answer
Stage: Stage-4
Stats-Aggr Operator
Stage: Stage-2
Map Reduce
Map Operator Tree:
TableScan
Reduce Output Operator
key expressions: _col3 (type: string), _col15 (type: string)
sort order: ++
Map-reduce partition columns: _col3 (type: string), _col15 (type: string)
Statistics: Num rows: 4024547 Data size: 7356871916 Basic stats: COMPLETE Column stats: PARTIAL
value expressions: _col0 (type: boolean), _col1 (type: string), _col2 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: smallint), _col12 (type: boolean), _col13 (type: int), _col14 (type: string), _col16 (type: boolean), _col17 (type: boolean), _col18 (type: boolean), _col19 (type: boolean), _col20 (type: string), _col21 (type: string), _col22 (type: string), _col23 (type: string), _col24 (type: int), _col25 (type: int), _col26 (type: int), _col27 (type: string)
TableScan
alias: b
Statistics: Num rows: 101549 Data size: 2741823 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: answer_id (type: string), insert_time (type: string)
sort order: ++
Map-reduce partition columns: answer_id (type: string), insert_time (type: string)
Statistics: Num rows: 101549 Data size: 2741823 Basic stats: COMPLETE Column stats: NONE
Reduce Operator Tree:
Join Operator
condition map:
Left Outer Join0 to 1
keys:
0 _col3 (type: string), _col15 (type: string)
1 answer_id (type: string), insert_time (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27, _col31
Statistics: Num rows: 4427001 Data size: 8092559283 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: _col31 is null (type: boolean)
Statistics: Num rows: 2213500 Data size: 4046278727 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: _col0 (type: boolean), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: smallint), _col12 (type: boolean), _col13 (type: int), _col14 (type: string), _col15 (type: string), _col16 (type: boolean), _col17 (type: boolean), _col18 (type: boolean), _col19 (type: boolean), _col20 (type: string), _col21 (type: string), _col22 (type: string), _col23 (type: string), _col24 (type: int), _col25 (type: int), _col26 (type: int), _col27 (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27
Statistics: Num rows: 2213500 Data size: 4046278727 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: zhihu.zhihu_answer
filterExpr: ym is not null (type: boolean)
Statistics: Num rows: 76466394 Data size: 16134409134 Basic stats: COMPLETE Column stats: PARTIAL
Reduce Output Operator
key expressions: ym (type: string)
sort order: +
Map-reduce partition columns: ym (type: string)
Statistics: Num rows: 76466394 Data size: 16134409134 Basic stats: COMPLETE Column stats: PARTIAL
value expressions: admin_closed_comment (type: boolean), answer_content (type: string), answer_created (type: string), answer_id (type: string), answer_updated (type: string), author_headline (type: string), author_id (type: string), author_name (type: string), author_type (type: string), author_url_token (type: string), avatar_url (type: string), badge_num (type: smallint), can_comment (type: boolean), comment_count (type: int), gender (type: string), insert_time (type: string), is_advertiser (type: boolean), is_collapsed (type: boolean), is_copyable (type: boolean), is_org (type: boolean), question_created (type: string), question_id (type: string), question_title (type: string), question_type (type: string), reward_member_count (type: int), reward_total_money (type: int), voteup_count (type: int)
TableScan
Reduce Output Operator
key expressions: _col0 (type: string)
sort order: +
Map-reduce partition columns: _col0 (type: string)
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
Reduce Operator Tree:
Join Operator
condition map:
Left Semi Join 0 to 1
keys:
0 ym (type: string)
1 _col0 (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27
Statistics: Num rows: 4024547 Data size: 7356871916 Basic stats: COMPLETE Column stats: PARTIAL
File Output Operator
compressed: false
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
@leftjoin 的 HQL 解释:
Explain
STAGE DEPENDENCIES:
Stage-3 is a root stage
Stage-6 depends on stages: Stage-3 , consists of Stage-7, Stage-1
Stage-7 has a backup stage: Stage-1
Stage-5 depends on stages: Stage-7
Stage-2 depends on stages: Stage-1, Stage-5
Stage-1
Stage-0 depends on stages: Stage-2
STAGE PLANS:
Stage: Stage-3
Map Reduce
Map Operator Tree:
TableScan
alias: zhihu_answer_increment
filterExpr: ym is not null (type: boolean)
Statistics: Num rows: 101549 Data size: 21426839 Basic stats: COMPLETE Column stats: COMPLETE
Select Operator
expressions: ym (type: string)
outputColumnNames: ym
Statistics: Num rows: 101549 Data size: 21426839 Basic stats: COMPLETE Column stats: COMPLETE
Group By Operator
keys: ym (type: string)
mode: hash
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator
key expressions: _col0 (type: string)
sort order: +
Map-reduce partition columns: _col0 (type: string)
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
Execution mode: vectorized
Reduce Operator Tree:
Group By Operator
keys: KEY._col0 (type: string)
mode: mergepartial
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
Group By Operator
keys: _col0 (type: string)
mode: hash
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE Column stats: COMPLETE
File Output Operator
compressed: false
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
Stage: Stage-6
Conditional Operator
Stage: Stage-7
Map Reduce Local Work
Alias -> Map Local Tables:
s:s-subquery1:$INTNAME
Fetch Operator
limit: -1
Alias -> Map Local Operator Tree:
s:s-subquery1:$INTNAME
TableScan
HashTable Sink Operator
keys:
0 ym (type: string)
1 _col0 (type: string)
Stage: Stage-5
Map Reduce
Map Operator Tree:
TableScan
alias: t
filterExpr: ym is not null (type: boolean)
Statistics: Num rows: 76466394 Data size: 16134409134 Basic stats: COMPLETE Column stats: PARTIAL
Map Join Operator
condition map:
Left Semi Join 0 to 1
keys:
0 ym (type: string)
1 _col0 (type: string)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27
Statistics: Num rows: 4024547 Data size: 7340773728 Basic stats: COMPLETE Column stats: PARTIAL
Select Operator
expressions: _col0 (type: boolean), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: smallint), _col12 (type: boolean), _col14 (type: string), _col15 (type: string), _col16 (type: boolean), _col17 (type: boolean), _col18 (type: boolean), _col19 (type: boolean), _col20 (type: string), _col21 (type: string), _col22 (type: string), _col23 (type: string), _col24 (type: int), _col25 (type: int), _col26 (type: int), _col27 (type: string), 0 (type: int)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27
Statistics: Num rows: 4024547 Data size: 756614836 Basic stats: COMPLETE Column stats: PARTIAL
File Output Operator
compressed: false
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
Local Work:
Map Reduce Local Work
Stage: Stage-2
Map Reduce
Map Operator Tree:
TableScan
Union
最佳答案
set hive.auto.convert.join=true; --this enables map-join
set hive.mapjoin.smalltable.filesize=25000000;
set hive.execution.engine=tez;
insert overwrite table zhihu.answer partition(ym)
select col1, col2 ... coln, ym, --list all columns
from
(
select col1, col2 ... coln, ym, --list all columns
row_number() over(partition by ym, answer_id, insert_time order by new_flag desc) rn
from
(
select col1, col2 ... coln, ym, --list all columns
0 as new_flag
from zhihu.answer t
where t.ym in (select distinct ym from zhihu.answer_increment)
UNION ALL
select col1, col2 ... coln, ym, --list all columns
1 as new_flag
from zhihu.answer_increment t
)s
)s
where s.rn=1;
Indexes were removed in Hive 3.0 ，更多详细信息请参见此 Jira:HIVE-18448
另请参阅此答案:https://stackoverflow.com/a/37744071/2700344
还可以调整并行性以获得更好的性能:https://stackoverflow.com/a/48487306/2700344
更新:我研究了@DennisLi 提供的计划。一些观察:
大表与整个增量表的连接作为映射连接执行。在这种情况下，FULL 连接方法可能比 UNION ALL+row_number 更好。
与分区列表的连接已被优化器转换为 LEFT SEMI JOIN(也可用作映射连接)，过滤后，总共 70M 中有 4K 行。 我建议分别计算 ym 分区的最小和最大增量，并使用 WHERE ym>= ${min_increment_ym} 和 ym<=${max_increment_ym} 将它们作为参数传递在这种情况下，分区修剪将在没有连接的情况下有效地过滤数据。但只有适用于增量数据集(增量包含单个小范围分区，我们可以有效地使用最小值和最大值)时才可以应用它，实现这一点将为您带来最大的好处
未启用中间压缩。启用压缩可能会给您带来一些好处，但值得尝试
推荐方法:
set hive.auto.convert.join=true; --this enables map-join
set hive.mapjoin.smalltable.filesize=25000000;
--check compression influence separately.
--it may give some improvement depending on your data entropy
set hive.exec.compress.intermediate=true;
set mapred.output.compress=true;
set hive.exec.compress.output=true;
insert overwrite table zhihu.answer partition(ym)
select --select increment if exists, old if not exists
case when i.answer_id is not null then i.col1 else t.col1 end as col1,
... --for all columns
case when i.answer_id is not null then i.coln else t.coln end as coln,
--partition is the last one
case when i.answer_id is not null then i.ym else t.ym end as ym
from zhihu.answer t
full join zhihu.answer_increment i
on t.answer_id = i.answer_id
and t.insert_time = i.insert_time
and t.ym=i.ym --check this condition
where t.ym in (select distinct ym from zhihu.answer_increment) --try to implement min and max parameters instead of this if possible (see 2)
--alternatively if you do not want to employ shell, check if you can
--remove the WHERE condition providing ym in the join condition,
--this will allow to get rid of the second join in the plan,
--though partition pruning will work with parameters better
;
最终该计划将是最优的。
您可能仍然需要根据执行日志调整映射器和化简器的并行性，请参阅之前的答案建议
关于performance - Hive 如何提高我的查询性能？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/58408704/
29
4
0
在这段令人惊叹的视频 ( https://www.youtube.com/watch?v=udix3GZouik ) 中，Alex Blom 谈到了 Ember 在移动世界中的“黑客攻击”。 在 22
我们希望通过我们的应用收集使用情况统计信息。因此，我们希望在服务器端的某个地方跟踪用户操作。 就性能而言，哪个选项更合适: 在 App Engine 请求日志中跟踪用户操作。即为每个用户操作写入一个日
在针对对象集合的 LINQ 查询的幕后究竟发生了什么？它只是语法糖还是发生了其他事情使其更有效的查询？ 最佳答案 您是指查询表达式，还是查询在幕后的作用？ 查询表达式首先扩展为“普通”C#。例如: v
我正在构建一个简单的照片库应用程序，它在列表框中显示图像。 xaml 是:
对于基于 Web 的企业应用程序，使用“静态 Hashmap 存储对象” 和 apache java 缓存系统有何优缺点？哪一个最有利于性能并减少堆内存问题 例如: Map store=Applica
我想知道在性能方面存储类变量的最佳方式是什么。我的意思是，由于 Children() 函数，存储一个 div id 比查找所有其他类名更好。还是把类名写在变量里比较好？ 例如这样: var $inne
我已经阅读了所有这些关于 cassandra 有多快的文章，例如单行读取可能需要大约 5 毫秒。 到目前为止，我不太关心我的网站速度，但是随着网站变得越来越大，一些页面开始需要相当多的查询，例如一个页
最近，我在缓存到内存缓存之前的查询一直需要很长时间才能处理!在这个例子中，它花费了 10 秒。在这种情况下，我要做的就是获得 10 个最近的点击。 我感觉它加载了所有 125,592 行然后只返回 1
我找了几篇文章(包括SA中的一些问题)，试图找到基本操作的成本。 但是，我尝试制作自己的小程序，以便自己进行测试。在尝试测试加法和减法时，我遇到了一些问题，我用简单的代码向您展示了这一点
这个问题在这里已经有了答案: Will Java app slow down by presence of -Xdebug or only when stepping through code? (
我记得很久以前读过 with() 对 JavaScript 有一些严重的性能影响，因为它可能对范围堆栈进行非确定性更改。我很难找到最近对此的讨论。这仍然是真的吗？ 最佳答案 与其说 with 对性能有
我们有一个数据仓库，其中包含非规范化表，行数从 50 万行到 6 多万行不等。我正在开发一个报告解决方案，因此出于性能原因我们正在使用数据库分页。我们的报告有搜索条件，并且我们已经创建了必要的索引，但
我有一条有效的 SQL 语句，但需要很长时间才能处理 我有一个 a_log 表和一个 people 表。我需要在 people 表中找到给定人员的每个 ID 的最后一个事件和关联的用户。 SELECT
很难说出这里问的是什么。这个问题是含糊的、模糊的、不完整的、过于宽泛的或修辞性的，无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开它，visit the help center 。 已关
通常当我建立一个站点时，我将所有的 CSS 放在一个文件中，并且一次性定义与一组元素相关的所有属性。像这样: #myElement { color: #fff; background-
两者之间是否存在任何性能差异: p { margin:0px; padding:0px; } 并省略最后的分号: p { margin:0px; padding:0px } 提前致谢!
我的应用程序 (PHP) 需要执行大量高精度数学运算(甚至可能出现一共100个数字) 通过这个论坛的最后几篇帖子，我发现我必须使用任何高精度库，如 BC Math 或 GMP，因为 float 类型不
我一直在使用 javamail 从 IMAP 服务器(目前是 GMail)检索邮件。 Javamail 非常快速地从服务器检索特定文件夹中的消息列表(仅 id)，但是当我实际获取消息(仅包含甚至不包含
我非常渴望开发我的第一个 Ruby 应用程序，因为我的公司终于在内部批准了它的使用。 在我读到的关于 Ruby v1.8 之前的所有内容中，从来没有任何关于性能的正面评价，但我没有发现关于 1.9 版
我是 Redis 的新手，我有一个包含数百万个成员(member) ID、电子邮件和用户名的数据集，并且正在考虑将它们存储在例如列表结构中。我认为 list 和 sorted set 可能最适合我的情
我是一名优秀的程序员,十分优秀！