Hive ppd tests

#!/bin/sh

# to be pasted into shell running in terminal (doesn't seem to work when executed as script :-/ )

echo >/tmp/cmd '
-- create a partitioned table
DROP TABLE IF EXISTS ppd_test;
CREATE TABLE ppd_test (col1 array<int>, col2 int)
  PARTITIONED BY (part_col bigint);

-- fill partitions with some random data
INSERT OVERWRITE TABLE ppd_test PARTITION (part_col=1) SELECT array(1,2), count(*) FROM ppd_test;
INSERT OVERWRITE TABLE ppd_test PARTITION (part_col=2) SELECT array(2,4,6), count(*) FROM ppd_test;
'
hive < /tmp/cmd &> /tmp/log

echo >/tmp/cmd '
set hive.optimize.ppd=true;
-- multi-group-by query on one partitions with ppd optimization
EXPLAIN EXTENDED
FROM ppd_test
LATERAL VIEW explode(col1) tmp AS exp_col1
INSERT OVERWRITE DIRECTORY '"'/test/1'"'
    SELECT exp_col1
    WHERE (part_col=2)
INSERT OVERWRITE DIRECTORY '"'/test/2'"'
    SELECT exp_col1
    WHERE (part_col=2)
;'
hive < /tmp/cmd &> /tmp/part_ppd.txt

echo >/tmp/cmd '
set hive.optimize.ppd=false;
-- multi-group-by query on one partitions without ppd optimization
EXPLAIN EXTENDED
FROM ppd_test
LATERAL VIEW explode(col1) tmp AS exp_col1
INSERT OVERWRITE DIRECTORY '"'/test/1'"'
    SELECT exp_col1
    WHERE (part_col=2)
INSERT OVERWRITE DIRECTORY '"'/test/2'"'
    SELECT exp_col1
    WHERE (part_col=2)
;'
hive < /tmp/cmd &> /tmp/part_noppd.txt

echo >/tmp/cmd '
-- create a non-partitioned table
DROP TABLE IF EXISTS ppd_test;
CREATE TABLE ppd_test (col1 array<int>, col2 int);

-- fill partitions with some random data
INSERT OVERWRITE TABLE ppd_test SELECT array(1,2), count(*) FROM ppd_test;
'
hive < /tmp/cmd &>> /tmp/log

echo >/tmp/cmd '
set hive.optimize.ppd=true;
-- multi-group-by query on regular column with ppd optimization
EXPLAIN EXTENDED
FROM ppd_test
LATERAL VIEW explode(col1) tmp AS exp_col1
INSERT OVERWRITE DIRECTORY '"'/test/1'"'
    SELECT exp_col1
    WHERE (col2=1)
INSERT OVERWRITE DIRECTORY '"'/test/2'"'
    SELECT exp_col1
    WHERE (col2=1)
;'
hive < /tmp/cmd &> /tmp/nopart_ppd.txt

echo >/tmp/cmd '
set hive.optimize.ppd=false;
-- multi-group-by query on regular column without ppd optimization
EXPLAIN EXTENDED
FROM ppd_test
LATERAL VIEW explode(col1) tmp AS exp_col1
INSERT OVERWRITE DIRECTORY '"'/test/1'"'
    SELECT exp_col1
    WHERE (col2=1)
INSERT OVERWRITE DIRECTORY '"'/test/2'"'
    SELECT exp_col1
    WHERE (col2=1)
;'
hive < /tmp/cmd &> /tmp/nopart_noppd.txt


echo >/tmp/cmd '
set hive.optimize.ppd=true;
-- multi-group-by query on regular column with ppd optimization and without lateral view
EXPLAIN EXTENDED
FROM ppd_test
INSERT OVERWRITE DIRECTORY '"'/test/1'"'
    SELECT col1
    WHERE (col2=1)
INSERT OVERWRITE DIRECTORY '"'/test/2'"'
    SELECT col1
    WHERE (col2=1)
;'
hive < /tmp/cmd &> /tmp/nopart_ppd_nolv.txt

echo >/tmp/cmd '
set hive.optimize.ppd=false;
-- multi-group-by query on regular column without ppd optimization and without lateral view
EXPLAIN EXTENDED
FROM ppd_test
INSERT OVERWRITE DIRECTORY '"'/test/1'"'
    SELECT col1
    WHERE (col2=1)
INSERT OVERWRITE DIRECTORY '"'/test/2'"'
    SELECT col1
    WHERE (col2=1)
;'
hive < /tmp/cmd &> /tmp/nopart_noppd_nolv.txt