ISIT312 Big Data Management

Assignment 3

Published on 23 September 2024

Scope

This assignment includes the tasks related to querying a data cube, design and implementation of HBase table, querying and manipulating data in HBase table, data processing with Pig, and data processing with Spark.

This assignment is due on Saturday, 26 October 2024, 7:00pm (sharp). This assignment is worth 20% of the total evaluation in the subject.

The assignment consists of 4 tasks and specification of each task starts from a new page.

Only electronic submission through Moodle at:

https://moodle.uowplatform.edu.au/login/index.php

will be accepted. A submission procedure is explained at the end of Assignment 1 specification. A policy regarding late submissions is included in the subject outline.

Only one submission of Assignment 3 is allowed and only one submission per student is accepted.

A submission marked by Moodle as "late" is always treated as a late submission no matter how many seconds it is late.

A submission that contains an incorrect file attached is treated as a correct submission with all consequences coming from the evaluation of the file attached.

All files left on Moodle in a state "Draft(not submitted)" will not be evaluated.

A submission of compressed files (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) is not allowed. The compressed files will not be evaluated.

An implementation that does not compile well due to one or more syntactical and/or run time errors scores no marks.

Using any sort of Generative Artificial Intelligence (GenAI) for this assignment is NOT allowed !

It is expected that all tasks included within Assignment 3 will be solved individually without any cooperation with the other students.  If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or office hours . Plagiarism will result in a FAIL grade being recorded for the assessment task.

Task 1 (5 marks)

Querying a data cube

Consider the following logical schema implementing a two-dimensional data cube.

The original data cube consists of a fact entity that represents the parts included in the orders, a dimension of orders and a dimension of parts supplied by suppliers.

The  relational  tables   PARTSUPP and  ORDERS implement  the  dimensions  of  parts supplied by suppliers and orders. A relational table LINEITEM implements a fact entity of a data cube.

Download a file task1.zip and unzip the file. You should obtain a folder task1 with    the    following    files:    dbcreate.hql,    dbdrop.hql,    partsupp.tbl, lineitem.tbl, and orders.tbl.

A file orders.tbl contains information about the orders submitted by the customers. A file lineitem.tbl contains information about the parts included in the orders. A file partsupp.tbl contains information about the parts and suppliers of parts included in the orders.

Open Terminal window and use cd command to navigate to a folder that contains the files   dbcreate.hql,    dbdrop.hql,   partsupp.tbl,    lineitem.tbl,    and orders.tbl.

Start Hive Server 2 in the terminal window (remember to start Hadoop and Metastore first). Then start beeline client.

When ready process a script. file dbcreate.hql to create the internal relational tables and to load data into the tables. You can use either beeline or Zeppelin. A script. dbdrop.hql can be used to drop the tables.

(1)  0.5 mark

Implement the following query using GROUP BY clause with CUBE operator.

For the order clerks (O_CLERK) Clerk#000000988, Clerk#000000992, find the total number of ordered parts per customer (O_CUSTKEY), per supplier (L_SUPPKEY), per customer and supplier (O_CUSTKEY, L_SUPPKEY), and the total number of ordered parts.

(2)  0.5 mark

Implement the following query using GROUP BY clause with ROLLUP operator.

For the parts with the keys (L_PARTKEY) 18, 19,20find the largest discount applied (L_DISCOUNT) per part key (L_PARTKEY) and per part key and supplier key (L_PARTKEY, L_SUPPKEY) and the largest discount applied at all.

(3)   1 mark

Implement  the  following  query  using   GROUP BY clause  with   GROUPING SETS operator.

Find the smallest price (L_EXTENDEDPRICE) per order year (O_ORDERDATE), and order clerk (O_CLERK) .

Implement the following SQL queries as SELECT statements using window partitioning technique.

(4)   1 mark

For each part list its key (PS_PARTKEY), all its available quantities (PS_AVAILQTY), the smallest available quantity, and the average available quantity. Consider only the parts with the keys 18, 19 and 20.

(5)   1 mark

For   each   part    list    its   key    (PS_PARTKEY)   and    all its   available    quantities (PS_AVAILQTY) sorted  in  descending order  and a   rank  (position number   in an ascending order) of each quantity. Consider only the parts with the keys 18, 19 and 20. Use an analytic function ROW_NUMBER().

(6)   1 mark

For  each  part  list its  key (PS_PARTKEY), its available quantity, and an average

available quantity (PS_AVAILQTY) of the current quantity and all previous quantities in the ascending order of available quantities. Consider only the parts with the keys 18, 19 and 20.  Use ROWS UNBOUNDED PRECEEDING sub-clause within PARTITION BY clause .

When ready, save your SELECT statements in a file solution1.hql. Then, process a script. file solution1.hql and save the results in a report solution1.txt.

Deliverables

A file solution1.txt that contains a report from processing of SELECT statements implementing the queries listed above.

Task 2 (5 marks)

Design and implementation of HBase table (3 marks)

(1)  Consider a conceptual schema of sample data cube given below . The schema

represents a data cube where applicants submit applications for positions offered by employers .

Design a single HBase table a database that contains information described by a conceptual schema given above.

Create HBase script. solution2-1.hb with HBase  shell  commands that create HBase table and load sample data into the table. Load into the table information about at least two applicants, two positions, two employers and three applications.

When ready use HBase shell to process a script. file solution2-1.hb and to save a report from processing in a file solution2-1.txt.

Querying HBase table (2 marks)

(2)  Consider a conceptual schema given below .  The schema represents a  data  cube where students submit assignments and each submission consists of several files and it is related to one subject.

Download  a  file   task2-2.hb with  HBase   shell  commands.  Process  a   script. task2-2.hb. Processing of the script. creates HBase table  task2-2 and loads some data into it.

Use  HBase  shell  to  implement  the  queries  and  data  manipulations  listed  below . Implementation of each step is worth 0.4 of a mark.

Save the queries and data manipulations in a file  solution2-2.hb. Note that implementation of the queries and data manipulations listed below may need more than one command of HBase shell.

(i)   Find all information included in a column family SUBJECT qualified by code and column family FILES qualified by fnumber1 and fnumber2.

(ii)  Find all information about a subject that has a code  312, list two versions per cell.

(iii) Find all information about a submission of assignment 1 performed by a student

007 in a subject 312, list one version per cell.

(iv) Replace a submission date of assignment 1 performed by a student  007 in a subject 312 with a date 02-APR-2019 and then list a column family SUBMISSION to verify the results.

(v)  Add a column family DEGREE that contains information about titles of degrees enrolled by the students. Assume that a student can enrol only one degree . Then add information about a title of degree enrolled by a student with a number 007. A degree title is up to you. List all information about a student with a number 007.

When ready, start HBase shell and process a script. file  solution2-2.hb with the Hbase shell commands. Save report from processing of the script. in a file solution2- 2.txt.

Deliverables

A file solution2-1.txt with a listing from processing of a script. file solution2- 1.hb.

A file solution2-2.txt with a listing from processing of a script. file solution2- 2.hb.

Task 3 (5 marks)

Data processing with Pig Latin

Consider the following logical schema of one-dimensional data cube.

Download  a  file  task3.zip published  on  Moodle  together  with  a  specification  of Assignment 3 and unzip it. You should obtain a folder task3 with the following files:

bank.tbl, account.tbl, and transaction.tbl. Use a text editor to examine the contents of the files.

Upload the files into HDFS.

Open  Terminal window  and  start  pig command  line  interface  to  Pig.  Use  pig command line interface to implement the following actions. Implementation of each step is worth 1 mark.

(1)  Use    load command   to    load   the    files   bank.tbl,    account.tbl,   and

transaction.tbl from HDFS into a Pig storage. Implement and process the following queries.

(2)  Find the names  (name) of banks (bank-type) that have headquarters located in Japan (hq-country) .

(3)  Find  the  account  numbers  (account-number)  opened  in  any  construction (bank-type) bank.

(4)  Find the names of banks (bank-name), that have no accounts opened in the banks.

(5)  Find  the  total  number  of  accounts  opened  in  each  bank  located  in  Japan  (hq- country) .

When ready, Copy into a clipboard the contents of Terminal window with the data loadings and queries processed above and the messages and results listed in the window and Paste the results from a clipboard into a text file solution3.txt.

Deliverables

A file solution3.txt that contains a listing of data loadings and queries performed above , the messages and results of operations. A file solution3.txt must be created through Copy/Paste of the contents of Terminal window into a file solution3.txt. No screen dumps are allowed and no screen dumps will be evaluated.

Task 4 (5 marks)

Data processing with Spark

Consider the following denormalized logical schema of one-dimensional data cube.

Download a file task4.zip published on Moodle together with a specification  of Assignment 3 and unzip it. You should obtain a folder task4 with the following files:

bank.csv, account.csv, and transaction.csv. Use a text editor to examine the contents of the files.

Upload the files bank.csv, account.csv, and transaction.csv to HDFS.

Open Terminal window and start pyspark command line interface to Spark. Use pyspark command line interface to implement the following actions. Implementation of each action is worth 1 mark.

(1)  Create    the     schemas     for     the    files       bank.csv,     account.csv,     and transaction.csv.

(2)  Create the data frames with the contents of the files bank.csv,  account.csv, and transaction.csv using the schemas created in the previous step.

Count the total number of rows in each frame. and then list the contents of each frame.

(3)  Implement the following query on a data frame. that contains information about the transactions.

Find the total amount of money involved in the deposit transactions per each bank. You can skip  the banks  that had no deposit transactions. Sort the results  in  the ascending order of the total amount of money found.

(4)  Create a temporary view over a data frame. with information about the transactions.

(5)  Implement the following query on a temporary view that contains information about the transactions.

Find the total amount of money involved in the deposit transactions per each bank. You can skip  the banks  that had no deposit transactions. Sort the results  in  the ascending order of the total amount of money found.

When ready, Copy into a clipboard the contents of Terminal window with  the operations processed above and the results listed in the window and Paste the results from a clipboard into a text file solution4.txt.

Deliverables

A file solution4.txt that contains a listing of operations performed above and the results of operations. A file solution4.txt must be created through Copy/Paste of the contents of Terminal window into a file solution4.txt. No screen dumps are allowed and no screen dumps will be evaluated.