@Andres Urrego There were multiple issues on the script that you posted. Also, thank you for sharing the test data, I was able to spot other issues too. 1. Your input file in pig is a CSV but you are using HCatLoader. This is incorrect, Hcatloader is only used when you want to load data in Pig from a Hive table. In you case you can put the data in a folder in HDFS and then load data from that path in Pig. 2. Since you loaded data in Pig without specifying the schema, then you must refer to the columns by position like $0, $1 and so on instead of referring to them by name like start_date,duration etc. Refer this page to understand the LOAD statement in Pig 3. When using ToDate function in Pig, either your data must match the default date format, or you must specify the date format that you have in you data. Refer to the script I have added below to see the same. Click here to understand the syntax of ToDate() in Pig. Click here to understand how to create date format string 4. When storing data from Pig to Hive using HCatStorer, ensure that the alias in Pig has fields names and data types to avoid any mismatch or type casting issues. After going through the sample data and replicating the issue, I was able to solve and load the data correctly. Here is the Pig script: july = LOAD '/hdfspath/july.csv' USING PigStorage(',');
july = filter july by $0!='start_date'; //to remove header line
july_cl = FOREACH july
GENERATE GetDay(ToDate((chararray)$0,'yyyy-MM-dd HH:mm')) as day:int,
$1 as start_station:chararray,(int)$4 as duration:int;
jul_cl = FILTER july_cl BY day==31;
july_gr = GROUP jul_cl BY (day,start_station);
july_result = FOREACH july_gr {
total_dura = SUM(jul_cl.duration);
avg_dura = AVG(jul_cl.duration);
qty_trips = COUNT(jul_cl);
GENERATE FLATTEN( group) AS (day, code_station),
(double)total_dura as (total_dura:double),
(float)avg_dura as (avg_dura:float),(int)qty_trips as (qty_trips:int);
}
STORE july_result INTO 'poc.july_analysis' USING org.apache.hive.hcatalog.pig.HCatStorer(); In the above script you were trying to store INTO 'poc.july_analysis'. Ensure there exists a database in hive called 'poc' and there exists the 'july_analysis' table in that poc database. If the poc database does not exist, then either create it or avoid using it(create july_analysis in default hive database). Here is the Hive Table DDL I used: CREATE TABLE july_analysis(
day int,
code_station string,
total_dura double,
avg_dura float,
qty_trips int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
... View more
