Mining Twitter data using Python and GemFire XD

Python is a very popular and powerful programming language, that’s a fact. Some people may find useful to write a Python client for GemFireXD, this article describes one of the easiest alternatives to accomplish it while explaining some details about Twitter API.

The standard for database connectivity in Python is DB-API 2.0 (PEP 249) which is almost like JDBC for Java. There are a bunch of implementations for most traditional databases like Oracle and MySQL, but very few alternatives for NewSQL databases like GemFire XD. As you may already know, GemFire XD is based on Apache Derby (10.4) and as very few will remember, Derby was initially distributed by IBM as Cloudspace until 2004, when the code was contributed to Apache Software Foundation as an incubator project. And why is important to remember this piece of history ? Because we’re going to leverage this IBM background of Apache Derby and use pyDB2 library for GemFire XD connectivity.

To make the tutorial a little bit more interesting we’re going to write a simple Twitter client that populate tweets into GemFire XD and some examples on how we can analyze the collected data.

Requirements and dependencies

Let’s get started by installing the dependencies:

DB2 Express-C – A no-charge (to develop/deploy), community edition of DB2 available for multiple platforms.

pyDB2 – Written by Man-Yong Lee, is licensed under LGPL and is the DB-API 2.0 compliant interface for IBM DB2 database.

TwitterAPI – A Python wrapper for Twitter API with support for Streaming.

Of course Python (2.7+) and GemFire XD are also required and download links are available under References.

Note: Alternatively you could also use GemFire XD ODBC drivers and mxODBC module written by Marc-Andre Lemburg, which has a commercial license or even pyODBC which is an open source alternative, but would rely on platform specific ODBC drivers.Note 2: An alternative to DB2 Express-C is the DB2 Universal Run-Time client, but AFAIK it’s only available for Windows.Note 3: I’m using OSX/Unix based instructions here but most of the instructions can easily be matched in a Windows environment.

Installation

DB2 Client Install

Download and install DB2 Express-C – For Mac OS X the package is called db2_v101_macos_expc.tar.gz – Uncompress it anywhere you want and run the installer command:

Note that we have started the locator with -jms-manager-start=true which will start Pulse, the embedded monitoring tool for GemFire XD.

Cataloging GemFire XD under DB2 client

In order to connect from pyDB2 to GemFire XD we need to “catalog” our database, which is a way to register the database into the DB2 client with an alias. This is required even for pure DB2 installations, when for example your database server is hosted on a different machine than your client.

Note: If you messed up during this process or want to rename your catalog node, use the uncatalog option.

Creating the application and testing

I’m going to use STS (Spring Tool Suite) with PyDev plugin for Python development, but it can easily be done using VIM or any other text editor. The reasoning for PyDev is code completion which is handy for occasional Python developers (like myself) and STS also offers a built-in SQL interface that can be used to browse GemFire XD tables and perform queries, which could be also done through sqlf command line utility.

Creating the TWEETS table

Read this article on how to setup GemFire XD connectivity with STS (Eclipse) for database development. With the setup in place, open a SQL Scrapbook and create a table:

We’re creating a simple table mapping the most common Tweet fields and partition by user_id, which should scale well if we later decide to have data about the Users related to these tweets co-located with it’s own tweets. But according to your needs, you may want to partition by Tweet location or any other criteria. Other than that, it’s the usual create table SQL syntax.

Creating the Python client

On STS create a new PyDev project and name it PyTweetXD

Under the project create a new PyDev module and name it GfxdClient.py and let’s write a hello world query:

Finally we are going to write a Twitter client that will monitor public tweets for a given term using the Twitter RESTful stream API. Before proceeding, make sure you have registered an application at https://dev.twitter.com/apps and generate the OAuth keys, which are required – To make it simple, put the OAuth keys in the config file under a Twitter section. Your config file may look like this:

We’re basically tracking every tweet that has the NBA word and inserting into GemFire XD. If any error occur we’ll just print the exception, count it but keep processing. Running the script to test on a Friday night with 10 games scheduled, I was able to collect approx. 80k tweets, which may not represent a huge amount of data (in GemFire XD scale) but we can already analyze and it’s good enough for some examples.

Monitoring using Pulse

Now while the script is running let’s open a browser and check Pulse, the GemFireXD built-in monitoring tool, and see what’s happening with the servers. The default URL for Pulse is: http://localhost:7070/pulse

After login (admin/admin) the first page gives you a clear and beautiful overview of the system, with total heap available (from both servers), current memory usage and last 15 minutes throughput (Put/Get operations rate).

We can double click one of the servers and check that the load is being balanced between both nodes.

Let’s get back to STS and perform some basic data analysis running some SQL queries. One of the joys of GemFire XD is that it will take care of parallelizing the query between the members, so even if I scale up or down, the locator will take care of load balancing the query between members and doing the proper data distribution.

Data analysis

Sources Rank (Query)

Most Twitter clients share their name through a field named ‘source’ on every tweet. So if you post a tweet from an iPhone the tweet source field look like this:
<a href=”http://twitter.com/download/iphone” rel=”nofollow”>Twitter for iPhone</a>

Now back on STS on the SQL Scrapbook we can perform the following query to see which source has more tweets from the tweets we collected:

-- rank of tweet sources
select ROW_NUMBER() OVER() as RANK,
source, count(*) total
from tweets
group by source
order by total desc

The first 20 results of this query returned the following:

Based on these results we can see that the number of IPhone users double the number of Android users in number of messages.

Languages Rank (Query)

On another quick example, we know that the NBA league is pretty much global with 90+ international players representing more than 30 different countries. But which languages, besides English of course, talks about NBA on Twitter? Run the following query against the data to get an idea:

-- languages most tweeted
select ROW_NUMBER() OVER() as RANK,
lang, count(*) total
from tweets
group by lang
order by total desc

And the top 20 results of this query are:

According to this data English tops the list of course, followed by Spanish, but then comes French, Japanese and German.

Word Frequency Analysis (Python script)

Our final example will use another Python script to parse the text from all tweets and return the most used words on all messages, leveraging the Counter class from Python collections package. The script looks like this:

So in about 6 seconds were able to parse 80000+ tweets, count every word and their frequency, generating the top 10 words of all these messages. Based on this result it’s clear that everybody was talking about Kyle Korver 3pt record. He has hit a shot from behind the arc for 90 consecutive games, an impressive mark, by the way.

Conclusion

This article demonstrated an alternative to ODBC for Python clients that want to use GemFire XD. It also shows how easy it can be to create an application that can store and analyze data from tweets about an specific topic using Twitter’s RESTful Stream API. A tweet contains a very limited number of characters and that’s why we didn’t even have to enable disk persistence or any other more advanced feature of GemFire XD, at least for this load. But for the person that may want to keep this data persisted for historical analysis it can easily be done.

Note that we’re using a very naive approach for the Python code, without parallelism for example, which would definitely improve our processing time and by adding data locality and processing data inside the GemFire nodes and not in the client, it would be a LOT faster.

What’s next ?

If you are using GemFire XD inside the Pivotal HD VM and want to play with GemFire XD PHD integration, here goes the extra work that needs to be done for Hadoop persistence using GemFire XD:

7 comments

There is no native Derby Python client, but since the Derby database has it’s roots from IBM, a DB2 Python client is “compliant” with Derby. Since GFXD is compliant (lightly based) with Derby you can use the DB2 client for most use cases.