CCwxvJrg

1create database if not exists extractions comment 'Raw imports';
2create database if not exists transformations comment 'Transformed Data' location '/user/hive/warehouse/transformations';
3use extractions;
4create table if not exists petextracts(
5    data string
6);
7show tables;
8load data inpath '/user/cloudera/case1.csv' into table petextracts;
9select data from petextracts;
10create table if not exists pets(
11    name string,
12    sequence string,
13     houseno string,
14     location string,
15     city string,
16     regid int,
17     breed string,
18     age int
19     )
20     row format delimited
21     fields terminated by '|'
22     stored as textfile;
23select * from extractions.petextracts;
24select 
25    split(data,'\\|')[0] as name,
26    split(data,'\\|')[1] as sequenceno,
27    split(split(data,'\\|')[2],',')[0] as houseno,
28    split(split(data,'\\|')[2],',')[1] as location,
29    split(split(data,'\\|')[2],',')[2] as city,
30    split(data,'\\|')[3] as regid,
31    split(data,'\\|')[4] as breed,
32    split(data,'\\|')[5] as age
33    from extractions.petextracts;
34
35insert overwrite table pets
36select 
37    split(data,'\\|')[0] as name,
38    split(data,'\\|')[1] as sequenceno,
39    split(split(data,'\\|')[2],',')[0] as houseno,
40    split(split(data,'\\|')[2],',')[1] as location,
41    split(split(data,'\\|')[2],',')[2] as city,
42    split(data,'\\|')[3] as regid,
43    split(data,'\\|')[4] as breed,
44    split(data,'\\|')[5] as age
45    from extractions.petextracts;
46	
47create table if not exists categories(
48    category_id int,
49    department_id int,
50    category_description string
51)
52row format delimited
53     fields terminated by '|'
54     stored as textfile;
55
56load data inpath '/user/cloudera/categories_pipe' into table categories;
57
58describe function extended split;
59show functions;
60describe function split;
61
62create external table if not exists categories_external(
63    category_id int,
64    department_id int,
65    category_description string
66)
67row format delimited
68     fields terminated by '|'
69     location '/user/cloudera/categories_pipe';
70
71load data inpath '/user/cloudera/categories_pipe_1m' into table categories_external;
72
73create external table if not exists categories_6m(
74    category_id int,
75    department_id int,
76    category_description string
77)
78row format delimited
79     fields terminated by '|'
80     location '/user/cloudera/categories_pipe_6m';