7 ms·

Back in the day (early 2000’s), I worked with a DB2 instance that had similar functionality. At the time, they called this feature federated databases. If you h

Back in the day (early 2000’s), I worked with a DB2 instance that had similar functionality. At the time, they called this feature federated databases. If you had the appropriate wrapper, you could use any data source in a query. Even output from other programs. At the time I used it for including dynamic DNA sequence alignments in queries. IIRC, SQLite can do similar things with virtual tables (with a more limited set of data file types). I always liked this way of working, but I also wonder why it never really took off. Data discovery can be an issue, and I can see the lack of indexing as being a problem. I guess that’s a long winded way to ask: as interesting as this is, what are the use cases where one would really want (or need) to use it?

solidsnack9000 2y ago
A related functionality is "SQL/MED", a SQL specification for federated databases that has some kind of relationship to medical data historically (I believe one of the use cases is data at one site that another site is allowed to query, in a limited way, data hosted at another site that may not be moved from it).
- sixdimensional 2y ago
  The MED stands for management of external data, nothing to do with medical data. It’s actually a chapter in the ISO/ANSI SQL specification.
  - solidsnack9000 2y ago
    My understanding from reading the literature around SQL/MED and related technologies is that they were, indeed, inspired by data management problems in medicine. The use cases are like those I outlined -- we can't move the data from this physical location, but we want to allow other sites to query it. As a result, a specification was added, as part of ISO/ANSI SQL specification process. The MED term is an acronym and not a shortening of "medical".
    - sixdimensional 2y ago
      That's quite interesting, would you be willing to share any sources? I wouldn't be surprised if the term SQL/MED had been spoken in healthcare/medicine circles (I worked in that industry before). That said, it's the first time I've ever seen anyone connect the term/concept specifically to medicine as the driver for the term. I'm actually someone who has gone deep down the SQL rabbit hole (I worked for a company that built a data federation platform previously), I'd be interested to read more if you can share. My apologies, I thought the confusion of the acronym simple came from the "MED" part, which as I mentioned, stands for "management of external data". In that respect we are in complete agreement :) EDIT: Perhaps you might be referring to something like this paper about data federation in general? https://citeseerx.ist.psu.edu/document?repid=rep1&type=pdf&doi=caa80deb428540752173ea998f1efc68df85ac98 https://citeseerx.ist.psu.edu/document?repid=rep1&type=pdf&d...
clscott 2y ago
Postgres calls these foreign data wrappers (FDW) https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
- refset 2y ago
  Steampipe demonstrates a rather impressive range of scenarios for using FDWs + SQL in place of more traditional ETL and API integrations: https://steampipe.io/ https://steampipe.io/
  - banku_brougham 2y ago
    I like this when I tried it out. The AWS API plugin was neat, query resources with SQL. However, I like to limit my toolset to three things, these days thats duckdb, julia for analysis side, and... OK two things. Oh yeah Trino for our distributed compute.
beachy 2y ago
> I always liked this way of working, but I also wonder why it never really took off. In today's new fangled world, a lot of developers don't use a lot of the great stuff that RDBMS can provide - stored procedures, SQL constraints, even indexes. The modern mindset seems to be that that stuff belongs in the code layer, above the database. Sometimes it's justified as keeping the database vanilla, so that it can be swapped out. In the old days you aimed to keep your database consistent as far as possible, no matter what client was using it. So of course you would use SQL constraints, otherwise people could accidentally corrupt the database using SQL tools, or just with badly written application code. So it's not hard to see why more esoteric functions are not widely used.
- dalyons 2y ago
  the approach in the article is for analytics / DW type use cases, not OTLP. So a dig about kids these days not understanding constraints is not particularly relevant.
- Arch-TK 2y ago
  I've heard people call the old approach "outdated" and even "wrong" before. It's honestly the dumbest situation, where people eagerly use extremely complex databases as if they were indexed KV stores. Completely ignoring about 97% of the features in the process. What's especially funny is that half the time a basic KV store would perform better given all the nonsense layered on top. And then there's this whole mentality of "we can't interface with this database unless we insert a translation layer which converts between relationships between sets of tuples and a graph structure". It's like people have unearthed an ancient technology and have no idea how it's intended to be used.
  - dalyons 2y ago
    Stored procs, triggers and suchlike actually are outdated and mostly wrong. Have you considered that people moved away from them for good reasons, not just ignorance? Hard to source control, impossible to debug, written in weird/bad languages, massive blast radius, don’t scale. Usability matters! I have written more TSQL than I would care to admit, and it’s a hell you couldn’t pay me enough to go back to. The “consistency must be enforced at all costs” just turned out not to be true. Worked at many places at moderate scale since my old dba days that don’t use fks. It just doesn’t actually matter, I can’t recall any serious bugs or outages related to dangling rows. Plus, you end up having multiple databases anyway for scale and organizational reasons so fks are useless in that environment anyway. On the other hand, I’m all for indexes and complex querying. not just k-v.
banku_brougham 2y ago
sqlite is awesome, obviously. but the api isnt elegant and easy the way duckdb (or postgres) is. handling dates in sqlite has me googling everything.
noisy_boy 2y ago
Similar timeline - Oracle had a feature called external tables where you can create a "table" on top of an external flat file and run queries against it. Prior to that we were use awk/perl etc and found it to be more performant + all the benefits that SQL brings.

solidsnack9000 2y ago

A related functionality is "SQL/MED", a SQL specification for federated databases that has some kind of relationship to medical data historically (I believe one of the use cases is data at one site that another site is allowed to query, in a limited way, data hosted at another site that may not be moved from it).

sixdimensional 2y ago
The MED stands for management of external data, nothing to do with medical data. It’s actually a chapter in the ISO/ANSI SQL specification.
- solidsnack9000 2y ago
  My understanding from reading the literature around SQL/MED and related technologies is that they were, indeed, inspired by data management problems in medicine. The use cases are like those I outlined -- we can't move the data from this physical location, but we want to allow other sites to query it. As a result, a specification was added, as part of ISO/ANSI SQL specification process. The MED term is an acronym and not a shortening of "medical".
  - sixdimensional 2y ago
    That's quite interesting, would you be willing to share any sources? I wouldn't be surprised if the term SQL/MED had been spoken in healthcare/medicine circles (I worked in that industry before). That said, it's the first time I've ever seen anyone connect the term/concept specifically to medicine as the driver for the term. I'm actually someone who has gone deep down the SQL rabbit hole (I worked for a company that built a data federation platform previously), I'd be interested to read more if you can share. My apologies, I thought the confusion of the acronym simple came from the "MED" part, which as I mentioned, stands for "management of external data". In that respect we are in complete agreement :) EDIT: Perhaps you might be referring to something like this paper about data federation in general? https://citeseerx.ist.psu.edu/document?repid=rep1&type=pdf&doi=caa80deb428540752173ea998f1efc68df85ac98 https://citeseerx.ist.psu.edu/document?repid=rep1&type=pdf&d...

clscott 2y ago

Postgres calls these foreign data wrappers (FDW) https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers

refset 2y ago
Steampipe demonstrates a rather impressive range of scenarios for using FDWs + SQL in place of more traditional ETL and API integrations: https://steampipe.io/ https://steampipe.io/
- banku_brougham 2y ago
  I like this when I tried it out. The AWS API plugin was neat, query resources with SQL. However, I like to limit my toolset to three things, these days thats duckdb, julia for analysis side, and... OK two things. Oh yeah Trino for our distributed compute.

beachy 2y ago

> I always liked this way of working, but I also wonder why it never really took off. In today's new fangled world, a lot of developers don't use a lot of the great stuff that RDBMS can provide - stored procedures, SQL constraints, even indexes. The modern mindset seems to be that that stuff belongs in the code layer, above the database. Sometimes it's justified as keeping the database vanilla, so that it can be swapped out. In the old days you aimed to keep your database consistent as far as possible, no matter what client was using it. So of course you would use SQL constraints, otherwise people could accidentally corrupt the database using SQL tools, or just with badly written application code. So it's not hard to see why more esoteric functions are not widely used.

dalyons 2y ago
the approach in the article is for analytics / DW type use cases, not OTLP. So a dig about kids these days not understanding constraints is not particularly relevant.
Arch-TK 2y ago
I've heard people call the old approach "outdated" and even "wrong" before. It's honestly the dumbest situation, where people eagerly use extremely complex databases as if they were indexed KV stores. Completely ignoring about 97% of the features in the process. What's especially funny is that half the time a basic KV store would perform better given all the nonsense layered on top. And then there's this whole mentality of "we can't interface with this database unless we insert a translation layer which converts between relationships between sets of tuples and a graph structure". It's like people have unearthed an ancient technology and have no idea how it's intended to be used.
- dalyons 2y ago
  Stored procs, triggers and suchlike actually are outdated and mostly wrong. Have you considered that people moved away from them for good reasons, not just ignorance? Hard to source control, impossible to debug, written in weird/bad languages, massive blast radius, don’t scale. Usability matters! I have written more TSQL than I would care to admit, and it’s a hell you couldn’t pay me enough to go back to. The “consistency must be enforced at all costs” just turned out not to be true. Worked at many places at moderate scale since my old dba days that don’t use fks. It just doesn’t actually matter, I can’t recall any serious bugs or outages related to dangling rows. Plus, you end up having multiple databases anyway for scale and organizational reasons so fks are useless in that environment anyway. On the other hand, I’m all for indexes and complex querying. not just k-v.

banku_brougham 2y ago

sqlite is awesome, obviously. but the api isnt elegant and easy the way duckdb (or postgres) is. handling dates in sqlite has me googling everything.

noisy_boy 2y ago

Similar timeline - Oracle had a feature called external tables where you can create a "table" on top of an external flat file and run queries against it. Prior to that we were use awk/perl etc and found it to be more performant + all the benefits that SQL brings.