CSV Files
Supported options
Read files
path: location of files. Accepts standard Hadoop globbing expressions. To read a directory of CSV files, specify a directory.header: when set totrue, the first line of files name columns and are not included in data. All types are assumed to be string. Default value isfalse.sep: the column delimiter. By default,, but can be set to any character.quote: the quote character. By default", but can be set to any character. Delimiters inside quotes are ignored.escape: the escape character. By default\, but can be set to any character. Escaped quote characters are ignored.parserLib: by default iscommons. Can be set tounivocityto use that library for CSV parsing.mode: the parsing mode. By default it isPERMISSIVE. Possible values are:PERMISSIVE: try to parse all lines: nulls are inserted for missing tokens and extra tokens are ignored.DROPMALFORMED: drop lines that have fewer or more tokens than expected or tokens which do not match the schema.FAILFAST: abort with aRuntimeExceptionif any malformed line is encountered.
charset: the character set. By defaultUTF-8, but can be set to other valid charset names.inferSchema: automatically infer column types. It requires one extra pass over the data and is false by default.comment: skip lines beginning with this character. Default is#. Disable comments by setting this to null.nullValue: string that indicates a null value, any fields matching this string will be set as nulls in the DataFrame.dateFormat: string that indicates the date format to use when reading dates or timestamps. Custom date formats follow the formats at java.text.SimpleDateFormat. This applies to bothDateTypeandTimestampType. By default it is null, which means try to parse times and date byjava.sql.Timestamp.valueOf()andjava.sql.Date.valueOf().
Write files
The package also supports saving simple (non-nested) DataFrame. When writing files the API accepts the following options:
path: location of files.header: when set to true, the header (from the schema in the DataFrame) is written at the first line.sep: the column delimiter. By default,, but can be set to any character.quote: the quote character. By default", but can be set to any character. This is written according toquoteMode.escape: the escape character. By default\, but can be set to any character. Escaped quote characters are written.nullValue: string that indicates a null value, nulls in the DataFrame will be written as this string.dateFormat: string that indicates the date format to use writing dates or timestamps. Custom date formats follow the formats atjava.text.SimpleDateFormat. This applies to bothDateTypeandTimestampType. If nodateFormatis specified, thenyyyy-MM-dd HH:mm:ss.S.codec: compression codec to use when saving. Should be the fully qualified name of a class implementingorg.apache.hadoop.io.compress.CompressionCodecor one of case-insensitive short names (bzip2,gzip,lz4, andsnappy). Defaults to no compression.quoteMode: when to quote fields (ALL,MINIMAL(default),NON_NUMERIC,NONE), see Quote Modes.
Examples
These examples use the diamonds dataset available as a Databricks dataset. Specify the path to the dataset as well as any options that you would like.
Read file in any language
This notebook shows how to a read file, display sample data, and print the data schema using Scala, R, Python, and SQL.
Specify schema
When the schema of the CSV file is known, you can specify the desired schema to the CSV reader with the schema option.
Verify correctness of the data
When reading CSV files with a specified schema, it is possible that the actual data in the files does not match the specified schema. For example, a field containing name of the city will not parse as an integer. The consequences depend on the mode that the parser runs in:
PERMISSIVE(default): nulls are inserted for fields that could not be parsed correctlyDROPMALFORMED: drops lines that contain fields that could not be parsedFAILFAST: aborts the reading if any malformed data is found
To set the mode, use the mode option.
val diamonds_with_wrong_schema_drop_malformed = sqlContext.read.format("csv").option("mode", "PERMISSIVE")
In the PERMISSIVE mode it is possible to inspect the rows that could not be parsed correctly. To do that, you can add _corrupt_record column to the schema.