You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Christopher Matta <cm...@mapr.com> on 2015/05/29 20:40:25 UTC
convert unix timestamp
I need to be able to convert a unix timestamp to a drill timestamp.
I can use the from_unixtime function return what seems like a timestamp, or
a string formatted correctly, however it seems like it doesn’t return it as
a string or as a timestamp type:
0: jdbc:drill:zk=sen11:5181,sen12:5181> select
from_unixtime(1432912733) from `sys`.`version`;
+----------------------+
| EXPR$0 |
+----------------------+
| 2015-05-29 15:18:53 |
+----------------------+
1 row selected (0.134 seconds)
Trying to run date-specific functions on the result:
0: jdbc:drill:zk=sen11:5181,sen12:5181> select datepart('hour',
from_unixtime(1432912733)) from `sys`.`version`;
Error: PARSE ERROR: From line 1, column 8 to line 1, column 50: No
match found for function signature datepart(<CHARACTER>, <ANY>)
[Error Id: aa906f4b-fa49-4a3d-9a7c-0063c7c6c97b on
se-node10.se.lab:31010] (state=,code=0)
Trying to cast it to a timestamp returns an error:
0: jdbc:drill:zk=sen11:5181,sen12:5181> select
CAST(from_unixtime(1432912733) as TIMESTAMP) from `sys`.`version`;
Error: SYSTEM ERROR:
org.apache.drill.exec.exception.SchemaChangeException: Failure while
trying to materialize incoming schema. Errors:
Error in expression at index -1. Error: Missing function
implementation: [castBIGINT(VAR16CHAR-OPTIONAL)]. Full expression:
--UNKNOWN EXPRESSION--..
Fragment 0:0
[Error Id: 7e75d4b9-a478-40cd-80b4-fda27815b9ef on
se-node10.se.lab:31010] (state=,code=0)
Any ideas?
Chris Mattacmatta@mapr.com
215-701-3146
convert unix timestamp
Posted by Christopher Matta <cm...@mapr.com>.
Yep, that was it, thanks Christine:
0: jdbc:drill:zk=sen11:5181,sen12:5181> select date_part('hour',
CAST(to_timestamp(1432912733) as timestamp)) from `sys`.`version`;
+---------+
| EXPR$0 |
+---------+
| 15 |
+---------+
Chris Matta
cmatta@mapr.com <javascript:_e(%7B%7D,'cvml','cmatta@mapr.com');>
215-701-3146
On Fri, May 29, 2015 at 4:22 PM, Kristine Hahn <khahn@maprtech.com
<javascript:_e(%7B%7D,'cvml','khahn@maprtech.com');>> wrote:
> I think TO_TIMESTAMP is what you're looking for. Please see
> http://drill.apache.org/docs/data-type-conversion/#to_timestamp.
>
> Kristine Hahn
> Sr. Technical Writer
> 415-497-8107 @krishahn
>
>
> On Fri, May 29, 2015 at 11:40 AM, Christopher Matta <cmatta@mapr.com
> <javascript:_e(%7B%7D,'cvml','cmatta@mapr.com');>> wrote:
>
> > I need to be able to convert a unix timestamp to a drill timestamp.
> >
> > I can use the from_unixtime function return what seems like a timestamp,
> or
> > a string formatted correctly, however it seems like it doesn’t return it
> as
> > a string or as a timestamp type:
> >
> > 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
> > from_unixtime(1432912733) from `sys`.`version`;
> > +----------------------+
> > | EXPR$0 |
> > +----------------------+
> > | 2015-05-29 15:18:53 |
> > +----------------------+
> > 1 row selected (0.134 seconds)
> >
> > Trying to run date-specific functions on the result:
> >
> > 0: jdbc:drill:zk=sen11:5181,sen12:5181> select datepart('hour',
> > from_unixtime(1432912733)) from `sys`.`version`;
> > Error: PARSE ERROR: From line 1, column 8 to line 1, column 50: No
> > match found for function signature datepart(<CHARACTER>, <ANY>)
> >
> > [Error Id: aa906f4b-fa49-4a3d-9a7c-0063c7c6c97b on
> > se-node10.se.lab:31010] (state=,code=0)
> >
> > Trying to cast it to a timestamp returns an error:
> >
> > 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
> > CAST(from_unixtime(1432912733) as TIMESTAMP) from `sys`.`version`;
> > Error: SYSTEM ERROR:
> > org.apache.drill.exec.exception.SchemaChangeException: Failure while
> > trying to materialize incoming schema. Errors:
> >
> > Error in expression at index -1. Error: Missing function
> > implementation: [castBIGINT(VAR16CHAR-OPTIONAL)]. Full expression:
> > --UNKNOWN EXPRESSION--..
> >
> > Fragment 0:0
> >
> > [Error Id: 7e75d4b9-a478-40cd-80b4-fda27815b9ef on
> > se-node10.se.lab:31010] (state=,code=0)
> >
> > Any ideas?
> >
> > Chris Mattacmatta@mapr.com
> <javascript:_e(%7B%7D,'cvml','Mattacmatta@mapr.com');>
> > 215-701-3146
> >
> >
>
--
Chris Matta
cmatta@mapr.com
215-701-3146
Re: convert unix timestamp
Posted by Andries Engelbrecht <ae...@maprtech.com>.
It seems the output from_unixtime seems to be VAR16CHAR, which creates issues with any conversion function.
Is that correct? Or are the conversion functions for the output by from_unixtime?
—Andries
On May 29, 2015, at 1:22 PM, Kristine Hahn <kh...@maprtech.com> wrote:
> I think TO_TIMESTAMP is what you're looking for. Please see
> http://drill.apache.org/docs/data-type-conversion/#to_timestamp.
>
> Kristine Hahn
> Sr. Technical Writer
> 415-497-8107 @krishahn
>
>
> On Fri, May 29, 2015 at 11:40 AM, Christopher Matta <cm...@mapr.com> wrote:
>
>> I need to be able to convert a unix timestamp to a drill timestamp.
>>
>> I can use the from_unixtime function return what seems like a timestamp, or
>> a string formatted correctly, however it seems like it doesn’t return it as
>> a string or as a timestamp type:
>>
>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
>> from_unixtime(1432912733) from `sys`.`version`;
>> +----------------------+
>> | EXPR$0 |
>> +----------------------+
>> | 2015-05-29 15:18:53 |
>> +----------------------+
>> 1 row selected (0.134 seconds)
>>
>> Trying to run date-specific functions on the result:
>>
>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select datepart('hour',
>> from_unixtime(1432912733)) from `sys`.`version`;
>> Error: PARSE ERROR: From line 1, column 8 to line 1, column 50: No
>> match found for function signature datepart(<CHARACTER>, <ANY>)
>>
>> [Error Id: aa906f4b-fa49-4a3d-9a7c-0063c7c6c97b on
>> se-node10.se.lab:31010] (state=,code=0)
>>
>> Trying to cast it to a timestamp returns an error:
>>
>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
>> CAST(from_unixtime(1432912733) as TIMESTAMP) from `sys`.`version`;
>> Error: SYSTEM ERROR:
>> org.apache.drill.exec.exception.SchemaChangeException: Failure while
>> trying to materialize incoming schema. Errors:
>>
>> Error in expression at index -1. Error: Missing function
>> implementation: [castBIGINT(VAR16CHAR-OPTIONAL)]. Full expression:
>> --UNKNOWN EXPRESSION--..
>>
>> Fragment 0:0
>>
>> [Error Id: 7e75d4b9-a478-40cd-80b4-fda27815b9ef on
>> se-node10.se.lab:31010] (state=,code=0)
>>
>> Any ideas?
>>
>> Chris Mattacmatta@mapr.com
>> 215-701-3146
>>
>>
Re: convert unix timestamp
Posted by Hao Zhu <hz...@maprtech.com>.
Could the extra bytes coming from from_unixtime hive UDF cause the
conversion issue?
create table t_timestamp as
select TO_TIMESTAMP(unix_timestamp()) as ts from `sys`.`version`;
create table t_timestamp2 as
select from_unixtime(unix_timestamp()) as ts from `sys`.`version`;
# parquet-schema t_timestamp/0_0_0.parquet
message root {
required int64 ts (TIMESTAMP);
}
parquet-schema t_timestamp2/0_0_0.parquet
message root {
optional binary ts;
}
> select convert_from(ts,'UTF8') from dfs.tmp.t_timestamp2;
+-------------------------------------------+
| EXPR$0 |
+-------------------------------------------+
| ��2015-05-29 20:51:18 |
+-------------------------------------------+
1 row selected (0.084 seconds)
Thanks,
Hao
On Fri, May 29, 2015 at 1:46 PM, Jason Altekruse <al...@gmail.com>
wrote:
> Tried taking a look at the function to see what the issue was,
> from_unixtime is actually a Hive UDF that is just on the classpath and
> available by default in Drill. It does look like Andries said that it is
> returning var16char, which might be a bug. The fact that it is trying to
> cast to bigInt despite it not appearing in the query seems to be an attempt
> to use an intermediate implicit cast that cannot actually be executed.
>
Re: convert unix timestamp
Posted by Jason Altekruse <al...@gmail.com>.
Tried taking a look at the function to see what the issue was,
from_unixtime is actually a Hive UDF that is just on the classpath and
available by default in Drill. It does look like Andries said that it is
returning var16char, which might be a bug. The fact that it is trying to
cast to bigInt despite it not appearing in the query seems to be an attempt
to use an intermediate implicit cast that cannot actually be executed.
Re: convert unix timestamp
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Good call Kristine!!!
Seems to_timestamp is the better way to go that using from_unixtime.
select date_part('hour', to_timestamp(1432912733)) from `sys`.`version`;
+---------+
| EXPR$0 |
+---------+
| 15 |
+---------+
1 row selected (0.171 seconds)
—Andries
On May 29, 2015, at 1:22 PM, Kristine Hahn <kh...@maprtech.com> wrote:
> I think TO_TIMESTAMP is what you're looking for. Please see
> http://drill.apache.org/docs/data-type-conversion/#to_timestamp.
>
> Kristine Hahn
> Sr. Technical Writer
> 415-497-8107 @krishahn
>
>
> On Fri, May 29, 2015 at 11:40 AM, Christopher Matta <cm...@mapr.com> wrote:
>
>> I need to be able to convert a unix timestamp to a drill timestamp.
>>
>> I can use the from_unixtime function return what seems like a timestamp, or
>> a string formatted correctly, however it seems like it doesn’t return it as
>> a string or as a timestamp type:
>>
>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
>> from_unixtime(1432912733) from `sys`.`version`;
>> +----------------------+
>> | EXPR$0 |
>> +----------------------+
>> | 2015-05-29 15:18:53 |
>> +----------------------+
>> 1 row selected (0.134 seconds)
>>
>> Trying to run date-specific functions on the result:
>>
>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select datepart('hour',
>> from_unixtime(1432912733)) from `sys`.`version`;
>> Error: PARSE ERROR: From line 1, column 8 to line 1, column 50: No
>> match found for function signature datepart(<CHARACTER>, <ANY>)
>>
>> [Error Id: aa906f4b-fa49-4a3d-9a7c-0063c7c6c97b on
>> se-node10.se.lab:31010] (state=,code=0)
>>
>> Trying to cast it to a timestamp returns an error:
>>
>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
>> CAST(from_unixtime(1432912733) as TIMESTAMP) from `sys`.`version`;
>> Error: SYSTEM ERROR:
>> org.apache.drill.exec.exception.SchemaChangeException: Failure while
>> trying to materialize incoming schema. Errors:
>>
>> Error in expression at index -1. Error: Missing function
>> implementation: [castBIGINT(VAR16CHAR-OPTIONAL)]. Full expression:
>> --UNKNOWN EXPRESSION--..
>>
>> Fragment 0:0
>>
>> [Error Id: 7e75d4b9-a478-40cd-80b4-fda27815b9ef on
>> se-node10.se.lab:31010] (state=,code=0)
>>
>> Any ideas?
>>
>> Chris Mattacmatta@mapr.com
>> 215-701-3146
>>
>>
Re: convert unix timestamp
Posted by Kristine Hahn <kh...@maprtech.com>.
I think TO_TIMESTAMP is what you're looking for. Please see
http://drill.apache.org/docs/data-type-conversion/#to_timestamp.
Kristine Hahn
Sr. Technical Writer
415-497-8107 @krishahn
On Fri, May 29, 2015 at 11:40 AM, Christopher Matta <cm...@mapr.com> wrote:
> I need to be able to convert a unix timestamp to a drill timestamp.
>
> I can use the from_unixtime function return what seems like a timestamp, or
> a string formatted correctly, however it seems like it doesn’t return it as
> a string or as a timestamp type:
>
> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
> from_unixtime(1432912733) from `sys`.`version`;
> +----------------------+
> | EXPR$0 |
> +----------------------+
> | 2015-05-29 15:18:53 |
> +----------------------+
> 1 row selected (0.134 seconds)
>
> Trying to run date-specific functions on the result:
>
> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select datepart('hour',
> from_unixtime(1432912733)) from `sys`.`version`;
> Error: PARSE ERROR: From line 1, column 8 to line 1, column 50: No
> match found for function signature datepart(<CHARACTER>, <ANY>)
>
> [Error Id: aa906f4b-fa49-4a3d-9a7c-0063c7c6c97b on
> se-node10.se.lab:31010] (state=,code=0)
>
> Trying to cast it to a timestamp returns an error:
>
> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
> CAST(from_unixtime(1432912733) as TIMESTAMP) from `sys`.`version`;
> Error: SYSTEM ERROR:
> org.apache.drill.exec.exception.SchemaChangeException: Failure while
> trying to materialize incoming schema. Errors:
>
> Error in expression at index -1. Error: Missing function
> implementation: [castBIGINT(VAR16CHAR-OPTIONAL)]. Full expression:
> --UNKNOWN EXPRESSION--..
>
> Fragment 0:0
>
> [Error Id: 7e75d4b9-a478-40cd-80b4-fda27815b9ef on
> se-node10.se.lab:31010] (state=,code=0)
>
> Any ideas?
>
> Chris Mattacmatta@mapr.com
> 215-701-3146
>
>