Extract

1. Extract

You made it to the third chapter. Impressive work so far. This chapter covers a concept that we often refer to as ETL in data engineering. ETL stands for Extract, Transform, and Load. We'll have one video on each of these steps. In the final video, we'll set up an ETL process using a scheduler we saw in the previous chapter.

2. Extracting data: what does it mean?

This first video is about data extraction or the extract phase in ETL. Now, what do we mean by extracting data? Very roughly, this means extracting data from persistent storage, which is not suited for data processing, into memory. Persistent storage could be a file on Amazon S3, for example, or a SQL database. It's the necessary stage before we can start transforming the data. The sources to extract from vary.

3. Extract from text files

First of all, we can extract data from plain text files. These are files that are generally readable for people. They can be unstructured, like a chapter from a book like Moby Dick. Alternatively, these can be flat files, where each row is a record and each column is an attribute of that record. The typical examples are comma- or tab-separated files, which use commas or tabs to separate the columns.

4. JSON

Another widespread data format is called JSON, or JavaScript Object Notation. JSON files hold information in a semi-structured way. It consists of 4 atomic data types: number, string, boolean and null. There are also 2 composite data types: array and object. JSON objects can be deeply nested, like in this example. They map neatly onto Python dictionaries, and the standard library has a `json` package to do the parsing for you, with `json.loads`. JSON got popular because so many web services use it to exchange data.

5. Data on the Web

At this point, it makes sense to do a crash course on the web. Most communication on the web happens through requests, and you can think of a request as a request for data. Every request gets a response. Browse to Google, and your browser requests the home page; Google's servers respond with the data that makes up that page.

6. Data on the Web through APIs

However, some web servers don't serve web pages that need to be readable by humans. Some serve data in a JSON data format. We call these servers APIs or application programming interfaces. GitHub, for example, hosts an API that returns information about users, organizations, and repositories in JSON format. Using the GitHub API does not tell us anything about how GitHub stores its data; it merely provides us with a structured way of querying it. Here's a request to the Hackernews API and the JSON that comes back. In Python, the `requests` package does the work: we call its `.get()` method with a URL, and the response object has a `.json()` helper that turns the incoming JSON into a Python object.

7. Data in databases

Finally, we have to talk about databases. The most common way of data extraction is extraction from existing application databases. Most applications, like web services, need a database to back them up and persist data. At this point, it's essential to make a distinction between two main database types. The databases behind applications are optimized for lots of transactions, where a transaction inserts or changes records. Think of a customer database: one row per customer, and a transaction adds a customer or changes an address. Those are called OLTP, or online transaction processing, and they're typically row-oriented. Databases built for analysis are called OLAP, online analytical processing, and they're often column-oriented. More on that later in this chapter.

8. Extraction from databases

To extract data from a database in Python, you'll always need a connection string, also called a connection URI. It holds everything needed to reach the database: the database type, a username and password, the host and port, and the database name. You hand that URI to a package like `sqlalchemy`, which gives you a database engine. That engine can then be passed to any package that supports it, like `pandas` in the example.

9. Let's practice!

Now that know saw the extract phase, let's look at some exercises!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.