ok thanks.... I should have caught that.
/tom On Thu, Apr 1, 2010 at 2:13 PM, Carl Steinbach <c...@cloudera.com> wrote: > Hi Tom, > > "Unix Time" is defined as the number of *seconds* since January 1, 1970. It > looks like the data you have in cola is in milliseconds. You need to divide > this value by 1000 before calling from_unixtime() on the result. > > Thanks. > > Carl > > > On Thu, Apr 1, 2010 at 2:02 PM, tom kersnick <hiveu...@gmail.com> wrote: > >> Thanks, but there is something fishy going on. >> >> Im using hive 0.5.0 with hadoop 0.20.1 >> >> I tried the column as both a bigint and a string. According the hive ddl: >> >> string >> >> from_unixtime(int unixtime) >> >> Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) >> to a string representing the timestamp of that moment in the current system >> time zone in the format of "1970-01-01 00:00:00" >> >> It looks like the input is int, that would be too small for my >> 1270145333155 timestamp. >> >> Any ideas? >> >> Example below: >> >> /tom >> >> >> hive> describe ut; >> OK >> cola bigint >> colb string >> Time taken: 0.101 seconds >> >> >> hive> select * from ut; >> OK >> 1270145333155 tuesday >> Time taken: 0.065 seconds >> >> hive> select from_unixtime(cola,'yyyy-MM-dd HH:mm:ss'),colb from ut; >> >> Total MapReduce jobs = 1 >> Launching Job 1 out of 1 >> Number of reduce tasks is set to 0 since there's no reduce operator >> Starting Job = job_201003031204_0083, Tracking URL = >> http://master:50030/jobdetails.jsp?jobid=job_201003031204_0083 >> Kill Command = /usr/local/hadoop/bin/../bin/hadoop job >> -Dmapred.job.tracker=master:54311 -kill job_201003031204_0083 >> 2010-04-01 16:57:32,407 Stage-1 map = 0%, reduce = 0% >> 2010-04-01 16:57:45,577 Stage-1 map = 100%, reduce = 0% >> 2010-04-01 16:57:48,605 Stage-1 map = 100%, reduce = 100% >> Ended Job = job_201003031204_0083 >> OK >> 42219-04-22 00:05:55 tuesday >> Time taken: 18.066 seconds >> >> >> hive> describe ut; >> OK >> cola string >> colb string >> Time taken: 0.077 seconds >> >> >> hive> select * from ut; >> OK >> 1270145333155 tuesday >> Time taken: 0.065 seconds >> >> >> hive> select from_unixtime(cola,'yyyy-MM-dd HH:mm:ss'),colb from ut; >> FAILED: Error in semantic analysis: line 1:7 Function Argument Type >> Mismatch from_unixtime: Looking for UDF "from_unixtime" with parameters >> [class org.apache.hadoop.io.Text, class org.apache.hadoop.io.Text] >> >> >> >> >> >> On Thu, Apr 1, 2010 at 1:37 PM, Carl Steinbach <c...@cloudera.com> wrote: >> >>> Hi Tom, >>> >>> I think you want to use the from_unixtime UDF: >>> >>> hive> describe function extended from_unixtime; >>> describe function extended from_unixtime; >>> OK >>> from_unixtime(unix_time, format) - returns unix_time in the specified >>> format >>> Example: >>> > SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss') FROM src LIMIT 1; >>> '1970-01-01 00:00:00' >>> Time taken: 0.647 seconds >>> hive> >>> >>> Thanks. >>> >>> Carl >>> >>> On Thu, Apr 1, 2010 at 1:11 PM, tom kersnick <hiveu...@gmail.com> wrote: >>> >>>> hive> describe ut; >>>> OK >>>> time bigint >>>> day string >>>> Time taken: 0.128 seconds >>>> hive> select * from ut; >>>> OK >>>> 1270145333155 tuesday >>>> Time taken: 0.085 seconds >>>> >>>> When I run this simple query, I'm getting a NULL for the time column >>>> with data type bigint. >>>> >>>> hive> select unix_timestamp(time),day from ut; >>>> Total MapReduce jobs = 1 >>>> Launching Job 1 out of 1 >>>> Number of reduce tasks is set to 0 since there's no reduce operator >>>> Starting Job = job_201003031204_0080, Tracking URL = >>>> http://master:50030/jobdetails.jsp?jobid=job_201003031204_0080 >>>> Kill Command = /usr/local/hadoop/bin/../bin/hadoop job >>>> -Dmapred.job.tracker=master:54311 -kill job_201003031204_0080 >>>> 2010-04-01 16:03:54,024 Stage-1 map = 0%, reduce = 0% >>>> 2010-04-01 16:04:06,128 Stage-1 map = 100%, reduce = 0% >>>> 2010-04-01 16:04:09,150 Stage-1 map = 100%, reduce = 100% >>>> Ended Job = job_201003031204_0080 >>>> OK >>>> NULL tuesday >>>> Time taken: 16.981 seconds >>>> >>>> Any ideas? >>>> >>>> Thanks! >>>> >>>> /tom >>>> >>> >>> >> >
