Week2_Hive-Assignment-3-DML_-Calculate-Amount-of-Posts-per-User-Age.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing assigment_reply.hql\n"
     ]
    }
   ],
   "source": [
    "%%writefile assigment_reply.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.11 seconds\n",
      "Query ID = jovyan_20190515190707_d0f762d1-988a-4730-ac1e-7a9f453a96c3\n",
      "Total jobs = 2\n",
      "Execution log at: /tmp/jovyan/jovyan_20190515190707_d0f762d1-988a-4730-ac1e-7a9f453a96c3.log\n",
      "2019-05-15 07:07:09\tStarting to launch local task to process map join;\tmaximum memory = 477626368\n",
      "2019-05-15 07:07:11\tDump the side-table for tag: 1 with group count: 5951 into file: file:/tmp/jovyan/159d7375-af8f-4ba5-869c-7dea7189f07d/hive_2019-05-15_19-07-00_042_6919854244033256903-1/-local-10005/HashTable-Stage-2/MapJoin-mapfile01--.hashtable\n",
      "2019-05-15 07:07:11\tUploaded 1 File to: file:/tmp/jovyan/159d7375-af8f-4ba5-869c-7dea7189f07d/hive_2019-05-15_19-07-00_042_6919854244033256903-1/-local-10005/HashTable-Stage-2/MapJoin-mapfile01--.hashtable (137665 bytes)\n",
      "2019-05-15 07:07:11\tEnd of local task; Time Taken: 2.125 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_1557847019885_0045, Tracking URL = http://b1dfc078ce9b:8088/proxy/application_1557847019885_0045/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1557847019885_0045\n",
      "Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1\n",
      "2019-05-15 19:07:26,616 Stage-2 map = 0%,  reduce = 0%\n",
      "2019-05-15 19:07:37,686 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 9.06 sec\n",
      "2019-05-15 19:07:46,370 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 12.42 sec\n",
      "MapReduce Total cumulative CPU time: 12 seconds 420 msec\n",
      "Ended Job = job_1557847019885_0045\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_1557847019885_0046, Tracking URL = http://b1dfc078ce9b:8088/proxy/application_1557847019885_0046/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1557847019885_0046\n",
      "Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 1\n",
      "2019-05-15 19:08:04,085 Stage-3 map = 0%,  reduce = 0%\n",
      "2019-05-15 19:08:11,750 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 2.53 sec\n",
      "2019-05-15 19:08:19,269 Stage-3 map = 100%,  reduce = 100%, Cumulative CPU 5.6 sec\n",
      "MapReduce Total cumulative CPU time: 5 seconds 600 msec\n",
      "Ended Job = job_1557847019885_0046\n",
      "MapReduce Jobs Launched: \n",
      "Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 12.42 sec   HDFS Read: 2261833 HDFS Write: 1056 SUCCESS\n",
      "Stage-Stage-3: Map: 1  Reduce: 1   Cumulative CPU: 5.6 sec   HDFS Read: 5627 HDFS Write: 376 SUCCESS\n",
      "Total MapReduce CPU Time Spent: 18 seconds 20 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.4 seconds, Fetched: 48 row(s)\n"
     ]
    }
   ],
   "source": [
    "!hive -f assigment_reply.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
}