from_avro function

Applies to: Databricks Runtime 16.0 and above

Returns a struct value with the avroBin and jsonSchemaStr.

Syntax

from_avro(avroBin, jsonSchemaStr, options )

Arguments

• avroBin: A BINARY expression specifying a row of Avro data.
• avroSchemaSpec: The target schema in JSON format. It must match the schema encoded in avroBin as specified in to_avro().
• options: A MAP<STRING,STRING> literal specifying directives.

Returns

A STRUCT with field names and types based on the result of schema_of_json(jsonStr).

avroBin must be well-formed with respect to the avroSchemaSpec and options or Databricks raises an exception.

Notes

The following options are most common supported:

Option	Value	Description
'mode'	'PERMISSIVE', 'FAILFAST'	In PERMISSIVE mode, any corrupted objects or fields in an object are set to NULL instead of raising an error.
compression	'uncompressed', 'snappy', 'deflade', 'bzip2', 'xz', 'zstandard'	Specifies the compression codec used to encode the Avro data.

For more options see Read and write streaming Avro data.

Examples

SQL

> SELECT from_avro(to_avro(5), '{ "type" : "int" }', NULL:MAP<STRING, STRING>);
  5

> SELECT from_avro(to_avro(5, '{ "type" : "int" }'), '{ "type" : "int" }', NULL:MAP<STRING, STRING>);
  5

> SELECT from_avro(to_avro(named_struct('num', 5, 'txt', 'hello')), '{ "type" : "record", "name": "my_record", "fields": [{ "name": "num", "type": "int"}, { "name": "txt", "type": "string"}]}', NULL:MAP<STRING, STRING>);
  {"num":5,"txt":"hello"}

> SELECT from_avro(to_avro(named_struct('num', 5, 'txt', 'hello')),
                   '{ "type" : "record", "name": "my_record", "fields": [{ "name": "num", "type": "int"}, { "name": "txt", "type": "double"}]}',
                   map('mode', 'failfast'));
  Error: Avro data is not valid for the specified schema.

> SELECT from_avro(to_avro(named_struct('num', 5, 'txt', 'hello')),
                   '{ "type" : "record", "name": "my_record", "fields": [{ "name": "num", "type": "int"}, { "name": "txt", "type": "double"}]}',
                   map('mode', 'permissive'));
  {"num":null,"txt":null}

Related functions

• from_avro function