ex2/hive/query.sql

   1 USE e700719f;
   2
   3 -- configure bucketing
   4 SET hive.enforce.bucketing = TRUE;
   5 SET hive.exec.dynamic.partition = TRUE;
   6 SET hive.exec.dynamic.partition.mode = nonstrict;
   7 --SET hive.exec.max.dynamic.partitions = 1980;
   8 --SET hive.exec.max.dynamic.partitions.pernode = 110;
   9 SET hive.exec.max.dynamic.partitions = 1000;
  10 SET hive.exec.max.dynamic.partitions.pernode = 100;
  11 -- 18 nodes total
  12 -- users.reputation has 1519 different values
  13
  14 -- query to optimize
  15 --SELECT p.id FROM posts p, comments c, users u, votes v
  16 --WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
  17 --AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
  18 --AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id)
  19
  20 -- find join column diff values
  21 --SELECT "COUNT(DISTINCT x)";
  22 --SELECT COUNT(DISTINCT postid) FROM comments; -- 117818 (total: 312277)
  23 --SELECT COUNT(DISTINCT userid) FROM comments; -- 40417 (total: 312277)
  24 --SELECT COUNT(DISTINCT owneruserid) FROM posts; -- 78561 (total: 244189)
  25 --SELECT COUNT(DISTINCT relatedpostid) FROM postlinks; -- 12461 (total: 25373)
  26 --SELECT COUNT(DISTINCT postid) FROM votes; -- 274081 (total: 1040881)
  27 --SELECT COUNT(DISTINCT userid) FROM votes; -- 21704 (total: 1040881)
  28
  29 DROP TABLE IF EXISTS comments_partitioned;
  30 DROP TABLE IF EXISTS postlinks_partitioned;
  31 DROP TABLE IF EXISTS posts_partitioned;
  32 DROP TABLE IF EXISTS votes_partitioned;
  33
  34 -- partitioned tables
  35 SET hive.exec.max.dynamic.partitions = 40000;
  36 SET hive.exec.max.dynamic.partitions.pernode = 40000;
  37 CREATE TABLE IF NOT EXISTS comments_partitioned (id BIGINT, creationdate TIMESTAMP, postid BIGINT, score INT, text VARCHAR(40000), userdisplayname VARCHAR(100)) PARTITIONED BY (userid BIGINT);
  38 INSERT OVERWRITE TABLE comments_partitioned PARTITION (userid) SELECT id,creationdate,postid,score,text,userdisplayname,userid FROM comments;
  39
  40 --SET hive.exec.max.dynamic.partitions = 200;
  41 --SET hive.exec.max.dynamic.partitions.pernode = 200;
  42 CREATE TABLE IF NOT EXISTS postlinks_partitioned (id BIGINT,creationdate TIMESTAMP,linktypeid BIGINT,postid BIGINT) PARTITIONED BY (relatedpostid BIGINT);
  43 INSERT OVERWRITE TABLE postlinks_partitioned PARTITION (relatedpostid) SELECT id,creationdate,linktype,postid,relatedpostid FROM postlinks;
  44
  45 --SET hive.exec.max.dynamic.partitions = 800;
  46 --SET hive.exec.max.dynamic.partitions.pernode = 800;
  47 CREATE TABLE IF NOT EXISTS posts_partitioned (id BIGINT,acceptedanswerid BIGINT,answercount INT,body VARCHAR(1000),closeddate TIMESTAMP,commentcount INT,communityowneddate TIMESTAMP,creationdate TIMESTAMP,favoritecount INT,lastactivitydate TIMESTAMP,lasteditdate TIMESTAMP,lasteditordisplayname VARCHAR(100),lasteditoruserid BIGINT,ownerdisplayname VARCHAR(100),parentid BIGINT,posttypeid TINYINT,score INT,tags VARCHAR(200),title VARCHAR(200),viewcount INT) PARTITIONED BY (owneruserid BIGINT);
  48 INSERT OVERWRITE TABLE posts_partitioned PARTITION (owneruserid) SELECT id,acceptedanswerid,answercount,body,closeddate,commentcount,communityowneddate,creationdate,favoritecount,lastactivitydate,lasteditdate,lasteditordisplayname,lasteditoruserid,ownerdisplayname,parentid,posttypeid,score,tags,title,viewcount,owneruserid FROM posts;
  49
  50 --SET hive.exec.max.dynamic.partitions = 300;
  51 --SET hive.exec.max.dynamic.partitions.pernode = 300;
  52 CREATE TABLE IF NOT EXISTS votes_partitioned(id BIGINT,bountyamount INT,creationdate TIMESTAMP,postid BIGINT,votetypeid BIGINT) PARTITIONED BY (userid BIGINT);
  53 INSERT OVERWRITE TABLE votes_partitioned PARTITION (userid) SELECT id,bountyamount,creationdate,postid,votetypeid,userid FROM votes;
  54
  55 --SELECT "\n\n\n";
  56
  57 SELECT "\n##### QUERY 1 UNOPTIMIZED #####\n";
  58 -- query unoptimized
  59 EXPLAIN SELECT p.id FROM posts p, comments c, users u, votes v
  60 WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
  61 AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
  62 AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id);
  63
  64 SELECT "\n##### QUERY 1 OPTIMIZED #####\n";
  65 -- query optimized
  66 EXPLAIN SELECT p.id FROM posts_partitioned p, comments_partitioned c, users u, votes_partitioned v
  67 WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
  68 AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
  69 AND NOT EXISTS (SELECT 1 FROM postlinks_partitioned l WHERE l.relatedpostid = p.id);