2017-11-16

Machine Learning Services and Result Sets part 2

Dave Mason SQL Server Machine Learning Services WITH RESULT SETS R Python

In the last post, I pondered the use of WITH RESULT SETS along with sp_execute_external_script when we don't know the structure of the OutputDataSet until run time. Let's look at another example. Here I'll use the rvest package to scrape a web page: each HTML <table> found on the web page becomes a data frame in the "tables" list (line 5). The first data frame in the list is returned to SQL Server (line 6).

2017-11-15

Machine Learning Services and Result Sets

Dave Mason SQL Server Machine Learning Services WITH RESULT SETS R Python

When you run an R or Python script in-database via the sp_execute_external_script stored procedure, result sets returned by the stored procedure are output with unnamed columns by default. Here's an example using R and the [WideWorldImporters] database. The input data is a simple query on the Application.Countries table. R creates a data frame from the input data and merely returns it back to SQL Server (as the "OutputDataSet" data frame).

2017-11-14

T-SQL Tuesday #96: Folks Who Have Made a Difference

Dave Mason T-SQL Tuesday

Last week while reviewing some of my old posts, I realized I had written 99 of them. For my 100th post, I asked for suggestions from the Twitterverse. There were some really great responses, including some that focused on the journey to 100: what I learned, who helped and influenced me along the way, etc. I was going to go in another direction, but this really dovetails nicely with the T-SQL Tuesday topic for November 2017.

2017-11-09

The PASS Website and Social Media Promotion

I haven't been involved in the SQL community for very long. Because of that, I haven't done much volunteering. But earlier this year, Andy Warren asked me to help out with the organization efforts for SQLSaturday Orlando 2017. Andy is a veritable fountain of knowledge when it comes to SQLSaturdays. He has a ton of great ideas on how to improve things for everyone involved: attendees, sponsors, speakers, and organizers. Seriously, check out his recent posts to see what I mean!

2017-11-06

Multiple Output Datasets With R and SQL Server

Dave Mason SQL Server R

Something I've been fooling around with lately is using R to scrape web pages for HTML table data. As an example, there are two HTML tables on this post about unpivoting the output of a RESTORE HEADER command in T-SQL. Basically, there's a "wide" HTML table with dozens of columns, and a "narrow" HTML table with just two.

2017-10-10

XE Profiler - Initial Thoughts

SSMS 17.3 was recently released. There's a raft of new items in the release. That includes "XE Profiler", which is what I want to focus on.

When you open SSMS 17.3 and navigate to the Object Explorer window, at the bottom you'll see the new "XE Profiler" node. Expand it and there are two items: "Standard" and "TSQL". Navigate back up the tree to Management, expand it, find "Extended Events", expand it, then expand "Sessions". You should also see two new Extended Events sessions: "QuickSessionStandard" and "QuickSessionTSQL".

2017-10-05

Visual Studio: Create a Python Environment from SQL Server 2017 Machine Learning Services

Dave Mason SQL Server 2017 Visual Studio Python

I'm starting to experiment with Python scripts in SQL Server 2017 using Machine Learning Services (In-Database). The problem is, I don't know Python. If I run into a Python error, the output I get from SSMS is not looking too helpful. My instincts tell me I'll be much better off developing and debugging Python code from a development tool. What I settled on was to use Visual Studio along with the Python interpreter that comes with SQL Server 2017 Machine Learning Services. I ran into a few issues that I'll review here.

2017-10-02

Regular Expressions With R And T-SQL

Dave Mason SQL Server R RegEx

Have you ever had the need to use Regular Expressions directly in SQL Server? I sometimes hear or see others refer to using RegEx in TSQL. But I always assume they're talking about the TSQL LIKE operator, because RegEx isn't natively supported. In TSQL's defence, you can get a lot of mileage out of LIKE and some clever pattern matching strings, even though it's not authentic RegEx.

2017-09-29

The End Of Tanking?

NBA Draft Lottery

As you may have heard, the NBA Board of Governors approved a revision to the Lottery Format for 2019. Many reports on this activity are proclaiming the incentive to "tank" has been eradicated and that the tanking problem has been solved. This couldn't be farther from the truth.

2017-09-18

Azure Virtual Machines: Initial Thoughts

Last week was a weird one for me. For the first time in more than 25 years, I found myself unemployed. I was anticipating using my newfound abundance of free time to learn as much as I could while I continued searching for my next employment opportunity. But my plans got scuttled: as a Floridian, I found myself coping with the aftermath of hurricane Irma. I was fortunate to have no damage to my home. There was plenty of cleanup to be done around the house, however (mostly downed tree limbs). I was one of the lucky few that had power at home. Many local friends were not so lucky. Some of them came over for a hot shower, the glory of air conditioning, and a sympathetic ear. Schools were closed for the week and my young son was home.