An example how to collect, analyze and visualize social media data with JAQL and Hadoop

This article takes you through an example how to query, transform and visualize data from social media. We are going to collect tweets from twitter, store them in HDFS (Hadoop distributed file system) and use JAQL and Java MapReduce application to manipulate and transform the data. Finally, we will visualize the results using a spreadsheet-style tool.

Motivation & Understanding the sample scenario

Very briefly – I’m going to search for tweets containing words “IBM” and “BigData”. After I collect them in JSON format, I use JAQL to transform them into a simpler structure and store them into HDFS as comma-delimited file. Then I run Java MapReduce program that counts the occurrence of each single word in all tweets. Finally I use BigSheets to visualize my analysis. Here is the outcome of the analysis:

Background

In this example, I’m using IBM’s Hadoop enterprise distribution InfoSphere BigInsights 2.1 (you can download quick start edition for free here). IBM InfoSphere BigInsights brings the power of Hadoop to the enterprise. Apache Hadoop is the open source software framework, used to reliably managing large volumes of structured and unstructured data (for more information about Hadoop see the article Big Data in Hadoop – How? What is it?). BigInsights makes it simpler for people to use Hadoop and build big data applications. It enhances this open source technology to withstand the demands of your enterprise, adding administrative, discovery, development, provisioning, and security features, along with best-in-class analytical capabilities from IBM Research. The result is that you get a more developer and user-friendly solution for complex, large scale analytics.

For transforming and storing twitter data, I use Jaql (which is incorporated within BigInsights). Jaql is one of the languages that helps to abstract complexities of MapReduce programming framework within Hadoop. It’s a loosely typed functional language with lazy evaluation (it means that Jaql functions are not materialized until they are needed). Jaql’s data model is based on JSON Query Language, it’s a fully expressive programming language (compared to Pig and Hive which are query languages), it elegantly handles deeply nested semi-structured data and can even deal with heterogeneous data. You can read my brief introduction to JAQL.

As a file system, I use HDFS which is also a part of BigInsights. HDFS is a distributed, scalable, Java-based file system that allows to store large volumes of unstructured data. You can find more information in my article Hadoop Distributed File System (HDFS).

For the graphical visualizations and final data manipulations, I use BigSheets. BigSheets is a spreadsheet-style tool provided with BigInsights that allows you to use standard spreadsheets functions, write your own macros, join tables, filter data, sort data, visualize data in graphs, and so on.

My working environment

• Red Hat Enterprise Linux 6.3 (64-bit) in VMWare (check BigInsights system requirements)
• IBM InfoSphere BigInsights 2.1 Enterprise Edition (you can obtain Quick Start Edition for free, article about how to install BigInsights to VMWare)
• IBM Rational Team Concert 4 (Eclipse 3.6 version which can be downloaded for free from jazz.net) with BigInsights plugin (instruction how to install the plugin is available after you install BigInsights at the web console welcome page)

Step 1: Collecting sample data

In this step I’m going to collect tweets using Twitter’s REST API v1.1, specifically GET search/tweets resource. Twitter’s REST-based search API allows you to collect a small amount of data (max 100 tweets per search request). A production application would likely use Twitter’s Streaming API to obtain large volumes of data, but I will keep it simple this time. I’m going to cover Twitter’s Streaming API together with BigInsights Stream in another article.

As you probably know, Twitter’s API returns data as JSON. I’m going to collect tweets which contains two keywords “IBM” and “BigData”. To do so, I need to call REST API:

https://api.twitter.com/1.1/search/tweets.json?q=IBM%2BWatson&count=100

If you type this address in your browser, you will find out that authentication failed. To start collecting data from Twitter, you need to get your OAuth tokens. To do so, follow this process (if you already don’t have your OAuth tokens):

1. Login to https://dev.twitter.com and go to your applications.
2. Click on “Create a new application”. Fill all the required information.
3. Click on “OAuth tool” tab.
4. Scroll down and use “Request Settings” to generate OAuth signature. Fill the form as shown on the picture below (you can change it for your purposes).
5. Save the generated Authorization header.

After you get your Authorization Header, you can finally collect the tweets. There are many ways how to do it. I’m using linux wget command. You just need to plug in your Authorization Header after the --header argument. Also check URI in the end. Running following command in linux shell collects tweets containing both keywords “IBM” and “BigData” and stores them into /home/biadmin/Documents/tweets.json.

Here is a shortened example of JSON structure with tweets you obtain from Twitter (whole structure available here):

Step 2: Processing and transforming data with JAQL

Now we have our tweets collected in JSON format and stored in /home/biadmin/Documents/tweets.json. Next thing we are going to do is to transform them in a simpler structure and store them in HDFS as comma-delimited file. We are going to use JAQL which is provided with BigInsights. If you are not familiar with Jaql, you can check my brief introduction to JAQL. There are several ways how to use JAQL:

• Jaql plugin for Eclipse (instruction how to install BigInsights plugin to Eclipse is available at welcome page of BigInsights web console – Jaql eclipse plugin is not supported on Windows at the time of publishing this article)
• Jaql shell (a command-line interface which can be launched from $BIGINSIGHTS_HOME/jaql/bin/jaqlshell)
• Jaql ad hoc query application accessible through the BigInsights web console (must be first deployed)
• Jaql web server, which allows executing Jaql scripts via REST API calls
• Jaql API for embedding Jaql in a Java program

The easiest and most straightforward way is to use Jaql shell. Just run this script in the terminal $BIGINSIGHTS_HOME/jaql/bin/jaqlshell and you can start writing your JAQL statements. However, I’m going to use Eclipse with BigInsights plugin. How to setup your Eclipse environment:

1. Jaql eclipse plugin works only under Linux environment (at the time of writing this article). Download and install Eclipse 3.6. (version 3.6 is the only one supported with BigInsights 2.1).
2. Go to your BigInsights web console (typically at http://localhost:8080/) and at the Welcome page click on “Enable your Eclipse development environment for BigInsights application development”.
   
3. Then follow the installation instructions.
4. Next step is to run Eclipse, switch to BigInsights perspective and set up a connection with your BigInsights server. Then you are ready to write your BigInsights applications.

Ok, our IDE environment is ready. Lets start with JAQL and write a script that loads our tweets, extracts data we consider important, and saves the results to HDFS. To do so, create a BigInsights Project within Eclipse and create a new Jaql script file in it. Here is the script that creates 2 files (one comma-delimited file and one hadoop sequence file which contains just the content of tweets).

The script can be run directly from the Eclipse.

Here is a very brief explanation what does the script do. You can learn more about JAQL in my article JAQL in Hadoop – a brief introduction or from the official reference guide.

• jaqlGet() loads JSON text file (in our example from local file system). More information about reading and writing JSON files is available here.
• tweets.statuses -> transform ... take original JSON structure and transform it to the simpler one
• tweets -> write(del("/user/root/tweets.del", schema = schema {... writes the new structure to a comma-delimited file (in our example to a file in HDFS)
• tweets -> transform $.text; extracts just the content of tweets (text part)
• tweets_text -> write(seq("/user/root/content/tweets_text.seq")); stores the extracted text content of tweets into a hadoop sequence file (again in this example to HDFS file system)

If you want to see the content of the tweets variable, just run this statement:

You should get an output like this:

And this is the output of /user/root/tweets.del:

The second file we created (/user/root/content/tweets_text.seq) is a sequence file. SequenceFile is a flat file consisting of binary key/value pairs. It is extensively used in MapReduce as input/output formats. This file contains just text parts of the tweets and we are going to process it by Java MapReduce program that counts occurrences of each word in all tweets. Then we are going to display the most frequently used words in a tag cloud.

Step 3: Counting word occurrences with a Java MapReduce program

This step is optional. Before we are going to work with BigSheets and visualize data we extracted, I would like to demonstrate you very simple Java MapReduce application that counts occurrences of each word in all tweets. The application we are going to use is called WordCount and is a part of Hadoop distribution. However, BigInsights is bundled with Hadoop 1.1.1 which contains WordCount version 1.0. Since that version is very simple, I’m going to download the source code of WordCount v2.0 and run this application against our tweets’ texts. Compared to version 1.0, version 2.0 provides an option to filter inadequate characters and transform all words to lowercase.

First step is to create a BigInsights project. Then in the folder “src” create a new package called “mypackage” and finally a new Java Class file “WordCount.java” and copy following WordCount v2.0 source code into it (you can also download the source code in the text file WordCount.java.txt):

Here is the WordCount.java file in my Eclipse:

The WordCount v2.0 is a very simple Java application that harness the power of MapReduce. If you aren’t familiar with MapReduce, I recommend you to visit BigDataUniversity.com and enroll in one of the courses about Hadoop fundamentals (it’s completely for free). Our WordCount application takes several arguments:

• -Dwordcount.case.sensitive=false says whether processing should be case sensitive
• path/to/input/directory specifies the input directory from which ALL files would be taken into processing
• path/to/output/directory specifies the output directory where results will be stored
• -skip /user/rood/patterns.txt specifies the file that contains characters/words to skip (Java regular expressions can be used)

Before we run the application and process the file containing text parts of the tweets /user/root/content/tweets_text.seq, we need to do 2 things. First one is to export our WordCount application to a JAR file. To do so, follow these steps:

1. Right click the Eclipse project and choose “Export”.
2. Select Java -> JAR file and click Next.
3. Check both “Export generated class files and resources” and “Export Java source files and resources”. Specify the export destination (for example /home/biadmin/Documents/WordCount2.jar). Click Next.
4. On the next page leave everything default and click Next.
5. On the last page set the Main class to be “mypackage.WordCount” and click Finish.

The second thing we need to do is to create a file with patterns that would be skipped when processing the words. This file must be then stored in HDFS. To do so, lets follow these steps:

1. Create a new file: touch /home/biadmin/Documents/patterns.txt
2. Edit the file: vi /home/biadmin/Documents/patterns.txt and specify characters you want to skip (in Java regular expression). Every line represents one expression. The sample file removing .,!:;@#'() characters from words could look like this:
   \.
   \,
   \!
   \:
   \;
   \@
   \#
   \'
   \(
   \)
3. Move the file to HDFS:
   hadoop fs -put /home/biadmin/Documents/patters.txt /user/root/patterns.txt
   If you are not familiar with HDFS, I recommend you to read my article about HDFS.
4. Now we have the pattern file stored in HDFS in the path /user/root/patterns.txt

Now we are ready to run our Java MapReduce application within Hadoop. If you still remember, our input file tweets_text.seq is stored in the folder /user/root/content. We are going to save results into /user/root/results (the output directory can’t exist before, otherwise the mapreduce job fails – to remove the directory use this command: hadoop fs -rmr /user/root/results). Finally to run the application, execute this statement in your terminal:

If everything goes well, your terminal should look like this:

The result is stored in HDFS in a location similar to /user/root/results/part-XXXXX. You can check the content of folder by running hadoop fs -ls /user/root/results. You can then view the result file by running this command: hadoop fs -cat /user/root/results/part-XXXXX. As you will find out, the file contains tab-separated data in the format WORD<tab>number of occurrences.

Other alternative how to look at the results is to use BigInsights web console. Go to http://yourserver:8080/ and from the menu click on “Files”. Then navigate to your file through the folder structure. You should see something similar to this:

Step 4: Visualizating outcomes with BigSheets

This part is coming in a separate article How to visualize BIG data with InfoSphere BigSheets.

Conclusion

This article should serve as a very simple example how to collect, analyze and visualize data from social media like twitter. It’s not definitely a best practice. To use all features of BigInsights, the steps 1, 2 and 3 could be merged into one Java application that could be deployed to BigInsights server and scheduled for automatic runs.

Follow Ups

1. An article about data exploration and visualizations in BigSheets (done)
2. An article about how to do sentiment analysis with BigInsights
3. An article about how to collect data from streams
4. An article about how to use visualizations with InfoSphere Data Explorer

Resources

• IBM InfoSphere BigInsights 2.1 Information center
• Presentation Hadoop scripting with JAQL at Innovate 2013 Conference (not available online)
• developerWorks: Analyzing social media and structured data with InfoSphere BigInsights
• developerWorks: Query social media and structured data with InfoSphere BigInsights
• Hadoop Map/Reduce Tutorial
• Twitter Developers