Fork me on GitHub

Version 1.0 of sqlutils available on CRAN


January 15, 2013
Categorized as: R, R-Bloggers, SQL


Version 1.0 of sqlutils has been released to CRAN. The sqlutils package is designed to manage a library of SQL files. This package grew out of the needs of an Office of Institutional Research where the vast majority of analysis is conducted on data from our Student Information System (SIS) which is stored in an Oracle database. A lot of our analyses and reports are derived from the same types of datasets but from easily extracted parameters (e.g. date range, program name, status, etc.). We used to store SQL commands in our R scripts but that can become quite cumbersome and in many ways, reduced the ease of reusability which is a major reason for using R in the first place, hence the birth of sqlutils. For our purposes we currently have over 40 SQL files that have been well vetted and documented. To share the library we simply add the following to our .Rprofile script:

require(sqlutils)
sqlPaths('/Path/to/shared/directory')

A full introduction to the squtils package is available here as well as on the Github project page. A key advantage to using sqlutils is that you can store your queries in plain text files (with a .sql file extension) and document them using roxygen2 style comments. Moreover, R function parameters are used to set parameters within the SQL command. Parameters are defined in SQL files using colon, parameter name, colon (i.e. :paramName:) format. Using this framework, it is easy to create a data dictionary of the library of SQL files.

Lastly, I wrote about an interactive SQL mode in R a few days ago. The isql function is included in the sqlutils package.

comments powered by Disqus

= Github page; = RSS XML Feed; = External website; = Portable Document File (PDF)
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License. Creative Commons License
Formulas powered by MathJax