CODEWITHSUNDEEP
mysqlapache-spark
yeyimilk
yeyimilk

Reputation: 614

Insert DataFrame into SQL table with AUTO_INCREMENT column

I have a MySQL table which includes a column that is AUTO_INCREMENT:

CREATE TABLE features (
  id INT NOT NULL AUTO_INCREMENT,
  name CHAR(30),
  value DOUBLE PRECISION
);

I created a DataFrame and wanted to insert it into this table.

case class Feature(name: String, value: Double)
val rdd: RDD[Feature]
val df = rdd.toDF()
df.write.mode(SaveMode.Append).jdbc("jdbc:mysql://...", "features", new Properties)

I get the error, Column count doesn’t match value count at row 1. If I delete the id column it works. How could I insert this data into the table without changing the schema?

Upvotes: 5

Views: 5282

Answers (1)

Daniel Darabos
Daniel Darabos

Reputation: 27455

You have to include an id field in the DataFrame, but its value will be ignored and replaced with the auto-incremented ID. That is:

case class Feature(id: Int, name: String, value: Double)

Then just set id to 0, or any number when you create a Feature.

Upvotes: 4

Related Questions