SQL Render#

get_temp_table_prefix()#

Get the temporary table prefix

Used for emulated temp tables for DBMSs that do not support temp tables (e.g. Oracle, BigQuery).

Wraps the R SqlRender::getTempTablePrefix function defined in SqlRender/R/RenderSql.R.

Returns:

The prefix used for emulated temp tables

Return type:

str

Examples

>>> get_temp_table_prefix()
list_supported_dialects()#

List the supported dialects

Wraps the R SqlRender::listSupportedDialects function defined in SqlRender/R/HelperFunctions.R.

Returns:

A dataframe with the supported dialects

Return type:

DataFrame

load_render_translate_sql(sql_file, package_name, dbms, temp_emulation_schema=None, warn_on_missing_parameters=True)#

Load, render, and translate a SQL file in a package

loadRenderTranslateSql Loads a SQL file contained in a package, renders it and translates it to the specified dialect.

Wraps the R SqlRender::loadRenderTranslateSql function defined in SqlRender/R/HelperFunctions.R.

Parameters:

  • sql_file (str | Path) – The source SQL file

  • package_name (str) – The package name

  • dbms (str) – The target dialect. Currently “oracle”, “postgresql”, “pdw”, “impala”, “sqlite”, “sqlite extended”, “netezza”, “bigquery”, “snowflake”, “synapse”, “spark”, and “redshift” are supported. Use list_supported_dialects to get the list of supported dialects.

  • temp_emulation_schema (str | None) – 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.

  • warn_on_missing_parameters (bool, optional) – If True, a warning is issued if a parameter is not found in the parameter list, by default True

Return type:

StrVector

read_sql(source_file)#

loads SQL from a file

Wraps the R SqlRender::readSql function defined in SqlRender/R/HelperFunctions.R.

Parameters:

source_file (str | Path) – The source SQL file

Returns:

The SQL from the file

Return type:

StrVector

render(sql, warn_on_missing_parameters=True, **kwargs)#

Renders SQL code based on parameterized SQL and parameter values

This function takes parameterized SQL and a list of parameter values and renders the SQL that can be send to the server.

Wraps the R SqlRender::render function defined in SqlRender/R/RenderSql.R.

Parameters:

  • sql (str) – The parameterized SQL.

  • warnOnMissingParameters (bool) – 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

  • kwargs (dict) –

    The parameter values.

    param sql:

    The parameterized SQL.

    type sql:

    str

Return type:

StrVector

Examples

>>> sql = "SELECT * FROM table WHERE id = @id"
>>> render("SELECT * FROM @a;", a = "myTable")
render_sql_file(source_file, target_file, warnOnMissingParameters=True, **kwargs)#

Render a SQL file

Renders SQL code in a file based on parameterized SQL and parameter values, and writes it to another file.

Wraps the R SqlRender::renderSqlFile function defined in SqlRender/R/HelperFunctions.R.

Parameters:

  • source_file (str | Path) – The source SQL file

  • target_file (str | Path) – The target SQL file

  • warnOnMissingParameters (bool, optional) – If True, a warning is issued if a parameter is not found in the parameter list, by default True

  • kwargs – The parameters to use for rendering

Return type:

None

Examples

>>> renderSqlFile(
>>>     "myParamStatement.sql",
>>>     "myRenderedStatement.sql",
>>>     a = "myTable"
>>> )
spark_handle_insert(sql, connection)#

Handles Spark Inserts

This function is for Spark connections only, it handles insert commands, as Spark cannot handle inserts with aliased or subset columns.

Wraps the R SqlRender::sparkHandleInsert function defined in SqlRender/R/SparkSql.R.

Parameters:

  • sql (str) – The SQL to be translated.

  • connection (str) – The connection to the database server.

Returns:

A sql string with INSERT command modified to contain the full column list, padded with NULLS as needed.

Return type:

StrVector

split_sql(sql)#

Split a single SQL string into one or more SQL statements

splitSql splits a string containing multiple SQL statements into a vector of SQL statements. This function is needed because some DBMSs (like ORACLE) do not accepts multiple SQL statements being sent as one execution.

Wraps the R SqlRender::splitSql function defined in SqlRender/R/RenderSql.R.

Parameters:

sql (str) – The SQL string to split into separate statements

Returns:

A vector of strings, one for each SQL statement

Return type:

str

Examples

>>> split_sql("SELECT * INTO a FROM b; USE x; DROP TABLE c;")
translate(sql, target_dialect, temp_emulation_schema=None)#

Translates SQL from one dialect to another

Wraps the R SqlRender::translate function defined in SqlRender/R/RenderSql.R.

Parameters:

  • sql (str) – The SQL to be translated

  • target_dialect (str) – The target dialect. Currently “oracle”, “postgresql”, “pdw”, “impala”, “sqlite”, “sqlite extended”, “netezza”, “bigquery”, “snowflake”, “synapse”, “spark”, and “redshift” are supported. Use list_supported_dialects to get the list of supported dialects.

  • temp_emulation_schema (str | None) – 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.

Returns:

The translated SQL.

Return type:

StrVector

translate_single_statement(sql, target_dialect, temp_emulation_schema=None)#

Translates a single SQL statement from one dialect to another

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.

Wraps the R SqlRender::translateSingleStatement function defined in SqlRender/R/RenderSql.R.

Parameters:

  • sql (str) – The SQL to be translated

  • target_dialect (str) – The target dialect. Currently “oracle”, “postgresql”, “pdw”, “impala”, “sqlite”, “sqlite extended”, “netezza”, “bigquery”, “snowflake”, “synapse”, “spark”, and “redshift” are supported. Use list_supported_dialects to get the list of supported dialects.

  • temp_emulation_schema (str | None) – 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.

Returns:

The translated SQL.

Return type:

str

Examples

>>> translate_single_statement(
>>>     "USE my_schema;",
>>>     targetDialect = "oracle"
>>> )
translate_sql_file(source_file, target_file, target_dialect, temp_emulation_schema=None)#

Translate a SQL file

This function takes SQL and translates it to a different dialect.

Wraps the R SqlRender::translateSqlFile function defined in SqlRender/R/HelperFunctions.R.

Parameters:

  • source_file (str | Path) – The source SQL file

  • target_file (str | Path) – The target SQL file

  • target_dialect (str) – The target dialect. Currently “oracle”, “postgresql”, “pdw”, “impala”, “sqlite”, “sqlite extended”, “netezza”, “bigquery”, “snowflake”, “synapse”, “spark”, and “redshift” are supported. Use list_supported_dialects to get the list of supported dialects.

  • temp_emulation_schema (str | None) – 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.

Return type:

None

Examples

>>> translateSqlFile(
>>>     "mySql.sql",
>>>     "myTranslatedSql.sql",
>>>     targetDialect = "oracle"
>>> )
write_sql(sql, file)#

Write SQL to a SQL (text) file

Wraps the R SqlRender::writeSql function defined in SqlRender/R/HelperFunctions.R.

Parameters:

  • sql (str) – The SQL to write

  • file (str | Path) – The target SQL file

Return type:

None

Examples

>>> write_sql("SELECT * FROM table;", "my_sql.sql")