{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Assignment 02 - MySQL\n",
"*DBMS for Analytics*\n",
"\n",
"**Due: Wednesday, March 3rd, at midnight**"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"*Notes:*\n",
" - All code should follow the PEP 8 Style Guide for Python\n",
" - Assignment should be submitted using jupyter notebooks\n",
" - File name should follow “{Your Name} – Assignment_XX_Submission”\n",
" - Each Task should be contained in its own cell\n",
" - Each Task should be properly commented\n",
" - Each Task should print out the answer to the Task if appropriate\n",
" - Sample submission can be found on blackboard\n",
" - **Not following these standards may result in lost points**"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"---\n",
"## Assignment Description\n",
"For this assignment we are going to explore how the different structures for Mongo and SQL lead to different optimizations on how we can query data. "
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"from pymongo import MongoClient\n",
"\n",
"client = MongoClient(host='18.219.151.47', #host is the hostname for the database\n",
" port=27017, #port is the port number that mongo is running on\n",
" username='student', #username for the db\n",
" password='emse6992pass', #password for the db\n",
" authSource='emse6992') #Since our user only exists for the emse6992 db, we need to specify this\n",
"\n",
"db = client.emse6992\n",
"stats_coll = db.twitter_statuses"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"import pymysql\n",
"\n",
"conn = pymysql.connect(host='gwumysql-restore.cazdwdlcg6dm.us-east-2.rds.amazonaws.com',\n",
" user='EMSE6992',\n",
" port=3306,\n",
" db='EMSE6992',\n",
" password='MySQLpass')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Task 1\n",
"Identify the set list (no duplicates) of users who have retweeted a statuses made by 'elonmusk' using both MySQL and Mongo. For both MySQL and Mongo ensure that your cell prints out the number of unique users.\n",
"\n",
"Expected Outputs:\n",
" - MySQL: **312** users\n",
" - Mongo: **312** users\n",
"\n",
"_Note: Mongo's distinct command can take a second parameter to filter prior to running **distinct** - `db.<collection_name>.distinct(<distinct_field>, {<filter>})`_"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Mongo: 312 users\n"
]
}
],
"source": [
"# Space for Mongo Implementation\n",
"retw_coll = db.twitter_retweets\n",
"task_one_mongo = retw_coll.distinct(('user.id'), {'retweeted_status.user.screen_name':'elonmusk'})\n",
"print(f'Mongo: {len(task_one_mongo)} users')"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"MySQL: 312 users\n"
]
}
],
"source": [
"# Space for MySQL implementation\n",
"cur = conn.cursor()\n",
"\n",
"task_one_MySQL = cur.execute(\"\"\"\n",
"SELECT distinct user_id FROM retweets \n",
" WHERE retweeted_status in (\n",
" SELECT distinct status_id FROM statuses \n",
" WHERE user_id = (SELECT user_id FROM users WHERE screen_name = 'elonmusk')\n",
" )\n",
"\"\"\")\n",
"\n",
"print(f'MySQL: {task_one_MySQL} users')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Task 2\n",
"Identify the set list (no duplicates) of all users using Mongo and MySQL. For both MySQL and Mongo ensure that your cell prints out the number of unique users.\n",
"\n",
"Expected Outputs:\n",
" - MySQL: **86202** users\n",
" - Mongo: **102812** users\n",
" - *NOTE: your number may differ for Mongo depending on approach and thoroughness*"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"# Space for Mongo Implementation\n",
"favor_coll = db.twitter_favorites\n",
"friend_coll = db.twitter_friends\n",
"lists_coll = db.twitter_lists\n",
"\n",
"favor_num = favor_coll.distinct('user.id')\n",
"friend_num = friend_coll.distinct('id')\n",
"list_num = lists_coll.distinct('user.id')\n",
"retw_num = retw_coll.distinct('user.id')\n",
"stats_num = stats_coll.distinct('user.id')\n",
"\n",
"final_list = favor_num+friend_num+list_num+retw_num+stats_num\n",
"\n",
"def get_unique_numbers(numbers):\n",
"\n",
" list_of_unique_numbers = []\n",
" unique_numbers = set(numbers)\n",
" for number in unique_numbers:\n",
" list_of_unique_numbers.append(number)\n",
"\n",
" return list_of_unique_numbers"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Mongo: 104268 users\n"
]
}
],
"source": [
"task_two_Mongo = get_unique_numbers(final_list)\n",
"print(f'Mongo: {len(task_two_Mongo)} users')"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"MySQL: 86202 users\n"
]
}
],
"source": [
"# Space for MySQL implementation\n",
"\n",
"task_two_mysql = cur.execute(\"Select distinct user_id From users\")\n",
"print(f'MySQL: {task_two_mysql} users')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Task 3\n",
"For each of the previous tasks provide a couple of sentences identifying which database was easier to work with and detailing why that database's structure simplified the task.\n",
"\n",
"**Answer the prompt regarding Task 1:**"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Regarding to task one, I believe Mongo database is easier to work with. Since the prompt on this specific question is asking us to find the list of users who have retweeted a post that was originally from Elon Musk. After exploring the database on Mongo, we discover that there is a ‘twitter_retweets’ table that has all the retweets information. And from this table, there is a group called ‘retweeted_status’ that contains all the information about the original tweet where the user retweeted from. So, by finding the user’s screen name equal to ‘elonmusk’ from the ‘quoted_status’ under the group of ‘retweeted_status’, we will be able to find all the list of users who have retweeted Elon Musk. On the other hand, SQL also has the 'retweet' table but does not contain any information from the original post. We will then inner join with the status and users table to obtain the users information. And finally we need to provide a condition statement to check the final list of unique users. It appears that Mongo will be easier in completing this task. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Answer the prompt regarding Task 2:**"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Regarding to task two, I believe SQL database is easier to work with. Since the prompt is asking us to find all the distinct users from the database and by discovering the SQL database, we know that SQL database has a table called ‘users’ that contains all the users’ information. So, by conducting a single line query on SQL, we are able to find all the distinct/unique value in the table by the primary key ‘user_id’, which it gives the final result as a list of users in the database with no duplicates. However, the Mongo database does not have a table that has all the user’s information. It will require to add up all the users from all the five different databases and to find the unique value from it. Therefore, it appears the SQL will be easier to complete this task. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Task 4\n",
"How would you modify our SQL database to handle hashtags? Please provide information regarding what tables you would modify/create and how these modifications would enable the support for Hashtags.\n",
"\n",
"**Explanation:**"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"First in order to allow to SQL database to handle hashtags, we need to modify the 'statuses' table in SQL. In the 'statuses' table, first we create a column or a field and name it as hashtag. In this column, we capture the hashtags’ value from each single post from the table. Since the hashtags’ value appear in the text column in the 'statuses' table, we store the string value starting behind the symbol “#” and ending before space since the format of hashtag is an unbroken word or phrase. If there is no hashtag from the post, we will have a null value in the hashtag field. If there are more than one hashtag value capture on a single, we can store them in the same cell of the hashtag field separated by commas. \n",
"\n",
"The modification that we have created would be benefic for searching the hashtags and finding the relationship between hashtags and their posts. It is also an efficient way to search since we do not need to create any additional tables that would causing more space for the database to store things. Regarding to the hashtag searching, we can conduct queries include the ‘like’ condition to search the desire the hashtag value that we are looking for. After executing the query, we will have all the posts’ information that contain the hashtag. And further by inner joining the ‘users’ table, we can also find the users’ information regarding the original post that has the hashtag. \n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Task 5\n",
"If twitter decided to add the ability to customize tweets font/colors/etc., how would you implement this in SQL vs Mongo. Which do you think is easier to setup/easier to maintain.\n",
"\n",
"**Explanation:**"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If twitter adds the ability of customizing tweet’s font and color, I would modify the ‘statuses’ table in SQL in order to help supporting this function. In the ‘status’ table, we can create a column or a field and name it as ‘font_style’. In this field, it captures the font style of each single post from the ‘status’ table. And likewise, for the colors we can create another column or field with name ‘color’. And it would be interesting and more meaningful for the perspective of analytics, if twitter can set up rules like different colors represent different mood or status of the users when posting the tweets. So, the ‘color’ field stores the color of each tweets in the ‘statuses’ table. \n",
"\n",
"For Mongo database, I would modify the ‘twitter_statuses’ table and‘twitter_retweets’ table since both tables contain the information of the posts. For the ‘twitter_statuses’ table, I would add two fields with the name of ‘font_style’ and ‘color’ underneath the field ‘text’. For ‘twitter_retweets’ table, I would add these two new fields in the group of ‘retweeted_status’ since the color and font style belong to the original post’s information. I would also add both the fields of 'font_style' and 'color' outside of the 'retweeted_status' under the field 'text'. This is because when someone is retweeting someone else's post, he/she can also comment on the post which will also need to have these two fields to record the value. \n",
"\n",
"I would say the implement on SQL will be a little bit easier than implementing on Mongo database. Since there is only one table in SQL that contains the specific information about each tweets/post, it will be easier to set up and to maintain by only monitoring this table. For Mongo on the other hands, it will require to set up and maintain on more than one table which will cause more time consuming and more human efforts. Also, The way to construct the database between SQL and Mongo varies a lot. Mongo has total of five different tables which has a lot more complex structure than the SQL database does. So when encountering problems like adding new fields to the database, Mongo will be relatively difficult than the SQL database. \n"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.5"
}
},
"nbformat": 4,
"nbformat_minor": 4
}