THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Querying RSS feed subscriber count on Google Reader using Data Explorer‏

I have been fiddling about with Data Explorer some more and have built an interesting little mashup that enables one to discover the number of Google Reader subscribers to each RSS feed on SQLBlog.com. Before I show you how its built I'll whet your appetite with a screenshot of the output:

excelscr
For brevity I have deliberately hidden some of the results however you can see the full dataset for yourself (and see if any of the numbers have changed since I took this screenshot) by visiting https://ws41451459.dataexplorer.sqlazurelabs.com/Published/SQLBlog%20subscriber%20counts%20on%20Google%20Reader and opening the data in the format of your choosing (i.e. Excel, OData or CSV). All the data is publicly available as is the mashup so you won't have any difficulty in accessing it. Opening the data yourself will also illustrate how long it takes Data Explorer to execute the mashup which, when you consider that the mashup queries the Google Reader API for data on each RSS feed in turn, could be quite interesting in itself.
So, how is it done? Its very simple once you know how, my mashup has four resources:
resources
Which we can take a look at in turn.

Resource: StripOutCommas

This is simply a function that will remove commas from a string, this is important because the API that we are using returns numbers as text and any numbers greater than 999 get commas inserted. It is defined simply as:

(value) => Text.Replace(value,",","")

 stripoutcommas

Resource: List Of Feeds

Simply a list of RSS feeds for which we are going to get the subscriber count. The first task, "Typed list of feeds" , is our typed-in list of RSS feeds:
listoffeeds

The second task, "Convert to table", does exactly what it says on the tin:
converttotable

N.B. I have, by the way, complained loudly about the inability to resize columns in the Data Explorer UI.

Resource: GetNumberOfSubscribersForFeed

This is where the real work occurs. We are using an API provided by Carter Cole at http://cartercole.com/dev/api/greaderapi.asp that wraps the Google Reader API thus making it easy to query for number of subscribers.

(feed) => StripOutCommas(Json.Document(Web.Contents("http://cartercole.com/dev/api/greaderapi.asp", [Query = [feed = feed]]))[subscribers])

getnumberofsubscribers

Here I have essentially defined a function that (1) calls Carter's API, (2) passes it a parameter called feed, (3) converts the result into a JSON document, (4) extracts the "subscribers" value and (5) passes the result to StripOutCommas().

Resource: Number Of Subscribers Per Feed

This resource is what pulls everything together. Firstly the "Call func on each RSS feed" task calls our "GetNumberOfSubscribersForFeed" resource on each RSS feed in the "List Of Feeds" resource:

Table.AddColumn(#"List Of Feeds", "NumberOfSubscribers", each GetNumberOfSubscribersForFeed([feed]))
numberofsubscribersperfeed


The "Rename column", "Convert Text to Integer" & "Sort Subscribers DESC" tasks are, hopefully, self-explanatory.

Consume

And that's it! As already stated the mashup has been published at https://ws41451459.dataexplorer.sqlazurelabs.com/Published/SQLBlog%20subscriber%20counts%20on%20Google%20Reader from where you can:

  • Download the mashup output as a CSV file
  • Download the mashup output as an Excel document
  • Download the mashup itself so you can play with it at your leisure
  • View the output in an OData feed
downloadsite

If you want to use the mashup to get similar counts for your own set of RSS feeds simply change the "List of Feeds" resource appropriately. Happy data exploring!

@Jamiet

Published Wednesday, December 21, 2011 9:29 PM by jamiet
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Miguel Llopis said:

Excellent post, Jamie!

December 24, 2011 12:35 PM
 

SSIS Junkie said:

Three months ago I published a fairly scathing attack on what I saw as some lacklustre announcements

July 12, 2013 12:36 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement