pyspark连接MySQL出错 java.sql.SQLException: No suitable driver

pyspark连接MySQL出错py4j.protocol.Py4JJavaError: An error occurred while calling o26.load.

: java.sql.SQLException: No suitable driver 解决方法

（1）错误提示：

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).
18/07/13 10:23:51 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
18/07/13 10:23:51 WARN Utils: Your hostname, localhost resolves to a loopback address: 127.0.0.1; using 10.2.33.175 instead (on interface en0)
18/07/13 10:23:51 WARN Utils: Set SPARK_LOCAL_IP if you need to bind to another address
Traceback (most recent call last):
  File "/Users/a6/Downloads/speiyou_di/hive/log_task/spark_local.py", line 16, in <module>
    .option("dbtable", "test_person").option("user", "root").option("password", "yyz!123456").load()
  File "/Library/Python/2.7/site-packages/pyspark/sql/readwriter.py", line 155, in load
    return self._df(self._jreader.load())
  File "/Library/Python/2.7/site-packages/py4j-0.10.6-py2.7.egg/py4j/java_gateway.py", line 1160, in __call__
    answer, self.gateway_client, self.target_id, self.name)
  File "/Library/Python/2.7/site-packages/pyspark/sql/utils.py", line 63, in deco
    return f(*a, **kw)
  File "/Library/Python/2.7/site-packages/py4j-0.10.6-py2.7.egg/py4j/protocol.py", line 320, in get_return_value
    format(target_id, ".", name), value)
py4j.protocol.Py4JJavaError: An error occurred while calling o26.load.
: java.sql.SQLException: No suitable driver
	at java.sql.DriverManager.getDriver(DriverManager.java:315)
	at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$7.apply(JDBCOptions.scala:84)
	at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$7.apply(JDBCOptions.scala:84)
	at scala.Option.getOrElse(Option.scala:121)
	at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:83)
	at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:34)
	at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:32)
	at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:330)
	at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:152)
	at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:125)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
	at py4j.Gateway.invoke(Gateway.java:280)
	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
	at py4j.commands.CallCommand.execute(CallCommand.java:79)
	at py4j.GatewayConnection.run(GatewayConnection.java:214)
	at java.lang.Thread.run(Thread.java:748)

（2）出错代码：

# !/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding("utf-8")
# 设置spark_home
import os
os.environ["SPARK_HOME"] = "/Users/a6/Applications/spark-2.1.0-bin-hadoop2.6"

from pyspark.sql import SQLContext
from pyspark import SparkContext
sc = SparkContext(appName="pyspark mysql demo")
sqlContext = SQLContext(sc)
# 创建连接获取数据
dataframe_mysql=sqlContext.read.format("jdbc").option("url", "jdbc:mysql://localhost:3306/spark_db")\
.option("dbtable", "test_person").option("user", "root").option("password", "yz123456").load()

# 输出数据
print "dataframe_mysql.collect()\n",dataframe_mysql.collect()
dataframe_mysql.registerTempTable("temp_table")
print dataframe_mysql.show()
print dataframe_mysql.count()

（3）解决办法：

最下面的java.sql.SQLException: No suitable driver才是重点

解决办法是将mysql的驱动jar包，放到D:\Program Files\Java\jdk1.8.0_91\jre\lib\ext目录下（JAVA_HOME目录下的jre\lib\ext目录下)

localhost:ext a6$ echo $JAVA_HOME
/Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home
localhost:ext a6$ cd /Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home
localhost:ext a6$ cd /jre/lib/ext

将下载的MySQL驱动jar包，放到jre\lib\ext目录下面

 sudo cp mysql-connector-java-5.1.44-bin.jar /Library/Java/JavaVirtualMachines/jdk1.8.0_131.jdk/Contents/Home/jre/lib/ext

适合我电脑的MySQL驱动jar包版本是mysql-connector-java-5.1.44，下载地址如下：

链接: https://pan.baidu.com/s/12dpXWNnmqZiizNV_vDp13g 密码: hy9m

然后就解决了这个问题

（4）错误场景还原需要

首先，需要在本地创建数据库spark_db，同时创建test_person数据，具体如下：

create database spark_db;

CREATE TABLE `test_person` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

insert into test_person(name,age) values('yyz',18);

参考：https://www.cnblogs.com/zhuiyi/p/5591886.html

https://stackoverflow.com/questions/36588084/pyspark-mysql-jdbc-load-an-error-occurred-while-calling-o23-load-no-suitable-dri