How to make an external database available to Views? There's a lot of discussion here and in other places about using external databases in Drupal. What I've figured out from that is: 


  The best way to work with data in a database table is to use the Views module. I've installed that and started using it and found it's great for working with a table that I've added to the site's database.
  The easy way to get Views to work with a table that was not created by Drupal is with the Data module. This module treats any table in the site database that is not in use by Drupal as an "orphan table" and allows it to be "adopted" at the click of a button.
  The module "Table Wizard" apparently provides a way to do that with tables that are not in the site's database. But that module is not available for Drupal 7.
  The "settings.php" file, in the section on "Database settings," has detailed documentation on how to define additional databases that the site can work with. But when a database is defined in that way, its tables do not show up in the list of orphan tables in the Data module.


The change that I've made to the database definition in the “settings.php” file of one of my sites is:


$databases = array (
          'default' => array ('default' => array (
                    'database' => 'db_local',
                    'username' => 'db_local',
                    'password' => '_________',
                    'host' => 'localhost',
                    'port' => '',
                    'driver' => 'mysql',
                    'prefix' => '',
          ), ),
          'extra'   => array ('default' => array (
                    'database' => 'db__extra',
                    'username' => 'db_admin',
                    'password' => '_________,
                    'host' => 'localhost',
                    'port' => '',
                    'driver' => 'mysql',
                    'prefix' => '',
          ), ), );


I have a table that I want to use in more than one site in my multi-site installation. This means I need to have the table in a database that is accessible to each site. According to the reading I've done, my options for doing that are:


Best, if it's possible: Find out that there's a way to get the Data module to recognize tables in the additional database defined in "settings.php", so it can adopt them.
Use table prefixes to allow all the sites to use one database.
The module “Forena Reports” seemed to do exactly what I need, but when I installed it, I couldn’t get it to work. I submitted issue 2475645 and can’t do much more there unless there’s a fix for that problem.
Write my own module to give Views access to the additional database defined in "settings.php".
Install Drupal 6 with Table Wizard and use that to get Views to see my external table, and then somehow take the result of that and put it into Drupal 7. (This has been suggested in some posts.)
Follow the procedure in https://drupal.stackexchange.com/a/3321/45991 , which starts with installing a patch and seems to also require creating my own module.
I’ve also looked at the modules Feeds, Feeds SQL, Views XML Backend, and Migrate, but none of them say they can connect an external db to Views. Am I missing something? Is one of them the right solution?


Option 2 is not practical because it would result in a huge database with hundreds of tables that would be very difficult to manage. I’ve been making great progress in getting started on Drupal, but I don’t feel up to the task of 4, 5, or 6. Is there any possibility of something like option 1 working? Or is there any other way to get Views to work with a table outside the site database without being able to write my own module?

Two additional details: The first table I need to work with is pretty large, over 6,000 rows and about 20 columns. There is no intention to give visitors to the site access to the database except for information drawn from it for page content.

Thanks for your help.
I've done this successfully a couple of different ways:

Use a SQL View to create a virtual table in db_local that refers to the corresponding table in db__extra. The query to create that would look something like this:

USE db_local;
 CREATE VIEW some_table AS 
     SELECT * FROM db__extra.some_table;


You can then refer to this SQL View as if it were a native table in your Drupal database, and you don't even need to add the external database at all in settings.php. Note that this might not be compatible with Data module (see https://www.drupal.org/node/1973806), but you should be able to describe the SQL View to the Drupal Views module by implementing hook_views_data(), and stop using Data module.

Here's another possibility: use selective table prefixes to trick Drupal into rewriting {some_table} to db__extra.some_table. This is not the same as prefixing your Drupal tables. 

Your $databases setting would look like this:

$databases = array (
          'default' => array ('default' => array (
                    'database' => 'db_local',
                    'username' => 'db_local',
                    'password' => '_________',
                    'host' => 'localhost',
                    'port' => '',
                    'driver' => 'mysql',
                    'prefix' => array(
                            'some_table' => 'db__extra.',
                    ),
          ))
);


Again, I'm not sure if this is compatible with Data module, since I don't use it. But it should work with Views if you implement hook_views_data() yourself.