Prezi logo
Log in
Loading…

HIVE

AR

Anisha Roy

Transcript

HIVE

a platform used to develop SQL

type scripts to do MapReduce operations

Introduction

Introduction

  • Hive is a data warehouse infrastructuretool to process structure data in Hadoop. It resides on top of Hadoop to summarize Big Data and makes querying and analyzing easy.

  • Initially Hive was developed by Facebook, later Apache software Foundation took it up and developed it further as an open source under the name Apache Hive

Features Of Hive

Features

  • It stores schema in database and processed data into HDFS( Hadoop Distributed File System)

  • It is designed for OLAP

  • It provides SQL type language for querying called HiveQl or HQL

  • It is fast, familiar, scalable and extensible

Architecture of Hive

  • User Interface- Hive is data warehouse infrastructure software that can create interaction betweeen user and HDFS. The user interfaces that Hive supports are Hive Web UI, Hive Command line and Hive HD.

  • HiveQL Process Engine- HiveQL is similar to SQL for querying on schema info on the Megastore. It is one of the replacements of traditional approach for MapReduce program. Instead of writing MapReduce program in Java, we can write a query for MapReduce job and process it.

Architecture

Hive Architecture

Contd.

  • Execution Engine- The conjuction part of HiveQL process Engine and MapReduce is a Hive Execution Engine. Execution engine processes the query and generates results as same as MapReduce results. It uses the flavour of MapReduce.

  • HDFS or HBASE- Hadoop distributd file system or HBASE are the data stirage techniques to store data into file system.

  • Meta Store- Hive chooses respective database servers to store the schema or Metadata of tables, databases, columns in a table, their data types and HDFS mapping.

Figure

Pictorial Architecture

Working of Hive

  • Execute Query- The Hive interface such as Command Line or WebUI send query Driver to execute
  • Get Plan- The driver takes the help of query compiler that parses the query to check the syntax or query plan or the requirement of query
  • Get Metadata- The compiler send metadata request to Megastore
  • Send Metadata- Metastore sends metadata as a response to the compiler.
  • Send Plan- The compiler checks the requirement and resend the plan to the driver. Till here the parsing adn compiling of query is completed.
  • Execute Plan- The driver send the execute plan to the execution engine.

Functioning

Contd.

  • Execute Job- Internally, the process of execution job is a MapReduce job. Here, the query executes MapReduce job.
  • Metadata Ops- Meanwhile in execution, the engine can execute metadata operations with Metastore
  • Fetch Results- The execution engine receives the results from Datanodes.
  • Send Results- The execution engine send those resultant values to the driver
  • Send Results- The driver send the results to Hive Interfaces.

Working

Pictorial Representation on Working of Hive

Diagrammatic Representation

Hive-Data Types

All the data types in Hive are classified into four types-

  • Column Types
  • Literals
  • Null Values
  • Complex Types

Data Types

Column Types

Column Types

  • Intergral Types- Specified for using intergral data types only. INT, BIGINT, SMALLINT, TINYINT.

  • String Types- Specified using ('') or (""). VARCHAR and CHAR

  • Timestamp- Supports java.sql format 'YYYY-MM-DD HH:MM:SS:ffffffffff' and format 'yyyy-mm-dd hh:mm:ss:ffffffffff'

  • Dates- decribed in format "YYYY-MM-DD"

  • Decimals- The DECIMAL type in Hive is as same as Big Decimal format of Java. Represnts arbitraty precision

  • Union Types- Collection of heterogeneous data types. One can create an instance using create union.

Literals

  • Floating Point Types- These are nothing but numbers with decomal points. Genrally this data type is composed of DOUBLE data type.
  • Decimal Type- These is nothing but floating point value with higher range than DOUBLE data types. The range if decimal type is approximately -10^-308 to 10^308

Literals

Complex Types

  • Arrays- They are used in same way as used in Java.

Syntax: ARRAY<DATA_TYPE>

  • Maps- They are similar to Java Maps.

Syntax: MAP<primitive_type, data_type>

  • Structs- they are similar to using complex data with comment.

Syntax: STRUCT<col_name: data_type[ COMMENT col_comment,.....]>

Complex Types

HQL Queries

HQL Statements

  • Create Databases-

hive>create database [if not exists] userdb;

hive>create schema usedb;

hive> show databses;

  • Drop Databases-

hive> drop database [if exists] userdb;

hive> drop database [if exists] userdb cascade;

hive> drop schema userdb;

  • Create Table-

hive> create table if not exists employee( eid int, name string, salary decinal, destionation string)

>comment 'employee details'

>row format delimited

>fields terminated by '\t'

>lines terminated by '\n'

>stored as text file

Partition

  • Hive organises tables into partitions as a way of dividing tables into related parts based on alues. It is easy to query a portion of data

  • Adding Partition-

Syntax: hive>alter table employee add partition(year='2013') location '/2012/part2012';

  • Dropping Partition-

Syntax: hive>alter table employee drop [if exists] partition (year='2013');

Partition

Other HQL queries

  • Select Where- hive> select *from employee where salary>50000;

  • Select Order By- hive> select Id, Name, Dept from employee order by dept;

  • Select-Group By- hive>select Dept,count(*) from employee group by Dept.

  • Joins- The different types of join listed below:

Join, Left Outer Join, Right Outer Join, Full Outer Join

Syntax: hive> SELECT c.ID, c.NAME, c.AGE, o. AMOUNT FROM CUSTOMERS c JOIN ORDERS o ON (c.ID= o. CUSTOMER_ID)

Others

Thank You

Thank you