]> git.somenet.org - pub/jan/adbs.git/blob - ex2/hive/query.sql
GITOLITE.txt
[pub/jan/adbs.git] / 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);