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:email@hotmail.com

  • Malformed row (contains extra column): admin:my:email@hotmail.com

closed as too broad by mustaccio, Tony Hinkle, Marcello Miorelli, Max Vernon, Paul White Apr 6 at 11:00

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • The point of ETL is to eliminate the manual process. Just because it has been through one flow does not mean it cannot go through another until it reaches the format you require. Your issue here is not which destination to choose, rather how to get the data into a format that can be loaded. – Mr.Brownstone Dec 23 '18 at 19:17
  • Oh alright, I understand what you mean. The total data size goes up to 150 GB (they're split into multiple documents of course). Any suggestions on how can I perform the ETL process on this much data? I've tried before to apply it using Java, but was too complicated on that end. – apple212 Dec 23 '18 at 20:31
  • I would recommend PostgreSQL. "Slurp" in the files line by line (no checking) into a staging table. You can do quite a bit of programming with just the "native" PostgreSQL string (and other) functions and then you can use PL/pgSQL - a powerful programming environment in its own right (with further staging tables). The latest versions of PostgreSQL have both document and key-value stores in addition to the relational store. You could also use Java on your imported file-strings and gradually "massage" your data into a format suitable for SQL querying and all the power that you get with that! – Vérace Apr 1 at 15:39
  • @Vérace Could you please provide a small example or link to one so I get a picture of what you exactly mean? Also, post it as an answer and not a comment. – apple212 Apr 2 at 6:44
  • OK! Please provide two lines from a typical file that you have. One line should be able to be loaded "normally" and the other should fail. I will also need a schema for the table that corresponds to the file. I can get you a few steps down the road that way. – Vérace Apr 2 at 11:37

When previously faced with a similar issue I ended up

  1. writing a pre import parser (c)which ran through the extracted data and created a file of valid entries and a file of malformed entries
  2. Import the valid entries Into Sql server using the import wizard which was used to create a schedule job for each of the import file types
  3. Once imported there were several jobs that further manipulated the data and copied to the final end user tables.

The main issue is identifying the malformed data And handling it and ensuring that you use the same pattern where possible for all files.

  • This answer is just an application of the ETL process. Also I'm not experienced enough to code a good filterer that has good memory management and good performance. I've already tried similar in Java and it didn't go well. – apple212 Dec 23 '18 at 22:33

You can use powershell's text (e.g. get-content, split, join) and csv (Import-csv, convertto-csv,export-csv and data table functions) to cleanse data its free powershell has regex support.

bulk insert from sql server is useful for loading text files and very fast

typically you would load the csv into a load table with text varchar(max) columns and then validate and load or reject from the load table into staging tables, with each stage performing a cleansing operation (fixing dates, converting, formatting), if you are using nix then awk and grep will work fine, awk is a great tool.

ETL is a discipline in itself and there are many issues with loading data into dbs

you could also load each row into a single large text column e.g. varchar(max) as a blob and use sql functions to split the data, into a column separated table.

  • 1
    This sounds interesting. Do you know the average speed for doing these operations? Also, an example would be appreciated. I'm running on Windows, so don't really have access to unix tools. – apple212 Dec 24 '18 at 0:52
  • OK so that narrows things down speed depends on your system, loading involves disk read speed and memory transform involves computation because you are verifying, validating, changing, formatting data if you are working with one row at a time it will be the time to process one row * the number of rows what would help is if you can load multiple files in parallel to maximise the utilisation of your processor and disk always doing so much your processor is not hitting 100% and your disk isnt queueing I will have a look at some code and get back to you Merry christmas – Computers are lie Dec 25 '18 at 0:58
  • Merry christmas to you too. I'm aware that loading everything into a single column will solve one issue, but I think using SQL string functions will create another issue. See, I'm going to be doing this to over 4 billion of rows and it's a standard box of computer so I don't actually believe the string functions will operate fast enough. But if you could provide data that they function good enough, I'll give it a shot. – apple212 Dec 25 '18 at 10:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.