I work at Socrata, and I've skimmed splitgraph and it looks pretty neat. How do you all deal with pushdowns or query rewriting in general when the target datasource doesn't implement the functionality your user writing real sql is trying to use? Does it explode or take forever, because it doesn't look like you're storing copies of everything in Real Postgres?
We expose a pretty limited subset of sql through our APIs, and an even more limited form to public APIs, so I'm interested in how you're doing things providing joins to your users. "It exists but is super slow" is a reasonable answer as well :)
Hey! Socrata is awesome :) The basic idea here is that you can write a Splitgraph plugin for any type of data source [0], so we like to use Socrata as a convenient example. Each plugin includes a Postgres Foreign Data Wrapper (FDW) to translate from SQL to whatever upstream query language (e.g. Socrata FDW [1]).
By default we don't copy any data into Splitgraph (although we do cache query results keyed by AST). When a query arrives, we parse it for any table identifiers, and "mount" a temporary foreign table for each, using the appropriate FDW. Foreign tables are mostly transparent to the Postgres query planner, so a JOIN will work (example [2]), and an FDW can even optionally implement aggregate pushdown. But yes, especially in the case of cross-dataset JOINS, there are some pathological cases. When your use case outgrows live querying (e.g. due to pathological JOIN queries), you can ingest any data source into a Splitgraph image, a versioned snapshot of data similar to a Docker image. [3]
Our focus right now is on the internal enterprise use case. We use the Socrata plugin as a demo, but our customers are more interested in plugins for data sources they use internally, e.g. Postgres, Snowflake, Google Search Console, CSV in S3, etc. We support all those too; you can mount them all locally, and you can also mount them on Splitgraph.com (or an internal deployment), but still thin docs / no web UI yet.
Comments
I work at Socrata, and I've skimmed splitgraph and it looks pretty neat. How do you all deal with pushdowns or query rewriting in general when the target datasource doesn't implement the functionality your user writing real sql is trying to use? Does it explode or take forever, because it doesn't look like you're storing copies of everything in Real Postgres?
We expose a pretty limited subset of sql through our APIs, and an even more limited form to public APIs, so I'm interested in how you're doing things providing joins to your users. "It exists but is super slow" is a reasonable answer as well :)
Hey! Socrata is awesome :) The basic idea here is that you can write a Splitgraph plugin for any type of data source [0], so we like to use Socrata as a convenient example. Each plugin includes a Postgres Foreign Data Wrapper (FDW) to translate from SQL to whatever upstream query language (e.g. Socrata FDW [1]).
By default we don't copy any data into Splitgraph (although we do cache query results keyed by AST). When a query arrives, we parse it for any table identifiers, and "mount" a temporary foreign table for each, using the appropriate FDW. Foreign tables are mostly transparent to the Postgres query planner, so a JOIN will work (example [2]), and an FDW can even optionally implement aggregate pushdown. But yes, especially in the case of cross-dataset JOINS, there are some pathological cases. When your use case outgrows live querying (e.g. due to pathological JOIN queries), you can ingest any data source into a Splitgraph image, a versioned snapshot of data similar to a Docker image. [3]
Our focus right now is on the internal enterprise use case. We use the Socrata plugin as a demo, but our customers are more interested in plugins for data sources they use internally, e.g. Postgres, Snowflake, Google Search Console, CSV in S3, etc. We support all those too; you can mount them all locally, and you can also mount them on Splitgraph.com (or an internal deployment), but still thin docs / no web UI yet.
[0] https://www.splitgraph.com/blog/foreign-data-wrappers
[1] https://github.com/splitgraph/splitgraph/blob/master/splitgr...
[2] https://www.splitgraph.com/workspace/ddn?layout=hsplit&query...
[3] https://www.splitgraph.com/docs/concepts/images