Week2-Hive-Assignment-2-DML:-Find-Most-Popular-Tags-submission.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If I believe what I read on forums, we should prefer a solution with only one subquery and sum(if())..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting assignment_reply_sumif.hql\n"
     ]
    }
   ],
   "source": [
    "%%writefile assignment_reply_sumif.hql\n",
    "\n",
    "USE stackoverflow_;\n",
    "\n",
    "SELECT\n",
    "    tag,\n",
    "    rank() OVER (ORDER BY total_2016 DESC) as rang_2016,\n",
    "    rank() OVER (ORDER BY total_2009 DESC) as rang_2009,\n",
    "    total_2016,\n",
    "    total_2009\n",
    "FROM(\n",
    "    SELECT\n",
    "        tag,\n",
    "        sum(if(year = '2009', 1, 0)) as total_2009,\n",
    "        sum(if(year = '2016', 1, 0)) as total_2016\n",
    "    FROM posts\n",
    "    LATERAL VIEW explode(tags) tag as tag\n",
    "    WHERE\n",
    "        year in ('2016', '2009')\n",
    "        AND post_type_id=1\n",
    "    GROUP BY tag\n",
    ") as tmp\n",
    "ORDER BY rang_2016;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Logging initialized using configuration in jar:file:/usr/local/apache-hive-1.1.0-bin/lib/hive-common-1.1.0.jar!/hive-log4j.properties\n",
      "OK\n",
      "Time taken: 1.132 seconds\n",
      "Query ID = jovyan_20190515183737_951c564b-ff4d-474d-bbbd-a3844f5f9905\n",
      "Total jobs = 4\n",
      "Launching Job 1 out of 4\n",
      "Number of reduce tasks not specified. Estimated from input data size: 1\n",
      "In order to change the average load for a reducer (in bytes):\n",
      "  set hive.exec.reducers.bytes.per.reducer=<number>\n",
      "In order to limit the maximum number of reducers:\n",
      "  set hive.exec.reducers.max=<number>\n",
      "In order to set a constant number of reducers:\n",
      "  set mapreduce.job.reduces=<number>\n",
      "Starting Job = job_1557847019885_0037, Tracking URL = http://b1dfc078ce9b:8088/proxy/application_1557847019885_0037/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1557847019885_0037\n",
      "Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1\n",
      "2019-05-15 18:37:52,342 Stage-1 map = 0%,  reduce = 0%\n",
      "2019-05-15 18:38:02,414 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 9.91 sec\n",
      "2019-05-15 18:38:11,234 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 15.84 sec\n",
      "MapReduce Total cumulative CPU time: 15 seconds 840 msec\n",
      "Ended Job = job_1557847019885_0037\n",
      "Launching Job 2 out of 4\n",
      "Number of reduce tasks not specified. Estimated from input data size: 1\n",
      "In order to change the average load for a reducer (in bytes):\n",
      "  set hive.exec.reducers.bytes.per.reducer=<number>\n",
      "In order to limit the maximum number of reducers:\n",
      "  set hive.exec.reducers.max=<number>\n",
      "In order to set a constant number of reducers:\n",
      "  set mapreduce.job.reduces=<number>\n",
      "Starting Job = job_1557847019885_0038, Tracking URL = http://b1dfc078ce9b:8088/proxy/application_1557847019885_0038/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1557847019885_0038\n",
      "Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1\n",
      "2019-05-15 18:38:29,271 Stage-2 map = 0%,  reduce = 0%\n",
      "2019-05-15 18:38:37,933 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 4.77 sec\n",
      "2019-05-15 18:38:46,654 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 10.59 sec\n",
      "MapReduce Total cumulative CPU time: 10 seconds 590 msec\n",
      "Ended Job = job_1557847019885_0038\n",
      "Launching Job 3 out of 4\n",
      "Number of reduce tasks not specified. Estimated from input data size: 1\n",
      "In order to change the average load for a reducer (in bytes):\n",
      "  set hive.exec.reducers.bytes.per.reducer=<number>\n",
      "In order to limit the maximum number of reducers:\n",
      "  set hive.exec.reducers.max=<number>\n",
      "In order to set a constant number of reducers:\n",
      "  set mapreduce.job.reduces=<number>\n",
      "Starting Job = job_1557847019885_0039, Tracking URL = http://b1dfc078ce9b:8088/proxy/application_1557847019885_0039/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1557847019885_0039\n",
      "Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 1\n",
      "2019-05-15 18:39:04,254 Stage-3 map = 0%,  reduce = 0%\n",
      "2019-05-15 18:39:12,869 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 3.93 sec\n",
      "2019-05-15 18:39:21,506 Stage-3 map = 100%,  reduce = 100%, Cumulative CPU 10.72 sec\n",
      "MapReduce Total cumulative CPU time: 10 seconds 720 msec\n",
      "Ended Job = job_1557847019885_0039\n",
      "Launching Job 4 out of 4\n",
      "Number of reduce tasks determined at compile time: 1\n",
      "In order to change the average load for a reducer (in bytes):\n",
      "  set hive.exec.reducers.bytes.per.reducer=<number>\n",
      "In order to limit the maximum number of reducers:\n",
      "  set hive.exec.reducers.max=<number>\n",
      "In order to set a constant number of reducers:\n",
      "  set mapreduce.job.reduces=<number>\n",
      "Starting Job = job_1557847019885_0040, Tracking URL = http://b1dfc078ce9b:8088/proxy/application_1557847019885_0040/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1557847019885_0040\n",
      "Hadoop job information for Stage-4: number of mappers: 1; number of reducers: 1\n",
      "2019-05-15 18:39:39,202 Stage-4 map = 0%,  reduce = 0%\n",
      "2019-05-15 18:39:47,800 Stage-4 map = 100%,  reduce = 0%, Cumulative CPU 4.97 sec\n",
      "2019-05-15 18:39:56,371 Stage-4 map = 100%,  reduce = 100%, Cumulative CPU 8.85 sec\n",
      "MapReduce Total cumulative CPU time: 8 seconds 850 msec\n",
      "Ended Job = job_1557847019885_0040\n",
      "MapReduce Jobs Launched: \n",
      "Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 15.84 sec   HDFS Read: 970658 HDFS Write: 316407 SUCCESS\n",
      "Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 10.59 sec   HDFS Read: 322322 HDFS Write: 347254 SUCCESS\n",
      "Stage-Stage-3: Map: 1  Reduce: 1   Cumulative CPU: 10.72 sec   HDFS Read: 353619 HDFS Write: 378100 SUCCESS\n",
      "Stage-Stage-4: Map: 1  Reduce: 1   Cumulative CPU: 8.85 sec   HDFS Read: 383265 HDFS Write: 188 SUCCESS\n",
      "Total MapReduce CPU Time Spent: 46 seconds 0 msec\n",
      "OK\n",
      "javascript\t1\t5\t2771\t192\n",
      "java\t2\t2\t2033\t243\n",
      "android\t3\t52\t1809\t25\n",
      "php\t4\t3\t1673\t215\n",
      "python\t5\t11\t1585\t108\n",
      "c#\t6\t1\t1519\t423\n",
      "html\t7\t14\t1212\t84\n",
      "jquery\t8\t8\t1167\t141\n",
      "ios\t9\t186\t914\t7\n",
      "css\t10\t20\t801\t59\n",
      "Time taken: 142.111 seconds, Fetched: 10 row(s)\n"
     ]
    }
   ],
   "source": [
    "!hive -f assignment_reply_sumif.hql"
   ]
  }
 ],
 "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}