WiGpJtrG

1hdfs dfs -mkdir /tmp/data
2hdfs dfs -chmod -R 777 /tmp/data
3hdfs dfs -mv /tmp/data.csv /tmp/data
4
5!connect jdbc:hive2://aervits-hdp1:10000 "" ""
6
7CREATE EXTERNAL TABLE IF NOT EXISTS traffic_csv (
8  `DT` STRING,
9  `TM` STRING,
10  `BOROUGH` STRING,
11  `ZIP CODE` STRING,
12  `LATITUDE` STRING,
13  `LONGITUDE` STRING,
14  `LOCATION` STRING,
15  `ON STREET NAME` STRING,
16  `CROSS STREET NAME` STRING,
17  `OFF STREET NAME` STRING,
18  `NUMBER OF PERSONS INJURED` STRING,
19  `NUMBER OF PERSONS KILLED` STRING,
20  `NUMBER OF PEDESTRIANS INJURED` STRING,
21  `NUMBER OF PEDESTRIANS KILLED` STRING,
22  `NUMBER OF CYCLIST INJURED` STRING,
23  `NUMBER OF CYCLIST KILLED` STRING,
24  `NUMBER OF MOTORIST INJURED` STRING,
25  `NUMBER OF MOTORIST KILLED` STRING,
26  `CONTRIBUTING FACTOR VEHICLE 1` STRING,
27  `CONTRIBUTING FACTOR VEHICLE 2` STRING,
28  `CONTRIBUTING FACTOR VEHICLE 3` STRING,
29  `CONTRIBUTING FACTOR VEHICLE 4` STRING,
30  `CONTRIBUTING FACTOR VEHICLE 5` STRING,
31  `UNIQUE KEY` STRING,
32  `VEHICLE TYPE CODE 1` STRING,
33  `VEHICLE TYPE CODE 2` STRING,
34  `VEHICLE TYPE CODE 3` STRING,
35  `VEHICLE TYPE CODE 4` STRING, 
36  `VEHICLE TYPE CODE 5` STRING)
37    ROW FORMAT DELIMITED
38    FIELDS TERMINATED BY ','
39    STORED AS TEXTFILE
40  LOCATION '/tmp/data';
41
42  CREATE TABLE IF NOT EXISTS traffic (
43    `DT` STRING,
44    `TM` STRING,
45    `ZIP CODE` STRING,
46    `LATITUDE` STRING,
47    `LONGITUDE` STRING,
48    `LOCATION` STRING,
49    `ON STREET NAME` STRING,
50    `CROSS STREET NAME` STRING,
51    `OFF STREET NAME` STRING,
52    `NUMBER OF PERSONS INJURED` STRING,
53    `NUMBER OF PERSONS KILLED` STRING,
54    `NUMBER OF PEDESTRIANS INJURED` STRING,
55    `NUMBER OF PEDESTRIANS KILLED` STRING,
56    `NUMBER OF CYCLIST INJURED` STRING,
57    `NUMBER OF CYCLIST KILLED` STRING,
58    `NUMBER OF MOTORIST INJURED` STRING,
59    `NUMBER OF MOTORIST KILLED` STRING,
60    `CONTRIBUTING FACTOR VEHICLE 1` STRING,
61    `CONTRIBUTING FACTOR VEHICLE 2` STRING,
62    `CONTRIBUTING FACTOR VEHICLE 3` STRING,
63    `CONTRIBUTING FACTOR VEHICLE 4` STRING,
64    `CONTRIBUTING FACTOR VEHICLE 5` STRING,
65    `UNIQUE KEY` STRING,
66    `VEHICLE TYPE CODE 1` STRING,
67    `VEHICLE TYPE CODE 2` STRING,
68    `VEHICLE TYPE CODE 3` STRING,
69    `VEHICLE TYPE CODE 4` STRING, 
70    `VEHICLE TYPE CODE 5` STRING)
71    PARTITIONED BY (BOROUGH STRING)
72      ROW FORMAT DELIMITED
73      FIELDS TERMINATED BY ','
74      STORED AS ORC;
75
76SET hive.exec.dynamic.partition.mode=nonstrict;
77INSERT OVERWRITE TABLE TRAFFIC PARTITION (BOROUGH) 
78  SELECT `DT`, 
79         `TM`, 
80         `ZIP CODE`, 
81         `LATITUDE`, 
82         `LONGITUDE`,
83         `LOCATION`,
84         `ON STREET NAME`,
85         `CROSS STREET NAME`,
86         `OFF STREET NAME`,
87         `NUMBER OF PERSONS INJURED`,
88         `NUMBER OF PERSONS KILLED`,
89         `NUMBER OF PEDESTRIANS INJURED`,
90         `NUMBER OF PEDESTRIANS KILLED`,
91         `NUMBER OF CYCLIST INJURED`,
92         `NUMBER OF CYCLIST KILLED`,
93         `NUMBER OF MOTORIST INJURED`,
94         `NUMBER OF MOTORIST KILLED`,
95         `CONTRIBUTING FACTOR VEHICLE 1`,
96         `CONTRIBUTING FACTOR VEHICLE 2`,
97         `CONTRIBUTING FACTOR VEHICLE 3`,
98         `CONTRIBUTING FACTOR VEHICLE 4`,
99         `CONTRIBUTING FACTOR VEHICLE 5`,
100         `UNIQUE KEY`,
101         `VEHICLE TYPE CODE 1`,
102         `VEHICLE TYPE CODE 2`,
103         `VEHICLE TYPE CODE 3`,
104         `VEHICLE TYPE CODE 4`, 
105         `VEHICLE TYPE CODE 5`,
106         `BOROUGH`
107  FROM TRAFFIC_CSV;