Pig - How to reference columns in a FOREACH after a JOIN when using positional notation?

Question

I have 2 tables orders and order_items

orders table contains (order_id, order_date,order_customer_id, order_status, order_month) order_items contains (order_item_id, order_item_order_id, order_item_product_id, order_item_quantity, order_item_subtotal, order_item_product_price)

the tables are joined by orders.order_id and order_items.order_item_order_id
the datatype is not provided so positional notation is used.

orders = LOAD '/user/horton/orders' USING PigStorage(',');
order_items = LOAD '/user/horton/orders' USING PigStorage(',');
ordersjoin = JOIN orders BY $0, order_items BY $1 ; 
orderrevenuebydate = FOREACH ordersjoin GENERATE orders::$1,     order_items::$4;

I get the following error when trying to generate the FOREACH for orderrevenuebydate

Unexpected character '$' 2016-06-19 19:17:22,757 [main] ERROR org.apache.pig.tools.grunt.Grunt - ERROR 1200: Unexpected character '$' Details at logfile: /home/6301dd50e3fac19f7c90fbf9898496/pig_1466356957630.log

ninja123 ninja123 · Accepted Answer · 2016-06-19T22:47:45

You'll have to directly reference the positional notation from the relation.

For example, if you want to generate order_date and order_item_subtotal fields from ordersjoin relation, use the below statement.

orderrevenuebydate = FOREACH ordersjoin GENERATE $1, $9;

Note that after the join operation, ordersjoin relation will contain all the attributes from two relations.

Pig - How to reference columns in a FOREACH after a JOIN when using positional notation?

1 Answers