Amazon QuickSight Connect to TiDB Cloud via AWS PrivateLink

This document will show you how to integrate the TiDB Cloud Serverless Tier Cluster and Amazon QuickSight via AWS PrivateLink. In this document, we need to use AWS CloudFormation and AWS CLI to simplify progress.

Overview

Note:

This is a simple overview. It only has the highest hierarchy component.

The outline progress is:

1. Run the first script 1-create-aws-stack.sh. Create the VPC (with the surrounding components), EC2 Interface, VPC Endpoint, etc.
2. Run the secound script 2-create-quicksight-stack.sh to create the QuickSight components.
3. Start analysis on Amazon QuickSight.
4. (Optional) Run the 3-clean.sh script to clean the environment.

Prerequisites

Before you can use this project, you will need the following:

• Git

• AWS CLI version 2

• A TiDB Cloud Account

• An AWS Identity and Access Management (IAM) user with the following requirements:

  • The user can access AWS using an access key.

  • The user has the following permissions:

    • AWSCloudFormationFullAccess: This guide uses the AWS CloudFormation to create AWS resources.

    • AmazonEC2FullAccess: To create the VPC, Subnet, VPCEndpoint, InternetGateway, Route, RouteTable, SecurityGroup, EC2Instance, etc.

    • AmazonRoute53ResolverFullAccess: To create Route53Resolver.

    • The Amazon QuickSight doesn't have an AWS managed policy. So you need to create a customer inline policy, and add this permission to this user:

      {
        "Version": "2012-10-17",
        "Statement": [
          {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "quicksight:*",
            "Resource": "*"
          }
        ]
      }

• An Amazon QuickSight Account of Enterprise Edition. The VPC feature is for Enterprise Edition only.

Before you begin

Note:

Keep the same AWS region to your TiDB Cloud Serverless Tier Cluster, Amazon QuickSight, and your AWS CLI default region.

• Create a TiDB Cloud account and get your free trial cluster(Serverless Tier).

• (Optional) If you never use the AWS CLI before, config it by:

  aws configure

• Import a dataset for analysis. In this document, use Fitness Trackers Products E-commerce for example. You can use fitness_trackers.sql to create the table in TiDB.

Get Your Private Endpoint Information

TiDB Cloud Private Endpoint will be created in Serverless Tier cluster automatically. So you can get the Endpoint ServiceName and Availability Zone(AZ) to input to the secret.json.

Note:

TiDB Cloud Serverless Tier will offer to you the AZ format like usw2-az1. This is the ID of AZ. And the name of AZ will different between all of users.

So you can use the aws ec2 describe-availability-zones --region <region name> to get information of AZs. The output will like:

{
   "AvailabilityZones": [
       {
           "State": "available",
           "OptInStatus": "opt-in-not-required",
           "Messages": [],
           "RegionName": "us-west-2",
           "ZoneName": "us-west-2a",
           "ZoneId": "usw2-az2",
           "GroupName": "us-west-2",
           "NetworkBorderGroup": "us-west-2",
           "ZoneType": "availability-zone"
       }
       ...
     ]
 }

And then, you can get the correspodent relations of zone ID and zone name.

1. Fill Parameters

Note:

If you don't know how to get the params, you can read those documents to get more information:

• TiDB Cloud - Connect via Private Endpoint
• TiDB
• Amazon QuickSight

1. Rename the secret.template.json to secret.json.
2. Replace those parameters:

• TiDBVPCEndpointServiceName: TiDB Cloud VPC endpoint service name.

• TiDBServerlessAvailabilityZone: The Availability Zone(AZ) of TiDB Serverless Tier Cluster.

• AnotherAvailabilityZone: This is a quirky way to work around the restriction of Amazon QuickSight VPC Connection. Please input an AZ that is not the same as TiDBServerlessAvailabilityZone.

• QuickSightRoleArn: The AWS role you want to create resources.

• QuickSightUser: The username of QuickSight.

  

• TiDBUser: TiDB username

• TiDBPassword: TiDB password

• TiDBDatabase: TiDB database

• TiDBHost: TiDB PrivateLink host

• TiDBPort: TiDB port

1. Create the VPC (With all components) / PrivateLink / EC2 / Amazon QuickSight VPC Connection / AWS Lambda

1. Run the script: 1-create-aws-stack.sh.

./1-create-aws-stack.sh

2. Waiting for the Status of Amazon QuickSight VPC Connection is AVAILABLE.

Note:

If you want to know the actual effect of each resource, please click the component links to get more information:

• AWS::EC2::VPC
• AWS::EC2::Subnet
• AWS::EC2::RouteTable
• AWS::EC2::Route
• AWS::EC2::SubnetRouteTableAssociation
• AWS::EC2::SecurityGroup
• AWS::Route53Resolver::ResolverEndpoint
• AWS::EC2::VPCEndpoint
• AWS::EC2::Instance
• AWS::EC2::InternetGateway
• AWS::EC2::VPCGatewayAttachment

2. Create QuickSight Dataset

This step will create an Amazon QuickSight datasource via VPC connection to TiDB. And use this datasource to create a bookstore dataset which is the table book we imported.

3. Run the script: 2-create-quicksight-stack.sh.

./2-create-quicksight-stack.sh

4. Start analysis on Amazon QuickSight

5. (Optional) Clean

• Run the clean script:

  ./3-clean.sh

• The Amazon QuickSight VPC Connection recycles maybe delayed.

Noteworthy Things

• If anything goes wrong in AWS CloudFormation. You can see the page of CloudFormation Stack to get more information.