How can we do partition pruninng  in impala join query , not able to figure out and where clause is also not giving the desired results , need some help on this . here is the query : select a11.apn apn, cast(a12.tariff_plan as int) tariff_plan, a11.subscriber_id subscriber_id, a11.partition_date partition_date, a13.name name, a12.tariff_plan tariff_plan0, a12.charging_node charging_node, concat(substring(cast(a11.partition_date as string),5,2),'/',substring(cast(a11.partition_date as string),7,2),'/', substring(cast(a11.partition_date as string),1,4)) CustCol_23, sum(case when (a11.volume between 10*1024 and 50*1024) then 1 else 0 end) WJXBFS1, sum(case when (a11.volume between 5*1024 and 10*1024) then 1 else 0 end) WJXBFS2, sum(case when a11.volume < 5*1024 then 1 else 0 end) WJXBFS3, sum(case when a11.volume>50*1024 then 1 else 0 end) WJXBFS4 from acc_volume_daily a11 join subscriber_history_daily a12 on (a11.partition_date = a12.partition_date and a11.subscriber_id = a12.subscriber_id and concat(substring(cast(a11.partition_date as string),5,2),'/',substring(cast(a11.partition_date as string),7,2),'/', substring(cast(a11.partition_date as string),1,4)) = concat(substring(cast(a12.partition_date as string),5,2),'/',substring(cast(a12.partition_date as string),7,2),'/', substring(cast(a12.partition_date as string),1,4))) join l_data_plans a13 on (cast(a12.tariff_plan as int) = a13.id) where a11.partition_date = a12.partition_date = 20140411 group by a11.apn, cast(a12.tariff_plan as int), a11.subscriber_id, a11.partition_date, a13.name, a12.tariff_plan, a12.charging_node, concat(substring(cast(a11.partition_date as string),5,2),'/',substring(cast(a11.partition_date as string),7,2),'/', substring(cast(a11.partition_date as string),1,4)); -------------------------------------------------------------------------------------------------------------------------------------------------------- here is the explain query result.  Explain String ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PLAN FRAGMENT 0 PARTITION: UNPARTITIONED 10:EXCHANGE tuple ids: 3 PLAN FRAGMENT 1 PARTITION: HASH_PARTITIONED: a11.apn, CAST(a12.tariff_plan AS INT), a11.subscriber_id, a11.partition_date, a13.name, a12.tariff_plan, a12.charging_node, concat(substring(CAST(a11.partition_date AS STRING), 5, 2), '/', substring(CAST(a11.partition_date AS STRING), 7, 2), '/', substring(CAST(a11.partition_date AS STRING), 1, 4)) STREAM DATA SINK EXCHANGE ID: 10 UNPARTITIONED 9:AGGREGATE | output: SUM(SUM(CASE WHEN (a11.volume BETWEEN 10 * 1024 AND 50 * 1024) THEN 1 ELSE 0 END)), SUM(SUM(CASE WHEN (a11.volume BETWEEN 5 * 1024 AND 10 * 1024) THEN 1 ELSE 0 END)), SUM(SUM(CASE WHEN a11.volume < 5 * 1024 THEN 1 ELSE 0 END)), SUM(SUM(CASE WHEN a11.volume > 50 * 1024 THEN 1 ELSE 0 END)) | group by: a11.apn, CAST(a12.tariff_plan AS INT), a11.subscriber_id, a11.partition_date, a13.name, a12.tariff_plan, a12.charging_node, concat(substring(CAST(a11.partition_date AS STRING), 5, 2), '/', substring(CAST(a11.partition_date AS STRING), 7, 2), '/', substring(CAST(a11.partition_date AS STRING), 1, 4)) | tuple ids: 3 | 8:EXCHANGE tuple ids: 3 PLAN FRAGMENT 2 PARTITION: RANDOM STREAM DATA SINK EXCHANGE ID: 8 HASH_PARTITIONED: a11.apn, CAST(a12.tariff_plan AS INT), a11.subscriber_id, a11.partition_date, a13.name, a12.tariff_plan, a12.charging_node, concat(substring(CAST(a11.partition_date AS STRING), 5, 2), '/', substring(CAST(a11.partition_date AS STRING), 7, 2), '/', substring(CAST(a11.partition_date AS STRING), 1, 4)) 5:AGGREGATE | output: SUM(CASE WHEN (a11.volume BETWEEN 10 * 1024 AND 50 * 1024) THEN 1 ELSE 0 END), SUM(CASE WHEN (a11.volume BETWEEN 5 * 1024 AND 10 * 1024) THEN 1 ELSE 0 END), SUM(CASE WHEN a11.volume < 5 * 1024 THEN 1 ELSE 0 END), SUM(CASE WHEN a11.volume > 50 * 1024 THEN 1 ELSE 0 END) | group by: a11.apn, CAST(a12.tariff_plan AS INT), a11.subscriber_id, a11.partition_date, a13.name, a12.tariff_plan, a12.charging_node, concat(substring(CAST(a11.partition_date AS STRING), 5, 2), '/', substring(CAST(a11.partition_date AS STRING), 7, 2), '/', substring(CAST(a11.partition_date AS STRING), 1, 4)) | tuple ids: 3 | 4:HASH JOIN | join op: INNER JOIN (BROADCAST) | hash predicates: | CAST(a12.tariff_plan AS INT) = a13.id | tuple ids: 0 1 2 | |----7:EXCHANGE | tuple ids: 2 | 2:HASH JOIN | join op: INNER JOIN (BROADCAST) | hash predicates: | a11.partition_date = a12.partition_date | a11.subscriber_id = a12.subscriber_id | other predicates: concat(substring(CAST(a11.partition_date AS STRING), 5, 2), '/', substring(CAST(a11.partition_date AS STRING), 7, 2), '/', substring(CAST(a11.partition_date AS STRING), 1, 4)) = concat(substring(CAST(a12.partition_date AS STRING), 5, 2), '/', substring(CAST(a12.partition_date AS STRING), 7, 2), '/', substring(CAST(a12.partition_date AS STRING), 1, 4)) | tuple ids: 0 1 | |----6:EXCHANGE | tuple ids: 1 | 0:SCAN HDFS table=reporting_mini.acc_volume_daily #partitions=0 size=0B tuple ids: 0 PLAN FRAGMENT 3 PARTITION: RANDOM STREAM DATA SINK EXCHANGE ID: 7 UNPARTITIONED 3:SCAN HDFS table=reporting_mini.l_data_plans #partitions=1 size=21.96KB compact tuple ids: 2 PLAN FRAGMENT 4 PARTITION: RANDOM STREAM DATA SINK EXCHANGE ID: 6 UNPARTITIONED 1:SCAN HDFS table=reporting_mini.subscriber_history_daily #partitions=191 size=2.33GB compact tuple ids: 1 ... View more