R and Databases

Abstract

While many tasks that used to be performed using relational databases can be easily implemented in R, there are some situations where using the power of a relational database nicely complements the capabilities of R. One obvious example are situations where the data to be used is stored in a relational database. Relational databases can also be used to make working with very large datasets easier. The topic of administration of a database is beyond the scope of this book, and the assumption will always be made that you have access to a running database, and that enough permissions have been granted to perform the necessary database operations. There are two principal ways to connect with databases in R. The first uses the ODBC (Open DataBase Connectivity) facility available on many comput-ers. The second uses the DBI package of R along with a specialized package for the particular database needed to be accessed. If there is a specialized package available for your database, you may find that the corresponding DBI-based package may give better performance than the ODBC approach. On the other hand, if you are using a database for which a specialized package is not available, using ODBC may be your only option. 3.1 A Brief Guide to SQL 3.1.1 Navigation Commands Since a single server may hold more than one database, each with potentially many tables, and since each table can contain many columns (variables), it may be useful to examine exactly what's available in a database before starting to work with it. Often there are graphical clients available to communicate with databases that will present this information in a convenient form, but R can also be used to create data frames containing this information. The table below shows some common tasks and the SQL statements to execute