drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jim Bates <jba...@maprtech.com>
Subject Re: Convert string to timestamp or unix timestamp to timestamp type
Date Wed, 26 Nov 2014 19:06:06 GMT
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