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
- Data Module (Currently Alpha for D7)
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.
- Table Wizard (Not available for D7)
Added this for reference. There is no D7 candidate at this point, but could serve as a starting point for a custom module.
- Views Schema (Abandoned, D6 only)
Added this for reference. This seems to have been absorbed by Table Wizard in Drupal 6. Again, added only for reference.
- Importer (RC2 for D7)
@MPD - Added "Custom Tables" as a possible solution, and expanded modules. Thank you for this addition.