SqlRender is part of HADES.
This is an R package for rendering parameterized SQL, and translating it to different SQL dialects. SqlRender can also be used as a stand-alone Java library and a command-line executable.
- Supports a simple markup syntax for making SQL parameterized, and renders parameterized SQL (containing the markup syntax) to executable SQL
- The syntax supports defining default parameter values
- The syntax supports if-then-else structures
- Has functions for translating SQL from one dialect (Microsoft SQL Server) to other dialects (Oracle, PostgreSQL, Amazon RedShift, Impala, IBM Netezza, Google BigQuery, Microsoft PDW, Apache Spark, and SQLite)
- Can be used as R package, Java library, or as stand-alone executable through a command-line interface
This example shows the use of parameters, as well as SqlRender's {if} ? {then} : {else} syntax:
sql <- render("SELECT * FROM @a; {@b != ''}?{USE @b;}", a = "my_table", b = "my_schema")
will produce the variable sql
containing this value:
"SELECT * FROM my_table; USE my_schema;"
subsequently running this code
sql <- translate(sql, "oracle")
will produce the variable sql
containing this value:
"SELECT * FROM my_table; ALTER SESSION SET current_schema = my_schema;"
The SqlDeveloper Shiny app is included in the SqlRender R package, and allows viewing the rendering and translation on the fly as you develop your SQL. The SqlDeveloper app is also available online here.
The SqlRender package is an R package wrapped around a Java library. The rJava package is used as interface.
The Java library is available as a JAR file.
Running the package requires R with the package rJava installed. Also requires Java 1.6 or higher.
In R, to install the latest stable version, install from CRAN:
install.packages("SqlRender")
To install the latest development version, install from GitHub:
install.packages("remotes")
remotes::install_github("ohdsi/SqlRender", ref = "develop")
Once installed, you can try out SqlRender in a Shiny app that comes with the package:
library(SqlRender)
launchSqlRenderDeveloper()
You can fetch the JAR file in the inst/java folder of this repository, or use Maven:
- First add the SqlRender repository so that maven can find and download the SqlRender artifact automatically:
<repositories>
<repository>
<id>ohdsi</id>
<name>repo.ohdsi.org</name>
<url>http://repo.ohdsi.org:8085/nexus/content/repositories/releases</url>
</repository>
<repository>
<id>ohdsi.snapshots</id>
<name>repo.ohdsi.org-snapshots</name>
<url>http://repo.ohdsi.org:8085/nexus/content/repositories/snapshots</url>
<releases>
<enabled>false</enabled>
</releases>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
</repositories>
2: Include the SqlRender dependency in your pom.xml
<dependency>
<groupId>org.ohdsi.sql</groupId>
<artifactId>SqlRender</artifactId>
<version>1.7.0-SNAPSHOT</version>
</dependency>
You can fetch the JAR file in the inst/java folder of this repository, or use Maven as described above. Run this from the command line to get a list of options:
java -jar SqlRender.jar ?
Documentation can be found on the package website.
PDF versions of the documentation are also available:
- Vignette: Using SqlRender
- Package manual: SqlRender manual
- Developer questions/comments/feedback: OHDSI Forum
- We use the GitHub issue tracker for all bugs/issues/enhancements
Read here how you can contribute to this package.
SqlRender is licensed under Apache License 2.0
SqlRender is being developed in R Studio.
Stable. The code is actively being used in several projects.
- This project is supported in part through the National Science Foundation grant IIS 1251151.