pyspark连接mysql读取数据、写入数据(四种模式)
环境:
mysql 5.7
Spark 2.4.4
连接用的jar包:mysql-connector-java-8.0.17.jar
这个jar包我用的是配置hive的时候,配置用的那个jar包,和其他文章写的好像不太一样,但是我的能用。另外有些文章写需要配置spark-env.sh,加上SPARK_CLASSPATH的配置指向这个jar包位置,但是我也是没配,大家可以自己试试。
1.下载mysql-connector-java-8.0.17.jar
下载地址:https://www.mysql.com/products/connector/
步骤看图
这两个包哪个都行,zip包下载之后,解压之后在目录里面找到mysql-connector-java-8.0.17.jar之后丢进去虚拟机里面就行,如果是tar包你可以直接wget在虚拟机里面下载之后解压找到jar包拿来用,反正都行。
如果你已经配置过hive了,其实你直接在hive的lib目录里面也找得到。
找到之后放到spark的jars目录里面。
/xx/xx/spark/jars/
2.代码
from pyspark import SparkContext
from pyspark.sql import SQLContext
if __name__ == '__main__':
sc = SparkContext(appName="mysqltest")
sqlContext = SQLContext(sc)
# 读取数据库表格,读取出来的数据类型是dataframe
tableDF = connect_Context.read.format("jdbc").\
option("url","jdbc:mysql://localhost:3306/dbname").\
option("driver","com.mysql.jdbc.Driver").\
option("dbtable", "tablename").\
option("user", "root").\
option("password", "123456").load()
tableDF.show()
# dataframe存入mysql,写入之前建议先自定义格式化数据,有些数据格式是写入不到mysql的,需要自己试一下,一般正常的格式都可以
"""写入模式(mode)从源码看有四种模式:
``overwrite``是覆盖;
``append``是增加;
``ignore``是如果数据已存在,则静默忽略此操作;
``error`` or ``errorifexists`` (default case):如果数据有问题就抛出异常。
"""
tableDF.write.jdbc('jdbc:mysql://localhost:3306/dbname?useSSL=false',
'tablename',
mode='overwrite',
properties={"user": "root", "password": "123456"})
=============================================================================
更新一下
=============================================================================
最近由于有业务需求,有可能需要应付把dataframe(有可能达到1000万条以上)一次性写入到mysql中,所以百度找了一下其他写入的方式,并且试着调优。
另一段dataframe写入mysql的代码:
df.write.mode('overwrite').format("jdbc").\
option("url", "jdbc:mysql://hostname:3306/wen_test?rewriteBatchedStatements=true").\
option("dbtable", "tb_demo").\
option("user", "root").\
option("password", "123456").\
option("driver", "com.mysql.cj.jdbc.Driver").\
option("batchsize", 10000).\
option("isolationLevel", "NONE").\
option("truncate", "true").save()
url :要连接的JDBC URL。在url后加上参数rewriteBatchedStatements=true表示MySQL服务开启批次写入,此参数是批次写入的一个比较重要参数,可明显提升性能
dbtable:应该读取的JDBC表。可以使用括号中的子查询代替完整表(使用select 语句代替表,例如,(select * from table_name) as t1,必须给查询结果加上别名)
driver:用于连接到此URL的JDBC驱动程序的类名partitionColumn,lowerBound,upperBound
numPartitions:这些options仅适用于read数据。这些options必须同时被指定。他们描述,如何从多个workers并行读取数据时,分割表。partitionColumn必须是表中的数字列。lowerBound和upperBound仅用于决定分区的大小,而不是用于过滤表中的行。表中的所有行将被分割并返回。
fetchsize:仅适用于read数据。JDBC提取大小,用于确定每次获取的行数。这可以帮助JDBC驱动程序调优性能,这些驱动程序默认具有较低的提取大小(例如,Oracle每次提取10行)。
batchsize:仅适用于write数据。JDBC批量大小,用于确定每次insert的行数。这可以帮助JDBC驱动程序调优性能。默认为1000。
isolationLevel:仅适用于write数据。事务隔离级别,适用于当前连接。它可以是以下配置:
NONE,不开启事务;
== REPEATABLE_READ==,这是MySQL的InnoDB引擎默认的隔离级别,它阻止查询的任何行被其他事务更改。因此,阻塞不可重复读,而不是幻读。也就是说在可重复读中,可能会出现幻读。重复读使用一种中等严格的锁定策略,以便事务中的所有查询都能看到来自相同快照(即事务启动时的数据)的数据。
READ_COMMITTED,读提交, 事务无法看到来自其他事务的未提交数据,但可以看到当前事务启动后另一个事务提交的数据。当拥有这种级别的事务执行 UPDATE … WHERE or DELETE … WHERE操作时,其他事务可能需要等待。但是该事务可以执行 SELECT … FOR UPDATE, and LOCK IN SHARE MODE操作,其他事务不需要等待;
READ_UNCOMMITTED,未读提交, 它是最低的隔离级别。它会读取到其他事务修改尚未提交的数据,使用此隔离级别就需要非常小心,认识到这种级别下的查询结果可能不一致或不可复制,这取决于其他事务同时在做什么。通常,具有此隔离级别的事务只执行查询,而不执行插入、更新或删除操作。
SERIALIZABLE,串行化隔离级别是最高的隔离级别,它使用了最保守的锁策略。它阻止任何其他事务插入或更改此事务读取的数据,直到该事务完成。简单的来说,就是一个事务一个事务的来执行,显然性能会很低。在这种隔离级别下,一个事务中的相同查询可以反复执行,每次查询结果是一样的。从当前事务开始执行,任何更改另一个事务提交的数据的尝试都会导致当前事务等待(阻塞)。这是SQL标准指定的默认隔离级别(注意不是MySQL)。在实践中,这种严格程度是很少需要的。
truncate:仅适用于write数据。当SaveMode.Overwrite启用时,此选项会truncate在MySQL中的表,而不是删除,再重建其现有的表。这可以更有效,并且防止表元数据(例如,索引)被去除。但是,在某些情况下,例如当新数据具有不同的模式时,它将无法工作。它默认为false。
createTableOptions:仅适用于write数据。此选项允许在创建表(例如CREATE TABLE t (name string) ENGINE=InnoDB.)时设置特定的数据库表和分区选项。
前面看到有文章说dataframe写入到mysql是不用开启事务的,有没有大神可以解释一下呢?
以上更新的内容参考了以下链接,整合了一下:
https://blog.csdn.net/Realoyou/article/details/97632376
https://blog.csdn.net/qq_33792843/article/details/91518940?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task