USE e700719f;

-- configure bucketing
SET hive.enforce.bucketing = TRUE;
SET hive.exec.dynamic.partition = TRUE;
SET hive.exec.dynamic.partition.mode = nonstrict;
--SET hive.exec.max.dynamic.partitions = 1980;
--SET hive.exec.max.dynamic.partitions.pernode = 110;
SET hive.exec.max.dynamic.partitions = 2000;
SET hive.exec.max.dynamic.partitions.pernode = 400;
-- 18 nodes total
-- users.reputation has 1519 different values

-- query to optimize
--SELECT p.id FROM posts p, comments c, users u, votes v
--WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
--AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
--AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id)

-- find join column diff values
SELECT "COUNT(DISTINCT x)";
SELECT COUNT(DISTINCT postid) FROM comments;
SELECT COUNT(DISTINCT userid) FROM comments;
SELECT COUNT(DISTINCT owneruserid) FROM posts;
SELECT COUNT(DISTINCT relatedpostid) FROM postlinks;

-- users_partitioned
--CREATE TABLE IF NOT EXISTS users_partitioned (id INT, aboutme STRING, accountid INT, creationdate TIMESTAMP, displayname STRING, downvotes INT, lastaccessdate TIMESTAMP, location STRING, profileimageurl STRING, upvotes INT, views INT, websiteurl STRING) PARTITIONED BY (reputation INT);
--INSERT OVERWRITE TABLE users_partitioned PARTITION (reputation) SELECT * FROM users;

SELECT "\n\n\n";

SELECT "\n##### QUERY 1 UNOPTIMIZED #####\n";
-- query unoptimized
--EXPLAIN SELECT p.id FROM posts p, comments c, users u, votes v
--WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
--AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
--AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id);

SELECT "\n##### QUERY 1 OPTIMIZED #####\n";
-- query optimized
--EXPLAIN SELECT p.id FROM posts p, comments c, users_partitioned u, votes v
--WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
--AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
--AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id);
