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

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hive Assignment 3. DML: Calculate Amount of Posts per User Age"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use Hive to complete the following task. Input tables was described in Hive Task1 and Hive Task2.\n",
    "\n",
    "Calculate number of questions and answers depending on users' age. Use аge from 'users' table, filter out users if their age is undefined. Output format:\n",
    "\n",
    "    age <tab> number of questions <tab> number of answers\n",
    "    \n",
    "Example:\n",
    "\n",
    "    22 12345 5678\n",
    "\n",
    "Output all ages. Order by age, increment.\n",
    "\n",
    "The part of the result on the sample dataset:\n",
    "\n",
    "    ...\n",
    "    21  11  24\n",
    "    22  6   18\n",
    "    23  12  15\n",
    "    24  16  27\n",
    "    25  20  33\n",
    "    ...\n",
    "\n",
    "\n",
    "Hint. To simplify your code and reduce the quantity of MapReduce jobs produced by the query, use IF clause.\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",
    "    users.age,\n",
    "    sum(if(post_type_id = 1, 1, 0)),\n",
    "    sum(if(post_type_id = 2, 1, 0))\n",
    "FROM \n",
    "    posts\n",
    "JOIN \n",
    "    users ON posts.owner_user_id = users.id\n",
    "WHERE \n",
    "    post_type_id IN (1,2) \n",
    "    AND users.age IS NOT NULL\n",
    "GROUP BY users.age\n",
    "ORDER BY users.age"
   ]
  },
  {
   "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.119 seconds\n",
      "Query ID = jovyan_20181113144545_62a4338d-277f-4da6-a01a-14c2947b8d9f\n",
      "Total jobs = 2\n",
      "Execution log at: /tmp/jovyan/jovyan_20181113144545_62a4338d-277f-4da6-a01a-14c2947b8d9f.log\n",
      "2018-11-13 02:45:16\tStarting to launch local task to process map join;\tmaximum memory = 477626368\n",
      "2018-11-13 02:45:19\tDump the side-table for tag: 1 with group count: 5951 into file: file:/tmp/jovyan/e61bf477-6d8d-4465-857a-1c45c0b59c68/hive_2018-11-13_14-45-07_749_269603870797727616-1/-local-10005/HashTable-Stage-2/MapJoin-mapfile01--.hashtable\n",
      "2018-11-13 02:45:19\tUploaded 1 File to: file:/tmp/jovyan/e61bf477-6d8d-4465-857a-1c45c0b59c68/hive_2018-11-13_14-45-07_749_269603870797727616-1/-local-10005/HashTable-Stage-2/MapJoin-mapfile01--.hashtable (137665 bytes)\n",
      "2018-11-13 02:45:19\tEnd of local task; Time Taken: 2.184 sec.\n",
      "Execution completed successfully\n",
      "MapredLocal task succeeded\n",
      "Launching Job 1 out of 2\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_0033, Tracking URL = http://c5bd5fec03a1:8088/proxy/application_1542041219823_0033/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1542041219823_0033\n",
      "Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1\n",
      "2018-11-13 14:45:33,799 Stage-2 map = 0%,  reduce = 0%\n",
      "2018-11-13 14:45:44,702 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 9.36 sec\n",
      "2018-11-13 14:45:53,446 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 14.06 sec\n",
      "MapReduce Total cumulative CPU time: 14 seconds 60 msec\n",
      "Ended Job = job_1542041219823_0033\n",
      "Launching Job 2 out of 2\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_0034, Tracking URL = http://c5bd5fec03a1:8088/proxy/application_1542041219823_0034/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1542041219823_0034\n",
      "Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 1\n",
      "2018-11-13 14:46:10,762 Stage-3 map = 0%,  reduce = 0%\n",
      "2018-11-13 14:46:18,440 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 2.41 sec\n",
      "2018-11-13 14:46:25,959 Stage-3 map = 100%,  reduce = 100%, Cumulative CPU 5.53 sec\n",
      "MapReduce Total cumulative CPU time: 5 seconds 530 msec\n",
      "Ended Job = job_1542041219823_0034\n",
      "MapReduce Jobs Launched: \n",
      "Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 14.06 sec   HDFS Read: 2261830 HDFS Write: 1056 SUCCESS\n",
      "Stage-Stage-3: Map: 1  Reduce: 1   Cumulative CPU: 5.53 sec   HDFS Read: 5621 HDFS Write: 376 SUCCESS\n",
      "Total MapReduce CPU Time Spent: 19 seconds 590 msec\n",
      "OK\n",
      "14\t1\t0\n",
      "15\t1\t2\n",
      "16\t2\t0\n",
      "17\t0\t1\n",
      "18\t4\t1\n",
      "19\t1\t1\n",
      "20\t0\t2\n",
      "21\t11\t24\n",
      "22\t6\t18\n",
      "23\t12\t15\n",
      "24\t16\t27\n",
      "25\t20\t33\n",
      "26\t23\t44\n",
      "27\t28\t56\n",
      "28\t24\t37\n",
      "29\t24\t66\n",
      "30\t26\t67\n",
      "31\t17\t33\n",
      "32\t13\t48\n",
      "33\t11\t40\n",
      "34\t24\t36\n",
      "35\t12\t42\n",
      "36\t8\t64\n",
      "37\t9\t35\n",
      "38\t6\t17\n",
      "39\t3\t7\n",
      "40\t1\t13\n",
      "41\t5\t20\n",
      "42\t5\t22\n",
      "43\t2\t26\n",
      "44\t7\t35\n",
      "45\t1\t4\n",
      "46\t7\t9\n",
      "47\t1\t1\n",
      "48\t1\t1\n",
      "49\t1\t26\n",
      "50\t1\t26\n",
      "51\t4\t5\n",
      "52\t0\t2\n",
      "53\t0\t2\n",
      "54\t0\t1\n",
      "57\t0\t3\n",
      "58\t1\t57\n",
      "60\t0\t6\n",
      "61\t0\t3\n",
      "64\t2\t1\n",
      "86\t0\t1\n",
      "96\t3\t1\n",
      "Time taken: 80.423 seconds, Fetched: 48 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
}