In this step, we will create _l1_, _l2_ and _l3_ views which will help us in calculating sentiment of each tweet.
Create view _l1_. _l1_ view converts each tweet into lower case and explodes it into a list of words. Run below command in Hive query editor in Hue.
create view l1 as select id, words from tweets_raw lateral view explode(sentences(lower(text))) dummy as words;
Sample rows of view _l1_ are
Create view _l2_. _l2_ view stores every word of a tweet in a new row.
Run below command in Hive query editor in Hue.
create view l2 as select id, word from l1 lateral view explode( words ) dummy as word ;
Sample rows of view _l2_ are
Create view _l3_. _l3_ view joins _l2_ view with _dictionary_ table and stores polarity of each word. Run below command in Hive query editor in Hue.
create view l3 as select id, l2.word, case d.polarity when 'negative' then -1 when 'positive' then 1 else 0 end as polarity from l2 left outer join dictionary d on l2.word = d.word;
Sample rows of view _l3_ are
What is the polarity of word "crushes"?