The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.
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.
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".
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.
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"
)
)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.
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.
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.
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.
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.
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:
Trino’s BIGINT spans values an R double cannot hold
exactly. By default RTrino returns such columns as
bit64::integer64, which is exact:
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.
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.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.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.
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.
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:
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.
These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.