|
|
CISC 7512X (DB2) HomeworksYou should EMAIL me homeworks, alex at theparticle dot com. Start email subject with "CISC 7512X HW#". Homeworks without the subject line risk being deleted and not counted.CISC 7512X HW# 1 (due by 2nd class;): Email me your name, prefered email address, IM account (if any), major, and year. (oh, and install PostgreSQL). CISC 7512X HW# 2 (due by 3rd class;): For the below `bank' schema:
CISC 7512X HW# 3 (due by Nth class): Your buddy stops over for lunch and tells you about this wonderful idea of building apps for phones (for profit!). The gist of the idea: ride sharing! (``Urgh, not again!'', you think). Unlike other ride-sharing ideas, this app is designed for the usual commuter who uses the car to get to work---and is willing to share the ride with someone else to lower their costs. Going out of the way to pickup folks is out of the question (the driver also needs to get to work themselves). Also, the driver prefers the fastest possible route (highways, etc.,) even if it means not picking up someone. Since everyone (including the driver) are benefitting from the ride, the goal is to lower the commute cost for everyone (including driver and passenger [passenger would use their own car if it costs them less]). The business takes a small slice of the money saved (so it's a win-win for everyone involved). Also, folks will be able to pay for the ride in bitcoins. This all seems like crazy talk until your buddy mentions there's a potential $10m investment (from the same folks who seeded Uber), and all they need from you is a working prototype and a write-up of the architecture by next week. Your task: Design and build a database to run this business. What tables would you need? What events would you capture? Etc. Write up what interface and functionality would be needed to interact with the database. Make the investors see that this is a real viable idea that will actually work. Produce a business plan, design document, whitepaper, architecture, prototype, etc., whatever it takes to get that investment. CISC 7512X HW# 4: below is a schema for an HR database:
CISC 7512X HW# 5: Load all files in ctsdata.20140211.tar (link on the left) into Oracle or PostgreSQL (or whichever works for you; postgresql recommended!). The format of these files is: cts(date,symbol,open,high,low,close,volume), splits(date,symbol,post,pre), dividend(date,symbol,dividend). Submit (email) whatever commands/files you used to load the data into whatever database you're using, as well as the raw space usage of the tables in your database. (this was part of previous homework). Part1: After loading the data, using a create-table-as SQL statement, create another table DAILY_PRCNT, with fields: TDATE,SYMBOL,PRCNT which will have the daily percentage gain/loss adjusted for dividends and splits. Do NOT write procedural code (Java, C#, C/C++, etc.) for this homework (all code must be SQL, etc.). HINT: MSFT (Microsoft) on 2004-11-12 closed at 29.97. HINT: splits, MSFT did a 1 to 2 split on 2003-02-18. During a split, each share of a company gets turned into several shares of lower value each. The total value held by investors is not changed. Part2: Using the DAILY_PRCNT table, calculate CTS_OUTLIER table, that will have tdate, symbol, closing price, previous-day closing price, prcnt return on tdate, 20-day moving avearge of return on tdate, 20-day moving standard deviation of return on tdate, and z-value (hint). [Note, you need to do calculation on price change, not percentage... so unless you want to repeat part1 calculations, you should use table daily_prcnt and convert it into a price, since that's adjusted for splits/dividends]. Part3: Identify 10 worst-days. On each day, a certain number of symbols will have z-value below 3. Find 10 worst-days by that count. Submit qeuries for part1 and part2, and submit queries and output for part3. (e.g. "create table DAILY_PRCNT as select ..."). CISC 7512X HW# 6: In the not-so-distant future, flying cars are commonplace---everyone on the planet got one. Yes, there are ~10 billion flying cars all over the globe. Each one logs its coordinates every 10 milliseconds, even when parked. Assume x,y,z coordinates, with z being altitude, and x,y, some cartesian equivalent of GPS. To avoid accidents, regulation states that no car can be next to any other car by more than 10 feet while in the air (z > 0) for longer than 1 second. Cars can go really fast, ~500mph. YOUR TASK: write an algorithm and program to find all violators. Assume input is a HUGE file (10 billion cars logging "VIN,timestamp,x,y,z" every 10 milliseconds all-the-time). Install Apache Hadoop. [hadoop]. Write a Hive query (or a series of queries), or a MapReduce program to find all violators (cars that are next to other cars while in flight). Assume data is in "cars" table in Hive (or "/use/hive/warehouse/cars" file on HDFS). What is the running time of your implementation? If it's O(N^2), can you make it run in O(N log N) time? (note that with this much data, N^2 is not practical, even N log N is a bit long). Using your 1 node Hadoop cluster, estimate the amount of resources this whole task will consume (to apply it on 10 billion cars), and put a dollar amount value (assuming it costs $0.10/hour to rent 1 node (machine); how much will your solution cost per day/month/year?); rationalize your answer. (note that you can't answer "I'll rent 1 node, and let it run until it's done."; You must process data at least as fast as it is being generated by all those billions of cars). Submit whatever you create to solve this problem (source code for map reduce tasks, or hive queries, etc.,). Note, your solution must run (on small dataset) on a 1-node hadoop cluster. You may ``ignore'' the GPS coordinate system, and simply assume those are cartesian x,y,z coordinates. CISC 7512X HW# 7: (this homework is inspired by an interview question I've been asked): In this homework you'll be using this file: This file includes US stock quote data. Each row is a quote. A quote could be from a single venue or a consolidated quote across all venues. Each file is 10 minutes for a subset of stocks. File Specification: The first row from the sample file: Each row contains two parts: Symbols have the form "AAA.BB" where AAA is the ticker and BB is the venue. The body is comprised of a variable number of pipe-delimited key-value fields representing the latest known value for a ticker/venue combination.
The relevant keys are: In general, both venue and consolidated quotes are valid until updated. The consolidated quote represents the highest valid bid (or lowest ask) across all venues. Certain condition codes on venue quotes can indicate that the venue is no longer valid for inclusion in the consolidated quote. Task1: Write ETL code to save the following fields from the venue quotes in Parquet format: The data written should be fully reflective of the state of the market as of each quote—i.e. if the current bid is unspecified in a row on the input because it is unchanged, it nonetheless should appear in the Parquet data. If the current bid is unavailable because it was explicitly nulled (i.e. a |0=| entry in the file) it should appear as a null in the Parquet data. Task2: For each date, ticker and minute from 09:31 through 16:00, calculate the number of venues that are showing the same bid price as the consolidated quote at the end of the minute interval. Include only quotes for the trade date specified in the file name. Submit the Spark program to do Task1 and Task2. CISC 7512X HW# 8 (due by Nth class;): Install HBase. You have a relational database from HW2: You would like to port it to HBase. How would you organize the data to make it easy to answer HW2 questions using HBase? What would you use as keys? Do you need to store multiple copies of the data? Outline pseudo code (please don't write actual java) that would answer the following questions using your design:
|