Submitted 9 months ago by Dust0741@lemmy.world to selfhosted@lemmy.world
If possible convert those files to compressed parquet, and apply sorting and partitioning to them.
I’ve gotten 10-100gb csv files down to 300-5gb sizes just by doing that
That makes searching and scanning so much faster, and you can do this all with open source free software like polars and ibis.
Parquet is great, especially if there is some reasonable way of partitioning records - for example, by month or year - if you might need to only search 2024 or something like that. Parquet is great for only needing to I/O the specific variables you are concerned with, and if you can partition the records and only subset a fraction of them, operations can be extremely efficient.
RDBMS shine on getbyId queries. Queries where the value starts with should also work well. But queries where the word is in the middle of the value or column generally don’t perform well. Since it’s just for personal use that might not matter too much. If you’re querying on exact values it’ll go pretty smooth. If you’re querying on ‘deniro’ while the value contains ‘bob deniro’ and others it’ll be less performant. But it’s possible it works well enough for your case.
Elasticsearch is well known for text searches and being incredibly flexible with queries and filtering. www.elastic.co
Manticore is one that’s been on my check-it-out for I don’t know how long. It looks great imo: manticoresearch.com
Open search: opensearch.org
Disclaimer: I haven’t really used any RDBMS systems extensively for years so it’s possible there are some that added support for full text searches being more performant.
Aleph also seems to be able to cross reference data between documents. I don’t think any of the ones listed above do this. But I also don’t know if this is part of your requirements.
If CSV entries are similars, you can try Opensearch or Elasticsearch. It’s great for plain text search (with Lucene)
Try ripgrep instead of grep. Unless you go for index etc.
I’ve used java Scanner objects to do this extremely efficiently with minimal memory required even with multiple parallel searches. Indexing is only necessary if you want to search for information many times and don’t know what exactly the search will be. For one time searches, it’s not going to be useful. Grep honestly is going to be faster and more efficient for most one time searches.
The initial indexing or searching of the files will be bottlenecked by the speed of the disk the files are on, no matter what you do. It only helps to index because you can move future searches to faster memory.
So it greatly depends on what and how often you need to search and the tradeoff is memory usage, but only for multiple searches of data you choose to index from the files in the first pass.
Done this with massive log files. Used perl and regex. That’s basically what the language was built for.
But with CSVs? I’d throw them in a db with an index.
Agreed. If the data is suitable enough, there are plenty of tools to slurp a CSV into mariadb or whatever.
Really depends on what data it is and whether you want to search it regularly or just as a one time thing.
You could load them into an rdbms (MySQL/Postgres) and have it handle the indexing, or use python tools to process the files.
If it’s just a one time thing grep is probably fine tho.
Are you looking for specific values in this table, or substrings?
If specific values, I’d probably import the CSV file into a database with an column indexed on the value you care about.
Many (most?) databases these days support some sort of full text search.
No idea about aleph. I’ve used Solr for that (solr.apache.org), thus my username, but maybe that is considered old school by now.
Elasticsearch should work too
Treczoks@lemmy.world 9 months ago
This depends on what you are actually looking for, and how you are looking for it.
Do you really need pattern matching, or do you only look for fixed strings? Then other tools may be faster.
If you need case independent search on an upper- and lowercase data set, make a copy that is all upper or all lower, and search there.
If you only search in certain columns, make a copy that only includes these.
Or import the data into a database.