sparksql读文件到mysql_利用spark实现hive到mysql的数据通信和存储

#该篇内容采用spark的python接口，即pyspark

现阶段我们已经实现sparksql读取hive数据，但日常一个频繁的操作就是数据导入导出。

我们知道hadoop生态圈里有一个成员叫sqoop，这组件可以实现hive到关系型数据库mysql，oracle等数据库的数据转移。

但是有两个缺点;

1.sqoop只能表到表的导入，不能在中间实现数据计算变换等操作

2.网上传言spark转移比sqoop快(没有具体测试，保留意见)

sparksql读取hive数据读取myql数据

读数据这里提供两种思路。原始python有mysql插件，sparkjdbc链接。

第一：python插件，如mysqldb，pymysql等。

pymysql是和mysqldb一样的插件连接器，需要pip下载，二者只是名字不一样，用法完全相同。connect里有host地址，用户名，密码，以及库名。

然后获取游标，游标操作sql语句，关闭链接。可以看到我们查到两条数据。

第二：jdbc链接。

因为hive的元数据存在mysql，所以jdbc的jar包做驱动链接，该jar包可去官网下载，记得选通用版本，因为选具体的操作系统不是tar.gz压缩文件，不方便操作，下载，tar解压，把jar包放到spark的jars下。然后指定jdbc链接就好了。

data是sparksql的hive查询数据，然后用df for spark的 write函数。

然后去mysql查看数据是否已被插入。

二者的区别

采用jdbc这种方式，是直接用的spark函数，读进来的结果是spark df，而采用mysqldb插件查询出来的文本数据，当然我还有一种方法，结合sqlalchemy创建引擎，mysqldb做驱动，直接将mysql数据读为dataframe，不过这是pandas df，依然要转成RDD df，然后才能利用spark函数往hive存。

数据互通存储

#这里备注一下，该测试数据有四列，mysql表为dbtest.EMPLOYEE,hive表为myhive.test02

现在mysql的数据读进来了，hive的数据也读进来了，那么就简单多了。相互复制就可以了。

先试一下pandas df到hive：

from sqlalchemy import create_engine

import pandas as pd

engine=create_engine('mysql+pymysql://root:123456@localhost:3306/dbtest?charset=utf8',echo=True)

sql=pd.read_sql('EMPLOYEE',engine,chunksize=10000)

for i in sql:#the sql is multip

print(i) #the i is df for python

spark_df = sqlContext.createDataFrame (i)#,schema=['a','b','c','d']

# df for spark

print(type(spark_df))

spark_df.registerTempTable('employee')

try:

sqlContext.sql('insert into myhive.test02 select * from employee' )

except:

print('----->Error')

这里简单做一下介绍：用mysqldb做驱动，sqlalchemy创建引擎，pandas读，这样讲mysql数据直接读为了pandas df，接着将creadataframe函数将pandas df转换成RDD df，再将RDD df 转换成临时表，然后利用sparksql往hivetest02表里插入数据。这里采用了insert into函数，

其实还有一种RDD df内置了存储型函数。

#write.saveAsTable("ai.da_aipurchase_dailysale_hive", None, "append", partitionBy='saledate')

#spark_df.write.saveAsTable("myhive.test02", None, "append", None)

不过这种写法只支持将数据写到一个新表，写到已存在的表，会报错，提示让用insert的写法。

#spark_df.write.mode("overwrite").partitionBy("saledate").insertInto("myhive.test02")

spark_df.write.format("csv").mode("overwrite").insertInto("myhive.test02")

这种方法可以写到已存在的表，但是也有一个问题，这里的overwrite(append)函数不起作用，还是采用append方式增量写入，另外还有format模块，不写这个函数默认的写到hdfs是增加新的文件，采用part-0000的命名方法，没有后缀名，追加；追加format csv 依然是这种形式(该hive表建表是采用stored as textfile，里面原有的数据是采用txt文本)

pd df转换的方式会了，jdbc链接就更容易了。

dataframe_mysql = sqlContext.read.format("jdbc")\

.options(\

url="jdbc:mysql://localhost:3306/dbtest", \

driver="com.mysql.cj.jdbc.Driver", \

dbtable="EMPLOYEE", \

user="root", \

password="123456"\

).load()

dataframe_mysql.show()

jdbc过来直接就是RDD df了，那么采用write函数就可以用了，转成临时表也可以。

hive写到mysql

data=sqlContext.sql('select * from myhive.test02')

print(type(data))

data.write.mode("append").format("jdbc").options(

url="jdbc:mysql://localhost:3306/dbtest",

user='root',

password='123456',

dbtable="EMPLOYEE",

#batchsize="1000",

).save()

mysql写查看数据也都写进来了。

扩展

当然这里我也有几点疑问，sqoop应该只是IO型的数据流，具体没用过，同事说跟集群也有关系，采用spark的话，spark是计算框架，所以必然牵扯到内存，比如我有一个t的数据，来导入导出，内存呢能不能撑不住，当然往关系型数据库，也不会倒一个t，都是往hiveload data。

这里只写了基于pyspark的hive，与mysql，的通信，到oracle，mgdb，等暂时机器上没有该软件，所以也测试，不过应该大同小异，以后有机会用到了再补充。

最后再附上一篇spark rdd dataframe 读取数据的介绍pyspark系列--读写dataframe - CSDN博客​blog.csdn.net