Package {RTrino}

RTrino: 'DBI' Backend for 'Trino'

Description

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.

Author(s)

Maintainer: Luis A Casas Ballestas luisangelcasasbl@gmail.com [copyright holder]

Authors:

• Luis A Casas Ballestas luisangelcasasbl@gmail.com [copyright holder]

See Also

Useful links:

• https://github.com/AngelCasasbl/RTrino

• Report bugs at https://github.com/AngelCasasbl/RTrino/issues

Instantiate the Trino driver

Description

Creates the driver object passed as the first argument to DBI::dbConnect(). The call has no side effects and always succeeds.

Usage

Trino()

Value

A TrinoDriver object.

Examples

drv <- Trino()
drv

Trino connection class

Description

An S4 class representing a connection to a Trino cluster. Trino's REST API is stateless, so the object only holds the information needed to build requests; no socket is kept open.

Usage

## S4 method for signature 'TrinoConnection'
show(object)

Arguments

Slots

host

Base URL of the coordinator, including the scheme.

port

Coordinator port.

user

Trino user name.

catalog

Trino catalog.

schema

Trino schema.

session.timezone

Session time zone used for timestamp columns.

bigint

How BIGINT columns are returned; see dbConnect().

extra.headers

Named list of additional HTTP headers.

source

Value of the X-Trino-Source header.

auth

Authentication closure, or NULL for no authentication.

ssl_options

SSL options as returned by trino_ssl().

timeout

Seconds allowed for each HTTP request, or Inf.

valid

Environment holding the connection's validity flag.

transaction

Environment holding the active transaction id, or NULL when there is none.

Trino driver class

Description

An S4 class representing the Trino driver. It carries no state and exists only as the entry point for DBI::dbConnect().

Usage

## S4 method for signature 'TrinoDriver'
show(object)

## S4 method for signature 'TrinoDriver'
dbIsValid(dbObj, ...)

## S4 method for signature 'TrinoDriver'
dbGetInfo(dbObj, ...)

## S4 method for signature 'TrinoDriver'
dbUnloadDriver(drv, ...)

Arguments

Trino result class

Description

An S4 class representing the result of a statement submitted to Trino.

Usage

## S4 method for signature 'TrinoResult'
show(object)

## S4 method for signature 'TrinoResult'
dbGetStatement(res, ...)

## S4 method for signature 'TrinoResult'
dbGetRowCount(res, ...)

## S4 method for signature 'TrinoResult'
dbGetRowsAffected(res, ...)

## S4 method for signature 'TrinoResult'
dbColumnInfo(res, ...)

Arguments

Details

Trino returns results page by page; each payload carries a nextUri to follow until the query reaches a terminal state. Because that progress is mutated as rows are fetched, and S4 slots are copy-on-modify, the mutable part of the result lives in the state environment rather than in slots. This makes res behave like a cursor, as DBI expects.

Slots

connection

The TrinoConnection the statement was sent on.

statement

The SQL statement, as a string.

state

Environment holding the mutable cursor state: next_uri, columns, data, completed, rows_fetched, query_id, and the update_type and update_count of a statement that changes something.

Insert rows into an existing table

Description

Builds one ⁠INSERT INTO ... VALUES (...)⁠ statement per batch of rows, with values quoted as literals by DBI::sqlAppendTable() (which calls dbQuoteIdentifier() and dbQuoteLiteral() for Trino) — there is no parameter binding to fall back on.

Usage

## S4 method for signature 'TrinoConnection'
dbAppendTable(conn, name, value, ..., row.names = NULL)

Arguments

Value

The number of rows inserted.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "memory", schema = "default")
DBI::dbCreateTable(con, "sales", c(id = "integer", label = "varchar"))
DBI::dbAppendTable(con, "sales", data.frame(id = 1:3, label = letters[1:3]))
DBI::dbGetQuery(con, "SELECT * FROM sales ORDER BY id")
DBI::dbRemoveTable(con, "sales")
DBI::dbDisconnect(con)

Start, commit and roll back a transaction

Description

Every statement runs inside Trino's engine-level transaction mechanism: dbBegin() sends ⁠START TRANSACTION⁠ and keeps the transaction id Trino answers with, which is then carried on every later request until dbCommit() or dbRollback() sends COMMIT or ROLLBACK and clears it.

Usage

## S4 method for signature 'TrinoConnection'
dbBegin(conn, ...)

## S4 method for signature 'TrinoConnection'
dbCommit(conn, ...)

## S4 method for signature 'TrinoConnection'
dbRollback(conn, ...)

Arguments

Details

Whether a ROLLBACK actually undoes a write depends on the connector behind the active catalog, not on RTrino: Iceberg and Delta Lake tables support it, most others (including Hive, strictly) only partially, and Trino's own synthetic catalogs (memory, tpch, ...) not at all. dbBegin() warns when the active catalog is one of the latter.

Value

TRUE, invisibly.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")

# tpch holds no real storage, so dbBegin() warns that a ROLLBACK would
# undo nothing. On an Iceberg or Delta Lake catalog it would not.
DBI::dbBegin(con)
DBI::dbGetQuery(con, "SELECT count(*) AS n FROM nation")
DBI::dbCommit(con)

DBI::dbDisconnect(con)

Parameters are not supported

Description

Trino's client protocol has no parameter binding of its own, so rather than ignoring the values, dbBind() fails.

Usage

## S4 method for signature 'TrinoResult'
dbBind(res, params, ...)

Arguments

Value

Never returns.

Release a Trino result

Description

Frees the buffered rows and marks the cursor cleared. If the query is still running on the server, a DELETE is sent to its nextUri so the coordinator stops working on it instead of letting it run to completion for nobody.

Usage

## S4 method for signature 'TrinoResult'
dbClearResult(res, ...)

Arguments

Value

TRUE, invisibly.

Connect to a Trino cluster

Description

Creates a TrinoConnection. Trino's REST API is stateless, so no session is opened on the server; the connection object holds the coordinator address and the per-request settings. To fail early on a wrong address or bad credentials, dbConnect() probes GET /v1/info before returning.

Usage

## S4 method for signature 'TrinoDriver'
dbConnect(
  drv,
  host = "http://localhost",
  port = 8080L,
  user = trino_default_user(),
  catalog = NULL,
  schema = NULL,
  source = "RTrino",
  session.timezone = "UTC",
  bigint = c("integer64", "numeric", "character"),
  auth = NULL,
  ssl_options = trino_ssl(),
  extra.headers = list(),
  timeout = 60,
  query_max_run_time = NULL,
  allow_http_auth = FALSE,
  ...
)

Arguments

Value

A TrinoConnection object.

Timeouts

A query is a series of HTTP requests, so two limits apply. timeout bounds each request: a coordinator or network that stops answering fails with an error instead of blocking the R session. query_max_run_time bounds the query as a whole on the server: past it, Trino stops the query and RTrino raises a trino_query_error whose error_name is "EXCEEDED_TIME_LIMIT". In a Shiny app, where a blocked session blocks its user, set both.

Examples

# The examples in this package run against the coordinator that
# RTRINO_TEST_URL points at, such as "http://localhost:8080" for
# `docker run -p 8080:8080 trinodb/trino`. It has no authentication.
con <- DBI::dbConnect(
  RTrino::Trino(),
  host = Sys.getenv("RTRINO_TEST_URL"),
  catalog = "tpch", schema = "tiny"
)
con
DBI::dbDisconnect(con)

# LDAP over TLS, with limits for use in a Shiny app
## Not run: 
con <- DBI::dbConnect(
  RTrino::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")),
  timeout = 30,
  query_max_run_time = "5m"
)
DBI::dbDisconnect(con)

## End(Not run)

Create a table

Description

Trino has no temporary tables, so temporary = TRUE is an error rather than being silently ignored.

Usage

## S4 method for signature 'TrinoConnection'
dbCreateTable(conn, name, fields, ..., row.names = NULL, temporary = FALSE)

Arguments

Value

TRUE, invisibly.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "memory", schema = "default")
DBI::dbCreateTable(con, "sales", c(id = "bigint", label = "varchar"))
DBI::dbListFields(con, "sales")
DBI::dbRemoveTable(con, "sales")

# The column types can also follow a data frame's
DBI::dbCreateTable(con, "sales", data.frame(id = 1L, label = "a"))
DBI::dbRemoveTable(con, "sales")

DBI::dbDisconnect(con)

Map an R type to a Trino type

Description

The inverse of trino_type_to_r(): used by dbCreateTable() to pick a column type when the caller does not give one explicitly.

Usage

## S4 method for signature 'TrinoConnection'
dbDataType(dbObj, obj, ...)

Arguments

Value

A string naming a Trino type.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
DBI::dbDataType(con, 1L)
DBI::dbDataType(con, Sys.Date())
DBI::dbDataType(con, data.frame(id = 1L, label = "a"))
DBI::dbDisconnect(con)

Disconnect from Trino

Description

Marks the connection as closed. Trino's REST API is stateless, so nothing is sent to the server; any later use of conn raises an error.

Usage

## S4 method for signature 'TrinoConnection'
dbDisconnect(conn, ...)

Arguments

Value

TRUE, invisibly.

Execute a statement that returns no rows

Description

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

Usage

## S4 method for signature 'TrinoConnection,character'
dbExecute(conn, statement, ...)

Arguments

Value

The number of affected rows, invisibly: a double, or NA for a data change whose count Trino did not report.

Does a table exist?

Description

Asks information_schema for a count, so the answer costs the same whatever the schema holds. Trino folds every identifier to lower case, so the name is matched the same way: "SALES" exists if sales does, as ⁠SELECT * FROM SALES⁠ would find it.

Usage

## S4 method for signature 'TrinoConnection,character'
dbExistsTable(conn, name, ...)

## S4 method for signature 'TrinoConnection,Id'
dbExistsTable(conn, name, ...)

## S4 method for signature 'TrinoConnection,ANY'
dbExistsTable(conn, name, ...)

Arguments

Value

A logical scalar. A name in a catalog or schema that does not exist is simply FALSE, not an error.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
DBI::dbExistsTable(con, "nation")
DBI::dbExistsTable(con, "tpch.sf1.nation")
DBI::dbExistsTable(con, DBI::Id(catalog = "tpch", schema = "sf1",
                                table = "nation"))
DBI::dbExistsTable(con, "no_such_table")
DBI::dbDisconnect(con)

Fetch rows from a Trino result

Description

Pulls rows from the cursor, following Trino's nextUri chain as needed, and converts each column to its R type according to the column's declared Trino type and the connection's bigint setting.

Usage

## S4 method for signature 'TrinoResult'
dbFetch(res, n = -1, ...)

Arguments

Value

A tibble. Once the result is exhausted, a zero-row tibble with the right columns and types.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
res <- DBI::dbSendQuery(con, "SELECT * FROM orders")
while (!DBI::dbHasCompleted(res)) {
  chunk <- DBI::dbFetch(res, n = 4000)
  # process chunk; here, just count its rows
  cat(nrow(chunk), "rows\n")
}
DBI::dbClearResult(res)
DBI::dbDisconnect(con)

Metadata about a Trino connection

Description

Reports the connection's settings plus the coordinator's version, read from ⁠/v1/info⁠. If that endpoint cannot be reached, db.version is NA rather than an error, so the rest of the metadata stays available.

Usage

## S4 method for signature 'TrinoConnection'
dbGetInfo(dbObj, ...)

Arguments

Value

A list with db.version, host, port, user, catalog and schema.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
DBI::dbGetInfo(con)
DBI::dbDisconnect(con)

Run a query and return all of its rows

Description

Submits the statement, fetches every row and clears the result, even if fetching fails.

Usage

## S4 method for signature 'TrinoConnection,character'
dbGetQuery(conn, statement, n = -1, ...)

Arguments

Value

A tibble.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
DBI::dbGetQuery(con, "SELECT * FROM nation LIMIT 5")
DBI::dbGetQuery(con, "SELECT * FROM nation", n = 3)
DBI::dbDisconnect(con)

Have all rows of a result been consumed?

Description

TRUE once Trino has stopped handing out pages and the rows already pulled have been handed to the caller. A result that has finished on the server but still holds buffered rows is not complete, so a ⁠while (!dbHasCompleted(res))⁠ loop over dbFetch() sees every row.

Usage

## S4 method for signature 'TrinoResult'
dbHasCompleted(res, ...)

Arguments

Value

A logical scalar.

Is this connection or result still usable?

Description

A connection is valid until dbDisconnect() is called. A result is valid until it is cleared by dbClearResult() - a result whose rows have all been fetched is still valid, so that dbColumnInfo() and friends keep working.

Usage

## S4 method for signature 'TrinoConnection'
dbIsValid(dbObj, ...)

## S4 method for signature 'TrinoResult'
dbIsValid(dbObj, ...)

Arguments

Value

A logical scalar.

List a table's columns

Description

List a table's columns

Usage

## S4 method for signature 'TrinoConnection,character'
dbListFields(conn, name, ...)

## S4 method for signature 'TrinoConnection,Id'
dbListFields(conn, name, ...)

## S4 method for signature 'TrinoConnection,ANY'
dbListFields(conn, name, ...)

Arguments

Value

A character vector of column names.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
DBI::dbListFields(con, "nation")
DBI::dbListFields(con, DBI::Id(schema = "sf1", table = "region"))
DBI::dbDisconnect(con)

List the tables in the connection's schema

Description

List the tables in the connection's schema

Usage

## S4 method for signature 'TrinoConnection'
dbListTables(conn, ...)

Arguments

Value

A character vector of table names.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
DBI::dbListTables(con)
DBI::dbDisconnect(con)

Quote identifiers and strings for Trino

Description

Trino follows the SQL standard: identifiers are wrapped in double quotes with embedded double quotes doubled, and string literals are wrapped in single quotes with embedded single quotes doubled. A value that is already DBI::SQL() is returned unchanged rather than quoted a second time.

Usage

## S4 method for signature 'TrinoConnection,character'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'TrinoConnection,SQL'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'TrinoConnection,character'
dbQuoteString(conn, x, ...)

## S4 method for signature 'TrinoConnection,SQL'
dbQuoteString(conn, x, ...)

Arguments

Value

A DBI::SQL object.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
DBI::dbQuoteIdentifier(con, "my column")
DBI::dbQuoteString(con, "O'Brien")
DBI::dbDisconnect(con)

Quote R values as Trino literals

Description

Turns R values into SQL literals Trino accepts where a value of the matching type is expected. Used by DBI::sqlInterpolate() and glue::glue_sql(), and so the way to put values into a statement, since RTrino has no parameter binding. Missing values become NULL.

Usage

## S4 method for signature 'TrinoConnection'
dbQuoteLiteral(conn, x, ...)

Arguments

Details

• character and factor: 'text', with embedded quotes doubled;

• logical: TRUE or FALSE;

• integer and integer64: the integer's digits;

• double: a DOUBLE literal that reads back as the same double, such as 0.30000000000000004E0, or nan(), infinity() or -infinity();

• Date: ⁠DATE '2026-01-15'⁠;

• POSIXct: ⁠TIMESTAMP '2026-01-15 10:30:00.123456'⁠, the instant as a wall-clock time in the connection's session.timezone, which is how Trino reads it back;

• a list of raw vectors: ⁠X'0102FF'⁠.

Other classes are quoted by DBI's default method.

Value

A DBI::SQL object.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
DBI::dbQuoteLiteral(con, as.Date("2026-01-15"))
DBI::sqlInterpolate(
  con, "SELECT * FROM orders WHERE orderdate >= ?since AND urgent = ?flag",
  since = as.Date("2026-01-01"), flag = TRUE
)
DBI::dbDisconnect(con)

Drop a table

Description

Drop a table

Usage

## S4 method for signature 'TrinoConnection,character'
dbRemoveTable(conn, name, ...)

## S4 method for signature 'TrinoConnection,Id'
dbRemoveTable(conn, name, ...)

## S4 method for signature 'TrinoConnection,ANY'
dbRemoveTable(conn, name, ...)

Arguments

Value

TRUE, invisibly.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "memory", schema = "default")
DBI::dbWriteTable(con, "sales", data.frame(id = 1:3))
DBI::dbRemoveTable(con, "sales")
DBI::dbExistsTable(con, "sales")
DBI::dbDisconnect(con)

Rename a table

Description

Not a DBI generic: DBI has no dbRenameTable(), so RTrino defines its own, the way RPresto does for Presto.

Usage

dbRenameTable(conn, name, new_name, ...)

## S4 method for signature 'TrinoConnection'
dbRenameTable(conn, name, new_name, ...)

Arguments

Value

TRUE, invisibly.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "memory", schema = "default")
DBI::dbWriteTable(con, "sales", data.frame(id = 1:3))
dbRenameTable(con, "sales", "sales_old")
DBI::dbExistsTable(con, "sales_old")
DBI::dbRemoveTable(con, "sales_old")
DBI::dbDisconnect(con)

Submit a statement to Trino

Description

Posts the statement to ⁠/v1/statement⁠ and returns immediately with a TrinoResult cursor; the rows are pulled by dbFetch(). A statement that fails during planning raises an error here, because Trino reports it in the response to the initial POST.

Usage

## S4 method for signature 'TrinoConnection,character'
dbSendQuery(conn, statement, ...)

Arguments

Details

Query parameters are not supported: passing params is an error rather than being silently ignored. Put values into the statement with DBI::sqlInterpolate() or glue::glue_sql(), which quote them with DBI::dbQuoteLiteral() as Trino literals.

Value

A TrinoResult object.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "tpch", schema = "tiny")
res <- DBI::dbSendQuery(con, "SELECT 1 AS n")
DBI::dbFetch(res)
DBI::dbClearResult(res)

# Values go into the SQL as literals, quoted for Trino
sql <- DBI::sqlInterpolate(
  con, "SELECT count(*) AS n FROM orders WHERE orderdate >= ?since",
  since = as.Date("1998-01-01")
)
DBI::dbGetQuery(con, sql)

DBI::dbDisconnect(con)

Submit a statement that changes something

Description

Unlike dbSendQuery(), runs the statement to completion before returning, so that dbGetRowsAffected() answers straight away, as DBI requires: Trino only reports the count once the statement has finished.

Usage

## S4 method for signature 'TrinoConnection,character'
dbSendStatement(conn, statement, ...)

Arguments

Value

A TrinoResult object whose statement has finished.

Write a table

Description

Creates the table with dbCreateTable() and fills it with dbAppendTable(). ⁠CREATE TABLE⁠ cannot be undone by dbRollback() on most connectors, so overwrite = TRUE plays it safe on its own: it renames the existing table rather than dropping it, and restores it if anything fails before the new table is fully written.

Usage

## S4 method for signature 'TrinoConnection,ANY'
dbWriteTable(
  conn,
  name,
  value,
  ...,
  overwrite = FALSE,
  append = FALSE,
  field.types = NULL,
  temporary = FALSE,
  row.names = NULL,
  chunk_size = 1000L
)

## S4 method for signature 'TrinoConnection,Id'
dbWriteTable(conn, name, value, ...)

Arguments

Value

TRUE, invisibly.

Examples

con <- DBI::dbConnect(RTrino::Trino(), host = Sys.getenv("RTRINO_TEST_URL"),
                      catalog = "memory", schema = "default")
DBI::dbWriteTable(con, "sales", data.frame(id = 1:3, label = letters[1:3]))
DBI::dbWriteTable(con, "sales", data.frame(id = 4L, label = "d"),
                  append = TRUE)
DBI::dbGetQuery(con, "SELECT * FROM sales ORDER BY id")

# Replace the table and its contents
DBI::dbWriteTable(con, "sales", data.frame(id = 9L, label = "z"),
                  overwrite = TRUE)
DBI::dbGetQuery(con, "SELECT * FROM sales")

DBI::dbRemoveTable(con, "sales")
DBI::dbDisconnect(con)

copy_to() for Trino

Description

Overrides dbplyr's default, which wraps the write in a transaction (dbBegin()). Whether that transaction could actually be rolled back depends on the catalog's connector, and ⁠CREATE TABLE⁠ itself is not something most connectors undo, so the write goes straight to dbWriteTable() instead, which already has its own way of playing it safe on overwrite = TRUE. temporary defaults to TRUE, as in the generic, which means the call fails unless the caller passes temporary = FALSE: Trino has no temporary tables.

Usage

db_copy_to.TrinoConnection(
  con,
  table,
  values,
  ...,
  overwrite = FALSE,
  types = NULL,
  temporary = TRUE,
  unique_indexes = NULL,
  indexes = NULL,
  analyze = TRUE,
  in_transaction = TRUE
)

Arguments

Value

table.

dbplyr edition used by Trino connections

Description

dbplyr refuses to work with a backend that has not declared the second edition of its interface, so the declaration is explicit even though every method here is written against the current API.

Usage

dbplyr_edition.TrinoConnection(con)

Arguments

Value

2L.

A Trino connection that never talks to a server

Description

Returns an object that carries the TrinoConnection class without holding a session, so sql_dialect.TrinoConnection() and, through it, sql_translation.sql_dialect_trino() dispatch as they would on a live connection. Useful for inspecting the SQL a dplyr pipeline produces, and used by this package's own translation tests, which then need no coordinator at all. Anything that reaches the database, such as collect(), still fails.

Usage

simulate_trino()

Value

A simulated dbplyr connection of class TrinoConnection.

Examples

if (requireNamespace("dbplyr", quietly = TRUE) &&
      utils::packageVersion("dbplyr") >= "2.6.0") {
  con <- simulate_trino()
  dbplyr::translate_sql(as.numeric(x), con = con)
}

SQL dialect for Trino

Description

Describes Trino to dbplyr so that dplyr verbs on a TrinoConnection are translated to SQL Trino accepts. Registered, when dbplyr 2.6.0 or later is loaded, as an S3 method for dbplyr::sql_dialect(), the extension point that version introduced, which separates the SQL dialect from the connection mechanism.

Usage

sql_dialect.TrinoConnection(con)

Arguments

Details

Trino quotes identifiers with double quotes (the SQL standard), supports the WINDOW clause, accepts AS before a table alias, and does not allow ⁠table.*⁠ prefixes on a star selection.

Value

A dbplyr SQL dialect object.

Literal dates and date-times in dplyr pipelines

Description

A Date or POSIXct from R, in a pipeline such as filter(orderdate >= !!as.Date("2026-01-01")), becomes a typed Trino literal. dbplyr's default renders it as a plain string, which Trino refuses to compare with a date.

Usage

sql_escape_date.sql_dialect_trino(con, x)

sql_escape_datetime.sql_dialect_trino(con, x)

Arguments

Value

SQL.

Explain a Trino query

Description

Trino's EXPLAIN takes the statement directly, without the parenthesised options some engines require.

Usage

sql_query_explain.sql_dialect_trino(con, sql, ...)

Arguments

Details

Column discovery (sql_query_fields()) is deliberately left to dbplyr's default, which wraps the query in ⁠WHERE 0 = 1⁠. On Trino that plans without reading any data, exactly like the ⁠LIMIT 0⁠ the specification suggests, and the default is built from dbplyr internals that a backend should not reach into.

Value

The EXPLAIN statement, as SQL.

Saving a query as a table

Description

Trino has no temporary tables, so compute() needs temporary = FALSE and a name, and then runs ⁠CREATE TABLE ... AS⁠. ANALYZE is not run afterwards: not every connector supports it, and where it does it scans the whole new table.

Usage

sql_query_save.sql_dialect_trino(con, sql, name, temporary = TRUE, ...)

sql_table_analyze.sql_dialect_trino(con, table, ...)

Arguments

Value

SQL, or NULL for no ANALYZE.

Function translations for Trino

Description

Extends dbplyr's base translation so that an R expression computes in Trino what it computes in R, or fails, rather than quietly computing something else:

Usage

sql_translation.sql_dialect_trino(con)

Arguments

Details

• / divides as a double, where Trino would truncate the quotient of two integers; %% takes the sign of the divisor, as in R.

• as.integer() and as.integer64() truncate towards zero, where a cast would round.

• paste(), paste0() and str_c() cast every argument to VARCHAR, which Trino's CONCAT_WS requires.

• grepl(), sub() and gsub() honour fixed and ignore.case, and rewrite R's ⁠\\1⁠ back-references into Trino's ⁠$1⁠; sub() replaces the first match only.

• days(), weeks(), months(), years(), hours(), minutes() and seconds() build intervals, so date + days(1) works where date + 1 does not.

• Casts name Trino types, and median() and quantile() use approx_percentile, which is the percentile Trino offers over an arbitrary number of rows. Null-safe comparison uses Trino's native ⁠IS DISTINCT FROM⁠ rather than dbplyr's portable ⁠CASE WHEN⁠ spelling.

Value

A dbplyr SQL variant.

Authentication methods for Trino

Description

Each helper returns a closure that takes an httr2 request and returns it with the appropriate credentials attached. The credentials live only in that closure's environment, so they are never written to the global environment and never stored on the connection object in plain sight. Pass the result as the auth argument of dbConnect().

Usage

trino_auth_basic(user, password)

trino_auth_jwt(token)

trino_auth_oauth2(client_id, client_secret, token_url, scope = NULL, ...)

Arguments

Details

• trino_auth_basic() — HTTP basic authentication, used by Trino's LDAP and password-file authenticators.

• trino_auth_jwt() — a bearer JWT, for service accounts and unattended pipelines, or for a cluster fronted by an SSO proxy that mints its own tokens.

• trino_auth_oauth2() — OAuth2 client credentials, for corporate identity providers such as Okta or Entra ID. httr2 caches the token and renews it when it expires.

Trino rejects basic and bearer credentials over plain HTTP, so use an ⁠https://⁠ host with all three.

Value

A function of one argument (an httr2 request) returning a modified request.

Examples

# Only builds the credential; nothing is sent until a connection uses it
auth <- trino_auth_basic("analyst", Sys.getenv("TRINO_PASSWORD"))

# Needs a cluster that authenticates with a password
## Not run: 
con <- DBI::dbConnect(
  RTrino::Trino(),
  host    = "https://trino.example.com",
  port    = 443,
  user    = "analyst",
  catalog = "hive",
  schema  = "default",
  auth    = auth
)

## End(Not run)

TLS options for a Trino connection

Description

Collects the TLS settings applied to every request made on a connection. Pass the result as the ssl_options argument of dbConnect().

Usage

trino_ssl(verify = TRUE, ca_bundle = NULL)

Arguments

Details

A cluster whose certificate is signed by an internal authority does not need verification turned off: point ca_bundle at the authority's PEM file and certificates keep being checked. verify = FALSE accepts any certificate, including an attacker's, and so dbConnect() warns once when it opens a connection with it.

Value

A list of TLS options.

Examples

# Secure default
trino_ssl()

# Trust an internal certificate authority
## Not run: 
trino_ssl(ca_bundle = "/etc/ssl/certs/internal-ca.pem")

## End(Not run)

Map a Trino type to the name of an R type

Description

Reports the R type a column of the given Trino type is converted to. Mostly useful for documentation and testing; dbFetch() converts columns through trino_cast_column().

Usage

trino_type_to_r(type, bigint = c("integer64", "numeric", "character"))

Arguments

Value

A string naming an R class.

Examples

trino_type_to_r("varchar(10)")
trino_type_to_r("bigint")
trino_type_to_r("bigint", bigint = "numeric")
trino_type_to_r("timestamp(3) with time zone")