blocks|key|138559|text|这个人也有类似的问题：http://apache-spark-user-list.1001560.n3.nabble.com/How-to-use-DataFrame-with-MySQL-td22178.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|138560|您是否已将连接器驱动程序更新为最新版本？另外，在调用load()时是否指定了驱动程序类？|138561|Map<String,+String>+options+=+new+HashMap<String,+String>();
options.put("url",+"jdbc:mysql://localhost:3306/video_rcmd?user=root&password=123456");
options.put("dbtable",+"video");
options.put("driver",+"com.mysql.cj.jdbc.Driver");+//here
DataFrame+jdbcDF+=+sqlContext.load("jdbc",+options);+|code-block|syntax|javascript|138562|在spark/conf/spark-defaults.conf中，您还可以将spark.driver.extraClassPath和spark.executor.extraClassPath设置为MySql驱动程序.jar的路径|138563|entityMap|0|LINK|mutability|MUTABLE|url|http://apache-spark-user-list.1001560.n3.nabble.com/How-to-use-DataFrame-with-MySQL-td22178.html^0|B|2O|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|C|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

This person was having similar issue: <a href="http://apache-spark-user-list.1001560.n3.nabble.com/How-to-use-DataFrame-with-MySQL-td22178.html" rel="noreferrer">http://apache-spark-user-list.1001560.n3.nabble.com/How-to-use-DataFrame-with-MySQL-td22178.html</a>

Have you updated your connector drivers to the most recent version? Also did you specify the driver class when you called load()?

<pre class="lang-java prettyprint-override"><code>Map&lt;String, String&gt; options = new HashMap&lt;String, String&gt;();
options.put("url", "jdbc:mysql://localhost:3306/video_rcmd?user=root&amp;password=123456");
options.put("dbtable", "video");
options.put("driver", "com.mysql.cj.jdbc.Driver"); //here
DataFrame jdbcDF = sqlContext.load("jdbc", options); 
</code></pre>

In spark/conf/spark-defaults.conf, you can also set spark.driver.extraClassPath and spark.executor.extraClassPath to the path of your MySql driver .jar

blocks|key|138574|text|有一个简单的Java技巧可以解决您的问题。您应该指定Class.forName()实例。例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|138575|+val+customers:+RDD[(Int,+String)]+=+new+JdbcRDD(sc,+()+=>+{
+++++++Class.forName("com.mysql.jdbc.Driver")
+++++++DriverManager.getConnection(jdbcUrl)
++++++},
++++++"SELECT+id,+name+from+customer+WHERE+?+<+id+and+id+<=+?"+,
++++++0,+range,+partitions,+r+=>+(r.getInt(1),+r.getString(2)))|code-block|syntax|javascript|138576|检查docs|138577|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-connect-drivermanager.html^0|Q|F|0|0|2|4|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

There exists a simple Java trick to solve your problem. You should specify <code>Class.forName()</code> instance. For example:

<pre><code> val customers: RDD[(Int, String)] = new JdbcRDD(sc, () =&gt; {
 Class.forName("com.mysql.jdbc.Driver")
 DriverManager.getConnection(jdbcUrl)
 },
 "SELECT id, name from customer WHERE ? &lt; id and id &lt;= ?" ,
 0, range, partitions, r =&gt; (r.getInt(1), r.getString(2)))
</code></pre>

Check the <a href="http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-connect-drivermanager.html" rel="nofollow">docs</a>

blocks|key|139513|text|spark驱动程序和executor都需要类路径上的mysql驱动程序，因此请指定|type|unstyled|depth|inlineStyleRanges|entityRanges|data|139514|spark.driver.extraClassPath+=+<path>/mysql-connector-java-5.1.36.jar
spark.executor.extraClassPath+=+<path>/mysql-connector-java-5.1.36.jar|code-block|syntax|javascript|139515|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Both spark driver and executor need mysql driver on class path so specify 

<pre><code>spark.driver.extraClassPath = &lt;path&gt;/mysql-connector-java-5.1.36.jar
spark.executor.extraClassPath = &lt;path&gt;/mysql-connector-java-5.1.36.jar
</code></pre>

blocks|key|136298|text|spark.driver.extraClassPath在客户端模式下不起作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|136299|JVM注意:在客户机模式下，不能直接在应用程序中通过SparkConf设置此配置，因为此时驱动程序|136300|已经启动。相反，请通过--driver-class-path命令行选项或在您的默认属性文件中设置它。|blockquote|136301|136302|136303|在Spark+1.0%2B中，环境变量SPARK_CLASSPATH已弃用。|136304|您应该首先将jdbc驱动程序jars复制到同一本地文件系统路径下的每个执行器中，然后在spark-submit中使用以下选项：|136305|--driver-class-path+"driver_local_file_system_jdbc_driver1.jar:driver_local_file_system_jdbc_driver2.jar"
--class+"spark.executor.extraClassPath=executors_local_file_system_jdbc_driver1.jar:executors_local_file_system_jdbc_driver2.jar"|code-block|syntax|javascript|136306|例如，在TeraData的情况下，您需要terajdbc4.jar和tdgssconfig.jar。|136307|或者，在所有工作节点上修改compute_classpath.sh，Spark文档说：|136308|136309|+JDBC+driver类必须在客户端会话和所有执行器上对原始类加载器可见。这是因为Java的DriverManager类做了一个安全检查，当打开一个连接时，它会忽略所有对原始类加载器不可见的驱动程序。一种方便的方法是修改所有工作节点上的compute_classpath.sh，以包括您的驱动程序JAR。|136310|136311|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|13|8|@]|9|@]|A|$]]|$1|G|3|-4|5|6|7|14|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|15|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|16|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|17|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|18|8|@]|9|@]|A|$P|Q]]|$1|R|3|S|5|6|7|19|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1A|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|W|3|X|5|F|7|1C|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|10|$]]

spark.driver.extraClassPath does not work in client-mode:

<blockquote>
 Note: In client mode, this config must not be set through the SparkConf directly in your application, because the driver JVM has already started at that point. Instead, please set this through the --driver-class-path command line option or in your default properties file.
</blockquote>

Env variable SPARK_CLASSPATH has been deprecated in Spark 1.0+.

You should first copy the jdbc driver jars into each executor under the same local filesystem path and then use the following options in you spark-submit:

<pre><code>--driver-class-path "driver_local_file_system_jdbc_driver1.jar:driver_local_file_system_jdbc_driver2.jar"
--class "spark.executor.extraClassPath=executors_local_file_system_jdbc_driver1.jar:executors_local_file_system_jdbc_driver2.jar"
</code></pre>

For example in case of TeraData you need both terajdbc4.jar and tdgssconfig.jar .

Alternatively modify compute_classpath.sh on all worker nodes, Spark documentation says:

<blockquote>
 The JDBC driver class must be visible to the primordial class loader on the client session and on all executors. This is because Java’s DriverManager class does a security check that results in it ignoring all drivers not visible to the primordial class loader when one goes to open a connection. One convenient way to do this is to modify compute_classpath.sh on all worker nodes to include your driver JARs.
</blockquote>

blocks|key|139532|text|我在集群模式下在Mesos集群上运行作业时也遇到了同样的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|139533|要使用JDBC驱动程序，必须将依赖项添加到系统类路径，而不是框架类路径。我只找到了在集群的每个实例的spark-defaults.conf文件中添加依赖项的方法。|offset|length|style|CODE|139534|要添加的属性为spark.driver.extraClassPath和spark.executor.extraClassPath，路径必须在本地文件系统中。|139535|entityMap^0|0|1E|J|0|7|R|Z|T|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|K|$]]

I had the same problem running jobs over a Mesos cluster in cluster mode.

To use a JDBC driver is necessary to add the dependency to the system classpath not to the framework classpath. I only found the way of doing it by adding the dependency in the file <code>spark-defaults.conf</code> in every instance of the cluster. 

The properties to add are <code>spark.driver.extraClassPath</code> and <code>spark.executor.extraClassPath</code> and the path must be in the local file system.

blocks|key|136335|text|我将jar文件添加到spark-env.sh中的SPARK_CLASSPATH中，它可以工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|136336|export+SPARK_CLASSPATH=$SPARK_CLASSPATH:/local/spark-1.6.3-bin-hadoop2.6/lib/mysql-connector-java-5.1.40-bin.jar|code-block|syntax|javascript|136337|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I add the jar file to the SPARK_CLASSPATH in spark-env.sh, it works.

<pre><code>export SPARK_CLASSPATH=$SPARK_CLASSPATH:/local/spark-1.6.3-bin-hadoop2.6/lib/mysql-connector-java-5.1.40-bin.jar
</code></pre>

blocks|key|2919926|text|在spark+2.2.0中，通过在python脚本中为SparkSession会话添加额外的类路径信息，为我解决了这个问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2919927|++++spark+=+SparkSession+\
++++++++.builder+\
++++++++.appName("Python+Spark+SQL+basic+example")+\
++++++++.config("spark.driver.extraClassPath",+"/path/to/jdbc/driver/postgresql-42.1.4.jar")+\
++++++++.getOrCreate()|code-block|syntax|javascript|2919928|请参阅官方文档https://spark.apache.org/docs/latest/configuration.html|offset|length|2919929|在我的例子中，spark不是从cli命令启动的，而是从django框架https://www.djangoproject.com/启动的。|2919930|entityMap|0|LINK|mutability|MUTABLE|url|https://spark.apache.org/docs/latest/configuration.html|1|https://www.djangoproject.com/^0|0|0|7|1J|0|0|Z|U|1|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|K|3|L|5|6|7|12|8|@]|9|@$I|13|J|14|1|15]]|A|$]]|$1|M|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]|U|$5|P|Q|R|A|$S|V]]]]

With spark 2.2.0, problem was corrected for me by adding extra class path information for SparkSession session in python script :

<pre><code> spark = SparkSession \
 .builder \
 .appName("Python Spark SQL basic example") \
 .config("spark.driver.extraClassPath", "/path/to/jdbc/driver/postgresql-42.1.4.jar") \
 .getOrCreate()
</code></pre>

See official documentation <a href="https://spark.apache.org/docs/latest/configuration.html" rel="noreferrer">https://spark.apache.org/docs/latest/configuration.html</a>

In my case, spark is not launched from cli command, but from django framework <a href="https://www.djangoproject.com/" rel="noreferrer">https://www.djangoproject.com/</a>

blocks|key|2919984|text|在spark+docs中清楚地提到了这些选项：--driver-class-path+postgresql-9.4.1207.jar+--jars+postgresql-9.4.1207.jar|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2919985|我犯的错误是在我的应用程序的jar之后提到了这些选项。|2919986|然而，正确的方法是在spark-submit之后立即指定这些选项：|2919987|spark-submit+--driver-class-path+/somepath/project/mysql-connector-java-5.1.30-bin.jar+--jars+/somepath/project/mysql-connector-java-5.1.30-bin.jar+--class+com.package.MyClass+target/scala-2.11/project_2.11-1.0.jar|2919988|entityMap|0|LINK|mutability|MUTABLE|url|https://spark.apache.org/docs/latest/sql-programming-guide.html#jdbc-to-other-databases^0|N|22|1|A|0|0|0|0|0|5Y|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@$9|W|A|X|1|Y]]|E|$]]|$1|F|3|G|5|6|7|Z|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|10|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]]]

These options are clearly mentioned in <a href="https://spark.apache.org/docs/latest/sql-programming-guide.html#jdbc-to-other-databases" rel="noreferrer">spark docs</a>: <code>--driver-class-path postgresql-9.4.1207.jar --jars postgresql-9.4.1207.jar</code>

The mistake I was doing was mentioning these options after my application's jar. 

However the correct way is to specify these options immediately after spark-submit:

<code>spark-submit --driver-class-path /somepath/project/mysql-connector-java-5.1.30-bin.jar --jars /somepath/project/mysql-connector-java-5.1.30-bin.jar --class com.package.MyClass target/scala-2.11/project_2.11-1.0.jar</code>

blocks|key|138667|text|当我试图在我的windows机器上运行spark-shell命令时，我也遇到了同样的问题。您为驱动程序位置和将要使用的jar传递的路径应该包含在双引号中，否则它会被误解，您将无法获得所需的确切输出。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|138668|您还必须从以下链接安装用于SQL+server的JDBC驱动程序：JDBC+Driver|offset|length|138669|我使用了下面的命令，在我的windows机器上可以很好地工作：|138670|jars+"C:\Program+Files\Microsoft+Driver+6.0+for+SQL+Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"“--spark-Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar--驱动程序类路径"C:\Program+Files\Microsoft+Driver+6.0+for+SQL+shell”|style|BOLD|138671|entityMap|0|LINK|mutability|MUTABLE|url|https://www.microsoft.com/en-ca/download/details.aspx?id=11774^0|0|X|B|0|0|0|2P|31|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@$D|10|E|11|J|K]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]]]

I was facing the same issue when I was trying to run the spark-shell command from my windows machine. The path that you pass for the driver location as well as for the jar that you would be using should be in the double quotes otherwise it gets misinterpreted and you would not get the exact output that you want.

you also would have to install the JDBC driver for SQL server from the link : <a href="https://www.microsoft.com/en-ca/download/details.aspx?id=11774" rel="nofollow noreferrer">JDBC Driver</a>

I have used the below command for this to work fine for me on my windows machine:

spark-shell --driver-class-path "C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar" --jars "C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"

blocks|key|138675|text|简单而简单的方法是将"mysql-connector-java-5.1.47.jar“复制到"spark-2.4.3\jars\”目录中|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|138676|entityMap^0|B|V|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Simple easy way is to copy "mysql-connector-java-5.1.47.jar" into "spark-2.4.3\jars\" directory

So I've been using sbt with assembly to package all my dependencies into a single jar for my spark jobs. I've got several jobs where I was using <code>c3p0</code> to setup connection pool information, broadcast that out, and then use <code>foreachPartition</code> on the RDD to then grab a connection, and insert the data into the database. In my sbt build script, I include 

<pre><code>"mysql" % "mysql-connector-java" % "5.1.33"
</code></pre>

This makes sure the JDBC connector is packaged up with the job. Everything works great.

So recently I started playing around with SparkSQL and realized it's much easier to simply take a dataframe and save it to a jdbc source with the new features in <code>1.3.0</code>

I'm getting the following exception :

<blockquote>
 java.sql.SQLException: No suitable driver found for
 jdbc:mysql://some.domain.com/myschema?user=user&amp;password=password at
 java.sql.DriverManager.getConnection(DriverManager.java:596) at
 java.sql.DriverManager.getConnection(DriverManager.java:233)
</blockquote>

When I was running this locally I got around it by setting

<pre><code>SPARK_CLASSPATH=/path/where/mysql-connector-is.jar
</code></pre>

Ultimately what I'm wanting to know is, why is the job not capable of finding the driver when it should be packaged up with it? My other jobs never had this problem. From what I can tell both <code>c3p0</code> and the dataframe code both make use of the <code>java.sql.DriverManager</code> (which handles importing everything for you from what I can tell) so it should work just fine?? If there is something that prevents the assembly method from working, what do I need to do to make this work?

Spark Unable to find JDBC Driver

文件系统

SQL Server

数据库

Python

Spark 

Java

因此，我一直在使用sbt和assembly将我的所有依赖项打包到一个jar中，用于我的spark作业。我有几个工作，我使用c3p0设置连接池信息，将其广播出去，然后在RDD上使用foreachPartition获取连接，并将数据插入到数据库中。在我的sbt构建脚本中，我包含了"mysql" % "mysql-conne...

问Spark找不到JDBC驱动程序
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Spark找不到JDBC驱动程序EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Spark找不到JDBC驱动程序
EN