I'm presented with multiple
.txt documents (each with its own set of columns) containing rows separated by some type of delimiter (which is different across the files). Each
.txt file has different amount of columns/names and they are huge in size, some are split into 100 MB chunks. The data may contain malformed rows.
My job is to import them to an appropriate DBMS that will fulfil these requirements:
Importing speed should not be too bad. Few hours is acceptable for 700m rows
Importing should support ignoring malformed rows
Querying the data should support pattern matching such as %m% in SQL
Read operations are more important than write. It's required that once everything is imported, the read should be within milliseconds
There will be many tables. It's required to be easy to query all tables
I have already tried to use relational DBMS (MySQL, PostgreSQL), but they don't support skipping malformed rows (which is a huge issue). I've tried mongoDB but some data contains double quotes and I'm unsure if the import speed is good enough.
I recently even tried using Cassandra, but it's pretty limited on pattern matching and multiple table querying.
This task is not easy, and I've spent a lot of time reading and trying to find a solution by my own. I'd very much appreciate if someone could help me out to solve this.
The data is already extracted and given, cannot modify that process. Transform is not possible as the data size is beyond the limit of manual editing. Loading is the step I'm struggling with. I'm trying to find a DBMS that can do such thing.
I've thought a lot about NoSQL DBMS but they do not fulfil all requirements. I think SQL such as PostgreSQL would do it, but I have trouble inserting the data as it does not support skipping malformed rows.
Now that I think about it, it's more structured data than unstructured. So it would fit perfectly for SQL DBMS, but the only issue is that the data contains some malformed rows and I need quite good import speed.
Two Rows from file of format (username:email):
Valid row: admin:firstname.lastname@example.org
Malformed row (contains extra column): admin:my:email@example.com