SQL on Twitter: Analysis Made Easy Using N1QL

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

[This is the article published on DZone: https://dzone.com/articles/sql-on-twitter-twitter-analysis-made-easy]

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!

Swami Vivekananda: The Monk That Nobody Sent to Chicago

There’s a saying in Chicago: “We don’t want nobody that nobody sent.” This was the cold reception Swami Vivekananda faced when he arrived in the windy city in July 1893, determined to attend the World Parliament of Religions that September. He belonged to no organization, carried no letter of recommendation, his countrymen were nobody, and represented an alien religion to the Western world. As the days passed, his hope of attending the parliament dwindled. With money running out and the odds stacked against him, he left the Windy City and went to Boston, praying for a glimmer of opportunity. Swamiji came to America to share India’s most profound gift: the wisdom of the Hindu sages, preserved through centuries of oral tradition and embodied by its monks. This was 1893, not 1993—India was under the British grip, its resources drained, and its spirit subdued. Swamiji’s mission was not just a cultural exchange; it was a bold step toward envisioning a future where India could re...

Planet NoSQL

Search This Blog