Schema¶

The schema block decides how API responses become columns.

Strict¶

Strict mode uses the OpenAPI schema to produce typed columns. Nested objects are flattened to flatten-depth levels. Anything deeper becomes a JSON string, which parse_json() can read if you need it.

Columns come from the schema of each endpoint's 200 (or default) response, under application/json, application/json with parameters such as ; charset=utf-8, any +json type such as application/hal+json, or */*, in that order of preference. A table whose endpoint has no such response fails when it's queried, naming the content types the spec does offer: in strict mode it would have no columns. Variant mode needs no schema, so there the table still loads. A source reading NDJSON takes its columns from the record under the NDJSON response instead; see Response formats.

Every column is nullable, including fields the spec marks required. A required field can still be null in the data: inside an optional parent object that's missing, when it's also declared nullable: true, or when the API breaks its own spec. A non-nullable column would let Spark drop IS NULL filters on it, and with them those rows.

Column types¶

An integer without a format has no size limit in OpenAPI, and IDs and epoch milliseconds routinely pass the 32-bit range, so only an explicit int32 is an int. A bigint holds any value up to 2^63 − 1.

Columns come from the fields a spec declares. A field the API sends but the spec doesn't declare is skipped, including when the spec allows extra fields with additionalProperties.

Values that don't match their column¶

APIs don't always send what their spec says. A value with exactly one reading in its column's type is converted:

Anything else that doesn't fit or doesn't parse becomes NULL, rather than failing the read. That includes values that would need a guess:

• a fraction in an integer column, as a number or a string: 4.2, "3.0". A JSON number with nothing after the point, such as 3.0, is read as 3.
• an int32 value out of range. The column's type is fixed when the query is planned.
• an epoch number in a timestamp column. Seconds and milliseconds would both give a plausible date.
• an empty string, or any other text, that isn't a date or timestamp.

The first such NULL in each column, in each task, is logged as a warning, naming the column and the value. Every one is counted: the scan's node in the SQL tab of the Spark UI shows values replaced with NULL, and values converted beside it, so you can see what was touched. A JSON null or a missing field is NULL without being counted.

array-handling controls array fields:

Variant¶

schema {
  mode = "variant"
}

-- One column, `value`, of type VARIANT
SELECT value FROM api.default.users LIMIT 5;

-- variant_get() reads typed fields, with no parse_json needed
SELECT
  variant_get(value, '$.name', 'STRING') AS name,
  variant_get(value, '$.email', 'STRING') AS email
FROM api.default.users;

-- Nested paths work too
SELECT
  variant_get(value, '$.address.city', 'STRING') AS city,
  variant_get(value, '$.stats[0].value', 'INT') AS first_stat
FROM api.default.users;

examples/pokeapi/pokeapi-variant.conf is a working variant-mode config.

Parent-child tables aren't supported in variant mode: loading one fails, saying so. The source's other tables still read. Use strict mode for a source with joins.