Handling Large Data with SQL Server

Hello Readers,

Big Data is all the rage these days. On this blog we have mainly used R as our analysis program of choice (sorry Python) to examine, model, and predict on the data. R is optimal for data with hundred thousands of rows or less, and dealing with larger data sets with millions of rows or more usually slows R down to a crawl. (Remember the neural network post?) Another deciding factor on computation speed is your computer setup. 

Because there are millions of rows in big data sets, we need only sample a few hundred thousand of them into R. But where will we store the millions of rows prior to sampling? Easy, into a data warehouse made for million plus row data sets, a SQL Server database. 

We have not dealt with hundreds of millions or billions of rows, so resist the urge to mention the Hadoop distributed file system. Or should I say not yet? Anyways, here we will load million plus row U.S. Census data sets in preparation for R sampling.

Getting Census Data

Specifically we concentrate on two data sets, U.S. Census American Community Survey 2011, where we can find nationwide population and housing data. You can download the data via ftp here, or choose 2011 ACS 1-year PUMS from the ACS site. The ACS encompasses social, economic, demographic, and housing data. A screenshot of the page is shown below:

Figure 1. American Community Survey (Census) Webpage

After clicking the red highlighted link, 2011 ACS 1-year PUMS, we choose from CSV or SAS file format. Take your pick, I chose CSV for importing into SQL Server.

Figure 2. Choose File Format - CSV

Next, the site gives us choices whether to download the entire dataset for the U.S. or individual states. Of course, download both the Population and Housing data for the entire U.S. Be warned, these two files take up nearly 1 GB of hard drive space, so be patient! Download accelerator anyone?

Figure 3. Download Population and Housing Data Files

After we have the zip files downloaded, unzip them. Note that the housing data 'ss11hus*' and population data 'ss11pus*' have parts a and b.

Figure 4. Unzipped Files

You can also find the helpful README file for the data at the ACS site as well.

Importing into SQL Server

Now for the crucial part, where we import the data into a database system, SQL Server, instead of reading the CSV files into R directly. After connecting to your server, select which database where you would import the CSV files. Right-click and under Tasks, > choose Import Files.

Figure 5. Import Files Option

Next we specify the data source. The CSV files are not Excel or Access files, rather they are a Flat File Source. So select the appropriate option, and select a CSV file from the unzipped file lcoation. On the left you can see a Columns view option under General. You can preview the file contents prior to importing it.

Figure 6. Select Flat File Source

Figure 7 shows the Columns for our preview, so we can ensure the proper headers and values are read by the SQL Server Wizard.

Figure 7. Column Previews

Even though we right-clicked a certain database, SQL Server still asks us in which database we want to import the data. You can change your mind, if you clicked wrong earlier, so take your pick.

Figure 8. Table Destination

Here we encounter the Source Table Option window. We can alter the schema and name of table with Edit Mapping at the bottom. The current schema and name are set as 'dbo' and 'ss11pusa', respectively. If you want to use a different schema than the default, now is the time to impose the change.

Figure 9. Select Source Tables

SQL Server gives us a last overview option before we start the importing process. Ready? Click Finish.

Figure 10. Last Check

And here we go! Look at the Import Wizard chug along as it imports over a million rows!

Figure 11. Import Progressing

When the process is finished, we are rewarded with... a new table lots of rows. Take a look below. 1.6 million? Not bad. Now you just have to import the remaining 3 for a complete set!

Figure 12. Execution Successful

Remember to refresh the Server instance for it to reflect the new table changes. Now you can query away in a new query window to explore the data, or you can keep the Server on and jump into R to establish a database connection using ODBC (open database connectivity).

Figure 13. Locating Imported Table

I thought this post made up for the previous few posts' lack of pictures. Was the compensation count this time sufficient (13)? Here we learned how import a large data set, separated into 4 tables, into a database management system, SQL Server. The next post will feature the next step in accessing Big Data in R, the database connection. There we shall use SQL Server as a data warehouse and R as the analytics tool. Stay tuned.

Thanks for reading,

Wayne
@beyondvalence
LinkedIn

Deploying Database Changes from Visual Studio to SQL Server 2012

Hello Readers,

Today we will demonstrate how to deploy a database using SQL Server Data Tools (SSDT), available in Visual Studio Shell. SSDT is a power tool where we can crease databases and database objects- and also perform database schema compares. Here I will show you how to connect Visual Studio to SQL Server and deploy (publish) a database.

SQL Server

When you login to SQL Server Management Studio, in the Object Explorer to the left, you can navigate through the databases. Here we have the famous AdventureWorks database already loaded. We aim to modify a column in the Person.Address table.

Figure 1. Object Explorer with AdventureWorks

Right-clicking the AddressLine1 column and select Properties to see the column properties. We will increase the length from 65 to 70, as an example.

Figure 2. AddressLine1 Properties

Data Tools in Visual Studio

Now that we have located the target column, we open Visual Studio to use the SQL Server Data Tools. First we need to create a new Project and a new connection to the AdventureWorks database in SQL Server.

Figure 3. Visual Studio New Project

Make sure you have selected the SQL Server Template to your left, and the SQL Server Database Project should appear in the middle dialogue box. Name your database and click OK. 

To connect to the SQL Server and database, right-click the project name and select Import   -> Database. Select New Connection and type in the server information and choose the database name from the drop down list towards the bottom of the Connection Properties window. You can click the Test Connection button at the bottom left, to see if you have the correct server typed in, and valid database selected.

Figure 4. Adding a New Connection

Also, change the Folder Structure in the Import Settings to Object Type.

Figure 5. Finishing Importing AdventureWorks

After you click Start, Visual Studio will begin to import AdventureWorks. After it is finished, in the Solution Explorer to the right, navigate to the Tables folder and select the first table, Address.sql. This table contains the column, AddressLine1, whose length we want to modify.

Figure 6. AddressLine1 Column in Address.sql Table

Simply click the Data Type nvarchar(65) and change it to nvarchar(70), and save all.

Deploying AdventureWorks

Since we have finished with our changes, we now need to deploy those changes into SQL Server. Right-click the AdventureWorks Project Folder in the Solution Explorer and select Publish. Re-enter the connection details and hit Publish. 

Figure 7. Publishing AdventureWorks

The middle highlighted box is the Publishing Options, and the bottom box shows the Output Dialogue Box. Note that it says the "Build: 1 succeeded".

Figure 8. Data Tools Output- Publishing

Turning back to SQL Server, in the Object Explorer, click the Refresh button with the circular arrows. Then navigate to the Person.Address Table and right-click the AddressLine1 column. Observe the change in length! It is now 70 characters long.

Figure 9. Checking AddressLine1 Length 70

Fantastic! We were able to connect to the AdventureWorks database in SQL Server, and change the length of a column in a table using SQL Server Data Tools in Visual Studio. Then we deployed those changes back into SQL Server and observed the changes. This is just a taste of what SQL Server is capable of when combined with SQL Server Data Tools- Visual Studio.

Stay tuned for more SQL posts!


Thanks for reading,

Wayne
@beyondvalence
LinkedIn

Importing Data into a Database Engine, SQL Server 2012

Hello Readers!

Welcome back. We need data to be able to query tables. So today we will walk through how to import data into a Database Engine in SQL Server Management Studio 2012. Click here for more info on SSMS 2012. 

Let us get started!

The Setup

First, open SQL Server Management Studio (SSMS). We are greeted with a dialogue box to connect to a server. I will use Windows Authentication to connect to my local server. 

Connect to a Server

After we have connected, we see the Object Explorer to the left of the screen. The Server will show a green arrow to indicate a successful connection. The Object Explorer allows us to view and manipulate the different utilities, tools, and capabilities in SSMS. The expanded explorer box is shown below. Note the green 'good to go' arrow by the server name.

Connection Established

Importing the Data

Now we need to import the data. It would be helpful if we had the target data already on the computer, and in this case, we will be importing the familiar AdventureWorks database which exists as an Access database file. We can also import it as an mdf (mirror disk file) from this link.

Start by right clicking the database to where the data will be imported. Now from Tasks, click Import Data... towards the bottom.

Import Data Button

In step, we are in the SQL Server Import and Export Wizard. As shown below. Select the appropriate data source and locate the file on the hard drive:

Select the Data Source and Location

After choosing the input specifications, we need to choose the output Destination. I will import the data into the POWER (short for PowerPivot) database.

Choosing the Destination

And then for the nitty-gritty of specifying which tables to copy. We have two options: manual selection or SQL query. Select either, I will select manual because I will be copying all the data.

Specify Which Selection Method

After which, we can check the boxes of the tables we require. Clicking the box at the top left will select all of the tables. Click Next.

Selecting Tables

We are then given a review of the selected tables before they are imported into the database. The table attributes and types are shown to verify the correct tables have been selected, below.

Data Type Review

Clicking Next and Finish, we now have the tables in the POWER database. The Object Explorer below reflects the newly imported tables.

Object Explorer with New Tables

And with the new query window open, we can now query tables that we require!

Blank Query Window

That concludes this post on how to import data into a database in SQL Server Management Studio 2012. Future posts will include SQL querying and use of the SSMS Analysis Services to analyze the data. Also, I will include a post on using R to connect to SQL Server to retrieve tables. Please look forward to the new posts!

Thanks for reading!

Wayne