List Amazon QuickSight's top 25 SPICE capacity consuming datasets.

qcc.go

// `qcc` lists Amazon QuickSight's top 25 SPICE capacity consuming datasets.
//
// It requires the following IAM permissions:
// - quicksight:ListDataSets
// - quicksight:DescribeDataSet
// - quicksight:DescribeDataSetPermissions
//
// It may also require the following IAM permission, if you have dataset(s) created using CSV files:
// - quicksight:ListIngestions
//
// Make sure you set the correct `AwsAccountId` and `AwsRegion` constants in line 32 and 33
// before building and running this program.
//
// Usage: rm -f qcc && go build -ldflags "-w -s" -o ./qcc ./ && ./qcc
package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"os"
	"sort"
	"strings"

	"github.com/aws/aws-sdk-go-v2/aws"
	"github.com/aws/aws-sdk-go-v2/config"
	"github.com/aws/aws-sdk-go-v2/service/quicksight"
	"github.com/aws/aws-sdk-go-v2/service/quicksight/types"
	"github.com/olekukonko/tablewriter"
)

const AwsAccountId = "REPLACE_ME"
const AwsRegion = "REPLACE_ME"

var (
	cfg aws.Config
	qs  *quicksight.Client
)

type DataSet struct {
	DataSetId          string
	Name               string
	SpiceUsage         float64
	UsersWhoHaveAccess string
}
type DataSets map[string]*DataSet

func init() {
	var err error
	cfg, err = config.LoadDefaultConfig(context.TODO(), config.WithRegion(AwsRegion))
	if err != nil {
		log.Fatalf("unable to load AWS SDK config, %v", err)
	}

	qs = quicksight.NewFromConfig(cfg)
}

func main() {
	log.Printf("creating a list of all datasets in the %s region (AWS Account ID: %s)...", AwsRegion, AwsAccountId)
	var dsList []types.DataSetSummary
	var nextToken *string
	for {
		out, err := qs.ListDataSets(context.TODO(),
			&quicksight.ListDataSetsInput{
				AwsAccountId: aws.String(AwsAccountId),
				MaxResults:   aws.Int32(100),
				NextToken:    nextToken,
			})
		if err != nil {
			log.Fatalf("fatal error failed to `ListDataSets`, %v", err)
		}
		dsList = append(dsList, out.DataSetSummaries...)
		nextToken = out.NextToken
		if nextToken == nil {
			break
		}
	}

	log.Println("fetching details of all the listed datasets...")
	var cntSpice int = 0
	ds := make(map[string]*DataSet)
	dsIds := make([]string, 0)
	for _, d := range dsList {
		if d.ImportMode != "SPICE" {
			continue
		}
		out, err := qs.DescribeDataSet(context.TODO(),
			&quicksight.DescribeDataSetInput{
				AwsAccountId: aws.String(AwsAccountId),
				DataSetId:    d.DataSetId,
			})
		usageSizeInBytes := int64(0)
		if err != nil {
			var e *types.InvalidParameterValueException
			if errors.As(err, &e) {
				// `DescribeDataSet` API may return `InvalidParameterValueException` error
				// for datasets created by CSV files AFAIK.
				// We need to use `ListIngestions` API to describe that kind of dataset to obtain
				// its SPICE usage size.
				out2, err2 := qs.ListIngestions(context.TODO(),
					&quicksight.ListIngestionsInput{
						AwsAccountId: aws.String(AwsAccountId),
						DataSetId:    d.DataSetId,
					})
				if err2 != nil {
					log.Printf("error failed to `ListIngestions`. skipping...: %v", err2)
					continue
				}
				totalIngestionSizeInBytes := int64(0)
				for _, in := range out2.Ingestions {
					totalIngestionSizeInBytes += *in.IngestionSizeInBytes
				}
				usageSizeInBytes = totalIngestionSizeInBytes
			} else {
				log.Printf("error failed to `DescribeDataSet`. skipping...: %v", err)
				continue
			}
		} else {
			usageSizeInBytes = out.DataSet.ConsumedSpiceCapacityInBytes
		}
		if usageSizeInBytes == 0 {
			log.Printf("dataset %s uses no SPICE capacity. skipping...", *d.DataSetId)
			continue
		}
		gb := float64(usageSizeInBytes) / (1 << 30) // bytes to gb
		ds[*d.DataSetId] = &DataSet{
			DataSetId:  *d.DataSetId,
			Name:       *d.Name,
			SpiceUsage: gb,
		}
		dsIds = append(dsIds, *d.DataSetId)
		cntSpice++
	}
	log.Printf("found %d datasets which consume SPICE capacity\n", cntSpice)

	// sort by SpiceUsage
	sort.SliceStable(dsIds, func(i, j int) bool {
		return ds[dsIds[i]].SpiceUsage > ds[dsIds[j]].SpiceUsage
	})

	n := 25 // qcc prints the top 25 SPICE capacity consuming datasets by default
	if len(dsIds) < n {
		n = len(dsIds)
	}
	var d *DataSet
	log.Printf("fetching who have access to the top %d SPICE capacity consuming datasets...\n", n)
	for i := 0; i < n; i++ {
		d = ds[dsIds[i]]
		out, err := qs.DescribeDataSetPermissions(context.TODO(),
			&quicksight.DescribeDataSetPermissionsInput{
				AwsAccountId: aws.String("384031817503"), // report-prod
				DataSetId:    aws.String(d.DataSetId),
			})
		if err != nil {
			log.Printf("skipping - failed to `DescribeDataSetPermissions`, %v", err)
			continue
		}
		for _, perm := range out.Permissions {
			if d.UsersWhoHaveAccess != "" {
				d.UsersWhoHaveAccess += ", "
			}
			p := strings.Split(*perm.Principal, "/")
			d.UsersWhoHaveAccess += p[len(p)-1]
		}
	}

	// print the top 25 SPICE capacity consuming datasets
	t := tablewriter.NewWriter(os.Stdout)
	t.SetRowLine(true)
	t.SetHeader([]string{"ID", "Name", "Spice Usage", "User(s) with access"})
	for i := 0; i < n; i++ {
		d = ds[dsIds[i]]
		t.Append([]string{d.DataSetId, d.Name, fmt.Sprintf("%.2f GB", d.SpiceUsage), d.UsersWhoHaveAccess})
	}
	t.Render()

	log.Println("done.")
}