CSE3BDC Big Data Management On The Cloud Assignment: Analysing Bank Data and Twitter Time Series Data
Department of Computer Science and Computer Engineering CSE3BDC Assignment 2024
Objectives
-
Gain in depth experience playing around with big data tools (Hive, SparkRDDs, and Spark SQL).
-
Solve challenging big data processing tasks by finding highly efficient solutions.
-
Experience processing three different types of real data:
a. Standardmulti-attributedata(Bankdata).
-
Time series data (Twitter feed data).
-
Bag of words data.
-
-
Practice using programming APIs to find the best API calls to solve your problem. Here are the API descriptions for Hive, Spark (especially spark look under RDD. There are a lot of really useful API calls).
-
a) [Hive] https://cwiki.apache.org/confluence/display/Hive/LanguageManual
-
b) [Spark] http://spark.apache.org/docs/latest/api/scala/index.html#package
-
c) [Spark SQL] https://spark.apache.org/docs/latest/sql-programming-guide.html
https://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.Datase t
https://spark.apache.org/docs/latest/api/sql/index.html
- If you are not sure what a spark API call does, try to write a small example and try it in the spark shell.
-
This assignment is due 11:59 pm on Friday 24th of May, 2024.
Penalties are applied to late assignments (accepted up to 5 business days after the due date only). Five precent is deducted per business day late. A mark of zero will be assigned to assignments submitted more than 5 days late.
This is an individual assignment. You are not permitted to work as a part of a group when writing this assignment.
Submission checklist
-
Ensure that all of your solutions read their input from the full data files (not the small example versions).
-
Check that all of your solutions run without crashing in the docker containers that you used in the labs.
-
Delete all output files.
-
Archive up everything into a single zip file and submit your assignment via LMS.
Copying, Plagiarism
Plagiarism is the submission of somebody else’s work in a manner that gives the impression that the work is your own. For individual assignments, plagiarism includes the case where two or more students work collaboratively on the assignment. The Department of Computer Science and Computer Engineering treats plagiarism very seriously. When it is detected, penalties are strictly imposed.
If you are working on your assignment on the lab computers, make sure you delete the virtual machine and empty the recycle bin before you leave. Otherwise, other students may be able to see your solutions.
ChatGPT and similar AI tools
Expected quality of solutions
-
a) In general, writing more efficient code (less reading/writing from/into HDFS and less data shuffles) will be rewarded with more marks.
-
b) This entire assignment can be done using the docker containers supplied in the labs and the supplied data sets without running out of memory. It is time to show your skills!
-
c) I am not too fussed about the layout of the output. As long as it looks similar to the example outputs for each task. That will be good enough. The idea is not to spend too much time massaging the output to be the right format but instead to spend the time to solve problems.
-
d) For Hive queries, we prefer answers that use less tables.
The questions in the assignment will be labelled using the following: • [Hive]
o Means this question needs to be done using Hive. • [Spark RDD]
o Means this question needs to be done using Spark RDDs, you are not allowed to use any Spark SQL features like dataframe or datasets.
• [Spark SQL]
o Means this question needs to be done using Spark SQL and therefore you are
not allowed to use RDDs. In addition, you need to do these questions using the spark dataframe or dataset API, do not use SQL syntax.
A key purpose of this assessment task is to test your own ability to complete the assigned
tasks. Therefore, the use of ChatGPT, AI tools or chatbots with similar functionality is
prohibited for this assessment task. Students who are found to be in breach of this rule will
be subject to normal academic misconduct measures. Additionally, students may be engaged
to provide an oral validation of their understanding of their submitted work (e.g. coding).
Assignment structure:
• A script which puts all of the data files into HDFS automatically is provided for you. Whenever you start the docker container again you will need to run the following script to upload the data to HDFS again, since HDFS state is not maintained across docker runs:
$ bash put_data_in_hdfs.sh
The script will output the names of all of the data files it copies into HDFS. If you do not run this script, solutions to the Spark questions will not work since they load data from HDFS.
To put the files onto HDFS do the following:
-
First start the docker container using run.sh like you have done for your labs.
-
Change to the directory that contains the file put_data_in_hdfs.sh file and then run the following command:
bash put_data_in_hdfs.sh -
The above will put all the assignment files into HDFS. You can now look at the HDFS contents in Hue like the following. Open Firefox browser and type in the following URL: localhost:8888
4. Typeinusername:rootandpassword:root
5. Next select the files icon on the left to see the files you have
uploaded to HDFS.
-
For each Hive question a skeleton .hql file is provided for you to write your solution in. You can run these just like you did in labs:
$ hive -f Task_XX.hql -
For each Spark question, a skeleton project is provided for you. Write your solution in the .scala file in the src directory. Build and run your Spark code using the provided scripts:
$ bash build_and_run.sh
Follow the instructions below to run a small test program that outputs to HDFS so you can see the output.
1. ChangetotheTask_testdirectoryandtypethefollowing command:
bash build_and_run.sh
2. NextlookattheoutputoftheprograminHue:
Tips:
-
Look at the data files before you begin each task. Try to understand what you are dealing with!
-
For each subtask we provide small example input and the corresponding output in the assignment specifications below. These small versions of the files are also supplied with the assignment (they have “-small” in the name). It’s a good idea to get your solution working on the small inputs first before moving on to the full files.
-
In addition to testing the correctness of your code using the very small example input. You should also use the large input files that we provide to test the scalability of your solutions.
-
It can take some time to build and run Spark applications from .scala files. So for the Spark questions it’s best to experiment using spark-shell first to figure out a working solution, and then put your code into the .scala files afterwards. As an example you can try to copy the following highlighted lines from the Task_test source file into the spark shell.
Task 1: Analysing Bank Data [38 marks total]
We will be doing some analytics on real data from a Portuguese banking institution1. The data is stored in a semicolon (“;”) delimited format.
The data is supplied with the assignment at the following locations:
Small version
Task_1/Data/bank-small.csv
Full version
Task_1/Data/bank.csv
The data has the following
attributes: Description numeric
Attribute
index
0
2
3 4 5 6 7 8
9 10
11 12
14 15 16
Attribute name age
marital
education default balance housing loan contact
day month
duration campaign
previous poutcome termdeposit
marital status (categorical: "married", "divorced", "single"; note:
"divorced" means divorced or widowed)
(categorical: "unknown", "secondary", "primary", "tertiary")
has credit in default? (binary: "yes", "no")
average yearly balance, in euros (numeric)
has housing loan? (binary: "yes", "no")
has personal loan? (binary: "yes", "no")
contact communication type (categorical: “unknown", "telephone",
"cellular")
last contact day of the month (numeric)
last contact month of year (categorical: "jan", "feb", "mar", ...,
"nov", "dec")
last contact duration, in seconds (numeric)
number of contacts performed during this campaign and for this
client (numeric, includes last contact)
number of contacts performed before this campaign and for this
client (numeric)
outcome of the previous marketing campaign (categorical:
"unknown","other","failure","success")
has the client subscribed a term deposit? (binary: "yes","no")
|
1 |
job |
type of job (categorical: "admin.", "unknown", "unemployed", "management", "housemaid", "entrepreneur", "student", “blue-collar", "self-employed", "retired", "technician", "services") |
|
13 |
pdays |
number of days that passed by after the client was last contacted from a previous campaign (numeric, -1 means client was not previously contacted) |
1
: Banking data source: http://archive.ics.uci.edu/ml/datasets/Bank+Marketing
Here is a small example of the bank data that we will use to illustrate the subtasks below (we only list a subset of the attributes in this example, see the above table for the description of the attributes):
|
job |
marital |
education |
balance |
loan |
management technician entrepreneur blue-collar services technician Management technician
Married Divorced Single Married Divorced Married Divorced Married
tertiary 2143 Yes secondary 29 Yes secondary 2 No unknown 1506 No secondary 829 Yes tertiary 929 Yes tertiary 22 No primary 10 No
Please note we specify whether you should use [Hive] or [Spark RDD] for each subtask at the beginning of each subtask.
a) [Hive] Report the number of clients of each job category. Write the results to “Task_1a-out”. For the above small example data set you would report the following (output order is not important for this question):
"blue-collar" 1 "entrepreneur" 1 "management" 2 "services" 1 "technician" 3
[8 marks]
b) [Hive] Report the average yearly balance for all people in each education category. Write the results to “Task_1b-out”. For the small example data set you would report the following (output order is not important for this question):
"primary" 10.0
"secondary" 286.6666666666667
"tertiary" 1031.3333333333333
"unknown" 1506.0
[Spark RDD] Group balance into the following three categories: a. Low:-infinityto500
-
Medium: 501 to 1500 =>
-
High: 1501 to +infinity
[8 marks]
c)
Report the number of people in each of the above categories. Write the results to “Task_1c-out” in text file format. For the small example data set you should get the following results (output order is not important in this question):
(High,2) (Medium,2) (Low,4)
[10 marks]
d) [Spark RDD] Sort all people in ascending order of education. For people with the same education, sort them in descending order by balance. This means that all people with the same education, should appear grouped together in the output. For each person, report the following attribute values: education, balance, job, marital, loan. Write the results to “Task_1d-out” in text file format (multiple parts are allowed). For the small example data set, you would report the following:
("primary",10,"technician","married","no") ("secondary",829,"services","divorced","yes") ("secondary",29,"technician","divorced","yes") ("secondary",2,"entrepreneur","single","no") ("tertiary",2143,"management","married","yes") ("tertiary",929,"technician","married","yes") ("tertiary",22,"management","divorced","no") ("unknown",1506,"blue-collar","married","no")
[12 marks]
Task 2: Analysing Twitter Time Series Data [32 marks]
In this task we will be doing some analytics on real Twitter data2. The data is stored in a tab (“\t”) delimited format.
The data is supplied with the assignment at the following locations:
Small version Full version
Task_2/Data/twitter-small.tsv Task_2/Data/twitter.tsv
The data has the following attributes:
Attribute Attribute name
index
0 tokenType
-
2 count
-
3 hashtagName
Description
In our data set all rows have Token type of hashtag. So this attribute is useless for this assignment.
An integer representing the number tweets of this hash
tag for the given year and month
The #tag name, e.g. babylove, mydate, etc.
|
1 |
month |
The year and month specified like the following: YYYYMM. So 4 digits for year followed by 2 digits for month. So like the following 200905, meaning the year 2009 and month of May |
Here is a small example of the Twitter data that we will use to illustrate the subtasks below:
Token type hashtag hashtag hashtag hashtag hashtag hashtag hashtag hashtag hashtag
Month count 200910 2 200911 2 200912 90 200812 100 200901 201 200910 1 200912 500 200905 23 200907 1000
Hash Tag Name babylove babylove babylove mycoolwife mycoolwife mycoolwife mycoolwife
abc abc
2
: Twitter data source: http://www.infochimps.com/datasets/twitter-census-conversation-metrics- one-year-of-urls-hashtags-sm
a)
b)
[Spark RDD] Find the single row that has the highest count and for that row report the month, count and hashtag name. Print the result to the terminal output using println. So, for the above small example data set the result would be:
month: 200907, count: 1000, hashtagName: abc
[6 marks]
[Do twice, once using Hive and once using Spark RDD] Find the hash tag name that was tweeted the most in the entire data set across all months. Report the total number of tweets for that hash tag name. You can either print the result to the terminal or output the result to a text file. So, for the above small example data set the output would be:
abc 1023
[12 marks total: 6 marks for Hive and 6 marks for Spark RDD]
[Spark RDD] Given two months x and y, where y > x, find the hashtag name that has increased the number of tweets the most from month x to month y. Ignore the tweets in the months between x and y, so just compare the number of tweets at month x and at month y. Report the hashtag name, the number of tweets in months x and y. Ignore any hashtag names that had no tweets in either month x or y. You can assume that the combination of hashtag and month is unique. Therefore, the same hashtag and month combination cannot occur more than once. Print the result to the terminal output using println. For the above small example data set:
Input x = 200910, y = 200912
Output hashtagName: mycoolwife, countX: 1, countY: 500
For this subtask you can specify the months x and y as arguments to the script. This is
c)
required to test on the full-sized data. For example:
$ bash build_and_run.sh 200901 200902
[14 marks]
Task 3: Indexing Bag of Words data [30 marks]
In this task you are asked to create a partitioned index of words to documents that contain the words. Using this index you can search for all the documents that contain a particular word efficiently.
The data is supplied with the assignment at the following locations3:
Small version
Task_3/Data/docword-small.txt
Task_3/Data/vocab-small.txt
Full version
Task_3/Data/docword.txt
Task_3/Data/vocab.txt
The first file is called docword.txt, which contains the contents of all the documents stored in the following format:
Attribute Attribute name
index
0 docId
1 vocabId
Description
The ID of the document that contains the word
Instead of storing the word itself, we store an ID from the
vocabulary file.
An integer representing the number of times this word
occurred in this document.
2 count
The second file called vocab.txt contains each word in the vocabulary, which is indexed by
vocabIndex from the docword.txt file.
Here is a small example content of the docword.txt file.
docId vocabId count
3 3 600 2 3 702 1 2 120 2 5 200 2 2 500 3 1 100 3 5 2000 3 4 122 1 3 1200 1 1 1000
3 : Data source: http://archive.ics.uci.edu/ml/datasets/Bag+of+Words
Here is an example of the vocab.txt file
vocabId word
-
1 plane
-
2 car
-
3 motorbike
-
4 truck
-
5 boat
Complete the following subtasks using Spark:
-
a) [spark SQL] Calculate the total count of each word across all documents. List the
words in ascending alphabetical order. Write the results to “Task_3a-out” in CSV format (multiple output parts are allowed). So for the above small example input the output would be the following (outputs with multiple parts will be considered in order of the part number):
boat,2200 car,620 motorbike,2502 plane,1100 truck,122
Note: spark SQL will give the output in multiple files. You should ensure that the data is sorted globally across all the files (parts). So, all words in part 0, will be alphabetically before the words in part 1.
[8 marks]
-
b) [spark SQL] Create a dataframe containing rows with four fields: (word, docId, count, firstLetter). You should add the firstLetter column by using a UDF, which extracts the first letter of word as a String. Save the results in parquet format partitioned by firstLetter to docwordIndexFilename. Use show() to print the first 10 rows of the dataframe that you saved.
So, for the above example input, you should see the following output (the exact
ordering is not important):
+---------+-----+-----+-----------+
| word|docId|count|firstLetter| +---------+-----+-----+-----------+ | plane| 1| 1000| p| | plane| 3| 100| p| |car|2|500| c| |car|1|120| c| |motorbike| 1| 1200| m| |motorbike| 2| 702| m| |motorbike| 3| 600| m| | truck| 3| 122| t| | boat| 3| 2000| b| | boat| 2| 200| b| +---------+-----+-----+-----------+
[14 marks]
c) [spark SQL] Load the previously created dataframe stored in parquet format from subtask b). For each document ID in the docIds list (which is provided as a function argument for you), use println to display the following: the document ID, the word with the most occurrences in that document (you can break ties arbitrarily), and the number of occurrences of that word in the document. Skip any document IDs that aren’t found in the dataset. Use an optimisation to prevent loading the parquet file into memory multiple times.
If docIds contains “2” and “3”, then the output for the example dataset would be: [2, motorbike, 702]
[3, boat, 2000]
For this subtask specify the document ids as arguments to the script. For example:
$ bash build_and_run.sh 2 3
[4 marks] d) [spark SQL] Load the previously created dataframe stored in parquet format from subtask b). For each word in the queryWords list (which is provided as a function
argument for you), use println to display the docId with the most occurrences of that word (you can break ties arbitrarily). Use an optimisation based on how the data is partitioned.
If queryWords contains “car” and “truck”, then the output for the example dataset would be:
[car,2] [truck,3]
For this subtask specify the query words as arguments to the script. For example:
$ bash build_and_run.sh computer environment power
Bonus Marks:
1. Using spark perform the following task using the data set of task 2.
[4 marks]
[Spark RDD or Spark SQL] Find the hash tag name that has increased the number of tweets the most from among any two consecutive months of any hash tag name. Consecutive month means for example, 200801 to 200802, or 200902 to 200903, etc. Report the hash tag name, the 1st month count, and the 2nd month count using println.
For the small example data set of task 2 the output would be:
Hash tag name: mycoolwife count of month 200812: 100 count of month 200901: 201
Total Marks:
Please note that the total mark for this assignment is capped at 100. If your marks add to
more than 100 then your final mark will be 100.
Return of Assignments
Departmental Policy requires that assignments be returned within three weeks of the submission date. We will endeavour to have your assignment returned before the BDC exam. The time and place of the return will be posted on LMS.
[10 marks]
