What is Hive

Hive is a data warehouse system for Hadoop that facilitates easy data summarization, ad-hoc queries, and the analysis of large datasets stored in Hadoop compatible file systems. Hive provides a mechanism to project structure onto this data and query the data using a SQL-like language called HiveQL. At the same time this language also allows traditional map/reduce programmers to plug in their custom mappers and reducers when it is inconvenient or inefficient to express this logic in HiveQL.

Hive is an abstraction on top of MapReduce it allows users to query data in the Hadoop cluster without knowing Java or MapReduce.It Uses the HiveQL language,Very similar to SQL.

8 Points about Hive:-

• Hive was originally developed at Facebook
• Provides a very SQL-like language
• Can be used by people who know SQL
• Enabling Hive requires almost no extra work by the system administrator
• Hive ‘layers’ table definitions on top of data in HDFS
• Hive tables are stored in Hive’s ‘warehouse’ directory in HDFS, By default, /user/hive/warehouse
• Tables are stored in subdirectories of the warehouse directory
• Actual data is stored in flat files- Control character-delimited text, or SequenceFiles

Hive is Data warehousing tool on top of Hadoop.It same as SQL

• SQL like Queries
• SHOW TABLES, DESCRIBE, DROPTABLE
• CREATE TABLE, ALTER TABLE
• SELECT, INSERT

Hive Limitations

• Not all ‘standard’ SQL is supported
• No support for UPDATE or DELETE
• No support for INSERTing single rows
• Relatively limited number of built-in functions
• No datatypes for date or time - Use the STRING datatype instead.In new version date or time datatype will support.

Hive Architecture

Metastore: stores system catalog
Driver: manages life cycle of HiveQL query as it moves thru’ HIVE; also manages session handle and session statistics
Query compiler: Compiles HiveQL into a directed acyclic graph of map/reduce tasks
Execution engines: The component executes the tasks in proper dependency order; interacts with Hadoop
HiveServer: provides Thrift interface and JDBC/ODBC for integrating other applications.
Client components: CLI, web interface, jdbc/odbc inteface
Extensibility interface include SerDe, User Defined Functions and User Defined Aggregate Function.

Hive Commands

To launch the Hive shell, start a terminal and run $ hive
Note: example is the table name for all qurey

hive>
Hive : Creating Tables
hive> CREATE TABLE example (id INT, name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
hive> DESCRIBE example;
hive> SHOW TABLES;

Hive : Loading Data Into Hive
Data is loaded into Hive with the LOAD DATA INPATH statement – Assumes that the data is already in HDFS
hive> LOAD DATA INPATH “file_txtdata.txt” INTO TABLE example;
If the data is on the local filesystem, use LOAD DATA LOCAL INPATH – Automatically loads it into HDFS
hive> LOAD DATA LOCAL INPATH "file_txtdata.txt" INTO TABLE example;
Hive : SELECT Queries
Hive supports most familiar SELECT syntax
hive> SELECT * FROM example LIMIT 10;
hive> SELECT * FROM example WHERE id > 100 ORDER BY name ASC LIMIT 10;

Joining Tables
SELECT e.name, e.dep, s.id FROM example e JOIN sample s ON (e.dep = s.dep) WHERE e.id >= 20;

Creating User-Defined Functions
INSERT OVERWRITE TABLE u_data_new
SELECT TRANSFORM (userid, movieid, rating, unixtime) USING 'python weekday_mapper.py' AS (userid, movieid, rating, weekday) FROM u_data;

Join Query: sample
1.Create table
CREATE TABLE example(ID int,SUBJECT string,PRODUCT string,PERIOD int,START_TIME int,OPERATION string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','STORED AS TEXTFILE;
2.Load data (save the file in related folder)
hive> LOAD DATA LOCAL INPATH "file_txtdata.txt" INTO TABLE example;
3.Join Query
select A.*
from example A
join (
select id, max(start_time) as start_time
from example B
where start_time < 25
group by id ) MAXSP
ON A.id=MAXSP.id and A.start_time = MAXSP.start_time;

Using NOT IN / IN  hive query
SELECT * FROM example WHERE NOT array_contains(array(7,6,5,4,2,12), id)

Hive Application,Components,Model and Layout

Hive Applications

• Log processing
• Text mining
• Document indexing
• Customer-facing business intelligence (e.g., Google Analytics)
• Predictive modeling, hypothesis testing

Hive Components

• Shell: allows interactive queries like MySQL shell connected to database – Also supports web and JDBC clients
• Driver: session handles, fetch, execute
• Compiler: parse, plan, optimize
• Execution engine: DAG of stages (M/R,HDFS, or metadata)
• Metastore: schema, location in HDFS,SerDe

Data Model

• Tables – Typed columns (int, float, string, date,boolean) – Also, list: map (for JSON-like data)
• Partitions – e.g., to range-partition tables by date
• Buckets – Hash partitions within ranges (useful for sampling, join optimization)

Metastore

• Database: namespace containing a set of tables
• Holds table definitions (column types,physical layout)
• Partition data
• Uses JPOX ORM for implementation; can be stored in Derby, MySQL, many other relational databases

Physical Layout

• Warehouse directory in HDFS – e.g., /home/hive/warehouse
• Tables stored in subdirectories of warehouse – Partitions, buckets form subdirectories of tables
• Actual data stored in flat files – Control char-delimited text, or SequenceFiles – With custom SerDe, can use arbitrary format