When reading a CSV is there an option to start on row 2 or below?

user_5212614 0
Viewed 525

I'm reading a bunch of CSV files into a dataframe using the sample code below. 

val df = spark.read.format("csv")
   .option("sep","|")
   .option("inferSchema","true")
   .option("header","false")
   .load("mnt/rawdata/corp/ABC*.gz")

I'm hoping there is a way to start on row 2 or below, because row 1 contains some basic metadata about these files, and the first row has 4 pipe characters, so Spark thinks the file has 4 columns, but it actually has over 100 columns in the actual data.

I tried playing with the inferSchema and header but I couldn't get anything to work.

pyspark databricks apache-spark azure
2 Answers

If the first line in CSV doesnt match actual column count and names, you may need to define your schema by hand, and then try this combination:

val df = spark.read.format("csv")
   .option("sep","|")
   .option("inferSchema","false")
   .option("header","true")     
   .schema(mySchema)
   .option("enforceSchema","true")
   .load(...

Full list of CSV options.

Note that for Spark 2.3 and above, you can use a shorthand, SQL-style notation for schema definition -- simple string "column1 type1, column2 type2, ...".

If however your header has more than one line, you will probably be forced to ignore all "errors" by using additional option .option("mode","DROPMALFORMED").

user_6387640

You are right! You need to define a custom schema! I ended up going with this.

import org.apache.spark.sql.SQLContext
import org.apache.spark.sql.types.{StructType, StructField, StringType, IntegerType};
import org.apache.spark.sql.functions.input_file_name

val customSchema = StructType(Array(
    StructField("field1", StringType, true),
    StructField("field2", StringType, true),
    StructField("field3", StringType, true),
    StructField("field4", StringType, true),
    StructField("field5", StringType, true),
    StructField("field6", StringType, true),
    StructField("field7", StringType, true)))

val df = sqlContext.read
    .format("com.databricks.spark.csv")
    .option("header", "false")
    .option("sep", "|")
    .schema(customSchema)
    .load("mnt/rawdata/corp/ABC*.gz")
    .withColumn("file_name", input_file_name())
user_52126140
Related