Press "Enter" to skip to content

Month: November 2016

Building A Hadoop Cluster

Published 2016-11-28 by Kevin Feasel

I have a post on building a five-node Hadoop cluster using Docker containers:

Notice how 3bd shows up for pretty much all of these services.  This is not what you’d want to do in a real production environment, but because we want to use Docker and easily pass ports through, it’s the simplest way for me to set this up.  If you knew beforehand which node would host which service, you could modify the run.sh batch script that we discussed earlier and open those specific ports.

After assigning masters, we next have to define which nodes are clients in which clusters.

Click through for a screenshot-laden walkthrough.

Comments closed

Learning About HCatalog

Published 2016-11-28 by Kevin Feasel

Adam Diaz explains the basics of HCatalog:

HCatalog, also called HCat, is an interesting Apache project. It has the unique distinction of being one of the few Apache projects that were once a part of another project, became its own project, and then again returned to the original project Apache Hive.

HCat itself is described in the documentation as “a table and storage management layer” for Hadoop. In short, HCat provides an abstraction layer for accessing data in Hive from a variety of programming languages.  It exposes data stored in the Hive metastore to additional languages other than HQL. Classically, this has included Pig and MapReduce. When Spark burst onto the big data scene, it allowed access to HCat.

Given HDInsight’s predilection toward WebHCat over WebHDFS, this does seem like a good thing to learn.

Comments closed

Understanding Status In DMVs

Published 2016-11-28 by Kevin Feasel

Ewald Cress looks at a number of DMVs and how they expose query status:

sys.dm_exec_sessions: status

This metric is completely disjunct from the above ones, and mostly reflects attributes of a CSession class instance. The respective values are derived through the following decision tree:

  • If the internal Boolean member m_fIsConnReset is set, return dormant

  • Else if a flag living outside of CSession itself is set, return preconnect (I’ll touch on the source of this mystery flag below)

  • Else if a flag within the CSession itself is set (indicating that it has been provided some work to do) return running

  • Else return sleeping

It’s interesting to see how something so very similar can have so many different understandings.

Comments closed

Azure VM Auto-Shutdown

Published 2016-11-28 by Kevin Feasel

Dave Bermingham shows how to configure automatic shutdown of Azure VMs:

If you are like me, I try to make my Azure MSDN subscription credits stretch the entire month. I’m typically just building labs to try out new features or to demonstrate SQL Server Failover Clusters in Azure. A lot of the time I am testing some pretty large instance sizes with plenty of premium storage. As you can imagine, you can burn through $150 pretty quick with a few GS5 instances running.

I try to be mindful and shutdown or destroy instances once I am done with them, but occasionally I’ll get pulled away for other business, only to log in the next day and see my credit has expired because I forgot to turn off the VMs.

Click through for details, including a warning about storage.

Comments closed

Using SET NOCOUNT ON

Published 2016-11-28 by Kevin Feasel

Andy Levy discusses the SET NOCOUNT operation:

So what happened here?  When you execute a query against SQL Server, both your data and some additional information is sent back to the client. This additional information is sent via a separate channel which is accessible via the SqlConnection.InfoMessages (or if you’re still using classic ADO, the InfoMessage) event. When you run queries in SSMS, you see this information in the Messages tab of the results pane most often as X row(s) affected.

That’s where my new stored procedures were causing problems. Where the original procedures were returning only one event which corresponded to the number of records returned by the single query in each procedure. But now that I’m loading temp tables, I’m getting multiple messages back – at a minimum, a count of the records affected when loading the temp table plus a count of the records returned to the calling application.

Data layers which can’t handle information streams are rare, but they do show up in the wild sometimes.

Comments closed

Scheduling VM Backups

Published 2016-11-28 by Kevin Feasel

Jens Vestergaard shows how to schedule Azure VM backups:

In this wizard we are presented with three (3) areas of configuration; First we need to decide if it’s in Azure or On-Premises. By selecting Azure, we are left with only Virtual Machine as the only option for the backup. On-Premises has more options, SQL Server, Sharepoint and Hyper-V VM’s among others. This example will be about Azure VM’s, hence we selected accordingly.

Step 2 is about the backup policy, or in other words frequency and retention. I am going with the default settings here, but options are great as you can configure retention range for weekly, monthly and yearly backups in parallel.

It’s easy and like any other backups, might save your bacon later.

Comments closed

Numeric Ranges In Power Query

Published 2016-11-28 by Kevin Feasel

Chris Webb solves one form of the gaps and islands problem in Power Query:

In a comment on my post on Creating Sequences of Integers And Characters In Power BI/Power Query Lists a reader, Paul G, asked me the following question:

can you reverse this? e.g i have a list (1,2,3,5,7,8,9,12,13,14,15) can i convert this to (1-3, 5 ,7-9,12-15)

This got me thinking… I was sure it could be done in M, but would it be possible using just the UI? As far as I can see, it isn’t – there’s one crucial thing I can’t do – but I would be interested to see if anyone else can come up with a no-code solution.

Check out the comments for other attempts at solving the problem with as little code as possible.

Comments closed

SQL Server EXE Size

Published 2016-11-28 by Kevin Feasel

Arun Sirpal points out that sqlservr.exe is a lot smaller in 2012 and up as compared  to 2008 R2:

I never really noticed the difference before, but I understand why.

From 2012 onwards the architecture changed, it has been broken up into multiple DLLs. I can see the extra DLL files within the BINN folder these being sqllang.dll and sqlmin.dll where each are roughly 30MB each.

Makes me a bit curious as to the reason behind the breakout.

2 Comments

Checking File Permissions

Published 2016-11-25 by Kevin Feasel

Andrew Peterson runs chmod 664 on a database backup:

You’re attempting to RESTORE a SQL Server database backup to your Linux installation and you get the message:

Msg 3201, Level 16, State 2, Line 17
Cannot open backup device ‘C:\home\user\Downloads\AdventureWorks2012.bak’.
Operating system error 2(The system cannot find the file
specified.).
Msg 3013, Level 16, State 1, Line 17
RESTORE HEADERONLY is terminating abnormally.

If you’re going to administer SQL Server on Linux, it’s a good idea to check out the Unix-style permissions model.  It’s a bit different than what we’re used to on Windows, though it does make sense with a bit of practice.

Comments closed

HDFS Cheat Sheet

Published 2016-11-25 by Kevin Feasel

Tim Spann has produced a guide for HDFS shell commands:

There are number of commands that you may need to use for administrating your cluster if you are one of the administrators for your cluster.   If you are running your own personal cluster or Sandbox, these are also good to know and try. Do Not Try These In Production if you are not the owner and fully understand the dire consequences of these actions.   These commands will be affecting the entire Hadoop cluster distributed file system.  You can shutdown data nodes, add quotas to directories for various users and other administrative features.

Many of the commands in this list should be familiar if you know much about Linux or Unix administration, but there are some Hadoop-specific commands as well, like moveFromLocal and moveToLocal.

Comments closed