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

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

Why Should Databases Go Natural?

From search to CRM, applications are adopting natural language and intuitive interactions. Should databases follow? This article provides a strategic perspective. Amid the many technological evolutions in software and hardware (CISC/RISC, Internet, Cloud, and AI), one technology has endured:  Relational Database Systems   (RDBMS), aka SQL databases. For over 50 years, RDBMS has survived and thrived, overcoming many challenges. It has evolved and adopted beneficial features from emerging technologies like object-relational databases and now competes robustly with   NoSQL databases .  Today, RDBMS dominates the market, with four of the top five databases and seven of the top ten being relational. RDBMS has smartly borrowed ideas, like JSON support, from NoSQL, while NoSQL has also borrowed from RDBMS. NoSQL no longer rejects SQL. From a user perspective, all modern databases have SQL-inspired query language and a set of APIs. All applications manage the respective data...
Post a Comment
Read more

iQ Interactive: Cool Things for Developers on Couchbase Capella iQ

  The landscape of software development is ever-evolving with the advent of new technologies. As we venture into 2023, natural language processing ( NLP ) is rapidly emerging as a pivotal aspect of programming. Unlike previous generations of tools that primarily aimed at enhancing coding productivity and code quality, the new generation of Artificial Intelligence ( GenAI ) tools, like iQ, is set to revolutionize every facet of a developer's workflow. This encompasses a wide range of activities: Reading, writing, and rewriting specifications Designing, prototyping, and coding Reviewing, refactoring, and verifying software Going through the iterative cycle of deploying, debugging, and improving the software Create a draft schema and sample data for any use case Natural language queries. Generate sample queries on a given dataset Fix the syntax error for a query Don't stop here. Let your imagination fly. Although the insights garnered from iQ are preliminary and should be treated ...
Post a Comment
Read more