package com.sym.common
import java.util.Properties
import org.apache.log4j.Logger
import org.apache.spark.sql._
/**
* FileName: SparkSqlSources
* Author: ShaoYM
* Date: 2019/6/14 9:04
* Description:
*/
object SparkSqlSources {
lazy val logger=Logger.getLogger(this.getClass.getSimpleName)
/*
* @methods registerTable
* @author ShaoYM
* @date 2019/6/14 10:53
* @param sqlContext
* @param dbtable
* @return Unit
* @descripe 获取数据库的表注册为临时表 mysql/oracle/phoenix
*/
def registerTable(sqlContext: SQLContext, dbtables: Array[String],dbType:DBType): Unit = {
// 根据传入的 dbType 初始化
lazy val dataFrameReader:DataFrameReader= dbType match {
case DBType.MYSQLDB => getMysqlDataFrameReader(sqlContext)
case DBType.ORACLEDB => getOracleDataFrameReader(sqlContext)
case DBType.PHOENIXDB => getphoenxDataFrameReader(sqlContext)
case DBType.HIVEDB => getHiveDataFrameReader(sqlContext)
case _ => {
logger.error(" The daType don't exists ,initialize fail ! ")
null
}
}
// 判断 初始化 是否成功
if(null == dataFrameReader){
logger.error("*********** dataFrameReader is null initialize fail ! ")
return
}
// 将传入的 数据库表 注册为 临时表
for(tableName <- dbtables){
dbType match {
case DBType.PHOENIXDB => dataFrameReader.option("dbtable","\""+tableName+"\"").load().createOrReplaceTempView(tableName)
case _ => dataFrameReader.option("dbtable",tableName).load().createOrReplaceTempView(tableName)
}
}
}
def registerMysqlTable(sqlContext: SQLContext, dbtables: Array[String]): Unit = {
// 根据传入的 dbType 初始化
lazy val dataFrameReader:DataFrameReader= getMysqlDataFrameReader(sqlContext)
// 判断 初始化 是否成功
if(null == dataFrameReader){
logger.error("*********** dataFrameReader is null initialize fail ! ")
return
}
// 将传入的 数据库表 注册为 临时表
for(tableName <- dbtables){
dataFrameReader.option("dbtable",tableName).load().createOrReplaceTempView(tableName)
}
}
def registerOracleTable(sqlContext: SQLContext, dbtables: Array[String]): Unit = {
// 根据传入的 dbType 初始化
lazy val dataFrameReader:DataFrameReader= getOracleDataFrameReader(sqlContext)
// 判断 初始化 是否成功
if(null == dataFrameReader){
logger.error("*********** dataFrameReader is null initialize fail ! ")
return
}
// 将传入的 数据库表 注册为 临时表
for(tableName <- dbtables){
dataFrameReader.option("dbtable",tableName).load().createOrReplaceTempView(tableName)
}
}
def registerPhoenixTable(sqlContext: SQLContext, dbtables: Array[String]): Unit = {
// 根据传入的 dbType 初始化
lazy val dataFrameReader:DataFrameReader= getphoenxDataFrameReader(sqlContext)
// 判断 初始化 是否成功
if(null == dataFrameReader){
logger.error("*********** dataFrameReader is null initialize fail ! ")
return
}
// 将传入的 数据库表 注册为 临时表
for(tableName <- dbtables){
dataFrameReader.option("dbtable","\""+tableName+"\"").load().createOrReplaceTempView(tableName)
}
}
/*
* @methods writeTable
* @author ShaoYM
* @date 2019/6/28 16:54
* @param df
* @param insertTableName
* @param dbType :DBType
* @return Unit
* @descripe 获取到的 DataFrame 存储到数据库中, mysql/oracle 不存在表则创建。phoenix 存储数据表需存在
*/
def writePhoenixTable(df: DataFrame,insertTableName:String): Unit ={
lazy val pro=InitializeProperties.InitPhoenixDBProperties()
df.write
.format("org.apache.phoenix.spark")
.mode(SaveMode.Overwrite)
.option("table", insertTableName)
.option("zkUrl", pro.getProperty("url"))
.save()
}
def writeOracleTable(df: DataFrame,insertTableName:String): Unit ={
lazy val pro=InitializeProperties.InitOrcleDBProperties()
lazy val prop = new Properties()
prop.put("user", pro.getProperty("userName"))
prop.put("password", pro.getProperty("passWord"))
df.write.mode(SaveMode.Append).jdbc(pro.getProperty("url"),insertTableName,prop)
}
def writeMysqlTable(df: DataFrame,insertTableName:String,dbType:DBType): Unit ={
lazy val pro=InitializeProperties.InitMysqlDBProperties()
lazy val prop = new Properties()
prop.put("user", pro.getProperty("userName"))
prop.put("password", pro.getProperty("passWord"))
df.write.mode(SaveMode.Append).jdbc(pro.getProperty("url"),insertTableName,prop)
}
def writeHiveTable(df: DataFrame,insertTableName:String): Unit ={
lazy val pro=InitializeProperties.InitHiveDBProperties()
lazy val prop = new Properties()
prop.put("user", pro.getProperty("userName"))
prop.put("password", pro.getProperty("passWord"))
df.write.mode(SaveMode.Append).jdbc(pro.getProperty("url"),insertTableName,prop)
}
/*
* @methods getMysqlDataFrameReader
* @author ShaoYM
* @date 2019/6/14 10:59
* @param sqlContext
* @return _root_.org.apache.spark.sql.DataFrameReader
* @descripe 返回一个 初始化了基础数据库信息的 DataFrameReader
*/
def getMysqlDataFrameReader(sqlContext: SQLContext): DataFrameReader ={
lazy val pro=InitializeProperties.InitMysqlDBProperties()
sqlContext.read.format("jdbc")
.option("url",pro.getProperty("url"))
.option("driver",pro.getProperty("driverClass"))
.option("user",pro.getProperty("userName"))
.option("password",pro.getProperty("passWord"))
}
/*
* @methods getOracleDataFrameReader
* @author ShaoYM
* @date 2019/7/1 9:16
* @param sqlContext
* @return _root_.org.apache.spark.sql.DataFrameReader
* @descripe TODO
*/
def getOracleDataFrameReader(sqlContext: SQLContext): DataFrameReader ={
lazy val pro=InitializeProperties.InitOrcleDBProperties()
sqlContext.read.format("jdbc")
.option("url",pro.getProperty("url"))
.option("driver",pro.getProperty("driverClass"))
.option("user",pro.getProperty("userName"))
.option("password",pro.getProperty("passWord"))
}
/*
* @methods getHiveDataFrameReader
* @author ShaoYM
* @date 2019/7/1 9:17
* @param sqlContext
* @return _root_.org.apache.spark.sql.DataFrameReader
* @descripe TODO
*/
def getHiveDataFrameReader(sqlContext: SQLContext): DataFrameReader ={
lazy val pro=InitializeProperties.InitHiveDBProperties()
sqlContext.read.format("jdbc")
.option("url",pro.getProperty("url"))
.option("driver",pro.getProperty("driverClass"))
.option("user",pro.getProperty("userName"))
.option("password",pro.getProperty("passWord"))
}
/*
* @methods getphoenxDataFrameReader
* @author ShaoYM
* @date 2019/7/1 9:17
* @param sqlContext
* @return _root_.org.apache.spark.sql.DataFrameReader
* @descripe TODO
*/
def getphoenxDataFrameReader(sqlContext: SQLContext): DataFrameReader ={
lazy val pro=InitializeProperties.InitPhoenixDBProperties()
sqlContext.read.format("jdbc")
.option("url",pro.getProperty("url"))
.option("driver",pro.getProperty("driverClass"))
}
def main(args: Array[String]): Unit = {
}
}
|