Chapter 5
Data Acquisition Features: SQL Database

In this chapter, you will be guided through two projects that demonstrate how to work with SQL databases as a source of data for analysis. This will build on the foundational application built in the previous two chapters.

This chapter will focus on SQL extracts. Since enterprise SQL databases tend to be very private, we’ll guide the reader through creating an SQLite database first. This database will be a stand-in for a private enterprise database. Once there’s a database available, we will look at extracting data from the database.

This chapter’s projects cover the following essential skills:

• Building SQL databases.

• Extracting data from SQL databases.

The first project will build a SQL database for use by the second project.

In an enterprise environment, the source databases will already exist.

On our own personal computers, these databases don’t exist. For this reason, we’ll build a database...

Chapter 5
Data Acquisition Features: SQL Database

In this chapter, you will be guided through two projects that demonstrate how to work with SQL databases as a source of data for analysis. This will build on the foundational application built in the previous two chapters.

This chapter will focus on SQL extracts. Since enterprise SQL databases tend to be very private, we’ll guide the reader through creating an SQLite database first. This database will be a stand-in for a private enterprise database. Once there’s a database available, we will look at extracting data from the database.

This chapter’s projects cover the following essential skills:

• Building SQL databases.

• Extracting data from SQL databases.

The first project will build a SQL database for use by the second project.

In an enterprise environment, the source databases will already exist.

On our own personal computers, these databases don’t exist. For this reason, we’ll build a database...

Chapter 5
Data Acquisition Features: SQL Database

In this chapter, you will be guided through two projects that demonstrate how to work with SQL databases as a source of data for analysis. This will build on the foundational application built in the previous two chapters.

This chapter will focus on SQL extracts. Since enterprise SQL databases tend to be very private, we’ll guide the reader through creating an SQLite database first. This database will be a stand-in for a private enterprise database. Once there’s a database available, we will look at extracting data from the database.

This chapter’s projects cover the following essential skills:

• Building SQL databases.

• Extracting data from SQL databases.

The first project will build a SQL database for use by the second project.

In an enterprise environment, the source databases will already exist.

On our own personal computers, these databases don’t exist. For this reason, we’ll build a database...

5.3 Summary

This chapter’s projects covered two following essential skills:

• Building SQL databases. This includes building a representative of a production database, as well as building a test database.

• Extracting data from SQL databases.

This requires learning some SQL, of course. SQL is sometimes called the lingua franca of data processing. Many organizations have SQL databases, and the data must be extracted for analysis.

Also important is learning to work in the presence of precious production data. It’s important to consider the naming conventions, file system paths, and permissions associated with database servers and the files in use. Attempting to extract analytic data is not a good reason for colliding with production operations.

The effort required to write an acceptance test that uses an ephemeral database is an important additional skill. Being able to create databases for test purposes permits debugging by identifying problematic data, creating a test case...

5.4 Extras

Here are some ideas for the reader to add to this project.

5.4.1 Consider using another database

For example, MySQL or PostgreSQL are good choices. These can be downloaded and installed on a personal computer for non-commercial purposes. The administrative overheads are not overly burdensome.

It is essential to recognize these are rather large, complex tools. For readers new to SQL, there is a lot to learn when trying to install, configure, and use one of these databases.

See https://dev.mysql.com/doc/mysql-getting-started/en/ for some advice on installing and using MySQL.

See https://www.postgresql.org/docs/current/tutorial-start.html for advice on installing and using PostgreSQL.

In some cases, it makes sense to explore using a Docker container to run a database server on a virtual machine. See https://www.packtpub.com/product/docker-for-developers/9781789536058 for more about using Docker as a way to run complex services in isolated environments.

See https://dev.mysql...