Steven F. Ashby Center for Applied Scientific Computing Month ...

Steven F. Ashby Center for Applied Scientific Computing Month ...

CSE 491/891 Lecture 23 (Pig and Hive) 1 Outline Last lecture (on Pig Latin) Relational operators FOREACH, GROUP, UNION, ORDER BY, DISTINCT, FILTER,

SPLIT, JOIN Functions TOKENIZE, FLATTEN, etc How to count number of words in documents using Pig Todays lecture Macros User-defined functions

Intro to Hive 2 Macros Macros provide a way to package reusable code DEFINE () RETURNS { Pig latin code for macro definition } Example: wordcount.pig DEFINE wordcount(text) RETURNS counts { tokens = foreach $text generate TOKENIZE($0) as terms;

wordlist = foreach tokens generate FLATTEN(terms) as word, 1 as freq; groups = group wordlist by word; $counts = foreach groups generate group as word, SUM(wordlist.freq) as freq; } 3 Macros You can either type in the macro definition in the grunt

shell directly or load it from a script file Example: At runtime, Pig will expand the macro using the macro definition above before executing it 4 User Defined Functions

Before writing your own functions, you may want to look at some examples first Piggy Bank is a repository of Pig functions shared by the Pig community. Pig functions are written as Java classes and archived in jar files You can download the source files (piggybank.tar) from the class webpage 5 User Defined Functions

There are different types of functions: Eval function: a function that takes one or more expression and returns another expression (e.g., MAX) results = FOREACH grp GENERATE group, myEvalFn(data.val) Filter function: a function that returns a Boolean value (to be used with FILTER operation) results = FILTER records BY myFilterFn(val) Store/Load function: a function that specifies how to load or store data mydata = LOAD mydata.txt USING myLoadFn()

6 Writing Your Own Function 1. Write your function as a Java program that extends the following Pig abstract classes Your Function Type 2. Pig Abstract Class

Eval function EvalFunc Filter function FilterFunc Load function LoadFunc

Compile your program: javac cp CLASSPATHFor AWS, the CLASSPATH is /usr/lib/pig/pig-0.16.0amzn-0-core-h2.jar:/usr/lib/hadoop/hadoop-aws-2.7.3amzn-1.jar (check first to make sure the file exists) 7 Writing Your Own Function 3. Next, create a jar file to archive your program shell> jar cvf

4. To use the function, you must register it first. grunt> REGISTER myfunction.jar 5. You can now start using the function in the grunt shell or in your Pig script file 8

Example Consider the problem of finding frequent words in a corpus of documents by filtering infrequent words that appear fewer times than a threshold Pig Latin: data = LOAD datafile AS (text:chararray); counts = wordcount(data); -- this uses the wordcount -- macro was defined on slide #3 REGISTER PopularTerms.jar; results = FILTER counts BY PopularTerms(freq,2); -- assume threshold is equal to 2 dump results;

User-defined filter function 9 PopularTerms.java This program takes 2 input arguments Word frequency Minimum frequency threshold (user parameter) It returns the value true if the word frequency is greater than or equal to the threshold

results = FILTER counts BY PopularTerms(freq,thres); 10 PopularTerms.java (Overall Structure) import java.io.*; import java.util.*; import org.apache.pig.FilterFunc; import org.apache.pig.backend.executionengine.ExecException; import org.apache.pig.data.Tuple; import org.apache.pig.impl.logicalLayer.FrontendException;

public class PopularTerms extends FilterFunc { @Override public Boolean exec (Tuple tuple) throws IOException { // This code will filter tuples with frequency below threshold // tuple contains the parameter values passed to the function } } 11 PopularTerms.java public Boolean exec (Tuple tuple) throws IOException { if (tuple == null || tuple.size() == 0) {

return false; } try { Object field1 = tuple.get(0); // get frequency Object field2 = tuple.get(1); // get threshold if (field1 == null || field2 == null) { return false; } int count = ((Long) field1).intValue(); int threshold = (Integer) field2; return (count >= threshold); } catch (ExecException e) { throw new IOException(e); }

} 12 Make sure you check the types of the fields first in grunt (using DESCRIBE) PopularTerms.java To compile and archive

Script to run the job (filterWords.pig); you can also run it interactively by typing into grunt 13 PopularTerms.java Submit the job 14 Summary Pig is a high level programming framework built

on top of Hadoop to simplify the task of writing programs to manipulate data (analogous to SQL) Limitation: syntax is still quite different from SQL Its a procedural language instead of a declarative language like SQL 15 CSE 491/891 Intro to Hive

16 Hive A data warehousing framework built on top of Hadoop Created to make it possible for analysts with strong SQL skills (but little Java programming skills) to run queries on large volumes of data Developed by Jeff Hammerbacher and his team at Facebook 17

Main Components of Hive Hive Server A service that enables remote clients to execute Hive query and retrieve the results HiveQL An SQL-like query language for interacting with Hive Hive Compiler Convert Hive queries into map-reduce jobs

Hive Client A command line interface (CLI) to interact with Hive Older Hive version: HiveCLI (now deprecated) Current Hive version: Beeline 18 Hive Separation of data from the table schema Data is stored on HDFS (raw data files)

Schema is stored in a metastore Unlike relational database (SQL), you cant simply update or delete an individual record in a table Hadoop is used for batch processing, it is not an online transaction database (where you can add/update/ remove any rows in real-time) 19 Launching Beeline Client After creating an AWS EMR cluster, you may

need to wait for > 15 min. to ensure hive is fully installed Otherwise, you may run into different types of errors when executing Hive query commands To launch the beeline hive client: ** This usually take about 10 minutes to get it up and running 20 Useful Commands !connect: connecting to Hive server

!help: get help !close: close the server connection !close all: close all server connections !quit: exit 21 Launching Hive Client Use jdbc to connect to the Hive server You can use the default database Username and password can be blank (press Enter)

Once connected, you can start entering SQL commands for creating, altering, querying, and dropping tables in the database 22 Other Example Commands List all the beeline commands available To exit the client program 23

Data Storage By default, Hive will store your data under the /user/hive/warehouse directory on HDFS You may request Hive to add compression and special directory structures (such as partitions) to the data to improve query performance However, you can also store your data in another directory on HDFS Hive will take the data as it is and will not try to optimize your data storage during query processing

24 Internal and External Tables Internal tables: When a new table is created, the raw data will be moved from its original location to /user/hive/warehouse When table is dropped, both the metadata (its schema) and the actual raw data will both be removed External tables: When table is created, a directory with the same name as the table name is created at /user/hive/warehouse,

but the data is kept at its original location When a table is dropped, its directory is removed from / user/hive/warehouse (along with meta data), but the original data (which is stored elsewhere) is not removed 25 CREATE TABLE Internal table: CREATE TABLE ( ) ...;

LOAD DATA INPATH INTO TABLE ; External table: CREATE EXTERNAL TABLE ( ) LOCATION ; -- specify the location of the original data 26 Example: Wikipedia Revisions Input data (wiki_edit.txt)

Columns are separated by white space Schema: (RevisionID, Article, EditDate, Username) 27 CREATE TABLE Syntax looks very similar to SQL: Each command is terminated by semicolon The above create table statement is incomplete You cant do INSERT command like SQL You need to specify the original data file and how the

fields within each record are delimited (separated) 28 CREATE INTERNAL TABLE To load data from an existing file, you need to specify the delimiter character for the fields 29 Example You can use hadoop fs to check the /user/hive/warehouse directory

Note that the command will create a subdirectory named wiki under /user/hive/warehouse But there is no data file in the directory since we did not specify where the original data is located 30 LOAD DATA INTO INTERNAL TABLE Input data is wiki_edit.txt (make sure it is uploaded to HDFS in a directory named wiki) filepath

This will move the data files in /user/hadoop/wiki on HDFS to the / user/hive/warehouse/wiki directory OVERWRITE forces the content of the target table to be deleted and replaced with files referred to by the inpath Hive does not parse the file to an internal database format, i.e., files are stored in its original format on HDFS Conversion from raw bytes in a file to appropriate fields occur when a retrieval or update query is processed 31 CREATE EXTERNAL TABLE

Data will NOT be moved to /user/hive/warehouse; they will remain in the external directory /user/hadoop/wiki 32 Data Types 33 Data Types

34 SHOW and DESCRIBE TABLES To display list of tables available To show the schema of a table 35 SQL Queries 36

Another Example: Bestbuy queries Upload data (bbuy.csv) to HDFS into a directory named bestbuy Schema: (user,sku,category,query,clicktime,querytime) 37 Another Example: Bestbuy queries Create an external table:

File location on HDFS If you drop the external table temp, the original data file still exist on /user/hadoop/bestbuy (but the table no longer exists) 38 DROP and ALTER TABLE Use drop table to delete the data and metadata for a table Drop table If you want to delete the data but keep the table

definition, simply remove the files in HDFS (without removing the directory) Use Alter table to modify the tables schema ALTER TABLE abc RENAME TO xyz ALTER TABLE abc ADD COLUMNS (newcol STRING) 39

Recently Viewed Presentations

  • Attachment Time to Change Seats! Your Experience How

    Attachment Time to Change Seats! Your Experience How

    1. Define "intersubjectivity" and describe the relationship between intersubjectivity and attachment. 2. Describe five components of Dyadic Developmental Psychotherapy (DDP) that are common to the empirically based psychotherapies and the four elements that comprise "PACE".
  • Start Your Engines - Social Studies School Service

    Start Your Engines - Social Studies School Service

    You are probably wondering what these car parts have to do with you, so let's get started! Without the battery, a car would never start up and go. A car battery provides the short burst of electric energy that is...
  • How to Integrate Quotes in Literary Analysis

    How to Integrate Quotes in Literary Analysis

    . it was obvious that an invisible wall, one impossible to breach, lay between me and the homeguard Indians of this white town" (168). Niska's feelings of alienation from the very people who, only decades ago, were living with her...
  • PERSONAL FINANCE & TAX PLANNING FOR COST ACCOUNTANTS

    PERSONAL FINANCE & TAX PLANNING FOR COST ACCOUNTANTS

    WHAT IS MUTUAL FUND. A Mutual fund is a trust that pools savings of a number of Investors who share a common financial goal. The money thus collected which could be as small as Rs 500 pm, is then invested...
  • Magnetism - Mayfield City School District

    Magnetism - Mayfield City School District

    History #1. Term comes from the ancient Greek city of Magnesia, at which many natural magnets were found. We now refer to these natural magnets as lodestones (also spelled loadstone; lode means to lead or to attract) which contain magnetite,...
  • ENVIRONMENTAL POLICY 2018 THREATS, OPPORTUNITIES, AND HOW WE

    ENVIRONMENTAL POLICY 2018 THREATS, OPPORTUNITIES, AND HOW WE

    Passed both chambers. SR1600 - Foxconn Opposition (Senator Melinda Bush): This resolution expresses significant concerns over the environmental waivers granted by the State of Wisconsin and the U.S. EPA for the Foxconn facility. It also states that Illinois would not...
  • Physical Education - Eversley

    Physical Education - Eversley

    Physical Education. Eversley Primary School Physical Education Vision: 'For all children to leave Eversley Primary School with the skills, confidence, knowledge and enthusiasm for a lifelong participation in physical activity and sport.'. Physical Education is an essential part of our...
  • VaR Impact Using CDS Spot Curve - Mathematical finance

    VaR Impact Using CDS Spot Curve - Mathematical finance

    Value at Risk. What is VaR. In its most general form, the Value at Risk measures the potential loss in value of a risky asset or portfolio over a defined period for a given confidence interval. ... Credit products. Structure...