AWS billing dashboard with Quicksight

By Manikandan DUpdated as on March 21, 2023 2 minutes read 99 Views

AWS

Use Case :

How do you Import a cross account S3 dataset into QuickSight?

To-Do :

  • Create a sample dataset [ .zip / .csv ] and upload it into S3 and consider this to be Account A.
  • Account B is where QuickSight resides.

Solution :

  • Update the below bucket policy with QuickSight role arn in Account A S3 dataset bucket :-
{
    "Version": "2012-10-17",
    "Id": "BucketPolicy",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<Account A>:role/service-role/aws-quicksight-service-role-v0"
            },
            "Action": ["s3:ListBucket", "s3:GetObject", "s3:GetObjectVersion" ], 
            "Resource": [
                "<s3 bucket arn>",
                "<s3 bucket arn>/*"
            ]
        }
    ]
}

Note :

  • Create a sample dataset [ .zip / .csv ] and upload it into S3 and consider this to be Account A.
  • Cross account S3 buckets will be listed out only when the option is set to Use QuickSight-managed role (Default), if the option is chosen as Use an existing role QuickSight won’t be listing any buckets from Account A.

2. Moving to Account B, let’s start creating the dataset. Click on Datasets and then click on New Dataset.

Choose S3.

3. Under Data Source name , provide the required name for your dataset and below that choose Upload.

Note :

  • Here we use a manifest.json file to import files from S3.
  • Files that you select for import must be delimited text (for example, .csv or .tsv), log (.clf), or extended log (.elf) format, or JSON (.json).

4. Make use of the below manifest.json snippet and edit the URL accordingly.

{
    "fileLocations": [
        {
            "URIs": [
                "<Paste your file https URL here>"
            ]
        }
    ],
    "globalUploadSettings": {
        "format": "CSV",
	"delimiter": ",",
	"textqualifier": "'",
	"containsHeader": "true"
    }
}

5. Once the upload is done click on connect and if everything works fine it will display a pop-up window something like this,

6. Now you can Edit or review your data. Click on Visualize to complete the process.

Might Face this :

  • If the bucket object is not owned by the bucket owner and the account owner QuickSight won’t be able to connect the S3 Data.
  • To overcome this, the Bucket ACL has to be turned off and here is how it can be done.

1. Go to S3 Bucket in Account A. Click on the Permission tab and look for Object Ownership and click on edit.

2. Check the ACLs disabled box and apply the changes.

3. Now, you will be able to connect to the S3 objects in Account A.