Package 'odbc'

Description

Connect to Databricks clusters and SQL warehouses via the Databricks ODBC driver.

In particular, the custom dbConnect() method for the Databricks ODBC driver implements a subset of the Databricks client unified authentication model, with support for personal access tokens, OAuth machine-to-machine credentials, and OAuth user-to-machine credentials supplied via Posit Workbench or the Databricks CLI on desktop. All of these credentials are detected automatically if present using standard environment variables. In addition, on macOS platforms, the dbConnect method will check for irregularities with how the driver is configured, and attempt to fix in-situ, unless the odbc.no_config_override environment variable is set.

Usage

databricks()

## S4 method for signature 'DatabricksOdbcDriver'
dbConnect(
  drv,
  httpPath,
  workspace = Sys.getenv("DATABRICKS_HOST"),
  useNativeQuery = TRUE,
  driver = NULL,
  HTTPPath,
  uid = NULL,
  pwd = NULL,
  session = NULL,
  ...
)

Arguments

Value

An OdbcConnection object with an active connection to a Databricks cluster or SQL warehouse.

Examples

## Not run: 
DBI::dbConnect(
  odbc::databricks(),
  httpPath = "sql/protocolv1/o/4425955464597947/1026-023828-vn51jugj"
)

# Use credentials from the viewer (when possible) in a Shiny app
# deployed to Posit Connect.
server <- function(input, output, session) {
  conn <- DBI::dbConnect(
    odbc::databricks(),
    httpPath = "sql/protocolv1/o/4425955464597947/1026-023828-vn51jugj",
    session = session
  )
}

## End(Not run)

Description

Convenience functions for reading/writing DBMS tables

Usage

## S4 method for signature 'OdbcConnection,character,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  overwrite = FALSE,
  append = FALSE,
  temporary = FALSE,
  row.names = NULL,
  field.types = NULL,
  batch_rows = getOption("odbc.batch_rows", NA),
  ...
)

## S4 method for signature 'OdbcConnection,Id,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  overwrite = FALSE,
  append = FALSE,
  temporary = FALSE,
  row.names = NULL,
  field.types = NULL,
  batch_rows = getOption("odbc.batch_rows", NA),
  ...
)

## S4 method for signature 'OdbcConnection,SQL,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  overwrite = FALSE,
  append = FALSE,
  temporary = FALSE,
  row.names = NULL,
  field.types = NULL,
  batch_rows = getOption("odbc.batch_rows", NA),
  ...
)

## S4 method for signature 'OdbcConnection'
dbAppendTable(
  conn,
  name,
  value,
  batch_rows = getOption("odbc.batch_rows", NA),
  ...,
  row.names = NULL
)

## S4 method for signature 'OdbcConnection'
sqlCreateTable(
  con,
  table,
  fields,
  row.names = NA,
  temporary = FALSE,
  ...,
  field.types = NULL
)

Arguments

Examples

## Not run: 
library(DBI)
con <- dbConnect(odbc::odbc())
dbListTables(con)
dbWriteTable(con, "mtcars", mtcars, temporary = TRUE)
dbReadTable(con, "mtcars")

dbListTables(con)
dbExistsTable(con, "mtcars")

# A zero row data frame just creates a table definition.
dbWriteTable(con, "mtcars2", mtcars[0, ], temporary = TRUE)
dbReadTable(con, "mtcars2")

dbDisconnect(con)

## End(Not run)

Description

dbListTables() provides names of remote tables accessible through this connection; dbListFields() provides names of columns within a table.

Usage

## S4 method for signature 'OdbcConnection'
dbListTables(
  conn,
  catalog_name = NULL,
  schema_name = NULL,
  table_name = NULL,
  table_type = NULL,
  ...
)

Arguments

Value

A character vector of table or field names respectively.

Description

Currently implemented only for select back-ends where we have a use for it (SQL Server, for example). Generic, in case we develop a broader use case.

Usage

isTempTable(conn, name, ...)

## S4 method for signature 'OdbcConnection,Id'
isTempTable(conn, name, ...)

## S4 method for signature 'OdbcConnection,SQL'
isTempTable(conn, name, ...)

Arguments

Description

The dbConnect() method documented here is invoked when DBI::dbConnect() is called with the first argument odbc(). Connecting to a database via an ODBC driver is likely the first step in analyzing data using the odbc package; for an overview of package concepts, see the Overview section below.

Usage

odbc()

## S4 method for signature 'OdbcDriver'
dbConnect(
  drv,
  dsn = NULL,
  ...,
  timezone = "UTC",
  timezone_out = "UTC",
  encoding = "",
  name_encoding = "",
  bigint = c("integer64", "integer", "numeric", "character"),
  timeout = 10,
  driver = NULL,
  server = NULL,
  database = NULL,
  uid = NULL,
  pwd = NULL,
  dbms.name = NULL,
  attributes = NULL,
  interruptible = getOption("odbc.interruptible", interactive()),
  .connection_string = NULL
)

Arguments

Connection strings

Internally, dbConnect() creates a connection string using the supplied arguments. Connection string keywords are driver-dependent; the arguments documented here are common, but some drivers may not accept them.

Alternatively to configuring DSNs and driver names with the driver manager, you can pass a complete connection string directly as the .connection_string argument. The Connection Strings Reference is a useful resource that has example connection strings for a large variety of databases.

Overview

The odbc package is one piece of the R interface to databases with support for ODBC:

The package supports any Database Management System (DBMS) with ODBC support. Support for a given DBMS is provided by an ODBC driver, which defines how to interact with that DBMS using the standardized syntax of ODBC and SQL. Drivers can be downloaded from the DBMS vendor or, if you're a Posit customer, using the professional drivers. To manage information about each driver and the data sources they provide access to, our computers use a driver manager. Windows is bundled with a driver manager, while MacOS and Linux require installation of one; this package supports the unixODBC driver manager.

In the R interface, the DBI package provides a front-end while odbc implements a back-end to communicate with the driver manager. The odbc package is built on top of the nanodbc C++ library.

Interfacing with DBMSs using R and odbc involves three high-level steps:

1. Configure drivers and data sources: the functions odbcListDrivers(), odbcListDataSources(), and odbcListConfig() help to interface with the driver manager.

2. Connect to a database: The dbConnect() function, called with the first argument odbc(), connects to a database using the specified ODBC driver to create a connection object.

3. Interface with connections: The resulting connection object can be passed to various functions to retrieve information on database structure (dbListTables()), iteratively develop queries (dbSendQuery(), dbColumnInfo()), and query data objects (dbFetch()).

Learn more

To learn more about databases:

• "Best Practices in Working with Databases" documents how to use the odbc package with various popular databases.

• The pyodbc "Drivers and Driver Managers" Wiki provides further context on drivers and driver managers.

• Microsoft's "Introduction to ODBC" is a thorough resource on the ODBC interface.

Description

odbcConnectionColumns()

If the catalog, or the schema arguments are NULL, attempt to infer by querying for CURRENT_DATABASE() and CURRENT_SCHEMA(). We do this to aid with performance, as the SQLColumns method is more performant when restricted to a particular DB/schema.

Usage

## S4 method for signature 'Snowflake,character'
dbExistsTableForWrite(conn, name, ..., catalog_name = NULL, schema_name = NULL)

Arguments

Description

This is used when creating a new table with dbWriteTable(). Databases with default methods defined are:

• MySQL

• PostgreSQL

• SQL Server

• Oracle

• SQLite

• Spark

• Hive

• Impala

• Redshift

• Vertica

• BigQuery

• Teradata

• Access

• Snowflake

Usage

odbcDataType(con, obj, ...)

Arguments

Details

If you are using a different database and dbWriteTable() fails with a SQL parsing error the default method is not appropriate, you will need to write a new method. The object type for your method will be the database name retrieved by dbGetInfo(con)$dbms.name. Use the documentation provided with your database to determine appropriate values for each R data type.

Value

Corresponding SQL type for the obj.

Description

Lists the names and types of each column (field) of a specified object.

Usage

odbcListColumns(connection, ...)

Arguments

Details

The object to inspect must be specified as one of the arguments (e.g. table = "employees"); depending on the driver and underlying data store, additional specification arguments may be required.

Value

A data frame with name and type columns, listing the object's fields.

Description

On MacOS and Linux, odbc uses the unixODBC driver manager to manage information about driver and data sources. This helper returns the filepaths where the driver manager will look for that information.

odbcListConfig() is a wrapper around the command line call odbcinst -j. The odbcEditDrivers(), odbcEditSystemDSN(), and odbcEditUserDSN() helpers provide a shorthand for file.edit(odbcListConfig()[[i]]).

Windows does not use .ini configuration files; on Windows, odbcListConfig() will return a 0-length vector and ⁠odbcEdit*()⁠ will raise an error.

Usage

odbcListConfig()

odbcEditDrivers()

odbcEditSystemDSN()

odbcEditUserDSN()

See Also

The odbcListDrivers() and odbcListDataSources() helpers return information on the contents of odbcinst.ini and odbc.ini files, respectively. odbcListDataSources() collates entries from both the System and User odbc.ini files.

Learn more about unixODBC and the odbcinst utility here.

Examples

configs <- odbcListConfig()

configs

# shorthand for file.edit(configs[[1]])
odbcEditDrivers()

Description

Collect information about the available data source names (DSNs). A DSN must be both installed and configured with the driver manager to be included in this list. Configuring a DSN just sets up a lookup table (e.g. in odbc.ini) to allow users to pass only the DSN to dbConnect().

DSNs that are not configured with the driver manager can still be connected to with dbConnect() by providing DSN metadata directly.

Usage

odbcListDataSources()

Value

A data frame with two columns:

name

Name of the data source. The entries in this column can be passed to the dsn argument of dbConnect().

description

Data source description.

Configuration

This function interfaces with the driver manager to collect information about the available data source names.

For MacOS and Linux, the odbc package supports the unixODBC driver manager. unixODBC looks to the odbc.ini configuration file for information on DSNs. Find the location(s) of your odbc.ini file(s) with odbcinst -j.

In this example odbc.ini file:

[MySQL]
Driver = MySQL Driver
Database = test
Server = 127.0.0.1
User = root
password = root
Port = 3306

...the data source name is MySQL, which will appear in the name column of this function's output. To pass the DSN as the dsn argument to dbConnect(), pass it as a string, like "MySQL". ⁠Driver = MySQL Driver⁠ references the driver name in odbcListDrivers() output.

Windows is bundled with an ODBC driver manager.

When a DSN is configured with a driver manager, information on the DSN will be automatically passed on to dbConnect() when its dsn argument is set.

For example, with the MySQL data source name configured, and the driver name ⁠MySQL Driver⁠ appearing in odbcListDrivers() output, the code:

con <-
  dbConnect(
    odbc::odbc(),
    Driver = "MySQL Driver",
    Database = "test",
    Server = "127.0.0.1",
    User = "root",
    password = "root",
    Port = 3306
  )

...can be written:

con <- dbConnect(odbc::odbc(), dsn = "MySQL")

In this case, dbConnect() will look up the information defined for MySQL in the driver manager (in our example, odbc.ini) and automatically pass the needed arguments.

See Also

odbcListDrivers()

Description

Collect information about the configured driver names. A driver must be both installed and configured with the driver manager to be included in this list. Configuring a driver name just sets up a lookup table (e.g. in odbcinst.ini) to allow users to pass only the driver name to dbConnect().

Driver names that are not configured with the driver manager (and thus do not appear in this function's output) can still be used in dbConnect() by providing a path to a driver directly.

Usage

odbcListDrivers(
  keep = getOption("odbc.drivers_keep"),
  filter = getOption("odbc.drivers_filter")
)

Arguments

Value

A data frame with three columns.

name

Name of the driver. The entries in this column can be passed to the driver argument of dbConnect() (as long as the driver accepts the argument).

attribute

Driver attribute name.

value

Driver attribute value.

If a driver has multiple attributes, there will be one row per attribute, each with the same driver name. If a given driver name does not have any attributes, the function will return one row with the driver name, but the last two columns will be NA.

Configuration

This function interfaces with the driver manager to collect information about the available driver names.

For MacOS and Linux, the odbc package supports the unixODBC driver manager. unixODBC looks to the odbcinst.ini configuration file for information on driver names. Find the location(s) of your odbcinst.ini file(s) with odbcinst -j.

In this example odbcinst.ini file:

[MySQL Driver]
Driver=/opt/homebrew/Cellar/mysql/8.2.0_1/lib/libmysqlclient.dylib

Then the driver name is ⁠MySQL Driver⁠, which will appear in the name column of this function's output. To pass the driver name as the driver argument to dbConnect(), pass it as a string, like "MySQL Driver".

Windows is bundled with an ODBC driver manager.

In this example, function output would include 1 row: the name column would read "MySQL Driver", attribute would be "Driver", and value would give the file path to the driver. Additional key-value pairs under the driver name would add additional rows with the same name entry.

When a driver is configured with a driver manager, information on the driver will be automatically passed on to dbConnect() when its driver argument is set. For an example, see the same section in the odbcListDataSources() help-file. Instead of configuring driver information with a driver manager, it is also possible to provide a path to a driver directly to dbConnect().

See Also

odbcListDataSources()

Examples

odbcListDrivers()

Description

Lists all of the objects in the connection, or all the objects which have specific attributes.

Usage

odbcListObjects(connection, ...)

Arguments

Details

When used without parameters, this function returns all of the objects known by the connection. Any parameters passed will filter the list to only objects which have the given attributes; for instance, passing schema = "foo" will return only objects matching the schema foo.

Value

A data frame with name and type columns, listing the objects.

Description

Lists the object types and metadata known by the connection, and how those object types relate to each other.

Usage

odbcListObjectTypes(connection)

Arguments

Details

The returned hierarchy takes the form of a nested list, in which each object type supported by the connection is a named list with the following attributes:

contains

A list of other object types contained by the object, or "data" if the object contains data

icon

An optional path to an icon representing the type

For instance, a connection in which the top-level object is a schema that contains tables and views, the function will return a list like the following:

list(schema = list(contains = list(
                   list(name = "table", contains = "data")
                   list(name = "view", contains = "data"))))

Value

The hierarchy of object types supported by the connection.

Description

Return the data inside an object as a data frame.

Usage

odbcPreviewObject(connection, rowLimit, ...)

Arguments

Details

The object to previewed must be specified as one of the arguments (e.g. table = "employees"); depending on the driver and underlying data store, additional specification arguments may be required.

Value

A data frame containing the data in the object.

Description

Set the Transaction Isolation Level for a Connection

Usage

odbcSetTransactionIsolationLevel(conn, levels)

Arguments

See Also

https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/setting-the-transaction-isolation-level

Examples

## Not run: 
# Can use spaces or underscores in between words.
odbcSetTransactionIsolationLevel(con, "read uncommitted")

# Can also use the full constant name.
odbcSetTransactionIsolationLevel(con, "SQL_TXN_READ_UNCOMMITTED")

## End(Not run)

Description

When connecting to a database using odbc, all the arguments are concatenated into a single connection string that looks like ⁠name1=value1;name2=value2⁠. That means if your value contains = or ⁠;⁠ then it needs to be quoted. Other rules mean that you need to quote any values that starts or ends with white space, or contains ⁠{⁠ or ⁠}⁠.

This function quotes a string in a way that should work for most drivers, but unfortunately there doesn't seem to be an approach that works everywhere. If this function doesn't work for you, you'll need to carefully read the docs for your driver.

Usage

quote_value(x)

Arguments

Value

A quoted string, wrapped in I().

Examples

quote_value("abc")
quote_value("ab'c")

# Real usage is more likely to look like:
## Not run: 
library(DBI)

con <- dbConnect(
  odbc::odbc(),
  dsn = "reallycooldatabase"
  password = odbc::quote_value(Sys.getenv("MY_PASSWORD"))
)

## End(Not run)

Description

Connect to a Snowflake account via the Snowflake ODBC driver.

In particular, the custom dbConnect() method for the Snowflake ODBC driver detects ambient OAuth credentials on platforms like Snowpark Container Services or Posit Workbench. In addition, on macOS platforms, the dbConnect method will check and warn if it detects irregularities with how the driver is configured, unless the odbc.no_config_override environment variable is set.

Usage

snowflake()

## S4 method for signature 'Snowflake'
dbConnect(
  drv,
  account = Sys.getenv("SNOWFLAKE_ACCOUNT"),
  driver = NULL,
  warehouse = NULL,
  database = NULL,
  schema = NULL,
  uid = NULL,
  pwd = NULL,
  session = NULL,
  ...
)

Arguments

Value

An OdbcConnection object with an active connection to a Snowflake account.

Examples

## Not run: 
# Use ambient credentials.
DBI::dbConnect(odbc::snowflake())

# Use browser-based SSO (if configured). Only works on desktop.
DBI::dbConnect(
  odbc::snowflake(),
  account = "testorg-test_account",
  authenticator = "externalbrowser"
)

# Use a traditional username & password.
DBI::dbConnect(
  odbc::snowflake(),
  account = "testorg-test_account",
  uid = "me",
  pwd = rstudioapi::askForPassword()
)

# Use credentials from the viewer (when possible) in a Shiny app
# deployed to Posit Connect.
server <- function(input, output, session) {
  conn <- DBI::dbConnect(odbc::snowflake(), session = session)
}

## End(Not run)