error when trying to save pyspark dataframe

Question

I have a python script using pyspark that runs fine when done thru jupyter. When run using spark-submit it for some reason crashes trying to save results with the line

df.write.format('jdbc').options(
    url='jdbc:mysql://{0}/{1}?useServerPrepStmts=false&rewriteBatchedStatements=true'.format(\
        output_server, output_db),\
    driver='com.mysql.jdbc.Driver',\
    dbtable=output_table,\
    user='user',\
    password='xxxx').mode('overwrite').save()

The error being :

Traceback (most recent call last):
  File "/opt/spark-2.1.0-bin-hadoop2.7/sbin/test.py", line 381, in <module>
    password='xxxx').mode('overwrite').save()
  File "/opt/spark-2.1.0-bin-hadoop2.7/python/lib/pyspark.zip/pyspark/sql/readwriter.py", line 548, in save
  File "/opt/spark-2.1.0-bin-hadoop2.7/python/lib/py4j-0.10.4-src.zip/py4j/java_gateway.py", line 1133, in __call__
  File "/opt/spark-2.1.0-bin-hadoop2.7/python/lib/pyspark.zip/pyspark/sql/utils.py", line 63, in deco
    if records_acum:
  File "/opt/spark-2.1.0-bin-hadoop2.7/python/lib/py4j-0.10.4-src.zip/py4j/protocol.py", line 319, in get_return_value
py4j.protocol.Py4JJavaError: An error occurred while calling o55.save.
: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at org.apache.spark.sql.execution.datasources.jdbc.DriverRegistry$.register(DriverRegistry.scala:38)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$6.apply(JDBCOptions.scala:78)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$6.apply(JDBCOptions.scala:78)
    at scala.Option.foreach(Option.scala:257)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:78)
    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:53)
    at org.apache.spark.sql.execution.datasources.DataSource.write(DataSource.scala:426)
    at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:215)
    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:745)

If I try to run this using

/opt/Spark/spark-2.2.0_hadoop-2.7/bin/spark-submit --packages mysql:mysql-connector-java:5.1.40 test.py

then the crash is avoided but the script never finishes, just hangs on the same df.save line. In case it isnt clear, I would like to run the script to completion, saving the data succesfully.

you've got it correct - jdbc mysql driver missed. You need to monitor job to understand why it hang. Maybe not enough resources to get it done. — vvg, Aug 03 '18 at 16:27
there must be enough resources since the job finishes when run in jupyter — jeremy_rutman, Aug 03 '18 at 20:19

Gangadhar Kadam · Answer 1 · 2018-08-03T19:12:47.080

0

Try adding the driver class path to your spark-submit application.

/opt/Spark/spark-2.2.0_hadoop-2.7/bin/spark-submit --driver-class-path=path/to/mysqlconnector.jar test.py

edited Aug 03 '18 at 19:12

answered Aug 03 '18 at 19:05

Gangadhar Kadam

536
1
4
15

I tried with --driver-class-path alone and with --packages, same story (hangs on save) . – jeremy_rutman Aug 06 '18 at 08:28

score 0 · Answer 2 · answered Aug 03 '18 at 21:14

0

I found the following Add jars to a Spark Job - spark-submit it should help resolve your loading problem. Seems like the executor is not able to get the MySQL driver.

answered Aug 03 '18 at 21:14

venBigData

600
1
8
23

error when trying to save pyspark dataframe

2 Answers2