Access S3 Tables in QuickSight without Athena

Posted: | Tags: aws til athena cloud s3

Over the summer AWS announced S3 Tables buckets were natively supported by QuickSight (yes, I’m still calling it QuickSight, not Quick). This meant simplifying a process I talked about earlier on using Athena as a middle-layer between QuickSight and S3 Tables when creating data sources. I just got the time to give it a go, and I think the AWS docs could use some clarifying. The S3 docs, Quick docs and the announcement blog all list out slightly different steps for getting started.

Here’s a TL;DR, like last time, on what you need to do:

  1. Create your S3 Tables bucket, namespace and table.
  2. Ensure your S3 Tables bucket has AWS analytics services integration enabled.
  3. Grant your QuickSight service role permission through IAM.
  4. Add S3 Tables as a data source in QuickSight.
  5. Add a dataset from your previously created data source and use Direct Query or SPICE to query your S3 tables.
  6. Verify you can use your table dataset from an analysis.

Creating the S3 Table

Like last time, the S3 Tables getting started guide does a fairly good job here on this setup. There’s one difference though, I previously recommended creating the bucket from the console in order to enable Integration with AWS analytics services. This would setup Lake Formation, a Glue Catalog and some other black magic, however S3 Tables now supports IAM natively and AWS also provides instructions of accomplishing this through the CLI.

I still have the S3 bucket and integration setup from the last experiment with Lake Formation so I won’t be recreating this. Keep this in mind if the behaviour you observe is different.

  1. Create the S3 Bucket.
aws s3tables create-table-bucket \
--region us-east-1 \
--name amzn-s3-demo-table-bucket
  1. Create a file called catalog.json in order to create the Glue Catalog that would’ve beeen automatically created if you had done this from the console. Add the following JSON object:
{
   "Name": "s3tablescatalog",
   "CatalogInput": {
      "FederatedCatalog": {
          "Identifier": "arn:aws:s3tables:us-east-1:111122223333:bucket/*",
          "ConnectionName": "aws:s3tables"
       },
       "CreateDatabaseDefaultPermissions":[
       {
                "Principal": {
                    "DataLakePrincipalIdentifier": "IAM_ALLOWED_PRINCIPALS"
                },
                "Permissions": ["ALL"]
            }
       ],
       "CreateTableDefaultPermissions":[
       {
                "Principal": {
                    "DataLakePrincipalIdentifier": "IAM_ALLOWED_PRINCIPALS"
                },
                "Permissions": ["ALL"]
            }
       ],
       "AllowFullTableExternalDataAccess": "True"
   }
}

Note that in this example you are defaulting to IAM permissions instead of the mystery that is Lake Formation. In my case, when I setup this up from the console and used Lake Formation (because it was my only choice) the CreateTableDefaultPermissions list was empty ([]).

  1. Create the Glue catalog that will enable integration with AWS analytics services.
aws glue create-catalog \
--region us-east-1 \
--cli-input-json file://catalog.json
  1. Create a namespace in the table bucket.
aws s3tables create-namespace \
--table-bucket-arn arn:aws:s3tables:us-east-1:111122223333:bucket/amzn-s3-demo-table-bucket \
--namespace my_namespace
  1. Create a namepsace in the table bucket.
aws s3tables create-table --cli-input-json file://tabledefinition.json

I’ve copied my table definition file from the last time:

{
    "tableBucketARN": "arn:aws:s3tables:us-east-1:111122223333:bucket/amzn-s3-demo-table-bucket",
    "namespace": "my_namespace",
    "name": "my_table",
    "format": "ICEBERG",
    "metadata": {
        "iceberg": {
            "schema": {
                "fields": [
                     {"name": "id", "type": "int","required": true},
                     {"name": "name", "type": "string"},
                     {"name": "some_int", "type": "int"},
                     {"name": "a_decimal", "type": "decimal(38,19)"}
                ]
            }
        }
    }
}

Granting access to QuickSight

There used to be two sets of access that were required, IAM and Lake Formation. Even though my S3 Tables was enabled with Lake Formation I found that my QuickSight user didn’t need grants, I even revoked my previously assigned grants and checked that this works! I don’t understand why but it makes my life a bit easier.

You’ll only need to update the QuickSight service role.

  1. Find your QuickSight service role. If you don’t know what it is, go to QuickSight, click the user drop down in the top right. Then click Manage Account, followed by AWS resources.

Here you can find out if you are using the default “Quick-managed role” or an existing role.

  1. If you are using the default role simply select S3 Tables and the specific buckets you want to access via QuickSight. Then click Save.

Interestingly enough, with this option AWS creates a separate role altogether that QuickSight uses to access S3 Tables called aws-quicksight-s3-tables-role-v0.

If you’re like me and are using an existing role, note down the ARN and find the role in the IAM console. Once there, attach a policy to the role with the following permissions, making sure to replace the example table ARN with your own:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3tables:GetTableBucket",
                "s3tables:ListNamespaces",
                "s3tables:ListTables"
            ],
            "Resource": [
                "arn:aws:s3tables:us-east-1:111122223333:bucket/amzn-s3-demo-table-bucket"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3tables:GetTable",
                "s3tables:GetTableData",
                "s3tables:GetTableMetadataLocation"
            ],
            "Resource": [
                "arn:aws:s3tables:us-east-1:111122223333:bucket/amzn-s3-demo-table-bucket/table/*"
            ]
        }
    ]
}

Setting up QuickSight

This is the moment we’ve been waiting for, setting up the data source using S3 Tables natively. At this point we’d have gone through Athena in the past.

  1. From the Amazon QuickSight console, click Data then Data source. From the table click Create data source.
  2. A modal will pop-up, from here select Amazon S3 Tables, then enter a name for your data source and the S3 Tables bucket ARN.
  3. If this worked you should not see your data source in the table. If you instead encountered an error along the line of “We weren’t able to create this data source.” Check that the permissions are correctly set on the service role.
  4. Click your newly created data source, and on the new page click Create dataset.
  5. From the dialog box you can select the namespace and tables from the S3 Tables bucket.
  6. Once selected you will see the option to use Direct Query or SPICE. Using SPICE will cost extra! Such is the price of speed. Like last time I will be using Direct Query, this also has the benefit of always being up to date wit the source as there is no caching involved unlike SPICE.

That’s it! You can now create your analysis and dashboards using S3 Tables directly from QuickSight.

I feel with the migrations between Lake Formation and IAM has made the situation a bit more complicated for existing users like myself. Oh well, more fun TIL posts I guess.


Related ramblings