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 17:38:34 GMT
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