G3rDqeSw

1# HIVE
2
3It let you manage hdfs in an SQL manner. It sits on top of MapReduce, and TEZ.
4It tranlates SQL queries to MR or Tez on the cluster.
5
6From ur point of view you just write SQL queries and the rest are let to HIVE to figure out the rest.
7
8__Characteristics:__ It is interactive, scalable, much easier than MR in Java, optimized, extensible.
9__Drawbacks__: Slow with OLTP (online data). It has its limits in complexity of queries, so Pig or Spark are more appropriate. 
10No transactions, no records, it is like database but it just Mapping and Reducing in a more efficient way.
11
12Break complicated parts of queries into __VIEWS__
13
14Example and Syntax:
15```
16CREATE VIEW given_name_by_u_now AS
17SELECT column1, count(column1) as give_a_column_name
18FROM existing_table
19GROUP BY column1
20ORDER BY give_a_column_name DESC;
21
22SELECT n.column2, give_a_column_name
23FROM given_name_by_u_now t JOIN names n ON t.column1 = n.column1;
24```
25With views we split queries into prettier structure that is dealable and clean.
26
27__Trick__
28```
29CREATE VIEW IF NOT EXISTSgiven_name_by_u_now AS
30```
31so no to give error if view already exists.
32
33
34### How Hive works
35* __schema on read__: Hive takes unstracture data and applies a schema to it  as being read.
36* Hive maintains a metastore that imparts a structure u define on the unstructure data that in sotred on HDFS.
37
38Syntax of making a table from unstractured data: 
39```
40CREATE TABLE table_name (
41  column1 INT,
42  column2 INT,
43  column3 INT)
44ROW FORMAT DELIMTED
45FILED TERMINATED BY '\t'
46STORED AS TEXTFILE;
47
48LOAD DATA LOCAL INPATH '${env:HOME}/folder/datafile'
49OVERWRITE INTO TABLE table_name;
50```
51
52Or you can cheat and upload data using AMBARI.
53
54
55### DATA LOACATIONS
56* LOAD DATA: moves data from the distributed filesystem into Hive
57* LOAD DATA LOCAL: copies data from the local filesystem into Hive
58
59share table with other systems outside Hive, so you need to create an external table with location where it is.
60```
61CREATE EXTERNAL TABLE IF NOT EXISTS ... (
62  ...)
63  ...
64LOCATION '/data/folder/file_name';
65```
66
67
68### Partitioning
69* You can store ur data in partitioned subdirectories (like the formating in hdf5)
70```
71CREATE TABLE ...(
72  ...
73)
74PARTINIONED BY (column_name  datatype)
75```
76
77### Save queries
78```
79 hive -f /give_a_path/a_name.hql
80```
81or using Ambari, or via Oozie