Unable to execute Amazon Athena User Defined Functions query from Sagemaker R kernel

Question

I am trying to run an athena query in a Sagemaker R Kernel notebook. The query uses an external UDF function. This query works on athena console but gives the following error in the notebook.

```
Error in dbSendQuery(conn, statement, ...): Unable to retrieve JDBC result set
  JDBC ERROR: [Simba][JDBC](11300) A ResultSet was expected but not generated from query . Query not executed. 
```
When I remove the following part of the query which uses external function, it works.

```
USING EXTERNAL FUNCTION get_hash_asin(page_id VARCHAR)
RETURNS INT 
LAMBDA '{arn}'
```

I have also added necessary lambda:invokeFunction permission to IAM user and notebook's role using the ref: https://docs.aws.amazon.com/athena/latest/ug/udf-iam-access.html

I am using RJDBC library for running queries using the ref:  https://aws.amazon.com/blogs/machine-learning/accessing-data-sources-from-amazon-sagemaker-r-kernels/

How do I resolve this issue?

Answer

Hi, can you try using `dbSendUpdate(conn, statement, ...)`?

RJDBC pre-dates the more recent DBI specification and uses a different function to access this functionality: RJDBC::dbSendUpdate(con, query), can find more info [here](https://cran.r-project.org/web/packages/RJDBC/RJDBC.pdf).

\
*If you find this to be useful, please consider accepting answer.

Unable to execute Amazon Athena User Defined Functions query from Sagemaker R kernel

Relevant content