Raspberry Pi Twitter Sentiment Analysis Server – Part 3 – Sqlite3 and R

You’ve probably noticed I’m doing the walkthrough in bit sized pieces, two reasons. Firstly time, to code, investigate and then write up what you’re doing can take time. A labour of love I know and I do enjoy doing it.  Secondly I personally think it’s easier to learn in small segments.  If I posted a huge start-to-finish project it’s harder to learn.

So, Part 3. Sqlite3 and R.

Installing Sqlite3 on to Raspberry Pi

On your Pi log in and then sudo su so you are the root user.  Now we’re going to install the Sqlite3 database command line client.

apt-get install sqlite3

It will download some things and get them installed.

We need a database to hold our tweets but also have a column for a sentiment score. This will become more clear later on when we start saving twitter data from R into the database.  For now though let’s just create the required table so we have it for later.

Open up a text editor and create a file with the following sql:

create table twitterdata (
  twitterid varchar(100),
  twitteruser varchar(100),
  twitterdata varchar(255),
  sentimentscore int

It’s the basic of the basic but it suits our needs here.

Next we need to run Sqlite3 and give a name of the database we want to create, then run the above sql file. We can do this in one line from the command line:

sqlite3 twitter.db < name_of_your_sql_file.sql

An easy way to check all is well is to run sqlite3 and run the .tables command.

root@raspberrypi:/home/pi# sqlite3 twitter.db 
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables

Getting R ready to access Sqlite3

R requires another library to be able to read/write to the Sqlite3 database.  So fire up R and run the following command:

install.packages("RSQLite", dependencies=TRUE)

It might take some time to build and compile so be patient.

The basic code to connect to our database from R is fairly trivial.

db = dbConnect(dbDriver("SQLite"), dbname="twitter.db")

So where are we at?

We’ve got the fair guts of what we need to retrieve and store tweets. Next time we turn our attention to coding in R the storing of tweets and getting the sentiment.

5 responses to “Raspberry Pi Twitter Sentiment Analysis Server – Part 3 – Sqlite3 and R”

  1. Yes you could do that too if you weren’t already logged in as root. I normally “sudo su” if I have multiple things to install, saves on typing. Each to their own really.

  2. I don’t have any Pi gear at the moment, machine learning, AI, Spark and Hadoop are more my mainstay. I think it would be perfect for the project I agree as things have moved on so much since I wrote that mini series.

    Thanks for your comment.

  3. Your blog is an inspiration, currently working on my dissertation using Hadoop with my Raspberry Pi cluster processing data for situational awareness and your big data/hadoop articles have been eye opening on this whole new world of data.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: