[D] Switching SQL Lab / chart-data results from JSON to Apache Arrow over the wire [superset]

["rusackas (via GitHub)" <gi...@apache.org>]

GitHub user rusackas created a discussion: Switching SQL Lab / chart-data results from JSON to Apache Arrow over the wire

> Posting this as an Idea first since I'm not sure it's SIP-worthy yet, happy to turn it into one if there's appetite. Loosely following the SIP template since most of this would carry over directly.

### Motivation

Right now both SQL Lab and the chart-data API (`/api/v1/chart/data`) send query results to the browser as JSON row-objects. That's simple, but it's expensive in a few ways that get worse as result sets grow: every number gets encoded as text instead of raw bytes, every row repeats the column names, and on the way there we materialize the whole thing twice, once as a list of Python dicts server-side (`df_to_records()`), once as JS objects via `JSON.parse()` client-side. We also carry some workarounds that only exist because JSON can't represent certain types cleanly, like the big-integer shim for values over `JS_MAX_SAFE_INTEGER` and ISO-string round-tripping for dates.

The interesting bit I ran into poking at this: we already produce Arrow IPC bytes in two places internally, the SQL Lab results-backend cache (when `results_backend_use_msgpack` is on) and an unused `result_format=arrow` option on the chart-data API. Neither ever reaches the browser today, both get converted back to row-dicts and JSON-ified before the response goes out. So the hard part (producing correct, cache-compatible Arrow IPC from a query result) is already solved code. The question is whether it's worth actually using it on the hot path.

### Proposed Change

- Add a `supportsArrowFormat` capability flag to `ChartMetadata`. Built-in plugins opt in individually as they're migrated; anything that doesn't set it, including every third-party/custom plugin, is unaffected.
- The chart-data fetch layer checks that flag per viz type and requests `result_format: arrow` only when it's set, falling back to today's `json` otherwise. The request itself carries the negotiation, so there's no response sniffing on the way back.
- Add the matching response path on `/api/v1/chart/data` and SQL Lab's execute/results endpoints, reusing the existing, currently-unused Arrow IPC serialization we already have.
- On the frontend, add `apache-arrow` and put a conversion shim at that same boundary that turns an Arrow response into row-dicts before handing it to the plugin, so flagged built-in plugins don't need their `transformProps` rewritten on day one either. Native columnar rendering per-plugin is a later, optional follow-up for whichever ones want the extra squeeze.

The expected gains are mostly payload size and CPU cost, not some new capability. Numeric-heavy result sets should see a meaningfully smaller wire payload (binary floats/ints vs. repeated text and repeated column names), and we'd skip one of the two full row-materialization passes (`df_to_records()` server-side, `JSON.parse()` client-side) that every result set pays today. I don't have Superset-specific numbers yet, which is part of why I'd want the draft PR to ship with an actual before/after benchmark rather than guessing.

### New or Changed Public Interfaces

An additive `result_format` value on `/api/v1/chart/data` and the SQL Lab execute/results endpoints. No change to existing JSON behavior for anyone who doesn't ask for it.

### New dependencies

`apache-arrow` on the frontend. No new backend dependency, `pyarrow` is already in the tree.

### Migration Plan and Compatibility

Opt-in per chart type, not global. Nothing changes for any plugin, built-in or third-party, until its `ChartMetadata` explicitly sets `supportsArrowFormat: true`, which can happen one plugin at a time with its own tests. A feature flag gates the whole mechanism as a rollback path independent of the per-plugin flags. Custom/third-party visualizations are never in the blast radius since they have no way to set a flag on metadata they don't own.

### Rejected Alternatives

- Leaning harder on HTTP-layer compression (gzip/br) for the existing JSON responses. Helps payload size some, does nothing for the double row-materialization CPU cost or the type-fidelity workarounds.
- A general binary format like Protobuf or msgpack instead of Arrow. Possible, but we'd be building that serialization path from scratch, where the Arrow IPC path already exists and is proven in the results-backend cache today.

@villebro, curious what you think, both on the idea itself and whether it's SIP-worthy as-is or needs more legwork before it's worth a `dev@` thread. I'm going to put up a draft PR implementing the shim and flipping the flag on for the built-in viz types, partly to make this concrete and partly to get real before/after payload numbers instead of hand-waving them here.


GitHub link: https://github.com/apache/superset/discussions/45345

----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

Re: [D] Switching SQL Lab / chart-data results from JSON to Apache Arrow over the wire [superset]

["rusackas (via GitHub)" <gi...@apache.org>]

GitHub user rusackas added a comment to the discussion: Switching SQL Lab / chart-data results from JSON to Apache Arrow over the wire

Update from building out the draft PR (#45363): the Arrow IPC bytes were only carrying the DataFrame, not the sidecar metadata (`cache_key`, timing, applied/rejected filters, etc.), so I had to add that back onto the wire via the Arrow schema metadata to keep things like the cache badge and "View query" accurate. That part's resolved now.

Two things came up along the way that feel like real design calls rather than bugs, though. Multi-query charts (mixed time-series, for one) can't use Arrow at all right now, since the endpoint's Arrow branch is single-query-only, same constraint as the existing datasource-query Arrow branch it mirrors. And on async-query deployments, Arrow-flagged charts fall back to JSON entirely for now, since the async read-back path doesn't know how to handle Arrow bytes yet.

Could probably teach the async path to carry Arrow too, but didn't want to make that call unilaterally inside a draft PR. @villebro curious if either of those change your read on whether this needs a `dev@` thread, or if they're fine as known limitations for a v1.


GitHub link: https://github.com/apache/superset/discussions/45345#discussioncomment-19210226

----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org