In this section, we will talk about getting data from SQLite3 into Haskell. We're going to understand the basic types within SQLite3 and their Haskell counterparts. We're also going to be installing the necessary software in order to get Haskell and SQLite3 to communicate with each other, and we're going to be writing a few SELECT queries within Haskell.

There are a few different data types in SQLite3 with their Haskell counterparts:

The four primary types are INTEGER, REAL, TEXT, and BLOB; TEXT and BLOB are almost the same types. One is for raw data and the other is for string data, but we can interpret both of those in Haskell as String. INTEGER corresponds to Integer; REAL corresponds to Decimal. There is a fifth type in SQLite3 called NUMERIC, which is adaptive and is treated as an integer with INTEGER data and a real with REAL data. For those instances in which you have a column with both, I recommend that you don't use NUMERIC at all and you just use that REAL column heading. Now, we're going to go back to our virtual environment for running our Haskell notebooks. I have my Terminal open to the /Code/HaskellDataAnalysis directory, and I have two folders under my HaskellDataAnalysis folder: analysis and data. What we would do now is to move our database, which we created in the last section, into the data folder. So, let's copy, from Downloads, the usgs.sqlite3 file into our data folder:

What we need to do now is to install the necessary software to get Haskell and SQLite3 to communicate with each other. We need a few drivers. So, let's run the following command:

 sudo apt-get install libghc-hdbc-sqlite3-dev

We're going to be needing it for our next task. We also need a few libraries from Cabal, so let's get them as well:

cabal install HDBC and HDBC_sqlite3. 

It will take some time to install, and it's going to download and install those libraries into our home folder. We're ready. So, let's start our Jupyter Notebook by typing jupyter notebook in our Terminal.

In the browser, inside our analysis folder, we are going to create a new Haskell notebook, and this will be renamed Earthquakes. Let's go ahead and import our database tools. Here are the following tools that we will need:

Alright, with that, we should be able to connect to our SQLite3 database. Now hopefully, you have put your data in your SQLite3 database into the data folder, as we did before turning on our notebook. Let's create a connection to our database. So, the command for creating a connection to the database is as follows:

So, we have passed in the path of our database file as a string. If you didn't get an error message, that means the connection was made and you should be ready to go. Let's issue our first query, to see if we get back any data. This will be a similar query to what we did in the last section, except we'll be doing it from the inside of Haskell:

So, we have done records, and the command for a quick query is quickQuery. We then passed in the first argument as the database connection, db, and then we have passed in our SELECT statement. So, we're going to select everything from earthquakes, and we're going to limit that response to five records. We then pass in an empty list, so adding square brackets at the end is a way of passing in arguments into the SELECT query. We have got a really simple query, so we are not using it, but we still need to have that empty list there. So, that's the purpose of the empty list at the end of the quickQuery call. Now, let's take a look at what is returned by records:

You can see that we have a list of records here. Let's make sure that we get 5, because that's how many we specified in the SQL query:

There you go, we got 5 records.

Now, similar to what we did in our first section, we're going to create a quick function using the function fromSql in order to parse data from a column into a more familiar Double type. So, let's create our quick function:

We have named it readColumn, and also, what we have done is call map fromSql, and we're going to produce this partial function called readColumn. Now, there is another function in Data.List called transpose, and it allows us to perform essentially a matrix transpose on a two-dimensional data type, which records is. This allows us to pull a record as an entire row from the dataset. So, the fourth column in our dataset is the depth, and if we do a transpose, we can say Give me the third row, because all indices in Haskell are zero-based. Here is the command:

So, we have done a transpose on the fourth column, so the index is 3, and we're going to read it as Double type. We now have the depths of the five earthquakes in this particular dataset.

In the next section, we're going to examine the essential features of the SELECT query that make it so useful. If you're already a whiz at SQL, you should be able to safely skip the next section. If SQL is unfamiliar to you, then sit tight. You're going to be learning about how to slice up data with SQL in the Haskell environment.