How to connect Athena with Power BI using ODBC

Question

How to connect AWS Athena with Power BI using ODBC.

I have installed 64 bit Simba Athena Connection.

I have added appropriate bucket policy.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "athena:BatchGetQueryExecution",
                "athena:CancelQueryExecution",
                "athena:GetCatalogs",
                "athena:GetExecutionEngine",
                "athena:GetExecutionEngines",
                "athena:GetNamespace",
                "athena:GetNamespaces",
                "athena:GetQueryExecution",
                "athena:GetQueryExecutions",
                "athena:GetQueryResults",
                "athena:GetTable",
                "athena:GetTables",
                "athena:ListQueryExecutions",
                "athena:RunQuery",
                "athena:StartQueryExecution",
                "athena:StopQueryExecution"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "glue:CreateDatabase",
                "glue:DeleteDatabase",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:UpdateDatabase",
                "glue:CreateTable",
                "glue:DeleteTable",
                "glue:BatchDeleteTable",
                "glue:UpdateTable",
                "glue:GetTable",
                "glue:GetTables",
                "glue:BatchCreatePartition",
                "glue:CreatePartition",
                "glue:DeletePartition",
                "glue:BatchDeletePartition",
                "glue:UpdatePartition",
                "glue:GetPartition",
                "glue:GetPartitions",
                "glue:BatchGetPartition"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload",
                "s3:CreateBucket",
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::bucketname*"
            ]
        }
    ]
}

But what would be the ODBC connection configuration for it to connect to Power BI?

Answer 1

Here are the steps:

1. Install AWS CLI from here

2. Once installed, open Command Prompt and give aws2 configure. Enter AWS Access and Secret Key for the user.

3. Install ODBC from here.

4. Create A bucket policy as JSON and attach it to User (replace bucketname with the bucket which has the data to be projected on Power BI):

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Action": [
                   "athena:BatchGetQueryExecution",
                   "athena:CancelQueryExecution",
                   "athena:GetCatalogs",
                   "athena:GetExecutionEngine",
                   "athena:GetExecutionEngines",
                   "athena:GetNamespace",
                   "athena:GetNamespaces",
                   "athena:GetQueryExecution",
                   "athena:GetQueryExecutions",
                   "athena:GetQueryResults",
                   "athena:GetTable",
                   "athena:GetTables",
                   "athena:ListQueryExecutions",
                   "athena:RunQuery",
                   "athena:StartQueryExecution",
                   "athena:StopQueryExecution"
               ],
               "Resource": [
                   "*"
               ]
           },
           {
               "Effect": "Allow",
               "Action": [
                   "glue:CreateDatabase",
                   "glue:DeleteDatabase",
                   "glue:GetDatabase",
                   "glue:GetDatabases",
                   "glue:UpdateDatabase",
                   "glue:CreateTable",
                   "glue:DeleteTable",
                   "glue:BatchDeleteTable",
                   "glue:UpdateTable",
                   "glue:GetTable",
                   "glue:GetTables",
                   "glue:BatchCreatePartition",
                   "glue:CreatePartition",
                   "glue:DeletePartition",
                   "glue:BatchDeletePartition",
                   "glue:UpdatePartition",
                   "glue:GetPartition",
                   "glue:GetPartitions",
                   "glue:BatchGetPartition"
               ],
               "Resource": [
                   "*"
               ]
           },
           {
               "Effect": "Allow",
               "Action": [
                   "s3:GetBucketLocation",
                   "s3:GetObject",
                   "s3:ListBucket",
                   "s3:ListBucketMultipartUploads",
                   "s3:ListMultipartUploadParts",
                   "s3:AbortMultipartUpload",
                   "s3:CreateBucket",
                   "s3:PutObject"
               ],
               "Resource": [
                   "arn:aws:s3:::bucketname*"
               ]
           }
       ] }
   

5. Create an S3 bucket in the same region for power BI to write logs (in the below example its s3://athenanelogbucket.

6. Enter the following in ODBC Athena simba connector in Control Panel -> Administrator tools -> ODBC -> Add

   

7. Set authentication options as Default credentials:

   

Click on Test. If successful then proceed to powerbi

8. And Get Data -> search for odbc -> select simba Athena odbc connector -> click connect.

9. It will ask for username and password, enter the user credentials.