4nLxUT78

1--table without partition
2
3drop table if exists ufodata;
4create table ufodata ( sighted string, reported string, city string, shape string, duration string, description string )
5row format delimited
6fields terminated by 't'
7Location '/mapreduce/hive/ufo';
8
9--load my data in ufodata
10
11load data local inpath '/home/training/downloads/ufo_awesome.tsv' into table ufodata;
12
13--create partition table
14drop table if exists partufo;
15create table partufo ( sighted string, reported string, city string, shape string, duration string, description string )
16partitioned by ( year string )
17clustered by (year) into 6 buckets
18row format delimited
19fields terminated by '/t';
20
21--by default dynamic partition is not set
22set hive.exec.dynamic.partition=true;
23set hive.exec.dynamic.partition.mode=nonstrict;
24--by default bucketing is false
25set hive.enforcebucketing=true;
26
27--loading mydata
28insert overwrite table partufo
29partition (year)
30select sighted, reported, city, shape, min, description, SUBSTR(TRIM(sighted), 1,4) from ufodata;
31	
32CREATE TABLE bckt_movies
33(mov_id BIGINT , mov_name STRING ,prod_studio STRING, col_world DOUBLE , col_us_canada DOUBLE , col_uk DOUBLE , col_aus DOUBLE)
34PARTITIONED BY (rel_year STRING)
35CLUSTERED BY(mov_id) INTO 6 BUCKETS;