haochunchang commented on issue #25903:
URL: https://github.com/apache/datafusion/issues/25903#issuecomment-5979634968
## Proposed change of direction: attach labels as field metadata instead of
renaming
After digging into open question 1 (renaming or metadata), I'd like to
change the proposal to the metadata approach. Column names stay exactly as they
are today and remain the lookup key. The readable label is attached to each
output field as Arrow field metadata, and tools that display results show the
label instead of the name.
Everything about how a label is **computed** stays as proposed above: the
`label_for` trace through the unoptimized plan, the labeling rules, window
rendering, and the `SqlDisplay` / `UdafHumanDisplayBuilder` fixes. Only the
last step changes: how the label is applied to the result.
### Why
The three hardest parts of the rename design all come from changing the
result's column names:
- Client code that reads columns by the old name breaks (`df["sum(t.a)"]` in
Python, JDBC, ADBC, Flight SQL, Ballista, and DataFrame calls after
`ctx.sql()`).
- Two columns with the same label, such as `SELECT t1.a + 1, t2.a + 1` or
`SELECT 1, '1'`, can't both be renamed, so every collision needs a fallback
rule.
- The label becomes the result's lookup key, so the name still does two jobs.
With metadata, no name changes, so none of these apply. Open questions 1, 3
(SQL followed by DataFrame calls), and 4 (collision handling) go away. The
trade-off is that clients that don't read field metadata, such as pandas, JDBC,
and BI tools, keep showing today's names.
### How labels are attached
The labeling step in the `Statement::Query` branch of
`sql_statement_to_plan` adds a projection that aliases each labeled column **to
its own name** and attaches the label with `Expr::alias_with_metadata`. Each
labeled field carries two keys:
| Key | Value |
| --------------------- | ----------------------------------- |
| `datafusion.label` | The label, such as `sum(a)` |
| `datafusion.label_of` | The column name, such as `sum(t.a)` |
`datafusion.label_of` guards against stale labels. `Alias::to_field`
(`datafusion/expr/src/expr_schema.rs`) merges the inner field's metadata into
an aliased field, so after `df.with_column_renamed("sum(t.a)", "total")` the
field would still carry `sum(a)`. A display tool uses the label only when the
field name equals `datafusion.label_of`, so the renamed column displays `total`.
Rule changes compared with the original proposal:
- A column gets no metadata when its label equals its name, or when the
trace returns `None`. A query in which no column gets a label gets no labeling
projection.
- Explicit aliases and columns named in the outermost `SELECT` get no label,
so they display their names.
- Duplicate labels are allowed. Names stay unique, so the query works as it
does today.
The behavior tables above still apply, with "Proposed name" read as "Label".
The "Current name" column becomes the column name, which doesn't change.
### The label already reaches the result
DataFusion already carries alias metadata from the logical plan to the
record batches:
- `OptimizeProjections` removes a same-name alias only when it has no
metadata (`datafusion/optimizer/src/optimize_projections/mod.rs`).
- `ProjectionExec` takes field metadata from the logical output schema
(`ProjectionExprs::make_projector_with_schema_metadata` in
`datafusion/physical-expr/src/projection.rs`).
- Physical projection-removal rules keep a projection that sets metadata
(`ProjectionExec::overrides_metadata` in
`datafusion/physical-plan/src/projection.rs`).
A local prototype test attaches labels this way on top of the unoptimized
plan for `SELECT a + 1, sum(b) FROM t GROUP BY a ORDER BY a + 1 DESC LIMIT 2`.
Every batch from `collect()` carries the labels, the column names are
unchanged, and the physical plan still uses `TopK(fetch=2)`.
### Displaying labels
| Consumer | Behavior
|
| ----------------------------------------------- |
--------------------------------------------------------------------------------------
|
| `datafusion-cli` table format | Show the label as the
header. |
| `datafusion-cli` CSV, TSV, JSON, NDJSON formats | Keep column names.
Programs parse these formats, and JSON object keys must be unique. |
| `DataFrame::show()` | Show the label as the
header. |
| Arrow IPC and Flight SQL clients | Receive the metadata
with the schema. A client can choose to display it. |
| Other clients | Display column names, as
they do today. |
A display tool rebuilds each batch with a renamed schema
(`RecordBatch::try_new` with the same arrays), which copies no data.
### What changes when you turn on the option
The configuration option and rollout stay as proposed: opt-in, default
`false`, turned on first in `datafusion-cli`. Because no names change, scripts
that parse `datafusion-cli` CSV or JSON output keep working. The compatibility
table becomes:
| Affected area
| Effect
| Impact |
|
-------------------------------------------------------------------------------------------------------------------
|
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| ------ |
| Code that checks a result batch against a schema that it built itself,
such as `MemTable::try_new(schema, batches)` | Arrow's `Schema::contains`
requires matching field metadata, so the check fails with `Mismatch between
schema and batches`. Using `df.schema()` avoids this. | Medium |
| `DataFrame::union` of a labeled result with another input
| Field metadata can differ between
the inputs.
| Medium |
| Tests that print table headers through `DataFrame::show()` or
`datafusion-cli` | Headers show labels.
| Medium |
| `EXPLAIN` output
| The top projection shows `<name> AS
<name>` for labeled columns.
| Low |
| Files written from a labeled result, such as `df.write_parquet()`
| The embedded Arrow schema stores the
label metadata. Column names don't change.
| Low |
| Substrait and protobuf round-trip tests
| Field metadata can be lost in the
round trip.
| Low |
### Additional required code changes
- Add the metadata key constants and a helper that returns the display name
of a field: the label when `datafusion.label_of` equals the field name, and the
field name otherwise.
- Use the helper in the `datafusion-cli` table format
(`datafusion-cli/src/print_format.rs`) and in `DataFrame::show()`.
- Add tests for duplicate labels and for `with_column_renamed` on a labeled
result.
- In `output-field-name-semantic.md`, describe the label metadata instead of
replacing naming rules. Field names keep the rules in the specification.
### Renaming becomes an alternative
Renaming the output columns to their labels moves to "alternatives
considered". A rename helper, such as `DataFrame::with_display_names()`, can be
built on top of the metadata later for clients that want readable names, and it
can handle collisions in one place.
### Updated open questions
1. **Option name and namespace.** Is `datafusion.sql_parser.column_labels` a
good name, or does the option belong in another namespace, such as
`datafusion.format`?
2. **Metadata keys.** Are `datafusion.label` and `datafusion.label_of` good
names? Neither the Arrow format nor Flight SQL defines a display-label key. The
Arrow format reserves only `ARROW:extension:name` and
`ARROW:extension:metadata`, and recommends namespaced keys for application
metadata. `ARROW:FLIGHT:SQL:REMARKS` is defined as a column comment, but the
Arrow Flight SQL JDBC driver maps it to `ResultSetMetaData.getColumnLabel()`.
Should a Flight SQL server built on DataFusion also copy the label into
`REMARKS` so that JDBC tools show it?
3. **Library default.** Labels change no names, so should the option default
to `true` after the `datafusion-cli` rollout, given the schema-metadata effects
above?
4. **Machine-readable CLI formats.** Should CSV and TSV headers in
`datafusion-cli` show labels, given that duplicate labels are possible?
5. **Session-dependent labels.** Unchanged from the original question 5.
6. **Custom window display.** Unchanged from the original question 6.
If this direction sounds right, I'll update the issue description to match.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
