Can I write to a Athena table on s3 using DBI?

[dougfir]

I have an odbc connection to Athena of the form:

[Athena]
Description=Simba Athena ODBC Driver (64-bit) DSN
Driver=/opt/simba/athenaodbc/lib/64/libathenaodbc_sb64.so
AwsRegion=us-east-1
Schema=default
S3OutputLocation=s3://ourco-athena/results
AuthenticationType=Instance Profile
LogPath=/var/log/
LogLevel=4

This works fine for reading data from Athena. For example, I created a new empty table in hive which uses the same meta store as s3 for Athena:

CREATE EXTERNAL TABLE IF NOT EXISTS adhoc.mtcars
(
mpg integer,
cyl integer,
disp integer,
hp integer,
drat integer,
wt integer,
qsec integer,
vs integer,
am integer,
gear integer,
carb integer)
partitioned by (year string, month string, day string)
stored as orc
location 's3://ourco-emr/tables/adhoc.db/mtcars';

I can read this new empty table using DBI::dbReadTable:

con <- dbConnect(odbc(), "Athena")
dbReadTable(con, DBI::Id(schema = "adhoc", table = "mtcars"))

Returns:

 [1] mpg   cyl   disp  hp    drat  wt    qsec  vs    am    gear  carb  year  month day  
<0 rows> (or 0-length row.names)

So, the empty table is clearly there and visible.

Note the above in the hive create table statement:

location 's3://ourco-emr/tables/adhoc.db/mtcars'

The data for this table should be stored in s3 at that location.

I tried to write mtcars to this location using dbWriteTable in the hope my empty table would populate with the data in mtcars:

dbWriteTable(conn = con,
             name = "tables/adhoc.db/mtcars",
             value = mtcars,
             overwrite = FALSE,
             append = TRUE,
             file.type = "orc",
             partition = c(year = "2020", month = "02", day = "01"),
             s3.location =  "s3://ourco-emr/tables/adhoc.db/mtcars/mtcars")

This seems to run for a few seconds before returning this error message:

Error: nanodbc/nanodbc.cpp:1617: 00000: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 130, HTTP Response Code: 400, Exception Name: InvalidRequestException, Error Message: line 1:14: no viable alternative at input 'CREATE TABLE "tables/adhoc.db/mtcars"' [Execution ID: ]
'CREATE TABLE "tables/adhoc.db/mtcars" (
"row_names" VARCHAR(255),
"mpg" DOUBLE PRECISION,
"cyl" DOUBLE PRECISION,
"disp" DOUBLE PRECISION,
"hp" DOUBLE PRECISION,
"drat" DOUBLE PRECISION,
"wt" DOUBLE PRECISION,
"qsec" DOUBLE PRECISION,
"vs" DOUBLE PRECISION,
"am" DOUBLE PRECISION,
"gear" DOUBLE PRECISION,
"carb" DOUBLE PRECISION
)

Looks like dbi is trying to create a new table where I just want to append to the existing, albeit empty on that I created earlier.

How can I send a data frame to s3 using DBI? Is this possible?

[dougfir]

I was able to get what I need using package aws.s3:

library(tidyverse)
library(aws.s3)
mtcars_json <- jsonlite::toJSON(mtcars %>% mutate(year = "2020", month = "03", day = "05"))
s3save(mtcars_json, bucket = "s3://ourco-emr/", object = "tables/adhoc.db/mtcars/mtcars")