4 ms·
Interview question: Given a 2GB csv file, write a script to do calculation with it. Me: Put all in SQLite and write a SQL query.
by revskill 4y ago
Interview question: Given a 2GB csv file, write a script to do calculation with it.
Me: Put all in SQLite and write a SQL query.
- dfinninger 4y agoYeah, there are actually a number of scripts I have at my company that use Python’s baked in SQLite3 module to do something complicated very easily. There’s even a lib for Parquet making analysis on a small number of problematic files quite easy.
- cauthon 4y agohow/where do y’all suggest hosting a shared SQLite db?
- simonw 4y agoThat's exactly what my https://datasette.io https://datasette.io project aims to solve! Recent examples: - https://simonwillison.net/2023/Jan/27/exploring-musiccaps/ https://simonwillison.net/2023/Jan/27/exploring-musiccaps/ - https://simonwillison.net/2022/Aug/21/scotrail/ https://simonwillison.net/2022/Aug/21/scotrail/ - https://simonwillison.net/2022/Sep/5/laion-aesthetics-weeknotes/ https://simonwillison.net/2022/Sep/5/laion-aesthetics-weekno...
- pletnes 4y agoHave a go with duckdb next time - you can query csv files without loading them first.
- simonw 4y agoYou can do that with SQLite too: https://til.simonwillison.net/sqlite/one-line-csv-operations https://til.simonwillison.net/sqlite/one-line-csv-operations (DuckDB is a lot more ergonomic for that kind of thing though - it's really fantastic tech)
- sam_lowry_ 4y agoUnlike sqlite, DuckDB is a very complex and much less polished.
- pletnes 4y agoCool! Although I believe duckdb can do it on disk / out of memory, so querying huge files are possible. I also like its syntax, I tend to CREATE VIEW mycsv AS SELECT * FROM ‘my.csv’ (or similar). Then I think you can select or join even across files, although I haven’t gotten that far yet.
- deaddodo 4y agoThe spirit/answer being sought of the question is to not have to load all of the data in memory first.
- pletnes 4y agoI believe duckdb does not load the whole csv file into memory. It will load a few rows to find column headers and guess data types.
- deaddodo 4y agoYou’re just pettyfogging the situation. The spirit of the question is to find a solution that is acceptable/performant algorithmically. Certainly, there are hiring panels that appreciate these sorts of tricks to go around the solution, usually citing “out of the box” thinking, but the majority would probably just say “do it without that solution” or mark you as a fail.
- deleted 4y ago[deleted]
- mjcohen 4y agoIf it is a "nice" csv file, gawk will do quite well.