from_avro function

  • Article

Applies to: check marked yes Databricks SQL check marked yes 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}