kskp kskp - 1 year ago 63
JSON Question

Join two aliases in Pig

This might seem like a basic question:

I am loading a json into Pig using elephant bird and I want only few fields in the json. Again, I want to use those fields and generate new fields and add in the original json. I have the following pig script:

data_input = LOAD '$DATA_INPUT' USING com.twitter.elephantbird.pig.load.JsonLoader() AS (json:map []);

x = FOREACH data_input GENERATE json#'user__id_str' AS user_id, json#'user__created_at' AS user_created_at, json#'avl_user_days_active' AS user_days_active, json#'user__notifications' AS user_notifications, json#'user__follow_request_sent' AS user_follow_request_sent, json#'user__friends_count' AS user_following_count, json#'user__name' AS user_name, json#'user__time_zone' AS user_time_zone, json#'user__profile_background_color' AS user_profile_background_color, json#'user__is_translation_enabled' AS user_translation_enabled, json#'user__profile_link_color' AS user_profile_link_color, json#'user__utc_offset' AS user_UTC_offset, json#'user__profile_sidebar_border_color' AS user_profile_sidebar_border_color, json#'user__has_extended_profile' AS user_has_extended_profile, json#'user__profile_background_tile' AS user_profile_background_tile, json#'user__is_translator' AS user_is_tranlator, json#'user__profile_text_color' AS user_profile_text_color, json#'user__location' AS user_location, json#'user__profile_banner_url' AS user_profile_banner_url, json#'user__profile_use_background_image' AS user_profile_use_background_image, json#'user__default_profile_image' AS user_default_profile_image, json#'user__description' AS user_description, json#'user__profile_background_image_url_https' AS user_profile_background_image_url_https, json#'user__profile_sidebar_fill_color' AS user__profile_sidebar_fill_color, json#'user__followers_count' AS user_followers_count, json#'user__profile_image_url' AS user_profile_image_url, json#'user__geo_enabled' AS user_geo_enabled, json#'user__entities__description__urls' AS user_entities_description_urls, json#'user__screen_name' AS user_scren_name, json#'user__favourites_count' AS user_total_liked, json#'user__url' AS user_url, json#'user__statuses_count' AS user_total_posts, json#'user__default_profile' AS user_default_profile, json#'user__lang' AS user_language, json#'user__protected' AS user_protected, json#'user__listed_count' AS user_totalPublic_lists, json#'user__profile_image_url_https' AS user_profile_image_url_https, json#'user__contributors_enabled' AS user_contributors_enabled, json#'user__following' AS user_following, json#'user__verified' AS user_verified;

y1 = FOREACH x GENERATE user_total_posts/user_days_active as user_post_frequency;

y2 = GROUP x BY user_id;


Now, I want to add the aliases
and write it to an output file. That is, I want to add new fields user_post_frequency and user_total_replies to
and store it.

How can I do that?


I tried to join the two aliases:

y1 = FOREACH x GENERATE user_id, user_total_posts/user_days_active as user_post_frequency;
y2 = JOIN x BY user_id, y1 BY user_id;
fs -rmr /tmp/user
STORE y2 INTO '/tmp/user' USING JsonStorage();

But my output looks like:

{"x::user_id":"9642792","x::user_created_at":"Wed Oct 24 02:44:30 +0000 2007","x::user_days_active":"3272","x::user_notifications":"false","x::user_foll ow_request_sent":"false","x::user_following_count":"500","x::user_name":"Everything Finance","x::user_time_zone":"Eastern Time (US & Canada)","x::user_p rofile_background_color":"131516","x::user_translation_enabled":"false","x::user_profile_link_color":"992E01"," y1::user_id":"9642792","y1::user_post_frequency":10.910452322738386}

I donot want
in the output. Just want the field names. Anything I should do?

Answer Source

You can use the PIG project range capability to generate all the columns and add additional columns to the existing relation.

x.$0 .. will project all fields in the relation x

y1 = FOREACH x GENERATE x.$0 ..,user_total_posts/user_days_active as user_post_frequency;
y2 = GROUP y1 BY user_id;
z = FOREACH y2 GENERATE y1.$0 ..,COUNT(x) as user_total_replies ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download