How to run Athena queries from Apache NiFi

1. Download the Athena JDBC jar from https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html

2. Place the jar file on nifi servers and make nifi user as owner.

3. Create the DBCPConnectionPool service

JDBC url: jdbc:awsathena://AwsRegion=[REGION];UID=[ACCESS KEY];PWD=[SECRET KEY];S3OutputLocation=[LOCATION]

jdbc:awsathena://AwsRegion=us-east-1;S3OutputLocation=s3://aws-athena-query-results-127370262072-us-east-1

for database user and password, provide access key and secret key.

4. Configure the ExecuteSQL processor like below

ERROR 9999 (28000): An internal error has occurred. Please retry or report your issues.

I was working on a project for deployment in Microsoft Azure and created an Azure Mysql database.
But while connecting to the database using the MySQL client on a RedHat server I was getting the following error.

ERROR 9999 (28000): An internal error has occurred. Please retry or report your issues.


Normally on the cloud, the first thing I look at checking if the port is open, I used Telnet to check port 3306 is open for the MySQL database. 

[root@cloudera-machine cloudera]# mysql -h mysql001.mysql.database.azure.com -u cloudera -P 3306 -p

Enter password:
ERROR 9999 (28000): An internal error has occurred. Please retry or report your issues.

Then I found to connect to database, we have to use the full username of the pattern youruser@azuredbserver and enclose it in double quotes to work.
[root@cloudera-machine cloudera]# mysql -h mysql001.mysql.database.azure.com -u "cloudera@mysql001" -P 3306 -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 61601
Server version: 5.6.39.0 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MySQL [(none)]>