[AWS] Twitter Analysis ~Part10: Analyze twitter data by Amazon QuickSight~

Purpose

In part 10 section, I would like to explain how to execute queries to S3 bucket which stores analyzed tweet data by utilizing Amazon Athena. Let me show you the scope of this section as below.

Prerequisite
Execute Analysis by Amazon QuickSight
Firstly, open QuickSight console.

If it is first time to use QuickSight, you need to sign up for it first.

You have two option, Enterprise and Enterprise + Q.
If you want to use BI function, you had better choose Enterprise + Q.
Please refer to below site if you are interested in it.
https://aws.amazon.com/quicksight/q/

In my case, I chose to use Enterprise Edition and click Continue button.

Select Authentication method, region and so on.
And please write down an e-mail address for notification.

And select IAM Role and resources and Click Select S3 buckets link.

Then, choose S3 buckets that you’ve created in the previous article.

After selecting Amazon S3 buckets, click Finish button.

Successfully signed up for Amazon QuickSight.
Click Go to Amazon QuickSight button.

OK, you can see Welcome to QuickSight screen.

And Click New Analysis button.

And click New dataset button.

In this case, please select Athena as a Dataset.

And, set Data source name and click Validate connection button.

After it is validated, click Create data source button.
You can see Database and Tables that is created by AWS Glue in the previous article.
Click Use Custom SQL button.

And edit New custom SQL and click Confirm query. Let me show you an example provided by AWS site.

Custom Query example
——————————————————————–
SELECT s.*,
         e.entity,
         e.type,
         e.score,
         t.lang as language,
         coordinates.coordinates[1] AS lon,
         coordinates.coordinates[2] AS lat,
         place.name,
         place.country, (t.timestamp_ms / 1000) + (9 * 60 * 60) AS timestamp_in_seconds,
         regexp_replace(source,
         ‘\<.+?\>’, ) AS src
FROM twitter_timeline_dashboard.tweets t JOIN twitter_timeline_dashboard.tweet_sentiments s
         ON (s.tweetid = t.id)
JOIN twitter_timeline_dashboard.tweet_entities e
         ON (e.tweetid = t.id)
——————————————————————–

And, click Edit/Preview data button.

You can analyze the data as you like to choose Visual types and Fields list.
In my case, I analyze the ratio of negative tweet.
I set sentimentnegscore to Value and sentiment to Target value as below.
2.96 % is ratio.

I want to receive a notification if the negative tweet ratio exceeds specific value.
Firstly, click Publish dashboard in Share tab.

Then write down the dashboard name and click Publish dashboard button.

You can see your analyzed graph as below.
Please click bell icon.

And set alert threshold as you like and save it.

After for a while, you will receive notification by QuickSight as below it the ratio exceeds the threshold !!

That’s all for this topic. I hope you could analyze data by Quicksight.
In my next article, let me show you how to get notification by other way, Amazon SNS.

Reference
Basically, I followed the procedure provided by AWS webpage. Please refer to below URL if necessary, though it is written in Japanese…
If you think this article is beneficial for you, I would be glad if you click below icon for my motivation.
ブログランキング・にほんブログ村へ

コメント