Tweetpoll and RESTful Northwind go bye-bye

jamiet — Sat, 23 Jan 2010 10:25:23 GMT

On 31st January 2010 Windows Azure and SQL Azure will transition to becoming services that you have to pay for which means that my three small demos that are hosted up there are going to disappear hence I thought now would be a good time to review before they are digitally ground into dust.

Tweetpoll

Tweetpoll was a demo that I wrote back in April 2009 and is hosted on Windows Azure at http://tweetpoll.cloudapp.net/. Here’s what it does:

periodically polls Twitter’s public timeline and gets returned a list of the latest tweets
pushes each returned tweet onto a Windows Azure queue as a message
picks each message off the queue, measures the length of the tweet, and increments a counter (in Windows Azure table store) for each length
Displays the distribution of tweet lengths on a graph at http://tweetpoll.cloudapp.net/ (using some questionable HTML and Javascript :)
Counts the number of hits on http://tweetpoll.cloudapp.net/ and maintains that count in Windows Azure table storage too
Displays the current contents of the Windows Azure queue

Here’s a diagram that shows that a little better and demonstrates which parts use Azure web and worker roles. It was built at a time when one could only have one worker role per application therefore two threads are used to simulate the pushing onto and picking off the queue:

Its a very noddy application and the use of a queue is gratuitous but nevertheless its a useful demo of what can be achieved and enabled me to get some experience of building an app on Azure. The source code is available at http://tweetpoll.codeplex.com.

I wrote a blog entry back in May 2009 called Tweetpoll – My first Windows Azure application is live that introduced Tweetpoll. In that blog entry I provided a screenshot of the graph that gets displayed at http://tweetpoll.cloudapp.net and here it is again:

Here’s what it looked like in July 2009:

And finally today in January 2010:

I said at the time that it would “be interesting to see if the graph smoothes out over time” and sure enough that is what has happened. We still get a massive spike around 140 characters of course but its clear to see that the curve has smoothed out before that spike. I also find it interesting that there are more tweets in the 30-50 range than 90-120; something for academics to ponder over perhaps. Finally, if you are wondering why some tweets appear to be over 140 characters in length then take a read of my blog entry The Longest Tweet where I explain why that is.

Incidentally, by adding up the counter for each tweet length I can determine how many messages Tweetpoll has gotten off of the public timeline since April 2009 and I display that number at http://tweetpoll.cloudapp.net too:

Last but not least here’s how many hits the site has had:

Again, if you’re interested in looking under the bonnet of Tweetpoll then the source code is available at http://tweetpoll.codeplex.com.

RESTful Northwind

When SQL Azure entered public beta I wanted to build an app that would demonstrate its capabilities so I built http://northwindazure.cloudapp.net/Northwind.svc/. Its is, quite simply, a copy of the venerable Northwind database hosted on SQL Azure with a WCF Data Services (née Astoria) head on top of it which is hosted on Windows Azure. If you hit the WCF Data Services head then this is what you see (make sure you have feed reading turned on in your browser):

Some sample queries that you can issue:

Customer ALFKI
http://northwindazure.cloudapp.net/Northwind.svc/Customers('ALFKI')
Order items with a price > 250
http://northwindazure.cloudapp.net/Northwind.svc/Order_Details?$filter=UnitPrice gt 250
All orders by customer ANATR
http://northwindazure.cloudapp.net/Northwind.svc/Customers('ANATR')?$expand=Orders
All order details of all orders by customer ANATR
http://northwindazure.cloudapp.net/Northwind.svc/Customers('ANATR')?$expand=Orders/Order_Details
All order details for the first 3 customers
http://northwindazure.cloudapp.net/Northwind.svc/Customers?$top=3&$expand=Orders/Order_Details
All products in all orders for the first 3 customers
http://northwindazure.cloudapp.net/Northwind.svc/Customers?$top=3&$expand=Orders/Order_Details/Products

All the code is available on Codeplex at http://nwindazure.codeplex.com/.

If you want to read more about RESTful Northwind then go read my blog entry RESTful Northwind on SQL Azure where I also talk a little about how I attempted to use datadude (aka Visual Studio Team System for DB Pros aka whatever-its-called-today) against SQL Azure.

TwitterCache

This one is a lot simpler than Tweetpoll or RESTful Northwind, it is simply a SQL database hosted up on SQL Azure. In December 2009 Brent Ozar blogged about how he had been archiving tweets from various people that he follows into a SQL Server database and he made a backup of that database available for anyone that wanted to use it. I thought it would be cool to host that database up on SQL Azure both because it would demonstrate SQL Azure’s abilities and because it would save anyone else having to go through the rigmarole of restoring it themselves so I copied the schema up there and pumped the data in using SSIS.

Read more (including instructions on how to access it before it disappears) at TwitterCache now hosted on SQL Azure.

These apps/services will be disappearing in about one week from now so if you’re interested in using them go and do it now. Remember, the code for Tweetpoll and RESTful Northwind is up on Codeplex at:

Hope these have been useful to someone, they certainly have been for me!

@Jamiet

TwitterCache now hosted on SQL Azure

jamiet — Thu, 03 Dec 2009 22:41:00 GMT

2012-06-07 Update: All good things must come to an end - I have now shutdown this server. I hope it was useful to some of you in the two and a half years that it has been available.

Earlier today Brent Ozar blogged about how he had been archiving tweets from various people that he follows into a SQL Server database.

He made a backup of that database available for download on his blog so that others could download it and have a play of their own. I thought that rather than have all those people that wanted to party on the data download and restore the database for themselves it might be fun (and prudent) to stick it somewhere where anyone could get it so (with Brent’s permission) I’ve hosted the database up on SQL Azure.

Here are the credentials that you’ll need if you want to connect:

server name	lx49ykb7y5.database.windows.net
username	ro
password	r3@d0nly
database name	brentotweets

You can connect using SQL Server Management Studio although if you’re not using the November 2009 community technology preview (CTP) of SQL Server 2008 R2 there are some hoops you need to jump through in order to do that. First thing to know is that you can’t connect the Object Explorer, only a query window. So, after hitting the “New Query” button here’s what you need to do:

Firstly, enter the details as shown in this screenshot:

and then hit “Options”. On the options screen you need to enter the name of the database that you need to connect to which, as I mentioned above, is [brentotweets]:

Then hit “Connect”. You’ll probably get the following error about not being able to get to sys.configurations. Don’t worry about it, you can safely click OK and ignore it:

Once you’re in you can hit the database just like you would any other SQL Server database:

SQL Azure requires that you specify a range of IPs that can connect to the server and I’ve made it as wide as possible so I’m assuming it should be OK:

Have fun! Note that this won’t be available forever because at some point I’m going to have to start paying for it, you’ll be good for the next few weeks though.

@Jamiet

P.S. For those that are interested I pumped all the data up there using SSIS.

It took 41m 41s over my home broadband line from a Virtual PC guest. Bear in mind that I’m in London and the server is I believe somewhere in Texas, US. According to speedtest.net I’m getting 4.67Mbps up and 0.57Mbps down:

(Note to self: Get new ISP. Or move house!]

SSIS Junkie : twittercache

Tweetpoll and RESTful Northwind go bye-bye

Tweetpoll

RESTful Northwind

TwitterCache

TwitterCache now hosted on SQL Azure