FmyaZS59

1SET hive.exec.dynamic.partition.mode=nonstrict;
2SET hive.exec.max.dynamic.partitions=2000;
3SET hive.exec.max.dynamic.partitions.pernode=500;
4USE wozniakmate;
5
6DROP TABLE IF EXISTS temp_Athlete PURGE;
7
8CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS temp_Athlete(
9  ID				INT,
10  Name				VARCHAR(100),
11  Sex				VARCHAR(100),
12  Age				TINYINT,
13  Height			SMALLINT,
14  Weight			SMALLINT,
15  Team				VARCHAR(100),
16  NOC				VARCHAR(100),
17  Games				VARCHAR(100),
18  Year				SMALLINT,
19  Season			VARCHAR(100),
20  City				VARCHAR(100),
21  Sport				VARCHAR(100),
22  Event				VARCHAR(100),
23  Medal				VARCHAR(100))
24  ROW FORMAT DELIMITED
25  FIELDS TERMINATED BY ','
26  STORED AS TEXTFILE
27  LOCATION '/user/wozniakmate/athlete'
28  TBLPROPERTIES ('skip.header.line.count'='1');
29  
30DROP TABLE IF EXISTS Athlete PURGE;
31
32CREATE TABLE Athlete (
33  Games VARCHAR(100),
34  Medal VARCHAR(100))
35  PARTITIONED BY(Team VARCHAR(100))
36  STORED AS ORC;
37  insert overwrite table Athlete PARTITION(Team)
38select  t.Games, t.Medal, t.Team from temp_Athlete t where t.Medal <>'NA'