Extracting and Converting Avro Values

Extracting Avro Values

subvalue_mapping specifies which subvalues are extracted and the column or variable to which each one is assigned.

LOAD DATA uses the ::-separated list of names in a subvalue_path to perform successive field name or union branch type name lookups in nested Avro records or unions. subvalue_path may not be used to extract elements of Avro arrays or maps. The path % refers to the entire Avro value being processed. Leading %:: may be omitted from paths which are otherwise non-empty.

If a path can’t be found in an input Avro value, then:

  • If a prefix of the path matches a record whose schema has no field matching the next name in the path, then LOAD DATA will terminate with an error.

  • If a prefix matches a union whose schema has no branch matching the next name, then LOAD DATA will terminate with an error.

  • If a prefix matches a union whose schema has a branch matching the next name, but that branch isn’t the selected branch in that instance of the union schema, then Avro null will be extracted instead and LOAD DATA will continue.

Path components naming union branches must use the two-part fullname of the branch’s type if that type is in a namespace.

Path components containing whitespace or punctuation must be surrounded by backticks.

Array and map elements may be indirectly extracted by applying JSON_EXTRACT_<type> in a SET clause.

For example, consider two Avro records with the union schema:

[
"int",
{ "type" : "record",
"name" : "a",
"namespace" : "n",
"fields" : [{ "name" : "f1",
"type" : "int" }]
}
]

The paths %::`n.a`::f1 and `n.a`::f1 will both extract 1 from an instance of this schema whose JSON encoding is {"n.a":{"f1":1}}.

They will extract null from an instance whose encoding is {"int":2}.

The paths %::int and int will extract 2 from the second instance and null from the first.

Converting Avro Values

Before assignment or set clause evaluation, the Avro value extracted according to a subvalue_path is converted to an unspecified SQL type which may be further explicitly or implicitly converted as if from a SQL string whose value is as follows:

Avro Type

Converted Value

null

SQL NULL

boolean

"1"/"0"

int

The string representation of the value

long

The string representation of the value

float

SQL NULL if not finite. Otherwise, a string convertible without loss of precision to FLOAT

double

SQL NULL if not finite. Otherwise, a string convertible without loss of precision to DOUBLE

enum

The string representation of the enum.

bytes

Verbatim, from input bytes

string

Verbatim, from input bytes

fixed

Verbatim, from input bytes

record

The JSON encoding of the value

map

The JSON encoding of the value

array

The JSON encoding of the value

union

The JSON encoding of the value

logicalType attributes are ignored and have no effect on conversion.

Last modified:

Was this article helpful?