R and SQL Server

This post is example for querying SQL Server and visualizing data using twitter. Package used is ROBDC. Sample walk through code snippet provided.

	library(ROBDC)
	conn <- odbcDriverConnect("Driver=SQL Server; Server=10.10.10.10,1500; Database=TestDB; Uid=useyouruser; Pwd=useyourpwd;")
	resultsdata <- sqlQuery(conn, "SELECT distinct(name), COUNT(1) as 'ZCount' FROM [TestDB].[dbo].[TableA] S JOIN [TestDB].[dbo].[TableB] Z ON S.id = Z.id group by name having count(1) > 20")
	odbcClose(conn)
	dim(resultsdata)
	library(lattice)
	dotplot(resultsdata$name~resultsdata$ZCount)
	slist <- resultsdata$sname
	zcount <- resultsdata$ZCount
	ggplot(data.frame(zcount,slist), aes(zcount,slist)) + geom_point()
	#highlight points
	ggplot(data.frame(zcount,slist), aes(zcount,slist)) + geom_point(size=3, colour="#CC0000")
	#background color change
	p<-ggplot(data.frame(zcount,slist), aes(zcount,slist)) + geom_point(size=3)
	p + theme(panel.background = element_rect(fill = 'green', colour = 'red'))
	ggsave("d:\\splot.png")
	#bar chart
	p = ggplot(data.frame(slist,zcount), aes(slist,zcount,fill=zcount)) + geom_bar(stat="identity")
	p + scale_x_discrete(labels = abbreviate)
	ggsave("d:\\splot.png")

view raw RSqlServer.R hosted with ❤ by GitHub

Happy Learning!!!