`from_avro` function

Applies to: check marked yes Databricks SQL Databricks Runtime 15.4 and later

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: An optional 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

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

> SELECT from_avro(to_avro(5, '{ "type" : "int" }'), '{ "type" : "int" }');
  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"}]}');
  {"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}

from_avro function