drill_twitter.md

Tweets

create or replace view MapRTweets as select
CAST(t.`dir3` as INT) as `hour`,
CAST(t.`dir2` as INT) as `day`,
CAST(t.`dir1` as INT) as `month`,
CAST(t.`dir0` as INT) as `year`,
CAST(t.`id` as BIGINT) as `id`,
CAST(t.`user`.`id` as BIGINT) as `user_id`,
CAST(t.`text` as VARCHAR(140)) as `tweet`,
to_timestamp(t.`created_at`, 'EEE MMM dd HH:mm:ss Z YYYY') 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 MapR t;

Users

create or replace view TwitterUsers as
select CAST(t.`user`.`id` as BIGINT) as `user_id`,
CAST(t.`user`.`name` as VARCHAR(128)) as `name`,
CAST(t.`user`.`screen_name` as VARCHAR(128)) as `screen_name`,
CAST(t.`user`.`location` as VARCHAR(128)) as `location`,
CAST(t.`user`.`lang` as VARCHAR(128)) as `language`
from MapR t;