SQL on Twitter: Analysis Made Easy Using N1QL
SQL on Twitter: Analysis Made Easy Using N1QL
Want to look more deeply into your tweets (or any JSON repository)? See how you can use Couchbase's N1QL to query and organize your data.
Join the DZone community and get the full member experience.
Join For FreeDownload the Scale-Out and High Availability whitepaper. Learn why leading enterprises choose the Couchbase NoSQL database over MongoDB™ after evaluating side by side.
"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!
Learn how to scale enterprise applications easily, efficiently, and reliably with NoSQL. See why Couchbase beats MongoDB™ for scale-out and high availability.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}