incubator-drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Matta <cma...@mapr.com>
Subject Re: Convert string to timestamp or unix timestamp to timestamp type
Date Wed, 26 Nov 2014 19:18:43 GMT
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 <jbates@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 <cmatta@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 <cmatta@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
> > > ​
> > >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message