Voice of the DBA

Author: way0utwest

A Good Use for Hadoop

Netflix is an amazing company. I’ve been a customer for over a decade, starting with the well known red envelopes that moved DVDs through the mail. Since then I’ve continued to subscribe as a streaming company and have enjoyed quite a few of their original programming efforts.

However I’ve also been fascinated by the way in which Netflix has made use of technology to grow a unique kind of company. They own very little, having used Amazon’s AWS to host their infrastructure and mostly serve content they acquire from contracts with various media companies. They are famous for their Chaos Monkey approach, having machines randomly fail to test the fault tolerance of their entire infrastructure. I read recently about the closing of their last, small data center, so outside of employees’ laptops, they keep all systems in the cloud.

However I ran across a post on the scale of their data flow, and it’s amazing. Apparently their events are generating 1.6PB a day of data. That’s incredible, and a scale at which very, very few of us will ever work. Personally, I think that’s interesting, but I’d prefer not to be working on a PB of changing data a day, but I might feel differently if I worked at a company like Netflix that has obviously been successful with dealing with data at that scale.

The post notes that they put this data into Hadoop, where it can then be queried. I’ve often wondered what the domains are for using Hadoop. Most of the people I know that have tried it aren’t really working at large scales. I think that a well built ETL process would allow their data to easily work in a SQL Server data warehouse, and possibly even the Azure SQL Data Warehouse. However at 1.6PM a day, Hadoop seems like a much better choice.

I’m curious how often that data is queried. Is most of that 1.6PB actually used in reporting, or is much of it lost in the shuffle and ignore? Is there a process to aggregate some of this raw data and then delete the older values? Can 1.6PBx365 actually be useful in analyzing your business? I’m sure some is, but wouldn’t a lot of those events lose their value over time?

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and LibSyn.

February 25, 2016
Grabbing Fitbit Data

Recently the T-SQL Tuesday topic was Power BI. A cool topic, and an interesting application.

I saw Rob Farley build a dashboard based on his Fitbit data. That was intriguing, so I went to get my data. I’ve worn a Fitbit for a couple years, recently having lost it. However, when I went to export my data from Fitbit, I found this.

Notice two things. One is the top announcement that my data belongs to me. Then notice the bottom left note that I can get 31 days of data.

Jerks, but feel free to substitute another word yourself.

Then I found this Fitbit Data Export site. I connected and ran this, which grabbed my data. I appreciate there’s a Github link with the source code, though I have no idea of what’s in Github corresponds to what’s running in Azure. After reading The God’s Eye View, it’s a little nerve wracking to use a service to connect somewhere that I have data stored, but Fitbit exposes some of this publically anyways, and besides, it’s not critical data for me.

In any case, I had a CSV of my data, which I am now going to upload into Power BI and play with it. Adding a CSV is straightforward.

February 24, 2016
SQL Server 2016: The Data Protection Version

I’ve spent a few years working with SQL Server encryption and security, trying to educate people on the various capabilities involved. There are some nice features available in the platform that can help you run a more secure SQL Server, but there has been a lot of room for improvement across the last few versions. The openness of the platform is somewhat tempered by the need for each of us to write a fair amount of code to properly secure our databases. That is the hard part as many developers don’t write secure code.

That is changing a bit. I am really excited about the SQL Server 2016 release in that there are a number of data protection mechanisms that are going to greatly expand how we can protect our data moving forward. From Row Level Security to Always Encrypted to Dynamic Data Masking, the number of new features that will help make security a bit easier is growing dramatically.

I certainly think Microsoft is definitely encouraging and supporting better, more secure coding practices. If you look in Books Online at MSDN, there is now a Security Center that covers a number of topics and organizes information in a much better way. Security has seemed like an afterthought in past documentation. From Encryption to SQL Injection to Auditing to even Metadata Visibility, it seems like the next version of SQL Server on premise, and in Azure, really is taking security more seriously.

I look forward to watching the platform evolve and security increasing over time. It does seem as though SQL Server has the fewest security holes in the platform, but I hope that many of us can take advantage of the changes in SQL Server over time and build applications that will be viewed as being secure as well.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

February 24, 2016
SQL Saturday Headshots

This past weekend was SQL Saturday #478 – Albuquerque, and I was happy to have the chance to attend again. I was at the first two, missing last year with another event. It was good to go back, and carpool down to Taos for a day of skiing beforehand. Three of us from Colorado went down( myself, Jason Horner, and Reeves Smith), meeting Tim Mitchell, Ginger Grant, and Amy Herold at Taos for a warm day of skiing. Not the best, but good company and a lot of fun.

Saturday was another well run event. Thanks to @keith_tate, @coffegrl, @ChrisHyde325 for all the work they put in. The event went off without a hitch, and it seemed that people really enjoyed the day of learning.

Jamey Johnston was there again, which I was glad to see. Jamey and I were at the Austin event a few weeks ago, and Jamey was taking pictures of the speakers. I got distracted and missed my chance, which I regretted. Fortunately, Jamey set up his equipment in Albuquerque and I got my chance again.

I took advantage of it.

Representing the Denver Broncos, US football champions, and having fun. However, I was serious at least for a few minutes.

All in all, a fun weekend and a good time.

Now I just need to get back to work and tackle a few of the things I learned while watching some Machine Learning, Power BI, Always Encrypted, and more. It’s a good time to be a SQL Server pro.

February 23, 2016