Drupal View on Steroids. Using a Cloud Database as View Data Source.

Tue, Sep 8, 2015

Having already created Drupal views on external data, I felt the next step was to use a cloud database as the data source for a Drupal view. In this post, part of a series on using Drupal as a data platform, I describe the steps I followed to use an Amazon RDS instance as the data source for a Drupal view. Connecting Drupal to cloud databases opens up possibilities to use Drupal for data analysis and reporting from applications and data warehouses that persist data to cloud databases.

Background

Running and maintaining a database server including the hardware and software requires highly skilled personnel, costly infrastructure and can be time consuming. This is why it is now common for organisations to utilise cloud databases from cloud providers including Amazon, Google and Microsoft to name a few. Cloud databases have the following advantages:

  • You get to focus on your application and business and not spend time keeping the database server running
  • Backups and recovery are part of the service
  • When your business grows you can upgrade processing and storage capacity usually without any downtime.

I chose Amazon RDS to use as a test because Amazon’s RDS evaluation tier was free for a year. Even though I have been working with VPS from various providers including Vultr and DigitalOcean, this was the first time I have ever used Amazon cloud services. I was pleased that I managed to have a Drupal view based on the cloud database just over an hour after I created my instance. I created an Amazon Aurora instance. Quoting from the product page, “Amazon Aurora is a MySQL-compatible, relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases”. Using a term I often see banded about, Amazon Aurora is like MySQL on steroids. Put another way we now have a Drupal view on steroids.

Implementation

I sketched out an outline of this post before implementing the idea. I thought the implementation would be the longest part of the post but to be honest the “hardest” part was getting familiar enough with the AWS user interface and terminology. Since Amazon Aurora is a MySQL database, connecting to it is no different from connecting to any other MySQL/MariaDB database. Most Drupal installations use MySQL as the back-end. These and the main steps I had followed:

  • Enable remote connections to Aurora database instance from the VPS running my demo site.
  • Set up phpMyAdmin running on same server as demo site to connect to my Amazon Aurora instance. Here are the steps I followed. Using phpMyAdmin I installed the Chinook database on Amazon Aurora instance.
  • I created a database connection in settings.php file. Below is the syntax for my connection.

$databases['amazon']['default'] = array(
'driver' => 'mysql',
'database' => 'chinook_rds',
'username' => 'my_user_name',
'password' => 'my_password',
'host' => 'database_name.****.us-west-*.rds.amazonaws.com',
);

I then created a view using the Views Database Connector (VDC) module. I followed the steps from an earlier post to create a view from an external database.

Conclusion

Connecting to an Amazon Aurora instance proved to be no different to connecting to any other MySQL database. I came up with this analogy to illustrate the significance of using a cloud database as the data source for a view. Opening a door is nothing amazing, even my three year old daughter can open a door standing on her chair. Opening the door to the bank vault is probably more rewarding than opening the door to the shower. The point I am making is that being able to connect to cloud databases enables you to query potentially very large volumes of data. I am not encouraging you to display large volumes of data using Drupal. You should only ever display summarised information, the data crunching should be passed on to the cloud database. I am more convinced that ever that organisations should consider Drupal as the platform to bring together disparate data sources, data silos, of information through one interface.



Tags amazon rds/ business intelligence/ drupal as data platform/ drupal planet/

Related Posts

Connecting phpMyAdmin to Amazon RDS
From Drupal to Hugo