Exception in thread "main" java.sql.SQLException: No suitable driver

spark寫入結果集至MySQL時報以下錯誤資訊，記錄下，供各位同行快速解決問題

spark dataframe結果資料儲存至MySQL時報以下錯誤資訊，而讀取時沒有問題

錯誤1：驅動找不到

Exception in thread "main" 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:85)
	at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions$$anonfun$7.apply(JDBCOptions.scala:85)
	at scala.Option.getOrElse(Option.scala:121)
	at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:84)
	at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.<init>(JDBCOptions.scala:35)
	at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:60)
	at org.apache.spark.sql.execution.datasources.SaveIntoDataSourceCommand.run(SaveIntoDataSourceCommand.scala:45)
	at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:70)
	at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:68)
	at org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:86)
	at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:131)
	at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:127)
	at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeQuery$1.apply(SparkPlan.scala:155)
	at org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:151)
	at org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:152)
	at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:127)
	at org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:80)
	at org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:80)
	at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:656)
	at org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:656)
	at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:77)
	at org.apache.spark.sql.DataFrameWriter.runCommand(DataFrameWriter.scala:656)
	at org.apache.spark.sql.DataFrameWriter.saveToV1Source(DataFrameWriter.scala:273)
	at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:267)
	at com.bdt.doep.dhe.ba.huitui.Main.run(Main.scala:124)
	at com.bdt.doep.dhe.ba.huitui.Main$.main(Main.scala:140)
	at com.bdt.doep.dhe.ba.huitui.Main.main(Main.scala)
	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 org.apache.spark.deploy.JavaMainApplication.start(SparkApplication.scala:52)
	at org.apache.spark.deploy.SparkSubmit$.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:904)
	at org.apache.spark.deploy.SparkSubmit$.doRunMain$1(SparkSubmit.scala:198)
	at org.apache.spark.deploy.SparkSubmit$.submit(SparkSubmit.scala:228)
	at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:137)
	at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)
           

之前以為是jar包找不到，但是如果是jar找不到，那麼寫入也應該找不到才是，故排除此問題，最後經過比對分析發現存儲時少了驅動資訊參數

.option(“driver”,“com.mysql.jdbc.Driver”)

錯誤二：類型轉換錯誤，df中為text等類型需要轉換

錯誤資訊：

Exception in thread "main" org.apache.spark.sql.AnalysisException: Column "product_type" not found in schema Some(StructType(StructField(txt_code,StringType,true), StructField(order_record_code,StringType,true), StructField(business_type,StringType,true), StructField(cust_id,StringType,true), StructField(net_cash_account,StringType,true), StructField(to_account_date,LongType,false), StructField(data_state,StringType,true), StructField(groupCol,StringType,true), StructField(goods_name,StringType,true)));
           

解決方案：

1、

.option(“createTableColumnTypes”, “txt_code varchar(200)”)

.option(“createTableColumnTypes”, “order_record_code varchar(200)”)

.option(“createTableColumnTypes”, “business_type varchar(200)”)

2、 如果添加以上後，還是報錯，請确認mysql 輸出表是否包含此字段，如果沒有，則需要添加，或者删除mysql輸出表資訊，因為程式報完後，會自動建立