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

[rusackas]

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.