Import large number of entries every day - 1 million

Question

I have a shopping product feed that is generated every day with around a million products. This can be in any format needed - CSV, JSON, XML etc, and will live on an S3 bucket.

Each day, I need to import this feed to entries in Craft and assign them categories etc. A large percentage of the products will be duplicates but it may need to update data within the product.

What would be the best way to import this large of a data set?

I have used FeedMe in the past, but I'm not sure whether it could handle this many products.

Thanks in advance.

Answer 1

You could write a service method that reads the import file and divides it into chunks, then it could dispatch a queue job for each chunk which does the actual entry creation/update. This way you could scale the resources and amount of queue workers available to work through the queue and even Play with the chunk size.

In the queue job you might want to implement proper exception handling to keep track of unsuccessful entry operations to handle them somehow.

Answer 2

I've recently looked at this problem myself due to having feeds that lack any pagination capability e.g. CSV file and while you can offload the queue to a completely separate worker process to avoid issues with interfering with the web front end delivery for long running tasks, having a long running task for lots of data isn't performant, if it fails you are restarting from the beginning and will likely take much longer than say dividing 1000 items into 10 chunks smaller chunks due to processing overhead.

As the original answer mentioned, chunking is one of the first steps. Because FeedMe in Craft 3 and 4 support limit and offset options you can in fact leverage the existing queue job, by passing in your desired chunking options, without having to reinvent the wheel, you would need a custom console command/service to leverage this however.

My approach with feeds that don't support pagination is to essentially:

1. Create a console command that has a chunk parameter e.g. 100 per chunk, could be customisable for different feeds if required.
2. Get the feed and call getFeedMeData() to get all the feed data so we can work with it in a loop.
3. Get the total items in the feed, so we know the overall items we have for limit/offset logic to come later.
4. Set the limit to the chunk size encountered in each loop. Something like count($chunk)
5. Set the offset to a calculation of $index * $chunkSize. Assuming you had a 100 chunk size, for the first chunk the offset is 0, the next would be 100, 200 etc. For the last chunk, you might want to change the offset calculation to be $totalItems - $chunkSize, so it doesn't ever exceed the actual total items, as the last chunk may not be necessarily align to the defined maximum chunk size equally, say if you had 1501 items, you'd have 15 queue jobs with 100 items, with then one queue job left with just 1.

You can then create create a FeedMe queue job with a loop similar to this:

$feed = FeedMe::$plugin->feeds->getFeedById($feedId);
$feedData = $feed->getFeedData();
$totalItems = count($feedData);
$chunkedFeedData = array_chunk($feedData, $this->chunkSize);
$steps = count($chunkedFeedData);

foreach ($chunkedFeedData as $index => $chunk) {
    $step = $index + 1;
    $chunkCount = count($chunk);
    $offset = $index * $chunkCount;

    Queue::push(new FeedImport([
        'feed' => $feed,
        'limit' => $chunkCount,
        'offset' => $offset,
        'continueOnError' => $this->continueOnError,
        'description' => "Running $feed->name step $step of $steps"
     ]));
}

This is just an example, you will want to add some checking on $feed being valid and $feedData having data.

We can also override the description so each individual queue job is labelled accordingly, so it's easier to keep track and know which part of the sequence the queue is running, given we are dealing with many queue jobs with chunking.

There is a potential downside to this method, getFeedMeData() will be getting called many times. As the default FeedMe queue job calls getFeedMeData in it's queue job logic, so you are potentially calling the feed many times than you need to when using limit and offset values. Alternatively, you could implement some form of data caching, so you cache the feed data response for a period of time before you chunk it, however for a large amount of records, caching that amount of data could be an issue.

Alternatively, you could write a modified FeedMe import queue job that doesn't call getFeedMeData() in the execute part and instead the data is simply passed to it, given you've already had to call getFeedMeData() once for the chunking logic to know how many chunks you have, the chunk size to break each smaller queue job down by etc.

Either way approaching a large data feed into smaller queue jobs is the way to go. The general rule for performance is splitting down a large amount of items into smaller batches is going to be faster and less memory intensive than doing it without chunking. Chunking will be much kinder to memory usage per queue job and in the event a queue job fails, only a batch of x amount of steps has failed not the whole lot. Equally with ttr and retry you can handle this, even FeedMe itself supports this.

The other consideration would be around the time a chunked Feed Me queue job would take. Even if it's chunked it's still probably a long time for 1 million records, therefore you may want to dedicate a queue worker specifically for feed processing and run another queue for general Craft queue jobs, essentially two workers running two different queues. You are very likely to create a massive backlog of general Craft queue jobs queuing up while your large feed import is running. So multiple workers/queues are going to help here too.

Both Craft CMS (since 3.4) and FeedMe support running under a completely different custom queue, you can find the information around this here: https://github.com/craftcms/cms/issues/5492