Fast data loading with duplicate check We are developing an application that loads data from clients and then processes it in some predictive analytics tasks. Each client has different data structure: some clients will for instance have very detailed website activity logs, others will not. We try our best to map their data model to ours.

Currently, we modeled our problem by defining "domains". A domain can be "Transactions", "Customers", "Events"... Each domain has a minimum set of columns that is required to link it to other domains or simply to make sense (each transaction always must have a timestamp and a customer ID, for instance). It then has "tags", types of which can be dynamically added, that represent extra columns that that specific client has.

We load data from the clients in chunks of a few gigabytes. Because an extract can have overlapping data on what has been loaded before, or even updates of previously loaded data, we need to be able to quickly link a record to the existing record (upsert). Would not be a big problem if each client had a defined ID for each entity of each of our domains. But this is not the case: most clients are not capable of providing a sound ID in 1 value.

On these cases, however, multiple columns are enough for a primary key. What I do then is that I take a hash of those columns and use it as a primary key. The fact that keys are randomly distributed creates a performance problem in a clustering key.

What kind of storage engine could I use to makes this efficient, while maintaining my capability of nicely querying it? With our current solution (innoDB), it takes hours to load a million rows on a computer with a classical hard disk. As a startup, we would be interested in a free or cheap solution.

The options we are considering right now:


Simply have a very slow data ingestion process. Once historical data is loaded, the DB only needs to be fast enough to absorb the incoming data each night.
Use a noSQL solution that would absorb the data faster and that would be able to run on commodity hardware. This would require a lot of work because the data must be queryable in a lot of different ways.
There are many articles already on performance tuning of INSERT or `BULK INSERT' queries. Main points:


Optimize hardware: RAID/SSD/Fusion IO etc.
Tune source file system and network: copy files locally, use JUMBO packets if reading over network
Find optimal batch size, minimum logging
Insert into table with no indexes, triggers, constraints etc.
Apply all calculations, lookups, indexes, compression etc after load or in chunks
After all data is ready AND main table is stored on same disks, then switch partition - usually it will be almost instant as it will be a metadata only operation. However there cases when it is not.