One of the recurring gripes I hear about in the data science, statistics, and computational biology domains is the lack of seamless workflows for cleaning, analysis, and visualization. Currently OpenRefine doesn’t expose a SQL interface. This makes workflows with tools like Scikit, R Studio, Knime, Pandas, Jupyter, and others cumbersome for round tripping.
I think we could do better post-4.0 and add a Java SQL interface or make progress towards alternative storage configurations (something like Apache Gora or more current Java DB technology). This would also make future features such as data joins and multi-way merging a reality without us having to do a lot of the built in coding but instead allow extensions and tools to read and write to the DB storage layer configured.
One new project that seems to be used by a few other data cleaning and analytic software such as Lilac AI and LanceDB is DuckDB which supports fantastic features that are very relevant to how OpenRefine works and how we want it to work better in the future such as parallel vector operations, joins, appends, Parquet, NDJson, ADBC support, etc.
Also have a look at some of the videos from DuckCon #3
A particularly clever language is Malloy which lends itself well for simpler nested, joined, aggregated query syntax and is developed by Lloyd Tabb
Looking at the rapidly changing landscape of existing RDBMS technologies, it appears that graph querying and traversal itself is getting added to more and more. Apache AGE is a PostgreSQL extension that adds graph querying.
There would be nifty things one could do with Record relations and data quality when a graph model is overlaid.
I would love to see a proof of concept where PostgreSQL could be an optional storage and query layer, maybe through the 4.0+ architecture, and as perhaps itself a storage engine extension, that other extensions might then depend upon it.
Is that a possibility if given enough time and effort?
@antonin_d I started helping contribute and sponsor an effort that I thought would make things easier in multiple ways on this issue. Would love to know what you think about perhaps instead just leveraging middleware and what else that middleware might need or how OpenRefine could take advantage? I'd be keen to know what else we might need here for easily writing Recon services that use an existing DB backend, as well as OpenRefine just using the middleware and what more that DB2Rest can do to make that happen.
Blogged about DB2Rest here:
Expose your database as a REST API safely and without code - DEV Community
I've also reached out to Luca with ArcadeDB to get his input (he's a senior Java dev also) and how he thinks this might help "bridge the middle gap and safely" in a lot of ways.
Note: Dhrubo is one of my best friends and we worked together at Ericsson back in the day with Tony (who did OpenRefine's database extension).