我可以使用DBI写到s3上的雅典娜表吗？

Question

我有一个到Athena的odbc连接，并且已经能够读取和检索数据了。例如，我在hive中创建了一个新的空表，它对Athena使用了与s3相同的元存储：

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';

我可以使用DBI：：DbReadTable读取这个新的空表：

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

返回：

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

location 's3://ourco-emr/tables/adhoc.db/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")

如何使用DBI向s3发送数据帧？

Answer 1

我无法评论ODBC，但有两个包Rathena和Noctua，它们具有DBI方法，用于将数据上传到AWS雅典娜。

Rathena使用Python SDKBoto3创建到AWS的连接。Noctua使用R SDKpaws创建到AWS的连接。

library(DBI)

# connect to AWS Athena using RAthena
con = dbConnect(RAthena::athena())

# OR connect to AWS Athena using noctua
con = dbConnect(noctua::athena())

# Uploading to existing AWS Athena table
dbWriteTable(conn = con,
             name = "adhoc.mtcars",
             value = mtcars,
             append = TRUE,
             file.type = "parquet",
             partition = c(year = "2020", month = "02", day = "01"),
             s3.location =  "s3://ourco-emr/tables/")

dbGetQuery(con, "select * from adhoc.iris")

目前，这些包在上传到AWS雅典娜时只支持file.types[“tsv”,“csv”,“parquet”]。要扩展当前软件包的功能，请在以下位置提出特性请求：https://github.com/dyfanjones/rathena/issues和https://github.com/dyfanjones/noctua/issues。