MIT Art Design and Technology University

MIT School of Computing, Pune

21BTCS604 - Big Data Technology

Class - TY - CORE (Sem - II)

Unit - IV – Hive and Pig

Prof. Dr. Jagannath Nalavade

AY 2025-2026 SEM-II
What is HIVE ?
● Data warehousing package built on top of Hadoop
● Used for data analysis
● Targeted towards users comfortable with SQL
● Similar to SQL the query language is HiveQL
● No need learn Java and Hadoop APIs
● Developed by Facebook and made open source
● For managing and querying structured data
 •UI: Users submits queries
and other operations to the
Hive Architecture system

•Metastore: Stores all the

structure information of the
various tables and partitions
in the warehouse

•Execution Engine: Manages

dependencies between these
different stages of the plan
and executes these stages on
the appropriate system
Hive Data Model
Tables: Data is stored as a directory in HDFS
Partitions: Divides a table into parts based on a key(column).
It is a way of dividing a table into related parts based on the values of
partitioned columns such as date, city, and department. Using partition, it is
easy to query a portion of the data.
Buckets: Bucketing decomposes data into more manageable or equal parts.
With partitioning, there is a possibility that you can create multiple small
partitions based on column values. If you go for bucketing, you are restricting
number of buckets to store the data. This number is defined during table
creation scripts.
Hive vs Relational Database
● By using Hive, we can perform some peculiar functionality that is not
achieved in Relational Databases.
● Relational databases are of "Schema on READ and Schema on Write"
Hive is "Schema on READ only".
● No support for Update or Delete in HIVE
● No support for inserting single rows.
● Supports Partitioning and Bucketing.
Data Hierarchy
• Hive is organised hierarchically into:
• Databases: namespaces that separate tables and other objects
• Tables: homogeneous units of data with the same schema
• Analogous to tables in an RDBMS
• Partitions: determine how the data is stored
• Allow efficient access to subsets of the data
• Buckets/clusters
• For sub-sampling within a partition
• Join optimization
HiveQL
• HiveQL / HQL provides the basic SQL-like operations:
• Select columns using SELECT
• Filter rows using WHERE
• JOIN between tables
• Evaluate aggregates using GROUP BY
• Store query results into another table
• Download results to a local directory (i.e., export from HDFS)
• Manage tables and queries with CREATE, DROP, and ALTER
Primitive Data Types
Type Comments
TINYINT, SMALLINT, INT, 1, 2, 4 and 8-byte integers
BIGINT
BOOLEAN TRUE/FALSE
FLOAT, DOUBLE Single and double precision real numbers
STRING Character string
TIMESTAMP Unix-epoch offset or datetime string
DECIMAL Arbitrary-precision decimal
BINARY Opaque; ignore these bytes
Complex Data Types
Type Comments
STRUCT A collection of elements
If S is of type STRUCT {a INT, b INT}:
S.a returns element a
MAP Key-value tuple
If M is a map from 'group' to GID:
M['group'] returns value of GID
ARRAY Indexed list
If A is an array of elements ['a','b','c']:
A[0] returns 'a'
HiveQL Limitations
• HQL only supports equi-joins, outer joins, left semi-joins
• Because it is only a shell for Map-Reduce, complex queries can be
hard to optimise
• Missing large parts of full SQL specification:
• HAVING clause in SELECT
• Correlated sub-queries
• Sub-queries outside FROM clauses
• Updatable or materialized views
• Stored procedures
Hive Metastore
• Stores Hive metadata
• Default metastore database uses Apache Derby
• Various configurations:
• Embedded (in-process metastore, in-process database)
• Mainly for unit tests
• Local (in-process metastore, out-of-process database)
• Each Hive client connects to the metastore directly
• Remote (out-of-process metastore, out-of-process database)
• Each Hive client connects to a metastore server, which connects to the metadata
database itself
Hive Warehouse
• Hive tables are stored in the Hive “warehouse”
• Default HDFS location: /user/hive/warehouse
• Tables are stored as sub-directories in the warehouse directory
• Partitions are subdirectories of tables
• External tables are supported in Hive
• The actual data is stored in flat files
Hive Schemas
• Hive is schema-on-read
• Schema is only enforced when the data is read (at query time)
• Allows greater flexibility: same data can be read using multiple schemas
• Contrast with an RDBMS, which is schema-on-write
• Schema is enforced when the data is loaded
• Speeds up queries at the expense of load times
Create Table Syntax
CREATE TABLE table_name
(col1 data_type,
col2 data_type,
col3 data_type,
col4 datatype )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS format_type;
Simple Table
CREATE TABLE page_view
(viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User' )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
More Complex Table
CREATE TABLE employees (
(name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT<street:STRING,
city:STRING,
state:STRING,
zip:INT>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
External Table
CREATE EXTERNAL TABLE page_view_stg
(viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/user/staging/page_view';
More About Tables
• CREATE TABLE
• LOAD: file moved into Hive’s data warehouse directory
• DROP: both metadata and data deleted
• CREATE EXTERNAL TABLE
• LOAD: no files moved
• DROP: only metadata deleted
• Use this when sharing with other Hadoop applications, or when you want to
use multiple schemas on the same data
Partitioning
• Can make some queries faster
• Divide data based on partition column
• Use PARTITION BY clause when creating table
• Use PARTITION clause when loading data
• SHOW PARTITIONS will show a table’s partitions
Bucketing
• Can speed up queries that involve sampling the data
• Sampling works without bucketing, but Hive has to scan the entire dataset
• Use CLUSTERED BY when creating table
• CLUSTERED BY (column_name) INTO N BUCKETS
• For sorted buckets, add SORTED BY
CLUSTERED BY (userid)
SORTED BY (viewTime)
INTO 4 BUCKETS

• To query a sample of your data, use TABLESAMPLE

SELECT * FROM table_name TABLESAMPLE(BUCKET 1 OUT OF 4);
Browsing Tables And Partitions
Command Comments
SHOW TABLES; Show all the tables in the database
SHOW TABLES 'page.*'; Show tables matching the
specification ( uses regex syntax )
SHOW PARTITIONS page_view; Show the partitions of the
page_view table
DESCRIBE page_view; List columns of the table
DESCRIBE EXTENDED page_view; More information on columns
(useful only for debugging )
DESCRIBE page_view List information about a partition
PARTITION (ds='2008-10-31');
col_name data_type comment
-------------------------------------
viewTime int
userid bigint
page_url string
referrer_url string
ip string IP Address of the User

# Detailed Table Information

Database: default
Owner: user
Location: /user/hive/warehouse/page_view
Table Type: MANAGED_TABLE
Loading Data
• Use LOAD DATA to load data from a file or directory
• Will read from HDFS unless LOCAL keyword is specified
• Will append data unless OVERWRITE specified
• PARTITION required if destination table is partitioned

LOAD DATA LOCAL INPATH '/tmp/pv_2008-06-8_us.txt'

OVERWRITE INTO TABLE page_view
PARTITION (date='2008-06-08', country='US')
Inserting Data
• Use INSERT to load data from a Hive query
• Will append data unless OVERWRITE specified
• PARTITION required if destination table is partitioned

FROM page_view_stg pvs

INSERT OVERWRITE TABLE page_view
PARTITION (dt='2008-06-08', country='US')
SELECT [Link], [Link], pvs.page_url,
pvs.referrer_url
WHERE [Link] = 'US';
Loading And Inserting Data: Summary
Use this For this purpose
LOAD Load data from a file or directory
INSERT Load data from a query
• One partition at a time
• Use multiple INSERTs to insert into
multiple partitions in the one query
CREATE TABLE AS (CTAS) Insert data while creating a table
Add/modify external file Load new data into external table
Sample Select Clauses
• Select from a single table
SELECT *
FROM sales
WHERE amount > 10 AND
region = "US";
• Select from a partitioned table
SELECT page_views.*
FROM page_views
WHERE page_views.date >= '2008-03-01' AND
page_views.date <= '2008-03-31'
Relational Operators
• ALL and DISTINCT
• Specify whether duplicate rows should be returned
• ALL is the default (all matching rows are returned)
• DISTINCT removes duplicate rows from the result set
• WHERE
• Filters by expression
• Does not support IN, EXISTS or sub-queries in the WHERE clause
• LIMIT
• Indicates the number of rows to be returned
Relational Operators
• GROUP BY
• Group data by column values
• Select statement can only include columns included in the
GROUP BY clause
• ORDER BY / SORT BY
• ORDER BY performs total ordering
• Slow, poor performance
• SORT BY performs partial ordering
• Sorts output from each reducer
Advanced Hive Operations
• JOIN
• If only one column in each table is used in the join, then only one
MapReduce job will run
• This results in 1 MapReduce job:
SELECT * FROM a JOIN b ON [Link] = [Link] JOIN c ON [Link] = [Link]

• This results in 2 MapReduce jobs:

SELECT * FROM a JOIN b ON [Link] = [Link] JOIN c ON b.key2 = [Link]

• If multiple tables are joined, put the biggest table last and the reducer will
stream the last table, buffer the others
• Use left semi-joins to take the place of IN/EXISTS
SELECT [Link], [Link] FROM a LEFT SEMI JOIN b on [Link] = [Link];
Advanced Hive Operations
• JOIN
• Do not specify join conditions in the WHERE clause
• Hive does not know how to optimise such queries
• Will compute a full Cartesian product before filtering it
• Join Example
SELECT
[Link], a.price_close, b.price_close
FROM stocks a
JOIN stocks b ON [Link] = [Link]
WHERE [Link] = 'AAPL' AND
[Link] = 'IBM' AND
[Link] > '2010-01-01';
Hive Stinger
• MPP-style execution of Hive queries
• Available since Hive 0.13
• No MapReduce
• We will talk about this more when we get to SQL on Hadoop
References
• [Link]
Apache Pig
What is PIG?
Apache Pig is a platform for analyzing large data sets that consists of a
high-level language for expressing data analysis programs, coupled with
infrastructure for evaluating these programs.
The salient property of Pig programs is that their structure is amenable to
substantial parallelization, which in turns enables them to handle very large
data sets.
Apache Pig creates a simpler procedural language abstraction over
MapReduce to expose a more SQL-like interface for Hadoop applications.
We can write simple Pig commands instead of entire MapReduce
applications.
Pig Capabilities
• Support for
• Grouping
• Joins
• Filtering
• Aggregation
• Extensibility
• Support for User Defined Functions (UDF’s)
• Leverages the same massive parallelism as native MapReduce
Pig Basics
• Pig is a client application
• No cluster software is required
• Interprets Pig Latin scripts to MapReduce jobs
• Parses Pig Latin scripts
• Performs optimization
• Creates execution plan
• Submits MapReduce jobs to the cluster
PIG Characteristics
● PIG Latin - A high-level language developed by Pig where programmers
can develop their own functions for reading, writing and processing
data.
● Apache Pig uses a multi-query approach thereby reducing the LoC and
development time.
● Execution Types -
○ Local Mode (JVM)
○ MapReduce Mode (Hadoop Cluster)
● Running Pig programs -
○ Script
○ Grunt
○ Embedded
PIG LATIN Dataflow
LOAD

TRANSFORM

DUMP DEPLOY
Pig Latin
• Pig Latin scripts are generally organized as follows
• A LOAD statement reads data
• A series of “transformation” statements process the data
• A STORE statement writes the output to the filesystem
• A DUMP statement displays output on the screen
• Logical vs. physical plans:
• All statements are stored and validated as a logical plan
• Once a STORE or DUMP statement is found the logical plan is executed
Example Pig Script
-- Load the content of a file into a pig bag named ‘input_lines’
input_lines = LOAD '[Link]' AS (line:chararray);

-- Extract words from each line and put them into a pig bag named ‘words’
words = FOREACH input_lines GENERATE FLATTEN(TOKENIZE(line)) AS word;

-- filter out any words that are just white spaces

filtered_words = FILTER words BY word MATCHES '\\w+';

-- create a group for each word

word_groups = GROUP filtered_words BY word;

-- count the entries in each group

word_count = FOREACH word_groups GENERATE COUNT(filtered_words) AS count, group AS word;

-- order the records by count

ordered_word_count = ORDER word_count BY count DESC;

-- Store the results ( executes the pig script )

STORE ordered_word_count INTO 'output’;
Basic “grunt” Shell Commands
• Help is available
$ pig -h
• Pig supports HDFS commands
grunt> pwd
• put, get, cp, ls, mkdir, rm, mv, etc.
About Pig Scripts
• Pig Latin statements grouped together in a file
• Can be run from the command line or the shell
• Support parameter passing
• Comments are supported
• Inline comments '--'
• Block comments /* */
PIG Vs HIVE
PIG: HIVE:
● Procedural Data FLow Language. ● Declarative SQL Language.
● Mainly used when there are more joins ● Used when limited number of joins are
and filters. present.
● Operates on the client side of a cluster. ● Operates on the server side of a cluster.
● Mainly used by researchers for ● Mainly used by data analysts for creating
programming. reports.
● Can handle both structured and ● Supports only structured data.
unstructured data.
● Cannot operate on thrift server.
● Can operate on thrift server.
● Pig uses Pig Latin for programming
● No need to create tables. ● It uses HQL which goes beyond the SQL.
● Should manually create tables.
Simple Data Types
Type Description
int 4-byte integer
long 8-byte integer
float 4-byte (single precision) floating point
double 8-byte (double precision) floating point
bytearray Array of bytes; blob
chararray String (“hello world”)
boolean True/False (case insensitive)
datetime A date and time
biginteger Java BigInteger
bigdecima Java BigDecimal
l
Complex Data Types

Type Description
Tuple Ordered set of fields (a “row / record”)
Bag Collection of tuples (a “resultset / table”)
Map A set of key-value pairs
Keys must be of type chararray
Pig Data Formats
• BinStorage
• Loads and stores data in machine-readable (binary) format
• PigStorage
• Loads and stores data as structured, field delimited text files
• TextLoader
• Loads unstructured data in UTF-8 format
• PigDump
• Stores data in UTF-8 format
• YourOwnFormat!
• via UDFs
Loading Data Into Pig
• Loads data from an HDFS file
var = LOAD '[Link]';
var = LOAD '[Link]' AS (id, name, salary);
var = LOAD '[Link]' using PigStorage()
AS (id, name, salary);
• Each LOAD statement defines a new bag
• Each bag can have multiple elements (atoms)
• Each element can be referenced by name or position ($n)
• A bag is immutable
• A bag can be aliased and referenced later
Input And Output
• STORE
• Writes output to an HDFS file in a specified directory
grunt> STORE processed INTO 'processed_txt';
• Fails if directory exists
• Writes output files, part-[m|r]-xxxxx, to the directory
• PigStorage can be used to specify a field delimiter
• DUMP
• Write output to screen
grunt> DUMP processed;
Relational Operators
• FOREACH
• Applies expressions to every record in a bag
• FILTER
• Filters by expression
• GROUP
• Collect records with the same key
• ORDER BY
• Sorting
• DISTINCT
• Removes duplicates
FOREACH . . .GENERATE
• Use the FOREACH …GENERATE operator to work with rows of data,
call functions, etc.
• Basic syntax:
alias2 = FOREACH alias1 GENERATE expression;
• Example:
DUMP alias1;
(1,2,3) (4,2,1) (8,3,4) (4,3,3) (7,2,5) (8,4,3)
alias2 = FOREACH alias1 GENERATE col1, col2;
DUMP alias2;
(1,2) (4,2) (8,3) (4,3) (7,2) (8,4)
FILTER. . .BY
• Use the FILTER operator to restrict tuples or rows of data
• Basic syntax:
alias2 = FILTER alias1 BY expression;
• Example:
DUMP alias1;
(1,2,3) (4,2,1) (8,3,4) (4,3,3) (7,2,5) (8,4,3)
alias2 = FILTER alias1 BY (col1 == 8) OR (NOT (col2+col3 >
col1));
DUMP alias2;
(4,2,1) (8,3,4) (7,2,5) (8,4,3)
GROUP. . .ALL
• Use the GROUP…ALL operator to group data
• Use GROUP when only one relation is involved
• Use COGROUP with multiple relations are involved
• Basic syntax:
alias2 = GROUP alias1 ALL;
• Example:
DUMP alias1;
(John,18,4.0F) (Mary,19,3.8F) (Bill,20,3.9F)
(Joe,18,3.8F)
alias2 = GROUP alias1 BY col2;
DUMP alias2;
(18,{(John,18,4.0F),(Joe,18,3.8F)})
(19,{(Mary,19,3.8F)})
(20,{(Bill,20,3.9F)})
ORDER. . .BY
• Use the ORDER…BY operator to sort a relation based on one or
more fields
• Basic syntax:
alias = ORDER alias BY field_alias [ASC|DESC];
• Example:
DUMP alias1;
(1,2,3) (4,2,1) (8,3,4) (4,3,3) (7,2,5) (8,4,3)
alias2 = ORDER alias1 BY col3 DESC;
DUMP alias2;
(7,2,5) (8,3,4) (1,2,3) (4,3,3) (8,4,3) (4,2,1)
DISTINCT. . .
• Use the DISTINCT operator to remove duplicate tuples in a relation.
• Basic syntax:
alias2 = DISTINCT alias1;
• Example:
DUMP alias1;
(8,3,4) (1,2,3) (4,3,3) (4,3,3) (1,2,3)
alias2= DISTINCT alias1;
DUMP alias2;
(8,3,4) (1,2,3) (4,3,3)
Relational Operators
• FLATTEN
• Used to un-nest tuples as well as bags
• INNER JOIN
• Used to perform an inner join of two or more relations based on common field
values
• OUTER JOIN
• Used to perform left, right or full outer joins
• SPLIT
• Used to partition the contents of a relation into two or more relations
• SAMPLE
• Used to select a random data sample with the stated sample size
INNER JOIN. . .
• Use the JOIN operator to perform an inner, equi-join join of two or
more relations based on common field values
• The JOIN operator always performs an inner join
• Inner joins ignore null keys
• Filter null keys before the join
• JOIN and COGROUP operators perform similar functions
• JOIN creates a flat set of output records
• COGROUP creates a nested set of output records
INNER JOIN Example
DUMP Alias1; Join Alias1 by Col1 to Alias2 by
(1,2,3) Col1
(4,2,1) Alias3 = JOIN Alias1 BY Col1,
Alias2 BY Col1;
(8,3,4)
(4,3,3)
(7,2,5) Dump Alias3;
(8,4,3) (1,2,3,1,3)
DUMP Alias2; (4,2,1,4,6)
(2,4) (4,3,3,4,6)
(8,9) (4,2,1,4,9)
(1,3) (4,3,3,4,9)
(2,7) (8,3,4,8,9)
(2,9) (8,4,3,8,9)
(4,6)
(4,9)
OUTER JOIN. . .
• Use the OUTER JOIN operator to perform left, right, or full outer
joins
• Pig Latin syntax closely adheres to the SQL standard
• The keyword OUTER is optional
• keywords LEFT, RIGHT and FULL will imply left outer, right outer and full
outer joins respectively
• Outer joins will only work provided the relations which need to
produce nulls (in the case of non-matching keys) have schemas
• Outer joins will only work for two-way joins
• To perform a multi-way outer join perform multiple two-way outer join
statements
User-Defined Functions
• Natively written in Java, packaged as a jar file
• Other languages include Jython, JavaScript, Ruby, Groovy, and Python
• Register the jar with the REGISTER statement
• Optionally, alias it with the DEFINE statement
REGISTER /src/[Link];
A = LOAD 'students';
B = FOREACH A GENERATE [Link]($0);
DEFINE
• DEFINE can be used to work with UDFs and also streaming
commands
• Useful when dealing with complex input/output formats
/* read and write comma-delimited data */
DEFINE Y '[Link]' INPUT(stdin USING PigStreaming(','))
OUTPUT(stdout USING PigStreaming(','));
A = STREAM X THROUGH Y;

/* Define UDFs to a more readable format */

DEFINE MAXNUM [Link];
A = LOAD ‘student_data’ AS (name:chararray, gpa1:float, gpa2:double);
B = FOREACH A GENERATE name, MAXNUM(gpa1, gpa2);
DUMP B;
HIVE Pros and Cons:
Pros: Cons:
● Hive works extremely well with large ● Joins (especially left join and right join)
data sets. Analysis over them is made are very complex, space consuming and
easy. time consuming. Improvement in this
● User-defined functions gives flexibility to area would be of great help!
users to define operations that are used ● Debugging can be messy with
frequently as functions. ambiguous return codes and large jobs
● String functions that are available in can fail without much explanation as to
Hive has been extensively used for why.
analysis. ● Slow because it uses mapreduce.
● Partition to increase query efficiency.
PIG Pros and Cons:
Pros: Cons:
● Writing your own User Defined
● It has many advanced features built-in Functions (UDFS) is a nice feature but
such as joins, secondary sort, many can be painful to implement in practice
optimizations, predicate push-down, etc. ● May not fit every need and a SQL-like
● Provides a decent abstraction for abstraction may not be easy
● The commands are not executed unless
Map-Reduce jobs, allowing for a faster either you dump or store an
result than creating your own MR jobs intermediate or final result. This
● Can handle large and unstructured increases the iteration between debug
and resolving the issue.
datasets.
HBase is… HBase is not
...
A distributed column oriented database built Not an SQL Database
on top HDFS.
Not Relational
A data model that is similar to Google’s Big
Table that designed to provide quick random No Joins
access to huge amounts of data.
No fancy query language and no
sophisticated query engine.
HBase Features
Linear Scalability: Capable of storing hundreds of terabytes of data.
Automatic and configurable sharding of tables.
Automatic failover support.
Strictly consistent read and writes.
HBase vs HDFS
Both are distributed systems that scale to hundreds or thousands of nodes.
 HBase vs HDFS (Continued...)
•HBase is a database built on top of the HDFS. •HDFS is a suitable for storing large files.

•HBase provides fast lookups for larger tables. •HDFS does not support fast individual record
lookups.
•It provides low latency access to single rows
from billions of records (Random access). •It provides high latency batch processing;

•HBase internally uses Hash tables and •It provides only sequential access of
provides random access, and it stores the data data.
in indexed HDFS files for faster lookups
Zookeeper
● Apache ZooKeeper is a software project of the Apache Software
Foundation.
● It is essentially a distributed hierarchical key-value store, which is used to
provide a distributed configuration service, synchronization service, and
naming registry for large distributed systems.
● Examples include configuration information, hierarchical naming space,
and so on. Applications can leverage these to coordinate distributed
processing across large clusters.
● ZooKeeper was developed by Yahoo research and was a sub-project of
Hadoop but is now a top-level Apache project in its own right.
ZooKeeper Service

● Zookeeper “Ensambles”
● Zookeeper provides high Availability and consistency
● Server know each other
● Client connects to only one server at a time.
Zookeeper Features and Uses
Features:
● Reliable System: This system is very reliable as it keeps working even if a node
fails.
● Simple Architecture: The architecture of ZooKeeper is quite simple as there is
a shared hierarchical namespace which helps coordinating the processes.
● Fast Processing: Zookeeper is especially fast in "read-dominant" workloads
(i.e. workloads in which reads are much more common than writes).
● Scalable: The performance of ZooKeeper can be improved by adding nodes.

Uses:
● HBase uses it for coordination between servers, bootstrapping etc.
● Hadoop and MapReduce for high availability of resource manager.
● Flume - Used for configuration.
References
• [Link]
VIDEO LINKS:
[Link]

[Link]

[Link]

[Link]