ScalaConn 中使用SPARK 启用hive 代码修改

package DAO;


import java.sql._
import java.text.SimpleDateFormat
import java.util.{List, Date, HashMap, Properties}
import com.google.protobuf.TextFormat.ParseException
import org.apache.hadoop.conf.Configuration
import org.apache.hadoop.hbase.{TableName, HBaseConfiguration}
import org.apache.hadoop.hbase.client._
import org.apache.hadoop.hbase.filter.PageFilter
import org.apache.hadoop.hbase.util.Bytes
import org.apache.hadoop.mapred.JobConf
import org.apache.spark.sql.{Row, DataFrame, SQLContext}
import org.apache.spark.{SparkContext, SparkConf}
import org.apache.spark.sql.SparkSession
import org.apache.hadoop.hbase.client.{Table, Connection}
import java.text.SimpleDateFormat;
import org.apache.hadoop.hbase.io.ImmutableBytesWritable
import org.apache.hadoop.hbase.mapreduce.TableInputFormat
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import org.apache.spark.sql.catalyst.encoders.ExpressionEncoder
import org.apache.spark.sql.Encoder
import org.apache.spark.rdd.RDD
import org.apache.hadoop.hbase.mapred.TableOutputFormat
import org.slf4j.LoggerFactory
import java.io.FileInputStream

object ScalaConn {
  def LOG = LoggerFactory.getLogger(getClass)
  /**
   * 从配置文件properties中获取初始化参数
   */
  def initParams(): Unit = {
    try {
      val prop = new Properties()
//      val path = Thread.currentThread().getContextClassLoader.getResource("params.properties").getPath
//      prop.load(new FileInputStream(path))

      prop.load(ScalaConn.getClass.getResourceAsStream("/params.properties"))
      Constant.P2P_DB_URL = prop.getProperty("p2p.url")
      Constant.P2P_DB_USER = prop.getProperty("p2p.username")
      Constant.P2P_DB_PWD = prop.getProperty("p2p.password")

      Constant.HAD_DB_URL = prop.getProperty("hadoop.url")
      Constant.HAD_DB_USER = prop.getProperty("hadoop.username")
      Constant.HAD_DB_PWD = prop.getProperty("hadoop.password")

      Constant.FTOU_DB_URL = prop.getProperty("ftoulanaly.url")
      Constant.FTOU_DB_USER = prop.getProperty("ftoulanaly.username")
      Constant.FTOU_DB_PWD = prop.getProperty("ftoulanaly.password")

      Constant.CRAW_DB_URL = prop.getProperty("ftcrawler.url")
      Constant.CRAW_DB_USER = prop.getProperty("ftcrawler.username")
      Constant.CRAW_DB_PWD = prop.getProperty("ftcrawler.password")

      Constant.DEDE_DB_URL = prop.getProperty("dedetest.url")
      Constant.DEDE_DB_USER = prop.getProperty("dedetest.username")
      Constant.DEDE_DB_PWD = prop.getProperty("dedetest.password")

      Constant.SHOP_DB_URL = prop.getProperty("ftoul_shop.url")
      Constant.SHOP_DB_USER = prop.getProperty("ftoul_shop.username")
      Constant.SHOP_DB_PWD = prop.getProperty("ftoul_shop.password")

      Constant.HBASE_ZOOKEEPER = prop.getProperty("hbase.zookeeper.quorum")
      Constant.HBASE_MASTER = prop.getProperty("hbase.master")
      Constant.HBASE_ROOTDIR = prop.getProperty("hbase.rootdir")

      Constant.SPARK_WAREHOUSE = prop.getProperty("spark.sql.warehouse.dir")
      Constant.HIVE_METASTORE_URIS = prop.getProperty("hive.metastore.uris")
      Constant.FS_DEFAULTFS = prop.getProperty("fs.defaultFS")
      Constant.DFS_NAMESERVICES = prop.getProperty("dfs.nameservices")
      Constant.DFS_HA_NAMENODES_CLUSTER = prop.getProperty("dfs.ha.namenodes.cluster")
      Constant.DFS_NAMENODE_RPC_1 = prop.getProperty("dfs.namenode.rpc-address.cluster.nn1")
      Constant.DFS_NAMENODE_RPC_2 = prop.getProperty("dfs.namenode.rpc-address.cluster.nn2")


    } catch {
      case e: Exception =>
        e.printStackTrace()
    }
  }

  def getHbaseConf: Configuration = {
    val conf: Configuration = HBaseConfiguration.create
    conf.set("hbase.zookeeper.property.clientPort", "2181")
    conf.set("spark.executor.memory", "3000m")
    conf.set("hbase.zookeeper.quorum", Constant.HBASE_ZOOKEEPER)
    conf.set("hbase.master", Constant.HBASE_MASTER)
    conf.set("hbase.rootdir", Constant.HBASE_ROOTDIR)
    conf
  }


//  sparkConf.set("es.nodes", Constant.ES_ADDRESS)

  val driver = "com.mysql.jdbc.Driver"
//  val jdbcUrl = "jdbc:mysql://192.168.100.233:3306/sp2p628"
//  val MYSQL_USERNAME: String = "root"
//  val MYSQL_PWD: String = "dzwang**"
//  val MYSQL_CONNECTION_URL: String = "jdbc:mysql://192.168.100.233:3306/sp2p628"

val warehouseLocation = "file:${system:user.dir}/spark-warehouse"
//val spark = SparkSession
//  .builder()
//  .master("local[*]") //spark://192.168.100.110:7077
//  .appName("SparkSQL")
//  .config("spark.sql.warehouse.dir", "file:///root/project/dcproject/spark-warehouse")
//  .getOrCreate()
local "local[*]"

val spark = SparkSession
  .builder()
  .master("local[*]")
  .appName("SparkHive")
  .config("spark.sql.warehouse.dir","file:///root/project/dcproject/spark-warehouse")
  .config("hive.metastore.uris","thrift://sy-003.hadoop:9083")
  .config("fs.defaultFS", "hdfs://cluster")
  .config("dfs.nameservices","cluster")
  .config("dfs.ha.namenodes.cluster","nn1,nn2")
  .config("dfs.namenode.rpc-address.cluster.nn1","sy-002.hadoop:8020")
  .config("dfs.namenode.rpc-address.cluster.nn2","sy-003.hadoop:8020")
  .config("dfs.client.failover.proxy.provider.cluster","org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider")
  .enableHiveSupport()
  .getOrCreate()

  def getJdbcConn {
//    var connection: Connection = null
    try {

      Class.forName(driver)
//      connection = DriverManager.getConnection(jdbcUrl, MYSQL_USERNAME, MYSQL_PWD)
//      val statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
      // val resultSet = statement.executeQuery("select name, password from scala_t")
      //  while (resultSet.next()) {
      //         val name = resultSet.getString("name")
      //        val password = resultSet.getString("password")
      // }
      //  return statement;
    } catch {
      case e: Exception => {
        LOG.error(e.toString)
        }
    }
  }
    def getP2PDataFrame(tableName: String): DataFrame =
  {

    val property = new Properties()
   val url =  Constant.P2P_DB_URL
    property.put("user",Constant.P2P_DB_USER)
    property.put("password", Constant.P2P_DB_PWD)
    val jdbcDF = spark.read.jdbc(url,tableName, property)
     jdbcDF
  }

  def getHadoopDataFrame(tableName: String): DataFrame =
  {
        val property = new Properties()
        val url = Constant.HAD_DB_URL
        property.put("user",Constant.HAD_DB_USER)
        property.put("password", Constant.HAD_DB_PWD)
        property.put("useSSL","false")
        val jdbcDF = spark.read.jdbc(url,tableName, property)
       jdbcDF
  }

  def getHadoopFtoulanalyDataFrame(tableName: String): DataFrame =
  {
    val property = new Properties()
    val url = Constant.FTOU_DB_URL
    property.put("user",Constant.FTOU_DB_USER)
    property.put("password", Constant.FTOU_DB_PWD)
    val jdbcDF = spark.read.jdbc(url,tableName, property)
    jdbcDF
  }
  def getHadoopFtcrawlerDataFrame(tableName: String): DataFrame =
  {
    val property = new Properties()
    val url = Constant.CRAW_DB_URL
    property.put("user",Constant.CRAW_DB_USER)
    property.put("password", Constant.CRAW_DB_PWD)
    val jdbcDF = spark.read.jdbc(url,tableName, property)
    jdbcDF
  }
  def getHadoopDedetestDataFrame(tableName: String): DataFrame =
  {
    val property = new Properties()
    val url = Constant.DEDE_DB_URL
    property.put("user", Constant.DEDE_DB_USER)
    property.put("password", Constant.DEDE_DB_PWD)
    val jdbcDF = spark.read.jdbc(url,tableName, property)
    jdbcDF
  }

  def getShopDataFrame(tableName: String): DataFrame =
  {
    val property = new Properties()
    val url = Constant.SHOP_DB_URL
    property.put("user",Constant.SHOP_DB_USER)
    property.put("password", Constant.SHOP_DB_PWD)
    val jdbcDF = spark.read.jdbc(url,tableName, property)
    jdbcDF
  }
//  def getHaseDataFrame(tableName: String):DataFrame=
//  {
//    getHbaseConf.set(TableInputFormat.INPUT_TABLE,tableName)
//    import spark.implicits._
//    val hbaseRDD = spark.sparkContext.newAPIHadoopRDD(getHbaseConf,classOf[TableInputFormat],classOf[ImmutableBytesWritable],classOf[Result])
//    val HbaseDF = hbaseRDD.map(r=>(
//      Bytes.toString(r._2.getValue(Bytes.toBytes("info"),Bytes.toBytes("customer_id"))),
//      Bytes.toString(r._2.getValue(Bytes.toBytes("info"),Bytes.toBytes("create_id")))
//      )).toDF("customer_id","create_id")
//    shop.createOrReplaceTempView("shop")
//    HbaseDF
//  }

  /**
   * 获得指定日期的前一天
   */
  def getSpecifiedDayBefore(specifiedDay:String):String = {
    //SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    var c:Calendar = Calendar.getInstance();
    var date:Date =null;
    try {
      date = new SimpleDateFormat("yyyyMMdd").parse(specifiedDay)
    }  catch {
      case e: Exception => {
        e.printStackTrace()
      }
    }
    c.setTime(date);
     var day =c.get(Calendar.DATE);
    c.set(Calendar.DATE,day-1);

   var dayBefore:String =new SimpleDateFormat("yyyyMMdd").format(c.getTime());
     dayBefore
  }

  /**
   * 获得指定日期的后一天
   */
  def getSpecifiedDayAfter(specifiedDay:String):String = {
    //SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    var c:Calendar = Calendar.getInstance();
    var date:Date =null;
    try {
      date = new SimpleDateFormat("yyyyMMdd").parse(specifiedDay)
    }  catch {
      case e: Exception => {
        e.printStackTrace()
      }
    }
    c.setTime(date);
    var day =c.get(Calendar.DATE);
    c.set(Calendar.DATE,day+1);

    var dayBefore:String =new SimpleDateFormat("yyyyMMdd").format(c.getTime());
    dayBefore
  }



    def GetStampByTime(time: String): Timestamp = {
      var Stamp: Timestamp = new Timestamp(System.currentTimeMillis)
      val sdf: SimpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
      var date: Date = null
      try {
        date = sdf.parse(time)
        Stamp = new Timestamp(date.getTime)
//        println("DT:"+Stamp)
      }
      catch {
        case e: Exception => {
          LOG.error(e.toString)
        }
      }
      Stamp
    }

    def GetStampByTime1(time: String): Timestamp = {
      var Stamp: Timestamp = new Timestamp(System.currentTimeMillis)
      val sdf: SimpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss")
      var date: Date = null
      try {
        date = sdf.parse(time)
        Stamp = new Timestamp(date.getTime)
//        println("DT:"+Stamp)
      }
      catch {
        case e: Exception => {
          LOG.error(e.toString)
        }
      }
      Stamp
    }
  def evaluate( i:Int): String = {
    var date:Date = new Date();//取时间
    var sf:SimpleDateFormat = new SimpleDateFormat("yyyyMMdd");
//    var nowDate:String  = sf.format(date);
//    println("nowDate="+nowDate)

    var time:Long  = (date.getTime() / 1000) + 60 * 60 * 24 * i;//秒
    date.setTime(time * 1000);//毫秒
    var nextDate:String  = sf.format(date);
//    println("nextDate="+nextDate)
    nextDate
  }
//  fmt: yyyy-MM-dd HH:mm:ss
  def getYesterday(i:Int,fmt:String):String= {
  var ft = ""
   if (fmt.isEmpty){
     ft = "yyyy-MM-dd HH:mm:ss"
   }else{
     ft = fmt
   }
    var dateFormat: SimpleDateFormat = new SimpleDateFormat(ft)
    var cal: Calendar = Calendar.getInstance()
    cal.add(Calendar.DATE, i)
    var yesterday = dateFormat.format(cal.getTime())
    yesterday
  }
    def getMaxDate(conf: Configuration, hconn: Connection,table: Table, colFamily: String, dt: String): String = {
      var maxDate: String = "2014-12-01 00:00:00"
      var results: ResultScanner = null
//      val table: Table = hconn.getTable(TableName.valueOf(tableName))
      try {

        val scan: Scan = new Scan

        scan.setReversed(true)
        scan.setMaxVersions()
        scan.setMaxResultSize(1)
        scan.setFilter(new PageFilter(1))


       scan.addFamily(Bytes.toBytes(colFamily));
       scan.addColumn(Bytes.toBytes(colFamily), Bytes.toBytes(dt));

        results = table.getScanner(scan)
//        println("results =================="+results.next())
        var i = 0
        import scala.collection.JavaConversions._
       for (r <- results)  {
         if( i == 0) {
           maxDate = new String(r.getValue(Bytes.toBytes(colFamily), Bytes.toBytes(dt)))
         }
//         i= i+1
//          println("maxDate =================="+maxDate)
        }
        if ((maxDate == "") || (maxDate == null)|| (maxDate == "null")) {
          maxDate = "2014-12-01 00:00:00"
        }
        LOG.info("maxDate="+maxDate)
      }
      catch {
        case e: Exception => {
          LOG.error(e.toString)
        }
      } finally {
        try {
          if (results != null) results.close
        }
        catch {
          case e: Exception => {
            LOG.error(e.toString)
          }
        }
      }
       maxDate
    }

  def addZeroForNum(str: String, strLength:Int): String = {
    var strg = str
    var  strLen:Int  = strg.length()
    var sb:StringBuffer  = null;
    while (strLen < strLength) {
      sb = new StringBuffer();
      sb.append("0").append(strg);// 左补0
      // sb.append(str).append("0");//右补0
      strg = sb.toString();
      strLen = strg.length();
    }
    return strg;
  }
  }