A Fine Slice Of SQL Server
Devin Knight continues his Power BI custom visuals series:
In this module you will learn how to use the Enlighten Stack Shuffle Custom Visual. The Enlighten Stack Shuffle is helpful when you want to display a Top N set of values. For example if you want to display your top 5 selling employees this visual can make that very easy.
This looks pretty good on a dashboard, especially if you have a top-heavy data set, where the top few items are by far the most important.
Comments closedYou don’t have to be a data scientist to use machine learning in SQL Server. You can use pre-trained models available for usage out of the box to do your analysis. The following example shows you how you quickly get started and do text sentiment analysis.
Before starting to use this model, you need to install it. The installation is quick and instructions for installing the model can be found here: How to install the models on SQL Server
Once you have SQL Server installed with Machine Learning Services, enabled external script execution, and installed the pre-trained model, you can execute the following script to create a stored procedure that uses Python and the microsoftml function get_sentiment with the pre-trained model to determine the probability of positive sentiment of a text:
Click through to read the whole thing.
Comments closedRohan Kumar announces a new management interface for SQL Server (among other things):
These new cross-platform capabilities have made SQL Server accessible to users of Windows, Linux and Mac. At PASS Summit, we are excited to provide a sneak peek at Microsoft SQL Operations Studio. In a few weeks, users will be able to download and evaluate this free, light-weight tool for modern database development and operations on Windows, Mac or Linux machines for SQL Server, Azure SQL Database, and Azure SQL Data Warehouse. Increase your productivity with smart T-SQL code snippets and customizable dashboards to monitor and quickly detect performance bottlenecks in your SQL databases on-premises or in Azure. You’ll be able to leverage your favorite command line tools like Bash, PowerShell, sqlcmd, bcp and ssh in the Integrated Terminal window. Users can contribute directly to SQL Operations Studio via pull requests from the GitHub repository.
This appears to be a new cross platform (Mac, Linux, and Windows) for running queries against SQL Server.
Perhaps this is a replacement for SQL Server Management Studio, or may just a subset of what SSMS does today, however it works cross platform, unlike SSMS.
You won’t be giving up Management Studio anytime soon, but there are some really cool parts of SQL Operations Studio forthcoming.
1 CommentLi Jin talks about a performance optimization coming in Apache Spark 2.3:
To enable data scientists to leverage the value of big data, Spark added a Python API in version 0.7, with support for user-defined functions. These user-defined functions operate one-row-at-a-time, and thus suffer from high serialization and invocation overhead. As a result, many data pipelines define UDFs in Java and Scala, and then invoke them from Python.
Vectorized UDFs built on top of Apache Arrow bring you the best of both worlds—the ability to define low-overhead, high performance UDFs entirely in Python.
This looks like a good performance improvement coming to PySpark, bringing it closer to Scala/Java performance with respect to UDFs.
Comments closedAnubhav Tarar notes that you can run Hive queries against data even without a Hadoop installation:
Starting with release 0.7, Hive also supports a mode to run map-reduce jobs in local-mode automatically
you just have to do two things first create your warehouse in local system and give the default fs name to local put these properties inside your hive-site.xml
This is a fairly short post; click through to see the changes you’d make to hive-site.xml.
Comments closedSibanjan Das has started a new series on text mining in R:
Next, we need to preprocess the text to convert it into a format that can be processed for extracting information. It is essential to reduce the size of the feature space before analyzing the text. There are various preprocessing methods that we can use here, such as stop word removal, case folding, stemming, lemmatization, and contraction simplification. However, it is not necessary to apply all of the normalization methods to the text. It depends on the data we retrieve and the kind of analysis to be performed.
The series starts off with a quick description of some preprocessing steps and then building an LDA model to extract key terms from articles.
Comments closedI ran into an error after installing SQL Server 2017:
After installation completed, the DBA enabled SQL Server 2017 Machine Learning Services, but as soon as I tried to run a simple R script, it stalled for about 30 seconds and then I got an error:
Msg 39012, Level 16, State 1, Line 0
Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime.
STDERR message(s) from external script:
Error: could not find function “rxSqlUpdateLibPaths”
Execution halted
Click through for the solution.
Comments closedBut what happens if we try to use STRING_SPLIT with a multi-character separator, like this?
SELECT * FROM STRING_SPLIT(@GodawfulString, ‘,,’)
Msg 214, Level 16, State 11, Line 67
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.Ah. That’s a no, then.
Click through for the solution.
Comments closedFrank Gill has a script to find current Query Store context settings:
The following query will return the hex and integer value for each row in the table (NOTE: Query store must be enabled for the database to return values):
USE YourQueryStoreDatabase; SELECT set_options, CONVERT(INT, set_options) AS IntSetOptions FROM sys.query_context_settings;The set_options value represents a bit mask, with each binary digit representing a specific set option. The full list of values can be found here. I created stored procedure ReturnSetOptions to take the IntSetOptions from the query above and return the set options represented. The code for the procedure is listed below.
Read on to get a script which breaks the bitmask field into human-readable results.
Comments closedAdam Bertram shows how to write to a file using Powershell:
Set-Contentis one of those core PowerShell cmdlets that I can’t do without. I still remember using VBscript before we could use PowerShell to write to a file. I remember always trying to remember what kind of object I needed to use and the method name. Was it FileSystemObject, FileObject or what? It was a pain! Also, even when I did recall the method name wasCreateTextFile, I’d always forget to addTrueas the second argument.Here’s an example of the monstrosity I’m talking about.
Click through to see how easy writing is with Powershell.
Comments closed