TDM 40100: Project 2 — 2023

The goodreads dataset contains a variety of files. With that being said there are 4 files which hold the bulk of the data. The rest is mostly derivatives of those 4 files.

Write a bash function that takes an argument that indicates the size unit to return in, and returns the size of a file in that unit by running du. For example, if I wanted to know how many bytes the goodreads_books.json file takes up using your function, I could run the following:

Run your function 4 times, passing in bytes, kilobytes, megabytes, and gigabytes in turn. Does your output make sense for each size? If not, double check your function to make sure you are using the correct unit of size for each du command. Don’t forget to try du --help if you are having trouble.

Approximately how many books, reviews, and authors are included in the datasets?

For this part of the question, I would recommend first looking at the head of each file using head -n10 filename, replacing filename with your file. How many books are on each line of the file on average? Once you know this, you can count how many lines are in the file (using wc) and extrapolate to find your solution.

Finally, let’s take a look at the first book.

If we want to find the size of the image for this book (given by the image_url field), we could just download it and run du. However, if we wanted to do this on a whole bunch of images it would be extremely slow and take up an enormous amount of space on our filesystem, which is extremely unideal for large datasets.

Instead, use curl in order to get the headers of the image, and grep to isolate the Content-Length portion of the header. Take the time to figure out how this works and make your solution clean, as we will be building on this idea in the next question and it will be tough if you don’t understand it or have a very convoluted solution.

In the last question, we got the size of a single image from our data. However, more often than not we will want to do this with many images in order to approximate how much space the images take on average, or to get any other sorts of summary statistics about the images in our data.

Write a function that takes an argument n that indicates the number of images to download, and returns the average size of n random images from our data. An example general outline of this function is included below to push you in the right direction.

Your function can be tested as follows:

Run your function using the testing code provided above, which returns the average size of images given a subset of 25, 50, 100, and 1000 images. In a markdown cell, write 2-3 sentences explaining which average you received is theoretically closest to the 'true average' size of an image in the dataset, and why.

Okay, so roughly, in total we are looking at around 27 gb of data. With that size it will definitely be useful for us to create a database. After all, answering questions like:

would be quite difficult with the data in its current form.

Realistically, things are not very straightforward if we hand you this data and say "get that info please". However, if we had a nice sqlite database the same tasks would be trivial! In the rest of this project, we will set up a sqlite database, and populate it with the data from the goodreads dataset with the end goal of creating a small database that make it easy to answer questions like the ones above.

First, before we do that, it would make sense to get a sample of each of the datasets. Working with samples just makes it a lot easier to load the data up and parse through it.

Use shuf to get a random sample of the goodreads_books.json and goodreads_reviews_dedup.json datasets. Approximate how many rows you’d need in order to get the datasets down to around 100 mb each, and do so. Put the samples, and copies of goodreads_book_authors.json and goodreads_book_series.json in a directory called goodreads_samples anywhere inside your $HOME directory.

Check out the 5 storage classes (which you can think of as 'data types' in languages like Python, C, and R) that sqlite3 uses: www.sqlite.org/datatype3.html

When we are looking into constructing a database, we need to think about what types of data we need to be storing. For example, if we wanted to store a bunch of values of a structure like 12349234, an INTEGER would likely work well. However, if we are trying to store values like 0012349234, storing as an INTEGER will lose us our 2 leading zeroes. In this case, TEXT may be more appropriate. These sort of small technicalities can make a big difference in how well our data is stored in our database, so be sure to look at the different values in each field before assigning them a type appropriate to store those values.

In a markdown cell, write out each of the keys in each of the json files (excluding goodreads_reviews_dedup.json), and list the appropriate storage class to use. For example, I’ve provided an example solution for goodreads_reviews_dedup.json.

We have done a lot of setup in the previous questions, and now we are finally ready to create our mini-database! We will do this using CREATE TABLE statements in sqlite3, while sourcing data from the goodreads_sample directory we created in the previous question. While you will have to run your CREATE TABLE statements in a terminal with sqlite3 launched in order for them to work, please paste them into your notebook as well in order to recieve points for this question.

Let’s start by launching sqlite3 like so:

This will put us inside our sqlite3 session, and we can start running our commands, sqlite3-specific dot functions, and SQL queries. While you work on the next portion of this question, feel free to use the following code to validate that your CREATE TABLE statements are working as expected. Additionally, this website has more information about sqlite for you to read up on in your extra time.

For now, we will only worry about creating tables with the columns that we identified in the previous question. This means that we will be leaving out the series, popular_shelves, similar_books, and authors columns in the goodreads_books.json file.

Now that we have our sqlite3 session launched, let’s create our first table. In Question 4, we essentially created an outline of what each table’s columns would contain. Translate each of the lists of keys and storage classes you made into a CREATE TABLE statement, and run it in your bash shell. Don’t forget to paste your statements into your Jupyter notebook so we can see them! As an example, I have provided the CREATE TABLE statement for the goodreads_reviews_dedup.json file below. You should still run this statement as you want to create this table as well.

Finally, run all of the below statements in your Jupyter notebook to verify (and show us) that your tables were created correctly.

Well done, you’ve finished your second project for this class and created your first database in sqlite3! Make sure to save your work and submit it to Gradescope in the correct format.

Here is the Zoom recording of the 4:30 PM discussion with students from 28 August 2023: