Power BI: How to Connect to Athena -- Part 1: AWS Setup

Part 1 of the video is for AWS Admin to set up the access for Power BI Developers. If you are a Power BI User, please jump to part 2 of the video • Power BI: How to Conne...
00:00 Intro
00:50 Break Down the Policy
05:23 Policy JSON
06:58 Live Demo in AWS Console
11:24 Advanced Topic
Policy:
***********************************Data***********************************
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "SourceBucketReadOnlyAccess",
"Effect": "Allow",
"Action": [
"s3:Get*",
"s3:List*"
],
"Resource": [
"arn:aws:s3:::hc4.tfs.amazonaws.com/AWSLogs/486152105877/*",
"arn:aws:s3:::hc4.tfs.amazonaws.com"
]
}
]
}
***********************************Glue***********************************
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "GlueCatalog",
"Effect": "Allow",
"Action": [
"glue:Get*"
],
"Resource": [
"arn:aws:glue:us-east-1:486152105877:catalog",
"arn:aws:glue:us-east-1:486152105877:table/default/*",
"arn:aws:glue:us-east-1:486152105877:database/default"
]
}
]
}
*************************Athena***********************************
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AthenaQueryExecution",
"Effect": "Allow",
"Action": [
"athena:StartQueryExecution",
"athena:StopQueryExecution",
"athena:GetQuery*"
],
"Resource": "arn:aws:athena:us-east-1:486152105877:workgroup/primary"
},
{
"Sid": "AthenaResultBucket",
"Effect": "Allow",
"Action": [
"s3:PutObject*",
"s3:Get*"
],
"Resource": [
"arn:aws:s3:::tfsds-matthew-test",
"arn:aws:s3:::tfsds-matthew-test/athena/*",
"arn:aws:s3:::tfsds-matthew-test/athena"
]
}
]
}

[@jimsun5895]

This is a day saver video if you need to develop PowerBI sourced from Athena!

[@jordanbreiner-moreno7992]

Very nice video Matthew!

[@cornel4931]

Hi @Matthew Yu and thanks for awesome guidance. 2 points of feedback. The IAM Policies. How does one get the template for the JSON? As other users have pointed out the location is no longer avail under properties.

[@L26ist]

This tutorial is very useful and is very well done. I followed each step, but when I try to connect with odbc, the Data Catalog does not bring the tables of the selected Data Base. Would you help me?

[@lorenvasquez17]

Hi, Matthew, I wanted to understand more about the security risk you talked about when authenticating with a secret key. Does it put in any danger the information itself contained in the databases of S3?

[@marcelaandreacastrillonbui7951]

Hey Mattew! When I I am looking for the location, it does not appear...

[@cornel4931]

Hey exact same issue here. Did you find a solution to getting the table location?

[@marcelaandreacastrillonbui7951]

@@cornel4931 Yes I did, I didn’t read the bottom the guy talked about it

[@jordanbreiner-moreno7992]

You can also run query SHOW CREATE TABLE table_example; in Athena query editor to get location.

[@lucasneves3465]

Hi! How can I access that json?

[@jordanbreiner-moreno7992]

The policy action names in AWS changed. This prevented me from connecting data to Power BI but I was able to connect by updating the inline policy. Example: { "Version": "2012-10-17", "Statement": [ { "Sid": "AthenaQueryExecution", "Effect": "Allow", "Action": [ "athena:StartQueryExecution", "athena:StopQueryExecution", "athena:GetQueryExecution", "athena:GetQueryResultsStream" ], "Resource": "arn:aws:athena:region-example:xxxxxxxxxxxx:workgroup/primary" }, { "Sid": "AthenaResultBucket", "Effect": "Allow", "Action": [ "s3:PutObject*", "s3:Get*" ], "Resource": [ "arn:aws:s3:::s3_bucket_example", "arn:aws:s3:::s3_bucket_exampl/query_results", "arn:aws:s3:::s3_bucket_exampl/query_results/*" ] } ] } Visit link for more info on policies: docs.aws.amazon.com/athena/latest/ug/managed-policies.html

[@akmr0079]

1.you should add your data source bucket also 2.In Action, you should add listbucket , listbucketall.. it will try to try to locate your bucket in s3.

[@ipsitamahapatra2062]

How can we connect to AWS S3 from powerBi??

[@amirthaparthasarathy8718]

Hi, any solution, I am looking for that currently

[@jamisonfriauf]

You do it through Athena. You need to have a Glue Database sitting over the S3 bucket as metadata. This video here is exactly what you need.