cs105_lab2_apache_log.ipynb

{"cells":[{"cell_type":"markdown","source":["<a rel=\"license\" href=\"http://creativecommons.org/licenses/by-nc-nd/4.0/\"><img alt=\"Creative Commons License\" style=\"border-width:0\" src=\"https://i.creativecommons.org/l/by-nc-nd/4.0/88x31.png\" /></a><br />This work is licensed under a <a rel=\"license\" href=\"http://creativecommons.org/licenses/by-nc-nd/4.0/\">Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License</a>."],"metadata":{}},{"cell_type":"markdown","source":["#![Spark Logo](http://spark-mooc.github.io/web-assets/images/ta_Spark-logo-small.png) + ![Python Logo](http://spark-mooc.github.io/web-assets/images/python-logo-master-v3-TM-flattened_small.png)\n# **Web Server Log Analysis with Apache Spark**\n\nThis lab will demonstrate how easy it is to perform web server log analysis with Apache Spark.\n\nServer log analysis is an ideal use case for Spark.  It's a very large, common data source and contains a rich set of information.  Spark allows you to store your logs in files on disk cheaply, while still providing a quick and simple way to perform data analysis on them.  This homework will show you how to use Apache Spark on real-world text-based production logs and fully harness the power of that data.  Log data comes from many sources, such as web, file, and compute servers, application logs, user-generated content,  and can be used for monitoring servers, improving business and customer intelligence, building recommendation systems, fraud detection, and much more."],"metadata":{}},{"cell_type":"code","source":["labVersion = 'cs105x-lab2-1.1.0'"],"metadata":{},"outputs":[],"execution_count":3},{"cell_type":"markdown","source":["## How to complete this lab\n\nThis lab is broken up into sections with bite-sized examples for demonstrating Spark functionality for log processing.\n\nIt consists of 5 parts:\n* *Part 1:* Introduction and Imports\n* *Part 2:* Exploratory Data Analysis\n* *Part 3*: Analysis Walk-Through on the Web Server Log File\n* *Part 4*: Analyzing Web Server Log File\n* *Part 5*: Exploring 404 Response Codes\n\nAlso, at the very bottom:\n\n* *Appendix A*: Submitting Your Exercises to the Autograder"],"metadata":{}},{"cell_type":"markdown","source":["## Part 1: Introduction and Imports\n\n### A note about DataFrame column references\n\nIn Python, it's possible to access a DataFrame's columns either by attribute (`df.age`) or by indexing (`df['age']`). Referring to a column by attribute (`df.age`) is very Pandas-like, and it's highly convenient, especially when you're doing interactive data exploration. But it can fail, for reasons that aren't obvious. For example:"],"metadata":{}},{"cell_type":"code","source":["throwaway_df = sqlContext.createDataFrame([('Anthony', 10), ('Julia', 20), ('Fred', 5)], ('name', 'count'))\n# throwaway_df.select(throwaway_df.count).show() # This line does not work. Please comment it out later."],"metadata":{},"outputs":[],"execution_count":6},{"cell_type":"markdown","source":["To understand why that failed, you have to understand how the attribute-column syntax is implemented.\n\nWhen you type `throwaway_df.count`, Python looks for an _existing_ attribute or method called `count` on the `throwaway_df` object. If it finds one, it uses it. Otherwise, it calls a special Python function (`__getattr__`), which defaults to throwing an exception. Spark has overridden `__getattr__` to look for a column on the DataFrame.\n\n**This means you can only use the attribute (dot) syntax to refer to a column if the DataFrame does not _already_ have an attribute with the column's name.**\n\nIn the above example, there's already a `count()` method on the `DataFrame` class, so `throwaway_df.count` does not refer to our \"count\" column; instead, it refers to the `count()` _method_.\n\nTo avoid this problem, you can refer to the column using subscript notation: `throwaway_df['count']`. This syntax will _always_ work."],"metadata":{}},{"cell_type":"code","source":["throwaway_df.select(throwaway_df['count']).show()"],"metadata":{},"outputs":[],"execution_count":8},{"cell_type":"markdown","source":["### (1a) Library Imports\n\n\nWe can import standard Python libraries ([modules](https://docs.python.org/2/tutorial/modules.html)) the usual way.  An `import` statement will import the specified module.  In this lab, we will provide any imports that are necessary.\n\nLet's import some of the libraries we'll need:\n\n* `re`: The regular expression library\n* `datetime`: Date and time functions\n* `Test`: Our Databricks test helper library"],"metadata":{}},{"cell_type":"code","source":["import re\nimport datetime\nfrom databricks_test_helper import Test"],"metadata":{},"outputs":[],"execution_count":10},{"cell_type":"code","source":["# Quick test of the regular expression library\nm = re.search('(?<=abc)def', 'abcdef')\nm.group(0)"],"metadata":{},"outputs":[],"execution_count":11},{"cell_type":"code","source":["# Quick test of the datetime library\nprint 'This was last run on: {0}'.format(datetime.datetime.now())"],"metadata":{},"outputs":[],"execution_count":12},{"cell_type":"markdown","source":["### (1b) Getting help\n\nRemember: There are some useful Python built-ins for getting help."],"metadata":{}},{"cell_type":"markdown","source":["You can use Python's [dir()](https://docs.python.org/2/library/functions.html?highlight=dir#dir) function to get a list of all the attributes (including methods) accessible through the `sqlContext` object."],"metadata":{}},{"cell_type":"code","source":["# List sqlContext's attributes\ndir(sqlContext)"],"metadata":{},"outputs":[],"execution_count":15},{"cell_type":"markdown","source":["Alternatively, you can use Python's [help()](https://docs.python.org/2/library/functions.html?highlight=help#help) function to get an easier to read list of all the attributes, including examples, that the `sqlContext` object has."],"metadata":{}},{"cell_type":"code","source":["# Use help to obtain more detailed information\nhelp(sqlContext)"],"metadata":{},"outputs":[],"execution_count":17},{"cell_type":"code","source":["# Help can be used on any Python object\nhelp(map)\nhelp(Test)"],"metadata":{},"outputs":[],"execution_count":18},{"cell_type":"markdown","source":["## Part 2: Exploratory Data Analysis\n\nLet's begin looking at our data.  For this lab, we will use a data set from NASA Kennedy Space Center web server in Florida. The full data set is freely available at <http://ita.ee.lbl.gov/html/contrib/NASA-HTTP.html>, and it contains all HTTP requests for two months. We are using a subset that only contains several days' worth of requests.  The log file has already been downloaded for you."],"metadata":{}},{"cell_type":"code","source":["# Specify path to downloaded log file\nimport sys\nimport os\n\nlog_file_path = 'dbfs:/' + os.path.join('databricks-datasets', 'cs100', 'lab2', 'data-001', 'apache.access.log.PROJECT')"],"metadata":{},"outputs":[],"execution_count":20},{"cell_type":"markdown","source":["### (2a) Loading the log file\n\nNow that we have the path to the file, let's load it into a DataFrame. We'll do this in steps. First, we'll use `sqlContext.read.text()` to read the text file. This will produce a DataFrame with a single string column called `value`."],"metadata":{}},{"cell_type":"code","source":["base_df = sqlContext.read.text(log_file_path)\n# Let's look at the schema\nbase_df.printSchema()"],"metadata":{},"outputs":[],"execution_count":22},{"cell_type":"markdown","source":["Let's take a look at some of the data."],"metadata":{}},{"cell_type":"code","source":["base_df.show(truncate=False)"],"metadata":{},"outputs":[],"execution_count":24},{"cell_type":"markdown","source":["### (2b) Parsing the log file"],"metadata":{}},{"cell_type":"markdown","source":["If you're familiar with web servers at all, you'll recognize that this is in [Common Log Format](https://www.w3.org/Daemon/User/Config/Logging.html#common-logfile-format). The fields are:\n\n_remotehost rfc931 authuser [date] \"request\" status bytes_\n\n| field         | meaning                                                                |\n| ------------- | ---------------------------------------------------------------------- |\n| _remotehost_  | Remote hostname (or IP number if DNS hostname is not available).       |\n| _rfc931_      | The remote logname of the user. We don't really care about this field. |\n| _authuser_    | The username of the remote user, as authenticated by the HTTP server.  |\n| _[date]_      | The date and time of the request.                                      |\n| _\"request\"_   | The request, exactly as it came from the browser or client.            |\n| _status_      | The HTTP status code the server sent back to the client.               |\n| _bytes_       | The number of bytes (`Content-Length`) transferred to the client.      |\n\n\nNext, we have to parse it into individual columns. We'll use the special built-in [regexp\\_extract()](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.functions.regexp_extract)\nfunction to do the parsing. This function matches a column against a regular expression with one or more [capture groups](http://regexone.com/lesson/capturing_groups) and allows you to extract one of the matched groups. We'll use one regular expression for each field we wish to extract.\n\nIf you can't read these regular expressions, don't worry. Trust us: They work. If you find regular expressions confusing (and they certainly _can_ be), and you want to learn more about them, start with the\n[RegexOne web site](http://regexone.com/). You might also find [_Regular Expressions Cookbook_](http://shop.oreilly.com/product/0636920023630.do), by Jan Goyvaerts and Steven Levithan, to be helpful.\n\n_Some people, when confronted with a problem, think \"I know, I'll use regular expressions.\" Now they have two problems._ (attributed to Jamie Zawinski)"],"metadata":{}},{"cell_type":"code","source":["from pyspark.sql.functions import split, regexp_extract\nsplit_df = base_df.select(regexp_extract('value', r'^([^\\s]+\\s)', 1).alias('host'),\n                          regexp_extract('value', r'^.*\\[(\\d\\d/\\w{3}/\\d{4}:\\d{2}:\\d{2}:\\d{2} -\\d{4})]', 1).alias('timestamp'),\n                          regexp_extract('value', r'^.*\"\\w+\\s+([^\\s]+)\\s+HTTP.*\"', 1).alias('path'),\n                          regexp_extract('value', r'^.*\"\\s+([^\\s]+)', 1).cast('integer').alias('status'),\n                          regexp_extract('value', r'^.*\\s+(\\d+)$', 1).cast('integer').alias('content_size'))\nsplit_df.show(truncate=False)"],"metadata":{},"outputs":[],"execution_count":27},{"cell_type":"markdown","source":["### (2c) Data Cleaning\n\nLet's see how well our parsing logic worked. First, let's verify that there are no null rows in the original data set."],"metadata":{}},{"cell_type":"code","source":["base_df.filter(base_df['value'].isNull()).count()"],"metadata":{},"outputs":[],"execution_count":29},{"cell_type":"markdown","source":["If our parsing worked properly, we'll have no rows with null column values. Let's check."],"metadata":{}},{"cell_type":"code","source":["bad_rows_df = split_df.filter(split_df['host'].isNull() |\n                              split_df['timestamp'].isNull() |\n                              split_df['path'].isNull() |\n                              split_df['status'].isNull() |\n                             split_df['content_size'].isNull())\nbad_rows_df.count()"],"metadata":{},"outputs":[],"execution_count":31},{"cell_type":"markdown","source":["Not good. We have some null values. Something went wrong. Which columns are affected?\n\n(Note: This approach is adapted from an [excellent answer](http://stackoverflow.com/a/33901312) on StackOverflow.)"],"metadata":{}},{"cell_type":"code","source":["from pyspark.sql.functions import col, sum\n\ndef count_null(col_name):\n  return sum(col(col_name).isNull().cast('integer')).alias(col_name)\n\n# Build up a list of column expressions, one per column.\n#\n# This could be done in one line with a Python list comprehension, but we're keeping\n# it simple for those who don't know Python very well.\nexprs = []\nfor col_name in split_df.columns:\n  exprs.append(count_null(col_name))\n\n# Run the aggregation. The *exprs converts the list of expressions into\n# variable function arguments.\nsplit_df.agg(*exprs).show()"],"metadata":{},"outputs":[],"execution_count":33},{"cell_type":"markdown","source":["Okay, they're all in the `content_size` column. Let's see if we can figure out what's wrong. Our original parsing regular expression for that column was:\n\n```\nregexp_extract('value', r'^.*\\s+(\\d+)$', 1).cast('integer').alias('content_size')\n```\n\nThe `\\d+` selects one or more digits at the end of the input line. Is it possible there are lines without a valid content size? Or is there something wrong with our regular expression? Let's see if there are any lines that do not end with one or more digits.\n\n**Note**: In the expression below, `~` means \"not\"."],"metadata":{}},{"cell_type":"code","source":["bad_content_size_df = base_df.filter(~ base_df['value'].rlike(r'\\d+$'))\nbad_content_size_df.count()"],"metadata":{},"outputs":[],"execution_count":35},{"cell_type":"markdown","source":["That's it! The count matches the number of rows in `bad_rows_df` exactly.\n\nLet's take a look at some of the bad column values. Since it's possible that the rows end in extra white space, we'll tack a marker character onto the end of each line, to make it easier to see trailing white space."],"metadata":{}},{"cell_type":"code","source":["from pyspark.sql.functions import lit, concat\nbad_content_size_df.select(concat(bad_content_size_df['value'], lit('*'))).show(truncate=False)"],"metadata":{},"outputs":[],"execution_count":37},{"cell_type":"markdown","source":["Ah. The bad rows correspond to error results, where no content was sent back and the server emitted a \"`-`\" for the `content_size` field. Since we don't want to discard those rows from our analysis, let's map them to 0."],"metadata":{}},{"cell_type":"markdown","source":["### (2d) Fix the rows with null content\\_size\n\nThe easiest solution is to replace the null values in `split_df` with 0. The DataFrame API provides a set of functions and fields specifically designed for working with null values, among them:\n\n* [fillna()](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame.fillna), which fills null values with specified non-null values.\n* [na](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame.na), which returns a [DataFrameNaFunctions](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrameNaFunctions) object with many functions for operating on null columns.\n\nWe'll use `fillna()`, because it's simple. There are several ways to invoke this function. The easiest is just to replace _all_ null columns with known values. But, for safety, it's better to pass a Python dictionary containing (column\\_name, value) mappings. That's what we'll do."],"metadata":{}},{"cell_type":"code","source":["# Replace all null content_size values with 0.\ncleaned_df = split_df.na.fill({'content_size': 0})"],"metadata":{},"outputs":[],"execution_count":40},{"cell_type":"code","source":["# Ensure that there are no nulls left.\nexprs = []\nfor col_name in cleaned_df.columns:\n  exprs.append(count_null(col_name))\n\ncleaned_df.agg(*exprs).show()"],"metadata":{},"outputs":[],"execution_count":41},{"cell_type":"markdown","source":["### (2e) Parsing the timestamp.\n\nOkay, now that we have a clean, parsed DataFrame, we have to parse the timestamp field into an actual timestamp. The Common Log Format time is somewhat non-standard. A User-Defined Function (UDF) is the most straightforward way to parse it."],"metadata":{}},{"cell_type":"code","source":["month_map = {\n  'Jan': 1, 'Feb': 2, 'Mar':3, 'Apr':4, 'May':5, 'Jun':6, 'Jul':7,\n  'Aug':8,  'Sep': 9, 'Oct':10, 'Nov': 11, 'Dec': 12\n}\n\ndef parse_clf_time(s):\n    \"\"\" Convert Common Log time format into a Python datetime object\n    Args:\n        s (str): date and time in Apache time format [dd/mmm/yyyy:hh:mm:ss (+/-)zzzz]\n    Returns:\n        a string suitable for passing to CAST('timestamp')\n    \"\"\"\n    # NOTE: We're ignoring time zone here. In a production application, you'd want to handle that.\n    return \"{0:04d}-{1:02d}-{2:02d} {3:02d}:{4:02d}:{5:02d}\".format(\n      int(s[7:11]),\n      month_map[s[3:6]],\n      int(s[0:2]),\n      int(s[12:14]),\n      int(s[15:17]),\n      int(s[18:20])\n    )\n\nu_parse_time = udf(parse_clf_time)\n\nlogs_df = cleaned_df.select('*', u_parse_time(split_df['timestamp']).cast('timestamp').alias('time')).drop('timestamp')\ntotal_log_entries = logs_df.count()"],"metadata":{},"outputs":[],"execution_count":43},{"cell_type":"code","source":["logs_df.printSchema()"],"metadata":{},"outputs":[],"execution_count":44},{"cell_type":"code","source":["display(logs_df)"],"metadata":{},"outputs":[],"execution_count":45},{"cell_type":"markdown","source":["Let's cache `logs_df`. We're going to be using it quite a bit from here forward."],"metadata":{}},{"cell_type":"code","source":["logs_df.cache()"],"metadata":{},"outputs":[],"execution_count":47},{"cell_type":"markdown","source":["## Part 3: Analysis Walk-Through on the Web Server Log File\n\nNow that we have a DataFrame containing the parsed log file as a set of Row objects, we can perform various analyses.\n\n### (3a) Example: Content Size Statistics\n\nLet's compute some statistics about the sizes of content being returned by the web server. In particular, we'd like to know what are the average, minimum, and maximum content sizes.\n\nWe can compute the statistics by calling `.describe()` on the `content_size` column of `logs_df`.  The `.describe()` function returns the count, mean, stddev, min, and max of a given column."],"metadata":{}},{"cell_type":"code","source":["# Calculate statistics based on the content size.\ncontent_size_summary_df = logs_df.describe(['content_size'])\ncontent_size_summary_df.show()"],"metadata":{},"outputs":[],"execution_count":49},{"cell_type":"markdown","source":["Alternatively, we can use SQL to directly calculate these statistics.  You can explore the many useful functions within the `pyspark.sql.functions` module in the [documentation](https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#module-pyspark.sql.functions).\n\nAfter we apply the `.agg()` function, we call `.first()` to extract the first value, which is equivalent to `.take(1)[0]`."],"metadata":{}},{"cell_type":"code","source":["from pyspark.sql import functions as sqlFunctions\ncontent_size_stats =  (logs_df\n                       .agg(sqlFunctions.min(logs_df['content_size']),\n                            sqlFunctions.avg(logs_df['content_size']),\n                            sqlFunctions.max(logs_df['content_size']))\n                       .first())\n\nprint 'Using SQL functions:'\nprint 'Content Size Avg: {1:,.2f}; Min: {0:.2f}; Max: {2:,.0f}'.format(*content_size_stats)"],"metadata":{},"outputs":[],"execution_count":51},{"cell_type":"markdown","source":["### (3b) Example: HTTP Status Analysis\n\nNext, let's look at the status values that appear in the log. We want to know which status values appear in the data and how many times.  We again start with `logs_df`, then group by the `status` column, apply the `.count()` aggregation function, and sort by the `status` column."],"metadata":{}},{"cell_type":"code","source":["status_to_count_df =(logs_df\n                     .groupBy('status')\n                     .count()\n                     .sort('status')\n                     .cache())\n\nstatus_to_count_length = status_to_count_df.count()\nprint 'Found %d response codes' % status_to_count_length\nstatus_to_count_df.show()\n\nassert status_to_count_length == 7\nassert status_to_count_df.take(100) == [(200, 940847), (302, 16244), (304, 79824), (403, 58), (404, 6185), (500, 2), (501, 17)]"],"metadata":{},"outputs":[],"execution_count":53},{"cell_type":"markdown","source":["### (3c) Example: Status Graphing\n\nNow, let's visualize the results from the last example.  We can use the built-in `display()` function to show a bar chart of the count for each response code.  After running this cell, select the bar graph option, and then use \"Plot Options...\" and drag `status` to the key entry field and drag `count` to the value entry field. See the diagram, below, for an example.\n\n<img src=\"http://spark-mooc.github.io/web-assets/images/cs105x/plot_options_1.png\" style=\"float: right; margin-right: 30px; border: 1px solid #999999\"/>"],"metadata":{}},{"cell_type":"code","source":["display(status_to_count_df)"],"metadata":{},"outputs":[],"execution_count":55},{"cell_type":"markdown","source":["You can see that this is not a very effective plot.  Due to the large number of '200' codes, it is very hard to see the relative number of the others.  We can alleviate this by taking the logarithm of the count, adding that as a column to our DataFrame and displaying the result."],"metadata":{}},{"cell_type":"code","source":["log_status_to_count_df = status_to_count_df.withColumn('log(count)', sqlFunctions.log(status_to_count_df['count']))\n\ndisplay(log_status_to_count_df)"],"metadata":{},"outputs":[],"execution_count":57},{"cell_type":"markdown","source":["While this graph is an improvement, we might want to make more adjustments.  The [`matplotlib` library](http://matplotlib.org/) can give us more control in our plot and is also useful outside the Databricks environment. In this case, we're essentially just reproducing the Databricks graph using `matplotlib`. However, `matplotlib` exposes far more controls than the Databricks graph, allowing you to change colors, label the axes, and more. We're using a set of helper functions from the [`spark_notebook_helpers`](https://pypi.python.org/pypi/spark_notebook_helpers/1.0.1) library."],"metadata":{}},{"cell_type":"code","source":["# np is just an alias for numpy.\n# cm and plt are aliases for matplotlib.cm (for \"color map\") and matplotlib.pyplot, respectively.\n# prepareSubplot is a helper.\nfrom spark_notebook_helpers import prepareSubplot, np, plt, cm"],"metadata":{},"outputs":[],"execution_count":59},{"cell_type":"code","source":["help(prepareSubplot)"],"metadata":{},"outputs":[],"execution_count":60},{"cell_type":"markdown","source":["We're using the \"Set1\" color map. See the list of Qualitative Color Maps at <http://matplotlib.org/examples/color/colormaps_reference.html> for more details. Feel free to change the color map to a different one, like \"Accent\"."],"metadata":{}},{"cell_type":"code","source":["data = log_status_to_count_df.drop('count').collect()\nx, y = zip(*data)\nindex = np.arange(len(x))\nbar_width = 0.7\ncolorMap = 'Set1'\ncmap = cm.get_cmap(colorMap)\n\nfig, ax = prepareSubplot(np.arange(0, 6, 1), np.arange(0, 14, 2))\nplt.bar(index, y, width=bar_width, color=cmap(0))\nplt.xticks(index + bar_width/2.0, x)\ndisplay(fig)"],"metadata":{},"outputs":[],"execution_count":62},{"cell_type":"markdown","source":["### (3d) Example: Frequent Hosts\n\nLet's look at hosts that have accessed the server frequently (e.g., more than ten times). As with the response code analysis in (3b), we create a new DataFrame by grouping `successLogsDF` by the 'host' column and aggregating by count.\n\nWe then filter the result based on the count of accesses by each host being greater than ten.  Then, we select the 'host' column and show 20 elements from the result."],"metadata":{}},{"cell_type":"code","source":["# Any hosts that has accessed the server more than 10 times.\nhost_sum_df =(logs_df\n              .groupBy('host')\n              .count())\n\nhost_more_than_10_df = (host_sum_df\n                        .filter(host_sum_df['count'] > 10)\n                        .select(host_sum_df['host']))\n\nprint 'Any 20 hosts that have accessed more then 10 times:\\n'\nhost_more_than_10_df.show(truncate=False)"],"metadata":{},"outputs":[],"execution_count":64},{"cell_type":"markdown","source":["### (3e) Example: Visualizing Paths\n\nNow, let's visualize the number of hits to paths (URIs) in the log. To perform this task, we start with our `logs_df` and group by the `path` column, aggregate by count, and sort in descending order.\n\nNext we visualize the results using `matplotlib`. We previously imported the `prepareSubplot` function and the `matplotlib.pyplot` library, so we do not need to import them again.  We extract the paths and the counts, and unpack the resulting list of `Rows` using a `map` function and `lambda` expression."],"metadata":{}},{"cell_type":"code","source":["paths_df = (logs_df\n            .groupBy('path')\n            .count()\n            .sort('count', ascending=False))\n\npaths_counts = (paths_df\n                .select('path', 'count')\n                .map(lambda r: (r[0], r[1]))\n                .collect())\n\npaths, counts = zip(*paths_counts)\n\ncolorMap = 'Accent'\ncmap = cm.get_cmap(colorMap)\nindex = np.arange(1000)\n\nfig, ax = prepareSubplot(np.arange(0, 1000, 100), np.arange(0, 70000, 10000))\nplt.xlabel('Paths')\nplt.ylabel('Number of Hits')\nplt.plot(index, counts[:1000], color=cmap(0), linewidth=3)\nplt.axhline(linewidth=2, color='#999999')\ndisplay(fig)"],"metadata":{},"outputs":[],"execution_count":66},{"cell_type":"markdown","source":["We can also visualize the results as a line graph using the built-in Databricks `display` function to graph the results.  After calling this function on `paths_df`, select the line graph option.\n\nThe graph is plotted using the first 1,000 rows of data. To see a more complete plot, click on the \"Plot over all results\" link. Be prepared to wait a minute or so."],"metadata":{}},{"cell_type":"code","source":["display(paths_df)"],"metadata":{},"outputs":[],"execution_count":68},{"cell_type":"markdown","source":["### (3f) Example: Top Paths\n\nFor the final example, we'll find the top paths (URIs) in the log.  Because we sorted `paths_df` for plotting, all we need to do is call `.show()` and pass in `n=10` and `truncate=False` as the parameters to show the top ten paths without truncating."],"metadata":{}},{"cell_type":"code","source":["# Top Paths\nprint 'Top Ten Paths:'\npaths_df.show(n=10, truncate=False)\n\nexpected = [\n  (u'/images/NASA-logosmall.gif', 59666),\n  (u'/images/KSC-logosmall.gif', 50420),\n  (u'/images/MOSAIC-logosmall.gif', 43831),\n  (u'/images/USA-logosmall.gif', 43604),\n  (u'/images/WORLD-logosmall.gif', 43217),\n  (u'/images/ksclogo-medium.gif', 41267),\n  (u'/ksc.html', 28536),\n  (u'/history/apollo/images/apollo-logo1.gif', 26766),\n  (u'/images/launch-logo.gif', 24742),\n  (u'/', 20173)\n]\nassert paths_df.take(10) == expected, 'incorrect Top Ten Paths'"],"metadata":{},"outputs":[],"execution_count":70},{"cell_type":"markdown","source":["### Part 4: Analyzing Web Server Log File\n\nNow it is your turn to perform analyses on the web server log files."],"metadata":{}},{"cell_type":"markdown","source":["**(4a) Exercise: Top Ten Error Paths**\n\nWhat are the top ten paths which did not have return code 200? Create a sorted list containing the paths and the number of times that they were accessed with a non-200 return code and show the top ten.\n\nThink about the steps that you need to perform to determine which paths did not have a 200 return code, how you will uniquely count those paths and sort the list."],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n# You are welcome to structure your solution in a different way, so long as\n# you ensure the variables used in the next Test section are defined\n\n# DataFrame containing all accesses that did not return a code 200\nfrom pyspark.sql.functions import desc\nnot200DF = logs_df.filter(logs_df['status'] != 200)\nnot200DF.show(10)\n# Sorted DataFrame containing all paths and the number of times they were accessed with non-200 return code\nlogs_sum_df = not200DF.groupBy('path').count().orderBy(desc(\"count\"))\n\nprint 'Top Ten failed URLs:'\nlogs_sum_df.show(10, False)"],"metadata":{},"outputs":[],"execution_count":73},{"cell_type":"code","source":["# TEST Top ten error paths (4a)\ntop_10_err_urls = [(row[0], row[1]) for row in logs_sum_df.take(10)]\ntop_10_err_expected = [\n  (u'/images/NASA-logosmall.gif', 8761),\n  (u'/images/KSC-logosmall.gif', 7236),\n  (u'/images/MOSAIC-logosmall.gif', 5197),\n  (u'/images/USA-logosmall.gif', 5157),\n  (u'/images/WORLD-logosmall.gif', 5020),\n  (u'/images/ksclogo-medium.gif', 4728),\n  (u'/history/apollo/images/apollo-logo1.gif', 2907),\n  (u'/images/launch-logo.gif', 2811),\n  (u'/', 2199),\n  (u'/images/ksclogosmall.gif', 1622)\n]\nTest.assertEquals(logs_sum_df.count(), 7675, 'incorrect count for logs_sum_df')\nTest.assertEquals(top_10_err_urls, top_10_err_expected, 'incorrect Top Ten failed URLs')"],"metadata":{},"outputs":[],"execution_count":74},{"cell_type":"markdown","source":["### (4b) Exercise: Number of Unique Hosts\n\nHow many unique hosts are there in the entire log?\n\nThere are multiple ways to find this.  Try to find a more optimal way than grouping by 'host'."],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\nunique_host_count = logs_df.select('host').distinct().count()\nprint 'Unique hosts: {0}'.format(unique_host_count)"],"metadata":{},"outputs":[],"execution_count":76},{"cell_type":"code","source":["# TEST Number of unique hosts (4b)\nTest.assertEquals(unique_host_count, 54507, 'incorrect unique_host_count')"],"metadata":{},"outputs":[],"execution_count":77},{"cell_type":"markdown","source":["### (4c) Exercise: Number of Unique Daily Hosts\n\nFor an advanced exercise, let's determine the number of unique hosts in the entire log on a day-by-day basis. This computation will give us counts of the number of unique daily hosts. We'd like a DataFrame sorted by increasing day of the month which includes the day of the month and the associated number of unique hosts for that day. Make sure you cache the resulting DataFrame `daily_hosts_df` so that we can reuse it in the next exercise.\n\nThink about the steps that you need to perform to count the number of different hosts that make requests *each* day.\n*Since the log only covers a single month, you can ignore the month.*  You may want to use the [`dayofmonth` function](https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.functions.dayofmonth) in the `pyspark.sql.functions` module.\n\n**Description of each variable**\n\n**`day_to_host_pair_df`**\n\nA DataFrame with two columns\n\n| column | explanation          |\n| ------ | -------------------- |\n| `host` | the host name        |\n| `day`  | the day of the month |\n\nThere will be one row in this DataFrame for each row in `logs_df`. Essentially, you're just trimming and transforming each row of `logs_df`. For example, for this row in `logs_df`:\n\n```\ngw1.att.com - - [23/Aug/1995:00:03:53 -0400] \"GET /shuttle/missions/sts-73/news HTTP/1.0\" 302 -\n```\n\nyour `day_to_host_pair_df` should have:\n\n```\ngw1.att.com 23\n```\n\n**`day_group_hosts_df`**\n\nThis DataFrame has the same columns as `day_to_host_pair_df`, but with duplicate (`day`, `host`) rows removed.\n\n**`daily_hosts_df`**\n\nA DataFrame with two columns:\n\n| column  | explanation                                        |\n| ------- | -------------------------------------------------- |\n| `day`   | the day of the month                               |\n| `count` | the number of unique requesting hosts for that day |"],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\nfrom pyspark.sql.functions import dayofmonth\n\nday_to_host_pair_df = logs_df.select('host', dayofmonth('time').alias('day'))\nday_group_hosts_df = day_to_host_pair_df.distinct()\ndaily_hosts_df = day_group_hosts_df.groupBy('day').count()\n\nprint 'Unique hosts per day:'\ndaily_hosts_df.show(30, False)\ndaily_hosts_df.cache()"],"metadata":{},"outputs":[],"execution_count":79},{"cell_type":"code","source":["# TEST Number of unique daily hosts (4c)\ndaily_hosts_list = (daily_hosts_df\n                    .map(lambda r: (r[0], r[1]))\n                    .take(30))\n\nTest.assertEquals(day_to_host_pair_df.count(), total_log_entries, 'incorrect row count for day_to_host_pair_df')\nTest.assertEquals(daily_hosts_df.count(), 21, 'incorrect daily_hosts_df.count()')\nTest.assertEquals(daily_hosts_list, [(1, 2582), (3, 3222), (4, 4190), (5, 2502), (6, 2537), (7, 4106), (8, 4406), (9, 4317), (10, 4523), (11, 4346), (12, 2864), (13, 2650), (14, 4454), (15, 4214), (16, 4340), (17, 4385), (18, 4168), (19, 2550), (20, 2560), (21, 4134), (22, 4456)], 'incorrect daily_hosts_df')\nTest.assertTrue(daily_hosts_df.is_cached, 'incorrect daily_hosts_df.is_cached')"],"metadata":{},"outputs":[],"execution_count":80},{"cell_type":"markdown","source":["### (4d) Exercise: Visualizing the Number of Unique Daily Hosts\n\nUsing the results from the previous exercise, we will use `matplotlib` to plot a line graph of the unique hosts requests by day.  We need a list of days called `days_with_hosts` and a list of the number of unique hosts for each corresponding day called `hosts`.\n\n**WARNING**: Simply calling `collect()` on your transformed DataFrame won't work, because `collect()` returns a list of Spark SQL `Row` objects. You must _extract_ the appropriate column values from the `Row` objects. Hint: A loop will help."],"metadata":{}},{"cell_type":"code","source":["# TODO: Your solution goes here\n\ndays_with_hosts = [d['day'] for d in daily_hosts_df.select('day').collect()]\nhosts = [d['count'] for d in daily_hosts_df.select('count').collect()]\n\nprint(days_with_hosts)\nprint(hosts)"],"metadata":{},"outputs":[],"execution_count":82},{"cell_type":"code","source":["# TEST Visualizing unique daily hosts (4d)\ntest_days = range(1, 23)\ntest_days.remove(2)\nTest.assertEquals(days_with_hosts, test_days, 'incorrect days')\nTest.assertEquals(hosts, [2582, 3222, 4190, 2502, 2537, 4106, 4406, 4317, 4523, 4346, 2864, 2650, 4454, 4214, 4340, 4385, 4168, 2550, 2560, 4134, 4456], 'incorrect hosts')"],"metadata":{},"outputs":[],"execution_count":83},{"cell_type":"code","source":["fig, ax = prepareSubplot(np.arange(0, 30, 5), np.arange(0, 5000, 1000))\ncolorMap = 'Dark2'\ncmap = cm.get_cmap(colorMap)\nplt.plot(days_with_hosts, hosts, color=cmap(0), linewidth=3)\nplt.axis([0, max(days_with_hosts), 0, max(hosts)+500])\nplt.xlabel('Day')\nplt.ylabel('Hosts')\nplt.axhline(linewidth=3, color='#999999')\nplt.axvline(linewidth=2, color='#999999')\ndisplay(fig)"],"metadata":{},"outputs":[],"execution_count":84},{"cell_type":"markdown","source":["You can also pass in the `day_host_count_df` DataFrame into Databricks plots to plot a line or bar graph of the unique hosts requests by day."],"metadata":{}},{"cell_type":"code","source":["display(daily_hosts_df)"],"metadata":{},"outputs":[],"execution_count":86},{"cell_type":"markdown","source":["### (4e) Exercise: Average Number of Daily Requests per Host\n\nNext, let's determine the average number of requests on a day-by-day basis.  We'd like a list by increasing day of the month and the associated average number of requests per host for that day. Make sure you cache the resulting DataFrame `avg_daily_req_per_host_df` so that we can reuse it in the next exercise.\n\nTo compute the average number of requests per host, find the total number of requests per day (across all hosts) and divide that by the number of unique hosts per day (which we found in part 4c and cached as `daily_hosts_df`).\n\n*Since the log only covers a single month, you can skip checking for the month.*"],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\ntotal_req_per_day_df = logs_df.groupBy(dayofmonth('time').alias('day')).count()\navg_daily_req_per_host_df = (\n  total_req_per_day_df.join(daily_hosts_df, total_req_per_day_df.day == daily_hosts_df.day)\n                      .select(total_req_per_day_df['day'], (total_req_per_day_df['count']/daily_hosts_df['count']).alias('avg_reqs_per_host_per_day'))\n)\n\nprint 'Average number of daily requests per Hosts is:\\n'\navg_daily_req_per_host_df.show()\navg_daily_req_per_host_df.cache()"],"metadata":{},"outputs":[],"execution_count":88},{"cell_type":"code","source":["# TEST Average number of daily requests per hosts (4e)\navg_daily_req_per_host_list = (\n  avg_daily_req_per_host_df.select('day', avg_daily_req_per_host_df['avg_reqs_per_host_per_day'].cast('integer').alias('avg_requests'))\n                           .collect()\n)\n\nvalues = [(row[0], row[1]) for row in avg_daily_req_per_host_list]\nprint values\nTest.assertEquals(values, [(1, 13), (3, 12), (4, 14), (5, 12), (6, 12), (7, 13), (8, 13), (9, 14), (10, 13), (11, 14), (12, 13), (13, 13), (14, 13), (15, 13), (16, 13), (17, 13), (18, 13), (19, 12), (20, 12), (21, 13), (22, 12)], 'incorrect avgDailyReqPerHostDF')\nTest.assertTrue(avg_daily_req_per_host_df.is_cached, 'incorrect avg_daily_req_per_host_df.is_cached')"],"metadata":{},"outputs":[],"execution_count":89},{"cell_type":"markdown","source":["### (4f) Exercise: Visualizing the Average Daily Requests per Unique Host\n\nUsing the result `avg_daily_req_per_host_df` from the previous exercise, use `matplotlib` to plot a line graph of the average daily requests per unique host by day.\n\n`days_with_avg` should be a list of days and `avgs` should be a list of average daily requests (as integers) per unique hosts for each corresponding day.  Hint: You will need to extract these from the Dataframe in a similar way to part 4d."],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\ndays_with_avg = [d['day'] for d in avg_daily_req_per_host_df.select('day').collect()]\navgs = [d['avg_reqs_per_host_per_day'] for d in avg_daily_req_per_host_df.select('avg_reqs_per_host_per_day').collect()]\n\nprint(days_with_avg)\nprint(avgs)"],"metadata":{},"outputs":[],"execution_count":91},{"cell_type":"code","source":["# TEST Average Daily Requests per Unique Host (4f)\nTest.assertEquals(days_with_avg, [1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22], 'incorrect days')\nTest.assertEquals([int(a) for a in avgs], [13, 12, 14, 12, 12, 13, 13, 14, 13, 14, 13, 13, 13, 13, 13, 13, 13, 12, 12, 13, 12], 'incorrect avgs')"],"metadata":{},"outputs":[],"execution_count":92},{"cell_type":"code","source":["fig, ax = prepareSubplot(np.arange(0, 20, 5), np.arange(0, 16, 2))\ncolorMap = 'Set3'\ncmap = cm.get_cmap(colorMap)\nplt.plot(days_with_avg, avgs, color=cmap(0), linewidth=3)\nplt.axis([0, max(days_with_avg), 0, max(avgs)+2])\nplt.xlabel('Day')\nplt.ylabel('Average')\nplt.axhline(linewidth=3, color='#999999')\nplt.axvline(linewidth=2, color='#999999')\ndisplay(fig)"],"metadata":{},"outputs":[],"execution_count":93},{"cell_type":"markdown","source":["As a comparison to the prior plot, use the Databricks `display` function to plot a line graph of the average daily requests per unique host by day."],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\ndisplay(avg_daily_req_per_host_df)"],"metadata":{},"outputs":[],"execution_count":95},{"cell_type":"markdown","source":["## Part 5: Exploring 404 Status Codes\n\nLet's drill down and explore the error 404 status records. We've all seen those \"404 Not Found\" web pages. 404 errors are returned when the server cannot find the resource (page or object) the browser or client requested."],"metadata":{}},{"cell_type":"markdown","source":["### (5a) Exercise: Counting 404 Response Codes\n\nCreate a DataFrame containing only log records with a 404 status code. Make sure you `cache()` `not_found_df` as we will use it in the rest of this exercise.\n\nHow many 404 records are in the log?"],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\nnot_found_df = logs_df.filter(logs_df['status'] == 404)\nprint('Found {0} 404 URLs').format(not_found_df.count())\nnot_found_df.cache()"],"metadata":{},"outputs":[],"execution_count":98},{"cell_type":"code","source":["# TEST Counting 404 (5a)\nTest.assertEquals(not_found_df.count(), 6185, 'incorrect not_found_df.count()')\nTest.assertTrue(not_found_df.is_cached, 'incorrect not_found_df.is_cached')"],"metadata":{},"outputs":[],"execution_count":99},{"cell_type":"markdown","source":["### (5b) Exercise: Listing 404 Status Code Records\n\nUsing the DataFrame containing only log records with a 404 status code that you cached in part (5a), print out a list up to 40 _distinct_ paths that generate 404 errors.\n\n**No path should appear more than once in your list.**"],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\nnot_found_paths_df = not_found_df.select(not_found_df['path'])\nunique_not_found_paths_df = not_found_paths_df.distinct()\n\nprint '404 URLS:\\n'\nunique_not_found_paths_df.show(n=40, truncate=False)"],"metadata":{},"outputs":[],"execution_count":101},{"cell_type":"code","source":["# TEST Listing 404 records (5b)\n\nbad_unique_paths_40 = set([row[0] for row in unique_not_found_paths_df.take(40)])\nTest.assertEquals(len(bad_unique_paths_40), 40, 'bad_unique_paths_40 not distinct')"],"metadata":{},"outputs":[],"execution_count":102},{"cell_type":"markdown","source":["### (5c) Exercise: Listing the Top Twenty 404 Response Code paths\n\nUsing the DataFrame containing only log records with a 404 response code that you cached in part (5a), print out a list of the top twenty paths that generate the most 404 errors.\n\n*Remember, top paths should be in sorted order*"],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\ntop_20_not_found_df = not_found_paths_df.groupBy('path').count().orderBy(desc(\"count\"))\n\nprint 'Top Twenty 404 URLs:\\n'\ntop_20_not_found_df.show(n=20, truncate=False)"],"metadata":{},"outputs":[],"execution_count":104},{"cell_type":"code","source":["# TEST Top twenty 404 URLs (5c)\n\ntop_20_not_found = [(row[0], row[1]) for row in top_20_not_found_df.take(20)]\ntop_20_expected = [\n (u'/pub/winvn/readme.txt', 633),\n (u'/pub/winvn/release.txt', 494),\n (u'/shuttle/missions/STS-69/mission-STS-69.html', 430),\n (u'/images/nasa-logo.gif', 319),\n (u'/elv/DELTA/uncons.htm', 178),\n (u'/shuttle/missions/sts-68/ksc-upclose.gif', 154),\n (u'/history/apollo/sa-1/sa-1-patch-small.gif', 146),\n (u'/images/crawlerway-logo.gif', 120),\n (u'/://spacelink.msfc.nasa.gov', 117),\n (u'/history/apollo/pad-abort-test-1/pad-abort-test-1-patch-small.gif', 100),\n (u'/history/apollo/a-001/a-001-patch-small.gif', 97),\n (u'/images/Nasa-logo.gif', 85),\n (u'', 76),\n (u'/shuttle/resources/orbiters/atlantis.gif', 63),\n (u'/history/apollo/images/little-joe.jpg', 62),\n (u'/images/lf-logo.gif', 59),\n (u'/shuttle/resources/orbiters/discovery.gif', 56),\n (u'/shuttle/resources/orbiters/challenger.gif', 54),\n (u'/robots.txt', 53),\n (u'/history/apollo/pad-abort-test-2/pad-abort-test-2-patch-small.gif', 38)\n]\nTest.assertEquals(top_20_not_found, top_20_expected, 'incorrect top_20_not_found')"],"metadata":{},"outputs":[],"execution_count":105},{"cell_type":"markdown","source":["### (5d) Exercise: Listing the Top Twenty-five 404 Response Code Hosts\n\nInstead of looking at the paths that generated 404 errors, let's look at the hosts that encountered 404 errors. Using the DataFrame containing only log records with a 404 status codes that you cached in part (5a), print out a list of the top twenty-five hosts that generate the most 404 errors."],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\nhosts_404_count_df = not_found_df.groupBy('host').count().orderBy(desc(\"count\"))\n\nprint 'Top 25 hosts that generated errors:\\n'\nhosts_404_count_df.show(n=25, truncate=False)"],"metadata":{},"outputs":[],"execution_count":107},{"cell_type":"code","source":["# TEST Top twenty-five 404 response code hosts (4d)\n\ntop_25_404 = [(row[0], row[1]) for row in hosts_404_count_df.take(25)]\nTest.assertEquals(len(top_25_404), 25, 'length of errHostsTop25 is not 25')\n\nexpected = set([\n  (u'maz3.maz.net ', 39),\n  (u'piweba3y.prodigy.com ', 39),\n  (u'gate.barr.com ', 38),\n  (u'nexus.mlckew.edu.au ', 37),\n  (u'ts8-1.westwood.ts.ucla.edu ', 37),\n  (u'm38-370-9.mit.edu ', 37),\n  (u'204.62.245.32 ', 33),\n  (u'spica.sci.isas.ac.jp ', 27),\n  (u'163.206.104.34 ', 27),\n  (u'www-d4.proxy.aol.com ', 26),\n  (u'203.13.168.17 ', 25),\n  (u'203.13.168.24 ', 25),\n  (u'www-c4.proxy.aol.com ', 25),\n  (u'internet-gw.watson.ibm.com ', 24),\n  (u'crl5.crl.com ', 23),\n  (u'piweba5y.prodigy.com ', 23),\n  (u'scooter.pa-x.dec.com ', 23),\n  (u'onramp2-9.onr.com ', 22),\n  (u'slip145-189.ut.nl.ibm.net ', 22),\n  (u'198.40.25.102.sap2.artic.edu ', 21),\n  (u'msp1-16.nas.mr.net ', 20),\n  (u'gn2.getnet.com ', 20),\n  (u'tigger.nashscene.com ', 19),\n  (u'dial055.mbnet.mb.ca ', 19),\n  (u'isou24.vilspa.esa.es ', 19)\n])\nTest.assertEquals(len(set(top_25_404) - expected), 0, 'incorrect hosts_404_count_df')"],"metadata":{},"outputs":[],"execution_count":108},{"cell_type":"markdown","source":["### (5e) Exercise: Listing 404 Errors per Day\n\nLet's explore the 404 records temporally. Break down the 404 requests by day (cache the `errors_by_date_sorted_df` DataFrame) and get the daily counts sorted by day in `errors_by_date_sorted_df`.\n\n*Since the log only covers a single month, you can ignore the month in your checks.*"],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\nerrors_by_date_sorted_df = not_found_df.groupBy(dayofmonth('time').alias('day')).count()\n\nprint '404 Errors by day:\\n'\nerrors_by_date_sorted_df.show()\nerrors_by_date_sorted_df.cache()"],"metadata":{},"outputs":[],"execution_count":110},{"cell_type":"code","source":["# TEST 404 response codes per day (5e)\n\nerrors_by_date = [(row[0], row[1]) for row in errors_by_date_sorted_df.collect()]\nexpected = [\n  (1, 243),\n  (3, 303),\n  (4, 346),\n  (5, 234),\n  (6, 372),\n  (7, 532),\n  (8, 381),\n  (9, 279),\n  (10, 314),\n  (11, 263),\n  (12, 195),\n  (13, 216),\n  (14, 287),\n  (15, 326),\n  (16, 258),\n  (17, 269),\n  (18, 255),\n  (19, 207),\n  (20, 312),\n  (21, 305),\n  (22, 288)\n]\nTest.assertEquals(errors_by_date, expected, 'incorrect errors_by_date_sorted_df')\nTest.assertTrue(errors_by_date_sorted_df.is_cached, 'incorrect errors_by_date_sorted_df.is_cached')"],"metadata":{},"outputs":[],"execution_count":111},{"cell_type":"markdown","source":["### (5f) Exercise: Visualizing the 404 Errors by Day\n\nUsing the results from the previous exercise, use `matplotlib` to plot a line or bar graph of the 404 response codes by day.\n\n**Hint**: You'll need to use the same technique you used in (4f)."],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\ndays_with_errors_404 = [d['day'] for d in errors_by_date_sorted_df.select('day').collect()]\nerrors_404_by_day = [d['count'] for d in errors_by_date_sorted_df.select('count').collect()]\n\nprint days_with_errors_404\nprint errors_404_by_day"],"metadata":{},"outputs":[],"execution_count":113},{"cell_type":"code","source":["# TEST Visualizing the 404 Response Codes by Day (4f)\nTest.assertEquals(days_with_errors_404, [1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22], 'incorrect days_with_errors_404')\nTest.assertEquals(errors_404_by_day, [243, 303, 346, 234, 372, 532, 381, 279, 314, 263, 195, 216, 287, 326, 258, 269, 255, 207, 312, 305, 288], 'incorrect errors_404_by_day')"],"metadata":{},"outputs":[],"execution_count":114},{"cell_type":"code","source":["fig, ax = prepareSubplot(np.arange(0, 20, 5), np.arange(0, 600, 100))\ncolorMap = 'rainbow'\ncmap = cm.get_cmap(colorMap)\nplt.plot(days_with_errors_404, errors_404_by_day, color=cmap(0), linewidth=3)\nplt.axis([0, max(days_with_errors_404), 0, max(errors_404_by_day)])\nplt.xlabel('Day')\nplt.ylabel('404 Errors')\nplt.axhline(linewidth=3, color='#999999')\nplt.axvline(linewidth=2, color='#999999')\ndisplay(fig)"],"metadata":{},"outputs":[],"execution_count":115},{"cell_type":"markdown","source":["Using the results from exercise (5e), use the Databricks `display` function to plot a line or bar graph of the 404 response codes by day."],"metadata":{}},{"cell_type":"code","source":["display(errors_by_date_sorted_df)"],"metadata":{},"outputs":[],"execution_count":117},{"cell_type":"markdown","source":["### (5g) Exercise: Top Five Days for 404 Errors\n\nUsing the DataFrame `errors_by_date_sorted_df` you cached in the part (5e), what are the top five days for 404 errors and the corresponding counts of 404 errors?"],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\ntop_err_date_df = errors_by_date_sorted_df.orderBy(desc('count'))\n\nprint 'Top Five Dates for 404 Requests:\\n'\ntop_err_date_df.show(5)"],"metadata":{},"outputs":[],"execution_count":119},{"cell_type":"code","source":["# TEST Five dates for 404 requests (4g)\n\nTest.assertEquals([(r[0], r[1]) for r in top_err_date_df.take(5)], [(7, 532), (8, 381), (6, 372), (4, 346), (15, 326)], 'incorrect top_err_date_df')"],"metadata":{},"outputs":[],"execution_count":120},{"cell_type":"markdown","source":["### (5h) Exercise: Hourly 404 Errors\n\nUsing the DataFrame `not_found_df` you cached in the part (5a) and sorting by hour of the day in increasing order, create a DataFrame containing the number of requests that had a 404 return code for each hour of the day (midnight starts at 0). Cache the resulting DataFrame `hour_records_sorted_df` and print that as a list."],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\nfrom pyspark.sql.functions import hour\nhour_records_sorted_df = not_found_df.groupBy(hour('time').alias('hour')).count()\n\nprint 'Top hours for 404 requests:\\n'\nhour_records_sorted_df.show(24)\nhour_records_sorted_df.cache()"],"metadata":{},"outputs":[],"execution_count":122},{"cell_type":"code","source":["# TEST Hourly 404 response codes (5h)\n\nerrs_by_hour = [(row[0], row[1]) for row in hour_records_sorted_df.collect()]\n\nexpected = [\n  (0, 175),\n  (1, 171),\n  (2, 422),\n  (3, 272),\n  (4, 102),\n  (5, 95),\n  (6, 93),\n  (7, 122),\n  (8, 199),\n  (9, 185),\n  (10, 329),\n  (11, 263),\n  (12, 438),\n  (13, 397),\n  (14, 318),\n  (15, 347),\n  (16, 373),\n  (17, 330),\n  (18, 268),\n  (19, 269),\n  (20, 270),\n  (21, 241),\n  (22, 234),\n  (23, 272)\n]\nTest.assertEquals(errs_by_hour, expected, 'incorrect errs_by_hour')\nTest.assertTrue(hour_records_sorted_df.is_cached, 'incorrect hour_records_sorted_df.is_cached')"],"metadata":{},"outputs":[],"execution_count":123},{"cell_type":"markdown","source":["### (5i) Exercise: Visualizing the 404 Response Codes by Hour\n\nUsing the results from the previous exercise, use `matplotlib` to plot a line or bar graph of the 404 response codes by hour."],"metadata":{}},{"cell_type":"code","source":["# TODO: Replace <FILL IN> with appropriate code\n\nhours_with_not_found = [d['hour'] for d in hour_records_sorted_df.select('hour').collect()]\nnot_found_counts_per_hour = [d['count'] for d in hour_records_sorted_df.select('count').collect()]\n\nprint hours_with_not_found\nprint not_found_counts_per_hour"],"metadata":{},"outputs":[],"execution_count":125},{"cell_type":"code","source":["# TEST Visualizing the 404 Response Codes by Hour (5i)\nTest.assertEquals(hours_with_not_found, [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23], 'incorrect hours_with_not_found')\nTest.assertEquals(not_found_counts_per_hour, [175, 171, 422, 272, 102, 95, 93, 122, 199, 185, 329, 263, 438, 397, 318, 347, 373, 330, 268, 269, 270, 241, 234, 272], 'incorrect not_found_counts_per_hour')"],"metadata":{},"outputs":[],"execution_count":126},{"cell_type":"code","source":["fig, ax = prepareSubplot(np.arange(0, 25, 5), np.arange(0, 500, 50))\ncolorMap = 'seismic'\ncmap = cm.get_cmap(colorMap)\nplt.plot(hours_with_not_found, not_found_counts_per_hour, color=cmap(0), linewidth=3)\nplt.axis([0, max(hours_with_not_found), 0, max(not_found_counts_per_hour)])\nplt.xlabel('Hour')\nplt.ylabel('404 Errors')\nplt.axhline(linewidth=3, color='#999999')\nplt.axvline(linewidth=2, color='#999999')\ndisplay(fig)"],"metadata":{},"outputs":[],"execution_count":127},{"cell_type":"markdown","source":["Using the Databricks `display` function and the results from exercise (5h), plot a line or bar graph of the 404 response codes by hour."],"metadata":{}},{"cell_type":"code","source":["display(hour_records_sorted_df)"],"metadata":{},"outputs":[],"execution_count":129},{"cell_type":"markdown","source":["## Appendix A: Submitting Your Exercises to the Autograder\nOnce you confirm that your lab notebook is passing all tests, you can submit it first to the course autograder and then second to the edX website to receive a grade.\n<img src=\"https://d37djvu3ytnwxt.cloudfront.net/asset-v1:BerkeleyX+CS105x+1T2016+type@asset+block/url-process.png\" alt=\"Drawing\" style=\"float:right\"/>\n\n** Note that you can only submit to the course autograder once every 1 minute. **"],"metadata":{}},{"cell_type":"markdown","source":["** (a) Restart your cluster by clicking on the dropdown next to your cluster name and selecting \"Restart Cluster\".**\n\nYou can do this step in either notebook, since there is one cluster for your notebooks.\n\n<img src=\"http://spark-mooc.github.io/web-assets/images/submit_restart.png\" alt=\"Drawing\" />"],"metadata":{}},{"cell_type":"markdown","source":["** (b) _IN THIS NOTEBOOK_, click on \"Run All\" to run all of the cells. **\n\n<img src=\"http://spark-mooc.github.io/web-assets/images/submit_runall.png\" alt=\"Drawing\" style=\"height: 80px\"/>\n\nThis step will take some time. While the cluster is running all the cells in your lab notebook, you will see the \"Stop Execution\" button.\n\n <img src=\"http://spark-mooc.github.io/web-assets/images/stop_execution.png\" alt=\"Drawing\" />\n\nWait for your cluster to finish running the cells in your lab notebook before proceeding."],"metadata":{}},{"cell_type":"markdown","source":["** (c) Verify that your LAB notebook passes as many tests as you can. **\n\nMost computations should complete within a few seconds unless stated otherwise. As soon as the expressions of a cell have been successfully evaluated, you will see one or more \"test passed\" messages if the cell includes test expressions:\n\n<img src=\"http://spark-mooc.github.io/web-assets/images/test_passed.png\" alt=\"Drawing\" />\n\nIf the cell contains `print` statements or `show()` actions, you'll also see the output from those operations.\n\nThe very last line of output is always the execution time:\n\n<img src=\"http://spark-mooc.github.io/web-assets/images/execution_time.png\" alt=\"Drawing\" />"],"metadata":{}},{"cell_type":"markdown","source":["** (d) Publish your LAB notebook(this notebook) by clicking on the \"Publish\" button at the top of your LAB notebook. **\n\n<img src=\"http://spark-mooc.github.io/web-assets/images/Lab0_Publish0.png\" alt=\"Drawing\" style=\"height: 150px\"/>\n\nWhen you click on the button, you will see the following popup.\n\n<img src=\"http://spark-mooc.github.io/web-assets/images/Lab0_Publish1.png\" alt=\"Drawing\" />\n\nWhen you click on \"Publish\", you will see a popup with your notebook's public link. **Copy the link and set the `notebook_URL` variable in the AUTOGRADER notebook (not this notebook).**\n\n<img src=\"http://spark-mooc.github.io/web-assets/images/Lab0_Publish2.png\" alt=\"Drawing\" />"],"metadata":{}},{"cell_type":"code","source":[""],"metadata":{},"outputs":[],"execution_count":135}],"metadata":{"name":"cs105_lab2_apache_log","notebookId":1427256573868007},"nbformat":4,"nbformat_minor":0}