Friday, July 27, 2012

Connecting To a MySQL Database In "The Cloud"

Detailed worked examples on Google Apps Script-JDBC/MySQL chapter 6 in this book on Leanpub

2013-02-08: There is another blog entry on this here!
 This goes through the process of setting up a MySQL database in the cloud and re-capitulates some of the content here.


Watch this leanpub book for a chapter on Google Spreadsheets/MySQL



Update added 2013-0124: All the code  here is still valid but the MySQL instance that it points to has disappeared (for some reason?).  Suggestion: Set up your own free instance with Xeround or one of the other companies, including Google, who offer cloud MySQL instances.

Cloud computing is a current buzz term beloved by IT managers but when we use products such as gmail we are in using the "cloud" and when we do Google App scripting with JavaScript, we are programming in the "cloud".  Spreadsheets generally are excellent data analysis tools but are not good data storage mechanisms though they are often co-opted into this role.  Plugging a spreadsheet into a relational database management system (RDBMS) gives us the best of both worlds in that we have a secure system built on the sound fundamentals of the relational model coupled with an excellent data analysis and data presentation tool. But we need a mechanism to connect the two; In Excel we can use VBA and ADO to connect to any relational database for which we have a suitable database driver.  JavaScript and JDBC can be used in an analogous manner to connect Google spreadsheets to cloud databases.  This blog post provides an introduction on how to this. 


The steps are as follows:
  • Create an account with a provider of cloud database services, evaluation accounts are generally free.  For this example, I have used xeround to create an account for a free cloud MySQL database.
  • After creating the account, you need to log in to create and populate an actual database.  You can use the mysql command line client or a fancier GUI style application to do this.  My personal favorite is Sequel Pro on the Mac but the standard MySQL Workbench is fine too.  You can also access the phpAdmin application from the Xeround website.
  • The account allocated by Xeround gives its owner full admin permissions to create and drop database objects.  I used this account to create additional accounts with fewer privileges, one such account is the "read_only" used below.  It is always a good policy to use an account with the minimum privileges needed so if you only need to retrieve data, then a "read only" account should be used to avoid inadvertent updates, deletes etc.


The test database contains just four tables that store information on a random selection of 10 published medical papers retrieved from the NCBI PubMed resource (Bethesda, MD, USA).  Briefly the data model is as follows:

  • The pubmed_id is the ID issued by PubMed to uniquely identify a published article and is the primary key for the table "article".  
  • An article can have one or more authors and an author can be listed for one or more papers (many-to-many).  The "author_article" table is used to establish this relationship.
  • An article is associated with one or more Medical Subject Headings, these are collected in the table "article_keyword".

The schema diagram should help to clarify this (database name: pubmed_publication)



Here is the code to connect to my cloud MySQL Database:

// Connect to a cloud MySQL database using a readonly account.
// Execute the MySQL command "SHOW TABLES" to get a listing of the table names in the target database.
// Loop through the results set (rs) returned from the database and write the extracted values to the active spreadsheet.
// Perform cleanup by closing objects (important that this is done)
function connectToMySql() {
  var conn = Jdbc.getConnection("jdbc:mysql://instance22444.db.xeround.com:12775/pubmed_publication", "account_name", "account_password");
  var stmt = conn.createStatement();
  var rs = stmt.executeQuery("SHOW TABLES");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getRange('A1');
  var row = 0;
  while(rs.next()) {
    cell.offset(row, 0).setValue(rs.getString(1));
    row++;
  }
  rs.close();
  stmt.close();
  conn.close();
}

The code comments should clarify what this simple function is doing.

  • My Xeround account uses the free or "evaluation" package so, unsurprisingly, there are restrictions.  Firstly, the database can have only five connections open at any one time.  Secondly, its size is limited to 10KB.  Additionally, although I created the tables as type InnoDB because I wanted the referential integrity conferred by foreign key support, they are converted to xeround types which do not support foreign keys which is slightly annoying.
  • To run, email me at mick@javascript-spreadsheet-programming.com and I'll send the account name and account password for the readonly account.  Alternatively, you can create your own cloud MySQL and use that instead, I'm happy to send the source data and SQL to make and populate the database also.  I don't mind sharing this database as it is for testing only, I'm just concerned about exceeding the connection limit thereby making it unusable.
  • The new stuff here is the use of JDBC.  JDBC is, in my opinion, an excellent choice for this task since it is widely used in the Java world and is well documented.




This is a very brief introduction into a vast area that is full of potential.  We can combine knowledge of Google spreadsheets, JavaScript, JDBC, and SQL to create very powerful applications that access data in the cloud.  Using an RDBMS offers us data security and scalability, concurrency, etc so they are a perfect back end for spreadsheets.  I am more familiar with PostgreSQL than MySQL and would prefer to use it but, thus far, Google Apps do not appear to provide a JDBC driver for it.  Having said that, MySQL is a good alternative and probably has a bigger user base so I won't complain.  I'll post more on this in the future.

Meanwhile, check out the Google documentation on JDBC!

5 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete