Press "Enter" to skip to content

Curated SQL Posts

Time-Series Analysis With Box-Jenkins

Published 2018-08-30 by Kevin Feasel

The folks at Knoyd walk us through time series analysis using the Box-Jenkins method:

However, this approach is not generally recommended so we have to find something more appropriate. One option could be forecasting with the Box-Jenkins methodology. In this case, we will use the SARIMA (Seasonal Auto Regressive Integrated Moving Average) model. In this model, we have to find optimal values for seven parameters:

  • Auto Regressive Component (p)
  • Integration Component (d)
  • Moving Average Component (q)
  • Seasonal Auto Regressive Component (P)
  • Seasonal Integration Component (D)
  • Seasonal Moving Average Component (Q)
  • Length of Season (s)

To set these parameters properly you need to have knowledge of auto-correlation functions and partial auto-correlation functions.

Read on for a nice overview of this method, as well as the importance of making sure your time series data set is stationary.

Comments closed

Databricks Runtime 4.3 Released

Published 2018-08-30 by Kevin Feasel

Todd Greenstein announces Databricks Runtime 4.3:

In addition to the performance improvements, we’ve also added new functionality to Databricks Delta:

  • Truncate Table: with Delta you can delete all rows in a table using truncate.  It’s important to note we do not support deleting specific partitions.  Refer to the documentation for more information: Truncate Table

  • Alter Table Replace columns: Replace columns in a Databricks Delta table, including changing the comment of a column, and we support reordering of multiple columns.   Refer to the documentation for more information: Alter Table

  • FSCK Repair Table: This command allows you to Remove the file entries from the transaction log of a Databricks Delta table that can no longer be found in the underlying file system. This can happen when these files have been manually deleted.  Refer to the documentation for more information: Repair Table

  • Scaling “Merge” Operations: This release comes with experimental support for larger source tables with “Merge” operations. Please contact support if you would like to try out this feature.

Looks like a nice set of reasons to upgrade.

Comments closed

Getting Started With Linux

Published 2018-08-30 by Kevin Feasel

Kellyn Pot’vin-Gorman has a new series on learning Linux for SQL Server DBAs:

As this series of articles are published, it will be important that you have a way to do some hands on with Linux, not just read about this powerful operating system. To accomplish this, my recommendation is to download Docker for Windows.

Choosing to use Docker over a VM or a cloud deployment might seem odd at first, but I’ve found that Docker is the perfect option from the testing and classes I’ve led. The Docker image is light–using a minute number of resources on any laptop. Access to the docker image isn’t dependent upon Wi-Fi or the user having a cloud account. The image, for the most part, offers an experience that is incredibly similar to a full Linux server in functionality for no additional cost.

Once you’ve downloaded Docker and have it installed, create a Linux container with SQL Server on your workstation, (thanks to Microsoft) to work with and build your knowledge. Start by opening a Command Prompt, cmd from the search option in Windows.

This post is mostly setup and updating packages.  I also want to pitch We Speak Linux if you’re in this boat.  Kellyn did our inaugural webinar, too.

Comments closed

Running Powershell Files From SQL Operations Studio

Published 2018-08-30 by Kevin Feasel

Jay Robinson shows how you can configure SQL Operations Studio to run Powershell with the click of two buttons:

As I’ve used SQL Operations Studio more and more, I’ve also been finally using PowerShell in more situations. Given that I like the editor and that there’s a built-in terminal, I’ve been running those in my Ops Studio instance. But for a while I didn’t have a slick way of running an entire PowerShell file in the terminal. Usually, I’d just Ctrl+A/Ctrl+C/Ctrl+V, which is a bit awkward.

But among all the other ways you can customize Ops Studio, you have a lot of control over the key mappings. One way to edit these mappings is to pull up the Command Pallette (Ctrl+Shift+P) and start typing “key”, and you’ll see “Preferences: Open Keyboard Shortcuts”. You’ll also see it mentions the Ctrl+K/Ctrl+S shortcut. This will bring you to the basic Keyboard Shortcuts window, where you’ll need to click “keybindings.json”. Either way, just like Ops Studio’s overall settings (and VSCode’s, for that matter), you get a JSON file you can now tweak. Actually, two of them, with the defaults on the left and your own settings on the right.

Read on to see Jay’s mapping and an explanation of what he’s doing.

Comments closed

Normalization Code Smells

Published 2018-08-30 by Kevin Feasel

Erik Darling has a few tips to see if you’re not properly normalizing your tables:

First Sign Of Problems: Prefixed Columns

Do you have columns with similar prefixes?

If you have naming patterns like this, it’s time to look at splitting those columns out.

I took the Users and Posts tables from Stack Overflow and mangled them a bit to look like this.

You may not have tables with this explicit arrangement, but it could be implied all over the place.

One great way to tell is to look at your indexes. If certain groups of columns are always indexed together, or if there are lots of missing index requests for certain groups of columns, it may be time to look at splitting them out into different tables.

These things tend to happen, but they can have serious negative consequences for database performance, not to mention the risk of bad data sneaking in.

Comments closed

HA/DR With Azure SQL Database

Published 2018-08-30 by Kevin Feasel

James Serra explains the High Availability and Disaster Recovery options available when using Azure SQL Database:

High Availability (HA) – Keeping your database up 100% of the time with no data loss during common problems.  Redundancy at system level, focus on failover, addresses single predictable failure, focus is on technology.  SQL Server IaaS would handle this with:

  • Always On Failover cluster instances
  • Always On Availability Groups (in same Azure region)
  • SQL Server data files in Azure

Disaster Recovery (DR) – Protection if major disaster or unusual failure wipes out your database.  Use of alternate site, focus on re-establishing services, addresses multiple failures, includes people and processes to execute recovery.  Usually includes HA also.  SQL Server IaaS would handle this with:

  • Log Shipping

  • Database Mirroring

  • Always On Availability Groups (different Azure regions)

  • Backup to Azure

Click through for more details.

Comments closed

Customizing Live Extended Events Data

Published 2018-08-30 by Kevin Feasel

Grant Fritchey shows how to make the Extended Events UI a lot more useful:

WHAT!!!!?!!

You mean I can make that upper window useful? Yes. Not only that, but, what ever columns you add to the upper window, your copy of Management Studio remembers what you did. The next time you open this Extended Events session in the the Live Data window, the columns you selected will be there. However, your ability to customize Live Data is even easier than that. If you notice, in the upper part of your SSMS window is the Live Data tool bar. Near the end is a button innocuously titled “Choose Columns.” Click on that and a window similar to this will open:

And you can share the results with others.

Comments closed

Read Event Stream Function Missing When Viewing Extended Events File

Published 2018-08-30 by Kevin Feasel

Tibor Karaszi points out an annoying bug which can happen when viewing an Extended Events file:

Here’s the relevant text in the message box, to facilitate searching:

The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage)

Cannot view the function ‘fn_MSXe_read_event_stream’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

Read on for the solution, and if you’re interested in changing that behavior, vote up the UserVoice submission.

Comments closed

Examples Of Charts In Different Languages

Published 2018-08-29 by Kevin Feasel

David Smith points out a great repository of information on generating different types of charts in different libraries:

The visualization tools include applications like Excel, Power BI and Tableau; languages and libraries including R, Stata, and Python’s matplotlib); and frameworks like D3. The data visualizations range from the standard to the esoteric, and follow the taxonomy of the book Data Visualisation (also by Andy Kirk). The chart categories are color coded by row: categorical (including bar charts, dot plots); hierarchical (donut charts, treemaps); relational (scatterplots, sankey diagrams); temporal (line charts, stream graphs) and spatial (choropleths, cartograms).

Check out the Chartmaker Directory.

Comments closed

Using MLFlow For Binary Classification In Keras

Published 2018-08-29 by Kevin Feasel

Jules Damji walks us through classifying movie reviews as positive or negative reviews, building a neural network via Keras on MLFlow along the way:

François’s code example employs this Keras network architectural choice for binary classification. It comprises of three Dense layers: one hidden layer (16 units), one input layer (16 units), and one output layer (1 unit), as show in the diagram. “A hidden unit is a dimension in the representation space of the layer,” Chollet writes, where 16 is adequate for this problem space; for complex problems, like image classification, we can always bump up the units or add hidden layers to experiment and observe its effect on accuracy and loss metrics (which we shall do in the experiments below).

While the input and hidden layers use relu as an activation function, the final output layer uses sigmoid, to squash its results into probabilities between [0, 1]. Anything closer to 1 suggests positive, while something below 0.5 can indicate negative.

With this recommended baseline architecture, we train our base model and log all the parameters, metrics, and artifacts. This snippet code, from module models_nn.py, creates a stack of dense layers as depicted in the diagram above.

The overall accuracy is pretty good—I ran through a sample of 2K reviews from the set with Naive Bayes last night for a presentation and got 81% accuracy, so the neural network getting 93% isn’t too surprising.  Seeing the confusion matrix in this demo would have been a nice addition.

Comments closed