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:
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:
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:
Execute this SQL-statement to set the security to ‘external access’:
EXEC SQL#.SQLsharp_SetSecurity 2, 'SQL#.Twitterizer'
The result:
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)
The result:
‘PowerPivotBook‘
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:
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)
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.
Clint Huijbers' Blog 