Skip to contents

SnowflakeConnector: An R6 Snowflake ODBC helper

SnowflakeConnector.Rd

SnowflakeConnector encapsulates a Snowflake ODBC connection with:

  • Safe SQL interpolation via glue::glue_sql() bound to the connection

  • Query history tracking (run_query_history)

  • data.table::setattr lineage under "snowflake-sources"

  • Simple transactions ($transaction_begin/commit/rollback)

  • Simple writes ($write_data)

Credentials are expected to be provided by your DSN and/or the constructor arguments (uid, pwd, etc.). This package does not read YAML files.

Public fields

connection

The live DBI ODBC connection object.

run_query_history

data.table with columns query and result.

See also

snowflake_get_query_dsn, sqlQuerySources

Public fields

connection

The live DBI ODBC connection object.

run_query_history

data.table with columns query and result.

Methods

Public methods

Method new()

Construct a new connector (opens a connection).

Usage

SnowflakeConnector$new(
  dsn,
  uid = NULL,
  pwd = NULL,
  database = NULL,
  schema = NULL,
  role = NULL,
  warehouse = NULL,
  timezone = Sys.timezone(),
  timezone_out = Sys.timezone(),
  ...
)

Arguments

dsn

ODBC DSN name.

uid

Optional user id (overrides DSN).

pwd

Optional password (overrides DSN).

database

Optional default database.

schema

Optional default schema.

role

Optional default role.

warehouse

Optional default warehouse.

timezone

Server timezone for the connection. Defaults to Sys.timezone().

timezone_out

Timezone for datetimes returned to R. Defaults to Sys.timezone().

...

Additional arguments passed to DBI::dbConnect().

Method close()

Close the connection.

Usage

SnowflakeConnector$close()

Method run_query()

Execute a SELECT and return a data.table (with lineage attribute).

Usage

SnowflakeConnector$run_query(
  SQL,
  literal = FALSE,
  glue_envir = parent.frame(1L)
)

Arguments

SQL

SQL string (glue placeholders allowed).

literal

If TRUE, skip parameter quoting (passed to glue_sql()).

glue_envir

Environment where glue placeholders are evaluated.

Method write_data()

Write a data.frame / data.table into Snowflake.

Usage

SnowflakeConnector$write_data(table, value, ...)

Arguments

table

Target table name (character) or DBI::Id.

value

Data to write (data.frame / data.table).

...

Passed to DBI::dbWriteTable() (e.g., append = TRUE, overwrite = TRUE).

Method transaction_begin()

Begin a transaction.

Usage

SnowflakeConnector$transaction_begin()

Method transaction_commit()

Commit a transaction.

Usage

SnowflakeConnector$transaction_commit()

Method transaction_rollback()

Roll back a transaction.

Usage

SnowflakeConnector$transaction_rollback()

Method clone()

The objects of this class are cloneable with this method.

Usage

SnowflakeConnector$clone(deep = FALSE)

Arguments

deep

Whether to make a deep clone.

Examples

if (FALSE) { # \dontrun{
con <- SnowflakeConnector$new(
  dsn = "snowflake-bi",
  role = "ANALYST",
  warehouse = "BI_WH",
  database = "PROD_DB",
  schema = "PRESENTATION"
)
con$run_query("show tables;")
con$run_query("select 1 as x")
con$run_query_history
con$close()
} # }