BTech-VII Sem

Computer Science and Engineering

Departmental Elective – CS702 (D) Big Data
Unit-3
Notes

Apache: Apache HTTP Server is an open source cross-platform web server. It is fast and
secure enough to be used to run major websites. As a Web server, Apache is responsible
for accepting directory (HTTP) requests from Internet users and sending them their desired
information in the form of files and Web pages. Apache is a web server software that is
responsible for accepting HTTP requests from visitors and sending them back the requested
information in the form of web pages. Or in simpler terms, it allows visitors to view content on
your website.

Introduction to Hive Hive Architecture:

The Hadoop ecosystem contains different sub-projects (tools) such as Sqoop,
Pig, and Hive that are used to help Hadoop modules.

 Sqoop: It is used to import and export data to and from between HDFS
and RDBMS.
 Pig: It is a procedural language platform used to develop a script for
MapReduce operations.
 Hive: It is a platform used to develop SQL type scripts to do MapReduce
operations.

What is Hive
The Hive Query Language (HiveQL) is a query language for Hive to
process and analyze structured data in a Metastore

Hive is a data warehouse infrastructure tool to process structured 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 the Apache Software

Foundation took it up and developed it further as an open source under the
name Apache Hive. It is used by different companies. For example, Amazon
uses it in Amazon Elastic MapReduce.

Hive is not
 A relational database
 A design for OnLine Transaction Processing (OLTP)
 A language for real-time queries and row-level updates
Note: Hive works on the server-side while Pig works on the client-side of your clusters. Hive
can access raw data while Pig Latin scripts cannot.

Features of Hive
 It stores schema in a database and processed data into HDFS.
 It is designed for OLAP.
 It provides SQL type language for querying called HiveQL or HQL.
 It is familiar, fast, scalable, and extensible.

Architecture of Hive
The following component diagram depicts the architecture of Hive:

This component diagram contains different units. The following table

describes each unit:

Unit Name Operation

Hive is a data warehouse infrastructure software that can create interaction between
User Interface user and HDFS. The user interfaces that Hive supports are Hive Web UI, Hive command
line, and Hive HD Insight (In Windows server).

Hive chooses respective database servers to store the schema or Metadata of tables,
Meta Store
databases, columns in a table, their data types, and HDFS mapping.
 HiveQL is similar to SQL for querying on schema info on the Metastore. It is one of the
HiveQL Process Engine 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.

The conjunction part of HiveQL process Engine and MapReduce is Hive Execution
Execution Engine Engine. Execution engine processes the query and generates results as same as
MapReduce results. It uses the flavor of MapReduce.

Hadoop distributed file system or HBASE are the data storage techniques to store data
HDFS or HBASE
into file system.

Working of Hive
The following diagram depicts the workflow between Hive and Hadoop.

The following table defines how Hive interacts with Hadoop framework:

Step No. Operation

Execute Query
1 The Hive interface such as Command Line or Web UI sends query to Driver (any database
driver such as JDBC, ODBC, etc.) to execute.

Get Plan
2 The driver takes the help of query compiler that parses the query to check the syntax and
query plan or the requirement of query.
 Get Metadata
3
The compiler sends metadata request to Metastore (any database).

Send Metadata
4
Metastore sends metadata as a response to the compiler.

Send Plan
5 The compiler checks the requirement and resends the plan to the driver. Up to here, the
parsing and compiling of a query is complete.

Execute Plan
6
The driver sends the execute plan to the execution engine.

Execute Job
Internally, the process of execution job is a MapReduce job. The execution engine sends
7
the job to JobTracker, which is in Name node and it assigns this job to TaskTracker, which
is in Data node. Here, the query executes MapReduce job.

Metadata Ops
7.1 Meanwhile in execution, the execution engine can execute metadata operations with
Metastore.

Fetch Result
8
The execution engine receives the results from Data nodes.

Send Results
9
The execution engine sends those resultant values to the driver.

Send Results
10
The driver sends the results to Hive Interfaces.

HIVE Data Types:

Hive data types are categorized in numeric types, string types, misc types, and complex
types. A list of Hive data types is given below.

Integer Types

Type Size Range

TINYINT 1-byte signed -128 to 127

integer

SMALLINT 2-byte signed 32,768 to 32,767

integer
 INT 4-byte signed 2,147,483,648 to 2,147,483,647
integer

BIGINT 8-byte signed -9,223,372,036,854,775,808 to

integer 9,223,372,036,854,775,807

Decimal Type

Type Size Range

FLOAT 4-byte Single precision floating point number

DOUBLE 8-byte Double precision floating point number

Date/Time Types
TIMESTAMP

o It supports traditional UNIX timestamp with optional nanosecond precision.

o As Integer numeric type, it is interpreted as UNIX timestamp in seconds.
o As Floating point numeric type, it is interpreted as UNIX timestamp in seconds
with decimal precision.
o As string, it follows [Link] format "YYYY-MM-DD
HH:MM:[Link]" (9 decimal place precision)

DATES

The Date value is used to specify a particular year, month and day, in the form YYYY--
MM--DD. However, it didn't provide the time of the day. The range of Date type lies
between 0000--01--01 to 9999--12--31.

String Types
STRING

The string is a sequence of characters. It values can be enclosed within single quotes
(') or double quotes (").

Varchar
The varchar is a variable length type whose range lies between 1 and 65535, which
specifies that the maximum number of characters allowed in the character string.

CHAR

The char is a fixed-length type whose maximum length is fixed at 255.

Complex Type

Type Size Range

Struct It is similar to C struct or an object where struct('James','Roy')

fields are accessed using the "dot" notation.

Map It contains the key-value tuples where the map('first','James','last','Roy')

fields are accessed using array notation.

Array It is a collection of similar type of values that

indexable using zero-based integers.

Hive Query Language:

The Hive Query Language (HiveQL) is a query language for Hive to

process and analyze structured data in a Metastore

To interact with tables, databases, and queries, Hive provides a SQL like environment
through hadoop hiveql. To execute various types of data processing and querying, we can
have different types of Clauses for improved communication with various nodes outside the
ecosystem. HIVE also has JDBC connectivity.

Following features provide by Hive:

 Creating databases, tables, and other forms of data modelling, etc.

 ETL features such data extraction, transformation, and table loading.
 Joins to combine various data tables.
 User-specific customised scripts for coding simplicity.
 A quicker querying tool built upon Hadoop.

Creating Databases and Tables

In Hive, a table is a collection of data that is sorted according to a specific set of identifiers
using a schema.

Step 1: Create a Database

CREATE DATABASE IF NOT EXISTS mydatabase;

If a database with the name "mydatabase" doesn't already exist, this statement creates one.
The database is only created if it doesn't already exist, thanks to the IF NOT EXISTS
condition.

Step 2: Switching to a Database:

USE mydatabase;

By switching to the "mydatabase" database using this line, further activities can be carried
out in that database.

Step 3: Creating a Table::

CREATE TABLE IF NOT EXISTS employees (

id INT,
name STRING,
age INT

);

The "employees" table is created with this statement. It has three columns: "id" (integer),
"name" (string), and "age" (integer). The table is only generated if it doesn't already exist
thanks to the IF NOT EXISTS condition.

Step 4: Creating an External Table::

CREATE EXTERNAL TABLE IF NOT EXISTS ext_employees (

id INT,
name STRING,
age INT
) LOCATION '/path/to/data';

This hadoop hiveql command creates a new external table called "ext_employees." External
tables point to data that is kept in a location independent of Hive, preserving the original
location of the data. The HDFS path where the data is located is specified by the LOCATION
clause.

Loading Data into Tables

 Load data from HDFS

LOAD DATA INPATH '/path/to/data' INTO TABLE employees;

 Insert data into the table

INSERT INTO TABLE employees VALUES (1, 'John Doe', 30);

The LOAD DATA statement inserts data into the designated table from an HDFS path. The
"employees" table receives a specific row of data when the INSERT INTO TABLE query is
executed.

Querying Data with HiveQL

One of the core functions of using Apache Hive is data querying with HiveQL. You may
obtain, filter, transform, and analyse data stored in Hive tables using HiveQL, which is a
language comparable to SQL.

Following are a few typical HiveQL querying operations:

1. Select All Records:

SELECT * FROM employees;

This hadoop hiveql command retrieves all records from the "employees" table.

2. Filtering: Example: Select employees older than 25

SELECT * FROM employees WHERE age > 25;

Only those records from the "employees" table that have a "age" greater than 25 are chosen
by this.

3. Aggregation: Example: Count the number of employees

SELECT COUNT(*) FROM employees;

Example: Calculate the average age

SELECT AVG(age) FROM employees;

These hadoop hiveql queries count the number of employees and determine the average age
using aggregation operations on the "employees" table.

[Link]: Example: Sort by age in descending order

SELECT * FROM employees ORDER BY age DESC;

In order to extract employee names and their related departments, this query connects the
"employees" and "departments" databases based on the "department_id" field.

5. Joining Tables: Example: Join employees and departments based on department_id

SELECT [Link], [Link], [Link]

FROM employees e

JOIN departments d ON e.department_id = [Link];

The "department_id" column is used to link the "employees" and "departments" databases in
order to access employee names and their related departments.

6. Grouping and Aggregation: Example: Count employees in each department

SELECT department, COUNT(*) as employee_count

FROM employees
GROUP BY department;

This query counts the number of employees in each department and organises employees by
department.

7. Limiting Results: Example: Get the top 10 oldest employees

SELECT * FROM employees ORDER BY age DESC LIMIT 10;

This search returns the ten oldest employees in order of age.

Data Filtering and Sorting

HiveQL offers the means to carry out these actions on your data contained in Hive tables.
Data filtering and sorting are crucial data analysis activities. To filter and sort data using
HiveQL, follow these steps:

1. Data Filtering: You can use the WHERE clause to filter rows based on specific
conditions.

Example: Select marks which are more than 60.

SELECT * FROM employees WHERE marks > 60;

The "marks" table's field must be greater than 60 in order for this query to return all items
with that value.

2. Sorting Data: You can use the ORDER BY clause to order the result set according to one
or more columns.

Example: Consider ranking the by marks in increasing order.

SELECT * FROM marks ORDER BY INCR;

[Link] Filtering and Sorting: To obtain particular subsets of data in a specified

order, you can combine filtering and sorting.

Example: Select and sort marks more than 60.

SELECT * FROM marks WHERE marks > 60 ORDER BY INCR;

Data Transformations and Aggregations

Some examples of data aggregations and transformations you can make with HiveQL:

1. Data Transformations: HiveQL provides a number of built-in functions for changing the
data in your query.

Example: Change the case of names

SELECT UPPER(name) as upper_case_name FROM employees;

This hadoop hiveql query pulls the "name" column from the "employees" table and uses the
UPPER function to change the names to uppercase.

[Link]: Using functions like COUNT, SUM, AVG, and others, aggregates let you
condense data.

Example: Calculate the average age of the workforce, for instance.

SELECT AVG(age) as average_age FROM employees;

Using the AVG function, this query determines the average age of every employee in the
"employees" table.

3. Grouping and Aggregating: To group data into categories, the GROUP BY clause is used
with aggregate functions.

Example: For instance, total the personnel in each department.

SELECT department, COUNT(*) as employee_count

FROM employees
GROUP BY department;

The COUNT function is used in this query to count the number of employees in each
department and group the employees by the "department" column.

[Link] Before Aggregating: Before doing aggregations, data transformations and

filtering might be used.

Example: Calculate the typical age of your staff members that are over 35.

SELECT AVG(age) as average_age

FROM employees
WHERE age > 35;

This hadoop hiveql query determines the average age of the filtered subset of employees by
first excluding those over the age of 35.

Joins and Subqueries

HiveQL's advanced features, including as joins and subqueries, let you aggregate data from
various tables and run sophisticated searches.

Using HiveQL, let's examine how to use joins and subqueries:

1. Joins: With the use of joins, you can merge rows from various tables based on a shared
column. The INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN are
examples of common join types.

Example: As an illustration, retrieve the employees and the corresponding departments from
an inner join.

SELECT [Link], [Link], [Link]

FROM employees e
JOIN departments d ON e.department_id = [Link];

Based on the "department_id" column, this query combines information from the
"employees" and "departments" tables to retrieve employee names and their related
departments.

2. Subqueries: A subquery is a query that is nested inside another query. The SELECT,
WHERE, and FROM clauses can all use them.

Example: Determine the typical age of employees in each department using a subquery in
the SELECT statement.
SELECT department, (
SELECT AVG(age)
FROM employees e

WHERE e.department_id = [Link]

) as avg_age
FROM departments d;

The average age of employees for each department in the "departments" dataset is determined
by this query using a subquery.

3. Correlated Subqueries: An inner query that depends on results from the outer query is
referred to as a correlated subquery.

Example: Find employees whose ages are higher than the department's average, for instance.

SELECT id, name

FROM employees e
WHERE age > (
SELECT AVG(age)
FROM employees

WHERE department_id = e.department_id

);

To locate employees whose ages are higher than the mean ages of employees in the same
department, this query uses a correlated subquery.

Conclusion
 HiveQL (Hive Query Language) is used to communicate with Apache Hive, a
Hadoop data warehouse and SQL-like query language.
 Hive provides a SQL-like environment through hadoop hiveql for interacting with
tables, databases, and queries.
 Hive offers features like creating databases, tables.
 HiveQL allows data retrieval, filtering, transformation, and analysis. Common
querying operations include SELECT, WHERE, ORDER BY, and JOIN.
 HiveQL provides built-in functions for data transformations and aggregation.
Aggregations use functions like COUNT, SUM, AVG, etc., to condense data.

Difference between SQL and HiveQL

 Last Updated : 24 Nov, 2022


1. Structured Query Language (SQL): SQL is a domain-specific language used
in programming and designed for managing data held in a relational database
management system also known as RDBMS. It is also useful in handling structured
data, i.e., data incorporating relations among entities and variables. SQL is a
standard language for storing, manipulating, and retrieving data in databases.
2. Hive Query Language (HiveQL): HiveQL is a query language for Hive to
analyze and process structured data in a Meta-store. It is a mixture of SQL-92,
MySQL, and Oracle’s SQL. It is very much similar to SQL and highly scalable. It
reuses familiar concepts from the relational database world, such as tables, rows,
columns and schema, to ease learning. Hive supports four file formats those are
TEXT FILE, SEQUENCE FILE, ORC and RC FILE (Record Columnar File).
Differences between SQL and HiveQL is as follows:

On the basis
of SQL HiveQL

Update-
UPDATE, DELETE UPDATE, DELETE
commands in
INSERT, INSERT,
table structure

Manages Relational data Data Structures

Transaction Supported Limited Support Supported

Indexes Supported Supported

It contain a total of five

It contains Boolean, integral, floating-
data types i.e., Integral,
point, fixed-point, timestamp(nanosecond
Data Types floating-point, fixed-
precision) , Date, text and binary strings,
point, text and binary
temporal, array, map, struct, Union
strings, temporal

Hundreds of built-in
Functions Hundreds of built-in functions
functions

Mapreduce Not Supported Supported

Multitable
Not supported Supported
inserts in table
 On the basis
of SQL HiveQL

Create
table…as Not supported Supported
Select

Supported with SORT BY clause for

Select
Supported partial ordering and LIMIT to restrict
command
number of rows returned

Inner joins, outer joins, semi join, map

Joins Supported
joins, cross joins

Only Used in FROM, WHERE, or

Subqueries Supported
HAVING clauses

Views Can be Updated Read-only i.e. cannot be updated

Introduction to Pig:

What is Apache Pig?

Apache Pig is an abstraction over MapReduce. It is a tool/platform which
is used to analyze larger sets of data representing them as data flows. Pig
is generally used with Hadoop; we can perform all the data manipulation
operations in Hadoop using Apache Pig.

To write data analysis programs, Pig provides a high-level language

known as Pig Latin. This language provides various operators using which
programmers can develop their own functions for reading, writing, and
processing data.

To analyze data using Apache Pig, programmers need to write scripts using
Pig Latin language. All these scripts are internally converted to Map and
Reduce tasks. Apache Pig has a component known as Pig Engine that
accepts the Pig Latin scripts as input and converts those scripts into
MapReduce jobs

What is Pig in Hadoop?

Pig is a scripting platform that runs on Hadoop clusters designed to process and
analyze large datasets. Pig is extensible, self-optimizing, and easily programmed.

Programmers can use Pig to write data transformations without knowing Java. Pig
uses both structured and unstructured data as input to perform analytics and
uses HDFS to store the results.

Why Do We Need Apache Pig

 Using Pig Latin, programmers can perform MapReduce tasks easily
without having to type complex codes in Java.
 Apache Pig uses multi-query approach, thereby reducing the length of
codes. For example, an operation that would require you to type 200
lines of code (LoC) in Java can be easily done by typing as less as
just 10 LoC in Apache Pig. Ultimately Apache Pig reduces the
development time by almost 16 times.
 Pig Latin is SQL-like language and it is easy to learn Apache Pig when
you are familiar with SQL.
 Apache Pig provides many built-in operators to support data
operations like joins, filters, ordering, etc. In addition, it also provides
nested data types like tuples, bags, and maps that are missing from
MapReduce.

Features of Pig
 Apache Pig comes with the following features −
 Rich set of operators − It provides many operators to perform
operations like join, sort, filer, etc.
 Ease of programming − Pig Latin is similar to SQL and it is easy to write
a Pig script if you are good at SQL.
 Optimization opportunities − The tasks in Apache Pig optimize their
execution automatically, so the programmers need to focus only on
semantics of the language.
 Extensibility − Using the existing operators, users can develop their
own functions to read, process, and write data.
 UDF’s − Pig provides the facility to create User-defined Functions in
other programming languages such as Java and invoke or embed
them in Pig Scripts.
 Handles all kinds of data − Apache Pig analyzes all kinds of data, both
structured as well as unstructured. It stores the results in HDFS.

Apache Pig Vs SQL

 Listed below are the major differences between Apache Pig and
SQL.

Pig SQL

Pig Latin is a procedural language. SQL is a declarative language.

In Apache Pig, schema is optional. We

can store data without designing a
Schema is mandatory in SQL.
schema (values are stored as $01, $02
etc.)

The data model in Apache Pig is nested The data model used in SQL is
relational. flat relational.

Apache Pig provides limited opportunity There is more opportunity for

for Query optimization. query optimization in SQL.

Apache Pig Hive

Hive uses a language

Apache Pig uses a language called Pig
called HiveQL. It was originally
Latin. It was originally created at Yahoo.
created at Facebook.

HiveQL is a query processing

Pig Latin is a data flow language.
language.

Pig Latin is a procedural language and it HiveQL is a declarative

fits in pipeline paradigm. language.

Apache Pig can handle structured, Hive is mostly for structured

unstructured, and semi-structured data. data.

Applications of Apache Pig

Apache Pig is generally used by data scientists for performing tasks
involving ad-hoc processing and quick prototyping. Apache Pig is used −

 To process huge data sources such as web logs.

 To perform data processing for search platforms.
 To process time sensitive data loads.

, Anatomy of Pig:
The language used to analyze data in Hadoop using Pig is known as Pig Latin. It is a highlevel data
processing language which provides a rich set of data types and operators to perform various
operations on the data.

To perform a particular task Programmers using Pig, programmers need to write a Pig script
using the Pig Latin language, and execute them using any of the execution mechanisms
(Grunt Shell, UDFs, Embedded). After execution, these scripts will go through a series of
transformations applied by the Pig Framework, to produce the desired output.
Internally, Apache Pig converts these scripts into a series of MapReduce jobs, and thus, it
makes the programmer’s job easy. The architecture of Apache Pig is shown below.

Apache Pig Components

As shown in the figure, there are various components in the Apache Pig framework. Let us
take a look at the major components.
Parser
Initially the Pig Scripts are handled by the Parser. It checks the syntax of the script, does
type checking, and other miscellaneous checks. The output of the parser will be a DAG
(directed acyclic graph), which represents the Pig Latin statements and logical operators.
In the DAG, the logical operators of the script are represented as the nodes and the data
flows are represented as edges.
Optimizer
The logical plan (DAG) is passed to the logical optimizer, which carries out the logical
optimizations such as projection and pushdown.
Compiler

The compiler compiles the optimized logical plan into a series of MapReduce jobs.
Execution engine
Finally the MapReduce jobs are submitted to Hadoop in a sorted order. Finally, these
MapReduce jobs are executed on Hadoop producing the desired results.
Pig Latin Data Model
The data model of Pig Latin is fully nested and it allows complex non-atomic datatypes such
as map and tuple. Given below is the diagrammatical representation of Pig Latin’s data
model.

Atom
Any single value in Pig Latin, irrespective of their data, type is known as an Atom. It is stored
as string and can be used as string and number. int, long, float, double, chararray, and
bytearray are the atomic values of Pig. A piece of data or a simple atomic value is known as
a field.
Example − ‘raja’ or ‘30’
Tuple
A record that is formed by an ordered set of fields is known as a tuple, the fields can be of
any type. A tuple is similar to a row in a table of RDBMS.
Example − (Raja, 30)
Bag
A bag is an unordered set of tuples. In other words, a collection of tuples (non-unique) is
known as a bag. Each tuple can have any number of fields (flexible schema). A bag is
represented by ‘{}’. It is similar to a table in RDBMS, but unlike a table in RDBMS, it is not
necessary that every tuple contain the same number of fields or that the fields in the same
position (column) have the same type.

Example − {(Raja, 30), (Mohammad, 45)}

A bag can be a field in a relation; in that context, it is known as inner bag.
Example − {Raja, 30, {9848022338, raja@[Link],}}
Map
A map (or data map) is a set of key-value pairs. The key needs to be of type chararray and
should be unique. The value might be of any type. It is represented by ‘[]’

Example − [name#Raja, age#30]

Relation
A relation is a bag of tuples. The relations in Pig Latin are unordered (there is no guarantee
that tuples are processed in any particular order).

Use Case for Pig:

Pig Use Cases – Examples

Pig Use Case#1
The weblog can be processed using pig because it has a goldmine of information.
Using this information we can analyze the overall server usages and improve the
server performance.
We can create the Usage Tracking mechanism such as monitoring users, processes,
preempting security attacks on your server.
We can also analyze the frequent errors and take a corrective measure to enhance
user experience.
Pig Use Case#2
To know about the effectiveness of an advertisement is one of the important goals
for any companies.
Many companies invest millions of dollars in buying the ads space and for them and
it is critical to know how popular their advertisement both in physical and virtual
space.
Gathering advertising information from multiple sources and analysis to understand
the customer behavior and its effectiveness is one of the important goals for many
companies. This can be easily achieved by using Pig Latin language.
Pig Use Case#3
Processing the healthcare information is one of the important use cases of Pig.
Neural Network for Breast Cancer Data Built on Google App Engine is one of the
important application developed using pig and Hadoop.
Pig Use Case#4
Stock analysis using Pig Latin such as to calculate average dividend, Total trade
estimation, Group the stocks and Join them.

ETL Processing:

INTRODUCTION:

1. ETL stands for Extract, Transform, Load and it is a process used in data warehousing to
extract data from various sources, transform it into a format suitable for loading into a
data warehouse, and then load it into the warehouse. The process of ETL can be broken
down into the following three stages:
2. Extract: The first stage in the ETL process is to extract data from various sources such as
transactional systems, spreadsheets, and flat files. This step involves reading data from
the source systems and storing it in a staging area.
3. Transform: In this stage, the extracted data is transformed into a format that is suitable
for loading into the data warehouse. This may involve cleaning and validating the data,
converting data types, combining data from multiple sources, and creating new data
fields.
4. Load: After the data is transformed, it is loaded into the data warehouse. This step
involves creating the physical data structures and loading the data into the warehouse.
5. The ETL process is an iterative process that is repeated as new data is added to the
warehouse. The process is important because it ensures that the data in the data
warehouse is accurate, complete, and up-to-date. It also helps to ensure that the data is
in the format required for data mining and reporting.
Additionally, there are many different ETL tools and technologies available, such as
Informatica, Talend, DataStage, and others, that can automate and simplify the ETL process.
ETL is a process in Data Warehousing and it stands for Extract, Transform and Load. It is a
process in which an ETL tool extracts the data from various data source systems, transforms
it in the staging area, and then finally, loads it into the Data Warehouse system.
Let us understand each step of the ETL process in-depth:
1. Extraction:
The first step of the ETL process is extraction. In this step, data from various source
systems is extracted which can be in various formats like relational databases, No SQL,
XML, and flat files into the staging area. It is important to extract the data from various
source systems and store it into the staging area first and not directly into the data
warehouse because the extracted data is in various formats and can be corrupted also.
Hence loading it directly into the data warehouse may damage it and rollback will be much
more difficult. Therefore, this is one of the most important steps of ETL process.
2. Transformation:
The second step of the ETL process is transformation. In this step, a set of rules or
functions are applied on the extracted data to convert it into a single standard format. It
may involve following processes/tasks:
 Filtering – loading only certain attributes into the data warehouse.
 Cleaning – filling up the NULL values with some default values, mapping U.S.A, United
States, and America into USA, etc.
 Joining – joining multiple attributes into one.
 Splitting – splitting a single attribute into multiple attributes.
 Sorting – sorting tuples on the basis of some attribute (generally key-attribute).
3. Loading:
The third and final step of the ETL process is loading. In this step, the transformed data is
finally loaded into the data warehouse. Sometimes the data is updated by loading into the
data warehouse very frequently and sometimes it is done after longer but regular
intervals. The rate and period of loading solely depends on the requirements and varies
from system to system.
ETL process can also use the pipelining concept i.e. as soon as some data is extracted, it can
transformed and during that period some new data can be extracted. And while the
transformed data is being loaded into the data warehouse, the already extracted data can be
transformed. The block diagram of the pipelining of ETL process is shown below:
ETL Tools: Most commonly used ETL tools are Hevo, Sybase, Oracle Warehouse builder,
CloverETL, and MarkLogic.
Data Warehouses: Most commonly used Data Warehouses are Snowflake, Redshift,
BigQuery, and Firebolt.

ADVANTAGES OR DISADVANTAGES:

Advantages of ETL process in data warehousing:

1. Improved data quality: ETL process ensures that the data in the data warehouse is
accurate, complete, and up-to-date.
2. Better data integration: ETL process helps to integrate data from multiple sources and
systems, making it more accessible and usable.
3. Increased data security: ETL process can help to improve data security by controlling
access to the data warehouse and ensuring that only authorized users can access the data.
4. Improved scalability: ETL process can help to improve scalability by providing a way to
manage and analyze large amounts of data.
5. Increased automation: ETL tools and technologies can automate and simplify the ETL
process, reducing the time and effort required to load and update data in the warehouse.

Disadvantages of ETL process in data warehousing:

1. High cost: ETL process can be expensive to implement and maintain, especially for
organizations with limited resources.
2. Complexity: ETL process can be complex and difficult to implement, especially for
organizations that lack the necessary expertise or resources.
3. Limited flexibility: ETL process can be limited in terms of flexibility, as it may not be able
to handle unstructured data or real-time data streams.
4. Limited scalability: ETL process can be limited in terms of scalability, as it may not be able
to handle very large amounts of data.
5. Data privacy concerns: ETL process can raise concerns about data privacy, as large
amounts of data are collected, stored, and analyzed.
Overall, ETL process is an essential process in data warehousing that helps to ensure that the
data in the data warehouse is accurate, complete, and up-to-date. However, it also comes
with its own set of challenges and limitations, and organizations need to carefully consider
the costs and benefits before implementing them.

Data types , Oerators in Pig

Pig data type can be classified into two categories, and they are –

 Primitive
 Complex

Primitive Data type

 It is also named as Simple Data type. The primitive data types are as follows int −
Signed 32-bit integer and similar to Integer in Java.
 Long − It is a fully signed 64-bit number similar to Long in Java.
 Float − It is a signed 32-bit floating surface that appears to be similar to Java's float.
 Double − A floating-point 63-bit and similar to Double in Java.
 Char array − A list of characters in the Unicode format, UTF-8. This is compatible with
the Java character unit item.
 byte array − The byte data type represents bytes by default. When the data file type
is not specified, the default value is byte array.
 Boolean − A value that is either true or false.

Complex Data type

Complex data types consist of a bit of logical and complicated data type. The following are
the complex data type −

Data Definition Code Example

Types

Tuple A set of ordered fields. The tuple is (field[,fields....]) (1,2)

written with braces.
 Bag A group of tuples is called a bag. {tuple,[,tuple...]} {(1,2), (3,4)}
Represented by folded weights or
curly braces.

Map A set of key-value pairs. The map is [Key # Value] ['keyname'#'valuename']

represented by square brackets.

 Key − An element of finding an element, the key must be unique and must be
charrarray.
 Value − Any data can be stored in a value, and each key has particular data related to
it. The map is built using a bracket and hash between key and values. Cas to separate
pairs of over one key value. Here # is used to distinguish key and value.
 Null Values − Valuable value is missing or unknown, and any data may apply. The pig
handles an empty value similar to SQL. Pig detects blank values when data is missing,
or an error occurs during data processing. Also, null can be used as a value proposition
of your choice.

Note − Pig allows for the reproduction of complex data structures. For example, you can easily
place a tuple on the interior side of a tuple, bag, and map.

Pig Latin – Data types

Given below table describes the Pig Latin data types.
Primitive Data types

S.N. Data Type Description & Example

Represents a signed 32-bit integer.

1 int
Example : 8

Represents a signed 64-bit integer.

2 long
Example : 5L

Represents a signed 32-bit floating point.

3 float
Example : 5.5F

Represents a 64-bit floating point.

4 double
Example : 10.5

Represents a character array (string) in Unicode UTF-8 format.

5 chararray
Example : ‘tutorials point’

6 Bytearray Represents a Byte array (blob).

Represents a Boolean value.

7 Boolean
Example : true/ false.
 Represents a date-time.
8 Datetime
Example : 1970-01-01T00:00:00.000+00:00

Represents a Java BigInteger.

9 Biginteger
Example : 60708090709

Represents a Java BigDecimal

10 Bigdecimal
Example : 185.98376256272893883

Complex Types

A tuple is an ordered set of fields.

11 Tuple
Example : (raja, 30)

A bag is a collection of tuples.

12 Bag
Example : {(raju,30),(Mohhammad,45)}

A Map is a set of key-value pairs.

13 Map
Example : [ ‘name’#’Raju’, ‘age’#30]

Null Values
Values for all the above data types can be NULL. Apache Pig treats null
values in a similar way as SQL does.

A null can be an unknown value or a non-existent value. It is used as a

placeholder for optional values. These nulls can occur naturally or can be
the result of an operation.

Pig Latin – Arithmetic Operators

The following table describes the arithmetic operators of Pig Latin.
Suppose a = 10 and b = 20.

Operator Description Example

+ Addition − Adds values on either side of the operator a + b will give 30

Subtraction − Subtracts right hand operand from left

− a − b will give −10
hand operand

Multiplication − Multiplies values on either side of the

* a * b will give 200
operator

Division − Divides left hand operand by right hand

/ b / a will give 2
operand
 Modulus − Divides left hand operand by right hand
% b % a will give 0
operand and returns remainder

b = (a == 1)? 20: 30;

Bincond − Evaluates the Boolean operators. It has three
if a = 1 the value of
operands as shown below.
?: b is 20.
variable x = (expression) ? value1 if true : value2 if
if a!=1 the value of
false.
b is 30.

CASE f2 % 2
CASE
WHEN 0 THEN
WHEN
Case − The case operator is equivalent to nested bincond 'even'
THEN
operator. WHEN 1 THEN
ELSE
'odd'
END
END

Pig Latin – Comparison Operators

The following table describes the comparison operators of Pig Latin.

Operator Description Example

Equal − Checks if the values of two operands are equal or not; (a = b) is not
==
if yes, then the condition becomes true. true

Not Equal − Checks if the values of two operands are equal or

!= (a != b) is true.
not. If the values are not equal, then condition becomes true.

Greater than − Checks if the value of the left operand is

(a > b) is not
> greater than the value of the right operand. If yes, then the
true.
condition becomes true.

Less than − Checks if the value of the left operand is less than
< the value of the right operand. If yes, then the condition (a < b) is true.
becomes true.

Greater than or equal to − Checks if the value of the left

(a >= b) is not
>= operand is greater than or equal to the value of the right operand.
true.
If yes, then the condition becomes true.

Less than or equal to − Checks if the value of the left operand

<= is less than or equal to the value of the right operand. If yes, then (a <= b) is true.
the condition becomes true.

Pattern matching − Checks whether the string in the left-hand f1 matches

matches
side matches with the constant in the right-hand side. '.*tutorial.*'

Pig Latin – Type Construction Operators

The following table describes the Type construction operators of Pig Latin.

Operator Description Example

Tuple constructor operator − This operator is used

() (Raju, 30)
to construct a tuple.

Bag constructor operator − This operator is used to {(Raju, 30),

{}
construct a bag. (Mohammad, 45)}

Map constructor operator − This operator is used

[] [name#Raja, age#30]
to construct a tuple.

Pig Latin – Relational Operations

The following table describes the relational operators of Pig Latin.

Operator Description

Loading and Storing

To Load the data from the file system (local/HDFS) into a

LOAD
relation.

STORE To save a relation to the file system (local/HDFS).

Filtering

FILTER To remove unwanted rows from a relation.

DISTINCT To remove duplicate rows from a relation.

FOREACH, GENERATE To generate data transformations based on columns of data.

STREAM To transform a relation using an external program.

Grouping and Joining

JOIN To join two or more relations.

COGROUP To group the data in two or more relations.

GROUP To group the data in a single relation.

CROSS To create the cross product of two or more relations.

Sorting

To arrange a relation in a sorted order based on one or more fields

ORDER
(ascending or descending).
 LIMIT To get a limited number of tuples from a relation.

Combining and Splitting

UNION To combine two or more relations into a single relation.

SPLIT To split a single relation into two or more relations.

Diagnostic Operators

DUMP To print the contents of a relation on the console.

DESCRIBE To describe the schema of a relation.

To view the logical, physical, or MapReduce execution plans to

EXPLAIN
compute a relation.

ILLUSTRATE To view the step-by-step execution of a series of statements.

Running Pig:

Apache Pig Run Modes

Apache Pig executes in two modes: Local Mode and MapReduce Mode.

Local Mode
o It executes in a single JVM and is used for development experimenting and
prototyping.
 o Here, files are installed and run using localhost.
o The local mode works on a local file system. The input and output data stored
in the local file system.

The command for local mode grunt shell:

1. $ pig-x local

MapReduce Mode
o The MapReduce mode is also known as Hadoop Mode.
o It is the default mode.
o In this Pig renders Pig Latin into MapReduce jobs and executes them on the
cluster.
o It can be executed against semi-distributed or fully distributed Hadoop
installation.
o Here, the input and output data are present on HDFS.

The command for Map reduce mode:

1. $ pig
Or,

1. $ pig -x mapreduce

Ways to execute Pig Program

These are the following ways of executing a Pig program on local and MapReduce
mode: -

o Interactive Mode - In this mode, the Pig is executed in the Grunt shell. To
invoke Grunt shell, run the pig command. Once the Grunt mode executes, we
can provide Pig Latin statements and command interactively at the command
line.
o Batch Mode - In this mode, we can run a script file having a .pig extension.
These files contain Pig Latin commands.
 o Embedded Mode - In this mode, we can define our own functions. These
functions can be called as UDF (User Defined Functions). Here, we use
programming languages like Java and Python.

Execution model of Pig:

Apache Pig executes in two modes: Local Mode and MapReduce Mode.

Note: Pig Engine has two type of execution environment i.e. a local execution environment in
a single JVM (used when dataset is small in size)and distributed execution environment in a
Hadoop Cluster.

Apache Pig Execution Methods

A user can execute Apache Pig Latin scripts in the ways as mentioned below.

In this mode, a user can interactively run Apache Pig using the Grunt shell. Users
can submit commands and get a result there only.

Let us see the below example. We are running the below statements in interactive
mode and getting output there only.

Command:
grunt> employee = LOAD 'hdfs://localhost:9000/pigdata/[Link]' USING PigSto
rage(',')
as (empid:int,empname:chararray,company:chararray);

grunt> dump employee;

2. Batch Mode (Script)

In this mode, a user can run Apache Pig in batch mode by creating a Pig Latin script
file and running it from local or MapReduce mode.

Let us see the below example. We have created a script file with the name
“emp_script.pig” and placed it at the HDFS location, now we are calling that file
using the batch mode command.

Command:
$pig -x mapreduce hdfs:///pigdata/emp_script.pig

Pig Commands
Given below in the table are some frequently used Pig Commands.

Command Function

load Reads data from the system

Store Writes data to file system

Applies expressions to each

foreach record and outputs one or
more records

Applies predicate and

filter removes records that do not
return true

Collects records with the

Group/cogroup same key from one or more
inputs

Joins two or more inputs

join
based on a key

Sorts records based on a

order
key

distinct Removes duplicate records

union Merges data sets

Splits data into two or more

split sets based on filter
conditions
 Sends all records through a
stream
user-provided binary

dump Writes output to stdout

limit Limits the number of reco

Pig built-in functions:

Type Examples
EVAL functions AVG, COUNT, COUNT_STAR, SUM, TOKENIZE, MAX,
MIN, SIZE etc
LOAD or STORE Pigstorage(), Textloader, HbaseStorage, JsonLoader, JsonStorage
functions etc
Math functions ABS, COS, SIN, TAN, CEIL, FLOOR, ROUND, RANDOM etc
String functions TRIM, RTRIM, SUBSTRING, LOWER, UPPER etc
DateTime function GetDay, GetHour, GetYear, ToUnixTime, ToString etc

Eval functions:

 AVG(col): computes the average of the numerical values in a single column of a bag
 CONCAT(string expression1, string expression2) : Concatenates two expressions of
identical type
 COUNT(DataBag bag): Computes the number of elements in a bag excluding null
values
 COUNT STAR (DataBag bag1, DataBag bag 2): Computes the number of elements in a
bag including null values.
 DIFF(DataBag bag1, DataBag bag2): It is used to compare two bags, if any element in
one bag is not present in the other bag are returned in a bag
  IsEmpty(DataBag bag), IsEmpty(Map map): It is used to check if the bag or map is
empty
 Max(col): Computes the maximum of the numeric values or character in a single column
bag
 MIN(col): Computes the minimum of the numeric values or character in a single column
bag
 DEFINE pluck pluckTuple(expression1): It allows the user to specify a string prefix, and
filters the columns which begins with that prefix
 SIZE(expression): Computes the number of elements based on any pig data
 SUBSTRACT(DataBag bag1, DataBag bag2): It returns the bag which does not contain
bag1 element in bag2
 SUM: Computes the sum of the values in a single-column bag
 TOKENIZE(String expression[,‘field delimiter’): It splits the string and outputs a bag of
words

Load or Store Functions:

 PigStorage ():

Syntax: PigStorage(field_delimiter)
A = LOAD ‘Employee’ USING PigStorage(‘\t’) AS (name: chararray, age:int, gpa:
float);
Loads and stores data as structured text file

 TextLoader():

Syntax: A = LOAD ‘data’ USING TextLoader();

Loads unstructured data in UTF 8 format

 BinStorage():

Syntax: A = LOAD ‘data’ USING BinStorage();

Loads and stores data in machine readable format

 Handling compression:
It loads and stores compressed data in Pig

 JsonLoader, JsonStorage:

Syntax: A = load ‘[Link]’ using JsonLoader();

It loads and stores JSON data

 Pig dump:

Syntax: STORE X INTO ‘output’ USING PigDump ();

Stores data in UTF 8 format

Math functions:

 ABS:

Syntax: ABS(expression)
It returns the absolute value of an expression

 COS:

Syntax: COS(expression)
It Returns the trigonometric cosine of an expression.

 SIN:

Syntax: SIN (expression)

It returns the sine of an expression.

 CEIL:

Syntax: CEIL(expression)
It is used to return the value of an expression rounded up to the nearest integer

 TAN:
Syntax: TAN(expression)
It is used to return the trigonometric tangent of an angle.

 ROUND:

Syntax: ROUND(expression)
It returns the value of an expression rounded to an integer (if the result type is float)
or long (if the result type is double)

 RANDOM:

Synatx: RANDOM ()
It returns a pseudo random number (type double) greater than or equal to 0.0 and
less than 1.0

 Floor:

Syntax: FLOOR(expression)
Returns the value of an expression rounded down to the nearest integer.

 CBRT:

Synatx: CBRT(expression)
It returns the cube root of an expression

 EXP:

Syntax: EXP(expression)
Returns Euler’s number e raised to the power of x.

String Functions:

 INDEXOF:

Syntax: INDEXOF (string, ‘character’, startIndex)

It returns an index of the first occurrence of a character in a string
 LAST_INDEX:

Syntax: LAST_INDEX_OF (expression)

It returns an index of the last occurrence of a character in a string

 TRIM:

Syntax: TRIM(expression)
It returns a copy of the string with leading and trailing whitespaces removed

 SUBSTRING:

Syntax: SUBSTRING (string, startIndex, stopIndex)

It will return a substring from a given string

 UCFIRST:

Syntax: UCFIRST(expression)
It will return a string with the first character changed to the upper case

 LOWER:

Syntax: LOWER(expression)
Converts all characters in a string to lowercase

 UPPER:

Synatx: UPPER(expression)
Converts all characters in a string to the uppercase

uple, Bag and Map functions:

Function Syntax Description
TOTUPLE TOTUPLE(expression [, expression …]) It is used to convert one or more
expressions to the type Tuple

TOBAG TOBAG(expression [, expression …]) It is used to convert one or more expression

to the individual tuple, which is then placed
in a bag

TOMAP TOMAP(key-expression, value- It is used to convert key/value expression

expression [, key-expression, value- pairs to a Map
expression …])

TOP TOP(topN,column,relation) Returns a top-n tuples from a bag of tuples

*********End**********