原文连接: http://dblab.xmu.edu.cn/blog/1724-2/
原文连接:https://mapr.com/support/s/article/PySpark-to-MySQL-connectivity?language=en_US (这个网址需要科学上网)
实验环境: centos7.6
spark: 2.4.3
python: 3.6.8
mysql: 5.6.38-log
首先,在home目录下建立jdbc文件夹,方便以后保存各种jdbc连接器
mkdir /home/jdbc
mkdir /home/jdbc/mysql
cd /home/jdbc/mysql
然后到
https://dev.mysql.com/downloads/connector/j/
下载一个MySQL的JDBC驱动
点击download
获取下载地址
https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-8.0.16.tar.gz
然后用wget下载到服务器上
wget https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-8.0.16.tar.gz
解压文件
tar -zxvf mysql-connector-java-8.0.16.tar.gz
cp -r mysql-connector-java-8.0.16 /usr/local/spark/spark-2.4.3-bin-hadoop2.7/jars
这时,你在jars下就可以看到你刚复制过来的mysql文件夹,文件夹中就有对应的jar文件
pyspark连接mysql数据库
下面,启动pyspark
cd /usr/local/spark/spark-2.4.3-bin-hadoop2.7/bin
./pyspark \
--jars /usr/local/spark/spark-2.4.3-bin-hadoop2.7/jars/mysql-connector-java-8.0.16/mysql-connector-java-8.0.16.jar \
--driver-class-path /usr/local/spark/spark-2.4.3-bin-hadoop2.7/jars/mysql-connector-java-8.0.16/mysql-connector-java-8.0.16.jar
上面的命令行中,在一行的末尾加入斜杠\,是为了告诉spark-shell,命令还没有结束。
启动进入pyspark以后,可以执行以下命令连接数据库,读取数据,并显示:
jdbcDF = spark.read.format("jdbc").option("url", "jdbc:mysql://localhost:3306/db_test").option("driver","com.mysql.cj.jdbc.Driver").option("dbtable", "temp_testsqqqq").option("user", "root").option("password", "123456").load()
jdbcDF.show()
scala 连接mysql数据库
scala 的连接跟pyspark非常相似
前面一样需要下载MySQL的连接器,只是启动方式略有不同。
cd /usr/local/spark/spark-2.4.3-bin-hadoop2.7/bin
./spark-shell \
--jars /usr/local/spark/spark-2.4.3-bin-hadoop2.7/jars/mysql-connector-java-8.0.16/mysql-connector-java-8.0.16.jar \
--driver-class-path /usr/local/spark/spark-2.4.3-bin-hadoop2.7/jars/mysql-connector-java-8.0.16/mysql-connector-java-8.0.16.jar
进入spark-shell后
val jdbcDF = spark.read.format("jdbc").option("url", "jdbc:mysql://localhost:3306/db_test").option("driver","com.mysql.cj.jdbc.Driver").option("dbtable", "temp_testsqqqq").option("user", "root").option("password", "123456").load()
jdbcDF.show()
python程序独立执行
import os
# sparkClassPath = os.getenv('SPARK_CLASSPATH', '/usr/local/spark/spark-2.4.3-bin-hadoop2.7/jars/mysql-connector-java-8.0.16/mysql-connector-java-8.0.16.jar')
os.environ['PYSPARK_SUBMIT_ARGS'] = '--jars /usr/local/spark/spark-2.4.3-bin-hadoop2.7/jars/mysql-connector-java-8.0.16/mysql-connector-java-8.0.16.jar ' \
'--driver-class-path /usr/local/spark/spark-2.4.3-bin-hadoop2.7/jars/mysql-connector-java-8.0.16/mysql-connector-java-8.0.16.jar ' \
'pyspark-shell'
# PYSPARK_SUBMIT_ARGS中填写具体的文件路径,与pyspark和scala的是一样的,注意空格间隔
from pyspark import SparkContext, SparkConf ,SQLContext
from pyspark.sql import SparkSession
conf = SparkConf()
conf.setMaster("local[*]")
conf.setAppName('mysqltest')
# conf.set('spark.jars','files:%s'%sparkClassPath)
# conf.set('spark.executor.extraClassPath', sparkClassPath)
# conf.set('spark.driver.extraClassPath', sparkClassPath)
conf.getAll()
sc = SparkContext(conf=conf)
sqlContext = SQLContext(sc)
url = "jdbc:mysql://xxx.xxx.x.xxx:3306/db_test"
properties = {
"user": "root",
"password": "123456",
"driver": "com.mysql.cj.jdbc.Driver"
}
df = sqlContext.read.jdbc(url=url, table="temp_testsqqqq", properties=properties)
查询结果
ssh://root@192.168.61.128:22/usr/local/python3/bin/python3.6 -u /tmp/pycharm_project_466/temp.py
19/06/10 04:53:16 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
19/06/10 04:53:33 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.
+---+--------------------------------------+----+----+--------------+--------------------+--------------------+
| id| key1|key2|key3| col1| col2| col3|
+---+--------------------------------------+----+----+--------------+--------------------+--------------------+
| 1| 中石化盛骏投资(深圳)有限公司|null|null| null| null| null...|
| 2| 深圳望正资产管理有限公司|null|null| null| null...| null...|
+---+--------------------------------------+----+----+--------------+--------------------+--------------------+
only showing top 20 rows
Process finished with exit code 0