DROP DATABASE IF EXISTS e700719f CASCADE;
CREATE DATABASE e700719f;
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;
-- 18 nodes total
-- users.reputation has 1519 different values

-- badges
CREATE TABLE IF NOT EXISTS badges (id INT, class INT, `date` DATE, name STRING, tagbased BOOLEAN, userid INT);
LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/badges.csv' OVERWRITE INTO TABLE badges;

-- comments
CREATE TABLE IF NOT EXISTS comments (id INT, creationdate DATE, postid INT, score INT, text STRING, userdisplayname STRING, userid INT);
LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/comments.csv' OVERWRITE INTO TABLE comments;

-- postlinks
CREATE TABLE IF NOT EXISTS postlinks (id INT, creationdate DATE, linktypeid INT, postid INT, relatedpostid INT);
LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/postlinks.csv' OVERWRITE INTO TABLE postlinks;

-- posts
CREATE TABLE IF NOT EXISTS posts (id INT, acceptedanswerid INT, answercount INT, body STRING, closeddate DATE, commentcount INT, communityowneddate DATE, creationdate DATE, favoritecount INT, lastactivitydate DATE, lasteditdate DATE, lasteditordisplayname STRING, lasteditoruserid INT, ownerdisplayname STRING, owneruserid INT, parentid INT, posttypeid INT, score INT, tags STRING, title STRING, viewcount INT);
LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/posts.csv' OVERWRITE INTO TABLE posts;

-- users
CREATE TABLE IF NOT EXISTS users (id INT, aboutme STRING, accountid INT, creationdate DATE, displayname STRING, downvotes INT, lastaccessdate DATE, location STRING, profileimageurl STRING, reputation INT, upvotes INT, views INT, websiteurl STRING);
LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/users.csv' OVERWRITE INTO TABLE users;

-- votes
CREATE TABLE IF NOT EXISTS votes (id INT, bountyamount INT, creationdate DATE, postid INT, userid INT, votetypeid INT);
LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/votes.csv' OVERWRITE INTO TABLE votes;

-- users_partitioned
CREATE TABLE IF NOT EXISTS users_partitioned (id INT, aboutme STRING, accountid INT, creationdate DATE, displayname STRING, downvotes INT, lastaccessdate DATE, 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);

