2 Big data analysis/Homeworks/Week 2/3 Num of questions and answers.ipynb

{
 "cells": [
  {
   "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",
    "\n",
    "SELECT concat_ws(\"\\t\", string(age), string(NumQuesions), string(NumAnswers))\n",
    "FROM (\n",
    "    SELECT\n",
    "        age\n",
    "        ,COUNT(CASE WHEN post_type_id=1 THEN t1.id ELSE NULL END) AS NumQuesions\n",
    "        ,COUNT(CASE WHEN post_type_id=2 THEN t1.id ELSE NULL END) AS NumAnswers\n",
    "    FROM (\n",
    "        SELECT id, owner_user_id, post_type_id\n",
    "        FROM posts\n",
    "        WHERE post_type_id IN (1, 2)\n",
    "    ) t1\n",
    "    JOIN users\n",
    "    ON t1.owner_user_id = users.id\n",
    "    WHERE age IS NOT NULL\n",
    "    GROUP BY age\n",
    "    ORDER BY age\n",
    ") t2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "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.238 seconds\n",
      "Query ID = jovyan_20180527105757_734e1090-0381-43c7-9201-6ceba01a5858\n",
      "Total jobs = 2\n",
      "Execution log at: /tmp/jovyan/jovyan_20180527105757_734e1090-0381-43c7-9201-6ceba01a5858.log\n",
      "2018-05-27 10:58:00\tStarting to launch local task to process map join;\tmaximum memory = 477626368\n",
      "2018-05-27 10:58:02\tDump the side-table for tag: 1 with group count: 5951 into file: file:/tmp/jovyan/1017063a-94ae-4743-b3ca-ec75d34c1f49/hive_2018-05-27_10-57-49_328_1546389887170275430-1/-local-10005/HashTable-Stage-2/MapJoin-mapfile01--.hashtable\n",
      "2018-05-27 10:58:02\tUploaded 1 File to: file:/tmp/jovyan/1017063a-94ae-4743-b3ca-ec75d34c1f49/hive_2018-05-27_10-57-49_328_1546389887170275430-1/-local-10005/HashTable-Stage-2/MapJoin-mapfile01--.hashtable (137665 bytes)\n",
      "2018-05-27 10:58:02\tEnd of local task; Time Taken: 2.633 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_1527414825805_0019, Tracking URL = http://c9bfde1d3c8a:8088/proxy/application_1527414825805_0019/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1527414825805_0019\n",
      "Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1\n",
      "2018-05-27 10:58:21,164 Stage-2 map = 0%,  reduce = 0%\n",
      "2018-05-27 10:58:33,968 Stage-2 map = 100%,  reduce = 0%, Cumulative CPU 9.67 sec\n",
      "2018-05-27 10:58:44,084 Stage-2 map = 100%,  reduce = 100%, Cumulative CPU 14.05 sec\n",
      "MapReduce Total cumulative CPU time: 14 seconds 50 msec\n",
      "Ended Job = job_1527414825805_0019\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_1527414825805_0020, Tracking URL = http://c9bfde1d3c8a:8088/proxy/application_1527414825805_0020/\n",
      "Kill Command = /opt/hadoop/bin/hadoop job  -kill job_1527414825805_0020\n",
      "Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 1\n",
      "2018-05-27 10:59:04,051 Stage-3 map = 0%,  reduce = 0%\n",
      "2018-05-27 10:59:12,828 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 2.35 sec\n",
      "2018-05-27 10:59:23,866 Stage-3 map = 100%,  reduce = 100%, Cumulative CPU 7.34 sec\n",
      "MapReduce Total cumulative CPU time: 7 seconds 340 msec\n",
      "Ended Job = job_1527414825805_0020\n",
      "MapReduce Jobs Launched: \n",
      "Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 14.05 sec   HDFS Read: 3741352 HDFS Write: 1056 SUCCESS\n",
      "Stage-Stage-3: Map: 1  Reduce: 1   Cumulative CPU: 7.34 sec   HDFS Read: 5902 HDFS Write: 376 SUCCESS\n",
      "Total MapReduce CPU Time Spent: 21 seconds 390 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: 96.825 seconds, Fetched: 48 row(s)\n"
     ]
    }
   ],
   "source": [
    "! hive -f query.hql"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}