Schema inference for datasets in ES|QL Data Federation

A dataset's schema defines the columns and data types that queries can use. A dataset can get its schema in three ways:

  • Infer every column: Columns and types are discovered from the dataset's files. Each file's format determines how its schema is found, and the schema resolution strategy combines schemas from multiple files.
  • Declare every column: Only the columns you declare are available, and nothing is inferred.
  • Declare some columns and infer the rest: Declared columns override what's inferred for them, and every other column is still inferred.
Warning

This feature is experimental. It is not intended for production use and there are no guarantees around performance, scale, or stability in this release.

The following table shows where each supported file format gets its schema and how it names columns:

Format Schema source Column names
Parquet File metadata Field names. Nested fields use dotted names, such as user.id.
CSV and TSV with a header row Sampled rows Header names
CSV and TSV without a header row Sampled rows col0, col1, and so on, by position. The column_prefix setting controls the prefix.
NDJSON Sampled rows JSON keys. Nested objects use dotted names, such as user.id.

Parquet metadata can also contain column statistics and bloom filters that let queries skip irrelevant data. For text formats, use schema_sample_size for CSV and TSV or NDJSON to control how many rows or lines are sampled.

When a dataset spans multiple files, schema_resolution controls how differences between their schemas are reconciled.

The default is first_file_wins. Datasets created before first_file_wins became the default keep using union_by_name when they have no stored schema_resolution value.

The default is union_by_name.

The following table compares the available strategies:

Strategy Behavior Use when
first_file_wins Reads the schema from the first file after file ordering, and reads later files with that schema. Columns that exist only in later files aren't included. Only one file's schema is inspected. Files share a schema, and you want the least schema-discovery work.
union_by_name Inspects every file and merges columns by name. Missing columns contain null values. Compatible types are widened, and incompatible types become keyword. Files can gain or lose columns, and those differences shouldn't fail the query.
strict Inspects every file and requires the same schema, apart from nullability. Schema drift should fail the query.

A type mismatch in a later Parquet file doesn't fail the query. If a column's type can't be read as the type from the first file, that column contains null values for the file, and the response includes a warning. The error_mode setting doesn't control this case.

When schema_resolution is first_file_wins, the schema comes from the first file after the discovered files are ordered. Files are ordered after any partition filters prune the listing. Use file_sort_by to choose how files are ordered, and file_order to take the first or last file. The other strategies reject these settings, because they inspect every file.

The following table shows which file_sort_by value to use:

Schema source file_sort_by value
First or last file in declaration or listing order list (default)
First or last file by path name
Oldest or newest file mtime

File order controls schema selection, not query row order. For example, LIMIT does not restrict a query to rows from the file that supplied the schema.

The default list and asc combination preserves the order of files in a comma-separated resource. Put a dedicated schema file first to select it without reading every file footer:

				PUT /_query/dataset/logs
					{
  "data_source": "prod_s3",
  "resource": "s3://logs/_schema.parquet,s3://logs/events/**/*.parquet",
  "settings": {
    "schema_resolution": "first_file_wins"
  }
}

The schema file can contain no rows as long as its Parquet footer or CSV header contains the complete column set. Set file_order to desc to use the last declared file instead.

For a resource that contains only a glob, list preserves the storage provider's listing order. Amazon S3, Azure Blob Storage, and Google Cloud Storage return keys in lexicographic order. A local directory is not necessarily sorted, so use file_sort_by: name when local schema selection must be stable.

Set file_sort_by to name to sort by path independently of declaration or provider order. For example, the following dataset uses the lexicographically greatest date path as its schema source:

				PUT /_query/dataset/logs
					{
  "data_source": "prod_s3",
  "resource": "s3://logs/events/dt=*/*.parquet",
  "settings": {
    "schema_resolution": "first_file_wins",
    "file_sort_by": "name",
    "file_order": "desc"
  }
}

Set file_sort_by to mtime to select a schema based on object modification time. For example, the following dataset uses the most recently modified object:

				PUT /_query/dataset/logs
					{
  "data_source": "prod_s3",
  "resource": "s3://logs/events/**/*.parquet",
  "settings": {
    "schema_resolution": "first_file_wins",
    "file_sort_by": "mtime",
    "file_order": "desc"
  }
}

Files without a modification time sort as the oldest. Equal modification times use the path in ascending order as a tie-breaker. Object stores expose modification time, not creation time. Copying an object to another prefix gives the copy a new modification time. Prefer name when the object path already encodes the relevant time.

By default, a dataset's schema is inferred from its files. To control column names and types, add an optional mappings block to the create or update request.

You can also declare columns in the Mapping step when you add a dataset in Kibana.

The following example declares the complete schema, renames the physical event_time column to @timestamp, and supplies its date format:

				PUT /_query/dataset/access_logs
					{
  "data_source": "prod_s3_logs",
  "resource": "s3://logs-bucket/access/**/*.csv",
  "mappings": {
    "dynamic": false,
    "properties": {
      "@timestamp": {
        "type": "date",
        "path": "event_time",
        "format": "yyyy-MM-dd HH:mm:ss"
      },
      "request_id": { "type": "keyword" },
      "service": { "type": "keyword" },
      "status_code": { "type": "integer" }
    }
  }
}

The mappings block supports the following properties:

  • properties: Columns keyed by their logical name. Each column requires a type.
    • path: Optional physical column name. Use it to expose a file column under a different logical name, including renaming a timestamp column to @timestamp.
      • To keep a file column whose name matches a metadata name, rename it here before requesting that name through METADATA.
    • format: Optional date parsing pattern for a column with type date.
  • dynamic: Controls undeclared columns. The default, true, overlays the declared columns on the inferred schema. Set it to false to treat the declaration as the complete schema, skip schema inference, and leave undeclared columns unavailable to queries.

A mappings block can't include _id. A create or update request that contains one is rejected.

Each declared column is read from one file column:

  • With path: The file column named by path.
  • Without path: The file column with the same name as the declared column.

Matching is exact and case-sensitive, and it works the same way whether dynamic is true or false. File columns are named as described in Schema sources by file format. Two formats need extra care:

  • CSV and TSV without a header row: Columns are named by position. To read the third field as status_code, set its path to col2.
  • NDJSON: A dotted name such as user.id matches either a nested key or a flat key with that name.

When a declared column can't be found, the result depends on dynamic:

  • dynamic: false: The column reads as null for each file that lacks it, and the response includes a warning.
  • dynamic: true: For Parquet and for CSV and TSV with a header row, the query fails if the column isn't in the inferred schema. For NDJSON and for CSV and TSV without a header row, the column reads as null, because their schemas come from a sample.

For Parquet, a declared type must match the file's type or be one it can be converted to. An incompatible type makes the query fail. With dynamic: false, this check uses one file. If another file has a type that can't be read as the declared type, that column reads as null for that file, and the response includes a warning.