Review: Twitter Search functionality for SQL Server (by SQL# – SQLsharp)

sql-sharp-logo
SQL# (SQLsharp)
Expanding the capabilities of T-SQL


SQL# is a small but extensive library of useful functions to help you code more powerful SQL Stored Procedures and User-Defined Functions.

From a Social BI’s perspective, I wanted to review the new Twitter Search functionality of SQL# (SQLsharp), which is available in the Full Version of SQL#.

System specifications:
This review was performed on a laptop with Windows 7 and SQL Server 2008R2 installed on it. Since SQL# uses a lot of the .NET framework and extra coding in DLLs, this should also work on SQL Server 2012 (and SSDT).


Solomon Rutzky did a great job by adding the Twitterizer .Net library (discontinued) and Twitter Search functionality to SQL# in this new version. For documentation about SQL#, click here.


Twitter – SearchTweets
The Full Version of SQL# provides 65 more functions than the Free Version, and it includes the Twitter: SearchTweets. An important note here, if you would only like to send tweets (twitter updates) and use other ‘basic’ Twitter functionalities, the Free Version would suffice:

SQL# – Free Version

Twitter: BlockUser, CreateFavorite, DestroyDirectMessage, DestroyFavorite, DestroyStatus, FollowUser, GetBlocks, GetFavorites, GetFollowers, GetFriends, GetHomeTimeline, GetMentions, GetMessages, GetRetweetedBy, GetRetweets, GetRetweetsOfMe, GetSentMessages, GetStatus, GetUser, GetUserTimeline, Retweet, SendDirectMessage, UnBlockUser, UnFollowUser, Update, xAuth

SQL# – Full Version

Twitter: SearchTweets

For more info about SQL# and perhaps to download the free version of SQL#, go to the website SQLSharp.com


Setup your Twitter Application:
A prerequisite is to have a Twitter Application already setup and connected to your Twitter-account. You’ll need to have the ‘Consumer key‘, ‘Consumer secret‘, ‘Access token‘ and ‘Access token secret‘.

Here’s HowTo to setup Twitter, the Twitter application and fetch the required keys:
Documentation: Twitter Setup
How to setup a Twitter Application (API)

So I’ve setup my Twitter Application called ‘PowerPivotBook‘ after the self-study course I wrote earlier:
dev-twitter-com-application-settings


Install SQL# (by executing the provided *.sql file):
To install SQL#, just open the *.sql-file in SQL Server Management Studio (SSMS, or SSDT) and execute it:

Install-SQLSharp-Succes


Set Security to External Access first
After installing SQL#, you’ll first need to set the security of Twitterizer to have ‘external access’. Otherwise you’ll be getting this error:
SetSecurity-first

Execute this SQL-statement to set the security to ‘external access’:
EXEC SQL#.SQLsharp_SetSecurity 2, 'SQL#.Twitterizer'

The result:
SetSecurity-done


Twitter Update functionality (send a Tweet via SSMS):
It’s time to send out a test Tweet and let the world know we’re alive 🙂


DECLARE @ConsumerKey NVARCHAR(100),
@ConsumerSecret NVARCHAR(100),
@AccessToken NVARCHAR(100),
@AccessTokenSecret NVARCHAR(100)

SELECT @ConsumerKey = '', //---Needs to be filled in.
@ConsumerSecret = '', //---Needs to be filled in.
@AccessToken = '', //---Needs to be filled in.
@AccessTokenSecret = '' //---Needs to be filled in.

SELECT SQL#.Twitter_Update(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'TweetTest - '+CAST(GETDATE() AS VARCHAR(100)), null, null, null)

Send-TestTweet

The result:
PowerPivotBook

TestTweet-successfull


SQL#.Twitter_SearchTweets() (TVF)
Now it’s time to use the Twitter Search functionality!

DECLARE @ConsumerKey NVARCHAR(100),
@ConsumerSecret NVARCHAR(100),
@AccessToken NVARCHAR(100),
@AccessTokenSecret NVARCHAR(100)

SELECT @ConsumerKey = '', //---Needs to be filled in.
@ConsumerSecret = '', //---Needs to be filled in.
@AccessToken = '', //---Needs to be filled in.
@AccessTokenSecret = '' //---Needs to be filled in.

SELECT * FROM SQL#.Twitter_SearchTweets(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'PowerPivot', NULL)

The results:
tweet-search-results

Optional parameters
You’re able to minimize your search area by supplying optional parameters, like for example geo-location (geocode), language (lang) or max number of tweets (count):


DECLARE @Params SQL#.Type_HashTable
SET @Params = ''
SET @Params = @Params.AddItem('count', '100') --Max = 100
SET @Params = @Params.AddItem('lang', 'NL') --LanguageCode (2 letters, ISO2)
SET @Params = @Params.AddItem('geocode','52.3708305359,4.9040098190,50km') --Geocode for 'Amsterdam, NL'

SELECT * FROM SQL#.Twitter_SearchTweets(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'weer', @Params)

Twitter-search-optional-parameters


SQL#.Twitter_SearchTweets() (TVF) Return Table specs:

[StatusID] [bigint] NULL,
[Created] [datetime] NULL,
[InReplyToStatusID] [bigint] NULL,
[InReplyToUserID] [int] NULL,
[IsFavorited] [bit] NULL,
[IsTruncated] [bit] NULL,
[Source] [nvarchar](200) NULL,
[StatusText] [nvarchar](200) NULL,
[RecipientID] [int] NULL,
[TimeZone] [nvarchar](100) NULL,
[ScreenName] [nvarchar](100) NULL,
[UserName] [nvarchar](100) NULL,
[UserID] [int] NULL,
[Location] [nvarchar](100) NULL,
[PlaceID] [nvarchar](50) NULL,
[PlaceName] [nvarchar](500) NULL,
[PlaceFullName] [nvarchar](500) NULL,
[PlaceType] [nvarchar](500) NULL,
[PlaceCountry] [nvarchar](500) NULL,
[PlaceLatitude] [float] NULL,
[PlaceLongitude] [float] NULL,
[RateLimit] [int] NULL,
[RateLimitRemaining] [int] NULL,
[RateLimitReset] [datetime] NULL

You’ll be able to fetch the results and feed it into your ETL-process. Social BI is just a few steps away 🙂


Conclusion:
SQL# is a great addition to your cookbook and/or standard functionalities of SQL Server.

Free Version
If you would like to build a TweetBot that tweets automated updates, like for example job vacancies or weather updates, the Free Version of SQL# is a great toolkit. It’s free and easy to install, but as a best practice for security or practical reasons, create a dedicated (new) database for it or even a different SQL Server instance.

Full Version
Today’s BI is all about gathering social data, an easy way to add twitter search data to your BI-stack is by using the SQL# Full Version. Of course an experienced .NET developer can easily set things up with the Twitterizer-kit, but keep in mind that Twitter makes changes to its API and that requires maintenance. Nevertheless, for US$ 200,- (about 2~3 times an average hourly rate) you’ll be getting a lot more functionalities.






Advertisements

One thought on “Review: Twitter Search functionality for SQL Server (by SQL# – SQLsharp)

  1. Pingback: Social Analytics on Twitter and Xbox Live data (Halo 4 release) | Clint Huijbers' Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s