Binding to a database on the server

[KrishnaPG]

Support Question

The remote server example nicely demonstrates hosting a local file as a table.

Wondering what does it take to host a real table (from a local database)? For example, say we have a SQLite or some other database and want to bind one of its tables - what kind of API marshalling need to be done?

Is there some kind of interface / class that can be implemented so that any datasource can potentially become a table?

[nevi-me]

Hi @KrishnaPG. I've been able to successfully use perspective on the browser, and serve it data separately from a SQL backend. To do this, I was using JDBC > Apache Arrow via the provided utilities in the Arrow project. I then converted the Arrow data into batches, which I streamed to the browser via gRPC-web. Then on the browser, I read the Arrow data into Perspective.

Would a workflow like that work for you?

[texodus]

This is by far the best way to do this. A few addendums though -

• You shouldn't need batching/streaming in this case does unless your server architecture has some message size restrictions, otherwise this should mostly already be transport optimal. Perspective at least does not read streaming Arrow format itself (e.g. you don't pass an unresolved Response to the constructor).
• Likewise you can provide the resulting Arrow trivially via HTTP or WebSocket, gRPC-web is not required.

[KrishnaPG]

Thank you @nevi-me That sounds reasonable. Perhaps the question would be then,:

• how do we map the frontEnd (perspective viewer's) filters, sort, scroll position etc. into the SQL / NoSQL queries on the backend

One way binding from server/DB -> frontEnd/Perspectiv Viewer (streaming the results one way) is straight forward. But how to achieve the two-way binding, specifically being able to apply the filters, sort etc. on the fly and get (only) the relevant data from the very large DB.

As user keeps changing the filters, sort etc. on the Perspective viewer, these need to be somehow sent to the server /DB adapter to tune the data that is streamed to the frontend. Is there some example / API to demonstrate the conversion of front-end params to query params?

[mhkeller]

@KrishnaPG, did you ever figure out how to dynamically pull data from a database into perspective?

[texodus]

This s a really complex topic, it is not possible to do this ad-hoc on Perspective currently. We've considered making Perspective adaptable to different data engines, but it is both a very large change, and IMO not nearly as useful as you expect (what I assume you expect at least, by virtue of you asking).

Perspective's model is much more complicated than "translate to SQL", it does the equivalent of creating a materialized view for each query and managing it's lifetime. It also handles:

• Pagination/windowing including sparse notification of windows for real-time updates.
• Message conflation (from the server and the UI) and cancellation. This isn't just for real-time data, it is how the UI updates optimally when you click on multiple elements before the query is completed.
• Session management (e.g. keeping a query in memory until it is no longer used).
• Separate query types to suite specific data-driven UI functionality, e.g. auto completion, render-warnings, tree collapse/expand, color-by-category, etc.

.. etc. At best, Perspective would need to use your DB as a view server, e.g. to write/create materialized views. It may leave garbage if it crashes without special configuration, and it would need to allocate these views for each unique query the UI needs (and it may need several, e.g. for auto-completion and dimensions). None of this of course would "just work" on a variety of databases, there would still be a lot of re-implementation in order to support different SQL engines. And some of them still require Perspective to read the entire column into memory to work.

Even with these changes, it would be a lot of work internally to read SQL query result responses, as Perspective's query format is OLAP-style (with parent aggregates for every group-by level), and the plugins are more-or-less hard-coded to expect data in this format, which few databases support. We want to add these features to Perspective anyway but this is itself quite a lot of work.

Beyond this, SQL is a fairly complicated language, and using a different engine will not itself add new features. Perspective' UI already encompasses everything that the engine can do - replacing the backend does not magically give it the ability to run subselects, joins, or anything else it doesn't already support. Perspective is also non-reflective (e.g. the query options are hard-coded), so we'd need to manually tweak the profiles of e.g. aggregates, filters, data types, formatters for all plugins, etc.

Similarly, in most cases this is going to be a net performance loss. If you're experiencing issues with load-time performance doing the equivalent of SELECT * FROM MyTable and loading the data into Perspective, I implore you to take a look at how you are processing the query in your app - e.g. generally, iterating a huge resultset from the node.js adapter and creating JSON rows will be abysmally slow, while @nevi-me 's suggestion above to try the JDBC/Arrow adapter should be pretty much ideal even over a network.

All this being said, it's pretty much impossible for me to advocate whether such a design would help y'alls use cases without knowing what they are, so I'm quit interested to learn more about what specifically you're looking to accomplish - specific new query features or performance bottlenecks you are trying to work around.

[mhkeller]

Thanks for the detailed answer! Yep that makes sense. I had been looking into the arrow approach and that sounds like the way to go. Thanks again!

[nevi-me]

Something that could be interesting is only passing down projections and filters. If there's an interface (I haven't used Perspective in a while) to listen to changes in columns used + filters applied, one could then listen to those changes and decide whether to fetch data from a DB.

I think the Arrow way would be the most performant way, though I did reach limits when I was trying to get very large files from Parquet (I also tinkered with a Delta file format browser using Perspective), for data that would in any case fit into Perspective, the zero-copy benefits of Arrow would hopefully offset the time spent waiting on the network.

The Arrow ecosystem has largely improved to the point where one could do Flight over gRPC or websockets to get data.

I agree that trying to translate everything to SQL would be a lot of work. Maybe one could manage to translate aggregations, but then the schema would change in significant ways compared to just select a, b, d vs select a, b, c, d,e .

[burakakca]

How to bring real-time data to perspective

[sinistersnare]

You simply call table.update to give the table more data. There is nothing fancy you need to do. How to connect to the datasource etc. is not Perspective's problem.

Examples like streaming show this.

[klaussilveira]

For what is worth: I have been experimenting with this concept with Perspectice and Clickhouse. ClickHouse supports Arrow and I have a small Rust server that takes care of distributing Arrow files to the clients. I have separated the data into monthly chunks. Every month equals 1 arrow file. So when you see the dashboard, you get back an Arrow file for 1 month from the server. If the client requests 1 month + 2 weeks, it'll fetch the other month. This way, server interaction is minimized and the customer can fully leverage Perspective.

This, in contrast to sending multiple parameterized queries, was much better. The sacrifice is a few extra seconds during initial load, which seems to be acceptable given the use case (versus slowness during analysis).