How can I retrieve data from AWS RDS and Redshift to use in AWS QuickSight BI dashboards in TypeScript

Question

Pulumi · Accepted Answer

To retrieve data from AWS RDS and Redshift to use in AWS QuickSight BI dashboards, you can use the AWS SDK for TypeScript/JavaScript to interact with these services. Below is a basic example that shows how to set up a QuickSight data source for RDS and Redshift, which can then be used to create datasets and integrate with QuickSight dashboards.

First, here's a summary of the steps you will need to perform:
1. **Retrieve Data Source**: Define and create a QuickSight data source that connects to your RDS instance and Redshift cluster.
2. **Create DataSet**: Use the data source(s) to create a QuickSight dataset.
3. **Create Analysis**: Create an analysis with the created dataset.
4. **Create Dashboard**: Share the analysis as a QuickSight dashboard.

Below is a TypeScript program that demonstrates how to create QuickSight data sources and a dashboard using Pulumi with the `aws` and `aws-native` providers.

Please note that the actual retrieval and transformation of data will be handled within QuickSight itself - the Pulumi program is responsible for setting up the infrastructure that allows QuickSight to access the data in RDS and Redshift.

```typescript
import * as aws from '@pulumi/aws';
import * as pulumi from '@pulumi/pulumi';

// Replace these with appropriate values for your environment
const awsAccountId = '123456789012';
const quicksightPrincipalArn = `arn:aws:quicksight:us-east-1:${awsAccountId}:user/default/USERNAME`;

// Assume that RDS and Redshift have already been set up and we have their connection details
// Here are placeholders for RDS and Redshift parameters.
const rdsParameters = { /* ... */ };
const redshiftParameters = { /* ... */ };

// Create a QuickSight data source for the RDS instance
const rdsDataSource = new aws.quicksight.DataSource('rdsDataSource', {
    awsAccountId: awsAccountId,
    name: 'RDSDataSource',
    type: 'RDS',
    dataSourceParameters: {
        rdsParameters: rdsParameters,
    },
    permissions: [
        {
            principal: quicksightPrincipalArn,
            actions: ["quicksight:DescribeDataSource", "quicksight:DescribeDataSourcePermissions", "quicksight:PassDataSource", "quicksight:UpdateDataSource", "quicksight:DeleteDataSource", "quicksight:UpdateDataSourcePermissions"],
        },
    ],
});

// Create a QuickSight data source for the Redshift cluster
const redshiftDataSource = new aws.quicksight.DataSource('redshiftDataSource', {
    awsAccountId: awsAccountId,
    name: 'RedshiftDataSource',
    type: 'REDSHIFT',
    dataSourceParameters: {
        redshiftParameters: redshiftParameters,
    },
    permissions: [
        {
            principal: quicksightPrincipalArn,
            actions: ["quicksight:DescribeDataSource", "quicksight:DescribeDataSourcePermissions", "quicksight:PassDataSource", "quicksight:UpdateDataSource", "quicksight:DeleteDataSource", "quicksight:UpdateDataSourcePermissions"],
        },
    ],
});

// In order to create a DataSet and a QuickSight dashboard, you would need to further define
// how the dataset can be retrieved from the dataSources, specify SPICE ingestion (if required),
// and also design the dashboard's visuals and layout in QuickSight itself.
// 
// Additional resources like aws.quicksight.Dataset, aws.quicksight.Analysis, and aws.quicksight.Dashboard
// could be used to programatically define these components if desired, but this requires an in-depth
// understanding of your specific data structures, business logic, and visualization requirements.

// To provision these resources, simply run `pulumi up` with the Pulumi CLI after setting the required configuration and stack.
```

In this program, we use the [`aws.quicksight.DataSource`](https://www.pulumi.com/registry/packages/aws/api-docs/quicksight/datasource/) resource in Pulumi to set up connections to both RDS and Redshift. We specify the `awsAccountId`, which is your AWS account ID, and `quicksightPrincipalArn`, which should be replaced with the actual ARN of the QuickSight principal who's allowed to access these data sources.

For `rdsDataSource` and `redshiftDataSource`, `dataSourceParameters` would be specific to your actual RDS and Redshift instances, including database names, hostnames, port numbers, and credentials. The `permissions` property grants the QuickSight principal permissions to perform actions on the data sources, such as describing, updating, or deleting them.

The subsequent steps to create datasets and dashboards generally happen within the AWS QuickSight UI, where you would build out your analyses and visualizations. Although you can do some of this programmatically through Pulumi using the `aws.quicksight.Dataset`, `aws.quicksight.Analysis`, and `aws.quicksight.Dashboard` resources, setting up the specifics of each analysis and dashboard widget typically involves a complex set of parameters that are often easier to configure through QuickSight’s interactive user interface.

Please notice that we’re assuming RDS and Redshift are already set up and properly configured, and that you've got the connection details ready to use. If you’re starting from scratch, you’ll need to set up the databases first before you can proceed with QuickSight data source creation. Also, you should handle your credentials securely by using AWS Secrets Manager or another secrets storage tool, rather than hardcoding sensitive information into your Pulumi program.

To deploy this infrastructure, you would need to install Pulumi and configure your AWS provider credentials. The program above is a basic scaffold and must be populated with actual values from your environment and extended to fully match your desired dashboard configuration.