13

My goal is to produce a fast, reliable and automated method for accessing read-only data contained in several very large flat-file data sources (CSVs, Fixed Width and XML docs) using Drupal 7 that can be queried against using the Views 3 module. I would prefer to use already available modules, but building a custom module is also an option.

To help rule out modules and methods not suited for the task, here are the stats on the files I'm working with:

  • Yearly Import: 8,500,000 line CSV file. (Purged and reloaded yearly. Has primary key.)
  • Weekly Import: 350,000 line fixed width file. (Purged and reloaded weekly. No primary key.)
  • Hourly Import: 3,400 line CSV file. (Would like to update and sync as often as possible, but no more than every 20 min. Has primary key)
  • Daily Import: 200 item XML file. (Purged and reloaded daily. Has primary key)

Converting between the three formats is not an issue and can be done if it will improve import performance or allow better tools to be made available. (AWK for Fixed Width to CSV, etc.) Retrieval and conversion automation is easy via cron and sh scripts, but still need to automate the Drupal 7 integration. The use of custom tables is also possible as long as vews can reference the data using relationships.

What would be the best practice to accomplish this type of data integration with Drupal 7? Also, am I leaving out any important details regarding the data or what I'm trying to accomplish?


Here are a few projects I'm currently looking at to find a solution. I would like to expand on this to help others decide which route to take when working with larger data imports.

Importing data into nodes:

  • Feeds (Currently Alpha for D7)

Feeds will import the data reliably. Speed is reasonable for the smaller data sources but is too slow for the 300k+ tables.

Automation available using cron and Job Scheduler (Currently Alpha for D7).

Not having an index or unique key available in the source data is making this difficult to use. It is faster than feeds, but still slow to import the very large tables.

Automation is available via drush and cron.

Custom Tables Instead of Nodes

The Data module looks very promising, but is very buggy for D7 at the moment. The automation and import speed requirements would be easily met using data, but reliability is lacking. The views integration (link is for D6) looks very promising.

Added this for reference. There is no D7 candidate at this point, but could serve as a starting point for a custom module.

Added this for reference. This seems to have been absorbed by Table Wizard in Drupal 6. Again, added only for reference.

Seems to require Table Wizard (D6 only) for Views integration. Added for reference, but doesn't meet Views requirement.


@MPD - Added "Custom Tables" as a possible solution, and expanded modules. Thank you for this addition.

8
+50

My gut tell me that this plan will make your servers catch on fire...

Seriously, if you are churning that much data, then I think you need to keep the data in an external datasource and then integrate it with Drupal.

My initial thought would to use two databases for the external data, so that you can do the weekly import w/o disturbing things too much. In other words, get database A up and running, and then import into B. When the import is done, make B the live source. Then wipe and import into A.

I have done a lot of integration of external datasources into Drupal, and it really isn't that hard. I gave an overview in Transition plan for PHP5 abomination to Drupal. That was for Drupal 6, but the same thing basically applies to Drupal 7. Essentially, you simulate what CCK / Fields API does with your own interface.

Not having a UUID for the weekly database really throws a wrench in the works, though. That part requires a lot of though, more that can be provided in a Q/A forum like this.

If you really do want to go down the import route, I would bail on Feeds and Migrate and write your own import script. Basically, you do the initial bookstrap process from index.php, query your datasource, make your nodes, and then save them. Programmatically making nodes is easy.

The best way to start out with this is to make a node with the UI, then print_r it, and replicate the object with code in your import script. Taxonomy, files, and noderefs, are hard parts, but you just need to get familiar with these portions of the API to build up these object properties. Once you have a valid node object, then you can just do a node_save(). Make sure you set a very large limit with set_time_limit() so your script runs.

EDIT BELOW TO ADDRESS CLARIFICATION/EXPANSION:

Personally, we stopped using the contrib module based approaches for data imports a while ago. They do work mostly well, but we just ended up spending way too much time fighting them and decided the cost/benefit was too low.

If you really need the data in Drupal proper, then my opinion about a custom import script hasn't changed. One of the modules you reference could be used as a starting point for how to build the node objects, then just loop through your data build nodes and save them. If you have a PK, you can easily add in logic to search the database and node_load(), modify, and save. An import script is really only a few hours work if you know the Drupal API.

If views integration is a key (and it sound like it is based on the edit) and you want to do the external tables approach, then your best option is do do a custom module and implement hook_views_data to get your data into views. More than likely, you will custom module anyway to support your datasource, so adding this hook shouldn't be that much more work. The TW and Data modules should have some example to get you going.

Personally, though, I have never found the views integration with external data to be really worthwhile. In the cases where I have considered it, the data was just too "different" to work well with a views based approach. I just end up using the method I described in the "abomination" link above.

  • You've brought up three excellent points, and I'm going to adjust my question accordingly. Mass importing and exporting would be nice but when importing hundreds of thousands, or possibly millions of nodes at this point seems at best, unrealistic. Custom tables could also be very usable if they can be integrated with views. Thank you for your response @MPD. – Citricguy Jan 9 '12 at 2:04
2

I think a node based (or even entity based) approach will burn out your server with millions of node. Besides, looking at your hourly import, that means your'll make a node_save() at least once a second. That's too much for Drupal and cause a performance problem.

The reason behind that is for those content, you won't need any hook mechanism, you won't need pathauto (but you can manually create alias, it's much cheaper than pathauto), you won't need fields... Write a simple "INSERT" query is 100x faster than node_save() or entity_save().

1/ IMHO the best option is a custom table and a custom module for your data import, then write Views handlers for Drupal integration.

2/ The database cache is invalidated during the hourly import. If it takes too much time, you can think about a replication. In the easiest form, create two identical tables, use the first one, import to the second, switch your Drupal configuration to use the second table, sync the 2nd table to the 1st (then optionally switch back to the first). Another solution is in your custom import script, prepare and group the INSERT/UPDATE queries, then only send it at the end in one transaction to reduce the database write time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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