course2-big-data-analysis/c02w02-Hive_Assignment_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hive Assignment 2. DML: Find Most Popular Tags"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compare most popular tags in year 2016 with tags in 2009. Tag popularity is the number of questions (post_type_id=1) with this tag. Output top 10 tags in format:\n",
    "\n",
    "    tag <tab> rank in 2016 <tab> rank in 2009 <tab> tag popularity in 2016 <tab> tag popularity in 2009\n",
    "    \n",
    "Example:\n",
    "\n",
    "  \n",
    "    php 5 3 1234 5678\n",
    "    \n",
    "For 'rank' in each year use rank() function. Order by 'rank in 2016'.\n",
    "\n",
    "The part of the result on the sample dataset:\n",
    "\n",
    "    ...\n",
    "    android 3   52  1809    25\n",
    "    php 4   3   1673    215\n",
    "    python  5   11  1585    108\n",
    "    c#  6   1   1519    423\n",
    "    ...\n",
    "\n",
    "Please use the tables 'posts' and 'users' from the database 'stackoverflow_'. 'posts' is partitioned by year and by month. For more details see \"Hive assignment. Intro and instructions\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting query.hql\n"
     ]
    }
   ],
   "source": [
    "%%writefile query.hql\n",
    "\n",
    "USE stackoverflow_;\n",
    "\n",
    "SELECT\n",
    "    tag,\n",
    "    rank() OVER (ORDER BY count2016 DESC) as rank2016,\n",
    "    rank() OVER (ORDER BY count2009 DESC) as rank2009,\n",
    "    count2016,\n",
    "    count2009\n",
    "FROM(\n",
    "    SELECT\n",
    "        tag,\n",
    "        sum(if(year = '2009', 1, 0)) as count2009,\n",
    "        sum(if(year = '2016', 1, 0)) as count2016\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 rank2016\n",
    "LIMIT 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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.167 seconds\n",
      "Query ID = jovyan_20181113143535_8924e4ad-6c73-4b05-82c3-af4315b87f7d\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_1542041219823_0029, Tracking URL = http://c5bd5fec03a1:8088/proxy/application_1542041219823_0029/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1542041219823_0029\n",
      "Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1\n",
      "2018-11-13 14:35:28,233 Stage-1 map = 0%,  reduce = 0%\n",
      "2018-11-13 14:35:37,130 Stage-1 map = 100%,  reduce = 0%\n",
      "2018-11-13 14:35:47,006 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 14.16 sec\n",
      "MapReduce Total cumulative CPU time: 14 seconds 160 msec\n",
      "Ended Job = job_1542041219823_0029\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_1542041219823_0030, Tracking URL = http://c5bd5fec03a1:8088/proxy/application_1542041219823_0030/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1542041219823_0030\n",
      "Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1\n",
      "2018-11-13 14:36:04,493 Stage-2 map = 0%,  reduce = 0%\n",
      "2018-11-13 14:36:13,170 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 4.87 sec\n",
      "2018-11-13 14:36:22,875 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 11.51 sec\n",
      "MapReduce Total cumulative CPU time: 11 seconds 510 msec\n",
      "Ended Job = job_1542041219823_0030\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_1542041219823_0031, Tracking URL = http://c5bd5fec03a1:8088/proxy/application_1542041219823_0031/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1542041219823_0031\n",
      "Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 1\n",
      "2018-11-13 14:36:39,305 Stage-3 map = 0%,  reduce = 0%\n",
      "2018-11-13 14:36:47,927 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 3.88 sec\n",
      "2018-11-13 14:36:56,576 Stage-3 map = 100%,  reduce = 100%, Cumulative CPU 9.83 sec\n",
      "MapReduce Total cumulative CPU time: 9 seconds 830 msec\n",
      "Ended Job = job_1542041219823_0031\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_1542041219823_0032, Tracking URL = http://c5bd5fec03a1:8088/proxy/application_1542041219823_0032/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1542041219823_0032\n",
      "Hadoop job information for Stage-4: number of mappers: 1; number of reducers: 1\n",
      "2018-11-13 14:37:13,881 Stage-4 map = 0%,  reduce = 0%\n",
      "2018-11-13 14:37:22,510 Stage-4 map = 100%,  reduce = 0%, Cumulative CPU 4.9 sec\n",
      "2018-11-13 14:37:31,037 Stage-4 map = 100%,  reduce = 100%, Cumulative CPU 8.9 sec\n",
      "MapReduce Total cumulative CPU time: 8 seconds 900 msec\n",
      "Ended Job = job_1542041219823_0032\n",
      "MapReduce Jobs Launched: \n",
      "Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 14.16 sec   HDFS Read: 970658 HDFS Write: 316407 SUCCESS\n",
      "Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 11.51 sec   HDFS Read: 322322 HDFS Write: 347254 SUCCESS\n",
      "Stage-Stage-3: Map: 1  Reduce: 1   Cumulative CPU: 9.83 sec   HDFS Read: 353619 HDFS Write: 378100 SUCCESS\n",
      "Stage-Stage-4: Map: 1  Reduce: 1   Cumulative CPU: 8.9 sec   HDFS Read: 383265 HDFS Write: 188 SUCCESS\n",
      "Total MapReduce CPU Time Spent: 44 seconds 400 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.406 seconds, Fetched: 10 row(s)\n"
     ]
    }
   ],
   "source": [
    "!hive -f query.hql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "default_view": {},
   "name": "901_to_students.ipynb",
   "provenance": [],
   "toc_visible": true,
   "version": "0.3.2",
   "views": {}
  },
  "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}