spark实现sqoop从oracle导数据到hive

一、Spark实现sqoop功能：同步mysql数据到hive

// scala 版
val df = spark.read.format("jdbc").option(
    "url",
    "jdbc:mysql://rr-bp1d22ltxgwa09g44720.mysql.rds.aliyuncs.com/" +
    dbname +
    "?useUnicode=true&characterEncoding=UTF-8"
).option("driver",
    "com.mysql.jdbc.Driver").option(
    "fetchsize", 1000).option(
    "numPartitions", 2).option(
    "dbtable", "(select * from " +
    tablename + ") as t").option(
    "user", "用户名").option(
    "password", "密码").load()

df.write.mode("Overwrite").saveAsTable(
    "写入hive的表名")

  

1.2 使用sqoop导数据

sqoop import \
--connect jdbc:mysql://localhost:3306/ods_stu\
--username root\
--password 123456\
--null-string 'NULL'  \
--null-non-string 'NULL' \
--query '
select * 
from stu
where $CONDITIONS' \
--target-dir /user/hive/warehouse/ods.db/ods_stu/dt=2020-06-04 \
--map-column-java addtime=String \
--map-column-hive addtime=String 
--append \
--split-by id \
--num-mappers 2 \
--as-parquetfile \

存在问题：sqoop直接从mysql导入parquet格式的hive表有一个数据类型异常

Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.LongWritable cannot be cast to org.apache.hadoop.hive.serde2.io.TimestampWritabl

解决方式：采用 --map-column-java 和 --map-column-hive 把mysql中时间类型之间转换为string类型存储hive中

load data inpath '/user/hive/warehouse/ods.db/ods_stu/dt=2020-06-04' 
into table ods.ods_minisns_systemtag 
partition (dt = '2020-06-04');

 1.3 使用spark导数据

    /**
     * 通过拉取mysql数据，创建临时表写入hive中
     */
    val df: DataFrame = spark.read.format("jdbc")
      .options(mysqlParam)
      //增量：单线程
      .option("query", query_mysql)
      .load()
    df.createOrReplaceTempView(tmp_tablename)
    spark.sql(insert_hive);

备注：spark可以直接写入parquet格式的hive表，并且可以直接数据清洗。但spark连接查询mysql会有一个散列id排序的问题，并且使用query自定义sql查询的话只有1个线程拉数据。而sqoop可以完美的解决这两个问题，使用方式简单。 

1.4sqoop与spark导数据的比较

	sqoop	spark
textfile格式外部表	不支持复杂分隔符，需要编译	无需关心分隔符
paquet格式	需处理数据类型转换	无需关心数据类型问题
散列id同步	完美处理	查询mysql时，直接按上下限及间隔划分区间，会有严重倾斜问题
并行度	完美处理	query自定义查询只有单线程查询mysql
直接清洗	仅在mysql查询时清洗，性能较差	拉取数据后直接清洗
使用难度	简单配置	写个工具类传参也可，但较复杂

使用pyspark模仿sqoop从oracle导数据到hive的主要功能（自动建表，分区导入，增量，解决数据换行符问题）

最近公司开始做大数据项目，让我使用sqoop（1.6.4版本）导数据进行数据分析计算，然而当我们将所有的工作流都放到azkaban上时整个流程跑完需要花费13分钟，而其中导数据（增量）就占了4分钟左右，老板给我提供了使用 spark 导数据的思路，学习整理了一个多星期，终于实现了sqoop的主要功能。

　　这里我使用的是pyspark完成的所有操作。

　　条件：hdfs平台，pyspark，ubuntu系统

　　运行：我这里是在 /usr/bin 目录下（或者指定在此目录下 ）运行的python文件，也可以使用系统自带的pyspark

./spark-submit 
--jars "/home/engyne/spark/ojdbc7.jar" 
--master local  
/home/engyne/spark/SparkDataBase.py

　　其中--jars 是指定连接oracle的驱动，ojdbc7.jar对应的是oracle12版本，--master local /...指定的是运行的python文件

　　注意：我的代码没有解决中文问题，所以不管是注释还是代码中都不能出现中文，记得删除！！！

 　　3、实现增量导入数据

　　我这里使用了MySql数据库，用来存储增量导入的信息，创建表（job）

DROP TABLE IF EXISTS `job`;

CREATE TABLE `job` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `database_name` varchar(50) DEFAULT NULL,     --数据库名称
  `table_name` varchar(100) DEFAULT NULL,       --需要增量导入的表名
  `partition_column_name` varchar(100) DEFAULT NULL,        --分区的字段名（这里只考虑对一个字段分区，如果多个字段这里应该使用一对多表结构吧）
  `partition_column_desc` varchar(50) DEFAULT NULL,     --分区字段类型
  `check_column` varchar(50) DEFAULT NULL,      --根据（table_name中）此字段进行增量导入校验（我这里例子使用的是updatetime）
  `last_value` varchar(255) DEFAULT NULL,       --校验值
  `status` int(1) NOT NULL,     --是否使用（1表示此job激活）
  PRIMARY KEY (`id`)
) INCREMENTAL=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

　　存储STUDENT表增量导入信息（这里是为了演示）

insert  into `job`(`id`,`database_name`,`table_name`,`partition_column_name`,`partition_column_desc`,`check_column`,`last_value`,`status`)
values 
(1,'test_datebase','STUDENT','AGE','string','UPDATETIME','2018-07-30',1)

import sys
import time
import MySQLdb
from pyspark.sql import HiveContext
from pyspark import SparkConf, SparkContext, SQLContext

# /usr/bin/spark-submit --jars "/home/engyne/spark/ojdbc7.jar" --master local  /home/engyne/spark/spark_test.py

conf = SparkConf().setAppName('inc_dd_openings')
sc = SparkContext(conf=conf)
sqlContext = HiveContext(sc)

reload(sys)
sys.setdefaultencoding("utf-8")

mysql_url = "192.168.1.111"
mysql_user = "root"
mysql_password = "xxx"
mysql_db = "xxx"

get_df_url = "jdbc:oracle:thin:@//192.168.1.xxx:1521/ORCLPDB"
get_df_driver = "oracle.jdbc.driver.OracleDriver"
get_df_user = "xxx"
get_df_password = "xxx"

# python 连接MySql的方法我这里就直接怼代码了，具体详解大家就看菜鸟教程

# insert		update		delete
def conMysqlDB_exec(sqlStr):
    db = MySQLdb.connect(mysql_url, mysql_user, mysql_password, mysql_db, charset='utf8' )
    cursor = db.cursor()
    try:
        cursor.execute(sqlStr)
        db.commit()
        result = True
    except:
        print("---->MySqlError: execute error")
        result = False
        db.rollback()
    db.close
    return result

# select
def conMysqlDB_fetchall(sqlStr):
	db = MySQLdb.connect(mysql_url, mysql_user, mysql_password, mysql_db, charset='utf8' )
	cursor = db.cursor()
	results = []
	try:
		cursor.execute(sqlStr)
		results = cursor.fetchall()
	except:
		print("---->MySqlError: unable to fecth data")
	db.close
	return results
    
def max(a, b):
    if a>b:
        return a
    else:
        return b
 
def getDF(tableName):
    try:
        df = sqlContext.read.format("jdbc") \
            .option("url", get_df_url) \
            .option("driver", get_df_driver) \
            .option("dbtable", tableName) \
            #.option("dbtable", "(select * from %s where to_char(%s, 'yyyy-MM-dd')>'%s')" % (tableName, checkColumn, lastValue)) \    
            #这里是关键，直接查询出新增的数据，这样后面的速度才能提升，否则要对整个表的dataframe进行操作，慢死了，千万不要相信dataframe的filter，where这些东西，4万多条数据要查3分钟！！！
            .option("user",  get_df_user).option("password", get_df_password) \
            .load()
    except:
        print("---->DF_ERROR: get df error")
    return df
    
#获取表结构字段
#这里需要先手动创建分区表，我使用dataframe的dtypes属性获取到表结构，然后循环拼接表的每个字段在hive中所对应的类型
def getTableDesc(sqlDF, partitionColumnName):
    columnNameSql = ""
    cnSql = ""
    dfTypeList = sqlDF.dtypes
    for i, val in enumerate(dfTypeList):
        if (val[0] != partitionColumnName):
            columnNameSql = columnNameSql + val[0] + " " + val[1]
            cnSql = cnSql + val[0]

            if i + 1 != len(dfTypeList):
                columnNameSql = columnNameSql + ","
                cnSql = cnSql + ","
    return cnSql, columnNameSql

# o2h
def df2hive(df, databaseName, tableName, partitionColumnName, partitionColumnDesc, isIncremental, type):
    sqlContext.sql("use %s" % databaseName)
    schema = df.schema
    cnSql, columnNameSql = getTableDesc(df, partitionColumnName)
    #df自带获取schema的方法,不要学我去拼凑出来
    # rdd = df.map(lambda x : [x[processLineList[i]].replace("\n","").replace("\r","") for i in range(len(processLineList))])
    rdd = df.map(lambda x : [(x[i].replace("\n","").replace("\r","") if isinstance(x[i], unicode) or isinstance(x[i], str) else x[i]) for i in range(len(x))])
    df = sqlContext.createDataFrame(rdd, schema)
    
    df.registerTempTable("temp%s" % tableName)
    
    if partitionColumnName == "":
        if isIncremental:
                saveSql = "insert into table %s select * from temp%s" % (tableName, tableName)
        else:
            if type == "import-overwrite":
                saveSql = "insert overwrite table %s select * from temp%s" % (tableName, tableName)
            else:
                saveSql = "create table %s as select * from temp%s" % (tableName, tableName)
    else:
        #修改一下hive的默认设置以支持动态分区
        sqlContext.sql("set hive.exec.dynamic.partition=true")
        sqlContext.sql("set hive.exec.dynamic.partition.mode=nonstrict")
        #设置hive支持创建分区文件的最大值
        sqlContext.sql("SET hive.exec.max.dynamic.partitions=100000")
        sqlContext.sql("SET hive.exec.max.dynamic.partitions.pernode=100000")
        if isIncremental:
            saveSql = "insert into table %s partition(%s) SELECT %s,%s FROM temp%s" % (tableName, partitionColumnName, cnSql, partitionColumnName, tableName)
            
        else:
            if type != "import-overwrite":
                # dynamic partition create table
                createTableSql = "create table %s (%s)PARTITIONED BY (%s %s) row format delimited fields terminated by '\t'  LINES TERMINATED BY '\n'" % (tableName, columnNameSql, partitionColumnName, partitionColumnDesc)

                sqlContext.sql(createTableSql)
                print("---->INFO: dynamic partition create success")
                # 这里需要先手动创建分区表，我使用dataframe的dtypes属性获取到表结构，然后循环拼接表的每个字段在hive中所对应的类型 
                # 最后写入表数据的代码是：#insert overwrite table STUDENT partition(AGE) SELECT ID,NAME,UPDATETIME,AGE FROM tempTable
            saveSql = "insert overwrite table %s partition(%s) SELECT %s,%s FROM temp%s" % (tableName, partitionColumnName, cnSql, partitionColumnName, tableName)
            
    sqlContext.sql(saveSql)
    sqlContext.dropTempTable("temp%s" % tableName)

#  import 查询增量信息，使用spark进行导入
def importData(type):
    time_start = time.time()
    findJobSql = "SELECT * FROM job where status=1"
    result = conMysqlDB_fetchall(findJobSql)
    resultInfoList = []
    for i, val in enumerate(result):
        databaseName = val[1]
        tableName = val[2]
        partitionColumnName = val[3]
        partitionColumnDesc = val[4]
        checkColumn = val[5]
        lastValue = val[6]
        
        sqlContext.sql("use %s" % databaseName)
        
        if type == "import-append":
            df = getDF("(select * from %s where to_char(%s, 'yyyy-MM-dd')>'%s')" % (tableName, checkColumn, lastValue))
            try: ##获取到新增字段的最大值！！！（这块也困了我好久）这里使用的是python的reduce函数，调用的max方法
                nowLastValue = df.rdd.reduce(max)[checkColumn]
                oracle2hive(df, databaseName, tableName, partitionColumnName, partitionColumnDesc, True, "")
                ##更新mysql表，使lastValue是表最新值
                updataJobSql = "UPDATE job SET last_value='%s' WHERE table_name='%s'" % (nowLastValue, tableName)
                if conMysqlDB_exec(updataJobSql):
                    print("---->SUCCESS: incremental import success")
                    resultInfoList.append("SUCCESS: %s import success" % tableName)
            except ValueError:
                print("---->INFO: No new data added!")
                resultInfoList.append("INFO: %s   ValueError(No new data added!)" % tableName)
                pass
            except:
                print("---->ERROR: other error")
                resultInfoList.append("ERROR: %s has other error" % tableName)
                pass
               
        else:
            df = getDF(tableName)
            try:
                oracle2hive(df, databaseName, tableName, partitionColumnName, partitionColumnDesc, False, type)
                print("---->INFO: import success")
                resultInfoList.append("SUCCESS: %s import success" % tableName)
            except:
                print("---->ERROR: import error")
                resultInfoList.append("ERROR: %s import error" % tableName)
                pass
    print("RESULT:")
    for i, val  in enumerate(resultInfoList):
        print(val)
    time_end = time.time()
    print("---->INFO: time cost", (time_end - time_start)/60, "m")
    
     
    
if __name__ == '__main__':
    
    # type :
    #import-overwrite: #General import   overwrite table 
    #import-append   :#Incremental import
    importData("import")

　　4、解决导入数据换行符问题

　　有时候oracle中的数据中会存在换行符（" \n "）然而hive1.1.0中数据换行默认识别的也是\n，最坑的是还不能对它进行修改（目前我没有查出修改的方法，大家要是有办法欢迎在评论区讨论）那我只能对数据进行处理了，以前使用sqoop的时候也有这个问题，所幸sqoop有解决换行符的语句，，，，巴拉巴拉，，，扯远了

　　解决换行符需要dataframe的map方法，然后使用lambda表达式进行replace，总结好就是下面的代码（第3行）

　　解释：这是个for循环里面加if else 判断，整个需要用  [ ]  包起来，没错这是个list ，如果不包就报错，lambda x 获取到的是表中一行行的数据，for循环对每一行进行遍历，然后对一行中每个字段进行判断，是否是unicode或者str类型，（一般只有是这两个类型才存在换行符）如果是则进行replace处理，否则不做处理。

　　转化好之后这是个rdd类型的数据，需要转化为dataframe类型才能写入hive

 #df自带获取schema的方法,不要学我去拼凑出来（😓）
 schema = df.schema
 rdd = df.map(lambda x : [(x[i].replace("\n","").replace("\r","") if isinstance(x[i], unicode) or isinstance(x[i], str) else x[i]) for i in range(len(x))])
 df = sqlContext.createDataFrame(rdd, schema)

　　总结：使用spark进行数据导入和增量导入与sqoop做对比，80张表，sqoop 4分钟多，使用此方法，0.7分钟，（同是没有新数据的前提下），普通导表，此方法5分钟，80张大表（外网oracle），sqoop的话我就不说了，当时一张200万数据的表导了一晚上。。。

使用pyspark模仿sqoop从oracle导数据到hive的主要功能（自动建表，分区导入，增量，解决数据换行符问题） - ZJ&Y - 博客园使用pyspark 在hive中建表，分区导入，增量，解决数据换行符问题汇总https://www.cnblogs.com/yzj-blog/p/9393297.html