AWS Athena Workgroups

[nfultz]

AWS Athena Workgroups

[daroczig]

As per the docs at https://s3.amazonaws.com/athena-downloads/drivers/JDBC/SimbaAthenaJDBC_2.0.7/docs/Simba+Athena+JDBC+Driver+Install+and+Configuration+Guide.pdf, I'm pretty sure you can simply pass in the Workgroup parameter to dbConnect.

[karrtikiyer]

Thanks @daroczig , I tried this, but it does not seem to be considering this parameter. It is still taking primary as the workgroup if I see the error below.
con <- dbConnect(AWR.Athena::Athena(), region='us-west-2', S3OutputLocation='s3://my-files/', Schema='default', Workgroup="my_group", user="my_access", password="my_secret" )

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve JDBC result set for Select count(*) from my_table ([Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. User: arn:aws:iam::24234:user/my_user is not authorized to perform: athena:StartQueryExecution on resource: arn:aws:athena:us-west-2:24234:workgroup/primary [Execution ID not available])

[daroczig]

@nfultz, do we need to manually add all options as slots to AthenaConnection?

[nfultz]

@karrtikiyer - do you have an example of connecting using the JDBC package directly? That would help narrow down where the issue may live.

@daroczig - We only need to add properties to AthenaConnection for roxygen / logging. It would be nice if the JDBC package were updated to use the newer S4 patterns (initialize, ...).

[hrbrmstr]

IIRC this feature (workgroup) is Athena JDBC JAR-dependent. I wonder if that's the OP's issue?

[karrtikiyer]

@nfultz : No I have tried using JDBC directly.
@hrbrmstr : Using AthenaJDBC41-1.1.0.jar

[hrbrmstr]

@karrtikiyer try it with AthenaJDBC42_2.0.5.jar or 2.0.6 https://docs.aws.amazon.com/athena/latest/ug/connect-with-previous-jdbc.html

[daroczig]

re versions: AWR.Athena already installs 2.0.6-1 of the jar
@karrtikiyer if you are still on 1.1.0, pls update to the most recent verison of the R pkg

[karrtikiyer]

@daroczig & @hrbrmstr : I have downloaded and installed the latest version of AWR.Athena, however the same issue persists, is there some logs or any other details that I can look into to further identify why the parameter workgroup is not being considered?

[nfultz]

So if the workgroup feature launched on 2019-02, maybe you need the driver that was released then, 2.0.7?

You can create, edit, and set properties, or enforce cost controls on Workgroups using the AWS console, CLI, or the API. By default, all your queries run in the primary Workgroup. No changes are required to use the primary Workgroup. If you create new Workgroups, and use the JDBC driver or the AWS SDK, you will need to upgrade to the latest version of the driver and SDK. Learn more

So maybe the old driver, 2.0.6 uses primary automatically, but can't use non-primary?

@karrtikiyer - can you try using 2.0.7 ? If that works, I can update the package to use it. I've started a new branch, not able to test myself right now:

devtools::install_github("nfultz/AWR.Athena@nfultz/2.0.7") 

[karrtikiyer]

@nfultz : I tried devtools::install_github("nfultz/AWR.Athena@nfultz/2.0.7") as well, installing 2.0.7 as you have mentioned above, however I still get the same error.

[karrtikiyer]

@nfultz , @daroczig , @hrbrmstr : I tried even directly with RJDBC, that also did not work.
Any other thoughts or workarounds which I can try?

[nfultz]

Have you tried pasting the Workgroup option into the jdbc connection string?

…

On Tue, Jun 11, 2019 at 11:17 PM karrtikiyer ***@***.***> wrote: @nfultz <https://github.com/nfultz> , @daroczig <https://github.com/daroczig> , @hrbrmstr <https://github.com/hrbrmstr> : I tried even directly with RJDBC, that also did not work. Any other thoughts or workarounds which I can try? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AADGGTSTXPLJ7FGTGWRJPUDP2CIHRA5CNFSM4HWTQPS2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXPLVNQ#issuecomment-501136054>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AADGGTSVH2F7WCBSYN7OQXLP2CIHRANCNFSM4HWTQPSQ> .

[karrtikiyer]

No I haven’t tried that, would you be able to share the correct syntax or a sample jdbc connection string with workgroup? I will also search for that in meanwhile.

[karrtikiyer]

@nfultz : Tried like below but it still did not work:
jdbc:awsathena://athena.us-west-2.amazonaws.com:443/;Workgroup=my-group;

[nfultz]

This is also mentioned:

Migration from Previous Version of the JDBC Driver
The current JDBC driver version 2.0.7 is a drop-in replacement of the previous version of the JDBC driver version 2.0.6, and is backwards compatible with the JDBC driver version 2.0.6, with the following step that you must perform to ensure the driver runs.

Important

To ensure that you can use the JDBC driver versions 2.0.5 or greater, add the athena:GetQueryResultsStream policy action to the list of policies for Athena. This policy action is not exposed directly with the API and is only used with the JDBC driver, as part of streaming results support. For an example policy, see AWSQuicksightAthenaAccess Managed Policy. For more information about upgrading to versions 2.0.5 or greater from version 2.0.2, see the JDBC Driver Migration Guide. Additionally, ensure that port 444 is open to outbound traffic.

Can you verify that the permission is set correctly?

[karrtikiyer]

I will check that @nfultz , but wanted to pass on one more information, the same athena connection using workgroup works well via python. If that is the case, I would tend to assume the permission would be set correctly. Does it make sense?

[nfultz]

Interesting - which python package are you referring to?

…

On Wed, Jun 12, 2019 at 8:42 PM karrtikiyer ***@***.***> wrote: I will check that @nfultz <https://github.com/nfultz> , but wanted to pass on one more information, the same athena connection using workgroup works well via python. If that is the case, I would tend to assume the permission would be set correctly. Does it make sense? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AADGGTTNOA6PWHVWKROUYRLP2G63TA5CNFSM4HWTQPS2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXSNQOQ#issuecomment-501536826>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AADGGTUMFOIUIYOM24GNX3LP2G63TANCNFSM4HWTQPSQ> .

[karrtikiyer]

@nfultz : PyAthenaJDBC

[nfultz]

Are you using the 4.1 or 4.2 version of the driver in python?

[karrtikiyer]

@nfultz : Using the below version:
pyathenajdbc 2.0.4 pypi_0 pypi

[nfultz]

Also what version of JDBC driver jar from Amazon is that using?

…

On Thu, Jun 13, 2019 at 9:48 PM karrtikiyer ***@***.***> wrote: @nfultz <https://github.com/nfultz> : Using the below version: pyathenajdbc 2.0.4 pypi_0 pypi — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=AADGGTUOLONEBALNULBHAGDP2MPKZA5CNFSM4HWTQPS2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXVWLMY#issuecomment-501966259>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AADGGTVA7SCNSMWNDBIGAPDP2MPKZANCNFSM4HWTQPSQ> .

[karrtikiyer]

@nfultz : Java Java >= 8 (JDBC 4.2)

[nfultz]

I am manually testing the nfultz/2.0.7 branch, and workgroups appear to be working for me:

> con <- dbConnect(AWR.Athena::Athena(), region='us-west-2', S3OutputLocation='s3://nfultz-athena-staging', Schema='default', Workgroup='AWR_Athena')
> dbGetQuery(con, "Select count(*) from sampledb.elb_logs")
    _col0
1 1356206
> dbGetQuery(con, "Select count(*) + 5 from sampledb.elb_logs")
    _col0
1 1356211

I can see the two queries when I go to the console, and select the "AWR_Athena" workgroup, and then click history:

Here is my sessionInfo, if it helps:

> sessionInfo()
R version 3.6.0 (2019-04-26)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 18.04.2 LTS

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/atlas/libblas.so.3.10.3
LAPACK: /usr/lib/x86_64-linux-gnu/atlas/liblapack.so.3.10.3

locale:
 [1] LC_CTYPE=en_US.UTF-8          LC_NUMERIC=C                 
 [3] LC_TIME=en_US.UTF-8           LC_COLLATE=en_US.UTF-8       
 [5] LC_MONETARY=en_US.UTF-8       LC_MESSAGES=en_US.UTF-8      
 [7] LC_PAPER=en_US.UTF-8          LC_NAME=en_US.UTF-8          
 [9] LC_ADDRESS=en_US.UTF-8        LC_TELEPHONE=en_US.UTF-8     
[11] LC_MEASUREMENT=en_US.UTF-8    LC_IDENTIFICATION=en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] DBI_1.0.0          AWR.Athena_2.0.7-0 colorout_1.2-1    

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.1        magrittr_1.5      usethis_1.5.0     devtools_2.0.2   
 [5] pkgload_1.0.2     R6_2.4.0          rlang_0.3.4       tools_3.6.0      
 [9] pkgbuild_1.0.3    sessioninfo_1.1.1 cli_1.1.0         withr_2.1.2      
[13] remotes_2.0.4     assertthat_0.2.1  digest_0.6.19     rprojroot_1.3-2  
[17] crayon_1.3.4      processx_3.3.1    rJava_0.9-11      callr_3.2.0      
[21] RJDBC_0.2-7.1     fs_1.3.1          ps_1.3.0          curl_3.3         
[25] testthat_2.1.1    memoise_1.1.0     glue_1.3.1        compiler_3.6.0   
[29] desc_1.2.0        backports_1.1.4   prettyunits_1.0.2

[nfultz]

@karrtikiyer Is it possible that you have incorrect IAM / permissions set?

[nfultz]

If there's no objections, I'll merge the PR, then send to Rhub and then cran.

[karrtikiyer]

Can I test this once?

On Fri, 21 Jun 2019 at 04:49, Neal Fultz ***@***.***> wrote: If there's no objections, I'll merge the PR, then send to Rhub and then cran. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#16?email_source=notifications&email_token=ABBMHME3MUTDYE2DHFYTLV3P3QGARA5CNFSM4HWTQPS2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYG7VXA#issuecomment-504232668>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABBMHMHZYRU2AAH6IYYKL3LP3QGARANCNFSM4HWTQPSQ> .

-- Thanks and regards, Karrtik

[karrtikiyer]

@karrtikiyer Is it possible that you have incorrect IAM / permissions set?

@nfultz : If this was the case this wouldn't work from python as well, is that the right way to think?

[karrtikiyer]

If there's no objections, I'll merge the PR, then send to Rhub and then cran.

@nfultz : I tested again using devtools::install_github("nfultz/AWR.Athena@nfultz/2.0.7"). And I confirm that the workgroup parameter works. You can merge the PR.
Thanks for all the support.