4. Importing and Exporting Data

Overview

In this chapter, we will look at ways in which we can move data between our database and our analytics tools. The first tool that we will look at is the command-line psql tool, which enables you to quickly query data from our database. With psql, we can also leverage the COPY command, which allows for the efficient importing and exporting of data. With these simple tools, you will be able to interact with the database and efficiently move data back and forth. By the end of this chapter, you will be able to import and export data. We will process and analyze data using Excel, Python, and R. Later in the chapter, we will explore SQLAlchemy's advanced functionality for interacting with your database in Python.

Introduction

In order to extract insights from your database, you need data. And, while many companies store and update data within a central database, there are scenarios in which you will need more data than is currently in your database. In this chapter, we are going to explore how we can efficiently upload data to our centralized database for further analysis.

Not only will we want to upload data to our database for further analysis, but if we are doing advanced analytics, there will also be situations wherein we will need to download data from our database (for example, if we want to perform a statistical analysis that is unavailable in SQL). For this reason, we will also explore the process of extracting data from our database. This will allow you to use other software to analyze your data.

In this chapter, we will look at how you can integrate your workflows with two specific programming languages that are frequently used for analytics: Python and R. These languages are...

The COPY Command

At this point, you are probably familiar with the SELECT statement (covered in Chapter 1, Introduction to SQL for Analytics), which allows us to retrieve data from our database. While this command is useful for small datasets that can be scanned quickly, we will often want to save a large dataset to a file. By saving these datasets to files, we can further process or analyze the data locally using Excel, Python, or R. In order to retrieve these large datasets, we can use the Postgres COPY command, which efficiently transfers data from a database to a file, or from a file to a database.

The COPY statement retrieves data from your database and dumps it into the file format that you choose. For example, consider the following statement:

COPY (SELECT * FROM customers LIMIT 5) TO STDOUT WITH CSV HEADER;

The following is the output of the code:

Figure 4.1: Using COPY to print the results to STDOUT in a CSV file format

This statement returns...

Using R with Our Database

At this point, you can now copy data to and from a database. This gives you the freedom to expand beyond SQL to other data analytics tools (such as Excel) and incorporate any program that can read a CSV file as input into your pipeline. While almost any analytics tool can read a CSV file, you will still need to download the data. Adding more steps to your analytics pipeline can make your workflow more complex. Complexity can be undesirable because it necessitates additional maintenance and because it increases the number of failure points.

Another approach is to connect to your database directly in your analytics code. In this part of the chapter, we are going to look at how to do this in R—a programming language designed specifically for statistical computing. Later in the chapter, we will look at integrating our data pipelines with Python as well.

Why Use R?

While we have managed to perform aggregate-level descriptive statistics on our data...

Using Python with Our Database

While R has a breadth of functionality, many data scientists and data analysts are starting to use Python. Why? Because Python offers a similarly high-level language that can be easily used to process data. While the number of statistical packages and functionality in R can still have an edge over Python, Python is growing fast and has generally overtaken R in most recent polls. A lot of Python's functionality is also faster than R, in part because so much of it is written in C, a lower-level programming language.

The other large advantage that Python has is that it is very versatile. While R is generally only used in the research and statistical analysis communities, Python can be used to do anything from statistical analysis to building a web application. As a result, the developer community is much larger for Python. A larger development community is a big advantage because there is better community support (for example, on Stack Overflow)...

Best Practices for Importing and Exporting Data

At this point, we have seen several different methods for reading and writing data between our computer and our database. Each method has its own use case and purpose. Generally, there are going to be two key factors that should guide your decision-making process:

• You should try to access the database with the same tool that you will use to analyze the data. As you add more steps to get your data from the database to your analytics tool, you increase the ways in which new errors can arise. When you can't access the database using the same tool that you will use to process the data, you should use psql to read and write CSV files to your database.
• When writing data, you can save time by using the COPY or \copy commands.

Going Passwordless

In addition to everything mentioned so far, it is also a good idea to set up a .pgpass file. A .pgpass file specifies the parameters that you use to connect to your database...

Summary

In this chapter, we learned how to interface our database with other analytical tools for further analysis and visualization. While SQL is powerful, there will still be those odd analyses which need to be undertaken in other systems. To solve this problem, SQL allows you to transfer data in and out of the database for whatever tasks you may require.

Initially, we looked at how we can use the psql command-line tool to query our database. From there, we were able to explore the COPY command and the psql-specific \copy command, which enabled us to import and export data to and from our database in bulk. Next, we looked at programmatically accessing our database using analytical software such as R and Python. From there, we were able to explore some of the advanced functionality in Python, including SQLAlchemy and pandas, which enabled us to perform data visualization.

In the next chapter, we will examine data structures that can be used to store complex relationships in...