A Fine Slice Of SQL Server
Dave Giles shares some thoughts on how machine learning and econometrics relate:
What is Machine Learning (ML), and how does it differ from Statistics (and hence, implicitly, from Econometrics)?
Those are big questions, but I think that they’re ones that econometricians should be thinking about. And if I were starting out in Econometrics today, I’d take a long, hard look at what’s going on in ML.
Click through for some quick thoughts and several resources on the topic.
Comments closedSolomon Rutzky points out a few things which you can unintentionally change when running an ALTER TABLE [tbl] ALTER COLUMN [col] command:
If the column is
NOT NULL, then not specifyingNOT NULLwill cause it to become NULLable. The documentation for ALTER TABLE even states:ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable.
Let’s see for ourselves.
Solomon also has a couple collation-related items, including unexpected silent truncation when working with UTF-8 collations.
Comments closedH2O provides popular open source software for data science and machine learning on big data, including Apache SparkTM integration. It provides two open source python AutoML classes: h2o.automl.H2OAutoML and pysparkling.ml.H2OAutoML. Both APIs use the same underlying algorithm implementations, however, the latter follows the conventions of Apache Spark’s MLlib library and allows you to build machine learning pipelines that include MLlib transformers. We will focus on the latter API in this post.
H2OAutoML supports classification and regression. The ML models built and tuned by H2OAutoML include Random Forests, Gradient Boosting Machines, Deep Neural Nets, Generalized Linear Models, and Stacked Ensembles.
The post only has a few lines of code but there are a lot of working parts under the surface.
Comments closedAndrew Pruski doesn’t want to mess with a bunch of yaml files:
In previous posts I’ve run through how to deploy sql server to Kubernetes using yaml files. That’s a great way to deploy but is there possibly an easier way?
Enter Helm. A package manager for Kubernetes.
Helm packages are called charts and wouldn’t you know it? There’s a chart for SQL Server!
Helm comes in two parts. Helm itself is the client side tool, and tiller, which is the server side component. Details of what each part does can be found here.
They’re making it too easy now…
Comments closedGreg Low explains some of the tricky bits behind using filtered indexes:
If you think about it, if all we’re ever going to use is one part of the index, i.e. just the unfinalized rows, having an entry in there for every single row is quite wasteful, as although the vast majority of the index will never be used, it still has to be maintained.
So in SQL Server 2008, we got the ability to create a filtered index. Now these were actually added to support sparse columns. But on their own, they’re incredibly useful anyway.
I use these on occasion but less than I want to, and a big part of the reason why is in this post, particularly around parameters.
Comments closedBrent Ozar shares some of the trouble you might run into when database load testing:
Managers think that to simulate more load, they can just take the production queries and replay them multiple times, simultaneously, from the replay tool. We’ve already talked about how you can’t reliably replay deletes, but even inserts and updates cause a problem.
Say we’re load testing Stack Overflow queries, and our app does this:
UPDATE dbo.Users
SET Reputation = Reputation + 1
WHERE Id = 22656;If try to simulate more load by running that exact same query from 100 different sessions simultaneously, we’re just going to end up with lock contention on that particular user’s row. We’ll be troubleshooting a blocking problem, not the problem we really have when 100 different users run that same query.
Click through for several issues you can run into and Brent’s advice.
Comments closedI still have vivid memories of that night. I’d ordered pizza so that I could stay back at my hotel room and finish my punch list of things before go-live the next day. It was after 2am, and I was sitting at the kitchen counter of the Residence Inn in Kalamazoo, MI, the pizza box still open next to me as I worked my way through a large pepperoni.
I got to the item on my punch list for “delete all test appointments.” The logic here was pretty simple: All the test appointments were for the same imaginary test patient. Just find all of that person’s appointments, and delete them. I decided I would do this one doctor at a time to make sure I didn’t mess it up too badly.
It’s a harrowing story with a happy ending.
Comments closedGuy Shilo explains erasure coding, a new feature in Hadoop 3:
The benefits are, of course, space-saving, and for large files also improved performance (blocks striped across datanodes can be read in parallel, and less blocks are written because there is no x3 replication). The larger the file the more notable is the performance gain.
Erasure encoding is disabled by default and you can enable it for only certain directories in HDFS. Some articles like this one suggest thatbest practice is to enable Erasure coding only for “cold” data that you do not write often, and for “hot” data use regular replication. However, in my tests I did not witness any problem dealing with hot data (maybe it’s evident in larger scales).
Click through for the full story on how it works.
Comments closedSpark, Hive and Presto are all very different code bases. Spark is made up of 500K lines of Scala, 110K lines of Java and 40K lines of Python. Presto is made up of 600K lines of Java. Hive is made up of over one million lines of Java and 100K lines of C++ code. Any libraries they share are out-weighted by the unique approaches they’ve taken in the architecture surrounding their SQL parsers, query planners, optimizers, code generators and execution engines when it comes to tabular form conversion.
I recently benchmarked Spark 2.4.0 and Presto 0.214 and found that Spark out-performed Presto when it comes to ORC-based queries. In this post I’m going to examine the ORC writing performance of these two engines plus Hive and see which can convert CSV files into ORC files the fastest.
The results surprised me.
Comments closedAllison Tharp has a project to blink a set of Lifx lights a team’s color when they score:
The first step is to generate an API token via the Lifx API here (https://cloud.lifx.com/settings). Keep this token safe and don’t let others see it!
In my functions file, I created 3 new functions for controlling the lights: invoke-setLight, invoke-Pulse, and invoke-Breathe. To understand what the API was expecting, I followed the Lifx API documentation here. As far as API documentation goes, this one is pretty good. Most functions have an interactive portion at the bottom which allows you to test it out yourself and also see what inputs the API expects.
As a Bills fan, at least I wouldn’t have to worry about the lights wearing out from overuse.
Comments closed