{"id":3254,"date":"2016-04-15T15:17:00","date_gmt":"2016-04-15T05:17:00","guid":{"rendered":"http:\/\/mappingonlinepublics.net\/?p=3254"},"modified":"2016-04-15T15:27:53","modified_gmt":"2016-04-15T05:27:53","slug":"twitter-analytics-using-tcat-and-tableau-via-gawk-and-bigquery","status":"publish","type":"post","link":"https:\/\/mappingonlinepublics.net\/dev\/2016\/04\/15\/twitter-analytics-using-tcat-and-tableau-via-gawk-and-bigquery\/","title":{"rendered":"Twitter Analytics Using TCAT and Tableau, via Gawk and BigQuery"},"content":{"rendered":"

I\u2019ve previously introduced my TCAT-Process package<\/a> of helper scripts (written in Gawk), which take exports of Twitter<\/em> data from the Twitter Capture and Analysis Toolkit (TCAT)<\/a><\/em>, developed by the Digital Methods Initiative at the University of Amsterdam, and convert them to a format that is best suited to using the data in the analytics software Tableau. This post is an update that provides the latest version of TCAT-Process, and outlines some of the alternative options that are now available through further developments in TCAT<\/em> itself.<\/p>\n

But beyond these updates, the major new addition to this setup that I\u2019ll describe here is a process for uploading the data to Google BigQuery. BigQuery is a commercial but very affordable platform for working quickly with large datasets, and uploading the processed Twitter<\/em> data to BigQuery can considerably speed up the analytics process \u2013 especially also because Tableau has the ability to connect natively to BigQuery databases. For large Twitter<\/em> datasets \u2013 and when you\u2019re dealing with major global events, they can easily grow to the millions of tweets and generate gigabytes of data \u2013, this is a crucial advance which outsources a lot of the computationally intensive processing to Google\u2019s servers, rather than doing it locally on a desktop machine.<\/p>\n

None of this is entirely easy \u2013 partly also because recent changes to TCAT<\/em>, although welcome where they\u2019ve added new functionality, have at times also changed the underlying data formats. But hopefully the following outline of steps will help you get your data from TCAT <\/em>into Tableau without too many problems.<\/p>\n

Please note:<\/strong> if your version of TCAT<\/em> has options to export hashtag and mention tables, if your dataset is small enough for these exports to work reliably, and if you\u2019re not interested in resolving the URLs contained in your data, you won\u2019t need to use TCAT-Process at all. In that case, skip the section below that discusses TCAT-Process, and load your TCAT<\/em> exports directly into BigQuery, following the steps described below.<\/p>\n

Downloading TCAT<\/em> Data<\/h3>\n

The first step in the process is necessarily to export the data from TCAT<\/em>. Recent versions of TCAT<\/em> have added an export option in tab-separated format (as opposed to comma-separated), and this is by far the preferred format for our further work.<\/p>\n

TCAT-Process is able to work with CSV files exported from TCAT<\/em>, too, but if at all possible, choose TSV:<\/p>\n

Then, select the desired export parameters (dataset, timeframe, etc.), and export all tweets<\/em> from your selection:<\/p>\n

\"image\"<\/a><\/p>\n

If your version of TCAT<\/em> is recent enough, you\u2019ll also have options to export hashtag and mentions tables. Depending on your server setup, these may not work very well for very large datasets (of a million tweets or more), and TCAT<\/em> will time out or produce empty files if you try them \u2013 so export hashtags and mentions if you can, but if this doesn\u2019t work we\u2019ll use TCAT-Process to create them in a later step.<\/p>\n

\"image\"<\/a><\/p>\n

Installing and Using TCAT-Process on the TCAT<\/em> Data<\/h3>\n

(As noted, skip this section if you\u2019ve managed to export the full, hashtag, and mentions tables from TCAT and are not interested in resolving the URLs in your data.)<\/em><\/p>\n

First, download the latest version of TCAT-Process from here: tcat-process.zip<\/a>. This contains a directory named _scripts<\/em> that should be placed in your central Twitter<\/em> data folder, alongside the folders that your various datasets are stored in.<\/p>\n

If necessary, you also need to install Gawk and cURL, the tools which TCAT-Process relies on \u2013 see my previous post<\/a> for more information on how to install these.<\/p>\n

I\u2019ve already outlined the inner workings of TCAT-Process in my previous post<\/a>, and won\u2019t repeat all of this here. However, there are a number of key additions to the processing options that are worth pointing out. Here is the full set of option switches that TCAT-Process now uses:<\/p>\n

\"image\"<\/a><\/p>\n