Csv Functions

This page lists all csv functions available in Spark SQL.

from_csv

from_csv(csvStr, schema[, options]) - Returns a struct value with the given csvStr and schema.

Arguments:

  • csvStr - A string expression of a single CSV record.
  • schema - A string literal or invocation of schema_of_csv describing the schema.
  • options - An optional map literal of string key-value pairs specifying CSV parsing options controlling how csvStr is parsed. Accepts the same options as the CSV data source.

Examples:

> SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');
 {"a":1,"b":0.8}
> SELECT from_csv('26/08/2015', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
 {"time":2015-08-26 00:00:00}

Note:

A CHAR csvStr is not rtrimmed. Padding is parsed as trailing spaces and can create extra fields or attach to the last unquoted field; use ignoreTrailingWhiteSpace to trim the last field. CHAR/VARCHAR in schema is accepted when CHAR/VARCHAR are first-class (spark.sql.charVarchar.standardSemantics.enabled or spark.sql.preserveCharVarcharTypeInfo): fields are assigned (CHAR padded; overflow is a bad record). spark.sql.legacy.charVarcharAsString reads them as STRING. Otherwise the schema raises UNSUPPORTED_CHAR_OR_VARCHAR_AS_STRING.

Since: 3.0.0


schema_of_csv

schema_of_csv(csv[, options]) - Returns schema in the DDL format of CSV string.

Arguments:

  • csv - A foldable string expression of a single CSV record.
  • options - An optional map literal of string key-value pairs specifying CSV parsing options that control schema inference. Accepts the same options as the CSV data source.

Examples:

> SELECT schema_of_csv('1,abc');
 STRUCT<_c0: INT, _c1: STRING>

Note:

A CHAR csv argument is not rtrimmed. When CHAR is first-class, padding can change the inferred field count and types; for example CAST('1' AS CHAR(3)) infers DOUBLE with the default comma delimiter.

Since: 3.0.0


to_csv

to_csv(expr[, options]) - Returns a CSV string with a given struct value

Arguments:

  • expr - A struct expression to convert into a CSV string.
  • options - An optional map literal of string key-value pairs specifying CSV generation options controlling how the struct is rendered. Accepts the same options as the CSV data source.

Examples:

> SELECT to_csv(named_struct('a', 1, 'b', 2));
 1,2
> SELECT to_csv(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
 26/08/2015

Since: 3.0.0