"If I had more time, I would have written shorter letter" — Blaise Pascal

There have been lengthy articles on analyzing Twitter data. From Cloudera: here, here, and here. More from Hortonworks here and here.  This one from Couchbase is going to be short, save the examples and results.

Step 1: Install Couchbase 4.5. Use the Couchbase console create a bucket called Twitter and CREATE PRIMARY INDEX on Twitter using the query workbench or cbq shell.

CREATE PRIMARY INDEX ON twitter;

Step 2: Request your Twitter archive. Once you receive it, unzip it. (You can use larger twitter archives as well): cd <to the unzipped location>/data/js/tweets

Step 3:

$ for i in `ls`; 
        do 
          grep -i -v ^Grailbird $i > $i.out ; 
        done

Step 4: Update your IP, username, and password before you run this:

for i in `ls *.out`; 
do 
    /opt/couchbase/bin/cbbackupmgr  json -host http://127.0.0.1:8091 --username Administrator --password password --bucket twitter --dataset file:///home/keshav/mytweets/data/js/tweets/$i --format list --generate-key %id_str%; 
done

Step 5: There is no step 5!

Log into Couchbase's query workbench or cbq shell and start playing! Simply use SQL-based N1QL to query and play with the data. This online interactive tutorial will get you started with N1QL.

Here are the example queries on my twitter archive.

1. Give me the count of my tweets.

SELECT COUNT(*) my_tweet_count
FROM   twitter 
LIMIT  1; 

Results:

[
  {
    "my_tweet_count": 1658
  }
]

2. Get me a sample Twitter document.

SELECT *
FROM   twitter
LIMIT  1;

Results:  Twitter document is rich. It has nested objects, arrays, and arrays of objects.

[
  {
    "twitter": {
      "created_at": "2011-08-19 18:09:31 +0000",
      "entities": {
        "hashtags": [
          {
            "indices": [
              79,
              88
            ],
            "text": "informix"
          },
          {
            "indices": [
              89,
              99
            ],
            "text": "warehouse"
          }
        ],
        "media": [],
        "urls": [
          {
            "display_url": "bit.ly/pkFdF4",
            "expanded_url": "http://bit.ly/pkFdF4",
            "indices": [
              113,
              132
            ],
            "url": "http://t.co/GnKGAKB"
          }
        ],
        "user_mentions": []
      },
      "geo": {},
      "id": 104615993220927490,
      "id_str": "104615993220927488",
      "source": "<a href=\"http://twitter.com\" rel=\"nofollow\">Twitter Web Client</a>",
      "text": "No tuning required! Lester took his queries from ~10 hours to 15 minutes using #informix #warehouse accelerator. http://t.co/GnKGAKB",
      "user": {
        "id": 282131568,
        "id_str": "282131568",
        "name": "Keshav Murthy",
        "profile_image_url_https": "https://pbs.twimg.com/profile_images/670081620205023233/rHlKlkMC_normal.jpg",
        "protected": false,
        "screen_name": "rkeshavmurthy",
        "verified": false
      }
    }
  }
]

3. What days did I tweet most?

SELECT SUBSTR(created_at, 0, 10) tweet_date, 
       COUNT(1) tweet_count
FROM   twitter 
GROUP  BY SUBSTR(created_at, 0, 10) 
ORDER  BY COUNT(1) DESC 
LIMIT  5;

[
  {
    "tweet_count": 67,
    "tweet_date": "2013-11-05"
  },
  {
    "tweet_count": 60,
    "tweet_date": "2013-11-06"
  },
  {
    "tweet_count": 42,
    "tweet_date": "2014-04-30"
  },
  {
    "tweet_count": 41,
    "tweet_date": "2013-11-04"
  },
  {
    "tweet_count": 41,
    "tweet_date": "2014-04-28"
  }
]

4. Give me the top 5 hashtags and counts in my tweets:

SELECT   ht.text hashtag, 
         COUNT(1) htcount
FROM     twitter UNNEST entities.hashtags ht 
GROUP BY ht 
ORDER BY COUNT(1) DESC 
LIMIT 5;

[
  {
    "hashtag": "ibmiod",
    "htcount": 133
  },
  {
    "hashtag": "informix",
    "htcount": 31
  },
  {
    "hashtag": "IBMIOD",
    "htcount": 30
  },
  {
    "hashtag": "informix",
    "htcount": 26
  },
  {
    "hashtag": "Informix",
    "htcount": 21
  }
]

(Yes, I worked for Informix and IBM!)

5. How many tweets have I done on Couchbase, N1QL, NoSQL, or SQL?

Because hashtags are stored in an array, you need to UNNEST it so you can group by the hashtab.

SELECT   UPPER(ht.text) hashtag, 
         COUNT(1)       htcount 
FROM     twitter UNNEST entities.hashtags ht 
WHERE    upper(ht.text) IN ['COUCHBASE', 'N1QL', 'NOSQL', 'SQL'] 
GROUP BY UPPER(ht.text) 
ORDER BY COUNT(1) DESC 

[
  {
    "hashtag": "NOSQL",
    "htcount": 258
  },
  {
    "hashtag": "COUCHBASE",
    "htcount": 162
  },
  {
    "hashtag": "SQL",
    "htcount": 64
  },
  {
    "hashtag": "N1QL",
    "htcount": 18
  }
]

6. Let’s see who I’ve mentioned in my tweets and how many times?

SELECT   UPPER(um.screen_name) umention, 
         COUNT(1)              htcount 
FROM     twitter UNNEST entities.user_mentions um 
GROUP BY upper(um.screen_name) 
ORDER BY count(1) DESC ;

I've only given partial results below. @N1QL and @Couchbase were top mentions. Note Twitter itself doesn't store the @ character in its data.

[
  {
    "htcount": 104,
    "umention": "N1QL"
  },
  {
    "htcount": 80,
    "umention": "COUCHBASE"
  },
…
]

7. Let’s get all the tweets I’ve mentioned @sangudi, creator of N1QL.

SELECT SUBSTR(created_at, 0, 10)    posted, 
       text                      AS tweet 
FROM   twitter 
WHERE  
         ANY u IN entities.user_mentions 
              SATISFIES u.screen_name = 'sangudi' 
         END 
ORDER BY SUBSTR(created_at, 0, 10) DESC ;

[
  {
    "posted": "2016-06-10",
    "tweet": "JOIN and enjoy in Couchbase Server 4.5. tx 2 @sangudi\n#SQL #NoSQL #Couchbase #MongoDB #JSON\nhttps://t.co/X9E0ghcx4L https://t.co/AYnetU5MHF"
  },
  {
    "posted": "2016-05-14",
    "tweet": "Brining SQL to NoSQL: Rich, declarative Query for NoSQL, with @sangudi \n at @NoCOUG \nhttps://t.co/mnpPYKNQeA\n#Couchbase #NoSQL #SQL #JSON"
  },
…
]

While this works fine, it scans the whole bucket using primary scan.

EXPLAIN SELECT SUBSTR(created_at, 0, 10)    posted, 
       text                      AS tweet 
FROM   twitter 
WHERE  
         ANY u IN entities.user_mentions 
              SATISFIES u.screen_name = 'sangudi' 
         END 
ORDER BY SUBSTR(created_at, 0, 10) DESC ;

    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "PrimaryScan",
              "index": "#primary",
              "keyspace": "twitter",
              "namespace": "default",
              "using": "gsi"
            },

Let’s create an index on this array element to make it go faster.

CREATE INDEX idxtwittername on twitter
     (ALL ARRAY  u.screen_name FOR u IN entities.user_mentions END);

Now, see the plan for the same query. This uses the index and pushes down the predicate to the index, making the query faster.

EXPLAIN SELECT SUBSTR(created_at, 0, 10)    posted, 
       text                      AS tweet 
FROM   twitter 
WHERE  
         ANY u IN entities.user_mentions 
              SATISFIES u.screen_name = 'sangudi' 
         END 
ORDER BY SUBSTR(created_at, 0, 10) DESC ; 

        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "index": "idxtwittername",
                "index_id": "df30f58c0e0b9677",
                "keyspace": "twitter",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "\"sangudi\""
                      ],
                      "Inclusion": 3,
                      "Low": [
                        "\"sangudi\""
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },

Couchbase 4.5 makes it very easy to ingest JSON so you can get insight into your data. For more advanced questions and advanced usage, use array.

Try it out with your own Twitter data or a public JSON archive. Create indices on fields and arrays. Ask more questions, find more insights!