Skip to contents

Getting started with RTrino

Source: vignettes/getting-started.Rmd
getting-started.Rmd

RTrino is a DBI backend for Trino, the distributed SQL query engine. It talks to a Trino coordinator over the cluster’s HTTP REST API, so there is no driver to install and nothing to compile.

The code in this vignette is not evaluated: every example needs a reachable cluster.

Connecting

Trino’s REST API is stateless, so a connection is really a bundle of settings: the coordinator’s address, who you are, and which catalog and schema unqualified table names resolve against. catalog and schema are required.

library(DBI)
library(RTrino)

con <- dbConnect(
  Trino(),
  host = "http://localhost",
  port = 8080,
  catalog = "hive",
  schema = "default"
)

dbConnect() calls /v1/info before returning, so a wrong address or a rejected credential fails immediately rather than on your first query. The port can also be written into the host, as in host = "localhost:8080".

Authentication

Each authentication method is a closure that attaches credentials to every request. The credentials live inside that closure, never in the global environment, and never in a slot you might print by accident.

All three need an https:// host. Over plain HTTP the credentials would travel unencrypted, so dbConnect() refuses to send them there, before anything reaches the network. Only if the connection is encrypted by other means, such as an SSH tunnel to the coordinator, set allow_http_auth = TRUE.

Basic — LDAP or a password file 

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")
  )
)

JWT — service accounts and pipelines 

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

OAuth2 client credentials — corporate SSO

httr2 caches the token and renews it when it expires, so a long-running session does not need to think about the token’s lifetime.

con <- dbConnect(
  Trino(),
  host = "https://trino.example.com",
  port = 443,
  user = Sys.getenv("TRINO_USER"),
  catalog = "hive",
  schema = "analytics",
  auth = trino_auth_oauth2(
    client_id     = Sys.getenv("OAUTH_CLIENT_ID"),
    client_secret = Sys.getenv("OAUTH_CLIENT_SECRET"),
    token_url     = "https://auth.example.com/oauth/token"
  )
)

Internal certificate authorities

A cluster whose certificate is signed by your own CA does not need certificate checking turned off. Point trino_ssl() at the CA’s PEM file and verification keeps working:

con <- dbConnect(
  Trino(),
  host = "https://trino.internal",
  port = 443,
  catalog = "hive",
  schema = "analytics",
  auth = trino_auth_basic(
    Sys.getenv("TRINO_USER"),
    Sys.getenv("TRINO_PASSWORD")
  ),
  ssl_options = trino_ssl(ca_bundle = "/etc/ssl/certs/internal-ca.pem")
)

trino_ssl(verify = FALSE) exists for a development cluster with a throwaway certificate. It accepts any certificate, including one presented by an attacker, so dbConnect() warns when it opens a connection with it.

Querying 

sales <- dbGetQuery(con, "SELECT * FROM sales LIMIT 100")

For a result too large to hold in memory, take it in chunks. dbFetch() follows Trino’s pagination for you and buffers whatever a page delivered beyond what you asked for:

res <- dbSendQuery(con, "SELECT * FROM sales")
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = 10000)
  # summarise, write out, accumulate...
}
dbClearResult(res)

Clearing a result that is still running sends a DELETE to the coordinator, so abandoning a query also stops the cluster working on it. So does interrupting R while it waits for a query.

dbColumnInfo() and dbFetch(res, n = 0) describe the result’s columns, with their types, before any row has been fetched.

Statements

dbExecute() runs a statement to completion and returns how many rows Trino says it changed: the count of an INSERT, UPDATE, DELETE, MERGE or CREATE TABLE ... AS SELECT, and 0 for a statement that changes no rows, such as CREATE TABLE or DROP TABLE.

dbExecute(con, "INSERT INTO memory.default.visits VALUES (1, 'a'), (2, 'b')")
#> [1] 2

RTrino has no parameter binding, and passing params is an error rather than being ignored. Put values into a statement with sqlInterpolate() or glue::glue_sql(): both quote them through dbQuoteLiteral(), which writes Trino literals of the right type, so a date is compared as a date and a string cannot break out of its quotes.

sql <- sqlInterpolate(
  con,
  "SELECT * FROM orders WHERE orderdate >= ?since AND orderstatus = ?status",
  since = as.Date("1995-01-01"),
  status = "F"
)
sql
#> <SQL> SELECT * FROM orders
#>       WHERE orderdate >= DATE '1995-01-01' AND orderstatus = 'F'
dbGetQuery(con, sql)

A POSIXct is written as a TIMESTAMP in the connection’s session.timezone, to the microsecond, which is how Trino reads it back.

Writing data

dbWriteTable(), dbAppendTable() and dbCreateTable() write data frames. Trino’s protocol has no parameter binding, so rows go in as INSERT INTO ... VALUES statements with every value written as a literal, chunk_size rows at a time (1000 by default). That suits tables of thousands of rows; for bulk loads, create the table with SQL, for example CREATE TABLE ... AS SELECT ... through dbExecute().

dbWriteTable(con, "sales", data.frame(id = 1:3, label = letters[1:3]))
dbAppendTable(con, "sales", data.frame(id = 4L, label = "d"))

# Replace the table. The old one is renamed first and restored if the write
# fails, so a failure does not leave you without it.
dbWriteTable(con, "sales", data.frame(id = 9L, label = "z"), overwrite = TRUE)

Trino has no temporary tables, so temporary = TRUE is an error, and dplyr::copy_to() and compute() need temporary = FALSE and a table name.

Transactions

dbBegin() starts a Trino transaction that every later statement runs in, until dbCommit() or dbRollback() ends it. dbWithTransaction() does both for you, rolling back if the code fails:

dbWithTransaction(con, {
  dbExecute(con, "INSERT INTO sales VALUES (5, 'e')")
  dbExecute(con, "INSERT INTO sales VALUES (6, 'f')")
})

Whether a rollback undoes a write is up to the connector behind the catalog, not RTrino: Iceberg and Delta Lake tables support it, memory and tpch do not. dbBegin() warns when the connection’s catalog is one of those.

Timeouts

A query is a series of HTTP requests, and two limits apply:

  • timeout (60 seconds by default) bounds each request, so a coordinator or a network that stops answering fails with an error instead of blocking R.
  • query_max_run_time bounds the whole query on the cluster. Past it, Trino stops the query and RTrino raises a trino_query_error with error_name = "EXCEEDED_TIME_LIMIT".

In a Shiny app, where a blocked R session blocks its user, set both:

con <- dbConnect(
  Trino(),
  host = "https://trino.example.com",
  port = 443,
  catalog = "hive",
  schema = "analytics",
  auth = trino_auth_jwt(Sys.getenv("TRINO_TOKEN")),
  timeout = 30,
  query_max_run_time = "5m"
)

Big integers and precision

Trino’s BIGINT spans values an R double cannot hold exactly. By default RTrino returns such columns as bit64::integer64, which is exact:

dbGetQuery(con, "SELECT 9007199254740993 AS id")$id
#> integer64
#> [1] 9007199254740993

The one exception is the lowest BIGINT, -9223372036854775808, which bit64 keeps for its own NA: it comes back as NA, with a warning. Choose a different representation at connection time with bigint = "numeric" (a double, exact only up to 2^53) or bigint = "character" (the exact digits, always).

DOUBLE values arrive bit for bit, NaN and the infinities included. DECIMAL is returned as a double, so a decimal with more than 15 significant digits is rounded; cast it to VARCHAR in the query to keep every digit. TIMESTAMP and TIME keep their full precision, down to the microsecond.

dplyr

With dbplyr 2.6.0 or newer installed, a connection carries a Trino SQL dialect and dplyr verbs are translated to SQL Trino accepts:

library(dplyr)

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

Trino-specific translations are applied so that an expression computes in Trino what it computes in R:

  • / divides as a double, where Trino would truncate the quotient of two integers, and %% takes the sign of the divisor, as in R.
  • as.integer() and as.integer64() truncate towards zero, where Trino’s cast would round.
  • paste(), paste0() and str_c() accept columns of any type.
  • grepl(), sub() and gsub() become REGEXP_LIKE/REGEXP_REPLACE, honour fixed = TRUE and ignore.case = TRUE, and accept R’s \\1 back-references; sub() replaces the first match only.
  • A Date or POSIXct from R, as in filter(orderdate >= !!since), becomes a typed Trino literal.

Where there is no faithful translation, a few differences remain, and it is worth knowing them before moving a computation from R to the cluster:

  • round() rounds halves away from zero, where R rounds them to the even digit: round(2.5) is 3 in Trino and 2 in R.
  • median() and quantile() become APPROX_PERCENTILE, the percentile Trino offers over an arbitrary number of rows. It is approximate: on skewed data it can be off by a few per cent. Collect the rows and compute in R when the exact value matters.
  • paste() skips NA, where R writes "NA", and renders a DOUBLE column the way Trino does, so a value of 1.5 becomes "1.5E0"; round or cast it to a DECIMAL first when that matters.
  • Regular expressions are Trino’s (Java’s syntax), not R’s; the common constructs mean the same in both.
  • date + 1 fails, because Trino does not add a number to a date; write date + days(1). days(), weeks(), months(), years(), hours(), minutes() and seconds() become intervals.

compute() needs temporary = FALSE and a name, because Trino has no temporary tables.

filter_out(), added in dplyr 1.2.0, is translated with Trino’s native IS DISTINCT FROM instead of the portable CASE WHEN comparison dbplyr falls back to, so the rows where the condition is NA are kept and the SQL stays readable.

Two groups of dplyr 1.2.0 functions have no SQL translation in dbplyr 2.6.0 and therefore none here either: when_any()/when_all(), which pass through untranslated and reach the cluster as an unknown function, and recode_values()/replace_values()/replace_when(), which fail with “Cannot translate a <formula> object to SQL”. Use | and & in place of when_any() and when_all(), and case_when() or if_else() in place of the recoding family, until dbplyr covers them.

Inspect the generated SQL before running it with dplyr::show_query(), and the cluster’s plan with dplyr::explain(). simulate_trino() gives a connection that dispatches the Trino dialect without a cluster, for checking a translation offline.

Introspection 

dbListTables(con)
dbListFields(con, "sales")
dbExistsTable(con, "sales")
dbGetInfo(con)$db.version

A table can be named as "table", "schema.table" or "catalog.schema.table", with Id(catalog = , schema = , table = ), or with an identifier that is already quoted. Trino folds every identifier to lower case, so dbExistsTable(con, "SALES") finds sales, as SELECT * FROM SALES would.

Session properties and other headers

Anything the protocol expects in a header can be added to every request through extra.headers — Trino session properties, for instance. query_max_run_time joins whatever properties are set here:

con <- dbConnect(
  Trino(),
  host = "http://localhost",
  port = 8080,
  catalog = "hive",
  schema = "default",
  extra.headers = list("X-Trino-Session" = "query_max_run_time=2h")
)

Disconnecting

There is no server-side session to close, so dbDisconnect() only invalidates the object; any later use of it is an error rather than a silent reconnect.