set hive.optimize.ppd=true;
set hive.ppd.remove.duplicatefilters=true;
set hive.tez.dynamic.partition.pruning=true;
set hive.optimize.metadataonly=false;
set hive.optimize.index.filter=true;
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask = true;
set hive.auto.convert.join.noconditionaltask.size = 10000000;

create table dim_shops (id int, label string) row format delimited fields terminated by ',' stored as textfile;
load data local inpath '../../data/files/dim_shops.txt' into table dim_shops;

create table agg_01 (amount decimal) partitioned by (dim_shops_id int) row format delimited fields terminated by ',' stored as textfile;
alter table agg_01 add partition (dim_shops_id = 1);
alter table agg_01 add partition (dim_shops_id = 2);
alter table agg_01 add partition (dim_shops_id = 3);

load data local inpath '../../data/files/agg_01-p1.txt' into table agg_01 partition (dim_shops_id=1);
load data local inpath '../../data/files/agg_01-p2.txt' into table agg_01 partition (dim_shops_id=2);
load data local inpath '../../data/files/agg_01-p3.txt' into table agg_01 partition (dim_shops_id=3);

select * from dim_shops;
select * from agg_01;

EXPLAIN SELECT d1.label, count(*), sum(agg.amount)
FROM agg_01 agg,
dim_shops d1
WHERE agg.dim_shops_id = d1.id
and
d1.label in ('foo', 'bar')
GROUP BY d1.label
ORDER BY d1.label;

SELECT d1.label, count(*), sum(agg.amount)
FROM agg_01 agg,
dim_shops d1
WHERE agg.dim_shops_id = d1.id
and
d1.label in ('foo', 'bar')
GROUP BY d1.label
ORDER BY d1.label;