4 ms·
PostgreSQL can do it on live data without importing anything. Set up the "file_fwd" extension: CREATE EXTENSION file_fdw; CREATE SERVER fdw FOREIGN DATA WR
by atombender 7y ago
PostgreSQL can do it on live data without importing anything. Set up the "file_fwd" extension:
CREATE EXTENSION file_fdw;
CREATE SERVER fdw FOREIGN DATA WRAPPER file_fdw;
Declare tables:
CREATE FOREIGN TABLE person (id int, sex text)
SERVER fdw
OPTIONS (filename '/tmp/person.csv', format 'csv');
CREATE FOREIGN TABLE weight (id int, weight int)
SERVER fdw
OPTIONS (filename '/tmp/weight.csv', format 'csv');
Now:
SELECT * FROM person JOIN weight ON weight.id = person.id;
- haddr 7y agoIn R this is rather straightforward, and you can also achieve it without additional libraries: weight <- read.csv("weight.csv") person <- read.csv("person.csv") merge(weight, person, all=TRUE) Of course, nowadays you would use data.table, but still the merging logic would be exactly the same.
- nkozyra 7y agoI never got deep into R as we had python or R as options in school for data projects so forgive me but where is the database join in this scenario?
- haddr 7y agoOriginal article doesn't say anything about "database join". It's about joining two datasets by some common ID. R in this case fits the bill and even allows for some relational algebra here (e.g. INNER JOIN would be merge(X, Y, all=FALSE), LEFT OUTER JOIN: merge(X, Y, all.x=TRUE), etc...)
- stilisstuk 7y agoMerge joins the two dataset on identical columns. All=True is an outer join.
- atombender 7y agoThat loads all the data into memory, though. Fine if you're joining all of it, but with the Postgres example you can throw the whole SQL language at the data, and it will be streamed and pipelined efficiently just like with normal Postgres tables.
- kragen 7y agoThis isn't "without importing anything". This is "importing all the data again every time you run a query".
- atombender 7y agoNo, "import" means "create state from data". It's not creating any persistent state at all. But it's an important nuance for another reason, because you can operate on the original files in real time. You can update the underlying files and your queries will reflect the changes. The downside is since the data is streamed at query time, it's less efficient and doesn't allow indexes to be created.
- kragen 7y agoThat's reasonable.