ex2/hive/create.sql

   1 DROP DATABASE IF EXISTS e700719f CASCADE;
   2 CREATE DATABASE e700719f;
   3 USE e700719f;
   4
   5 -- raw tables
   6 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");
   7 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");
   8 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");
   9 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");
  10 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");
  11 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");
  12
  13 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/badges.csv' OVERWRITE INTO TABLE raw_badges;
  14 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/comments.csv' OVERWRITE INTO TABLE raw_comments;
  15 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/postlinks.csv' OVERWRITE INTO TABLE raw_postlinks;
  16 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/posts.csv' OVERWRITE INTO TABLE raw_posts;
  17 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/users.csv' OVERWRITE INTO TABLE raw_users;
  18 LOAD DATA LOCAL INPATH '/home/adbs/2019S/shared/hive/votes.csv' OVERWRITE INTO TABLE raw_votes;
  19
  20 -- real tables
  21 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 ',';
  22 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 ',';
  23 CREATE TABLE IF NOT EXISTS postlinks (id BIGINT,creationdate TIMESTAMP,linktypeid BIGINT,postid BIGINT,relatedpostid BIGINT) row format delimited fields terminated by ',';
  24 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 ',';
  25 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 ',';
  26 CREATE TABLE IF NOT EXISTS votes(id BIGINT,bountyamount INT ,creationdate TIMESTAMP,postid BIGINT,userid BIGINT,votetypeid BIGINT) row format delimited fields terminated by ',';
  27
  28 INSERT OVERWRITE TABLE badges
  29         SELECT id,class,date_format(regexp_replace(`date`, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),name,tagbased,userid FROM raw_badges;
  30 INSERT OVERWRITE TABLE comments
  31         SELECT id,date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),postid,score,text,userdisplayname,userid FROM raw_comments;
  32 INSERT OVERWRITE TABLE postlinks
  33         SELECT id,date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),linktypeid,postid,relatedpostid FROM raw_postlinks;
  34 INSERT OVERWRITE TABLE posts
  35         SELECT id,acceptedanswerid,answercount,body,
  36                 date_format(regexp_replace(closeddate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),commentcount,
  37                 date_format(regexp_replace(communityowneddate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  38                 date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),favoritecount,
  39                 date_format(regexp_replace(lastactivitydate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  40                 date_format(regexp_replace(lasteditdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  41                 lasteditordisplayname,lasteditoruserid,ownerdisplayname,owneruserid,parentid,posttypeid,score,tags,title,viewcount FROM raw_posts;
  42 INSERT OVERWRITE TABLE users
  43         SELECT id,aboutme,accountid,date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  44                 displayname,downvotes,date_format(regexp_replace(lastaccessdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),
  45                 location,profileimageurl,reputation,upvotes,views,websiteurl FROM raw_users;
  46 INSERT OVERWRITE TABLE votes
  47         SELECT id,bountyamount,date_format(regexp_replace(creationdate, 'T', ' '),'yyyy-MM-dd HH:mm:ss.SSS'),postid,userid,votetypeid FROM raw_votes;