| Title: | Rendering Parameterized SQL and Translation to Dialects |
|---|---|
| Description: | 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', 'Snowflake', 'Azure Synapse Analytics Dedicated', 'Apache Spark', 'SQLite', and 'InterSystems IRIS'. |
| Authors: | Martijn Schuemie [aut, cre], Marc Suchard [aut] |
| Maintainer: | Martijn Schuemie <[email protected]> |
| License: | Apache License 2.0 |
| Version: | 1.19.0 |
| Built: | 2024-11-08 05:44:17 UTC |
| Source: | https://github.com/ohdsi/sqlrender |
Convert a camel case string to snake case
camelCaseToSnakeCase(string)camelCaseToSnakeCase(string)
string |
The string to be converted |
A string
camelCaseToSnakeCase("exposureConceptId1")camelCaseToSnakeCase("exposureConceptId1")
Convert the names of an object from camel case to snake case
camelCaseToSnakeCaseNames(object)camelCaseToSnakeCaseNames(object)
object |
The object of which the names should be converted |
The same object, but with converted names.
x <- data.frame(conceptId = 1, conceptName = "b") camelCaseToSnakeCaseNames(x)x <- data.frame(conceptId = 1, conceptName = "b") camelCaseToSnakeCaseNames(x)
Convert a camel case string to title case
camelCaseToTitleCase(string)camelCaseToTitleCase(string)
string |
The string to be converted |
A string
camelCaseToTitleCase("exposureConceptId1")camelCaseToTitleCase("exposureConceptId1")
createRWrapperForSql creates an R wrapper for a parameterized SQL file. The created R script
file will contain a single function, that executes the SQL, and accepts the same parameters as
specified in the SQL.
createRWrapperForSql( sqlFilename, rFilename, packageName, createRoxygenTemplate = TRUE )createRWrapperForSql( sqlFilename, rFilename, packageName, createRoxygenTemplate = TRUE )
sqlFilename |
The SQL file. |
rFilename |
The name of the R file to be generated. Defaults to the name of the SQL file with the extension reset to R. |
packageName |
The name of the package that will contains the SQL file. |
createRoxygenTemplate |
If true, a template of Roxygen comments will be added. |
This function reads the declarations of defaults in the parameterized SQL file, and creates an R
function that exposes the parameters. It uses the loadRenderTranslateSql function, and
assumes the SQL will be used inside a package. To use inside a package, the SQL file should be
placed in the inst/sql/sql_server folder of the package.
## Not run: # This will create a file called CohortMethod.R: createRWrapperForSql("CohortMethod.sql", packageName = "CohortMethod") ## End(Not run)## Not run: # This will create a file called CohortMethod.R: createRWrapperForSql("CohortMethod.sql", packageName = "CohortMethod") ## End(Not run)
Get the prefix used for emulated temp tables for DBMSs that do not support temp tables (e.g. Oracle, BigQuery).
getTempTablePrefix()getTempTablePrefix()
The prefix string.
getTempTablePrefix()getTempTablePrefix()
Launch the SqlRender Developer Shiny app
launchSqlRenderDeveloper(launch.browser = TRUE)launchSqlRenderDeveloper(launch.browser = TRUE)
launch.browser |
Should the app be launched in your default browser, or in a Shiny window. Note: copying to clipboard will not work in a Shiny window. |
Launches a Shiny app that allows the user to develop SQL and see how it translates to the supported dialects.
List the target dialects supported by the translate function.
listSupportedDialects()listSupportedDialects()
A data frame with two columns. The 'dialect' column contains the abbreviation used in SqlRender, and the 'description' column contains a more human-readable description.
listSupportedDialects()listSupportedDialects()
loadRenderTranslateSql Loads a SQL file contained in a package, renders it and translates it
to the specified dialect
loadRenderTranslateSql( sqlFilename, packageName, dbms = "sql server", ..., tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"), oracleTempSchema = NULL, warnOnMissingParameters = TRUE )loadRenderTranslateSql( sqlFilename, packageName, dbms = "sql server", ..., tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"), oracleTempSchema = NULL, warnOnMissingParameters = TRUE )
sqlFilename |
The source SQL file |
packageName |
The name of the package that contains the SQL file |
dbms |
The target dialect. Currently 'sql server', 'oracle', 'postgres', and 'redshift' are supported |
... |
Parameter values used for |
tempEmulationSchema |
Some database platforms like Oracle and Impala do not truly support temp tables. To emulate temp tables, provide a schema with write privileges where temp tables can be created. |
oracleTempSchema |
DEPRECATED: use |
warnOnMissingParameters |
Should a warning be raised when parameters provided to this function do not appear in the parameterized SQL that is being rendered? By default, this is TRUE. |
This function looks for a SQL file with the specified name in the inst/sql/<dbms> folder of the
specified package. If it doesn't find it in that folder, it will try and load the file from the
inst/sql or inst/sql/sql_server folder and use the translate function to translate it to the
requested dialect. It will subsequently call the render function with any of the additional
specified parameters.
Returns a string containing the rendered SQL.
## Not run: renderedSql <- loadRenderTranslateSql("CohortMethod.sql", packageName = "CohortMethod", dbms = connectionDetails$dbms, CDM_schema = "cdmSchema" ) ## End(Not run)## Not run: renderedSql <- loadRenderTranslateSql("CohortMethod.sql", packageName = "CohortMethod", dbms = connectionDetails$dbms, CDM_schema = "cdmSchema" ) ## End(Not run)
readSql loads SQL from a file
readSql(sourceFile)readSql(sourceFile)
sourceFile |
The source SQL file |
readSql loads SQL from a file
Returns a string containing the SQL.
## Not run: readSql("myParamStatement.sql") ## End(Not run)## Not run: readSql("myParamStatement.sql") ## End(Not run)
render Renders SQL code based on parameterized SQL and parameter values.
render(sql, warnOnMissingParameters = TRUE, ...)render(sql, warnOnMissingParameters = TRUE, ...)
sql |
The parameterized SQL |
warnOnMissingParameters |
Should a warning be raised when parameters provided to this function do not appear in the parameterized SQL that is being rendered? By default, this is TRUE. |
... |
Parameter values |
This function takes parameterized SQL and a list of parameter values and renders the SQL that can be send to the server. Parameterization syntax:
Parameters are indicated using a @ prefix, and are replaced with the actual values provided in the render call.
Default values for parameters can be defined using curly and the DEFAULT keyword.
The if-then-else pattern is used to turn on or off blocks of SQL code.
A character string containing the rendered SQL.
render("SELECT * FROM @a;", a = "myTable") render("SELECT * FROM @a {@b}?{WHERE x = 1};", a = "myTable", b = "true") render("SELECT * FROM @a {@b == ''}?{WHERE x = 1}:{ORDER BY x};", a = "myTable", b = "true") render("SELECT * FROM @a {@b != ''}?{WHERE @b = 1};", a = "myTable", b = "y") render("SELECT * FROM @a {1 IN (@c)}?{WHERE @b = 1};", a = "myTable", b = "y", c = c(1, 2, 3, 4) ) render("{DEFAULT @b = \"someField\"}SELECT * FROM @a {@b != ''}?{WHERE @b = 1};", a = "myTable" ) render("SELECT * FROM @a {@a == 'myTable' & @b != 'x'}?{WHERE @b = 1};", a = "myTable", b = "y" ) render( sql = "SELECT * FROM @a;", warnOnMissingParameters = FALSE, a = "myTable", b = "missingParameter" )render("SELECT * FROM @a;", a = "myTable") render("SELECT * FROM @a {@b}?{WHERE x = 1};", a = "myTable", b = "true") render("SELECT * FROM @a {@b == ''}?{WHERE x = 1}:{ORDER BY x};", a = "myTable", b = "true") render("SELECT * FROM @a {@b != ''}?{WHERE @b = 1};", a = "myTable", b = "y") render("SELECT * FROM @a {1 IN (@c)}?{WHERE @b = 1};", a = "myTable", b = "y", c = c(1, 2, 3, 4) ) render("{DEFAULT @b = \"someField\"}SELECT * FROM @a {@b != ''}?{WHERE @b = 1};", a = "myTable" ) render("SELECT * FROM @a {@a == 'myTable' & @b != 'x'}?{WHERE @b = 1};", a = "myTable", b = "y" ) render( sql = "SELECT * FROM @a;", warnOnMissingParameters = FALSE, a = "myTable", b = "missingParameter" )
This function has been deprecated. Use render instead. This new function returns a
character vector instead of a list.
renderSql(sql = "", warnOnMissingParameters = TRUE, ...)renderSql(sql = "", warnOnMissingParameters = TRUE, ...)
sql |
The parameterized SQL |
warnOnMissingParameters |
Should a warning be raised when parameters provided to this function do not appear in the parameterized SQL that is being rendered? By default, this is TRUE. |
... |
Parameter values |
A list containing the following elements:
The original parameterized SQL code
The rendered sql
renderSqlFile Renders SQL code in a file based on parameterized SQL and parameter values,
and writes it to another file.
renderSqlFile(sourceFile, targetFile, warnOnMissingParameters = TRUE, ...)renderSqlFile(sourceFile, targetFile, warnOnMissingParameters = TRUE, ...)
sourceFile |
The source SQL file |
targetFile |
The target SQL file |
warnOnMissingParameters |
Should a warning be raised when parameters provided to this function do not appear in the parameterized SQL that is being rendered? By default, this is TRUE. |
... |
Parameter values |
This function takes parameterized SQL and a list of parameter values and renders the SQL that can be send to the server. Parameterization syntax:
Parameters are indicated using a @ prefix, and are replaced with the actual values provided in the render call.
Default values for parameters can be defined using curly and the DEFAULT keyword.
The if-then-else pattern is used to turn on or off blocks of SQL code.
## Not run: renderSqlFile("myParamStatement.sql", "myRenderedStatement.sql", a = "myTable") ## End(Not run)## Not run: renderSqlFile("myParamStatement.sql", "myRenderedStatement.sql", a = "myTable") ## End(Not run)
Convert a snake case string to camel case
snakeCaseToCamelCase(string)snakeCaseToCamelCase(string)
string |
The string to be converted |
A string
snakeCaseToCamelCase("exposure_concept_id_1")snakeCaseToCamelCase("exposure_concept_id_1")
Convert the names of an object from snake case to camel case
snakeCaseToCamelCaseNames(object)snakeCaseToCamelCaseNames(object)
object |
The object of which the names should be converted |
The same object, but with converted names.
x <- data.frame(concept_id = 1, concept_name = "b") snakeCaseToCamelCaseNames(x)x <- data.frame(concept_id = 1, concept_name = "b") snakeCaseToCamelCaseNames(x)
This function is for Spark connections only, it handles insert commands, as Spark cannot handle inserts with aliased or subset columns.
sparkHandleInsert(sql, connection)sparkHandleInsert(sql, connection)
sql |
The SQL to be translated. |
connection |
The connection to the database server. |
A sql string with INSERT command modified to contain the full column list, padded with NULLS as needed.
splitSql splits a string containing multiple SQL statements into a vector of SQL statements
splitSql(sql)splitSql(sql)
sql |
The SQL string to split into separate statements |
This function is needed because some DBMSs (like ORACLE) do not accepts multiple SQL statements being sent as one execution.
A vector of strings, one for each SQL statement
splitSql("SELECT * INTO a FROM b; USE x; DROP TABLE c;")splitSql("SELECT * INTO a FROM b; USE x; DROP TABLE c;")
Tests Java virtual machine (JVM) java.version system property to check if version >= 8.
supportsJava8()supportsJava8()
Returns TRUE if JVM supports Java >= 8.
supportsJava8()supportsJava8()
translate translates SQL from one dialect to another.
translate( sql, targetDialect, tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"), oracleTempSchema = NULL )translate( sql, targetDialect, tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"), oracleTempSchema = NULL )
sql |
The SQL to be translated |
targetDialect |
The target dialect. Currently "oracle", "postgresql", "pdw", "impala",
"sqlite", "sqlite extended", "netezza", "bigquery", "snowflake", "synapse", "spark",
"redshift", and "iris" are supported.
Use |
tempEmulationSchema |
Some database platforms like Oracle and Impala do not truly support temp tables. To emulate temp tables, provide a schema with write privileges where temp tables can be created. |
oracleTempSchema |
DEPRECATED: use |
This function takes SQL in one dialect and translates it into another. It uses simple pattern
replacement, so its functionality is limited. Note that trailing semicolons are not removed for
Oracle, which is required before sending a statement through JDBC. This will be done by
splitSql.
A character string containing the translated SQL.
translate("USE my_schema;", targetDialect = "oracle")translate("USE my_schema;", targetDialect = "oracle")
translateSingleStatement translates a single SQL statement from one dialect to another.
translateSingleStatement( sql = "", targetDialect, tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"), oracleTempSchema = NULL )translateSingleStatement( sql = "", targetDialect, tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"), oracleTempSchema = NULL )
sql |
The SQL to be translated |
targetDialect |
The target dialect. Currently "oracle", "postgresql", "pdw", "impala", "sqlite", "sqlite extended", "netezza", "bigquery", "snowflake", "synapse", "spark", "redshift", and "iris" are supported. |
tempEmulationSchema |
Some database platforms like Oracle and Impala do not truly support temp tables. To emulate temp tables, provide a schema with write privileges where temp tables can be created. |
oracleTempSchema |
DEPRECATED: use |
This function takes SQL in one dialect and translates it into another. It uses simple pattern replacement, so its functionality is limited. This removes any trailing semicolon as required by Oracle when sending through JDBC. An error is thrown if more than one statement is encountered in the SQL.
A character vector with the translated SQL.
translateSingleStatement("USE my_schema;", targetDialect = "oracle")translateSingleStatement("USE my_schema;", targetDialect = "oracle")
This function has been deprecated. Use translate instead. This new function returns a
character vector instead of a list.
translateSql(sql = "", targetDialect, oracleTempSchema = NULL)translateSql(sql = "", targetDialect, oracleTempSchema = NULL)
sql |
The SQL to be translated |
targetDialect |
The target dialect. Currently "oracle", "postgresql", "pdw", "impala", "netezza", "bigquery", "snowflake", "synapse", "spark", "redshift", and "iris" are supported |
oracleTempSchema |
A schema that can be used to create temp tables in when using Oracle or Impala. |
A list containing the following elements:
The original parameterized SQL code
The translated SQL
This function takes SQL and translates it to a different dialect.
translateSqlFile( sourceFile, targetFile, targetDialect, tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"), oracleTempSchema = NULL )translateSqlFile( sourceFile, targetFile, targetDialect, tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"), oracleTempSchema = NULL )
sourceFile |
The source SQL file |
targetFile |
The target SQL file |
targetDialect |
The target dialect. Currently "oracle", "postgresql", "pdw", "impala", "sqlite", "netezza", "bigquery", "snowflake", "synapse", "spark", "redshift" and "iris" are supported. |
tempEmulationSchema |
Some database platforms like Oracle and Impala do not truly support temp tables. To emulate temp tables, provide a schema with write privileges where temp tables can be created. |
oracleTempSchema |
DEPRECATED: use |
This function takes SQL and translates it to a different dialect.
## Not run: translateSqlFile("myRenderedStatement.sql", "myTranslatedStatement.sql", targetDialect = "postgresql" ) ## End(Not run)## Not run: translateSqlFile("myRenderedStatement.sql", "myTranslatedStatement.sql", targetDialect = "postgresql" ) ## End(Not run)
writeSql writes SQL to a file
writeSql(sql, targetFile)writeSql(sql, targetFile)
sql |
A string containing the sql |
targetFile |
The target SQL file |
writeSql writes SQL to a file
## Not run: sql <- "SELECT * FROM @table_name" writeSql(sql, "myParamStatement.sql") ## End(Not run)## Not run: sql <- "SELECT * FROM @table_name" writeSql(sql, "myParamStatement.sql") ## End(Not run)