Press "Enter" to skip to content

Month: September 2018

Joining Your SQL Server On Linux VM To A Domain

Published 2018-09-05 by Kevin Feasel

Dylan Gray and Tejas Shah provides some tips on joining a SQL Server on Linux instance to an existing Active Directory domain:

AD authentication is a popular mechanism for login and user authentication. It works very well in many scenarios, especially for enterprise applications. AD authentication is a supported scenario on SQL Server on Linux. Configuring the Linux VM to join with Active Directory (AD) can be a little tricky at sometimes though, especially in a complex enterprise environment.

  • One error message you may see from “realm join” is “realm: Couldn’t join realm: This computer’s host name is not set correctly.” This is due to a generic hostname (e.g. “localhost”), an incorrect domain in your hostname (e.g. “host1.abcd.com” instead of “host1.contoso.com”), or a duplicate hostname on the domain. To fix this, edit /etc/hostname to have a unique hostname and reboot the machine. On Ubuntu, it can also be helpful to put the fully qualified domain name in /etc/hostname (e.g. “host1.contoso.com” instead of “host1”).

 

They provide in this post some of the low-hanging fruit answers, where the problem is in basic server configuration.

Comments closed

Column Order Matters For Indexes

Published 2018-09-05 by Kevin Feasel

Bert Wagner violates Betteridge’s Law of Headlines:

When beginning to learn SQL, at some point you learn that indexes can be created to help improve the performance of queries.

Creating your first few indexes can be intimidating though, particularly when trying to understand what order to put your key columns in.

Today we’ll look at how row store indexes work to understand whether index column order matters.

Despite the flagrant violation, you should check out Bert’s post, as it’s a good one.

Comments closed

Changing Language In SQL Server

Published 2018-09-05 by Kevin Feasel

Jeff Mlakar shows how to switch languages in SQL Server:

The SET LANGUAGE command allows us to choose a language for a session. By session here I mean by SPID. Each query tab you open in SSMS is another thread to the database and receives a SPID. This can be called by almost anyone who has permissions to access the database because it only requires membership in the public role to execute.

Now let us change the session language to Russian.

You can change the default language for all sessions, as well as switching language for a specific session.

Comments closed

The Two-Digit Cutoff For Years

Published 2018-09-05 by Kevin Feasel

Claudio Silva explains why a two-digit year may be interpreted differently in SQL Server versus Excel:

What do you read when you see some date in a format like “01-Jan-00 00:00:00.000”? Keep in mind that I’m talking about the output directly from the table and without any formatting.
1st of January seems to leave no doubt (just because there is no default date format starting with two digits for the year), but…what about the year part ’00’?
It stands for 1900 and the 3rd column is wrong?
Or it stands for 2000 and the DATEPART function is returning the wrong value?

This is why you want to stick with four-digit years.  But if you’re stuck with two-digit years for some reason, Claudio explains how you can get Excel and SQL Server to return the same results.

Comments closed

Network Analysis With Python In Power BI

Published 2018-09-05 by Kevin Feasel

Tori Tompkins shows us how to use the NetworkX package in Power BI:

The data I used was created to demonstrate this task in Power BI but there are many real-world network datasets to experiment with provided by Stanford Network Analysis Project. This small dummy dataset represents a co-purchasing network of books.

The data I loaded into Power BI consisted of two separate CSVs. One, Books.csv, consisted of metadata pertaining to the top 40 bestselling books according to Wikipedia and their assigned IDs. The other, Relationship.csv, was an edgelist of the book IDs which is a popular method for storing/ delivering network data. The graph I wanted to create was an undirected, unweighted graph which I wanted to be able to cross-filter accurately. Because of this, I duplicated this edgelist and reversed the columns so the ToNodeId and FromNodeId were swapped. Adding this new edge list onto the end of the original edgelist has created a dataset with can be filtered on both columns later down the line. For directed graphs, this step is unnecessary and can be ignored.

Once loaded into Power BI, I duplicated the Books table to create the following relationship diagram as it isn’t possible to replicate the relationship between FromNodeId to Book ID and ToNodeId to Book ID with only one Books table.

Read on for an example using this data set.

Comments closed

Kalman Filters With Spark And Kafka

Published 2018-09-04 by Kevin Feasel

Konur Unyelioglu goes deep into Kalman filters:

In simple terms, a Kalman filter is a theoretical model to predict the state of a dynamic system under measurement noise. Originally developed in the 1960s, the Kalman filter has found applications in many different fields of technology including vehicle guidance and control, signal processing, transportation, analysis of economic data, and human health state monitoring, to name a few (see the Kalman filter Wikipedia page for a detailed discussion). A particular application area for the Kalman filter is signal estimation as part of time series analysis. Apache Spark provides a great framework to facilitate time series stream processing. As such, it would be useful to discuss how the Kalman filter can be combined with Apache Spark.

In this article, we will implement a Kalman filter for a simple dynamic model using the Apache Spark Structured Streaming engine and an Apache Kafka data source. We will use Apache Spark version 2.3.1 (latest, as of writing this article), Java version 1.8, and Kafka version 2.0.0. The article is organized as follows: the next section gives an overview of the dynamic model and the corresponding Kalman filter; the following section will discuss the application architecture and the corresponding deployment model, and in that section we will also review the Java code comprising different modules of the application; then, we will show graphically how the Kalman filter performs by comparing the predicted variables to measured variables under random measurement noise; we’ll wrap up the article by giving concluding remarks.

This is going on my “reread carefully” list; it’s very interesting and goes deep into the topic.

Comments closed

Values Belong In Columns

Published 2018-09-04 by Kevin Feasel

John Mount argues that to reduce ambiguity, ensure that your values are columns on appropriate data frames:

Here is an (artificial) example.

chamber_sizes <- mtcars$disp/mtcars$cyl
form <- hp ~ chamber_sizes
model <- lm(form, data = mtcars)
print(model)
# Call:
# lm(formula = form, data = mtcars)
#
# Coefficients:
#   (Intercept)  chamber_sizes  
#         2.937          4.104

Notice: one of the variables came from a vector in the environment, not from the primary data.frame. chamber_sizes was first looked for in the data.frame, and then in the environment the formula was defined (which happens to be the global environment), and (if that hadn’t worked) in the executing environment (which is again the global environment).

Our advice is: do not do that. Place all of your values in columns. Make it unambiguous all variables are names of columns in your data.frame of interest. This allows you to write simple code that works over explicit data. The style we recommend looks like the following.

Read the whole thing.

Comments closed

Important Community Tools

Published 2018-09-04 by Kevin Feasel

Chrissy LeMaire shares some great community-driven Powershell modules:

dbops

dbops was created by Kirill Kravtsov.

dbops is a Powershell module that provides Continuous Integration/Continuous Deployment capabilities for SQL database deployments.

It is based on DbUp, which is DbUp is an open source .NET library that helps you to deploy changes to SQL Server databases. dbops currently supports both SQL Server and Oracle.

Read on for links to several more projects.

Comments closed

Using R With Excel

Published 2018-09-04 by Kevin Feasel

David Smith walks us through various ways to integrate R and Excel:

If you’re familiar with analyzing data in Excel and want to learn how to work with the same data in R, Alyssa Columbus has put together a very useful guide: How To Use R With Excel. In addition to providing you with a guide for installing and setting up R and the RStudio IDE, it provide a wealth of useful tips for working with Excel data in R, including:

  • To import Excel data into R, use the readxl package

  • To export Excel data from R, use the openxlsx package

  • How to remove symbols like “$” and “%” from currency and percentage columns in Excel, and convert them to numeric variables suitable for analysis in R

  • How to do computations on variables in R, and a list of common Excel functions (like RAND and VLOOKUP) with their R equivalents

  • How to emulate common Excel chart types (like histograms and line plots) using R plotting functions

David also shows how to run R within Excel.  One of the big benefits of readxl is that it doesn’t require Java; most other Excel readers do.

Comments closed

Installing External Python Modules In SQL Server

Published 2018-09-04 by Kevin Feasel

David Fowler shows how to import an external Python module into SQL Server Machine Learning Services:

But how do we go about installing them into SQL Server?  Now I’m a DBA and not a Python wizz so had to do a little digging to figure it out but to be honest, it’s fairly easy.

I don’t know how many other DBAs know that we can install these modules or even how to do it so I thought I’d write up a quick post explaining it.

First things first, you’re going to need to do this from your SQL Server.

Read on for the instructions.

Comments closed