ex2/hive/hive.sql

   1 DROP DATABASE IF EXISTS e700719f CASCADE;
   2 CREATE DATABASE e700719f;
   3 USE e700719f;
   4
   5 -- configure bucketing
   6 SET hive.enforce.bucketing = TRUE;
   7 SET hive.exec.dynamic.partition = TRUE;
   8 SET hive.exec.dynamic.partition.mode = nonstrict;
   9 SET hive.exec.max.dynamic.partitions = 1980;
  10 SET hive.exec.max.dynamic.partitions.pernode = 110;
  11 -- 18 nodes total
  12 -- users.reputation has 1519 different values
  13
  14 -- badges
  15 CREATE TABLE IF NOT EXISTS badges (id INT, class INT, `date` DATE, name STRING, tagbased BOOLEAN, userid INT);
  16 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/badges.csv' OVERWRITE INTO TABLE badges;
  17
  18 -- comments
  19 CREATE TABLE IF NOT EXISTS comments (id INT, creationdate DATE, postid INT, score INT, text STRING, userdisplayname STRING, userid INT);
  20 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/comments.csv' OVERWRITE INTO TABLE comments;
  21
  22 -- postlinks
  23 CREATE TABLE IF NOT EXISTS postlinks (id INT, creationdate DATE, linktypeid INT, postid INT, relatedpostid INT);
  24 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/postlinks.csv' OVERWRITE INTO TABLE postlinks;
  25
  26 -- posts
  27 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);
  28 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/posts.csv' OVERWRITE INTO TABLE posts;
  29
  30 -- users
  31 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);
  32 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/users.csv' OVERWRITE INTO TABLE users;
  33
  34 -- votes
  35 CREATE TABLE IF NOT EXISTS votes (id INT, bountyamount INT, creationdate DATE, postid INT, userid INT, votetypeid INT);
  36 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/votes.csv' OVERWRITE INTO TABLE votes;
  37
  38 -- users_partitioned
  39 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);
  40 INSERT OVERWRITE TABLE users_partitioned PARTITION (reputation) SELECT * FROM users;
  41
  42 SELECT "\n\n\n";
  43
  44 SELECT "\n##### QUERY 1 UNOPTIMIZED #####\n";
  45 -- query unoptimized
  46 EXPLAIN SELECT p.id FROM posts p, comments c, users u, votes v
  47 WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
  48 AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
  49 AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id);
  50
  51 SELECT "\n##### QUERY 1 OPTIMIZED #####\n";
  52 -- query optimized
  53 EXPLAIN SELECT p.id FROM posts p, comments c, users_partitioned u, votes v
  54 WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
  55 AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
  56 AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id);
  57