Voice of the DBA

Tag: ETL

Quick CSV Import with dbaTools – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I was searching around for some sample data for a project. I stumbled on a CSV of data from the Internet Movie Database (IMDB). I wanted to quickly import this into a database to play with and query. I decided to use dbaTools since my csv python skills were a little rusty and I thought this would be quick.

This post looks at how easy this was.

Disconnected from the Internet

I had downloaded the CSV before getting on a plane, but once I was ready to import this, I had no access to the Internet. This meant I had no access to looking up commands or documentation. As a result, I decided PowerShell and dbatools would be the easiest way to do this.

I ran a quick Get-Help command and got the name of the command. Using wildcards, I could see the dbatools result below.

I then ran “Get-Help Import-DbaCsv” to get the syntax. Using the results below, I wanted to see how quickly this would run.

A Quick Experiment

With the docs above, I created a database and then entered this command.
```
$movie = Import-DbaCsv -SqlInstance localhost -Database IMDB -AutoCreateTable -Path "IMDB-Movie-Data.csv"
```
This was my experiment. Just try some values and see what happens here. This ran in less than a second, and from ADS, I could see the data had been imported.

When I checked my variable, I saw that things had just worked with the 1000 rows imported in less than half a second.

Success!

This wasn’t my main task, but using some tools and some past knowledge, I figured out how to accomplish this task quickly and get back to the process of writing SQL code to query the data.

SQLNewBlogger

My entire purpose here was to write some queries against this data, but I needed to import the data. Either ADS or SSMS have import wizards, but I’ve had various levels of success at times with them. I didn’t want to work through an ETL process. Once I saw how quick dbatools made this, I decided to write this post, based on the ease of getting something done.

You could easily duplicate this post, noting why you needed to do an import and how easy this was. You could compare this to SSMS or ADS, or even write about starting to use dbatools for this purpose.

This took me less than 10 minutes.
November 1, 2021
Chopping Off Data

Do you know the difference between XLS and XLSX? They’re both Excel formats, and many of us might just use one or the other. After all, the latest versions of Excel work with both, and if you’ve been using a spreadsheet for years, perhaps you stick with the older format when exchanging data with others.

As many of you might have seen, Public Health England recently learned there is a difference with large amounts of data. They found data was being chopped off in a spreadsheet because they were using the old XLS format, which only supports 65k rows. The newer XLSX format will support a million rows, but both numbers are far below what SQL Server, MySQL, PostgreSQL, and other platforms support. Those platforms support billions, and most are limited only by the storage available.

I know that pandemic has had many groups scrambling to assemble and analyze data. We have people building dashboards and gathering data together in numerous ways, from paper and pencil to Excel to (hopefully) enterprise databases. An import into a relational store would make more sense than Excel, but I also understand that setting a schema, dealing with ETL and different formats from different sources, and other issues are a pain. There is a reason data professionals get paid a lot of money for these tasks.

To me, this highlights one of the issues of working with SQL Server, MySQL, PostgreSQL, etc., in that they are cumbersome and difficult to get started with. Even if scientists chose Cassandra or MongoDB, there would be issues, because there aren’t easy, simple client tools that facilitate work with data sets coming in disparate text files and formats.

I don’t mean to excuse this, because IT professionals should know better. If you’re using XLS, stop. Data volumes increase and you don’t want to realize you’ve hit the limit after data is lost.

I get the ease and convenience of using Excel, but stop using it for major projects once we realize these are important. Once you realize this is data that needs to be intact, secured, and protected, put it in a real platform. Excel, PowerBI, and most tools can query SQL Server.

Use those tools where you need them and where you can, just don’t use them as your database.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

October 19, 2020
What Happened to Hadoop?

Six or Seven years back, Hadoop was the big thing. It was going to solve our big data analytic needs, it would provide cheap storage and query power with commodity servers. More and more companies were going to be using it. Microsoft invested in HDInsight, SQL Server got Polybase to query data directly from HDFS. I was seeing the Hadoop elephant everywhere. I still remember popping into a few sessions at SQLBits to try and learn a bit more about how Hadoop worked.

In the last couple years, Hadoop has somewhat dropped off the radar as “the thing” that most companies need to become data driven and deal with large amounts of unstructured data. I found this analysis that talks about why Hadoop hasn’t taken over the world.

The short answer? Real time needs, cloud computing, and containers. I think really, though, the complexity of Hadoop became a problem. It was too hard for most companies to deal with, and with too few willing to invest in the large infrastructure needed and skills required to manage a system. I’d say the same thing about Kubernetes, but it’s evolving rapidly to become easier, and it’s dirt simple in the cloud. I suspect we’ll see more Kubernetes cloud deployments than on premises.

The other issue is the Hadoop batches jobs, which isn’t what many organizations want. They already deal with, and complain about, plenty of relational batch jobs. Whether this is ETL to a warehouse, cube processing, or some other delay. They want queries on data in place, which is becoming more commonplace all the time.

Of course, one other important point from the piece is something I believe. The relational database, or data warehouse, is not going away. It’s still important to many organizations, and it’s useful to handle lots of reporting. With the growth of the SQL Server platform, you might even do more AI/ML analysis on your data in place, without the need to move it to an HDFS platform.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

March 2, 2020
Data Orchestration

We continue to deal with larger and larger data sets all the time. In fact, it seems that most people find themselves outgrowing the capabilities of some of their OLTP databases, often RDBMS stores, and need to upgrade hardware or re-architect software. It doesn’t matter if you have a 100GB database on a few cores or 10TB on dozens of cores, there is often a need to upgrade to meet our workload demands.

In addition to the transactional needs, there is a growing demand for reporting and analysis workloads. Some people use a separate warehouse, and some want to just query data where it is. Certainly ETL processes and platforms have grown tremendously over the last few decades for those that want to implement the former process, but there is plenty of demand for the latter. In fact, I’m amazed how many customers have inquired if Redgate’s SQL Clone product will enable them to do this and spread their workload to other systems (it’s not designed for this).

I’ve been thinking that with SQL Server 2019 we will start to access data where it lives, not move it to another place we want it. To me, this is more of what future data orchestration might involve. I ran across an article that takes a slightly different approach, thinking AI and other products will help better move data around, and perhaps that’s true, but I do think more and more we want to query data where it lives, and use larger, distributed compute platforms to do this.

The scale out capabilities of SQL Server 2019, with the separation of compute and storage in Big Data Clusters, is a huge change that I think will be the future for many of us that look to meet reporting needs. The ability to grow hardware to match the workload needs is huge. This alone is a good reason to think about doing this in a hybrid or public cloud scenario.

Of course this doesn’t come cheap, easy, or quick. There is work to be done to evolve systems, but it is an area I think is worth experimenting in during the coming year. I bet many companies would be interested in some PoC work here to determine how to better meet the reporting requirements of larger data sets. Perhaps this is something you suggest to someone in your organization.

Steve Jones

February 27, 2020