Write tables

[pjrh-moj]

Hi @mratford and @ymao2 ,

I'm just trying to tidy up Rdbtools a bit as more people are using it. The last bit of functionality I haven't got working is to write data to Athena - I don't know if this is a permissions issue?

The code currently lets you create a temporary database, and create new tables in it from existing tables on Athena (e.g. CREATE TABLE AS ) but not using the dbWriteTable function from noctua. I'd like to get dbWriteTable - see my attempts in the branch table_write.

I should be able to do:

library(tidyverse)
test_table <- tibble(col1 = c(1,2,3,4), col2 = c("a","b","c","d"))
con <- connect_athena()
dbWriteTable(con, "__temp__.tab_test", test_table)

However I try this, I get: Error: AccessDenied (HTTP 403). Access Denied.

The reason I want this is twofold

• one is simple I often have a list of ids that I want to use to select rows out of an existing table. It would be really useful to be able to write these to a temporary table and then join in the required records. Composing a SELECT ... WHERE id = ... statement doesn't work with too many ids.
• secondly in dbplyr (of which I'm a big fan!) if you want to join a local table to one in Athena (this could be to do the same as above, or as a lookup) then internally this needs to write a table to Athena

Just wondering if either of you could shed some light on this!

[pjrh-moj]

I've has this in the back of my head all day, and I think I've understood where the issue is. It seems like as long as I specify the s3.location manually then I can upload a table - suggesting that the permissions for the s3_staging_dir which is generated for us doesn't give write permissions.

E.g., this works: dbWriteTable(con, "__temp__.dbwrite2", df, s3.location = "s3://bucket_where_I_have_access")

So we have two options:

• change the permissions for users to write to their staging dir (may be tricky to get this done, and I have no idea about the unintended consequences)
• just get users to set their own staging_dir when they open the connection to the database - this we can do straightforwardly, although is less reproducible when new people come to run the code they also need access to that staging dir

[ymao2]

@pjrh-moj , Thank you for adding me into this thread and the update for this issue, thinking about to reply you yesterday but was busy. tbh, based on my knowledge to Athena and R, I am not going to be useful for this issue at this stage, maybe later once I have time to dive in 😳, just based on your finding and comment, my comment would be

• I am not sure you mean 'change'? are you proposing that we change the permission of s3 bucket folder? I would think it may not be possible to do it from our code, it should be controlled by the parent folder's policy mainly, also I am surprised that the caller (using AP) doesn't have write permission on their personal staging dir if the staging dir is meant to store the result, maybe due to the role?

If it is the main reason, I suggest to speak to AP, e.g. setup separate folder with proper permission of parent folder for temporary table only, then we can not only to justify our finding, also you may be able to do something for switching the staging dir in the code?

• I agree, again, it is good to discuss with AP team

[pjrh-moj]

No problem @ymao2 - I think there's a combination of technical Athena questions, but also how users will use this is practise so keen to get your view on this as well.

I agree that changing permissions is an AP team issue, not something we can do ourselves. So the second option becomes the obvious route to pursue, and I've done this in PR #8. Actually, in the end it's mainly a case of updating the documentation as it was something that was already possible in the code so a fairly straightforward fix. It would be useful if you wanted to have a look at the code in that PR and see if the instructions are clear enough and then we can merge it in?

I'll ask the AP team why we don't have write permission to the staging dir which gets created for us - I suspect it's an issue that they have a good reason for and we can't easily change. Given we've got a working method now and have documented why it is, then I guess we don't have such a strong need for any changes at their end - it might be easiest to see how it goes as is first anyway.