Press "Enter" to skip to content

Author: Kevin Feasel

What’s New With Machine Learning Services

Niels Berglund looks at SQL Server 2019’s Machine Learning Services offering for updates:

So, when I read What’s new in SQL Server 2019, I came across a lot of interesting “stuff”, but one thing that stood out was Java language programmability extensions. In essence, it allows us to execute Java code in SQL Server by using a pre-built Java language extension! The way it works is as with R and Python; the code executes outside of the SQL Server engine, and you use sp_execute_external_script as the entry-point.

I haven’t had time to execute any Java code as of yet, but in the coming days, I definitely will drill into this. Something I noticed is that the architecture for SQL Server Machine Learning Services has changed (or had additions to it).

That Java support is for Spark, I’d imagine.  And I hope they allow for Scala.

Comments closed

Rayshader: 3D Surface Plotting In R

David Smith looks at an interesting package in R:

Tyler describes the rayshader package in a gorgeous blog post: his goal was to generate 3-D representations of landscape data that “looked like a paper weight”. (Incidentally, you can use this package to produce actual paper weights with 3-D printing.) To this end, he went beyond simply visualizing a 3-D surface in rgl and added a rectangular “base” to the surface as well as shadows cast by the geographic features. He also added support for detecting (or specifying) a water level: useful for representing lakes or oceans (like the map of the Monterey submarine canyon shown below) and for visualizing the effect of changing water levels like this animation of draining Lake Mead.

It looks great.

Comments closed

Apache Pulsar Now A Top-Level Project

George Leopold reports on Apache Pulsar:

Apache Pulsar is touted as a highly scalable, low-latency messaging platform running on commodity hardware. Besides Yahoo (NASDAQ: AABA), current enterprise users include Zhaopin Ltd., the Chinese online recruitment service. Zhaopin said Apache Pulsar addresses “the shortcomings of existing messaging systems, such as message durability, low latency.”

Other early enterprise users said they are using the messaging system as a bridge between public and private clouds as they roll out hybrid cloud strategies. Other early uses include stream processing and analysis of industrial Internet of Things sensor data. Most emerging use cases seek to move beyond slow batch processing, Pulsar supporters said.

Now that it’s a top-level Apache project, it’ll be interesting to see if it eats away at Kafka’s market share.

Comments closed

SOS_WORK_DISPATCHER

Joe Obbish digs into a new wait type in SQL Server 2019:

Upon upgrading to SQL Server 2019 CTP2, you may see the new SOS_WORK_DISPATCHER wait type at the top of the list:

The above screenshot is server level wait stats from my four core desktop after SQL Server was running for a few hours. SQL Server wasn’t really doing much since start up, so it felt unlikely that this wait was a sign of a problem. However, I was curious about what this wait type meant and wanted to know more.

Click through for Joe’s findings and what you should do with this wait type.

Comments closed

String Or Binary Data Would Be Truncated

Denis Gobo shares one of the biggest improvements in SQL Server 2019 CTP 2.0:

And there we go, you get the table name, the column name as well as the value, notice that the message id changed from 8152 to 2628 now

Msg 2628, Level 16, State 1, Line 20
String or binary data would be truncated in table ‘truncatetest.dbo.TruncateMe’, column ‘somevalue’. Truncated value: ‘33333’.
The statement has been terminated.

So it looks it only returns the first value that generates the error, let’s change the first value to fit into the column and execute the insert statement again

It’s not perfect, as it only shows one column from the first failed row, but that is still a lot more information than we had before and I’m happy that this is making into the product.

Comments closed

Estimating Columnstore Index Size

Niko Neugebauer is somewhat impressed with an update to sp_estimate_data_compression_savings:

The final results were pretty much amazing – 2.1 GB, making the estimation of the sys.sp_estimate_data_compression_savings System Stored Procedure much more precise then my own function! This leaves me very happy and makes me want to investigate and learn how this new stored procedure is capable of providing better estimations.

I decided to test on the other tables within TPCH database and my test on the Orders table have shown a different situation where the 0.7 GB estimation of the sys.sp_estimate_data_compression_savings Stored Procedure were pretty much offbeat when comparing to the CISL dbo.cstore_sp_estimate_columnstore_compression_savings – showing 0.92 GB while the end result was 0.89 GB.
I guess the final answer is that it will depend, but that the estimation of the new stored procedure are not totally offbeat is an incredibly good sign, though I would still keep an eye or even two on the provided estimated results.

Read the whole thing for more details on these examples.

Comments closed

Parsing Numeric Values From Multiple Cultures

Bert Wagner shows us a good way of converting strings to numbers when multiple cultures are in play:

Why are the salaries stored as nvarchar and formatted with commas, spaces, and periods?

Great question!  Someone wanted to make sure these amounts would look good in the UI so storing the formatted values in the database would be the way to go…

Pretty for the UI, not really great for needing to do analysis on.

Admittedly, TRY_PARSE is slow, but for a one-time conversion as you try to fix a bad idea, it’s fine.

Comments closed

Hadoop + SQL Server In 2019

Travis Wright shows off a big part of what the SQL Server team has been working on the last couple of years:

SQL Server 2019 big data clusters provide a complete AI platform. Data can be easily ingested via Spark Streaming or traditional SQL inserts and stored in HDFS, relational tables, graph, or JSON/XML. Data can be prepared by using either Spark jobs or Transact-SQL (T-SQL) queries and fed into machine learning model training routines in either Spark or the SQL Server master instance using a variety of programming languages, including Java, Python, R, and Scala. The resulting models can then be operationalized in batch scoring jobs in Spark, in T-SQL stored procedures for real-time scoring, or encapsulated in REST API containers hosted in the big data cluster.

SQL Server big data clusters provide all the tools and systems to ingest, store, and prepare data for analysis as well as to train the machine learning models, store the models, and operationalize them.
Data can be ingested using Spark Streaming, by inserting data directly to HDFS through the HDFS API, or by inserting data into SQL Server through standard T-SQL insert queries. The data can be stored in files in HDFS, or partitioned and stored in data pools, or stored in the SQL Server master instance in tables, graph, or JSON/XML. Either T-SQL or Spark can be used to prepare data by running batch jobs to transform the data, aggregate it, or perform other data wrangling tasks.

Data scientists can choose either to use SQL Server Machine Learning Services in the master instance to run R, Python, or Java model training scripts or to use Spark. In either case, the full library of open-source machine learning libraries, such as TensorFlow or Caffe, can be used to train models.

Lastly, once the models are trained, they can be operationalized in the SQL Server master instance using real-time, native scoring via the PREDICT function in a stored procedure in the SQL Server master instance; or you can use batch scoring over the data in HDFS with Spark. Alternatively, using tools provided with the big data cluster, data engineers can easily wrap the model in a REST API and provision the API + model as a container on the big data cluster as a scoring microservice for easy integration into any application.

I’ve wanted Spark integration ever since 2016 and we’re going to get it.

Comments closed

Combining Parameters And Prompts In Powershell

Shane O’Neill compromises with a co-worker on building a Powershell function:

As you may have guessed…

…this did not go down well with my co-worker.

That?! How are we supposed to know to write “!?” there? How does that even mean help anyway? Can we not just get the help message to show up on it’s own!

To which I said…

We can but it’s going to create a massive bottleneck! If we try and put in the help messages then it’s going to slow things down eventually because we’re not going to be able to run this without someone babysitting it! Do you want to be the person that has to sit there watching this every single time it’s run?

Compromise is a wonderful thing though and we eventually managed to merge the best of both worlds…

Shane gives us a solution and has a couple of updates for simpler solutions to boot.

Comments closed

Formatting Summary Tables In R

Laura Ellis shows us how to create formatted tables using the formattable package in R:

We are going to narrow down the data set to focus on 4 key health metrics. Specifically the prevalence of obesity, tobacco use, cardiovascular disease and obesity. We are then going to select only the indicator name and yearly KPI value columns. Finally we are going to make extra columns to display the 2011 to 2016 yearly average and the 2011 to 2016 metric improvements.

Tables are an area of data visualization that we tend to forget at our own peril.

Comments closed