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:

logicalType attributes are ignored and have no effect on conversion.