Drupal 7 - Use external database in (virtual?) nodes and views

Question

I'm a bit of a Drupal newbie but have committed myself to porting some quite complex bespoke websites.

One of these is a surf report site which uses a database with millions of time/location rows to get the local conditions. Obviously can't write this to a Drupal table everyday so am looking for a way to connect and retrieve live data.

I'll need to use the external data in a number of displays which would normally be nodes, views, blocks etc.

The ideal solution would be if the external data just appeared as local data, but I'm guessing that is a rather big ask.

I'm open to any approach. If it involves writing a module some pointers on what to look at (preferably code examples) would be much appreciated.

Thanks, Chris

Answer 1

I had to show info from a legacy staff databse in my Drupal 6. I have explored three ways:

1. Importing database rows as nodes in a nightly cronjob
2. Accessing the external database in my own module
3. Accessing the external database using hook_views_data()

If I started again I would use hook_views_data(), it's more flexible, specially if you want to match the external database information with drupal nodes.

Best.

Answer 2

Maybe you can use the Data module.

From the documentation:

The Data module lets you use database tables that are foreign to Drupal.

You can 'adopt' a table in your database that ordinarily Drupal would not be aware of. This might be external data you have imported, or a table that another application has created.

It also provides Views integration.

Answer 3

Have a look at the Forena module which can be used to display (= query, not update) data stored in databases external to Drupal. It comes with a full suite of Supported database connections, such as MySQL, MS SQL, Oracle, Postgres or any PDO compliant variation.

For more details about Forena, 2 types of documentation are available:

• Community documentation.
• Documentation that comes with Forena, which you can access right after install and enable of the module. Checkout the demo site for an online example of the Forena Documentation.

The Sample reports and graphs are fully functional, such as the drill downs available on the Master Summary with drill down. Using Forena "skins", you can also use the amazing "dataTables" (and its widgets, such as sorting by selected columns).

Forena also includes a UI for either creating your reports (the WYSIWYG Report Editor) and/or for creating your SQL queries (the WYSIWYG Query Builder).

^{Disclosure: I'm a co-maintainer of Forena.}