1 / 69

Pig Programming

Pig Programming. Cloud Computing & Data Infrastructure Group, Yahoo! Inc. Viraj Bhat ( viraj@yahoo-inc.com ). University of Illinois at Urbana-Champaign Feb 13,2009. About Me. Part of the Yahoo! Grid Team since May 2008 PhD from Rutgers University, NJ

halla-gordon
Download Presentation

Pig Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Pig Programming Cloud Computing & Data Infrastructure Group, Yahoo! Inc. Viraj Bhat (viraj@yahoo-inc.com) University of Illinois at Urbana-Champaign Feb 13,2009

  2. About Me Part of the Yahoo! Grid Team since May 2008 PhD from Rutgers University, NJ Specialization in Data Streaming, Grid, Autonomic Computing Worked on streaming data from live simulations executing in NERSC (CA), ORNL (TN) to Princeton Plasma Physics Lab (PPPL - NJ) Library introduce less then 5% overhead on computation PhD Thesis on In-Transit data processing for peta-scale simulation workflows Developed CorbaCoG kit for Globus

  3. Agenda • Introduction to Pig -- Session I - [11 am -12 pm] • Overview • Pig Latin overview • Pig Examples • Pig Architecture • Pig Latin details • Additional Topics • Pig Streaming and Parameter substitution • Translating SQL queries into Pig Latin • Hands-on Pig -- Session II [1 pm - 4 pm] • Running, debugging, optimization

  4. Hadoop and Pig Hadoop is great for distributed computation but.. 1. Several simple, frequently-used operations Filtering, projecting, grouping, aggregation, joining, sorting commonly used by users at Yahoo! 2. Users’ end goal typically requires several Map-Reduce jobs Thus, making it hard to maintain, extend, debug, optimize

  5. What is PIG? • SQL like language (PIG Latin) for processing large semi-structured data sets using Hadoop M/R • PIG is an Hadoop (Apache) sub-project • ~30 % Grid users in Yahoo use PIG • PIG Latin • PIG is procedural (data flow) language • Allows users to specify explicit sequence of steps for data processing • Think of it as: Scripting harness to chain together multiple map-reduce jobs • Supports relational model • But NOT very strongly typed • Supports primitive relational operators like, FILTER, PROJECTIONS, JOIN, GROUPING etc.

  6. Advantages of using Pig • Pig can be treated as a higher level language • Increases programmer productivity • Decreases duplication of effort • Opens the M/R programming system to more users • Pig insulates against hadoop complexity • Hadoop version upgrades • JobConf configuration tuning

  7. Pig making Hadoop Easy • How to find the top 100 most visited sites by users aged 18 to 25?? • Using Native Hadoop Java code requires 20x more lines of code vs. Pig script • Native Hadoop Java code requires 16x more development time than Pig • Native Hadoop Java code takes 11 min to run while Pig requires 20 min Solution in Native Hadoop M/R program Users = LOAD ‘users’ AS (name, age); Fltrd = FILTER Users by age >= 18 and age <= 25; Pages = LOAD ‘pages’ AS (user, url); Jnd = JOIN Fltrd BY name, Pages BY user; Grpd = GROUP Jnd by url; Smmd = FOREACH Grpd GENERATE group, COUNT(Jnd) AS clicks; Srtd = ORDER Smmd BY clicks; Top100 = LIMIT Srtd 100; STORE Top100 INTO ‘top100sites’; Solution using Pig Script

  8. Pig Latin

  9. Introduction to Pig Latin • Pig Latin is a dataflow language through which users can write programs in Pig • Pig Latin consists of the following datatypes • Data Atom • Simple atomic data value eg. ‘alice’ or ‘1.0’ • Tuple • Data record consisting of a sequence of “fields” (similar to record in SQL) e.g. (alice,lakers) or (alice,kings) • Data Bag • Set of Tuples equivalent to a “table” in SQL terminology e.g. {(alice,lakers), (alice,kings)} • Data Map • A set of key value pairs e.g. [likes#(lakers,kings),age#22]

  10. (alice,lakers) (alice,kings) Pig Latin: Data Types Type Example 1. Data Atom an atomic value ‘alice’ 2. Tuple a record (alice,lakers) 3. Data Bag collection supporting scan [ ] (lakers), (kings) ‘likes’# 4. Data Map collection with key lookup ‘age’#22

  11. Pig Types • Pig supports the following types in addition to map, bag and tuples • int: 32 bit integer, signed type • long: 64 bit for unsigned type • (alice,25,6L) • float: 32 bit floating point • (alice,25, 5.6f),(alice,25,1.92e2f) • double: 64 bit floating point • (alice,22,1.92),(alice,22,1.92e2) • chararray: set of characters stored in Unicode • (‘alice’, ‘bob’) • bytearray: array of bytes • default type if you do not specify the type of the field

  12. Expressions a = name of alias/relation which contains types int, tuple and map note: bag, tuple keywords are optional a  (f1:int , f2:bag{t:tuple (n1:int, n2:int)}, f3: map[] ) ( ) (2,3), (4,6) 1, , [‘yahoo’#‘mail’] Sample tuple of a f3 or $2 f1or $0 f2 or $1 Field referred to by position Projection of a data item Field referred to by name (2,3), (4,6) (2), (4) (2,3), (4,6) $1 = f2.$0 = f2 = $0 = 1 Function Application Map Lookup SUM(f2.$0) = 6 (2+4) COUNT(f2) = 2L f3# ‘yahoo’ = ‘mail’

  13. Conditions (Boolean Expressions) a = name of alias which contains types int, tuple and map a  (f1:int , f2:bag{t:tuple (n1:int, n2:int)}, f3: map[] ) ( ) (2,3), (4,6) 1, , [‘yahoo’#‘mail’] Sample tuple of a f3 or $2 f1or $0 f2 or $1 • ==, !=, >, >=, <, <= for numerical comparison • Pig: only f1==1 works as f1 is type integer • eq, neq, gt, gte, lt, lte for string comparisons • f3#‘yahoo’ gt ‘ma’ • deprecated, does not force operations to string • matches for regular expression matching • f3#‘yahoo’ matches ‘(?i)MAIL’

  14. Pig Latin Operators and Conditional Expression a = name of alias which contains types int, tuple and map a  (f1:int , f2:bag{t:tuple (n1:int, n2:int)}, f3: map[] ) ( ) (2,3), (4,6) 1, , [‘yahoo’#‘mail’] Sample tuple of a f3 or $2 f1or $0 f2 or $1 • Logical Operators • AND, OR, NOT • f1==1 AND f3#‘yahoo’ eq ‘mail’ • Conditional Expression (aka BinCond) • (Condition?exp1:exp2) • f3#‘yahoo’matches ‘(?i)MAIL’ ? ‘matched’: ‘notmatched’

  15. Pig Latin Language by Example

  16. Examples • Word Count • Computing average number of page visits by user • Identify users who visit highly ranked or Good pages • Namenode Audit Log processing via UDF’s in Chukwa (User Defined Functions)

  17. Word count using Pig wc.txt • Count words in a text file separated by lines and spaces • Basic idea: • Load this file using a loader • Foreach record generate word token • Group by each word • Count number of words in a group • Store to file

  18. Word Count using Pig myinput = load '/user/viraj/wc.txt' USING TextLoader() as (myword:chararray); words = FOREACH myinput GENERATE FLATTEN(TOKENIZE(*)); grouped = GROUP words BY $0; counts = FOREACH grouped GENERATE group, COUNT(words); store counts into ‘/user/viraj/pigoutput’ usingPigStorage(); Write to HDFS /user/viraj/pigoutput/part-* file

  19. Computing average number of page visits by user • Logs of user visiting a webpage consists of (user,url,time) • Fields of the log are tab separated and in text format • Basic idea: • Load the log file • Group based on the user field • Count the group • Calculate average for all user Visits log . . .

  20. How to program this in Pig Latin VISITS = load ‘/user/viraj/visits' as (user, url, time); USER_VISITS = group VISITS by user; USER_CNTS = foreach USER_VISITS generate group as user, COUNT(VISITS) as numvisits; ALL_CNTS = group USER_COUNTS all; AVG_CNT = foreach ALL_CNTS generateAVG(USER_COUNTS.numvisits); dump visits;

  21. Identify users who visit “Good Pages” Visits log Pages log Good page • Good pages are those pages visited by users whose page rank is greater than 0.5 • Basic idea • Join tables based on url • Group based on user • Calculate average pagerank of user visited pages • Filter user who has average pagerank greater than 0.5 • Store the result . . . . . .

  22. How to Program this in Pig VISITS = load ‘/user/viraj/visits.txt' as (user:chararray, url:chararray, time:chararray); PAGES = load '/user/viraj/pagerank.txt' as (url:chararray, pagerank:float); VISITS_PAGES = join VISITS by url, PAGES by url; Load files for processing with appropriate types Join them on url fields of table

  23. How to Program this in Pig Group by user, in our case Amy, Fred USER_VISITS = group VISITS_PAGES by user; USER_AVGPR = foreach USER_VISITS generate group, AVG(VISITS_PAGES.pagerank) as avgpr; GOOD_USERS = filter USER_AVGPR by avgpr > 0.5f; store GOOD_USERS into '/user/viraj/goodusers'; Generate an average pagerank per user Filter records based on pagerank Store results in hdfs

  24. Namenode Audit log processing (1232063871538L, [capp#/hadoop/log/audit.log,ctags#cluster=“cluster2", body#2009-01-15 23:57:51,538 INFO org.apache.hadoop.fs.FSNamesystem.audit: ugi=users,hdfs<tab> ip=/11.11.11.11<tab>cmd=mkdirs src=/user/viraj/output/part-0000<tab>dst=null<tab> perm=users:rwx------ ,csource#machine1@grid.com]) (1232063879123L, [capp#/hadoop/log/audit.log,ctags#cluster=“cluster2",body#2009-01-15 23:57:59,123 INFO org.apache.hadoop.fs.FSNamesystem.audit: ugi=users,hdfs ip=/11.12.11.12 <tab>cmd=rename<tab>src=/user/viraj/output/part-0001<tab> dst=/user/viraj/output/part-0002<tab>perm=users:rw-------, csource#machine2@grid.com]) • Count number of HDFS operations that took place • Basic idea: • Load this file using a custom loader known as ChukwaStorage into a long column and a list of map fields • Extract the value of the body key as it contains the cmd the namenode executed • Cut the body tag on tabs to extract only the cmd value using a UDF • Group by cmd value and Count it audit.log

  25. How to Program this in Pig Register the jars which contain your udf’s • register chukwa-core.jar • register chukwaexample.jar • A = load '/user/viraj/Audit.log' using chukwaexample.ChukwaStorage() as (ts: long,fields) • B = FOREACH A GENERATE fields#'body' as log; • C = FOREACH B GENERATE FLATTEN(chukwaexample.Cut(log)) as (timestamp, ip, cmd, src, dst, perm); Jar internally used by ChukwaStorage Contains both ChukwaStorage and Cut classes Project the right value for key body Cut the body based on tabs to extract “cmd”

  26. How to Program this in Pig • D = FOREACH C GENERATE cmd as cmd:chararray; • E = group D by cmd; • F = FOREACH E GENERATE group, COUNT(D) as count; • dump F; Project the right column for use in group Group by command type Count the commands and dump

  27. Pig Architecture

  28. cluster Pig Architecture - Map-Reduce as Backend ( SQL ) user automatic rewrite + optimize Pig or or Logical Plan Map-Reduce Physical Plan A high-level language to express computation and execute it over hadoop M/R Plan

  29. Pig Architecture • Parsing • Semantic checking • Planning • Runs on user machine or gateway Front End Back End • Script execution • Runs on grid

  30. Front End Architecture Pig Latin Pig Latin Programs Query Parser Logical Plan Semantic Checking Logical Plan Logical Optimizer Optimized Logical Plan Logical to Physical Translator Physical Plan Physical To M/R Translator MapReduce Plan Map Reduce Launcher Create a job jar to be submitted to hadoop cluster

  31. Logical Plan • Consists of DAG of Logical Operators as nodes and Data Flow represented as edges • Logical Operators contain list of i/p’s o/p’s and schema • Logical operators • Aid in post parse stage checking (type checking) • Optimization • Translation to Physical Plan Proj(0) Load a = load ‘myfile’; b = filter a by $0 > 5; store b into ‘myfilteredfile’; Filter > Store Const(5)

  32. Physical Plan • Layer to map Logical Plan to multiple back-ends, one such being M/R (Map Reduce) • Chance for code re-use if multiple back-ends share same operator • Consists of operators which pig will run on the backend • Currently most of the physical plan is placed as operators in the map reduce plan • Logical to Physical Translation • 1:1 correspondence for most Logical operators • Except Cross, Distinct, Group, Co-group and Order

  33. Logical to Physical Plan for Group operator • Logical operator for co-group/group is converted to 3 Physical operators • Local Rearrange (LR) • Global Rearrange (GR) • Package (PKG) • Example: • group A by Acol1, B by Bcol1 {1,{(1,R)1}, {(1,B)2}} {2,{(2,G)1}, {(2,Y)2}} PKG {1,{(1,R)1, (1,B)2}} {2,{(2,Y)2, (2,G)2}} GR {Key,{ListofValues}} LR LR {1,(1,B)}2 {2,(2,Y)}2 {1,(1,R)}1 {2,(2,G)}1 {Key,(Value)}(table no) A B Tuples (1,R) (2,G) (1,B) (2,Y) Acol1 Bcol1

  34. mapi reduce1 map1 reducei group cogroup load filter cogroup C1 Ci+1 Ci Map Reduce Plan • Physical to Map Reduce (M/R) Plan conversion happens through the MRCompiler • Converts a physical plan into a DAG of M/R operators • Boundaries for M/R include cogroup/group, distinct, cross, order by, limit (in some cases) • Push all subsequent operators between cogroup to next cogroup into reduce • order by is implemented as 2 M/R jobs • JobControlCompiler then uses the M/R plan to construct a JobControl object

  35. Back End Architecture Pig operators are placed in a pipeline and each record is then pulled through. record from hadoop Map phase will have for each & filter A = load … B = foreach … C = filter … D = group … … For each Filter Prep for reduce collect

  36. Lazy Execution • Nothing really executes until you request output • Only when the store, dump, explain, describe, illustrate command is encountered does the M/R execution takes place either on your cluster or front end • Advantages • In-memory pipelining • Filter re-ordering across multiple commands

  37. Commands for manipulating data sets

  38. Loading Data using PigStorage query = load ‘/user/viraj/query.txt’ using PigStorage() as (userId, queryString, timestamp) queries = load‘/user/viraj/querydir/part-*’using PigStorage() as (userId, queryString, timestamp) queries = load ‘query_log.txt’ using PigStorage(‘\u0001’) as (userId, queryString, timestamp) All files under querydir containing part-* are loaded • Can be a file • Can be a path with globbing • userID, queryString, timestamp fields should to be tab separated • PigStorage(‘{delimiter}’) • loads records with fields delimited by {delimiter} • unicode representation ‘\u0001’ for Ctrl+A, • ‘\u007C’ for pipe character: |

  39. Storing data using PigStorage and viewing Data store joined_result into ‘/user/viraj/output’ ; store joined_result into ‘/user/viraj/myoutput/’ usingPigStorage(‘\u0001’); store joined_result into ‘/user/viraj/myoutputbin’ usingBinStorage(); store sports_views into ‘/user/viraj/myoutput’ usingPigDump(); dump sports_views; • Default PigStorage if you do not specify anything • Result stored as ASCII text in files part-* under output dir • Similar to load can use PigStorage (‘{delimiter}’) • Stores records with fields delimited by unicode {delimiter} • Default is tab i.e. you don’t specify anything • Store as Ctrl+A separated by specifying PigStorage(‘\u0001’) • BinStorage store arbitrarily nested data. • Used for loading intermediate results that were previously stored using it. • Dump displays data on terminal • Use PigDump storage class internally (alice,lakers,3L) (alice,lakers, 0.7f)

  40. Filtering Data queries = load ‘query_log.txt’ using PigStorage(‘\u0001’) as (userId: chararray, queryString: chararray, timestamp: int); filtered_queries = filter queries by timestamp > 1; filtered_queries_pig20 = filter queries by timestamp is not null; store filtered_queries into ‘myoutput’ using PigStorage(); queries: (userId, queryString, timestamp) filtered_queries: (userId, queryString, timestamp) (alice, iPod, 3) (alice, lakers, 4) (alice, lakers, 1) (alice, iPod, 3) (alice, lakers, 4) (alice, lakers, 1) (alice, iPod, 3) (alice, lakers, 4) filtered_queries_pig20: (userId, queryString, timestamp)

  41. (Co)Grouping Data Data 1: queries: (userId: charrarray, queryString : chararray, timestamp: int) Data 2: sports_views: (userId: chararray, team: chararray, timestamp: int) queries = load query.txt as ()..; sport_views = load sports_views.txt as ….; team matches = cogroup sports_views by (userId, team), queries by (userId, queryString);

  42. Example of Cogrouping sports_views: (userId, team, timestamp) queries: (userId, queryString, timestamp) (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, iPod, 3) (alice, lakers, 4)

  43. Example of Cogrouping sports_views: (userId, team, timestamp) queries: (userId, queryString, timestamp) (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, iPod, 3) (alice, lakers, 4) cogroup team_matches: (group, sports_views, queries) team matches = cogroup sports_views by (userId, team), queries by (userId, queryString);

  44. ) ( (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, lakers, 4) (alice, lakers), , Example of Cogrouping sports_views: (userId, team, timestamp) queries: (userId, queryString, timestamp) (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, iPod, 3) (alice, lakers, 4) cogroup team_matches: (group, sports_views, queries)

  45. Example of Cogrouping sports_views: (userId, team, timestamp) queries: (userId, queryString, timestamp) (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, iPod, 3) (alice, lakers, 4) cogroup ) ( (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, lakers, 4) (alice, lakers), , ( (alice, iPod), {},{ (alice, iPod, 3) }) team_matches: (group, sports_views, queries)

  46. Cogrouping sports_views: (userId, team, timestamp) queries: (userId, queryString, timestamp) (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, iPod, 3) (alice, lakers, 4) cogroup ) ( (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, lakers, 4) (alice, lakers), , ( (alice, iPod), {},{ (alice, iPod, 3) }) team_matches: (group, sports_views, queries) Can operate on grouped data just as on base data using foreach

  47. Filtering records of Grouped Data filtered_matches = filter team_matches by COUNT(sports_views) > ‘0’; team_matches: (group, sports_views, queries) ) ( (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, lakers, 4) (alice, lakers), , ( (alice, iPod), {},{ (alice, iPod, 3) }) ) ( (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, lakers, 4) (alice, lakers), ,

  48. Per-Record Transformations using foreach times_and_counts = foreach team_matches generate group, COUNT(sports_views), MAX(queries.timestamp); team_matches: (group, sports_views, queries) ) ( (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, lakers, 4) (alice, lakers), , ( (alice, iPod), {},{ (alice, iPod, 3) }) ((alice, lakers), 2, 4) ( (alice, iPod), 0, 3 ) times_and_counts: (group, -, -)

  49. Giving Names to Output Fields in FOREACH times_and_counts = foreach team_matches generate group, COUNT(sports_views) ascount, MAX(queries.timestamp) asmax; ) ( (alice, lakers, 3) (alice, lakers, 7) (alice, lakers, 1) (alice, lakers, 4) (alice, lakers), , ( (alice, iPod), {},{ (alice, iPod, 3) }) ((alice, lakers), 2, 4) ( (alice, iPod), 0, 3 ) times_and_counts: (group, count, max)

  50. Eliminating Nesting: FLATTEN expanded_queries1 = foreachqueries generate userId, expandQuery(queryString); expanded_queries2 = foreach queries generate userId, flatten(expandQuery(queryString)); queries: (userId, queryString, timestamp) ) ( (lakers rumors) (lakers news) alice, (alice, lakers, 1) (bob, iPod, 3) expanded_queries1 ) ( (iPod nano) (iPod shuffle) bob, queries: (userId, queryString, timestamp) flatten(expandQuery(queryString)) (alice, lakers rumors) (alice, lakers news) (bob, iPod nano) (bob, iPod shuffle) (alice, lakers, 1) (bob, iPod, 3) expanded_queries2

More Related