A rendering tool for parameterized SQL that also translates into different SQL dialects. These dialects include 'Microsoft Sql Server', 'Oracle', 'PostgreSql', 'Amazon RedShift', 'Apache Impala', 'IBM Netezza', 'Google BigQuery', 'Microsoft PDW', and 'SQLite'.
SqlRender is part of the OHDSI Methods Library.
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.
This exampe 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, "sql server", "oracle")
will produce the variable sql
containing this value:
"SELECT * FROM my_table; ALTER SESSION SET current_schema = my_schema;"
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("devtools")devtools::install_github("ohdsi/SqlRender")
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:
ohdsi repo.ohdsi.org http://repo.ohdsi.org:8085/nexus/content/repositories/releases ohdsi.snapshots repo.ohdsi.org-snapshots http://repo.ohdsi.org:8085/nexus/content/repositories/snapshots false true
2: Include the SqlRender dependency in your pom.xml
org.ohdsi.sql SqlRender 1.0.0-SNAPSHOT
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 ?
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.
Changes:
Major overhaul of BigQuery translation.
Added support for SQLite.
ISNUMERIC translation implemented for Impala, Netezza, and BigQuery.
Performance improvement for Impala temp tables (CREATE TABLE ... STORED AS PARQUET).
Adding functions render
and translate
that output strings instead of lists. Deprecating renderSql
and translateSql
.
Added function translateSingleStatement
.
Bugfixes:
Dropping WHERE clause when translating CREATE INDEX for PDW.
Fixed PDW's equivalent of CREATE TABLE IF NOT EXISTS.
Changes:
Bugfixes:
Changes:
Bugfixes:
Changes:
Bugfixes:
Changes:
Changes:
Changes:
Bugfixes:
Changes:
Bugfixes:
Changes:
Bugfixes:
Changes:
Bugfixes:
Changes: initial submission to CRAN