QuickSight cannot see cross-account Athena DB

user_15400208 0
Viewed 1205

I have QuickSight set up in account_a in eu-west-1. I am attempting to query from Athena which is in account_b.

I have two databases [one is "default" and another one with another name] in Athena, both under the same AWS Glue catalog. The catalog shows up as a data source in QuickSight but none of the databases show up in QuickSight, like the following:

Screenshot of Athena Data Source in QuickSight

When I try to use the custom query functionality, it gives the following error:

[Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 2:34: Schema default does not exist [Execution ID: **-**-**-**-**]

The query I'm running is:

select * from default.tablename;

At this point I am really confused. What are some of the possible actions that I could take here?

amazon-quicksight amazon-athena aws-glue
1 Answers

I ran into a similar issue today. I am using Lake Formation to handle permissions of the Glue Catalog. If you are as well, you can follow this guide to get up and running: https://learnquicksight.workshop.aws/en/admin-level-up/data-lake-permissions.html

Essentially you grant the QuickSight ARN access to your source database/tables from within Lake Formation. Once granted, the database will appear inside QuickSight's Athena Datasets.

Lake Formation will also help with cross-account sharing of data resources in general. You enable Lake Formation on the source and target accounts and grant permissions as needed. This video is useful: https://www.youtube.com/watch?v=MJ9_hsCk52w

There are, as of May 2021, a few UI caveats with Lake Formation that I've ran into that are not directly related to this question but might help:

In source account, upon setting up Lake Formation in new account:

  • Under Data catalog > settings, uncheck the 'Use only IAM ...' options. This is for backward compatibility not needed for new accounts.
  • The 'data lake locations' option MUST include your S3 buckets in order to share to other accounts, it is this steps that provides the role that your target account will assume
  • You will probably want to crawl your tables via Glue. Make sure you have a Glue IAM role created AND grant privileges to the role via Lake Formation. Principle of least privilege applies to the Glue IAM role but you can just use 'poweruser' access for testing.
  • Once the DB/tables are added, you must revoke 'IAMAllowedPrincipals' for each database/table you are to share or it will fail. I think this is also a legacy Glue setting that can be safely removed for new accounts.

In target account:

  • You must create a 'resource link' in order to use the shared db/table (see the video for ref). This can be done by either a) creating a new local database and attaching the new resource link to the new database... or
    • you can create a resource link on the database itself... however, I found that you had to manually copy paste the name exactly from the source account, otherwise it doesn't work (it didn't auto populate the shared database unlike the shared tables). Hope that makes sense. It is probably a bug that will be fixed soon.
user_62836410
Related