RAthena (version 1.3.0)

AthenaWriteTables: Convenience functions for reading/writing DBMS tables

Description

Convenience functions for reading/writing DBMS tables

Usage

# S4 method for AthenaConnection,character,data.frame
dbWriteTable(conn, name,
  value, overwrite = FALSE, append = FALSE, row.names = NA,
  field.types = NULL, partition = NULL, s3.location = NULL,
  file.type = c("csv", "tsv", "parquet"), ...)
# S4 method for AthenaConnection,Id,data.frame
dbWriteTable(conn, name, value,
  overwrite = FALSE, append = FALSE, row.names = NA,
  field.types = NULL, partition = NULL, s3.location = NULL,
  file.type = c("csv", "tsv", "parquet"), ...)
# S4 method for AthenaConnection,SQL,data.frame
dbWriteTable(conn, name, value,
  overwrite = FALSE, append = FALSE, row.names = NA,
  field.types = NULL, partition = NULL, s3.location = NULL,
  file.type = c("csv", "tsv", "parquet"), ...)

Arguments

conn

An '>AthenaConnection object, produced by [DBI::dbConnect()]

name

A character string specifying a table name. Names will be automatically quoted so you can use any sequence of characters, not just any valid bare table name.

value

A data.frame to write to the database.

overwrite

Allow overwriting the destination table. Cannot be `TRUE` if `append` is also `TRUE`.

append

Allow appending to the destination table. Cannot be `TRUE` if `overwrite` is also `TRUE`.

row.names

Either TRUE, FALSE, NA or a string.

If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector.

A string is equivalent to TRUE, but allows you to override the default name.

For backward compatibility, NULL is equivalent to FALSE.

field.types

Additional field types used to override derived types.

partition

Partition Athena table (needs to be a named list or vector) for example: c(var1 = "2019-20-13")

s3.location

s3 bucket to store Athena table, must be set as a s3 uri for example ("s3://mybucket/data/")

file.type

What file type to store data.frame on s3, RAthena currently supports ["csv", "tsv", "parquet"]. Note: "parquet" format is supported by the arrow package and it will need to be installed to utilise the "parquet" format.

...

Other arguments used by individual methods.

Value

dbWriteTable() returns TRUE, invisibly. If the table exists, and both append and overwrite arguments are unset, or append = TRUE and the data frame with the new data has different column names, an error is raised; the remote table remains unchanged.

See Also

dbWriteTable

Examples

# NOT RUN {
# Note: 
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `RAthena::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name 
con <- dbConnect(RAthena::athena())

# List existing tables in Athena
dbListTables(con)

# Write data.frame to Athena table
dbWriteTable(con, "mtcars", mtcars,
             partition=c("TIMESTAMP" = format(Sys.Date(), "%Y%m%d")),
             s3.location = "s3://mybucket/data/")
             
# Read entire table from Athena
dbReadTable(con, "mtcars")

# List all tables in Athena after uploading new table to Athena
dbListTables(con)

# Checking if uploaded table exists in Athena
dbExistsTable(con, "mtcars")

# Disconnect from Athena
dbDisconnect(con)
# }