Description: C:\Users\Chris\Desktop\Manning\Images\cover images\Spark-shadow.jpg

Complex ingestion from CSV

From Spark in Action, 2nd Ed. by Jean Georges Perrin

This is the first in a series of 4 articles on the topic of ingesting data from files with Spark. This section deals with ingesting data from CSV.


Save 37% off Spark in Action, 2nd Ed. Just enter code fccperrin into the discount code box at checkout at manning.com.


CSV[1] is probably the most popular data-exchange format around. Due to its age and wide use, this format has many variations on its core structure: separators aren’t always commas, some records may span over multiple lines, there are various ways of escaping the separator, and many more creative considerations. When your customer tells you “I’ll send you a CSV file,” you can certainly nod, smile, and slowly start to freak out on the inside.

Fortunately for you, Spark offers a variety of options for ingesting those CSV files. Ingesting CSV is easy and schema inference is a powerful feature.

Let’s have a look at more advanced examples with more options that illustrate the complexity of CSV files in the outside world. You’ll first look at the file you’ll ingest, and understand its specifications. You’ll then have a look at the result and finally build the mini-application to achieve the result. This pattern repeats for each format.

Figure 1 illustrates the process you’re going to implement.


Figure 1 Spark is ingesting a complex CSV-like file with non-default options. After ingesting the file, the data is in a dataframe, from which you can display records and the schema – in this case the schema is inferred by Spark.


In listing 1 is an excerpt of a CSV file with two records and a header row. Note that CSV has become a generic term: nowadays, the C means more “character” than comma. You’ll find files where values are separated by semicolons, tabs, pipes (|), and more. For the purist, the acronym may matter, but for Spark, they all fall into the same category.

A few observations:

  • The file isn’t comma-separated but semicolon-separated.
  • I manually added the end of paragraph symbol () to show the end of line. It isn’t in the file.
  • If you look at the record with id 4, there’s a semicolon in the title, which breaks the parsing, therefore this field is surrounded by stars. Keep in mind that this is a stretch example to illustrate some of Spark features.
  • If you look at the record with id 6, you’ll see that the title is split over two lines: there’s a carriage return after Language? And before An.

Listing 1 Complex CSV file (abstract of books.csv)

 
 id;authorId;title;releaseDate;link ¶
 4;1;*Harry Potter and the Chamber of Secrets: The Illustrated Edition (Harry Potter; Book 
        2)*;10/4/16;http://amzn.to/2kYhL5n ¶
 6;2;*Development Tools in 2006: any Room for a 4GL-style Language? ¶
 An independent study by Jean Georges Perrin, IIUG Board 
        Member*;12/28/16;http://amzn.to/2vBxOe1 ¶
 

Desired output

Listing 2 shows a possible output. I added the paragraph mark to illustrate the new line, as long records aren’t easy to read…

Listing 2 Complex CSV file (abstract of books.csv)

 
 Excerpt of the dataframe content:
 +---+--------+------------------------------------------------------------------------------------------+-----------+-----------------------+¶
 | id|authorId|                                                                                    title|releaseDate|                  link|¶
 +---+--------+------------------------------------------------------------------------------------------+-----------+-----------------------+¶
 …
 |  4|       1|   Harry Potter and the Chamber of Secrets: The Illustrated Edition (Harry Potter; Book 2)|    10/4/16|http://amzn.to/2kYhL5n|¶
 …
 |  6|       2|Development Tools in 2006: any Room for a 4GL-style Language? ¶
 An independent study by...|   12/28/16|http://amzn.to/2vBxOe1|¶ //  
 …
 +---+--------+------------------------------------------------------------------------------------------+-----------+-----------------------+¶
 only showing top 7 rows
  
 Dataframe's schema:
 root
  |-- id: integer (nullable = true)                              
  |-- authorId: integer (nullable = true)                        
  |-- title: string (nullable = true)
  |-- releaseDate: string (nullable = true)                      
  |-- link: string (nullable = true)
 

❶   See that our release date is seen as a string, not a date!

 The line break that was in your CSV file is still here.

The datatype is an integer: in CSV files, everything is a string, but Spark makes an educated guess!

Code

To achieve the result in listing 2, you’ll have to code something similar to listing 3: first get a session, then configure and run the parsing operation in one call using method chaining. Finally, show some records and display the schema of the dataframe.

Listing 3 ComplexCsvToDataframeApp.java

 
 package net.jgp.books.sparkWithJava.ch07.lab_100.csv_ingestion;
  
 import org.apache.spark.sql.Dataset;
 import org.apache.spark.sql.Row;
 import org.apache.spark.sql.SparkSession;
  
 public class ComplexCsvToDataframeApp {
  
   public static void main(String[] args) {
     ComplexCsvToDataframeApp app = new ComplexCsvToDataframeApp();
     app.start();
   }
  
   private void start() {
     SparkSession spark = SparkSession.builder()
         .appName("Complex CSV to Dataframe")
         .master("local")
         .getOrCreate();
  
     Dataset<Row> df = spark.read().format("csv")  
         .option("header", "true")                 
         .option("multiline", true)                
         .option("sep", ";")                       
         .option("quote", "*")                     
         .option("dateFormat", "M/d/y")            
         .option("inferSchema", true)              
         .load("data/books.csv");
  
     System.out.println("Excerpt of the dataframe content:");
     df.show(7, 90);
     System.out.println("Dataframe's schema:");
     df.printSchema();
   }
 }
 

The format we want to ingest is CSV

❷   The first line of your CSV file is a header line

Some of our records are splitting over multiple lines, note that you can either use a string or a Boolean, making it easier to load values from a configuration file

The separator between values is a semicolon (;)

The quote character is a star (*)

The date format matches the month/day/year format, as commonly used in the United States (see below)

Spark infers (guesses) the schema (see below)

As you probably guessed, you need to know what your file looks like (separator character, escape character, and so on) before you can configure the parser. Spark won’t infer those, this is part of the contract that comes (or, as in most of the times, you have to guess) with your CSV files.

The schema inference feature is a pretty neat one; but, as you can see here, it didn’t infer that the releaseDate column was a date. One way to tell Spark that it’s a date is to specify a schema.


And that’s where we’re going to stop for this section. For more, check out part 2. If you’re interested in some more general information about the book, check it out on liveBook here and see this slide deck.


[1] For more information, look at Wikipedia’s page on CSV at https://en.wikipedia.org/wiki/Comma-separated_values.  In the history section, you’ll learn that CSV has been around for quite some time.