to_avro function
Applies to: 
Databricks Runtime 16.0 and later
Returns a Avro binary value with the specified input value.
Syntax
to_avro(expr [, avroSchemaSpec] )
Arguments
expr: An expression.avroSchemaSpec: An optional target schema in JSON format. If specified it must match theexprtype as specified in Notes.
Returns
An Avro encoded BINARY.
Notes
The mapping of SQL types to Avro types is as follows:
| SQL type | Avro schema |
|---|---|
VOID |
{ "type" : "null" } |
BOOLEAN |
{ "type" : "boolean" } |
TINYINT |
{ "type" : "int" } |
SMALLINT |
{ "type" : "int" } |
INT |
{ "type" : "int" } |
BIGINT |
{ "type" : "long" } |
DECIMAL(p, s) |
{ "type": "fixed", "name": "a", logicalType": "decimal", "size": (p+1)/2, precision": p, "scale": s } |
FLOAT |
{ "type" : "float" } |
DOUBLE |
{ "type" : "float" } |
STRING |
{ "type" : "string" } |
DATE |
{ "type" : "int", "logicalType" : "date" } |
TIMESTAMP |
{ "type" : "long" } as microseconds since 1970-01-01 00:00:00.000000 |
TIMESTAMP_NTZ |
{ "type" : "long" } as microseconds since 1970-01-01 00:00:00.000000 |
YEAR MONTH INTERVAL |
{ "type" : "long" } as months |
DAY TIME INTERVAL |
{ "type" : "long" } as microseconds |
BINARY |
{ "type" : "bytes" } |
STRUCT<field1 type1, ...> |
{ "type" : "record", "name": "struct_name", "fields": [ { "name" : "field1", "type" : ... }, ... ] } |
ARRAY<type> |
{ "type" : "array", "items": { "type" : ... } |
MAP<STRING, valueType> |
{ "type" : "map", "keyType": { "type" : ... }, "valueType": { "type" : ... } } |
MAP<nonStringType, valueType> |
Not supported |
VARIANT |
Not supported |
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"}