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.






How to log all SQL Server and SQL Agent starts

SQL Server

To log all SQL Server starts, simply execute the T-SQL code provided below to create a new table and Stored Procedure (SP) to log this event:

USE LoggingDB
GO
CREATE TABLE dbo.SQLServerStartTimes
(
LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StartTime DATETIME NOT NULL
CONSTRAINT DF_ServerStartTimes_StartTime DEFAULT GETDATE()
)

USE master
GO
CREATE PROCEDURE dbo.sp_LogSQLServerStart
AS
BEGIN

SET NOCOUNT ON

PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO LoggingDB.dbo.SQLServerStartTimes DEFAULT VALUES
END
GO

To trigger/execute this SP once SQL Server starts, use this statement (more on MSDN about this built-in Stored Procedure)
EXEC SP_PROCOPTION sp_LogSQLServerStart, 'STARTUP', 'ON'
GO

Result:

SQL Agent

For SQL Agent starts, simply execute the T-SQL code provided below to create a new table and SP to log this event:


USE LoggingDB
GO
CREATE TABLE dbo.SQLAgentStartTimes
(
LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StartTime DATETIME NOT NULL
CONSTRAINT DF_SQLAgentStartTimes_StartTime DEFAULT GETDATE()
)

USE master
GO
CREATE PROCEDURE dbo.sp_LogSQLAgentStart
AS
BEGIN

SET NOCOUNT ON

PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO LoggingDB.dbo.SQLAgentStartTimes DEFAULT VALUES
END

Now, since all coding is done…we still need to setup a new SQL Agent job to execute this SP once SQL Agent starts.
Create a new job in SQL Agent and click on Schedules, select

Result:

Special thanks to Armando Prato for the inspiration!

How To: Generate a script to drop all Indexes

Well…here it is!
I know some of you are thinking, why would you ever do that?
In my case, I had to upload the entire database (about 40GB) to an external FTP-server.
It saved me a few GB’s to drop the indexes first and send them a SQL-script to (re-)create them πŸ˜‰

SELECT
'DROP INDEX ' + SS.name + '.' + SO.name + '.' + '[' + SI.name + ']'
FROM sys.objects as SO
INNER JOIN sys.schemas SS
ON SO.schema_id = SS.schema_id
INNER JOIN sys.indexes as SI
ON SO.Object_Id = SI.Object_Id
WHERE LEFT(si.Name,2) IN ('IX') --'IX' is the used naming prefix.
ORDER BY SO.Name , SI.name

SSIS / SQLCMD: Delete old TXT files

An easy way to delete old TXT files on your server is to use a SQL Agent Job and can be used with my previous GenerateScripts SP’s.

For this, I wrote a SP that uses the SQLCMD utility of SQL Server instead of SSIS because it’s lean and easy to implement.

A simple folder lookup by using SQLCMD:

I wrote to different Stored Procedures:
– Delete TXT files based on the file’s filename (for example:’_yyyymmdd‘)
– Delete TXT files based on the file’s creation/modification date

Download both SP’s here:
sp_DeleteOldTXTFiles

Stored Procedure: Automatically ‘Generate Scripts’ for local or linked server

Normally you would have done this manually:

Time for a change!
Schedule it in SQL Agent as a Stored Procedure and export it to a TXT-file.

SP GenerateScripts on LocalServer

On execution:

To export it to a TXT-file:
SQL Agent Job – ExportDatasetToTXT

Make sure that xp_cmdshell is enabled:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Automated query performance test

Just got back from San JosΓ© and San Fransisco!
Went there for two weeks to work on my project for LumiLEDs (Philips Lightning).

A great view on the Golden Gate Bridge!

 
I did something new: needed to test my new Stored Procedures (SP’s) on performance and make an comparison between the ‘new’ and the ‘old’ queries.
For this blog post I rewrote my original code to use AdventureWorksLT2008R2, but it’s easy to rewrite it for AdventureWorksLT.

Like a lot of things you start with an idea:

The basics:
Test two scenarios (S1 – old vs. S2 – new) on performance with a common set of ID’s (TestIDs table) and log the results (TestResults table). Each scenario has three queries/SP’s (Q1 to Q3) with the same resultset, but in this example they also share a common query.

The objects:

 
For this test I made two tables:
TestIDs, which contains a common set of IDs to test:

TestResults, well….for the results πŸ™‚

 
Each scenario has three queries (Q1 to Q3) to test, so in total I’ve made six SP’s.
For each scenario I made a SP to run the individual SP’s with a cursor called ‘sp_TestScript_S#‘.

 
How to start:
Execute the generated script to create all the objects:
Test_scripts

Don’t forget to install/attach AdventureWorksLT(2008R2).

Execute the SP called: dbo.sp_Populate_tbl_TestIDs
The table TestIDs should be filled with ID’s by now.

Now you need to execute both SP’s (‘sp_TestScript_S#‘) at the same time.
You may want to use SQL Agent Jobs and schedule them.

 
The results:
As soon as the SQL Agent Jobs are finished, have a look at the results.
Execute the SP called dbo.sp_TestResults and analyse the results:

For a more detailed overview execute the SP called dbo.sp_TestResult_Details.
In the preview below, I zoomed in on ‘sp_S2_Q3_ProductModelID’:

 
Conclusion:
In my development environment I first tested my new queries and took the ID’s with the longest processing time or the highest result count.
 
You may want to combine:
1000 x Max(ProcessTime)
+
1000 x Max(ResultCount)

Next: use these ID’s to fill up the TestIDs table (sort of a worst case scenario) and run both SQL Agent Jobs (S1 and S2) simultaneously.
 
Have fun with testing! πŸ˜€

Going independent as a freelancer/contractor!

Last month I made the decision to start working as a freelancer/contractor starting from the 1th of April 2011!
I’m very excited!

Launched my own company called BumbleBI IT (Dutch).