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

本文转载：https://blog.csdn.net/helloxiaozhe/article/details/81027196

主要报错信息：

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）出错代码：

from pyspark import SparkConf, SQLContext
from pyspark import SparkContext

conf=SparkConf().setAppName("miniProject").setMaster("local[*]")
sc = SparkContext(appName="mysqltest")
sqlContext =SQLContext(sc)

df = sqlContext.read.format("jdbc").options(
url="jdbc:mysql://xxxx:xxxx/ec_order?user=root&password=xxxx", dbtable="dim_moonr_oms_channel_shop_info").load()

df.show()

　

（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目录下)

 

 

然后就解决了这个问题