18 PigLatin

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

Pig Latin is a high-level dataflow language used for analyzing large data sets. It was specifically designed to handle the massive volumes of data processed by Hadoop's MapReduce framework, abstracting away the complex programming required by raw MapReduce.

Dataflow Language Explained

Pig Latin is a dataflow language because each statement defines a processing step that takes a relation (data set) as input and produces a new, separate relation as output.

Relations/Data Sets: In Pig Latin, data is stored in relations, which are analogous to tables in a relational database.

Sequential Transformation: Processing is a series of transformations (like load, filter, foreach). The output of one transformation becomes the input for the next. This creates a logical flow of data through a series of operations until the final result is achieved.

No Mutability: The input relation is never modified in place. Instead, a new relation is created, preserving the integrity of the previous step.

Exampledemonstrates this dataflow principle clearly:

Statement Operation Input Relation Output Relation3

A = load 'NYSE_dividends' ... Load data from a file. (File: NYSE_dividends) A (All NYSE dividends data)

B = filter A by dividends > 0; Filter the data in relation A. A B (Only dividend records >0)

C = foreach B generate UPPER(symbol); Transform the data in relation B. B C (A list of uppercase stock symbols from B)

Preliminary Matters

Pig Latin is a dataflow language

Each processing step results in a new data set, or relation


A = load 'NYSE_dividends' (exchange, symbol, date, dividends);
B = filter A by dividends > 0;
C = foreach B generate UPPER(symbol);

Case Sensitivity
- A = load 'foo'; is not equivalent to a = load 'foo';

Comments


A = load 'foo'; -- this is a single-line comment
/*
  This is a multiline comment.
*/

Pig Latin

The LOAD command is the foundational step in any Pig Latin script. Its purpose is to read data from a persistent storage system (typically the Hadoop Distributed File System, or HDFS) and bring it into Pig as a relation (a data set) for processing.

Default Behavior

When you use the simple LOAD statement, Pig uses its default assumptions:

divs = load '/data/examples/file';

Location: Pig assumes the file path is relative to the root of the HDFS.

The path you provided, /data/examples/file, resolves to a full HDFS path like hdfs://[host-name]:[port-name]/data/examples/file.

Default Loader: By default, Pig uses the PigStorage function.

PigStorage assumes the fields (columns) within your data file are separated by the tab character (\t).

It treats all data fields as bytearrays (untyped collections of bytes) by default, meaning no specific data type validation or conversion is performed initially.

Specifying a Custom Loader (USING)

You can override the default PigStorage by explicitly specifying a different load function using the USING keyword. This is essential when dealing with data that is not tab-delimited or is stored in a specialized format (like a database or an advanced file type).

divs = load '/data/examples/file' using HBaseStorage();

Load Function: HBaseStorage is a built-in function that tells Pig how to connect to and read data from an HBase table, not a standard file.

Purpose: Using a custom loader allows Pig to handle various data formats and sources, such as:

Comma-separated values (CSV files, often by passing a delimiter to PigStorage).

Specific database systems (e.g., HBase, JDBC).

Optimized file formats (e.g., ParquetStorage, RCStorage).

Defining the Schema (AS)

While Pig can process data without knowing the specific data types, it's highly recommended to specify a schema using the AS keyword. This defines the name and data type for each field as the data is loaded.

divs = load '/data/examples/file' as (exchange: int, symbol: chararray);

Schema Definition: The data is loaded and immediately converted into a relation with two fields:

exchange (an integer type: int).

symbol (a character array type: chararray, which is Pig's string type).

Benefits:Error Detection: It enables earlier detection of data type mismatch errors.

Efficiency: It allows Pig's optimizer to generate more efficient physical execution plans.

Clarity: It makes the script much easier to read and understand.

Input in Pig Latin

load
By default, load looks for your data on HDFS in a tab-delimited file using the default load function PigStorage
A statement above looks for a file called 'file' in a folder
hdfs://[host-name]:[port- name]/data/examples

It is possible to determine a particular load function


divs = load '/data/examples/file' using HBaseStorage(,);

It is also possible to specify the schema when loading the file


divs = load '/data/examples/file' as (exchange: int, symbol: chararray);

The STORE command is used to persistently save the results of a Pig Latin job (a relation) to a file system, typically HDFS

store processed into '/data/examples/processed';

Default Behavior: By default, STORE uses PigStorage, which writes the data to the specified location using tab-delimited fields.

Custom Storage: Just like LOAD, you can specify a custom storage function using the USING keyword, such as HBaseStorage() or another format like ParquetStorage(). This allows you to write the output to a database, or save it in a specific, efficient file format.

Multiple Output Files (Why?)

When you STORE a large relation, the data is typically saved across multiple "part" files (e.g., part-r-00000, part-r-00001, etc.) rather than a single file. This happens because:

MapReduce Execution: Pig translates your script into a series of MapReduce jobs.

Parallelism: The Reduce phase of a MapReduce job runs in parallel across multiple machines (or virtual containers). Each Reducer processes a portion of the total data.

Independent Output: Each Reducer writes its own output file independently.

Efficiency: This parallel writing mechanism is fundamental to achieving high performance on large datasets. Attempting to force all output into a single file would require a single bottleneck step, defeating the purpose of a distributed system like Hadoop.

The directory specified (/data/examples/processed in your example) acts as a folder containing all these part files.

The DUMP command is used to display the contents of a Pig Latin relation directly to the console (screen).

dump processed;

For Debugging Only: DUMP is primarily intended for debugging and testing with small datasets.

Execution Trigger: Unlike STORE, which only saves the output, DUMP forces the execution of the entire Pig script up to that point. If you have a massive dataset, using DUMP can be very slow and consume significant resources, as it has to collect all the distributed output and print it sequentially to the screen.

Single Output: The final result is consolidated and displayed in a single, readable format on your terminal.

Output in Pig Latin

store
Pig stores your data on HDFS in a tab-delimited file using PigStorage
As in load, you can also specify a store function, e.g. HbaseStorage()
In processed, there are usually multiple part files rather than a single file (why ?)
dump
Occasionally you will want to see it on the screen, this is done by the dump action

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

Pig Latin, being a dataflow language, supports standard mathematical and logical operations within its transformation statements, but its true power lies in its specialized relational operators.

1. Arithmetic Operations

These are used for calculating new values based on existing fields.

Operators: +, -, *, /

Use Case: They are most commonly used within the FOREACH...GENERATE statement to create new fields (projections).

Example: GENERATE price * quantity AS total_sales;

2. Boolean Operations (Logical)

These are used to combine or negate logical conditions and result in a true or false value.

Operators: AND, OR, NOT

Use Case: They are primarily used in the FILTER statement to select records based on complex criteria.

Example: FILTER data BY (state == 'CA' AND sales > 100);

3. Relational Operations (Comparison)

These are used to compare values and result in a true or false value.

Operators: ==, !=, <, >, <=, >=, MATCHES (for regular expression pattern matching).

Use Case: Used in both FILTER statements (for selecting records) and sometimes in JOIN or GROUP conditions, although joins typically use field names directly.

Example: FILTER records BY date >= '2026-01-01';

The Power of Relational Operators

The term "Relational Operations" in the context of Pig Latin refers not just to the comparison operators (like ==), but more broadly to the specialized Pig Latin commands that manipulate entire relations (datasets). These are the statements that embody the "horsepower" and are directly translatable to complex, distributed MapReduce jobs.

These commands include:

Pig Latin Command Relational Operation Purpose

FILTER Filtering/Selection Selects a subset of records based on a condition (uses comparison and boolean ops).

FOREACH...GENERATE Projection/Transformation Selects fields and performs column-level transformations (uses arithmetic ops).

GROUP Grouping Collects records with the same key into a single relation for aggregation.

JOIN Joining Combines two or more relations based on common fields.

ORDER BY Sorting Sorts the records in a relation.

DISTINCT Duplication Removal Removes duplicate records from a relation.

Arithmetic, Boolean and relational Operations

Pig Latin supports the following basic operations
- arithmetic operations: +, -, *, /
- Boolean operations: and, or, not
- relational operations: ==, !=, <,>, <=, >=, matches (pattern matching)
Relational operations are the main tools Pig Latin provides to operate on your data
They are the horsepower of Pig Latin
They allow you to transform the operations such as sorting, grouping, joining, projecting, and filtering

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

That's a good list of the categories of Built-In Functions (UDFs - User-Defined Functions) available in Pig Latin. These functions allow you to perform transformations and calculations on the data fields within a relation.

They are primarily used within the FOREACH...GENERATE statement to create new, modified, or aggregated fields.

Mathematical Functions

These functions operate on numeric fields and are used for standard calculations and aggregations.

Aggregation: Functions like AVG, COUNT, MAX, MIN, and SUM are used to calculate summary statistics, typically after a GROUP operation.

Example: Calculating the average dividend amount: AVG(B.dividends)

Numeric Calculation: Functions like ABS (absolute value), CEIL (ceiling), EXP (exponent), FLOOR (floor), LOG (logarithm), RANDOM (random number), and ROUND (round to nearest integer) perform standard arithmetic operations on single values.

Example: GENERATE ROUND(price);

String Functions

These functions manipulate and analyze textual data stored in fields (which Pig calls chararray).

Case Conversion: LOWER and UPPER change the case of a string.

Matching/Extraction: STARTWITH and ENDWITH check if a string begins or ends with a specified substring. REGEX_EXTRACT allows you to pull specific parts of a string using regular expressions.

Tokenization: TOKENIZE breaks a string into smaller pieces (tokens) based on a specified delimiter, often used to split a line of text into words.

Example: GENERATE UPPER(symbol); (as seen in your initial example)

Date/Time Functions

These functions are used to manage, compare, and extract components from date and time values.

Creation/Conversion: CurrentTime returns the system's current time. ToDate converts a string representation into a date object that Pig can use for calculations.

Extraction: GetDay, GetHour, GetMinute, and similar functions extract specific components from a date/time field.

Comparison: DaysBetween calculates the number of days between two date fields.

Example: GENERATE DaysBetween(startDate, endDate);

These functions significantly enhance Pig's capability, allowing complex data cleaning, transformation, and analysis without needing to write custom Java code for every common operation.

Built-In Functions

Mathematical functions
- AVG, COUNT, MAX, MIN, SIZE, SUM, ABS, CEIL, EXP, FLOOR,LOG, RANDOM, ROUND, and others
String functions
- STARTWITH, ENDWITH, LOWER, UPPER, REGEX_EXTRACT, TOKENIZE, and others
Date/time functions
- CurrentTime, DaysBetween, GetDay, GetHour, GetMinute, ToDate, and others

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

The FOREACH...GENERATE statement is one of the most fundamental and frequently used operations in Pig Latin. It is the primary tool for projection (selecting columns) and transformation (modifying data) on a per-record basis.

1. Function: Projecting and Transforming

The command takes an input relation and applies a set of expressions (including simple field selection, functions, and calculations) to every single record (row) in that relation, resulting in a new output relation.

Projection: Selecting specific fields to include in the new relation.

Transformation: Applying functions, arithmetic, or logical operations to create new, modified, or derived fields.

2. Basic Projection Example

The first example shows simple projection, selecting only a subset of fields from the input relation A:

A = load 'input' as (user:chararray, id:long, address:chararray, phone:chararray, preferences:map[]);

B = foreach A generate user, id;

Input (A): Contains five fields (user, id, address, phone, preferences).

Operation: For every record in A, select the values for the user and id fields.

Output (B): A new relation containing only two fields: user and id.

3. Transformation and Field Referencing

The second example demonstrates using expressions to create new, calculated fields. You can reference fields either by name or by their position (index):

prices = load 'NYSE_daily' as (exchange, symbol, date, open, high, low, close, volume, adj_close);

gain = foreach prices generate close - open;

gain2 = foreach prices generate $6 - $3;

Field Name Reference (close - open):

gain is a new relation where each record is the result of subtracting the open price from the close price, which yields the daily price change. The resulting field will be automatically named $$1 unless you explicitly give it a name using AS (e.g., generate close - open AS daily_gain).

Positional Reference ($6 - $3):

Pig Latin allows you to reference fields by their 0-based index. In the prices schema:

$3 refers to the 4th field, open.

$6 refers to the 7th field, close.

The result in gain2 is identical to gain, showcasing two ways to achieve the same transformation. Using names is strongly recommended for script readability and maintainability.

The FOREACH...GENERATE statement is essentially the mechanism that executes all the Arithmetic, String, and Built-In Functions (like UPPER(), ROUND(), etc.) across the dataset.

Foreach

foreach takes a set of expressions and applies them to every record in the data pipeline, it is the projecting and transforming operation


A = load 'input' as (user:chararray, id:long, address:chararray, phone:chararray,
		     preferences:map[]);
B = foreach A generate user, id;

It is convenient to use expressions in foreach statement


prices = load 'NYSE_daily' as (exchange, symbol, date, open, high, low, close,
                               volume, adj_close);
gain = foreach prices generate close - open;
gain2 = foreach prices generate $6 - $3;

The FOREACH...GENERATE statement is Pig Latin's primary tool for record-by-record transformations, and it offers convenient shorthand methods for selecting ranges of fields and extracting data from complex data types like Bags and Maps.

1. Range Selection (Shorthand Syntax)

Instead of listing every field, you can use the double-dot (..) operator to easily select a continuous range of fields from the input relation.

Syntax Example Fields Selected Explanation

..Field beginning = foreach prices generate ..open; exchange, symbol, date, open Selects all fields up to and including the specified field (open).

Field..Field middle = foreach prices generate open..close; open, high, low, close Selects all fields starting with the first field (open) and ending with the second (close).

Field.. end = foreach prices generate volume..; volume, adj_close Selects all fields starting with the specified field (volume) to the end of the relation's schema.

* all_in_one = foreach prices generate *; All fields in prices Selects all fields in the relation. The output is typically a tuple containing all fields of the input record.

2. Extracting Data from Complex Types

Pig Latin supports complex, nested data types, and FOREACH...GENERATE provides special syntax for accessing the data within them.

A. Maps (# Operator)

A Map is a collection of key-value pairs (like a Python dictionary or Java map). You use the pound symbol (#) to retrieve the value associated with a specific key.

bball_avg = foreach bball generate bat#'batting_average';

bball_avg extracts the value associated with the key 'batting_average' from the bat map field for every record.

B. Bags (. Operator)

A Bag is a collection of tuples (like a list of rows/records). You use the dot symbol (.) followed by the field name to extract a field from each tuple within the bag.

bball_p = foreach bball generate position.p;

The position field is a bag of tuples, where each tuple t has a field p (representing a position, e.g., 'Pitcher', 'Catcher').

bball_p generates a new bag containing only the p fields from the tuples within the original position bag for that record. This is common for un-nesting specific data within a bag structure.

Foreach

Some other expressions


prices = load 'NYSE_daily' as (exchange, symbol, date, open, high, low,
                               close, volume, adj_close);
beginning = foreach prices generate ..open;
-- produces exchange, symbol, date, open

middle = foreach prices generate open..close;
-- produces open, high, low, close

end = foreach prices generate volume..;
-- produces volume, adj_close

all_in_one = foreach prices generate *;
-- produces a tuple of all fields

To extract data from complex types


bball = load 'baseball' as (name:chararray, team:chararray,
                            position:bag{t:(p:chararray)}, bat:map[]);
bball_avg = foreach bball generate bat#'batting_average';
bball_p = foreach bball generate position.p;

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

The FILTER statement is a fundamental Pig Latin operation that performs the function of selection—it allows you to choose which records (rows) from a relation should be kept and passed along to the next step in your data pipeline.

How FILTER Works

Input Relation: The FILTER operation takes an existing relation as its input (e.g., divs in your example).

Predicate: It requires a predicate—a logical expression that evaluates to either TRUE or FALSE for every record.

Output Relation:

If the predicate evaluates to TRUE for a given record, that record is retained in the new output relation.

If the predicate evaluates to FALSE, the record is discarded (not included in the output).

Using the Predicate

A predicate can be any expression using relational and Boolean operators:

Relational Operators: == (equal), != (not equal), < (less than), > (greater than), etc.

Boolean Operators: AND, OR, NOT to combine multiple conditions.

startswithcm = filter divs by symbol matches 'CM.*';

Input Relation: divsPredicate: symbol matches 'CM.*'

This predicate checks if the value in the symbol field matches the regular expression pattern 'CM.*'. The pattern means "starts with 'CM' followed by any characters (.*)".

Output Relation: startswithcm will only contain dividend records where the stock symbol begins with "CM" (e.g., CME, CMA, CMG). All other records are discarded.

This operation is highly efficient in large-scale data processing because it reduces the size of the data set early on, saving time and resources for subsequent transformations.

Filter

filter statement allows you to select which records will be retained in your data pipeline
A filter contains a predicate
If a predicate evaluates to true for a given record, that record will be passed down the pipeline

Otherwise, it will not


divs = load 'NYSE_dividends' as (exchange:chararray, symbol:chararray,
                                 date:chararray, dividends:float);
startswithcm = filter divs by symbol matches 'CM.*';
-- Only records matching the pattern 'CM.*' are retained

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

The GROUP statement is a critical operation in Pig Latin used to prepare data for aggregation. It works by collecting all records that share the same value in a specified field (the key) into a single, nested structure called a Bag.

How GROUP Works

Key Specification: You specify the relation you want to group and the field (or fields) to use as the grouping key.

grpd = group daily by stock_id;

Output Relation Structure: The resulting relation, grpd, is structured differently than the input. It consists of two main fields (columns):

group: This field contains the actual value of the grouping key (e.g., a specific stock_id).

Bag of Records: The second field is a Bag (a collection of tuples) that contains all the original records from the input relation (daily) that share that specific key value.

DESCRIBE Output Explained: The DESCRIBE output clearly shows this structure:

grpd: {group: (stock_id: int), daily: {exchange: chararray,stock_id: int}}

group: The key is stock_id (an integer).

daily: This is the Bag. It is named after the input relation and contains all the original fields of the input records (exchange and stock_id).

Pig Latin GROUP vs. SQL GROUP BY

The most important distinction you noted is the difference between Pig's GROUP and SQL's GROUP BY:

Feature Pig Latin GROUP SQL GROUP BY

Output Creates a Bag of records. Creates a single summary row for each group.

Aggregation Decoupled. The Bag must be explicitly passed to an aggregate function (like COUNT, SUM, AVG) in a separate FOREACH step. Coupled. Aggregation is mandatory and happens within the GROUP BY clause.

Purpose Collects and nests the data. Aggregates and summarizes the data.

In Pig Latin, GROUP is just the first step in aggregation—it collects the data. The second step is a subsequent FOREACH...GENERATE where you apply an aggregate function to the resulting Bag to calculate the final summary.

Example Aggregation in Pig:

-- 1. GROUP (collects data into a Bag)

grpd = group daily by stock_id;

-- 2. FOREACH (aggregates the Bag)

result = foreach grpd generate group, COUNT(daily);

Group

group statement collects together records with the same key
It is sharing its syntax with SQL, but the grouping operator in Pig Latin is fundamentally different than the one in SQL
In SQL GROUP BY clause creates a group that must feed directly into one or more aggregate functions
In Pig Latin there is no direct connection between group and aggregate functions

Instead, group does exactly what it says, i.e. it collects all records with the same value for the provided key together into a bag


daily = load 'NYSE_daily' as (exchange:chararray, stock_id:int);
grpd = group daily by stock_id;
describe grpd;
grpd: {group: (stock_id: int), daily: {exchange: chararray,stock_id: int}}

The GROUP statement in Pig Latin is highly flexible, allowing for grouping by multiple fields and even grouping the entire dataset into a single collection.

1. Grouping by Multiple Keys

You can specify more than one field to act as the grouping key. The GROUP operation will collect records only if they have identical values for all the specified keys.

Code snippet

grpd = group daily by (exchange, stock);

Compound Key: The combination of exchange and stock forms the compound key. A group is created for every unique pair of (exchange, stock).

Output Structure: The resulting relation (grpd) still has two main fields:

group: This field is now a Tuple containing the values for each of the keys.

Bag of Records: The second field is the Bag (daily), holding all the original records that share that compound key.

The DESCRIBE output confirms this:

grpd: {group: (exchange: bytearray,stock: bytearray),daily: {exchange: bytearray, ...}}

The group field is a tuple (exchange, stock).

In the aggregation step:

avg = foreach grpd generate group, AVG(daily.dividends);

group: This refers to the entire key tuple (exchange, stock).

AVG(daily.dividends): Calculates the average dividend across the Bag of records (daily) for that specific (exchange, stock) pair.

2. Grouping the Entire Relation (GROUP ALL)

The special keyword ALL instructs Pig to treat the entire input relation as a single group. This is typically used when you want to calculate a global aggregate (like a total count, maximum value, or overall average) across the entire dataset.

grpd = group daily all;

Output: The relation grpd will contain only one record.

Single Group: The group field for this single record will be a constant value (all or an empty tuple), and the Bag field (daily) will contain every single record from the input relation.

This is the most efficient way to calculate statistics that apply to the dataset as a whole:

Example Global Count:

grpd = group daily all;

total_records = foreach grpd generate COUNT(daily); -- total_records has one row: the count of all original records

Group

Group supports multiple keys

The resulting records still have two fields and group field is a tuple with a field for each key


daily = load 'NYSE_daily' as (exchange, stock, date, dividends);
grpd = group daily by (exchange, stock);
avg = foreach grpd generate group, AVG(daily.dividends);
describe grpd;
grpd: {group: (exchange: bytearray,stock: bytearray),daily: {exchange: bytearray,
                                                             stock: bytearray,
                                                             date: bytearray,
                                                             dividends: bytearray}}

It is also possible to use all to group together all of the records in a pipeline


daily = load 'NYSE_daily' as (exchange, stock);
grpd = group daily all;
--grpd will have only one row containing all records

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

The ORDER BY statement in Pig Latin is used to sort the records (rows) of a relation based on the values in one or more specified fields. This process results in a total order of the output data, meaning the final relation is completely sorted.

How ORDER BY Works

Sorting Key: You specify the field(s) you want to sort by. You can list multiple fields, and the sorting will proceed sequentially based on that list.

Order Direction: By default, Pig sorts in ascending order (ASC). You can explicitly specify descending order using the DESC keyword.

Data Type Sorting:Numeric values (int, long, float, double) are sorted numerically (e.g., 1, 2, 10, 20).

Text/Character values (chararray) are sorted lexically (alphabetically, A-Z, often case-sensitive).

Multiple Sort Keys Example

daily = load 'NYSE_daily' as (exchange:chararray, symbol:chararray, date:chararray, open:float);

byclose = order daily by close desc, open;

Primary Sort (close desc): The entire relation is first sorted by the close field in descending order (highest price first).

Secondary Sort (open): For any records that have the exact same close value (a "tie"), the secondary key is used. These tied records are then sorted by the open field in the default ascending order (lowest price first).

The output relation, byclose, is a fully sorted list based on this two-level criterion.

Importance of Total Order

While ORDER BY is conceptually simple, achieving a total order on large, distributed datasets (like those in HDFS) is a resource-intensive operation. It typically requires the data to be moved across the network to ensure all records are correctly placed relative to each other, making it one of the more expensive Pig Latin operations. It's often reserved for final output or for operations that strictly require sequential processing.

Order By

order by statement sorts producing a total order of output data
The syntax of order by is similar to group statement

Data is sorted based on the types of the indicated fields: numeric values are sorted numerically, chararray fields are sorted lexically


daily = load 'NYSE_daily' as (exchange:chararray, symbol:chararray, date:chararray,
		                                                      open:float);
byclose = order daily by close desc, open;

dump byclose;
-- first close in descending order, then open sorted in ascending order

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

The DISTINCT statement in Pig Latin is used to remove duplicate records from a relation, ensuring that every record in the resulting relation is unique.

How DISTINCT Works

Input Relation: It takes an existing relation (like daily) as input.

Comparison: Pig compares every record in the input relation to identify duplicates. For two records to be considered duplicates, they must be identical across all fields.

Output: It produces a new relation (uniq) containing only one copy of each unique record found in the input.

daily = load 'NYSE_daily' as (exchange:chararray, symbol:chararray);

uniq = distinct daily;

The resulting relation uniq will contain only one row for every unique combination of exchange and symbol found in the original daily data. If the NYSE_daily file contained two rows like ('NYSE', 'IBM') and ('NYSE', 'IBM'), only one would be kept in uniq.

Operating on Entire Records

The crucial point about DISTINCT is that it operates only on entire records (rows), not on individual fields.

You cannot specify a single field (like symbol) and ask Pig to find all distinct values of that field.

If you only want the distinct values of a subset of fields, you must first use the FOREACH...GENERATE statement to project just those fields, and then apply DISTINCT to the resulting smaller relation.

Example: Finding Distinct Symbols Only

-- 1. Project only the field(s) of interest

symbols_only = FOREACH daily GENERATE symbol;

-- 2. Apply DISTINCT to the new relation

uniq_symbols = DISTINCT symbols_only;

This two-step process achieves the common goal of finding unique values for a column, while the core DISTINCT operation still fundamentally compares every field in the relation it is applied to (symbols_only in this case).

Distinct

distinct statement removes duplicate records


daily = load 'NYSE_daily' as (exchange:chararray, symbol:chararray);
uniq = distinct daily;

Distinct statement operates only on entire records and not on individual fields

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

The JOIN operation in Pig Latin is used to combine records from two or more existing relations (data sets, or "bags") based on a common field or set of fields, known as the join key.

Purpose: The primary goal of a JOIN is to merge data that is logically related but stored separately. For example, combining customer details from one file with their purchase history from another.

Join Keys: You must specify a field from each input relation that holds the common data used for matching. These fields are the join keys.

If a record in the left entity has the same value in its join key as a record in the right entity, Pig combines those records into a single new record in the resulting relation.

Left vs. Right Entity:

The first relation specified in the JOIN statement is the left entity (or left data set).

The second relation specified is the right entity (or right data set).

Analogy to SQL

As you noted, Pig's JOIN operation is analogous to SQL's JOIN. The primary difference is in the syntax and how Pig handles the underlying distributed processing (MapReduce), but the conceptual result is the same: a wider relation containing fields from both inputs.

Example Syntax (Standard Inner Join)

The most common type of join is the Inner Join, which only keeps records that have a match in both the left and right relations.

-- 1. Load two relations

A = load 'customers' as (cust_id: int, name: chararray);

B = load 'orders' as (order_id: int, cust_id: int, item: chararray);

-- 2. Join them on the common field (cust_id)

C = JOIN A BY cust_id, B BY cust_id;

-- Result (C):

-- {A::cust_id: int, A::name: chararray, B::order_id: int, B::cust_id: int, B::item: chararray}

Left Entity: A (Customers)

Right Entity: B (Orders)

Join Key: cust_id

The resulting relation C includes all fields from both A and B. Pig automatically prepends the relation name (e.g., A::) to field names to prevent ambiguity if both relations have fields with the same name.

Types of Joins

Pig Latin supports several types of joins, just like SQL:

Inner Join (default): Only records with matching keys in both relations are included.

Outer Joins:

Left Outer: Includes all records from the left relation, matching them with the right where possible. Missing values from the right are filled with NULL.

Right Outer: Includes all records from the right relation, matching them with the left where possible. Missing values from the left are filled with NULL.

Full Outer: Includes all records from both sides, filling missing values with NULL where a match does not exist.

Joins

join operations in Pig are analogous to the join operations in SQL
join operations combine records from two bags based on a common field
join operations act on two different data sets where one field in each data set is nominated as a join key
In a join, the first (second) dataset specified is called as the left (right) entity or data set

The JOIN operation in Pig Latin is a powerful way to merge two relations (datasets) based on a common field (the join key). Pig supports several types of joins, categorized by how they handle records that don't have a matching key in the other relation.

1. Inner Join (Default)

Syntax: Simply JOIN A BY keyA, B BY keyB;

Result: The resulting relation includes only those records where the join key is present in both the left (first) dataset and the right (second) dataset.

Analogy: It's like finding the intersection of two sets of keys. Records with keys unique to either side are excluded.

2. Left Outer Join

Syntax: JOIN A BY keyA LEFT OUTER, B BY keyB;

Result:All records from the left (first) dataset are returned.

If a record from the left finds a match in the right dataset, the fields from the right record are included.

If a record from the left has no match in the right dataset, the fields corresponding to the right dataset are filled with NULL values.

Use Case: Useful for keeping a complete list of records from one dataset (e.g., all customers) and appending information from another (e.g., orders) only when available.

3. Right Outer Join

Result:All records from the right (second) dataset are returned.

If a record from the right finds a match in the left dataset, the fields from the left record are included.

If a record from the right has no match in the left dataset, the fields corresponding to the left dataset are filled with NULL values.

Use Case: The mirror image of a Left Outer Join; useful for keeping a complete list of records from the right side.

4. Full Outer Join

Syntax: JOIN A BY keyA FULL OUTER, B BY keyB;

Result:All records from both the left and right datasets are returned.

Where keys match, the records are combined.

Where a key is only in the left dataset, the right-side fields are NULL.

Where a key is only in the right dataset, the left-side fields are NULL.

Analogy: It's like finding the union of the keys from both sets.

These variations allow for precise control over which data is retained during the merging of two large datasets.

Joins

Types of join operation
- inner join, often simply called a join, returns all elements or records from both datasets where the nominated key is present in both datasets
- left outer join returns all records from the left, or first dataset along with matched records only (by the specified key) from the right, or second, dataset
- right outer join returns all records from the right, or second dataset along with matched records only (by the specified key) from the left, or first dataset
- full outer join returns all records from both datasets whether there is a key match or not

An Inner Join is the default type of join operation in Pig Latin. It's used to combine records from two relations (datasets) and only keeps the resulting records where the join key is present in both datasets.

The example illustrates the standard Pig Latin syntax for an inner join:

inner = JOIN stores BY storeid, salespeople BY storeid;

Relations: You are joining the stores relation (the left entity) and the salespeople relation (the right entity).

Join Key: The common field used for matching is storeid in both relations.

Operation: Pig compares the storeid value in every record of the stores relation against the storeid value in every record of the salespeople relation.

Key Result Characteristics

Intersection of Keys: The output relation (inner) will only contain records where a specific storeid value exists in both the stores relation and the salespeople relation.

Excluded Records:

Any store that does not have a matching salesperson (i.e., a storeid only in stores) is excluded.

Any salesperson whose storeid does not match an existing store (i.e., a storeid only in salespeople) is excluded.

Fields: The resulting records in inner will contain all fields from the matching records of both the stores and salespeople relations.

Analogy: Think of an inner join as finding the overlap or intersection between two Venn diagrams based on a common key. Only the records that fall into the overlapping area are retained.

Inner Join


inner = JOIN stores BY storied, salespeople BY storeid;

ORM

When you perform a LEFT OUTER JOIN between two tables, say Table A (Left) and Table B (Right):

All rows from Table A are included in the result.

For each row in Table A, the query looks for a matching row in Table B based on the specified join condition (the column(s) used in the BY clause, e.g., storeid).

If a match is found, the data from Table A and the matching data from Table B are combined into a single result row.

If no match is found in Table B, the columns from Table A are still included, but the columns that would normally come from Table B are filled with NULL values.

Applying it to Example

An Example uses a data processing language (like Pig Latin, common in big data) that expresses a join operation:

leftouter = JOIN stores BY storeid LEFT OUTER, salespeople BY storeid;

Element Description

stores This is the Left Table (A). All rows from this table will be kept.

salespeople This is the Right Table (B). Matching rows from this table will be included.

BY storeid This is the Join Key. The rows are matched when the value in the storeid column is the same in both tables.

LEFT OUTER This specifies the type of join.

The result set, leftouter, will contain:

Every single store from the stores table.

Any salespeople that are linked to those stores via a matching storeid.

If a store exists but has NO entry in the salespeople table (meaning no salesperson has that specific storeid), the store's information will still be present, and the columns for the salesperson (name, ID, etc.) will be filled with NULL.

In simple terms: You get a list of all stores, with their assigned salespeople listed next to them. If a store has no salespeople, you still see the store's name, but the salesperson fields are blank.

Left Outer Join


leftouter = JOIN stores BY storied LEFT OUTER, salespeople BY storeid;

ORM

A RIGHT OUTER JOIN (often just called a RIGHT JOIN) is an operation used to combine rows from two or more tables based on a related column, similar to a LEFT JOIN.

The critical difference is that a RIGHT OUTER JOIN ensures that all rows from the "right" table (the second table named in the JOIN statement) are included in the final result set.

When you perform a RIGHT OUTER JOIN between two tables, say Table A (Left) and Table B (Right):

All rows from Table B are included in the result.

For each row in Table B, the query looks for a matching row in Table A based on the specified join condition (the column(s) used in the BY clause, e.g., storeid).

If a match is found, the data from the matching Table A row and the Table B row are combined.

If no match is found in Table A, the columns from Table B are still included, but the columns that would normally come from Table A are filled with NULL values.

A RIGHT OUTER JOIN is logically identical to a LEFT OUTER JOIN if you simply swap the order of the tables.

Applying it to Example

An Example uses a data processing language (like Pig Latin):

rightouter = JOIN stores BY storeid RIGHT OUTER, salespeople BY storeid;

Element Description

stores This is the Left Table (A). Only matching rows from this table will be kept, plus those that match the right table.

salespeople This is the Right Table (B). All rows from this table will be kept.

BY storeid This is the Join Key. The rows are matched when the value in the storeid column is the same in both tables.

RIGHT OUTER This specifies the type of join, prioritizing the right table.

The result set, rightouter, will contain:

Every single salesperson from the salespeople table.

Any store information that is linked to those salespeople via a matching storeid.

If a salesperson exists but the storeid they are associated with DOES NOT exist in the stores table (perhaps the store record was deleted or the storeid is an error), the salesperson's information will still be present, and the columns for the store (name, address, etc.) will be filled with NULL.

In simple terms: You get a list of all salespeople, with the store they work at listed next to them. If a salesperson's assigned store is missing from the stores table, you still see the salesperson's information, but the store fields are blank.

Right Outer Join


rightouter = JOIN stores BY storied RIGHT OUTER, salespeople BY storeid;

ORM

A FULL OUTER JOIN combines the functionality of both a LEFT OUTER JOIN and a RIGHT OUTER JOIN. Its purpose is to ensure that all rows from both the "left" and "right" tables are included in the result set, regardless of whether a match exists in the other table.

When you perform a FULL OUTER JOIN between two tables, Table A (Left) and Table B (Right):

All matching rows from both Table A and Table B are combined into the result.

All unmatched rows from Table A are included. The columns that would have come from Table B are filled with NULL.

All unmatched rows from Table B are included. The columns that would have come from Table A are filled with NULL.

Essentially, it gives you a complete picture of all data from both sides of the join.

Applying it toExample

An Example uses a data processing language (like Pig Latin):

fullouter = JOIN stores BY storeid FULL OUTER, salespeople BY storeid;

Element Description

stores The Left Table.

salespeople The Right Table.

BY storeid The Join Key. Rows are matched on this column.

FULL OUTER This specifies the join type, ensuring all records from both tables are kept.

The result set, fullouter, will contain three distinct groups of records:

Matching Records: Stores that have one or more matching salespeople (based on storeid).

Unmatched Left Records: Stores that do not have any corresponding records in the salespeople table. The salesperson columns will be NULL.

Unmatched Right Records: Salespeople who have a storeid that does not exist in the stores table (e.g., they are associated with a non-existent or deleted store). The store columns will be NULL.

In simple terms: You get a single list that includes every store, every salesperson, and their connection if one exists. If a store is alone, or a salesperson is alone, they are still listed, with the missing information filled in as blank (NULL).

Full Outer Join


fullouter = JOIN stores BY storied FULL OUTER, salespeople BY storeid;

ORM

Pig Latin

Outline

Preliminary matters
Input/Output in Pig Latin
Operations
Built-in functions
Foreach
Filter
Group
Order by
Distinct
Joins
Limit and sample

The LIMIT and SAMPLE statements are used for controlling the size and composition of a result set, primarily for faster inspection or processing of large datasets. They address two different needs: seeing the start of the data versus seeing a random subset of the data.

LIMIT: Restricting to the First N Records

The LIMIT statement restricts the output to a specific, fixed number of records, starting from the beginning of the dataset.

Purpose: To quickly inspect the first few records of a large dataset or to restrict the final output size.

Behavior: It takes the first N records produced by the previous operation (in your example, the data loaded into divs). The records are usually ordered by their insertion order unless a specific ORDER BY clause precedes the LIMIT.

Example:

divs = load 'NYSE_dividends';

first10 = limit divs 10;

The variable first10 will contain at most 10 records—specifically, the first 10 records loaded from the NYSE_dividends file.

SAMPLE: Restricting to a Random Percentage

The SAMPLE statement restricts the output by returning a random subset of the records, based on a specified percentage or ratio.

Purpose: To get a smaller, statistically representative snapshot of the entire dataset. This is crucial for testing operations, estimating results, or analyzing data patterns without processing the full volume.

Behavior: It randomly selects records from the input dataset based on the probability provided. The input value is a ratio (a number between 0.0 and 1.0).

Example:

divs = load 'NYSE_dividends';

some = sample divs 0.1;

The variable some will contain approximately 10% of the total records in the divs dataset. If divs has 1,000,000 records, some will have around 100,000 records, chosen randomly.

Key Difference

Feature LIMIT SAMPLE

Selection Type Deterministic (non-random) Random

Output Size Basis Fixed Number (N) Percentage/Ratio (e.g., 0.1)

Goal See the start of the data See a representative subset of the data

Limit and Sample

Sometimes we want to see only a limited number of results
limit statement allows for the restriction of an output to a given number of the first records
The following returns at most 10 lines, i.e., the first 10 records
Sometimes we want to see only a limited number of randomly selected results
sample statement allows for the restriction of an output to a given percentage of the total number of records

In the following example, 0.1 indicates 10%


divs = load 'NYSE_dividends';
some = sample divs 0.1;

References

Gates A., Programming Pig, O'Reilly Media, Inc., 2011, (Available in READINGS folder)
Vaddeman B., Beginning Apache Pig: Big Data Processing Made Easy, Apress 2016 (Available in READINGS folder)