Databricks Community

Malthe · Wednesday

Using read_files to read Avro-formatted blobs from an external location, I get the error:

> Malformed records are detected in record parsing: Event 0. Parse Mode: FAILFAST. To process malformed records as null result, try setting the option 'mode' as 'PERMISSIVE'. SQLSTATE: 22023

Now, I have tried to configure this mode using "mode => 'PERMISSIVE'". Somehow it doesn't seem to kick in:

Parse Mode: FAILFAST. To process malformed records as null result, try setting the option 'mode' as 'PERMISSIVE'.

According to the documentation, mode should be a supported option for Avro. Note that the documentation for read_files provides the link to these format-specific options.

szymon_dybczak · Wednesday

Hi @Malthe ,

read_files accepts format-specific options, but the Avro option table has an important scope column. For mode, that scope is only from_avro, not Avro file reads. Spark documents it as:

"mode … allows you to specify parse mode for function from_avro"

As a workaround, if your blobs are raw Avro-encoded records/messages, rather than standard Avro Object Container Files, read them as binary and use from_avro, where mode = PERMISSIVE really is supported:

SELECT
from_avro(
content,
'<avro schema>',
map('mode', 'PERMISSIVE')
) AS decoded
FROM read_files(
's3://bucket/path',
format => 'binaryFile'
);

View solution in original post

szymon_dybczak · Wednesday

Hi @Malthe ,

read_files accepts format-specific options, but the Avro option table has an important scope column. For mode, that scope is only from_avro, not Avro file reads. Spark documents it as:

"mode … allows you to specify parse mode for function from_avro"

As a workaround, if your blobs are raw Avro-encoded records/messages, rather than standard Avro Object Container Files, read them as binary and use from_avro, where mode = PERMISSIVE really is supported:

SELECT
from_avro(
content,
'<avro schema>',
map('mode', 'PERMISSIVE')
) AS decoded
FROM read_files(
's3://bucket/path',
format => 'binaryFile'
);

Malthe · Wednesday

Thanks for the workaround!

That said, I do think this is if nothing else, a documentation issue with Databricks.

Documentation says:
> For options specific to each file format (JSON, CSV, XML, Parquet, Avro, text, ORC, and binary), see DataFrameReader options.

The first line here is:
> Use these options with DataFrameReader.option(), DataFrameReader.options(), read_files, COPY INTO, and Auto Loader to control how Databricks reads data files.

I don't see anything about a "scope".

For mode specifically:

> Parser mode for handling corrupt records. FAILFAST throws an exception. PERMISSIVE sets malformed fields to null. DROPMALFORMED silently drops bad records.