{"id":82,"date":"2016-12-02T18:41:10","date_gmt":"2016-12-02T18:41:10","guid":{"rendered":"http:\/\/vargas-solar.com\/bigdata-visualisation\/?page_id=82"},"modified":"2016-12-02T18:43:38","modified_gmt":"2016-12-02T18:43:38","slug":"answers","status":"publish","type":"page","link":"http:\/\/vargas-solar.com\/bigdata-visualisation\/hands-on\/answers\/","title":{"rendered":"Answers"},"content":{"rendered":"<ul>\n<li>Filter the speedtest conducted in Barcelona or\u00a0Madrid. Then list the internet providers\u00a0working in those cities.<\/li>\n<\/ul>\n<div class=\"entry-content\">\n<pre>NeubotTests = LOAD 'NeubotTests' using PigStorage(';') as (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_address: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_country: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lon: float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lat: float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_provider: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 mlabservername:\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connect_time:\u00a0\u00a0\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 download_speed:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 neubot_version:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 platform:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 remote_address:\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 test_name:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 timestamp:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 long,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 upload_speed:\u00a0\u00a0\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 latency:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 uuid:\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 asnum:\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 region:\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 city:\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 hour:\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 month:\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 year:\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 weekday:\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day:\u00a0\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 filedate: chararray\r\n);\r\n\r\n--\r\n-- A1: Internet Providers in 'Barcelona' or 'Madrid' where speedtests were conducted\r\n--\r\n\r\nSpeedTests = FILTER @ BY (test_name matches '.*speedtest.*');\r\n\r\nSpeedTests = FILTER @ BY (\r\n\u00a0\u00a0\u00a0 city matches '.*Barcelona.*' OR\r\n\u00a0\u00a0\u00a0 city matches '.*Madrid.*'\r\n);\r\n\r\nProviders = FOREACH @ GENERATE city, client_provider;\r\nProviders = DISTINCT @;\r\n\r\nDUMP @;<\/pre>\n<\/div>\n<ul>\n<li>List the names and the IP ranges of the internet providers\u00a0located in Barcelona. For this you need to use the IPtoNumber user defined function (cf. NeubotTestsUDFs.jar).<\/li>\n<\/ul>\n<pre>NeubotTests = LOAD 'NeubotTests' using PigStorage(';') as (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_address: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_country: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lon: float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lat: float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_provider: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 mlabservername:\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connect_time:\u00a0\u00a0\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 download_speed:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 neubot_version:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 platform:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 remote_address:\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 test_name:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 timestamp:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 long,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 upload_speed:\u00a0\u00a0\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 latency:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 uuid:\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 asnum:\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 region:\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 city:\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 hour:\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 month:\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 year:\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 weekday:\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day:\u00a0\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 filedate: chararray\r\n);\r\n\r\n--\r\n-- A2: Internet Providers in Barcelona and their IP range based on the speedtests observations\r\n--\r\n\r\nSpeedTests = FILTER @ BY (test_name matches '.*speedtest.*');\r\n\r\nSpeedTests = FILTER @ BY (\r\n\u00a0\u00a0\u00a0 city matches '.*Barcelona.*'\r\n);\r\n\r\nProviders = FOREACH @ GENERATE\r\n    city,\r\n    client_provider,\r\n    IPtoNumber(client_address) AS ip\r\n;\r\n\r\nProviders = GROUP @ BY client_provider;\r\n\r\nProviders_IP_Range = FOREACH @ GENERATE\r\n    group,\r\n    NumberToIP( MIN(Providers.ip) ),\r\n    NumberToIP( MAX(Providers.ip) )\r\n;\r\n\r\nDUMP @;<\/pre>\n<ul>\n<li>Group the speedtest based on the user network infrastructure (e.g., 3G\/4G vs ADSL). For this\u00a0you can\u00a0assume some max bandwidth (e.g., 21Mb\/sec for ADSL).<\/li>\n<\/ul>\n<pre>NeubotTests = LOAD 'NeubotTests' using PigStorage(';') as (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_address: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_country: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lon: float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lat: float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_provider: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 mlabservername:\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connect_time:\u00a0\u00a0\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 download_speed:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 neubot_version:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 platform:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 remote_address:\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 test_name:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 timestamp:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 long,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 upload_speed:\u00a0\u00a0\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 latency:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 uuid:\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 asnum:\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 region:\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 city:\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 hour:\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 month:\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 year:\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 weekday:\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day:\u00a0\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 filedate: chararray\r\n);\r\n\r\n--\r\n-- A3: Speedtests (conducted in Barcelona) organized by network type: Mobile vs ADSL\r\n--\r\n\r\nSpeedTests = FILTER @ BY (test_name matches '.*speedtest.*');\r\nSpeedTests = FILTER @ BY (\r\n\u00a0\u00a0\u00a0 city matches '.*Barcelona.*'\r\n);\r\n\r\nSPLIT @ INTO\r\n\u00a0\u00a0\u00a0 Mobile_Tests IF (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 download_speed &gt; 21000000 -- 21 Mb \/ sec\r\n\u00a0\u00a0\u00a0 ),\r\n\r\n\u00a0\u00a0\u00a0 ADSL_Tests IF (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 download_speed &lt;= 21000000 -- 21 Mb \/ sec\r\n\u00a0\u00a0\u00a0 )\r\n;\r\n\r\nMobileSpeeds = FOREACH Mobile_Tests GENERATE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0CEIL(download_speed \/ 1000000) AS download_speed,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0'mobile' AS network_type: chararray\r\n;\r\n\r\nADSLSpeeds = FOREACH ADSL_Tests GENERATE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0CEIL(download_speed \/ 1000000) AS download_speed,\r\n\u00a0\u00a0\u00a0\u00a0 'adsl' AS network_type: chararray\r\n;\r\n\r\nSpeeds = UNION MobileSpeeds, ADSLSpeeds;\r\nSpeeds = GROUP @ BY (download_speed, network_type);\r\nSpeeds = FOREACH @ GENERATE\r\n\u00a0\u00a0\u00a0\u00a0 CONCAT( (chararray) group.download_speed, ' mb\/sec' ),\r\n\u00a0\u00a0\u00a0\u00a0 group.network_type,\r\n\u00a0\u00a0\u00a0\u00a0 COUNT(Speeds)\r\n;\r\n\r\nDUMP @;<\/pre>\n<ul>\n<li>Find the user that realized the maximum number of tests. For this user, produce a table showing the evolution of her\/his download\/upload speeds.<\/li>\n<\/ul>\n<pre>NeubotTests = LOAD 'NeubotTests' using PigStorage(';') as (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_address: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_country: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lon: float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lat: float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 client_provider: chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 mlabservername:\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 connect_time:\u00a0\u00a0\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 download_speed:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 neubot_version:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 platform:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 remote_address:\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 test_name:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 timestamp:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 long,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 upload_speed:\u00a0\u00a0\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 latency:\u00a0 float,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 uuid:\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 asnum:\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 region:\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 city:\u00a0\u00a0\u00a0\u00a0 chararray,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 hour:\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 month:\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 year:\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 weekday:\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 day:\u00a0\u00a0\u00a0\u00a0\u00a0 int,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 filedate: chararray\r\n);\r\n\r\n--\r\n-- Determines the user that realized the maximum number of tests and \r\n-- obtain his\/her download_speed log\r\n--\r\n\r\nTests = FILTER @ BY (test_name matches '.*speedtest.*');\r\n\r\nTests_In_Barcelona = FILTER @ BY (\r\n\u00a0\u00a0\u00a0 city matches '.*Barcelona.*'\r\n);\r\n\r\nTests_Per_User = GROUP Tests_In_Barcelona BY uuid;\r\n\r\nTests_Per_User = FOREACH @ GENERATE\r\n\u00a0\u00a0\u00a0 group AS uuid,\r\n\u00a0\u00a0\u00a0 COUNT(Tests_In_Barcelona) AS numberOfTests\r\n;\r\n\r\nMAX_NUM_TESTS = GROUP @ ALL;\r\nMAX_NUM_TESTS = FOREACH @ GENERATE\r\n\u00a0\u00a0\u00a0 MAX( Tests_Per_User.numberOfTests) AS numberOfTests\r\n;\r\n\r\nTOP_1_USER = JOIN\r\n\u00a0\u00a0\u00a0\u00a0Tests_Per_User BY numberOfTests,\r\n\u00a0\u00a0\u00a0 MAX_NUM_TESTS\u00a0 BY numberOfTests\r\n;\r\n\r\nTOP_1_USER = FOREACH @ GENERATE Tests_Per_User::uuid AS uuid;\r\n\r\nTOP_1_USER_TESTS = JOIN\r\n\u00a0\u00a0\u00a0\u00a0Tests BY uuid,\r\n\u00a0\u00a0\u00a0\u00a0TOP_1_USER BY uuid\r\n;\r\n\r\nTOP_1_USER_TESTS = FOREACH @ GENERATE\r\n\u00a0\u00a0\u00a0\u00a0Tests::uuid AS uuid,\r\n\u00a0\u00a0\u00a0\u00a0Tests::city AS city,\r\n\u00a0\u00a0\u00a0\u00a0Tests::timestamp AS timestamp,\r\n\u00a0\u00a0\u00a0\u00a0Tests::download_speed AS download_speed\r\n;\r\n\r\nTOP_1_USER_TESTS = ORDER @ BY timestamp;\r\n\r\nSTORE @ INTO 'Top_1_User' USING PigStorage(',');<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Filter the speedtest conducted in Barcelona or&nbsp;Madrid. Then list the internet providers&nbsp;working in those cities. NeubotTests = LOAD &#8216;NeubotTests&#8217; using PigStorage(&#8216;;&#8217;) as ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; client_address: chararray, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; client_country: chararray, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lon: float, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lat: float, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; client_provider: chararray, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mlabservername:&nbsp; chararray, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; connect_time:&nbsp;&nbsp;&nbsp; float, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; download_speed:&nbsp; float, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; neubot_version:&nbsp; float, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; platform:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; chararray, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; remote_address:&nbsp; [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":17,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-82","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/pages\/82","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/comments?post=82"}],"version-history":[{"count":1,"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/pages\/82\/revisions"}],"predecessor-version":[{"id":83,"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/pages\/82\/revisions\/83"}],"up":[{"embeddable":true,"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/pages\/17"}],"wp:attachment":[{"href":"http:\/\/vargas-solar.com\/bigdata-visualisation\/wp-json\/wp\/v2\/media?parent=82"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}