<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/sql-saturday-statistics-web-scraping-with-r-and-sql-server/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2017/11/13/sql-saturday-statistics-web-scraping-with-r-and-sql-server/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I wanted to check a simple query: How many times has a particular topic been presented and from how many different presenters.</p>
<p>Sounds interesting, tackling the problem should not be a problem, just that the end numbers may vary, since there will be some text analysis included.</p>
<p>First of all, some web scraping and getting the information from <a rel="nofollow" href="http://www.sqlsaturday.com/" target="_blank">Sqlsaturday web page</a>. Reading the information from the website, and with R/Python integration into SQL Server, this is fairly straightforward task:</p>
<pre>EXEC sp_execute_external_script
 @language = N'R'
 ,@script = N'
 library(rvest)
 library(XML)
 library(dplyr)

#URL to schedule
 url_schedule &lt;- ''http://www.sqlsaturday.com/687/Sessions/Schedule.aspx''

#Read HTML
 webpage &lt;- read_html(url_schedule)

# Event schedule
 schedule_info &lt;- html_nodes(webpage, ''.session-schedule-cell-info'') # OK

# Extracting HTML content
 ht &lt;- html_text(schedule_info)

df &lt;- data.frame(data=ht)

#create empty DF
 df_res &lt;- data.frame(title=c(), speaker=c())

for (i in 1:nrow(df)){
 #print(df[i])
 if (i %% 2 != 0) #odd flow
 print(paste0(&quot;title is: &quot;, df$data[i]))
 if (i %% 2 == 0) #even flow
 print(paste0(&quot;speaker is: &quot;, df$data[i]))
 df_res &lt;- rbind(df_res, data.frame(title=df$data[i], speaker=df$data[i+1]))
 }
df_res_new = df_res[seq(1, nrow(df_res), 2), ]
OutputDataSet &lt;- df_res_new'</pre>
<p>Python offers Beautifulsoup library that will do pretty much the same (or even better) job as rvest and XML packages combined. Nevertheless, once we have the data from a test page out (in this case I am reading the Slovenian SQLSaturday 2017 schedule, simply because, it is awesome), we can “walk though” the whole web page and generate all the needed information.</p>
<p>SQLSaturday website has every event enumerated, making it very easy to parametrize the web scrapping process:</p>
<p><img data-orig-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-12-13_13_30-sqlsaturday-687-slovenia-2017-_-sessions-_-schedule.png?w=456" data-orig-size="400,50" data-medium-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-12-13_13_30-sqlsaturday-687-slovenia-2017-_-sessions-_-schedule.png?w=456?w=300" data-attachment-id="4887" data-image-meta="{" data-image-description="" src="https://tomaztsql.files.wordpress.com/2017/11/2017-11-12-13_13_30-sqlsaturday-687-slovenia-2017-_-sessions-_-schedule.png?w=456" data-permalink="https://tomaztsql.wordpress.com/2017/11/13/sql-saturday-statistics-web-scraping-with-r-and-sql-server/2017-11-12-13_13_30-sqlsaturday-687-slovenia-2017-_-sessions-_-schedule/#main" data-large-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-12-13_13_30-sqlsaturday-687-slovenia-2017-_-sessions-_-schedule.png?w=456?w=400" alt="2017-11-12 13_13_30-SQLSaturday #687 - Slovenia 2017 _ Sessions _ Schedule" data-image-title="2017-11-12 13_13_30-SQLSaturday #687 – Slovenia 2017 _ Sessions _ Schedule" sizes="(max-width: 400px) 100vw, 400px" data-comments-opened="1" class="wp-image-4887" data-recalc-dims="1"></p>
<p>So we will scrape through last 100 events, by simply incrementing the integer of the event; so input parameter will be parsed as:</p>
<p><a rel="nofollow" href="http://www.sqlsaturday.com/600/Sessions/Schedule.aspx" target="_blank">http://www.sqlsaturday.com/600/Sessions/Schedule.aspx</a></p>
<p><a rel="nofollow" href="http://www.sqlsaturday.com/601/Sessions/Schedule.aspx" target="_blank">http://www.sqlsaturday.com/601/Sessions/Schedule.aspx</a></p>
<p><a rel="nofollow" href="http://www.sqlsaturday.com/602/Sessions/Schedule.aspx" target="_blank">http://www.sqlsaturday.com/602/Sessions/Schedule.aspx</a></p>
<p>and so on, regardless of the fact if the website functions or not. Results will be returned back to the SQL Server database.</p>
<p>Creating stored procedure will go the job:</p>
<pre>USE SqlSaturday;
GO

CREATE OR ALTER PROCEDURE GetSessions
 @eventID SMALLINT
AS

DECLARE @URL VARCHAR(500)
SET @URL = 'http://www.sqlsaturday.com/' +CAST(@eventID AS NVARCHAR(5)) + '/Sessions/Schedule.aspx'

PRINT @URL

DECLARE @TEMP TABLE
(
 SqlSatTitle NVARCHAR(500)
 ,SQLSatSpeaker NVARCHAR(200)
)

DECLARE @RCODE NVARCHAR(MAX)
SET @RCODE = N' 
 library(rvest)
 library(XML)
 library(dplyr)
 library(httr)
 library(curl)
 library(selectr)
 
 #URL to schedule
 url_schedule &lt;- &quot;'
 
DECLARE @RCODE2 NVARCHAR(MAX) 
SET @RCODE2 = N'&quot;
 #Read HTML
 webpage &lt;- html_session(url_schedule) %>%
 read_html()

# Event schedule
 schedule_info &lt;- html_nodes(webpage, ''.session-schedule-cell-info'') # OK

# Extracting HTML content
 ht &lt;- html_text(schedule_info)

df &lt;- data.frame(data=ht)

#create empty DF
 df_res &lt;- data.frame(title=c(), speaker=c())

for (i in 1:nrow(df)){
 #print(df[i])
 if (i %% 2 != 0) #odd flow
 print(paste0(&quot;title is: &quot;, df$data[i]))
 if (i %% 2 == 0) #even flow
 print(paste0(&quot;speaker is: &quot;, df$data[i]))
 df_res &lt;- rbind(df_res, data.frame(title=df$data[i], speaker=df$data[i+1]))
 }

df_res_new = df_res[seq(1, nrow(df_res), 2), ]
 OutputDataSet &lt;- df_res_new ';

DECLARE @FINAL_RCODE NVARCHAR(MAX)
SET @FINAL_RCODE = CONCAT(@RCODE, @URL, @RCODE2)

INSERT INTO @Temp
EXEC sp_execute_external_script
 @language = N'R'
 ,@script = @FINAL_RCODE


INSERT INTO SQLSatSessions (sqlSat,SqlSatTitle,SQLSatSpeaker)
SELECT 
 @EventID AS sqlsat
 ,SqlSatTitle
 ,SqlSatSpeaker
FROM @Temp</pre>
<p> </p>
<p>Before you run this, just a little environement setup:</p>
<pre>USE [master];
GO

CREATE DATABASe SQLSaturday;
GO

USE SQLSaturday;
GO

CREATE TABLE SQLSatSessions
(
 id SMALLINT IDENTITY(1,1) NOT NULL
,SqlSat SMALLINT NOT NULL
,SqlSatTitle NVARCHAR(500) NOT NULL
,SQLSatSpeaker NVARCHAR(200) NOT NULL
)</pre>
<p> </p>
<p>There you go! Now you can run a stored procedure for a particular event (in this case SQL Saturday Slovenia 2017):</p>
<pre>EXECUTE GetSessions @eventID = 687</pre>
<p>or you can run this procedure against multiple SQLSaturday events and web scrape data from SQLSaturday.com website instantly.</p>
<p>For Slovenian SQLSaturday, I get the following sessions and speakers list:</p>
<p><img data-orig-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-13-19_19_46-49_blog_post-sql-sicn-kastrun-sqlsaturday-spar_si01017988-57_-microsoft.png?w=456" data-orig-size="627,601" data-medium-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-13-19_19_46-49_blog_post-sql-sicn-kastrun-sqlsaturday-spar_si01017988-57_-microsoft.png?w=456?w=300" data-attachment-id="4912" data-image-meta="{" data-image-description="" src="https://tomaztsql.files.wordpress.com/2017/11/2017-11-13-19_19_46-49_blog_post-sql-sicn-kastrun-sqlsaturday-spar_si01017988-57_-microsoft.png?w=456" data-permalink="https://tomaztsql.wordpress.com/2017/11/13/sql-saturday-statistics-web-scraping-with-r-and-sql-server/2017-11-13-19_19_46-49_blog_post-sql-sicn-kastrun-sqlsaturday-spar_si01017988-57_-microsoft/#main" data-large-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-13-19_19_46-49_blog_post-sql-sicn-kastrun-sqlsaturday-spar_si01017988-57_-microsoft.png?w=456?w=627" alt="2017-11-13 19_19_46-49_blog_post.sql - SICN-KASTRUN.SQLSaturday (SPAR_si01017988 (57))_ - Microsoft .png" data-image-title="2017-11-13 19_19_46-49_blog_post.sql – SICN-KASTRUN.SQLSaturday (SPAR_si01017988 (57))_ – Microsoft" sizes="(max-width: 627px) 100vw, 627px" data-comments-opened="1" class="wp-image-4912" data-recalc-dims="1"></p>
<p>Please note that you are running this code behind the firewall and proxy, so some additional changes for the proxy or firewall might be needed!</p>
<p>So going to original question, how many times has the query store been presented on SQL Saturdays (from SQLSat600 until  SqlSat690), here is the frequency table:</p>
<p><img data-orig-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-13-19_57_04-statistics_on_web_scraping_results-sql-sicn-kastrun-sqlsaturday-spar_si010179.png?w=456" data-orig-size="222,167" data-medium-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-13-19_57_04-statistics_on_web_scraping_results-sql-sicn-kastrun-sqlsaturday-spar_si010179.png?w=456?w=222" data-attachment-id="4923" data-image-meta="{" data-image-description="" src="https://tomaztsql.files.wordpress.com/2017/11/2017-11-13-19_57_04-statistics_on_web_scraping_results-sql-sicn-kastrun-sqlsaturday-spar_si010179.png?w=456" data-permalink="https://tomaztsql.wordpress.com/2017/11/13/sql-saturday-statistics-web-scraping-with-r-and-sql-server/2017-11-13-19_57_04-statistics_on_web_scraping_results-sql-sicn-kastrun-sqlsaturday-spar_si010179/#main" data-large-file="https://tomaztsql.files.wordpress.com/2017/11/2017-11-13-19_57_04-statistics_on_web_scraping_results-sql-sicn-kastrun-sqlsaturday-spar_si010179.png?w=456?w=222" alt="2017-11-13 19_57_04-Statistics_on_web_scraping_results.sql - SICN-KASTRUN.SQLSaturday (SPAR_si010179" data-image-title="2017-11-13 19_57_04-Statistics_on_web_scraping_results.sql – SICN-KASTRUN.SQLSaturday (SPAR_si010179" sizes="(max-width: 222px) 100vw, 222px" data-comments-opened="1" class="wp-image-4923" data-recalc-dims="1"></p>
<p>Or presented with pandas graph:</p>
<p><img data-orig-file="https://tomaztsql.files.wordpress.com/2017/11/session_stats.png?w=456" data-orig-size="373,358" data-medium-file="https://tomaztsql.files.wordpress.com/2017/11/session_stats.png?w=456?w=300" data-attachment-id="4926" data-image-meta="{" data-image-description="" src="https://tomaztsql.files.wordpress.com/2017/11/session_stats.png?w=456" data-permalink="https://tomaztsql.wordpress.com/2017/11/13/sql-saturday-statistics-web-scraping-with-r-and-sql-server/session_stats/#main" data-large-file="https://tomaztsql.files.wordpress.com/2017/11/session_stats.png?w=456?w=373" alt="session_stats" data-image-title="session_stats" sizes="(max-width: 373px) 100vw, 373px" data-comments-opened="1" class="wp-image-4926" data-recalc-dims="1"></p>
<p>Query store is popular, beyond all R, Python or Azure ML topics, but Powershell is gaining its popularity like crazy. Good work PowerShell people! <img alt="🙂" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png?w=456&ssl=1" class="wp-smiley" data-recalc-dims="1"></p>
<p>As always, code is available at <a rel="nofollow" href="https://github.com/tomaztk/SQL_Saturday_statistics-Web_Scraping_with_R_and_SQL_Server" target="_blank">Github</a>.</p>
<p> </p>
<p> <a rel="nofollow" href="https://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/4869/" target="_blank"><img border="0" alt="" src="https://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/4869/"></a> <img border="0" alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" data-recalc-dims="1"></p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2017/11/13/sql-saturday-statistics-web-scraping-with-r-and-sql-server/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/iCW8YYUJzTE" height="1"></a></div>