RPresto: DBI Connector to Presto

Description

Implements a 'DBI' compliant interface to Presto. Presto is an open source distributed SQL query engine for running interactive analytic queries against data sources of all sizes ranging from gigabytes to petabytes: https://prestodb.io/.

Package options

rpresto.max.rows

Max number of rows to be fetched before a warning is given.

rpresto.quiet

Verbose output during processing? The default value, NA, turns on verbose output for interactive queries that run longer than two seconds. Use FALSE for immediate verbose output, TRUE for quiet operation.

Author(s)

Maintainer: Jarod G.R. Meng jarodm@fb.com

Authors:

• Onur Ismail Filiz onur@fb.com

• Sergey Goder sgoder@fb.com

Other contributors:

• Thomas J. Leeper thomasleeper@fb.com [contributor]

• John Myles White johnmyleswhite@fb.com [contributor]

See Also

Useful links:

• https://github.com/prestodb/RPresto

• Report bugs at https://github.com/prestodb/RPresto/issues

Connect to a Presto database

Description

Connect to a Presto database

Usage

Presto(...)

## S4 method for signature 'PrestoDriver'
dbConnect(
  drv,
  catalog,
  schema,
  user,
  host = "localhost",
  port = 8080,
  source = methods::getPackageName(),
  session.timezone = "",
  output.timezone = "",
  parameters = list(),
  ctes = list(),
  request.config = httr::config(),
  use.trino.headers = FALSE,
  extra.credentials = "",
  bigint = c("integer", "integer64", "numeric", "character"),
  ...
)

## S4 method for signature 'PrestoConnection'
dbDisconnect(conn)

Arguments

Value

Presto A PrestoDriver object

DBI::dbConnect() A PrestoConnection object

DBI::dbDisconnect() A logical() value indicating success

Examples

## Not run: 
conn <- dbConnect(Presto(),
  catalog = "hive", schema = "default",
  user = "onur", host = "localhost", port = 8080,
  session.timezone = "US/Eastern", bigint = "character"
)
dbListTables(conn, "%_iris")
dbDisconnect(conn)

## End(Not run)

S4 implementation of DBIConnection for Presto.

Description

S4 implementation of DBIConnection for Presto.

Usage

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

## S4 method for signature 'PrestoConnection,ANY,data.frame'
dbAppendTable(conn, name, value, ..., chunk.fields = NULL, row.names = NULL)

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

## S4 method for signature 'PrestoConnection'
dbCreateTableAs(conn, name, sql, overwrite = FALSE, with = NULL, ...)

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

## S4 method for signature 'PrestoConnection,character'
dbGetQuery(conn, statement, ..., quiet = getOption("rpresto.quiet"))

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

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

## S4 method for signature 'PrestoConnection,dbplyr_schema'
dbListFields(conn, name, ...)

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

## S4 method for signature 'PrestoConnection,SQL'
dbListFields(conn, name, ...)

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

## S4 method for signature 'PrestoConnection,dbplyr_schema'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'PrestoConnection,dbplyr_table_path'
dbQuoteIdentifier(conn, x, ...)

## S4 method for signature 'PrestoConnection,AsIs'
dbQuoteIdentifier(conn, x, ...)

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

## S4 method for signature 'PrestoConnection,ANY'
dbReadTable(conn, name, ...)

## S4 method for signature 'PrestoConnection,character'
dbReadTable(conn, name, ...)

## S4 method for signature 'PrestoConnection,dbplyr_schema'
dbReadTable(conn, name, ...)

## S4 method for signature 'PrestoConnection,Id'
dbReadTable(conn, name, ...)

## S4 method for signature 'PrestoConnection,SQL'
dbReadTable(conn, name, ...)

## S4 method for signature 'PrestoConnection,ANY'
dbRemoveTable(conn, name, ..., fail_if_missing = TRUE)

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

## S4 method for signature 'PrestoConnection,character'
dbSendQuery(conn, statement, ..., quiet = getOption("rpresto.quiet"))

## S4 method for signature 'PrestoConnection,ANY,data.frame'
dbWriteTable(
  conn,
  name,
  value,
  overwrite = FALSE,
  ...,
  append = FALSE,
  field.types = NULL,
  temporary = FALSE,
  row.names = FALSE,
  with = NULL,
  chunk.fields = NULL,
  use.one.query = FALSE
)

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

## S4 method for signature 'PrestoConnection'
sqlCreateTableAs(con, name, sql, with = NULL, ...)

Arguments

An S4 class to represent a Presto Driver (and methods) It is used purely for dispatch and dbUnloadDriver is unnecessary

Description

An S4 class to represent a Presto Driver (and methods) It is used purely for dispatch and dbUnloadDriver is unnecessary

Usage

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

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

Class to encapsulate a Presto query

Description

This reference class (so that the object can be passed by reference and modified) encapsulates the lifecycle of a Presto query from its inception (by providing a PrestoConnection and a query statement) to the steps it takes to execute (i.e. an initial POST request and subsequent GET requests).

Details

This is similar to the PrestoQuery class defined in the Presto Python client

Slots

.conn

A PrestoConnection object

.statement

The query statement

.id

The query ID returned after the first POST request

.timestamp

The timestamp of the query execution

.bigint

How BIGINT fields should be converted to an R class

.state

The query state. This changes every time the query advances to the next stage

.next.uri

The URI that specifies the next endpoint to send the GET request

.info.uri

The information URI

.stats

Query stats. This changes every time the query advances to the next stage

.response

HTTP request response. This changes when the query advances

.content

Parsed content from the HTTP request response

.fetched.row.count

How many rows of data have been fetched to R

.post.data.fetched

A boolean flag indicating if data returned from the POST request has been fetched

.quiet

If a progress bar should be shown for long queries (which run for more than 2 seconds. Default to NA which turns on the progress bar for interactive queries.

An S4 class to represent a Presto Result

Description

An S4 class to represent a Presto Result

Usage

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

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

## S4 method for signature 'PrestoResult,numeric'
dbFetch(res, n)

## S4 method for signature 'PrestoResult,missing'
dbFetch(res)

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

## S4 method for signature 'PrestoResult'
dbGetRowsAffected(res)

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

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

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

## S4 method for signature 'PrestoResult,missing'
dbListFields(conn, name)

## S4 method for signature 'PrestoResult,integer'
fetch(res, n = -1, ...)

## S4 method for signature 'PrestoResult,numeric'
fetch(res, n = -1, ...)

## S4 method for signature 'PrestoResult,missing'
fetch(res)

Slots

statement

The SQL statement sent to the database

connection

The connection object associated with the result

query

An internal implementation detail for keeping track of what stage a request is in

post.data

Any data extracted from the POST request response

bigint

How bigint type should be handled

Class to encapsulate a Presto session

Description

A session contains temporary attributes and information that are only useful for the session. It's attached to a PrestoConnection for as long as as the connection lives. There are a few types of information stored.

1. Session properties that can be set via query response headers and need to be sent with following HTTP requests.

2. Common table expressions (CTEs) that can be used to store a subquery and be used in a WITH statement.

Slots

.parameters

List of Presto session parameters to be added to the X-Presto-Session header.

.ctes

List of common table expressions (CTEs), i.e. SELECT statements with names. They can be used in a WITH statement.

Add a chunk field to a data frame

Description

This auxiliary function adds a field, if necessary, to a data frame so that each compartment of the data frame that corresponds to a unique combination of the chunk fields has a size below a certain threshold. This resulting data frame can then be safely used in dbAppendTable() becauase Presto has a size limit on any discrete INSERT INTO statement.

Usage

add_chunk(
  value,
  base_chunk_fields = NULL,
  chunk_size = 1e+06,
  new_chunk_field_name = "aux_chunk_idx"
)

Arguments

Examples

## Not run: 
# returns the original data frame because it's within size
add_chunk(iris)
# add a new aux_chunk_idx field
add_chunk(iris, chunk_size = 2000)
# the new aux_chunk_idx field is added on top of Species
add_chunk(iris, chunk_size = 2000, base_chunk_fields = c("Species"))

## End(Not run)

Create a table in database using a statement

Description

Create a table in database using a statement

Usage

dbCreateTableAs(conn, name, sql, overwrite = FALSE, with = NULL, ...)

Arguments

Return the corresponding presto data type for the given R object

Description

Return the corresponding presto data type for the given R object

Usage

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

Arguments

Details

The default value for unknown classes is ‘VARCHAR’.

Value

A character value corresponding to the Presto type for obj

Examples

drv <- RPresto::Presto()
dbDataType(drv, 1)
dbDataType(drv, NULL)
dbDataType(drv, as.POSIXct("2015-03-01 00:00:00", tz = "UTC"))
dbDataType(drv, Sys.time())
dbDataType(
  drv,
  list(
    c("a" = 1L, "b" = 2L),
    c("a" = 3L, "b" = 4L)
  )
)
dbDataType(
  drv,
  list(
    c(as.Date("2015-03-01"), as.Date("2015-03-02")),
    c(as.Date("2016-03-01"), as.Date("2016-03-02"))
  )
)
dbDataType(drv, iris)

Metadata about database objects

Description

Metadata about database objects

For the PrestoResult object, the implementation returns the additional stats field which can be used to implement things like progress bars. See the examples section.

Usage

## S4 method for signature 'PrestoDriver'
dbGetInfo(dbObj)

## S4 method for signature 'PrestoConnection'
dbGetInfo(dbObj)

## S4 method for signature 'PrestoResult'
dbGetInfo(dbObj)

Arguments

Value

PrestoResult A list() with elements

statement

The SQL sent to the database

row.count

Number of rows fetched so far

has.completed

Whether all data has been fetched

stats

Current stats on the query

Examples

## Not run: 
conn <- dbConnect(Presto(), "localhost", 7777, "onur", "datascience")
result <- dbSendQuery(conn, "SELECT * FROM jonchang_iris")
iris <- data.frame()
progress.bar <- NULL
while (!dbHasCompleted(result)) {
  chunk <- dbFetch(result)
  if (!NROW(iris)) {
    iris <- chunk
  } else if (NROW(chunk)) {
    iris <- rbind(iris, chunk)
  }
  stats <- dbGetInfo(result)[["stats"]]
  if (is.null(progress.bar)) {
    progress.bar <- txtProgressBar(0, stats[["totalSplits"]], style = 3)
  } else {
    setTxtProgressBar(progress.bar, stats[["completedSplits"]])
  }
}
close(progress.bar)

## End(Not run)

Rename a table

Description

Rename a table

Usage

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

Arguments

S3 implementation of db_desc for Presto.

Description

S3 implementation of db_desc for Presto.

S3 implementation of dplyr::db_data_type() for Presto.

S3 implementation of dplyr::db_explain() for Presto.

S3 implementation of dplyr::db_query_rows() for Presto.

S3 implementation of db_collect for Presto.

S3 implementation of collect for Presto.

S3 implementation of compute for Presto.

Usage

## S3 method for class 'PrestoConnection'
db_desc(x)

## S3 method for class 'PrestoConnection'
db_data_type(con, fields, ...)

## S3 method for class 'PrestoConnection'
db_explain(con, sql, ...)

## S3 method for class 'PrestoConnection'
db_query_rows(con, sql, ...)

## S3 method for class 'PrestoConnection'
db_collect(con, sql, n = -1, warn_incomplete = TRUE, ...)

## S3 method for class 'tbl_presto'
collect(x, ..., n = Inf, warn_incomplete = TRUE)

## S3 method for class 'tbl_presto'
compute(x, name, temporary = FALSE, ..., cte = FALSE)

Arguments

dbplyr database methods

Description

dbplyr database methods

Usage

## S3 method for class 'PrestoConnection'
db_list_tables(con)

## S3 method for class 'PrestoConnection'
db_has_table(con, table)

## S3 method for class 'PrestoConnection'
db_write_table(
  con,
  table,
  types,
  values,
  temporary = FALSE,
  overwrite = FALSE,
  ...,
  with = NULL
)

## S3 method for class 'PrestoConnection'
db_copy_to(
  con,
  table,
  values,
  overwrite = FALSE,
  types = NULL,
  temporary = TRUE,
  unique_indexes = NULL,
  indexes = NULL,
  analyze = TRUE,
  ...,
  in_transaction = TRUE,
  with = NULL
)

## S3 method for class 'PrestoConnection'
db_compute(
  con,
  table,
  sql,
  temporary = TRUE,
  unique_indexes = list(),
  indexes = list(),
  analyze = TRUE,
  with = NULL,
  ...
)

## S3 method for class 'PrestoConnection'
db_sql_render(con, sql, ..., use_presto_cte = TRUE)

Arguments

Inform the dbplyr version used in this package

Description

Inform the dbplyr version used in this package

Usage

## S3 method for class 'PrestoConnection'
dbplyr_edition(con)

Arguments

A dummy PrestoConnection

Description

A dummy PrestoConnection

Usage

dummyPrestoConnection()

Examples

dummyPrestoConnection()

Create dummy tables in Presto for testing

Description

create_primitive_arrays_table() creates a dummy table that has ARRAYs of primitive Presto data types.

create_primitive_maps_table() creates a dummy table that has MAPs of primitive Presto data types.

create_primitive_types_table() creates a dummy table that has primitive Presto data types.

create_primitive_rows_table() creates a dummy table that has all primitive data types included in one ROW type column.

create_array_of_rows_table() creates a dummy table that has an ARRAY(ROW) column that has 2 ROW elements, each containing all 17 supported primitive data types.

create_array_of_maps_table() creates a dummy table that has 17 ARRAY(MAP) columns, each of which has an ARRAY of 2 MAP elements.

Usage

create_primitive_arrays_table(
  con,
  table_name = "presto_primitive_arrays",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_primitive_maps_table(
  con,
  table_name = "presto_primitive_maps",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_primitive_types_table(
  con,
  table_name = "presto_primitive_types",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_primitive_rows_table(
  con,
  table_name = "presto_primitive_rows",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_array_of_rows_table(
  con,
  table_name = "presto_array_of_rows",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_array_of_maps_table(
  con,
  table_name = "presto_array_of_maps",
  time_zone = "America/New_York",
  verbose = TRUE
)

Arguments

Details

We construct the arrays-of-primitive-types table by putting two different values of the same type and a NULL value in an array. In this way, the three values of the same type appear together in the source code and therefore are easier to compare. For integer values, we use the theoretical lower bound (i.e., minimum value) and the theoretical upper bound (i.e., maximum value) as the two values. The field names are taken from the Presto data types they represent.

Here's the complete primitive type values included in the table

We construct the maps-of-primitive-types table by first creating a table with ARRAYs of all primitive data types. We then use the MAP() function to create the MAPs from ARRAYs.

We construct the primitive-types table by first creating a table with ARRAYs of all primitive data types. We then use Presto's UNNEST() function to expand the arrays into three separate rows. Each supported Presto data type has three rows in the table so that the resulting R data frame is distinctly different from a simple named list.

We construct the primitive-rows table by first creating a table with all primitive data types. We then use Presto's ⁠CAST(ROW() AS ROW())⁠ function to create the ROW column.

We construct the array-of-rows table by first creating a table with a ROW type column that includes all 17 supported primitive data types. We then use the ARRAY[] function to construct the 2-element ARRAY(ROW) column.

We construct the array-of-maps table by first creating a table a primitive MAP table and then calling the ARRAY[] function to create the ARRAY(MAP) columns.

A convenient wrapper around Kerberos config

Description

The configs specify authentication protocol and additional settings.

Usage

kerberos_configs(user = "", password = "", service_name = "presto")

Arguments

Value

A httr::config() output that can be passed to the request.config argument of dbConnect().

Check if default database is available.

Description

presto_default() works similarly but returns a connection on success and throws a testthat skip condition on failure, making it suitable for use in tests.

RPresto examples and tests connect to a default database via dbConnect(Presto(), ...). This function checks if that database is available, and if not, displays an informative message.

Usage

presto_default(...)

presto_has_default(...)

Arguments

Examples

if (presto_has_default()) {
  db <- presto_default()
  print(dbListTables(db))
  dbDisconnect(db)
} else {
  message("No database connection.")
}

Compose query to create a simple table using a statement

Description

Compose query to create a simple table using a statement

Usage

sqlCreateTableAs(con, name, sql, with = NULL, ...)

Arguments

S3 implementation of sql_query_fields for Presto.

Description

S3 implementation of sql_query_fields for Presto.

S3 implementation of custom escape method for sql_escape_date

S3 implementation of custom escape method for sql_escape_datetime

S3 implementation of sql_translation for Presto.

Usage

## S3 method for class 'PrestoConnection'
sql_query_fields(con, sql, ...)

## S3 method for class 'PrestoConnection'
sql_escape_date(con, x)

## S3 method for class 'PrestoConnection'
sql_escape_datetime(con, x)

## S3 method for class 'PrestoConnection'
sql_translation(con)

dbplyr SQL methods

Description

dbplyr SQL methods

Usage

## S3 method for class 'PrestoConnection'
sql_query_save(con, sql, name, temporary = TRUE, ..., with = NULL)

Arguments

dplyr integration to connect to a Presto database.

Description

Allows you to connect to an existing database through a presto connection.

Usage

src_presto(
  catalog = NULL,
  schema = NULL,
  user = NULL,
  host = NULL,
  port = NULL,
  source = NULL,
  session.timezone = NULL,
  parameters = NULL,
  bigint = c("integer", "integer64", "numeric", "character"),
  con = NULL,
  ...
)

Arguments

Examples

## Not run: 
# To connect to a database
my_db <- src_presto(
  catalog = "memory",
  schema = "default",
  user = Sys.getenv("USER"),
  host = "http://localhost",
  port = 8080,
  session.timezone = "Asia/Kathmandu"
)
# Use a PrestoConnection
my_con <- DBI::dbConnect(
  catalog = "memory",
  schema = "default",
  user = Sys.getenv("USER"),
  host = "http://localhost",
  port = 8080,
  session.timezone = "Asia/Kathmandu"
)
my_db2 <- src_presto(con = my_con)

## End(Not run)

dplyr integration to connect to a table in a database.

Description

Use src_presto to connect to an existing database, and tbl to connect to tables within that database. If you're unsure of the arguments to pass, please ask your database administrator for the values of these variables.

Automatically create a Presto remote database source to wrap around the PrestoConnection object via which DBI APIs can be called.

Usage

## S3 method for class 'src_presto'
tbl(src, from, ..., vars = NULL)

## S3 method for class 'PrestoConnection'
tbl(src, from, ...)

## S3 method for class 'src_presto'
copy_to(
  dest,
  df,
  name = deparse(substitute(df)),
  overwrite = FALSE,
  types = NULL,
  temporary = FALSE,
  unique_indexes = NULL,
  indexes = NULL,
  analyze = FALSE,
  ...,
  in_transaction = FALSE,
  with = NULL
)

## S3 method for class 'PrestoConnection'
copy_to(
  dest,
  df,
  name = deparse(substitute(df)),
  overwrite = FALSE,
  types = NULL,
  temporary = FALSE,
  unique_indexes = NULL,
  indexes = NULL,
  analyze = FALSE,
  ...,
  in_transaction = FALSE,
  with = NULL
)

Arguments

Examples

## Not run: 
# First create a database connection with src_presto, then reference a tbl
# within that database
my_db <- src_presto(
  catalog = "memory",
  schema = "default",
  user = Sys.getenv("USER"),
  host = "http://localhost",
  port = 8080,
  session.timezone = "Asia/Kathmandu"
)
my_tbl <- tbl(my_db, "my_table")

## End(Not run)
## Not run: 
# First create a database connection, then reference a tbl within that
# database
my_con <- DBI::dbConnect(
  catalog = "memory",
  schema = "default",
  user = Sys.getenv("USER"),
  host = "http://localhost",
  port = 8080,
  session.timezone = "Asia/Kathmandu"
)
my_tbl <- tbl(my_con, "my_table")

## End(Not run)