Skip to main content

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!




Labels:

Comments

Post a Comment

Popular posts from this blog

MongoDB to Couchbase for Developers: Part 2: Database Objects

    MongoDB developers and DBAs work with physical clusters, machines, instances, storage systems, disks, etc. All MongoDB users, developers, and their applications work with logical entities: databases, collections, documents, fields, shards, users, data types.  There are a lot of similarities with Couchbase since both are document (JSON) oriented databases.  Let’s compare and contrast the two with respect to database objects.  Here’s the first part of this series comparing the architecture.   MongoDB Organization A full list of MongoDB schema objects is listed here and here . A database instance can have many databases; A database can have many collections; Each collection can have many indexes.  Each collection can be sharded based into multiple chunks on multiple nodes of a cluster using hash-sharding strategy or index sharding strategy.  The MongoDB indexes are local to their data.  Therefore, the indexes use the same strategy as ...
3 comments
Read more

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...
Post a Comment
Read more

MongoDB to Couchbase for Developers: Part 1: Architecture

  Introduction By any measure, MongoDB is a popular document-oriented JSON database. In the last dozen years, it has grown from its humble beginnings of a single lock per database to a modern multi-document transaction with snapshot isolation. MongoDB University has trained a large number of developers to develop the MongoDB database. There are many JSON databases now. While it's easy to start with MongoDB to learn NoSQL and flexible JSON schema, many customers choose Couchbase for performance, scale, and SQL. As you progress in your database evaluation and evolution, it's easier to start from what you know now. That's the idea for this series. Use your MongoDB knowledge to easily learn and eventually master Couchbase.  Don't despair if you don't know MongoDB do know any one of the RDBMS. This Oracle to Couchbase series will help you learn by comparison.  Many nations are known by their language: French, German, Spanish). Many databases are known for their language...
1 comment
Read more