-- records_orders.pig
-- by Joe Hahn, jhahn@spacescience.org, 7 February 2014.
--
-- This Pig script reads the records and orders csv files, joins them on record_id,
-- groups by city,state and then sums the $ amounts of all orders at each locale,
-- and writes the results to file.
--
-- To execute in local mode > pig -x local records_orders.pig

records = load 'records.csv' using PigStorage(',') as (index, record_id, city, state);
orders = load 'orders.csv' using PigStorage(',') as (index, order_id, record_id, amount);
rec_ord = join records by record_id, orders by record_id;
rec_ord_grouped = group rec_ord by (city, state);
amount_s = foreach rec_ord_grouped generate group, SUM(rec_ord.amount) as amount;
amount_so = order amount_s by amount desc;
store amount_so into 'amount';