'DBI' Backend for 'Trino'

A 'DBI' compliant backend for 'Trino' < https://trino.io>, the distributed SQL query engine (successor of 'PrestoSQL'). Connects to a 'Trino' cluster over its HTTP REST API, executes queries with automatic pagination, maps 'Trino' types to R types, and integrates with 'dplyr' through 'dbplyr' SQL dialects. Supports basic, JSON Web Token (JWT) bearer and OAuth2 client credentials authentication, as well as custom certificate authorities.

RTrino

A DBI backend for Trino, the distributed SQL query engine (the successor of PrestoSQL).

RTrino talks to a Trino coordinator over its HTTP REST API, so there is no client library to install and nothing to compile. It is built on httr2 and on the sql_dialect() extension point introduced in dbplyr 2.6.0.

Installation

# install.packages("pak")
pak::pak("AngelCasasbl/RTrino")

Usage

library(DBI)
library(RTrino)

con <- dbConnect(
  Trino(),
  host    = "https://trino.example.com",
  port    = 443,
  user    = Sys.getenv("TRINO_USER"),
  catalog = "hive",
  schema  = "analytics",
  auth    = trino_auth_basic(Sys.getenv("TRINO_USER"), Sys.getenv("TRINO_PASSWORD"))
)

dbGetQuery(con, "SELECT region, sum(amount) AS total FROM sales GROUP BY region")

library(dplyr)
tbl(con, "sales") |>
  filter(year == 2026) |>
  group_by(region) |>
  summarise(total = sum(amount, na.rm = TRUE)) |>
  collect()

dbDisconnect(con)

See vignette("getting-started", package = "RTrino") for authentication, certificate authorities, chunked fetching and the dplyr translations.

What it does

• Connects over HTTP or HTTPS and probes /v1/info, so a bad address or a rejected credential fails at dbConnect() rather than at your first query.
• Follows Trino's nextUri pagination, and handles every state the protocol can report (QUEUED, PLANNING, RUNNING, FINISHED, FAILED, CANCELED).
• Maps Trino types to R types without losing precision: BIGINT comes back as exact bit64::integer64 by default, DOUBLE bit for bit, and timestamps to the microsecond.
• Reports the rows a statement changed from dbExecute(), and accepts table names as strings, DBI::Id() or quoted identifiers.
• Writes data frames with dbWriteTable(), dbAppendTable() and dbCreateTable(), and renames or drops tables with dbRenameTable() and dbRemoveTable(). dbWriteTable(overwrite = TRUE) renames the existing table instead of dropping it, and puts it back if the write fails.
• Wraps statements in Trino transactions with dbBegin(), dbCommit(), dbRollback() and dbWithTransaction().
• Cancels a query on the server when its result is cleared while still running.
• Bounds each HTTP request with a timeout, and a query's run time on the cluster with query_max_run_time.
• Authenticates with basic credentials, a bearer JWT or OAuth2 client credentials — each held in a closure, never in a global variable — and refuses to send them over plain HTTP.
• Trusts an internal certificate authority without turning verification off.
• Translates dplyr pipelines to Trino SQL when dbplyr (>= 2.6.0) is installed, so that an expression computes in Trino what it computes in R.

Limits

• Parameter binding: put values into a statement with DBI::sqlInterpolate(), which quotes them as typed Trino literals. Passing params is an error.
• Writing is done with INSERT INTO ... VALUES, one statement per chunk_size rows (1000 by default), with every value written into the SQL as a literal. It suits tables of thousands of rows, not bulk loads: for those, write files in your data lake and let Trino read them.
• Trino has no temporary tables, so temporary = TRUE is an error, and dplyr::copy_to() and compute() need temporary = FALSE and a table name.
• Whether dbRollback() undoes a write depends on the connector behind the catalog, not on RTrino. Iceberg and Delta Lake tables support it; the memory, tpch and other catalogs that hold no real storage do not, and dbBegin() warns when the connection's catalog is one of them.

The vignette lists the few places where a dplyr translation cannot match R, such as round() on halves and the approximate median().

Authentication

Development

R 4.1.0 or newer. The test suite runs offline against a fake Trino coordinator built with webfakes, so no cluster is needed. The fake replays responses recorded from a real Trino (tests/testthat/fixtures, captured with dev/record-fixtures.R), so the tests read the payloads a cluster really sends:

devtools::test()
devtools::check()

With a Trino at hand, the integration tests in tests/testthat/test-live.R also run: they compare every value with Trino's own rendering of it, and run the SQL of the dplyr translations against what R computes on the same rows.

# docker run --rm -d -p 8080:8080 --name trino trinodb/trino
Sys.setenv(RTRINO_TEST_URL = "http://localhost:8080")
devtools::test()

License

BSD 3-Clause. See LICENSE.md.