search

OHDSI / SqlRender

This is an R package and Java library for rendering parameterized SQL, and translating it to different SQL dialects.
https://ohdsi.github.io/SqlRender
Other
82 stars 77 forks source link
hades

readme

SqlRender

Build Status codecov.io CRAN_Status_Badge CRAN_Status_Badge

SqlRender is part of HADES.

Introduction

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.

Features

Examples

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;"

SqlDeveloper

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.

Technology

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.

System Requirements

Running the package requires R with the package rJava installed. Also requires Java 1.6 or higher. It is highly recommended for best performance to use a version of R that is at least version 4 or higher otherwise, unexpected bugs or errors may occur.

Installation

R package

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()

Java library

You can fetch the JAR file in the inst/java folder of this repository, or use Maven:

  1. First add the SqlRender repository so that maven can find and download the SqlRender artifact automatically: 
    <repository>
<id>ohdsi</id>
<name>repo.ohdsi.org</name>
<url>https://repo.ohdsi.org/nexus/content/repositories/releases</url>
</repository>
<repository>
<id>ohdsi.snapshots</id>
<name>repo.ohdsi.org-snapshots</name>
<url>https://repo.ohdsi.org/nexus/content/repositories/snapshots</url>
<releases>
  <enabled>false</enabled>
</releases>
<snapshots>
  <enabled>true</enabled>
</snapshots>
</repository>

    2: Include the SqlRender dependency in your pom.xml

    <dependency>
<groupId>org.ohdsi.sql</groupId>
<artifactId>SqlRender</artifactId>
<version>1.9.2-SNAPSHOT</version>
</dependency>

Command-line executable

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 ?

User Documentation

Documentation can be found on the package website.

PDF versions of the documentation are also available:

Support

Contributing

Read here how you can contribute to this package.

License

SqlRender is licensed under Apache License 2.0

Development

SqlRender is being developed in R Studio.

Development status

Stable. The code is actively being used in several projects.

Acknowledgements