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 2014/11/26 18:17:04 UTC
Convert string to timestamp or unix timestamp to timestamp type
If I have a string like this (from Twitter): “Mon Nov 24 18:18:22 +0000
2014” is there a way to cast that as a timestamp, or date type?
Twitter also provides: "timestamp_ms": "1416853102087"
Any way to cast that as a timestamp with Drill?
Chris Matta
cmatta@mapr.com
215-701-3146
Re: Convert string to timestamp or unix timestamp to timestamp type
Posted by Mehant Baid <ba...@gmail.com>.
Currently cast('input_string' as timestamp) will work only if your input
is in the following format: "yyyy-mm-dd hh:mm:ss.sss".
If you have a custom Timestamp input string you should be able to use
to_timestamp().
Here is an example for the string you mentioned:
0: jdbc:drill:zk=local> select to_timestamp('Mon Nov 24 18:18:22
2014', 'EEE MMM dd HH:mm:ss YYYY') from dfs.`/tmp/a.json`;
+------------+
| EXPR$0 |
+------------+
| 2014-11-24 18:18:22.0 |
+------------+
For the format specifiers to use look at:
http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html
One thing to note is that I don't have timezone information in my input
string, this is because Drill does not support Timestamp with time zone
data type, currently we seem to be doing something with the timezone
which is a bug and I will file a JIRA so that we completely ignore any
timezone information
Thanks
Mehant
On 11/26/14, 9:17 AM, Christopher Matta wrote:
> If I have a string like this (from Twitter): “Mon Nov 24 18:18:22 +0000
> 2014” is there a way to cast that as a timestamp, or date type?
>
> Twitter also provides: "timestamp_ms": "1416853102087"
> Any way to cast that as a timestamp with Drill?
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
>
Re: Convert string to timestamp or unix timestamp to timestamp type
Posted by Christopher Matta <cm...@mapr.com>.
I’ve tried doing that:
0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
CAST(FROM_UNIXTIME(t.`timestamp_ms`/1000) as timestamp) as `timestamp`
from `twitter_tweets` t limit 10;
Query failed: Failure while running fragment., Line 126, Column 19:
"value" is neither a method, a field, nor a member class of
"org.apache.drill.exec.expr.holders.NullableVar16CharHolder" [
16302cda-0325-40ba-9a49-fe6f513de2a6 on ip-172-16-1-177:31010 ]
Error: exception while executing query: Failure while executing query.
(state=,code=0)
No luck casting the return value of FROM_UNIXTIME() as a timestamp.
Chris Matta
cmatta@mapr.com
215-701-3146
On Wed, Nov 26, 2014 at 2:06 PM, Jim Bates <jb...@maprtech.com> wrote:
> I have had issues with the values that come out of functions not being cast
> or being cast as ANY. I had better results if I added a cast around the
> result making sure it comes out as intended. Mine was with the to_timestamp
> function. the result didn't end up a timestamp till I cast it as a
> timestamp.
>
> executionTime = 2014-11-24 08:29:01 AM
>
> cast(to_timestamp( `executionTime`,'YYYY-MM-dd hh:mm:ss a') as timestamp)
> as `executionTime`
>
> ordering and comparisons worked after that.
>
> On Wed, Nov 26, 2014 at 11:38 AM, Christopher Matta <cm...@mapr.com>
> wrote:
>
> > I just figured this out:
> >
> > create or replace view mfs.views.twitter_tweets as select
> > CAST(t.`id` as BIGINT) as `id`,
> > CAST(t.`user`.`id` as BIGINT) as `user_id`,
> > CAST(t.`text` as VARCHAR(140)) as `tweet`,
> > CAST(t.`timestamp_ms` as BIGINT) as `timestamp_ms`,
> > FROM_UNIXTIME(CAST(t.`timestamp_ms` as BIGINT)/1000) as `created_at`,
> > CAST(t.`favorited` as BOOLEAN) as `favorited`,
> > CAST(t.`favorite_count` as INT) as `favorite_count`,
> > CAST(t.`retweeted` as BOOLEAN) as `retweeted`,
> > CAST(t.`retweet_count` as INT) as `retweet_count`,
> > CAST(t.`possibly_sensitive` as BOOLEAN) as `possibly_sensetive`,
> > CAST(t.`place` as VARCHAR(140)) as `place`,
> > t.`coordinates`,
> > CAST(t.`source` as VARCHAR(140)) as `source`
> > from mfs.`/user/cmatta/tweets` t;
> >
> > Which seems to work:
> >
> > 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
> > t.`created_at` from `twitter_tweets` t limit 10;
> > +------------+------------+
> > | id | created_at |
> > +------------+------------+
> > | 537183914948038657 | 2014-11-25 10:00:00 |
> > | 537183920442580992 | 2014-11-25 10:00:01 |
> > | 537183920630923264 | 2014-11-25 10:00:01 |
> > | 537183922140889088 | 2014-11-25 10:00:02 |
> > | 537183922321653761 | 2014-11-25 10:00:02 |
> > | 537183922770444290 | 2014-11-25 10:00:02 |
> > | 537183923642462208 | 2014-11-25 10:00:02 |
> > | 537183925719027712 | 2014-11-25 10:00:02 |
> > | 537183926465609728 | 2014-11-25 10:00:03 |
> > | 537183926721466369 | 2014-11-25 10:00:03 |
> > +------------+------------+
> > 10 rows selected (1.621 seconds)
> > 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
> > t.`created_at` from `twitter_tweets` t ORDER BY t.`created_at` limit
> > 10;
> >
> > However, when I try and do comparisons, or order by queries:
> >
> > 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
> > t.`created_at` from `twitter_tweets` t ORDER BY t.`created_at` limit
> > 10;
> > +------------+------------+
> > | id | created_at |
> > +------------+------------+
> > Query failed: Failure while running fragment., Failure finding
> > function that runtime code generation expected. Signature:
> > compare_to( VAR16CHAR:OPTIONALVAR16CHAR:OPTIONAL, ) returns
> > INT:REQUIRED [ d5c7a01c-dddf-446c-91c0-41104ac5234c on
> > ip-172-16-1-176:31010 ]
> >
> > java.lang.RuntimeException: java.sql.SQLException: Failure while
> > executing query.
> > at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2514)
> > at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2148)
> > at sqlline.SqlLine.print(SqlLine.java:1809)
> > at sqlline.SqlLine$Commands.execute(SqlLine.java:3766)
> > at sqlline.SqlLine$Commands.sql(SqlLine.java:3663)
> > at sqlline.SqlLine.dispatch(SqlLine.java:889)
> > at sqlline.SqlLine.begin(SqlLine.java:763)
> > at sqlline.SqlLine.start(SqlLine.java:498)
> > at sqlline.SqlLine.main(SqlLine.java:460)
> >
> > This seems like Drill doesn’t respect the result of FROM_UNIXTIME as a
> > date/time type, is that correct?
> >
> > I’ve also tried to cast the results of FROM_UNIXTIME as a timestamp with
> > not much better results:
> >
> > 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
> > CAST(t.`created_at` as timestamp) as `created` from `twitter_tweets` t
> > ORDER BY `created` limit 10;
> > Query failed: Failure while running fragment., Line 126, Column 19:
> > "value" is neither a method, a field, nor a member class of
> > "org.apache.drill.exec.expr.holders.NullableVar16CharHolder" [
> > a9d7bad6-2fac-41ed-a021-8dd3297a4b65 on ip-172-16-1-173:31010 ]
> >
> > Error: exception while executing query: Failure while executing query.
> > (state=,code=0)
> >
> > Any help here would be great.
> >
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> > On Wed, Nov 26, 2014 at 12:17 PM, Christopher Matta <cm...@mapr.com>
> > wrote:
> >
> > > If I have a string like this (from Twitter): “Mon Nov 24 18:18:22 +0000
> > > 2014” is there a way to cast that as a timestamp, or date type?
> > >
> > > Twitter also provides: "timestamp_ms": "1416853102087"
> > > Any way to cast that as a timestamp with Drill?
> > >
> > > Chris Matta
> > > cmatta@mapr.com
> > > 215-701-3146
> > >
> > >
> >
>
Re: Convert string to timestamp or unix timestamp to timestamp type
Posted by Jim Bates <jb...@maprtech.com>.
I have had issues with the values that come out of functions not being cast
or being cast as ANY. I had better results if I added a cast around the
result making sure it comes out as intended. Mine was with the to_timestamp
function. the result didn't end up a timestamp till I cast it as a
timestamp.
executionTime = 2014-11-24 08:29:01 AM
cast(to_timestamp( `executionTime`,'YYYY-MM-dd hh:mm:ss a') as timestamp)
as `executionTime`
ordering and comparisons worked after that.
On Wed, Nov 26, 2014 at 11:38 AM, Christopher Matta <cm...@mapr.com> wrote:
> I just figured this out:
>
> create or replace view mfs.views.twitter_tweets as select
> CAST(t.`id` as BIGINT) as `id`,
> CAST(t.`user`.`id` as BIGINT) as `user_id`,
> CAST(t.`text` as VARCHAR(140)) as `tweet`,
> CAST(t.`timestamp_ms` as BIGINT) as `timestamp_ms`,
> FROM_UNIXTIME(CAST(t.`timestamp_ms` as BIGINT)/1000) as `created_at`,
> CAST(t.`favorited` as BOOLEAN) as `favorited`,
> CAST(t.`favorite_count` as INT) as `favorite_count`,
> CAST(t.`retweeted` as BOOLEAN) as `retweeted`,
> CAST(t.`retweet_count` as INT) as `retweet_count`,
> CAST(t.`possibly_sensitive` as BOOLEAN) as `possibly_sensetive`,
> CAST(t.`place` as VARCHAR(140)) as `place`,
> t.`coordinates`,
> CAST(t.`source` as VARCHAR(140)) as `source`
> from mfs.`/user/cmatta/tweets` t;
>
> Which seems to work:
>
> 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
> t.`created_at` from `twitter_tweets` t limit 10;
> +------------+------------+
> | id | created_at |
> +------------+------------+
> | 537183914948038657 | 2014-11-25 10:00:00 |
> | 537183920442580992 | 2014-11-25 10:00:01 |
> | 537183920630923264 | 2014-11-25 10:00:01 |
> | 537183922140889088 | 2014-11-25 10:00:02 |
> | 537183922321653761 | 2014-11-25 10:00:02 |
> | 537183922770444290 | 2014-11-25 10:00:02 |
> | 537183923642462208 | 2014-11-25 10:00:02 |
> | 537183925719027712 | 2014-11-25 10:00:02 |
> | 537183926465609728 | 2014-11-25 10:00:03 |
> | 537183926721466369 | 2014-11-25 10:00:03 |
> +------------+------------+
> 10 rows selected (1.621 seconds)
> 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
> t.`created_at` from `twitter_tweets` t ORDER BY t.`created_at` limit
> 10;
>
> However, when I try and do comparisons, or order by queries:
>
> 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
> t.`created_at` from `twitter_tweets` t ORDER BY t.`created_at` limit
> 10;
> +------------+------------+
> | id | created_at |
> +------------+------------+
> Query failed: Failure while running fragment., Failure finding
> function that runtime code generation expected. Signature:
> compare_to( VAR16CHAR:OPTIONALVAR16CHAR:OPTIONAL, ) returns
> INT:REQUIRED [ d5c7a01c-dddf-446c-91c0-41104ac5234c on
> ip-172-16-1-176:31010 ]
>
> java.lang.RuntimeException: java.sql.SQLException: Failure while
> executing query.
> at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2514)
> at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2148)
> at sqlline.SqlLine.print(SqlLine.java:1809)
> at sqlline.SqlLine$Commands.execute(SqlLine.java:3766)
> at sqlline.SqlLine$Commands.sql(SqlLine.java:3663)
> at sqlline.SqlLine.dispatch(SqlLine.java:889)
> at sqlline.SqlLine.begin(SqlLine.java:763)
> at sqlline.SqlLine.start(SqlLine.java:498)
> at sqlline.SqlLine.main(SqlLine.java:460)
>
> This seems like Drill doesn’t respect the result of FROM_UNIXTIME as a
> date/time type, is that correct?
>
> I’ve also tried to cast the results of FROM_UNIXTIME as a timestamp with
> not much better results:
>
> 0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
> CAST(t.`created_at` as timestamp) as `created` from `twitter_tweets` t
> ORDER BY `created` limit 10;
> Query failed: Failure while running fragment., Line 126, Column 19:
> "value" is neither a method, a field, nor a member class of
> "org.apache.drill.exec.expr.holders.NullableVar16CharHolder" [
> a9d7bad6-2fac-41ed-a021-8dd3297a4b65 on ip-172-16-1-173:31010 ]
>
> Error: exception while executing query: Failure while executing query.
> (state=,code=0)
>
> Any help here would be great.
>
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
> On Wed, Nov 26, 2014 at 12:17 PM, Christopher Matta <cm...@mapr.com>
> wrote:
>
> > If I have a string like this (from Twitter): “Mon Nov 24 18:18:22 +0000
> > 2014” is there a way to cast that as a timestamp, or date type?
> >
> > Twitter also provides: "timestamp_ms": "1416853102087"
> > Any way to cast that as a timestamp with Drill?
> >
> > Chris Matta
> > cmatta@mapr.com
> > 215-701-3146
> >
> >
>
Re: Convert string to timestamp or unix timestamp to timestamp type
Posted by Christopher Matta <cm...@mapr.com>.
I just figured this out:
create or replace view mfs.views.twitter_tweets as select
CAST(t.`id` as BIGINT) as `id`,
CAST(t.`user`.`id` as BIGINT) as `user_id`,
CAST(t.`text` as VARCHAR(140)) as `tweet`,
CAST(t.`timestamp_ms` as BIGINT) as `timestamp_ms`,
FROM_UNIXTIME(CAST(t.`timestamp_ms` as BIGINT)/1000) as `created_at`,
CAST(t.`favorited` as BOOLEAN) as `favorited`,
CAST(t.`favorite_count` as INT) as `favorite_count`,
CAST(t.`retweeted` as BOOLEAN) as `retweeted`,
CAST(t.`retweet_count` as INT) as `retweet_count`,
CAST(t.`possibly_sensitive` as BOOLEAN) as `possibly_sensetive`,
CAST(t.`place` as VARCHAR(140)) as `place`,
t.`coordinates`,
CAST(t.`source` as VARCHAR(140)) as `source`
from mfs.`/user/cmatta/tweets` t;
Which seems to work:
0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
t.`created_at` from `twitter_tweets` t limit 10;
+------------+------------+
| id | created_at |
+------------+------------+
| 537183914948038657 | 2014-11-25 10:00:00 |
| 537183920442580992 | 2014-11-25 10:00:01 |
| 537183920630923264 | 2014-11-25 10:00:01 |
| 537183922140889088 | 2014-11-25 10:00:02 |
| 537183922321653761 | 2014-11-25 10:00:02 |
| 537183922770444290 | 2014-11-25 10:00:02 |
| 537183923642462208 | 2014-11-25 10:00:02 |
| 537183925719027712 | 2014-11-25 10:00:02 |
| 537183926465609728 | 2014-11-25 10:00:03 |
| 537183926721466369 | 2014-11-25 10:00:03 |
+------------+------------+
10 rows selected (1.621 seconds)
0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
t.`created_at` from `twitter_tweets` t ORDER BY t.`created_at` limit
10;
However, when I try and do comparisons, or order by queries:
0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
t.`created_at` from `twitter_tweets` t ORDER BY t.`created_at` limit
10;
+------------+------------+
| id | created_at |
+------------+------------+
Query failed: Failure while running fragment., Failure finding
function that runtime code generation expected. Signature:
compare_to( VAR16CHAR:OPTIONALVAR16CHAR:OPTIONAL, ) returns
INT:REQUIRED [ d5c7a01c-dddf-446c-91c0-41104ac5234c on
ip-172-16-1-176:31010 ]
java.lang.RuntimeException: java.sql.SQLException: Failure while
executing query.
at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2514)
at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2148)
at sqlline.SqlLine.print(SqlLine.java:1809)
at sqlline.SqlLine$Commands.execute(SqlLine.java:3766)
at sqlline.SqlLine$Commands.sql(SqlLine.java:3663)
at sqlline.SqlLine.dispatch(SqlLine.java:889)
at sqlline.SqlLine.begin(SqlLine.java:763)
at sqlline.SqlLine.start(SqlLine.java:498)
at sqlline.SqlLine.main(SqlLine.java:460)
This seems like Drill doesn’t respect the result of FROM_UNIXTIME as a
date/time type, is that correct?
I’ve also tried to cast the results of FROM_UNIXTIME as a timestamp with
not much better results:
0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select t.`id`,
CAST(t.`created_at` as timestamp) as `created` from `twitter_tweets` t
ORDER BY `created` limit 10;
Query failed: Failure while running fragment., Line 126, Column 19:
"value" is neither a method, a field, nor a member class of
"org.apache.drill.exec.expr.holders.NullableVar16CharHolder" [
a9d7bad6-2fac-41ed-a021-8dd3297a4b65 on ip-172-16-1-173:31010 ]
Error: exception while executing query: Failure while executing query.
(state=,code=0)
Any help here would be great.
Chris Matta
cmatta@mapr.com
215-701-3146
On Wed, Nov 26, 2014 at 12:17 PM, Christopher Matta <cm...@mapr.com> wrote:
> If I have a string like this (from Twitter): “Mon Nov 24 18:18:22 +0000
> 2014” is there a way to cast that as a timestamp, or date type?
>
> Twitter also provides: "timestamp_ms": "1416853102087"
> Any way to cast that as a timestamp with Drill?
>
> Chris Matta
> cmatta@mapr.com
> 215-701-3146
>
>