Order by date time in pig script

Question

After pig 0.11.0, datetime basic variable type is introduced for processing. In my case i have to order by date time. I used this way

data = LOAD 'database_name.table_name' USING org.apache.hcatalog.pig.HCatLoader() AS (id:chararray,name:chararray,birth_date_time:chararray);

selected_data = FOREACH data GENERATE id, name,ToDate(birth_date_time,'yyyy-MM-dd HH:mm:ss') AS birth_date_time;

ordered_data = ORDER selected_data BY birth_date_time DESC;

DUMP ordered_data;

But i t doesn't work. throws this error

org.apache.pig.impl.logicalLayer.FrontendException: ERROR 1066: Unable to open iterator for alias cba_ordered. Backend error : Unable to recreate exception from backed error: AttemptID:attempt_1452577118821_0005_m_000000_3 Info:Error: org.joda.time.DateTime.compareTo(Lorg/joda/time/ReadableInstant;)I at org.apache.pig.PigServer.openIterator(PigServer.java:872) at org.apache.pig.tools.grunt.GruntParser.processDump(GruntParser.java:774) at org.apache.pig.tools.pigscript.parser.PigScriptParser.parse(PigScriptParser.java:372) at org.apache.pig.tools.grunt.GruntParser.parseStopOnError(GruntParser.java:198) at org.apache.pig.tools.grunt.GruntParser.parseStopOnError(GruntParser.java:173) at org.apache.pig.tools.grunt.Grunt.exec(Grunt.java:84) at org.apache.pig.Main.run(Main.java:607) at org.apache.pig.Main.main(Main.java:156) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.util.RunJar.main(RunJar.java:212) Caused by: org.apache.pig.backend.executionengine.ExecException: ERROR 2997: Unable to recreate exception from backed error: AttemptID:attempt_1452577118821_0005_m_000000_3 Info:Error: org.joda.time.DateTime.compareTo(Lorg/joda/time/ReadableInstant;)I at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.Launcher.getErrorMessages(Launcher.java:217) at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.Launcher.getStats(Launcher.java:151) at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.MapReduceLauncher.launchPig(MapReduceLauncher.java:429) at org.apache.pig.PigServer.launchPlan(PigServer.java:1324) at org.apache.pig.PigServer.executeCompiledLogicalPlan(PigServer.java:1309) at org.apache.pig.PigServer.storeEx(PigServer.java:980) at org.apache.pig.PigServer.store(PigServer.java:944) at org.apache.pig.PigServer.openIterator(PigServer.java:857) ... 12 more

How can we order by date_time field?

Maybe it is just a typo here at SO, but you load into data as birth_date_time, but then try to convert as ToDate(birth_date-time, ...). So the column names differ in _ vs. - in the end. Not super likely that this is causing the error, but maybe worth double checking. — LiMuBei
that is typo error while typing this question. I will correct it. Question is can we use Order method for date time? — Buru
Are you sure the date conversion worked? Can you provide some sample input data? — LiMuBei

Ramesh KB Ramesh KB · Accepted Answer · 2016-05-25T06:36:17

I will suggest you to use ToUnixTime(datetime) function to order date column.

Please check below and do like this.

data = LOAD 'database_name.table_name' USING org.apache.hcatalog.pig.HCatLoader() AS (id:chararray,name:chararray,birth_date_time:chararray);

selected_data = FOREACH data { 
bdt = (datetime)ToDate(birth_date_time,'yyyy-MM-dd HH:mm:ss');
GENERATE id, name, bdt AS birth_date_time, ToUnixTime(bdt) AS birth_date_time_unix;
};

ordered_data = ORDER selected_data BY birth_date_time_unix DESC;

DUMP ordered_data;

Please let me know if it works.

Order by date time in pig script

1 Answers