{"id":70897,"date":"2017-04-26T10:24:03","date_gmt":"2017-04-26T10:24:03","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=70897"},"modified":"2018-05-22T09:43:13","modified_gmt":"2018-05-22T09:43:13","slug":"python-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/python-sql-server\/","title":{"rendered":"Python in SQL Server"},"content":{"rendered":"

\n Anyone using R in SQL Server employs the procedure sp_execute_external_script<\/strong>, the first parameter of this being the language to use. The documentation rather obliquely says that “the script must be written in a supported and registered language<\/em>“. Until recently, the only language was R, but now a second supported language, Python, has appeared.\n<\/p>\n

\n Yes, at their recent ‘Data Amp’ conference, Microsoft announced that SQL Server 2017 will add Python as a supported language. From now on, any Python package will run natively in SQL Server. SQL Server R Services will be renamed as SQL Server Machine Learning Services since it will support both languages. This brings it more in line with Azure’s ‘Machine Learning’ product.\n<\/p>\n

\n It marks quite a remarkable turnaround since, back in 2010, Microsoft officially handed over responsibility for IronPython (and IronRuby) “to the community”. Despite optimistic reports that IronPython was not dead just resting<\/a>, it was in fact, dead.\n<\/p>\n

\n It was a shame to lose a simple, dynamic scripting language with robust typing, and strong data-processing capabilities, ideal for seeking out anomalies, trends, and patterns in data. The ‘Iron’ languages pioneered the dynamic runtime inherited subsequently by PowerShell, but they fell between two stools. Although there were great advantages for .NET programmers, in being able to reuse their existing C# assemblies directly within IronPython, it also meant straying from the idea of a true “cross-platform” language, and from the Python standard. It meant putting up with a few things that worked differently in IronPython (such as garbage collection). Python programmers weren’t interested, and PowerShell’s subsequent emergence dented the demand for a Python-based .NET scripting language.\n<\/p>\n

\n Now of course, Microsoft’s attitude is entirely different. Python in SQL Server makes perfect sense. It has become the natural scripting language for data science; the glue to create large-scale analysis processes. Its advanced predictive analytics, or machine learning, capabilities make it a “natural peer” to the R language, and means Microsoft can fully support data science applications that are written in Python. We’ll no longer need to export large volumes of SQL Server data, or using only sample data, for Python processing. We deploy the Python model right inside the T-SQL stored procedure.\n<\/p>\n

\n So, that strange first parameter to sp_execute_external_script <\/strong>now <\/strong>makes better sense. <\/strong>Why stop at one extra language, though? What other language should be added to ‘Machine Learning Services’ that would benefit from fast access to large sets of data? Scala and Spark maybe? It would be great to know your thoughts.\n<\/p>\n

\n

Commentary Competition<\/h4>\n

Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.<\/p>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":"

Anyone using R in SQL Server employs the procedure sp_execute_external_script, the first parameter of this being the language to use. The documentation rather obliquely says that “the script must be written in a supported and registered language“. Until recently, the only language was R, but now a second supported language, Python, has appeared. Yes, at……<\/p>\n","protected":false},"author":200703,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,47125],"tags":[],"coauthors":[7955],"class_list":["post-70897","post","type-post","status-publish","format-standard","hentry","category-blogs","category-editorials"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70897","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/200703"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=70897"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70897\/revisions"}],"predecessor-version":[{"id":71041,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70897\/revisions\/71041"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=70897"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=70897"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=70897"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=70897"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}