配置单元解释计划不显示分区 [英] hive explain plan not showing partition

问题描述

我有一张包含251M记录的表格，大小为2.5GB。
我在两个列上创建了一个分区，我在谓词中做了一个条件。
但解释计划没有显示它正在读取分区，即使我已经分区。从分区列中选择，我插入到另一个表。

I have a table which contains 251M records and size is 2.5gb. I created a partition on two columns which I am doing condition in predicate. But the explain plan is not showing it is reading partition even though I have partitioned. With selecting from partition column, I am inserting to another table.

是否有特定的顺序，我不得不提到谓词中的条件？
我应该如何改善表现。

Is there a particular order I have to mention the condition in predicate ? How should I improve performance.

explain
  SELECT
              '123'                                                                         AS run_session_id
            , tbl1.transaction_id
            , tbl1.src_transaction_id
            , tbl1.transaction_created_epoch_time
            , tbl1.currency
            , tbl1.event_type
            , tbl1.event_sub_type
            , tbl1.estimated_total_cost
            , tbl1.actual_total_cost
            , tbl1.tfc_export_created_epoch_time
            , tbl1.authorizer
            , tbl1.acquirer
            , tbl1.processor
            , tbl1.company_code
            , tbl1.country_of_account
            , tbl1.merchant_id
            , tbl1.client_id
            , tbl1.ft_id
            , tbl1.transaction_created_date
            , tbl1.event_pst_time
            , tbl1.extract_id_seq
            , tbl1.src_type
            , ROW_NUMBER() OVER(PARTITION by tbl1.transaction_id ORDER BY tbl1.event_pst_time DESC)   AS seq_num       -- while writing back to the pfit events table, write each event so that event_pst_time populates in right way

          FROM db.xx_events tbl1                                --<hiveFinalDB>--                           -- DB variables wont work, so need to change the DB accrodingly for testing and PROD deployment
          WHERE id_seq     >= 215
             AND id_seq   <= 275
            AND vent        in('SPT','PNR','PNE','PNER','ACT','NTE'); 

现在我该如何提高性能。分区列是（id_seq，vent）

Now how do I improve performance. The partition column is (id_seq,vent)

推荐答案

解释依赖关系选择... $ b

Demo

explain dependency select ...

create table mytable (i int) 
partitioned by (dt date)
;

---

---

alter table mytable add 
    partition (dt=date '2017-06-18')
    partition (dt=date '2017-06-19')
    partition (dt=date '2017-06-20')
    partition (dt=date '2017-06-21')
    partition (dt=date '2017-06-22')
;

---

---

explain dependency 
select  *
from    mytable
where   dt >= date '2017-06-20'
;

---

---

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                            Explain                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"input_tables":[{"tablename":"local_db@mytable","tabletype":"MANAGED_TABLE"}],"input_partitions":[{"partitionName":"local_db@mytable@dt=2017-06-20"},{"partitionName":"local_db@mytable@dt=2017-06-21"},{"partitionName":"local_db@mytable@dt=2017-06-22"}]} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

input_partitions ：{partitionName：local_db @ mytable @ dt = 2017-06-21}，{[分区名称'：local_db @ mytable @ dt = 2017-06-20}， partitionName：local_db @mytable @ dt = 2017-06-22}]}

"input_partitions":[
{"partitionName":"local_db@mytable@dt=2017-06-20"},{"partitionName":"local_db@mytable@dt=2017-06-21"},{"partitionName":"local_db@mytable@dt=2017-06-22"}]}

这篇关于配置单元解释计划不显示分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！