RAthena (version 1.7.0)

sqlCreateTable: Creates query to create a simple Athena table

Description

Creates an interface to compose CREATE EXTERNAL TABLE.

Usage

# S4 method for AthenaConnection
sqlCreateTable(
  con,
  table,
  fields,
  field.types = NULL,
  partition = NULL,
  s3.location = NULL,
  file.type = c("tsv", "csv", "parquet"),
  compress = FALSE,
  ...
)

Arguments

con

A database connection.

table

Name of the table. Escaped with dbQuoteIdentifier().

fields

Either a character vector or a data frame.

A named character vector: Names are column names, values are types. Names are escaped with dbQuoteIdentifier(). Field types are unescaped.

A data frame: field types are generated using dbDataType().

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/"). By default s3.location is set s3 staging directory from '>AthenaConnection object.

file.type

What file type to store data.frame on s3, RAthena currently supports ["csv", "tsv", "parquet"]

compress

FALSE | TRUE To determine if to compress file.type. If file type is ["csv", "tsv"] then "gzip" compression is used. Currently parquet compression isn't supported.

...

Other arguments used by individual methods.

Value

sqlCreateTable returns data.frame's DDL in the SQL format.

See Also

sqlCreateTable

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())
                 
# Create DDL for iris data.frame
sqlCreateTable(con, "iris", iris, s3.location = "s3://path/to/athena/table")

# Create DDL for iris data.frame with partition
sqlCreateTable(con, "iris", iris, 
               partition = c("timestamp" = format(Sys.Date(), "%Y%m%d")),
               s3.location = "s3://path/to/athena/table")
               
# Create DDL for iris data.frame with partition and file.type parquet
sqlCreateTable(con, "iris", iris, 
               partition = c("timestamp" = format(Sys.Date(), "%Y%m%d")),
               s3.location = "s3://path/to/athena/table",
               file.type = "parquet")

# Disconnect from Athena
dbDisconnect(con)
# }