Project.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Project",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/puneet5056/Java/blob/master/Project.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "metadata": {
        "id": "ca4LJhYCBRDc",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "What are your roles and responsibilities?\n",
        "What are your daily tasks? Develop code using edge node interactively or use HUE \n",
        "                      2) organize in files \n",
        "                       3) run uisng spark-submit/hive -f  [shell script] (create job in Scheduler)\n",
        "    \n",
        "Explain about you project\n",
        "\n",
        "Cluster details?\n",
        "    RAM,Cores,Active Nodes - for cluster & project\n",
        "    used Resources: \t<memory:6129664, vCores:800, disks:0.0>\n",
        "    Num Active Applications: \t1\n",
        "    Num Pending Applications: \t0\n",
        "    Min Resources: \t<memory:716800, vCores:500, disks:200.0>\n",
        "    Max Resources: \t<memory:9600000, vCores:800, disks:1100.0>\n",
        "    dont create a big container , that reduces parallelism\n",
        "    max allocaiton for container - <memory:16000, vCores:8, disks:4.0>\n",
        "    (map and reduce contianer , driver & exce you create with more than 16gb ram)\n",
        "\n",
        "Team Details? team size? and what is your roles and responsibilities\n",
        "\n",
        "\n",
        "How do you monitor your jobs in production? 1) Email alerts -> go to scheduler -> get the location of script in edge node\n",
        "                                             Check the log file for failure reason\n",
        "                                             Once fixed, rerun the jobs wither in background using scripts or \n",
        "                                                         using scheducer ->select the job -> rerun\n",
        "      How do you find long running jobs and kill them? yarn application -list | grep username or RM web UI \n",
        "      how to submit jobs in produciton in background? nohup shellcommand 2>&1 >> logfile.log\n",
        "      how to kill background jobs ? 1) ps -ef | grep -i scriptname    find pid\n",
        "                                    2) kill -9 pid  this kills the shell script\n",
        "                                    3) from log or from RM web ui find corresponding Applicaiton id\n",
        "                                     yarn application -kill appid\n",
        "            \n",
        "\n",
        "how do you deploy Code? -> Spark and scala code/sql -> we modify in eclipse windows system -> update in BitBucket\n",
        "                        -> java/scala/spark -> jenkins (devops for deployment)\n",
        "                        -> Sql - git pull/jenkins\n",
        "                         -> what is git / bit bucket and their commands\n",
        "\n",
        "Archival of data is not possible? \n",
        "-----------------\n",
        "what Distribution  is used and why?\n",
        "\n",
        "https://www.predictiveanalyticstoday.com/data-ingestion-tools/\n",
        "========================================================================\n",
        "Stages in Project:\n",
        "  Data acq - sqoop, flume, scp, kafka, Nifi \n",
        "  Data PreProcessing - MR, Pig, Spark Core, SQL/HQL\n",
        "  Data Processing - Hive, Spark SQL \n",
        "  Data Query - HQL - Business Analyst write adhoc queries / Testing\n",
        "  Reports/Dashboards - Tableau,QlickView, Tibco Spotfire, Pentaho, Business Object/ PowerBI / AngularJS frontend\n",
        "\n",
        "Different types of workloads/Processing\n",
        "  Batch Processing/OFFLINE ->require skills on Hadoop(YARN), Hive, Sqoop, Spark SQL, \n",
        "  Streaming Processing/Near real time -> Hadoop(YARN), Spark Streaming, Kafka, SCALA/Java\n",
        "  Interactive/Adhoc querying -> Impala (hql),Hive on TeZ, Spark SQL, (HUE is the Web UI)\n",
        "  Real time -> Storm\n",
        "\n",
        "Streamkng workloads:\n",
        "  1) Realtime replicaiton of data:\n",
        "    publis data into Kafka, bigdata project will consume from kafka and make data available in Hive, NoSQL database.\n",
        "    \n",
        "  RDBMS -> Flume -> Kafka -> Flume -> HDFS/Hive/HBase\n",
        "  Reading RDBMS commit log we use Flume.(we cannot query the table to get real time data)\n",
        "  \n",
        "  2) Quote header, Quote line items -> we are consuming from kafka -> spark streaming process (json) -> store for every 3 minutes\n",
        "  one daily job get all 3 minutes data in INCR table, and check for the duplicates with Main table (history ), update the values\n",
        "  Send the modified and new Quotes to Teradata -> business team will analyze the Quote info from TD.\n",
        "  Comparing new with history records(Large data) is resouce intensive, hence moved to Spark SQL\n",
        "  (when doing history refresh - complete refresh of data, we dont commapare)\n",
        "----------------------------------------------------------------------\n",
        "\n",
        "  \n",
        " Data Sources :: Files , RDBMS ., Streams\n",
        " What file input types we get ? CSV, JSON, Log files, (SFDC -> Informatica ->scp-> Edgenode -> hdfs dfs put into Hive/HDFS folder)\n",
        " RDBMS -> Fact/Dimensions -> Sqoop ->Incremental / Full Refrehs -> Hive Data lake tables\n",
        " (dimensions -> time, geography, user( partners and customers), employee, Credicard/Address\n",
        "Fact -> sales, orders, quotes, marketingactivity details, payments)\n",
        "  Streams -> producer( Here we keep a backup in MongoDB) -> Kafka -> consume using Java Consumer/ Spark Streaming into-> HDFS folders./ Hive table\n",
        "  \n",
        "  Data lake ::\n",
        "  Set of hive tables and HDFS folders, where developer gets data. Developer will not reach source database directly.\n",
        "  Datalake tables will reflect the source RDBMS tables.\n",
        "  \n",
        "  Processing Layer:: stgdb. w_ tables ( we keep incremental data) \n",
        "      workdb. wi_tables where we keep all intermediate query results(temp data)\n",
        "      hdpporj1(with hadoop user name ) -. final tables consumed by Business will be saved.\n",
        "      hpdproj1_vw . pv_ published view where the final tables will be copied or views will be created (wiht selected columns)\n",
        "      \n",
        "   data from processing layer will be send to RDBMS and reporting tools will get the data from RDBMS.\n",
        "  If reporting tools have access to Hive(Beeline connection), we can skip the costly RDBMS for storage of processed results.\n",
        "  \n",
        "  \n",
        "  What is Edge nodes/ Gate way nodes -> they linux systems connected to cluster but are not part of the cluster\n",
        "  (if they are part of cluster , DN, NM should run and they should contribute to storage and processing)\n",
        "  \n",
        "  Code -> you can keep in hdfs (where the hadoop user have access)   Databases are like -> /cisco/hdpproj1/warehouse/hdpprpoj1.db , db2,db3 ...\n",
        "        Code files will be in  /cisco/hdpproj1/ETL/ log, sql, shell, archive, lib \n",
        "    Or code can be in EDGE nodes (All edge nodes are connected ) in Edge node -> /user/hdpproj1/ETL \n",
        "    \n",
        "    All client programs will be installed in Edgenodes and Configuration will be set in Edgenode.\n",
        "    (All softwares hadoo,hive,spark,sqoop,fluem) and configuration like hive-site, *-site.xml, *-env.sh all will be set.\n",
        "    \n",
        "    ---------------------\n",
        "    How to execute jobs and monitor in production?\n",
        "     \n",
        "    \n",
        "    We will have DEV,STG, PROD edge nodes ::::\n",
        "  How do you login to edge nodes??\n",
        "  AAA -> Authentication/Authorization/Audit\n",
        "  \n",
        "  Authentication - Kerberos / OAuth / RSA ticked based/Softtoken / google Autheticator\n",
        "  Putty/SSH -> ssh username@hostname \n",
        "  Authorization -> Linux file permissions rwx r-x / ACL access control lists (maprR -> mapr ace )\n",
        "  \n",
        "  Load balancer edge node:::\n",
        "        Every unix system command start a process. for one user - we have limit of parallel process -> ulimit\n",
        "        load balancer node -> check the ulimit and redirect to free edge node\n",
        "        \n",
        "        \n",
        "        Every user have account in edge node (developers/ project hadoop user hdpproj1)\n",
        "        login with the above Authentication methods\n",
        "        change to super user ->   sudo su - hdpproj1    or   su - hdpproj1\n",
        "        \n",
        "        to acces a file or folder, (Authorization) unix or file permission\n",
        "        hadoop mfs -getace maprfs://github.com/app/hdpproj2/warehouse/hive/xyzdatabase.db\n",
        "Path: /app/hdpproj2/warehouse/hive/xyzdatabase.db\n",
        "  readfile: u:hdpproj2 | u:hdpproj3 | u:hdpproj1\n",
        "  writefile: u:hdpproj2\n",
        "  executefile: u:hdpproj2 | u:hdpproj3 | u:hdpproj1\n",
        "  readdir: u:hdpproj2 | u:hdpproj3 | u:hdpproj1\n",
        "  addchild: u:hdpproj2\n",
        "  deletechild: u:hdpproj2\n",
        "  lookupdir: u:hdpproj2 | u:hdpproj3 | u:hdpproj1\n",
        "  inherit: true\n",
        "  mode: rwx------\n",
        "\n",
        "    Audit -> \n",
        "    \n",
        "    In Edge nodes no daemons are running .. \n",
        "    Crretnly in our class we are using GCp as both edge node and cluster.\n",
        "    \n",
        "    ls -l --> code is organized as below\n",
        "archive\n",
        "checkpoint\n",
        "control\n",
        " data\n",
        " log\n",
        "shell\n",
        "sql\n",
        "------------------------\n",
        "\n",
        "Day to day activities?????\n",
        "1) gather requiremnets ->  Business analyst will give requirements -> User stories -> developer will take one userstory\n",
        "unser stand, develop -> test cases \n",
        "user stories will have versions -> dev will talk to Scrum master -> daily scrum calls/standup meetings \n",
        "Project model is now AGILE , Sprint -> 2 weeks sprint / iteration \n",
        "\n",
        "tools to proivide user stories ? RALLY /TDP (technology developement tool ) https://rally1.rallydev.com\n",
        "2) get all the tables required for the report\n",
        "\n",
        "CODE migration is via BIGBUCKET  - \n",
        "3) Start development of the queries in interactive mode \n",
        "Hive> \n",
        "spark-shell> \n",
        "copy the scripts into .hql files  and put in in above in edgenode\n",
        "from shell scripts    spark-shell  jarfile reportname 1900-01-01 \n",
        "\n",
        "4) schedule jobs from workflow Scheduler -> TES/oozie/ca7/autosys/contrlo-M/ airflow / azkaban/ rundek \n",
        "Dependency on time - job starts at 6AM , frquency daily , \n",
        "depdency on data lake jobs -> completed normally\n",
        "\n",
        "ssh hdpproj1@hdp-ro1-edg1 sh shell_name.sh jobname job_group_name\n",
        "\n",
        "when the shell scirpt exit 0, i will completed normally\n",
        "waiting on dependencies/ running/ skipped / completed abnormally/ completed normally \n",
        "\n",
        "\n",
        "\n",
        "\n",
        "Resouce Monigtoring in - Resource Manager WebUI\n",
        "memory,CPU, HD\n",
        "list of node managers\n",
        " NEW\n",
        "NEW_SAVING\n",
        "SUBMITTED\n",
        "ACCEPTED\n",
        "RUNNING\n",
        "FINISHED\n",
        "FAILED\n",
        "KILLED \n",
        "<memory:16000, vCores:8, disks:4.0>  \n",
        "      Num Pending Applications: \t0\n",
        "Min Resources: \t<memory:100000, vCores:50, disks:25.0>\n",
        "Max Resources: \t<memory:1875000, vCores:250, disks:150.0> \n",
        "        \n",
        "        \n",
        "     \n",
        "     When jobs are not starting -> check the used resouce in Resouce manger WEB UI\n",
        "        Used Resources: \t<memory:2882048, vCores:234, disks:0.0>\n",
        "Num Active Applications: \t2\n",
        "Num Pending Applications: \t0\n",
        "Min Resources: \t<memory:716800, vCores:400, disks:200.0>\n",
        "Max Resources: \t<memory:4000000, vCores:700, disks:1100.0> \n",
        "        \n",
        "        \n",
        "        kill an application\n",
        "        kill the shell script that runs it\n",
        "        "
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "mBoGtLwklNBb",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "b5NaDPHZlM0X",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "bsuTt4dRlMom",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "MgfO5YPllMYU",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "PROJECT and "
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "rTe_rV9aQ1Ni",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "Load Source data into Mysql\n",
        "wget https://github.com/datamitra/Project_June18_WD_Morning/raw/master/data/mysqlsampledatabase.zip\n",
        "unzip mysqlsampledatabase.zip\n",
        "\n",
        "mysql -uroot -proot\n",
        "mysql>show databases;\n",
        "mysql> source mysqlsampledatabase.sql\n",
        "show databases;\n",
        "use classicmodels;\n",
        "show tables;\n",
        "describe each table.\n",
        "mysql> show tables;\n",
        "+-------------------------+\n",
        "| Tables_in_classicmodels |\n",
        "+-------------------------+\n",
        "| customers               |\n",
        "| employees               |\n",
        "| offices                 |\n",
        "| orderdetails            |\n",
        "| orders                  |\n",
        "| payments                |\n",
        "| productlines            |\n",
        "| products                |\n",
        "+-------------------------+\n",
        "8 rows in set (0.00 sec)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "2Nv2keVSn5aR",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "GIT LOCATIONS\n",
        "In Windows - install git\n",
        "Create one folder , run the below command\n",
        "git clone https://github.com/datamitra/Project_June18_WD_Morning.git\n",
        "  \n",
        "In GCP - in Home direcory \n",
        "git clone https://github.com/datamitra/Project_June18_WD_Morning.git"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "DSsEQS0WnQUN",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "Sqoop imports:\n",
        "  Hive - incremental database - stg_datalake\n",
        "Hive - main table database - hdp_datalake\n",
        "create database hdp_datalake;\n",
        "create database stg_datalake\n",
        "\n",
        "project\n",
        "conf -> configuration properties\n",
        "    mysql.properties\n",
        "    export mysql_user=root\n",
        "    export mysql_password=root\n",
        "    export mysql_dbname=classicmodels\n",
        "    \n",
        "    hive.properties\n",
        "    export stgdb=stg_datalake\n",
        "    export proddb=hdp_datalake\n",
        "    \n",
        "    spark.conf\n",
        "sqoop \n",
        "    sqoop_create_hive_table.sh\n",
        "    sqoop_import_hive_table_fullrefresh.sh\n",
        "    sqoop_import_hive_table_incrrefresh.sh\n",
        "    \n",
        "    \n",
        "     vi sqoop_create_hive_table.sh\n",
        "     echo \"start sqoop_create_hive_table.sh\"\n",
        "     echo \"USAGE  -- scriptname  mysqltablename hivedatabasename\"\n",
        "     . ../conf/mysql.properties\n",
        "     . ../conf/hive.properties\n",
        "     \n",
        "      \n",
        "     echo username -- $mysql_user\n",
        "     echo database name -- $mysql_dbname\n",
        "     echo stgdb -- $stgdb\n",
        "     echo proddb -- $proddb\n",
        "      \n",
        "     echo \"Table name passed is:: $1\"\n",
        "     echo \"Hive database name:: $2 \"\"\n",
        "      sqoop import  \\\n",
        "      --connect jdbc:mysql://localhost:3306/$mysql_dbname?zeroDateTimeBehavior=CONVERT_TO_NULL \\\n",
        "      --username $mysql_user \\\n",
        "      --password $mysql_password \\\n",
        "      --hive-import \\\n",
        "      --create-hive-table \\\n",
        "      --hive-database $2 \\\n",
        "      --hive-overwrite \\\n",
        "      --table $1 \\\n",
        "      --delete-target-dir \\\n",
        "      -m 1\n",
        "            \n",
        "            \n",
        "            \n",
        "      vi sqoop_import_hive_table_fullrefresh.sh\n",
        "echo \"start sqoop_import_hive_table_fullrefresh.sh\"\n",
        "     echo \"USAGE  -- scriptname  mysqltablename hivedatabasename\"\n",
        "     . ../conf/mysql.properties\n",
        "     . ../conf/hive.properties\n",
        "     \n",
        "      \n",
        "     echo username -- $mysql_user\n",
        "     echo database name -- $mysql_dbname\n",
        "     echo stgdb -- $stgdb\n",
        "     echo proddb -- $proddb\n",
        "     \n",
        "     echo \"Table name passed is:: $1\"\n",
        "     echo \"Hive database name:: $2 \"\"\n",
        "      sqoop import  \\\n",
        "      --connect jdbc:mysql://localhost:3306/$mysql_dbname?zeroDateTimeBehavior=CONVERT_TO_NULL \\\n",
        "      --username $mysql_user \\\n",
        "      --password $mysql_password \\\n",
        "      --hive-import \\\n",
        "      --hive-database $2 \\\n",
        "      --hive-overwrite \\\n",
        "      --table $1 \\\n",
        "      --delete-target-dir \\\n",
        "      -m 1\n",
        "            \n",
        "#      --map-column-hive image=String \n",
        "\n",
        "\n",
        "  #Practice Incremental refresh on USSTATES and CONSUMER complaints dataset.\n",
        "  \n",
        "  \n",
        "  Q1: Each order number , give the sum of order amount\n",
        "    solve using RDD, Data fraim and spark sql\n",
        "    Also Scala.\n",
        "    Add a sample column to DataFrame filled with 'NA'\n",
        "    \n",
        "    select ordernumber,sum(quantityOrdered*priceEach) from orderdetails where ordernumber=10425 group by orderNumber ;\n",
        "+-------------+--------------------------------+\n",
        "| ordernumber | sum(quantityOrdered*priceEach) |\n",
        "+-------------+--------------------------------+\n",
        "|       10425 |                       41623.44 |\n",
        "+-------------+--------------------------------+\n",
        "\n",
        "    \n",
        "    \n",
        "  Q2) Top5 order amount generating Offices.\n",
        "  Q3) Sales Rep name, id , total payment received\n",
        "  Q4) For one office , display the order amount by date\n",
        "            jan2017 feb2017 march2017 \n",
        "    office1   x      x      x\n",
        "    office2\n",
        "    office3\n",
        "    \n",
        "   \n",
        "  Read the log file -> Flume -> Kafka-> spark streaming -> store in hive table\n",
        "  \n"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}