mqEuE8Hv

1SET hive.exec.dynamic.partition.mode=nonstrict;
2SET hive.exec.max.dynamic.partitions=2000;
3SET hive.exec.max.dynamic.partitions.pernode=500;
4USE warchulsszym;
5
6DROP TABLE IF EXISTS blackfriday_temp PURGE;
7
8CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS blackfriday_temp(
9  	User_ID SMALLINT,
10	Product_ID STRING,
11	Gender STRING, 
12	Age STRING,
13	Occupation SMALLINT,
14	City_Category STRING,
15	Stay_In_Current_City_Years STRING,
16	Marital_Status SMALLINT,
17	Product_Category_1 SMALLINT,
18	Product_Category_2 SMALLINT,
19	Product_Category_3 SMALLINT,
20	Purchase SMALLINT)
21  ROW FORMAT DELIMITED
22  FIELDS TERMINATED BY ','
23  STORED AS TEXTFILE
24  LOCATION '/user/warchulsszym'
25  TBLPROPERTIES ('skip.header.line.count'='1');
26  
27DROP TABLE IF EXISTS blackfriday PURGE;
28
29CREATE TABLE blackfriday (
30  	user_Id STRING,
31	Purchase SMALLINT)
32  PARTITIONED BY(City_category STRING)
33  CLUSTERED BY(user_id) INTO 3 BUCKETS
34;
35
36INSERT OVERWRITE TABLE blackfriday PARTITION(city_category)
37  SELECT s.City_category, s.purchase, s.user_id
38  	from blackfriday_temp s;