6 ms·
sqlite> .mode csv sqlite> .header on sqlite> .import weight.csv weight sqlite> .import person.csv person sqlite> select * from person, weight where pers
by bumbledraven 7y ago
sqlite> .mode csv
sqlite> .header on
sqlite> .import weight.csv weight
sqlite> .import person.csv person
sqlite> select * from person, weight where person.ID = weight.ID;
ID,sex,ID,weight
123,M,123,200
789,F,789,155
sqlite>
- macdice 7y agoIn PostgreSQL we could do that in parallel: https://www.postgresql.org/message-id/CA+hUKGKzn3mjCAp=TDrjiVSA1r5O=nhxXRmBX-yQMADjcRexvQ@mail.gmail.com https://www.postgresql.org/message-id/CA+hUKGKzn3mjCAp=TDrji...
- geogra4 7y agoI can't speak for XSV but when I had to do similar analysis of logfiles in range of hundreds of thousands of records - SQLite would consistently seize up on my machine and make it impossible to do analysis. Had i know about XSV I may have tried it, but GNU Join saved my bacon that week and would do my analysis in minutes.
- lsb 7y agoThat's why you can `EXPLAIN` your query, and maybe even add indices, to speed things up! Plus in the last few years SQLite has gotten a lot more powerful, and has added lots of JSON support and has improved index usage. I'd avoid working with lots of data as flat ASCII text, because of all the I/O and wasted cycles to read the data and insert it into some data structure and then write it out as flat ASCII text, but it can be super debuggable.
- ainar-g 7y ago> GNU Join The join utility is actually a part of POSIX[1], so every UNIX should have one. Here's one from OpenBSD[2], for example. The GNU version probably has more flags though. [1] https://pubs.opengroup.org/onlinepubs/9699919799/utilities/join.html https://pubs.opengroup.org/onlinepubs/9699919799/utilities/j... [2] https://man.openbsd.org/join.1 https://man.openbsd.org/join.1
- ashkankiani 7y agoDid you create indices on the columns you wanted to join?
- f311a 7y agoI was trying to join two 300M lines CSV files using SQLite and Postgres and it took days to merge them on c4.xlarge. Later, I tried Miller and it took less than 4-6 hours. It also takes some time to tune Postgres to make it faster for this particular task.
- TylerE 7y agoSeems like you were doing something naively. Probably would have been much faster if you'd written a simple conversion script to do batch inserts. Perhaps something pathological like commiting (or flushing to disk) after every row?
- kyllo 7y ago4-6 hours still seems like a long time for this, did you have sorted indices on the join columns? How high of cardinality (distinct counts) did the join columns have? Traditional RDBMS are pretty fast at joining when you have the right indices in place, especially when the cardinality of the join columns isn't very high. And how much of that time was just getting the data into the tables? There are fast and slow ways to do that, too...
- f311a 7y agoIIRC, it took me 4-7 hours to load 600GB of uncompressed data to the PostgreSQL after disabling WAL and tunning some config variables. I didn't have time to test all possible speed improvements. After import, it takes 3-6 hours additionally to create indexes for those tables. I think the main problem was that I had text indices. I'm not an expert in RDBMS and used a simple join that was taking ages to start producing the actual data. There are definitely a lot of ways to tune such queries and PostgreSQL configs, but I wanted a simple and universal solution.
- creddit 7y agoOn average each line was 1GB of data!? Can you give a rough description of the content of the two files?
- adele11 7y agoCool - you could create a bash alias called xsv for that.
- 7thaccount 7y agoThis converts everything to "text" type though and makes many queries jacked up.
- thibran 7y agoYou might want to save the query result to a file too: .once out.csv select * from person, weight where person.ID = weight.ID; .mode column
- ashkankiani 7y agoYou should probably create an index on the tables first and save the result into a temporary table.
- atombender 7y agoPostgreSQL 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.