code,  data science

Setup Your Local Data Warehouse Content

In last week’s post, I showed you how to get a free SQL database by setting up a MySQL database server on your desktop. Now, it’s time to finish setting up your local data warehouse that you can use it to practice your SQL chops.

We will use data that is posted on data.world to fill out our data warehouse. The first dataset is from the WICHE organization and shows you student enrollments and graduations in K-12 schools. This is a dataset that I uploaded to data.world years ago and I use it in demos like this. We will also download a file with information on US states so we have something to JOIN to in our demos.

Download Datasets

The first thing that you need to do is download the two datasets. Click here to go to the WICHE Enrollment Projections page on data.world. You will see a page that looks like this:

Click on the “Explore this dataset” that I highlighted in the screenshot above. In the next screen, click on the data table icon labeled wiche_graduate_projections (I have this highlighted in the screenshot below):

Now you will have a screen that shows you a sample of the content. Click on the highlighted button to download the data.

Don’t forget to note where you saved your file.

Repeat for US States

Repeat the steps above, but start with this link to download the US States dataset. When you are finished, you will have these two datasets in your downloads folder:

  • wiche_graduate_projections.csv
  • state_table.csv

Import Datasets Into Your Data Warehouse

The next step is to put these datasets into the data warehouse that you set up last week. If you have not done so already, get this free SQL database by following these instructions.

Start the MAMP app and click on “Start Servers” to run your database. Your web browser will open up a page with links to your local server. On this page, click on the TOOLS tab and then PHPMYADMIN link.

This will bring you to the PHPMyAdmin dashboard screen:

This is the program that you can use to create and delete databases as well as run SQL queries.

Create Database

Click on the Database tab to get to the screen where we will add our database.

The areas that we need to use are highlighted in red. Now we want to create a new database called higher_ed.

In the Create database form, type higher_ed into the first text box. Your database will be created and you will be presented with a form that could use to create new tables from scratch. Ignore that for now, but take a look at your left-hand column on your dashboard. You should see that your database has been created.

Import Datasets

Now that we have somewhere to put our datasets, it’s time to use the CSV files that we downloaded from data.world to populate our higher_ed database. In the PHPMyAdmin dashboard, click on the Import tab.

Click on the higher_ed database in the left-hand pane to make sure that we are working in the correct database. In the File to Import: tab click on the Choose File button and select the wiche_graduate_projections.csv that you downloaded at the beginning of this article.

Choose the option The first line of the file contains the table column names toward the bottom of the form. Now go to the bottom of the form and click the Go button. Your file will start importing into your database. This process will take a few minutes.

Now follow the same procedure for the state_table.csv file. At this point, your database will have both files available to use in queries. The tables will be named “TABLE 1” and “TABLE 2”, but you can rename these tables by going to the Operations tab, then Table Options, and then typing in the new table name that you want into the form next to the label Rename table to. Finally, click Go to make the change. I changed my filenames to projections and states.

Conclusion

At this point, you have a small database that you can use to practice your SQL coding on. You can use MySQL to create your own local databases. You will probably find that the web-based interface is not ideal. Alternatively, you can work with your local MySQL with the command line or with GUI apps like DBeaver.

Getting your own MySQL based system set up on your desktop is great when you are working on your own projects or if you want a database for your own side-hustle business. However, it may take some tinkering to get to work at first and you may end up learning more about being a DBA than you anticipated.

There is another option if you just want to practice your SQL skills, but you don’t want a local database. You can get a free account at data.world and upload datasets there. As you play around on this site, you will find that they have a query window that you can use to play around with your data.

Matt has worked as a data analyst, writer, counselor, and business owner for a total of 20 years. Since the start of his career he's been fascinated by technology and passionate about helping people use modern technology to hack their work and their lives.

Leave a Reply

Your email address will not be published. Required fields are marked *