42

SqlRender

Rendering Parameterized SQL and Translation to Dialects

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', 'Apache Spark', and 'SQLite'.

SqlRender

Build Status codecov.io CRAN_Status_Badge CRAN_Status_Badge

SqlRender is part of the OHDSI Methods Library.

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

  • 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, and SQLite)
  • Can be used as R package, Java library, or as stand-alone executable through a command-line interface

Examples

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

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.

Installation

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

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:
<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.0.0-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

Support

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

  • This project is supported in part through the National Science Foundation grant IIS 1251151.

News

SqlRender 1.6.0

Changes:

  1. Major overhaul of BigQuery translation.

  2. Added support for SQLite.

  3. ISNUMERIC translation implemented for Impala, Netezza, and BigQuery.

  4. Performance improvement for Impala temp tables (CREATE TABLE ... STORED AS PARQUET).

  5. Adding functions render and translate that output strings instead of lists. Deprecating renderSql and translateSql.

  6. Added function translateSingleStatement.

Bugfixes:

  1. Dropping WHERE clause when translating CREATE INDEX for PDW.

  2. Fixed PDW's equivalent of CREATE TABLE IF NOT EXISTS.

SqlRender 1.5.3

Changes:

  1. Added translation rules for DATETIME2.

Bugfixes:

  1. Fixed misspelling of DISTRIBUTE in Netezza translation rules.

SqlRender 1.5.2

Changes:

  1. Translation for indexes for RedShift and Impala.
  2. Translation for UPDATE STATISTICS.

Bugfixes:

  1. Fixed translation of AS when used for table names in Oracle.

SqlRender 1.5.0

Changes:

  1. Improved support for Netezza.
  2. Added random distribution hints for PDW, RedShift, Netezza.
  3. Improved MPP index translation for PDW and Netezza.
  4. Warnings about missing parameters when rendering SQL can now be turned off.

Bugfixes:

  1. Translation of camelCase to snake_case now adds underscore before numbers.

SqlRender 1.4.8

Changes:

  1. Better handling of 'FROM DUAL' for Oracle.
  2. Improved support for Netezza and Impala.

SqlRender 1.4.6

Changes:

  1. Bigquery support for mismatched string and int arguments in coalesce.
  2. Translate decimal to float for BigQuery.
  3. Created rules to add dummy 'group by' for Oracle statements combining 'case' and 'count' to prevent Oracle from crashing.
  4. Adding 'UNBOUNDED PRECEDING' to RedShift windowing functions.

SqlRender 1.4.3

Changes:

  1. Added a Shiny app for developing parameterized SQL, and view how this would be rendered and translated into the various supported dialects.
  2. Added support for Google BigQuery.
  3. Added many more rules for Amazon RedShift, including support for optimization hints.
  4. Added rules for DELETE FROM translation for Impala.

Bugfixes:

  1. Fixed issue when splitting SQL containing hints.

SqlRender 1.3.7

Changes:

  1. Added translation rules for ISNUMERIC and LOG(@expression, @base)

Bugfixes:

  1. Fixed bug when trying to split SQL where reserved word 'end' is used as a field name.
  2. Fixes for Impala translations.
  3. Fixed translation issues for Oracle involving 'FROM DUAL'.
  4. Added workaround for Oracle bug for intervals greater than 99 days.
  5. Fixed bug when trying to split SQL where last line has comment but no EOL.

SqlRender 1.3.0

Changes:

  1. Added ability to use regular expression in translation patterns. This allowed SELECT TOP n to be translated.
  2. Deprecated sourceDialect argument.
  3. Added translation for CONCAT function with >2 arguments to Oracle (which only allows 2 arguments)
  4. Added hints for translation optimation to massive parallel platforms like RedShift
  5. Throw warnings when translateSql is called with variable names that are not in the SQL
  6. Throw warnings when table names are too long for Oracle

Bugfixes:

  1. Fixed translation for date functions so they will now work properly with datetime fields as well.
  2. Now throwing error when boolean logic cannot be parsed (instead of assuming result is TRUE)

SqlRender 1.2.0

Changes:

  1. Added support for Impala

Bugfixes:

  1. Fixed translation for DATEFROMPARTS for RedShift

SqlRender 1.1.7

Changes: initial submission to CRAN

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.
install.packages("SqlRender")

1.8.1 by Martijn Schuemie, 10 days ago

https://ohdsi.github.io/SqlRender/, https://github.com/OHDSI/SqlRender

Report a bug at https://github.com/OHDSI/SqlRender/issues

Browse source code at https://github.com/cran/SqlRender

Authors: Martijn Schuemie [aut, cre] , Marc Suchard [aut]

Documentation:   PDF Manual  

Apache License 2.0 license

Imports rJava, rlang

Suggests testthat, knitr, rmarkdown, shiny, shinydashboard

System requirements: Java version 8 or higher (https://www.java.com/)

Imported by DatabaseConnector, Eunomia.

See at CRAN

R links

R homepage

Download R

Mailing lists

R documentation

R manuals

R FAQs

The R Journal

CRAN links

CRAN homepage

CRAN repository policy

Submit a package

METACRAN stuff

About METACRAN

At github

Report a bug