Part 1: From JSON to SQL 

AuctionBase Project: Database and the Web Part 1: From JSON to SQL 
Overview
Weprovideyouwithafairlylargevolumeofdatadownloaded(overadecadeago!) fromtheeBaywebsiteandstored in JSON files. JSON1 is a model primarily used to store semi-structured data. It has become increasingly popular in recent years, and, for now, you can treat it as just another type of data that is very straightforward to read. You will examine the data and design a good relational schema for it. You will then write a Python program to transform the data from its JSON form into SQLite’s load file format, conforming to your relational schema. You will createyourschemainaSQLitedatabase,loadyourtransformeddata,andtestitbyrunningsomeSQLqueriesoverit.
Note: All development for the AuctionBase project (including Parts 2 and 3), should be done on the corn.stanford.edu cluster machines, not the myth.stanford.edu cluster machines. The version of SQLite on corn is correct, while the version of SQLite on myth is not, so please use corn, not myth!
1If you’re interested in learning more about JSON, feel free to watch the JSON lecture videos on OpenEdX.
1
TaskA:ExaminetheJSONdatafiles
We are providing a JSON-encoded auction data set for you to use in your project. The data files are located in the directory
/usr/class/cs145/project/ebay_data/ 1) As a small data set for initial experimentation and debugging. we suggest you use just one file:
/usr/class/cs145/project/ebay_data/items-0.json It contains 500 auctions, comprising about 900KB of plain-text data. 2) Your AuctionBase system also must work on the full data set, which consists of all 40 files:
/usr/class/cs145/project/ebay_data/items-n.json for n = 0...39. There are a total of about 20,000 auctions, comprising about 38MB of plain-text data. Your first task is to examine the schema and the JSON files to completely understand the data you will be starting with. You will translate this data into relations and load it into your AuctionBase database. Please read the auction data JSON schema file in /usr/class/cs145/project/ebay_data/items_schema.txt to familiarize yourself with the schema of the data set. Oneofthemostimportantthingstounderstandaboutthedatayou’restartingwithisthatitrepresentsasinglepointin time. (Specifically, it represents the following point in time: December 20th, 2001, 00:00:01.) It contains items that have been auctioned off in the past and items that are currently up for auction. Once your AuctionBase database is online, you will provide the capability to enter bids on items, to move forward in time so that auctions close. As you develop your AuctionBase system, we will ask you to impose certain constraints and restrictions on the data set. For now, however, you won’t need to worry about this; for now, you only need to worry about designing your relational schema for your database.
TaskB:Designyourrelationalschema
Consider the mega-relation that would be formed by simply adding all of the auction data to a single relation, where every attribute in the relation corresponded to a field in the JSON files. Clearly, this would not be a good schema design. You will need to come up with a better schema design. Create a file design.pdf and document the following: • Provide your relational schema definitions in text form, including the attributes for each relation. Make sure to clearly indicate your chosen keys (including primary and foreign). In addition, all column names in your schemata should come from the list below. There might be more terms than you need and that is completely okay. Just make sure you are not using any terms aside from those given. Note that punctuation matters! – item id, name, currently, buy price, first bid, number of bids, bidder, bidder id, user id, rating, location, country, time, amount, started, ends, seller id, description, category id, category, category name • An Entity-Relation (ER) diagram that describes your schema. Our advice is to use the ER diagram as a starting point to help determine your schema, rather than the other way around. Make sure to include the actual ER diagram in your design.pdf file. While designing your relational schema, you may realize that two fields from the JSON files are technically not necessary to have in your schema for it to represent the same information: Currently and Number of Bids. For example, the Number of Bids can be obtained by simply running a query to calculate the number of bids on a particular item. However, for the purposes of this project, we ask that you have both of these attributes somewhere in your schema. For websites with large databases and many users, running a query to calculate an aggregation every time it needs to be viewed can be costly, so storing the aggregated result itself can help improve performance.
2
TaskC:Writeadatatransformationprogram
First, make sure you are familiar with bulk-loading data into SQLite databases support document (posted on the class website, along with this pdf), which explains, in great detail, how to appropriately bulk-load data into your database. Your task is to write a program that transforms the JSON data into SQLite load files that are consistent with the relationalschemayoudesignedinTaskB.Tostartyouoff,weareprovidinga“skeleton”implementationoftheparser in Python – you need to implement the parseJSON method in the starter code, which must parse and extract each JSON file and then output the appropriate SQLite bulk-loading files according to the relational schema you designed in Task B. You can find more detailed information in the comments of the starter code:
/usr/class/cs145/project/pa1/skeleton_parser.py
You should start by copying this file to a local directory using the cp command; we also encourage you to rename the file to something other than skeleton parser.py to differentiate between the starter code and your own implementation. The following command will call the skeleton parser on the small data set:
python skeleton_parser.py /usr/class/cs145/project/ebay_data/items-0.json
To parse the full data set, we simply use the commmand above, but change items-0.json to items-*.json:
python skeleton_parser.py /usr/class/cs145/project/ebay_data/items-*.json
We strongly suggest that you fully debug your program on the small data set before using it with the full data set. Please note that some strings in the auction data contain characters commonly used as delimiters. We’ve cleaned the data to remove all instances of the ‘|’ character, so feel free to use it as indicated in the bulk-loading document (again, posted on the class website, along with this pdf). For grading purposes, please use the .dat extension for the SQLite load files you generate.
Dollaranddate/timevalues
Dollarvalueamountsintheauctiondataareexpressedas $X,XXX.XX.Tosimplifycomparisonsbetweendollarvalues in your database, we have provided a function transformDollar(string) in skeleton_parser.py, in order to reformat these strings into values that SQLite can interpret as floating point numbers. Similarly, date/time values in the auction data are expressed like Mar-25-01 10:25:57. SQLite has support for date/time strings, but only when expressed in the ISO format, e.g. 2001-03-25 10:25:57. We have provided a function transformDttm(string) in skeleton_parser.py in order to do this conversion for you.
Duplicateelimination
WhentransformingtheJSONdatatorelationaltuples,youmaydiscoverthatyougeneratecertaintuplesmultipletimes but only want to retain one copy. You can code duplicate-elimination as part of your Python parser. Alternatively, you may also use various Unix command-line programs (e.g., sort and uniq) directly on the generated load files to eliminate the duplicate tuples.
Runningtime
On the full data set, your parser should take at most a couple of minutes to run. If it takes much longer than that, you areprobablydoingsomethingunnaturalandhighlyinefficient–pleasetrytoreducetherunningtimebeforesubmitting your parser; see the course staff if you need assistance.
3
Automatingtheprocess
Create a file called runParser.sh that consists of the command that invokes your parser over the full data set (i.e. python my_parser.py /usr/class/cs145/project/ebay_data/items-*.json. If there are any other steps involved in your data translation process (such as invoking any Unix commands), also include those steps in the runParser.sh file. Your runParser.sh must operate on the full data set directly from the class directory, not over your own local copy. When we grade your work up to this point, we will only run sh runParser.sh.
TaskD:LoadyourdataintoSQLite
The next step is to create and populate your AuctionBase database. SQLite provides a facility for reading a set of commandsfromafile. Youshouldusethisfacilityfor(re)buildingyourdatabaseandrunningsamplequeries,andyou must use it extensively in your submitted work. Create a command file called create.sql that includes the SQL commands that create all of the necessary tables according to your schema design from Task B. Before creating the tables, you should also ensure that any old tables ofthesamenamehavebeendeleted. (Thismakesiteasiertotestyoursubmission.) Thisfilewilllooksomethinglike:
drop table if exists Item; drop table if exists AuctionUser; ... create table Item ( .... ); create table AuctionUser ( ... ); ...
Then, createacommandfilecalled load.txt thatloadsyourdataintoyourtables. Thisfilewilllooksomethinglike:
.separator | .import items.dat Items update Items set ... -- Replace all token ‘NULL’ values with null .import auctionuser.dat AuctionUser ...
If you are unsure what these commands do, refer to the bulk-loading document (again, posted on the class website, along with this pdf). Each one of your files should run properly when taken as input into the sqlite command, for example:
sqlite3 <db_name < create.sql
or
/usr/class/cs145/bin/sqlite3 <db_name < create.sql
TaskE:TestyourSQLitedatabase
The final step is to take your newly loaded database for a test drive by running a few SQL queries over it. As with database creation, first test your queries interactively using the SQLite command-line client, then set up a command file to run them in batch mode. First, try some simple queries over one relation, then more complex queries involving joins and aggregation. Make sure the results look correct. When you are confident that everything is correct, write SQL queries for the following specific tasks:
4
1. Find the number of users in the database. 2. Find the number of users from New York (i.e., users whose location is the string “New York”). 3. Find the number of auctions belonging to exactly four categories. 4. Find the ID(s) of auction(s) with the highest current price. 5. Find the number of sellers whose rating is higher than 1000. 6. Find the number of users who are both sellers and bidders. 7. Find the number of categories that include at least one item with a bid of more than $100. Your answers to the above seven queries over the large data set should be (in order): 13422, 80, 8365, 1046871451, 3130, 6717, 150. (Hint: If your second query result is incorrect, it might be because you set the location of a seller incorrectly in your database. The location of a seller is the location of his or her item. If your third query result is wrong, it is probably because you did not eliminate duplicates correctly in your database.) Your queries should be fairly efficient – they should each take at most ten seconds to execute, most of them under a second. If any of your queries are taking much longer than that, you’ve probably written them in an unnatural way; please try rewriting them, and see the course staff if you need any help. Put each of the seven queries in its own command file: query1.sql, query2.sql, ..., query7.sql. Make sure that the naming of your files corresponds to the ordering above, as we will be checking correctness using an automated script.
Submissioninstructions
To submit Part 1 of the project, first gather the following files in a single submission directory:
design.pdf {your_parser_name}.py runParser.sh create.sql load.txt query1.sql query2.sql query3.sql query4.sql query5.sql query6.sql query7.sql
DoNOTincludeany.dator.jsonfilesinyoursubmission! We reserve the right to deduct points from your project grade if you include them. Once your submission directory is properly assembled, with no extraneous files, execute the following script from from your submission directory:
/usr/class/cs145/bin/submit-project
Be sure to select “Part1” when the script prompts you for which assignment you’re submitting!