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 SET hive.exec.max.dynamic.partitions = 2000;
  12 SET hive.exec.max.dynamic.partitions.pernode = 400;
  13 -- 18 nodes total
  14 -- users.reputation has 1519 different values
  15
  16 -- raw tables
  17 CREATE TABLE IF NOT EXISTS raw_badges (id BIGINT, class INT, `date` STRING, name VARCHAR(100), tagbased BOOLEAN, userid BIGINT) row format delimited fields terminated by ',' tblproperties ("skip.header.line.count"="1");
  18 CREATE TABLE IF NOT EXISTS raw_comments (id BIGINT, creationdate STRING, postid BIGINT, score INT, text VARCHAR(40000), userdisplayname VARCHAR(100), userid BIGINT) row format delimited fields terminated by ',' tblproperties ("skip.header.line.count"="1");
  19 CREATE TABLE IF NOT EXISTS raw_posts (id BIGINT,acceptedanswerid BIGINT,answercount INT,body VARCHAR(1000),closeddate STRING,commentcount INT,communityowneddate STRING,creationdate STRING,favoritecount INT,lastactivitydate STRING,lasteditdate STRING,lasteditordisplayname VARCHAR(100),lasteditoruserid BIGINT,ownerdisplayname VARCHAR(100),owneruserid BIGINT,parentid BIGINT,posttypeid TINYINT,score INT,tags VARCHAR(200),title VARCHAR(200),viewcount INT) row format delimited fields terminated by ',' tblproperties ("skip.header.line.count"="1");
  20 CREATE TABLE IF NOT EXISTS raw_postlinks (id BIGINT,creationdate STRING,linktypeid BIGINT,postid BIGINT,relatedpostid BIGINT) row format delimited fields terminated by ',' tblproperties ("skip.header.line.count"="1");
  21 CREATE TABLE IF NOT EXISTS raw_users (id BIGINT,aboutme VARCHAR(3000),accountid BIGINT,creationdate INT,displayname VARCHAR(100),downvotes INT,lastaccessdate STRING,location VARCHAR(100),profileimageurl VARCHAR(500),reputation INT,upvotes INT,views INT,websiteurl VARCHAR(500)) row format delimited fields terminated by ',' tblproperties ("skip.header.line.count"="1");
  22 CREATE TABLE IF NOT EXISTS raw_votes(id BIGINT,bountyamount INT ,creationdate STRING,postid BIGINT,userid BIGINT,votetypeid BIGINT) row format delimited fields terminated by ',' tblproperties ("skip.header.line.count"="1");
  23
  24 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/badges.csv' OVERWRITE INTO TABLE raw_badges;
  25 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/comments.csv' OVERWRITE INTO TABLE raw_comments;
  26 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/postlinks.csv' OVERWRITE INTO TABLE raw_postlinks;
  27 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/posts.csv' OVERWRITE INTO TABLE raw_posts;
  28 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/users.csv' OVERWRITE INTO TABLE raw_users;
  29 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/votes.csv' OVERWRITE INTO TABLE raw_votes;
  30
  31 -- real tables
  32 CREATE TABLE IF NOT EXISTS badges (id BIGINT, class INT, `date` TIMESTAMP, name VARCHAR(100), tagbased BOOLEAN, userid BIGINT) row format delimited fields terminated by ',';
  33 CREATE TABLE IF NOT EXISTS comments (id BIGINT, creationdate TIMESTAMP, postid BIGINT, score INT, text VARCHAR(40000), userdisplayname VARCHAR(100), userid BIGINT) row format delimited fields terminated by ',';
  34 CREATE TABLE IF NOT EXISTS postlinks (id BIGINT,creationdate TIMESTAMP,linktypeid BIGINT,postid BIGINT,relatedpostid BIGINT) row format delimited fields terminated by ',';
  35 CREATE TABLE IF NOT EXISTS posts (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),owneruserid BIGINT,parentid BIGINT,posttypeid TINYINT,score INT,tags VARCHAR(200),title VARCHAR(200),viewcount INT) row format delimited fields terminated by ',';
  36 CREATE TABLE IF NOT EXISTS users (id BIGINT,aboutme VARCHAR(3000),accountid BIGINT,creationdate INT,displayname VARCHAR(100),downvotes INT,lastaccessdate TIMESTAMP,location VARCHAR(100),profileimageurl VARCHAR(500),reputation INT,upvotes INT,views INT,websiteurl VARCHAR(500)) row format delimited fields terminated by ',';
  37 CREATE TABLE IF NOT EXISTS votes(id BIGINT,bountyamount INT ,creationdate TIMESTAMP,postid BIGINT,userid BIGINT,votetypeid BIGINT) row format delimited fields terminated by ',';
  38
  39 INSERT OVERWRITE TABLE badges
  40         SELECT id,class,date_format(regexp_replace(`date`, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),name,tagbased,userid FROM raw_badges;
  41 INSERT OVERWRITE TABLE comments
  42         SELECT id,date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),postid,score,text,userdisplayname,userid FROM raw_comments;
  43 INSERT OVERWRITE TABLE postlinks
  44         SELECT id,date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),linktypeid,postid,relatedpostid FROM raw_postlinks;
  45 INSERT OVERWRITE TABLE posts
  46         SELECT id,acceptedanswerid,answercount,body,
  47                 date_format(regexp_replace(closeddate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),commentcount,
  48                 date_format(regexp_replace(communityowneddate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  49                 date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),favoritecount,
  50                 date_format(regexp_replace(lastactivitydate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  51                 date_format(regexp_replace(lasteditdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  52                 lasteditordisplayname,lasteditoruserid,ownerdisplayname,owneruserid,parentid,posttypeid,score,tags,title,viewcount FROM raw_posts;
  53 INSERT OVERWRITE TABLE users
  54         SELECT id,aboutme,accountid,date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  55                 displayname,downvotes,date_format(regexp_replace(lastaccessdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  56                 location,profileimageurl,reputation,upvotes,views,websiteurl FROM raw_users;
  57 INSERT OVERWRITE TABLE votes
  58         SELECT id,bountyamount,date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),postid,userid,votetypeid FROM raw_votes;
  59
  60 -- users_partitioned
  61 --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);
  62 --INSERT OVERWRITE TABLE users_partitioned PARTITION (reputation) SELECT * FROM users;
  63
  64 SELECT "\n\n\n";
  65
  66 SELECT "\n##### QUERY 1 UNOPTIMIZED #####\n";
  67 -- query unoptimized
  68 --EXPLAIN SELECT p.id FROM posts p, comments c, users u, votes v
  69 --WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
  70 --AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
  71 --AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id);
  72
  73 SELECT "\n##### QUERY 1 OPTIMIZED #####\n";
  74 -- query optimized
  75 --EXPLAIN SELECT p.id FROM posts p, comments c, users_partitioned u, votes v
  76 --WHERE c.postid=p.id AND c.userid=p.owneruserid AND u.id=p.owneruserid
  77 --AND u.reputation > 100 AND v.postid = p.id AND v.userid = p.owneruserid
  78 --AND NOT EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid = p.id);