{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"./assets/logos/Logo_main.png\" width=500></img> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Amazon Athena\n",
    "You will use Amazon Athena to monitor the performance of your model. Athena allows you to run SQL queries against data in S3, like the game archive data. You can see an example query by running the following command:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!../bin/athena_info.sh"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Go to the [Athena Console](https://console.aws.amazon.com/athena/home?region=us-east-1#query)\n",
    "1. You will need to change your Athena workgroup. Select \"working group\". Then in the list select \"GameDay\" and then \"switch workgroup\".\n",
    "<img src=\"./assets/athena-homepage.jpg\" width=500></img> \n",
    "<img src=\"./assets/athena-workgroups.jpg\" width=500></img> \n",
    "1. You are going to use the following query but you need to first get some values. You need the name of the Amazon Glue catalog that holds the game data and the S3 bucket where that data is stored. These values are given by the above command. Replace <Game-data-catalog> and <Game-data-bucket> with those values. So for example the line `FROM \"<Game-data-catalog>\".\"<Game-data-bucket>\"` will look something like `FROM \"game-catalog-adafafdad\".\"game-archive-adfasdfasdf\"`\n",
    "1. Next, replace the occurrences of <Team-Name> with your team name. For example, the line `WHEN winner = '<Team-Name>' THEN 1.0` becomes `WHEN winner = 'my-team' THEN 1.0`\n",
    "\n",
    "```sql\n",
    "SELECT *\n",
    "FROM   (SELECT gameid,\n",
    "               Cast(Cast(teama.teamname AS JSON) AS VARCHAR)\n",
    "               AS\n",
    "                      teama,\n",
    "               Cast(Cast(teamb.teamname AS JSON) AS VARCHAR)\n",
    "               AS\n",
    "                      teamb,\n",
    "               Json_extract(teama.session, '$.shootType')\n",
    "               AS\n",
    "                      teamAShoot,\n",
    "               Json_extract(teama.session, '$.type')\n",
    "               AS\n",
    "                      teamALayout,\n",
    "               Json_extract(teamb.session, '$.shootType')\n",
    "               AS\n",
    "                      teamBShoot,\n",
    "               Json_extract(teamb.session, '$.type')\n",
    "               AS\n",
    "                      teamBLayout,\n",
    "               winner,\n",
    "               Date_format(From_unixtime(start_time / 1000), '%m-%d-%Y %h:%i:%s'\n",
    "               ) AS\n",
    "                      start_time,\n",
    "               Date_format(From_unixtime(end_time / 1000), '%m-%d-%Y %h:%i:%s')\n",
    "               AS\n",
    "                      end_time\n",
    "        FROM\n",
    "\"<Game-data-catalog>\".\"<Game-data-bucket>\") game \n",
    "WHERE  game.teama = '<Team-Name>'\n",
    "ORDER  BY end_time DESC\n",
    "```\n",
    "\n",
    "If you get an error saying there is no database or table in the database, then follow these instructions:\n",
    "1. Go to the [AWS Glue console](https://console.aws.amazon.com/glue/home)\n",
    "1. On the left hand side menu, select \"Crawlers\":\n",
    "<img src=\"./assets/glue-console-1.jpg\" width=100></img> \n",
    "1. Select the archive crawler and then run it\n",
    "<img src=\"./assets/glue-console-2.jpg\" width=500></img> \n",
    "1. When the crawler has finished, you can then run your query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next Steps\n",
    "Try editing hyperparamters or the code to get better results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}