Carry out safe database write-backs with Amazon QuickSight

Amazon QuickSight is a scalable, serverless, machine studying (ML)-powered enterprise intelligence (BI) answer that makes it straightforward to connect with your information, create interactive dashboards, get entry to ML-enabled insights, and share visuals and dashboards with tens of hundreds of inside and exterior customers, both inside QuickSight itself or embedded into any utility.

A write-back is the flexibility to replace a knowledge mart, information warehouse, or some other database backend from inside BI dashboards and analyze the up to date information in near-real time inside the dashboard itself. On this publish, we present methods to carry out safe database write-backs with QuickSight.

Use case overview

To reveal methods to allow a write-back functionality with QuickSight, let’s contemplate a fictional firm, AnyCompany Inc. AnyCompany is an expert providers agency that makes a speciality of offering workforce options to their clients. AnyCompany decided that operating workloads within the cloud to help its rising international enterprise wants is a aggressive benefit and makes use of the cloud to host all its workloads. AnyCompany determined to boost the way in which its branches present quotes to its clients. Presently, the branches generate buyer quotes manually, and as a primary step on this innovation journey, AnyCompany is seeking to develop an enterprise answer for buyer quote era with the potential to dynamically apply native pricing information on the time of quote era.

AnyCompany at present makes use of Amazon Redshift as their enterprise information warehouse platform and QuickSight as their BI answer.

Constructing a brand new answer comes with the next challenges:

  • AnyCompany needs an answer that’s straightforward to construct and preserve, and so they don’t wish to spend money on constructing a separate person interface.
  • AnyCompany needs to increase the capabilities of their current QuickSight BI dashboard to additionally allow quote era and quote acceptance. It will simplify function rollouts as a result of their staff already use QuickSight dashboards and benefit from the easy-to-use interface that QuickSight supplies.
  • AnyCompany needs to retailer the quote negotiation historical past that features generated, reviewed, and accepted quotes.
  • AnyCompany needs to construct a brand new dashboard with quote historical past information for evaluation and enterprise insights.

This publish goes by the steps to allow write-back performance to Amazon Redshift from QuickSight. Be aware that the standard BI instruments are read-only with little to no choices to replace supply information.

Resolution overview

This answer makes use of the next AWS providers:

  • Amazon API Gateway – Hosts and secures the write-back REST API that shall be invoked by QuickSight
  • AWS Lambda – Runs the compute operate required to generate the hash and a second operate to securely carry out the write-back
  • Amazon QuickSight – Provides BI dashboards and quote era capabilities
  • Amazon Redshift – Shops quotes, costs, and different related datasets
  • AWS Secrets and techniques Supervisor – Shops and manages keys to signal hashes (message digest)

Though this answer makes use of Amazon Redshift as the information retailer, the same strategy could be carried out with any database that helps creating user-defined features (UDFs) that may invoke Lambda.

The next determine exhibits the workflow to carry out write-backs from QuickSight.

Step one within the answer is to generate a hash or a message digest of the set of attributes in Amazon Redshift by invoking a Lambda operate. This step prevents request tampering. To generate a hash, Amazon Redshift invokes a scalar Lambda UDF. The hashing mechanism used right here is the favored BLAKE2 operate (accessible within the Python library hashlib). To additional safe the hash, keyed hashing is used, which is a quicker and less complicated different to hash-based message authentication code (HMAC). This secret is generated and saved by Secrets and techniques Supervisor and must be accessible solely to allowed purposes. After the safe hash is generated, it’s returned to Amazon Redshift and mixed in an Amazon Redshift view.

Writing the generated quote again to Amazon Redshift is carried out by the write-back Lambda operate, and an API Gateway REST API endpoint is created to safe and move requests to the write-back operate. The write-back operate performs the next actions:

  1. Generate the hash primarily based on the API enter parameters acquired from QuickSight.
  2. Signal the hash by making use of the important thing from Secrets and techniques Supervisor.
  3. Examine the generated hash with the hash acquired from the enter parameters utilizing the compare_digest methodology accessible within the HMAC module.
  4. Upon profitable validation, write the document to the quote submission desk in Amazon Redshift.

The next part present detailed steps with pattern payloads and code snippets.

Generate the hash

The hash is generated utilizing a Lambda UDF in Amazon Redshift. Moreover, a Secrets and techniques Supervisor secret is used to signal the hash. To create the hash, full the next steps:

  1. Create the Secrets and techniques Supervisor key from the AWS Command Line Interface (AWS CLI):
aws secretsmanager create-secret --name “name_of_secret” --description "Secret key to signal hash" --secret-string '{" name_of_key ":"worth"}' --region us-east-1

  1. Create a Lambda UDF to generate a hash for encryption:
import boto3	
import base64
import json
from hashlib import blake2b
from botocore.exceptions import ClientError

def get_secret(): 	#This secret is utilized by the Lambda operate to additional safe the hash.

    secret_name = "<name_of_secret>"
    region_name = "<aws_region_name>"

    # Create a Secrets and techniques Supervisor shopper
    session = boto3.session.Session()
    shopper = session.shopper(service_name="secretsmanager", region_name=<aws_region_name>    )

    # On this pattern we solely deal with the precise exceptions for the 'GetSecretValue' API.
    # See 
    # We rethrow the exception by default.

        get_secret_value_response = shopper.get_secret_value(SecretId=secret_name)
    besides Exception as e:
            increase e

   if "SecretString" in get_secret_value_response:
       access_token = get_secret_value_response["SecretString"]
       access_token = get_secret_value_response["SecretBinary"]

   return json.hundreds(access_token)[<token key name>]

SECRET_KEY = get_secret()

def signal(payload):
    h = blake2b(digest_size=AUTH_SIZE, key=SECRET_KEY)
    return h.hexdigest().encode('utf-8')

def lambda_handler(occasion, context):
ret = dict()
  res = []
  for argument in occasion['arguments']:
     msg = json.dumps(argument)
     signed_key = signal(str.encode(msg))
   ret['success'] = True
   ret['results'] = res
besides Exception as e:
  ret['success'] = False
  ret['error_msg'] = str(e)
 return json.dumps(ret)

  1. Outline an Amazon Redshift UDF to name the Lambda operate to create a hash:
CREATE OR REPLACE EXTERNAL FUNCTION udf_get_digest (par1 varchar)
LAMBDA 'redshift_get_digest'
IAM_ROLE 'arn:aws:iam::<AWSACCOUNTID>function/service-role/<role_name>';

The AWS Id and Entry Administration (IAM) function within the previous step ought to have the next coverage hooked up to have the ability to invoke the Lambda operate:

    "Model": "2012-10-17",
    "Assertion": [
            "Effect": "Allow",
            "Action": "lambda:InvokeFunction",
            "Resource": "arn:aws:lambda:us-east-1:<AWSACCOUNTID>1:function:redshift_get_digest"

  1. Fetch the key from Secrets Manager.

This key is used by the Lambda function to further secure the hash. This is indicated in the get_secret function in Step 2.

Set up Amazon Redshift datasets in QuickSight

The quote generation dashboard uses the following Amazon Redshift view.

Create an Amazon Redshift view that uses all the preceding columns along with the hash column:

create view quote_gen_vw as select *, udf_get_digest 
( customername || BGCheckRequired || Skill|| Shift ||State ||Cost ) from billing_input_tbl

The records will look like the following screenshot.

The preceding view will be used as the QuickSight dataset to generate quotes. A QuickSight analysis will be created using the dataset. For near-real-time analysis, you can use QuickSight direct query mode.

Create API Gateway resources

The write-back operation is initiated by QuickSight invoking an API Gateway resource, which invokes the Lambda write-back function. As a prerequisite for creating the calculated field in QuickSight to call the write-back API, you must first create these resources.

API Gateway secures and invokes the write-back Lambda function with the parameters created as URL query string parameters with mapping templates. The mapping parameters can be avoided by using the Lambda proxy integration.

Create a REST API resource of method type GET that uses Lambda functions (created in the next step) as the integration type. For instructions, refer to Creating a REST API in Amazon API Gateway and Set up Lambda integrations in API Gateway.

The following screenshot shows the details for creating a query string parameter for each parameter passed to API Gateway.

The following screenshot shows the details for creating a mapping template parameter for each parameter passed to API Gateway.

Create the Lambda function

Create a new Lambda function for the API Gateway to invoke. The Lambda function performs the following steps:

  1. Receive parameters from QuickSight through API Gateway and hash the concatenated parameters.

The following code example retrieves parameters from the API Gateway call using the event object of the Lambda function:

   customer= event['customer’])
    bgc = occasion['bgc']

The operate performs the hashing logic as proven within the create hash step earlier utilizing the concatenated parameters handed by QuickSight.

  1. Examine the hashed output with the hash parameter.

If these don’t match, the write-back received’t occur.

  1. If the hashes match, carry out a write-back. Verify for the presence of a document within the quote era desk by producing a question from the desk utilizing the parameters handed from QuickSight:
query_str = "choose * From tbquote the place cust="" + cust + "" and bgc="" + bgc +""" +" and skilledtrades="" + skilledtrades + ""  and shift="" +shift + "" and jobdutydescription ='" + jobdutydescription + "'"

  1. Full the next motion primarily based on the outcomes of the question:
    1. If no document exists for the previous mixture, generate and run an insert question utilizing all parameters with the standing as generated.
    2. If a document exists for the previous mixture, generate and run an insert question with the standing as in overview. The quote_Id for the present mixture shall be reused.

Create a QuickSight visible

This step includes making a desk visible that makes use of a calculated subject to move parameters to API Gateway and invoke the previous Lambda operate.

  1. Add a QuickSight calculated subject named Generate Quote to carry the API Gateway hosted URL that shall be triggered to put in writing again the quote historical past into Amazon Redshift:

  1. Create a QuickSight desk visible.
  2. Add required fields comparable to Buyer, Ability, and Value.
  3. Add the Generate Quote calculated subject and elegance this as a hyperlink.

Selecting this hyperlink will write the document into Amazon Redshift. That is incumbent on the identical hash worth returning when the Lambda operate performs the hash on the parameters.

The next screenshot exhibits a pattern desk visible.

Write to the Amazon Redshift database

The Secrets and techniques Supervisor secret is fetched and utilized by the Lambda operate to generate the hash for comparability. The write-back shall be carried out provided that the hash matches with the hash handed within the parameter.

The next Amazon Redshift desk will seize the quote historical past as populated by the Lambda operate. Information in inexperienced signify the latest data for the quote.

Concerns and subsequent steps

Utilizing safe hashes prevents the tampering of payload parameters which are seen within the browser window when the write-back URL is invoked. To additional safe the write-back URL, you may make use of the next strategies:

  • Deploy the REST API in a personal VPC that’s accessible solely to QuickSight customers.
  • To stop replay assaults, a timestamp could be generated alongside the hashing operate and handed as an extra parameter within the write-back URL. The backend Lambda operate can then be modified to solely enable write-backs inside a sure time-based threshold.
  • Comply with the API Gateway entry management and safety greatest practices.
  • Mitigate potential Denial of Service for public-facing APIs.

You possibly can additional improve this answer to render a web-based type when the write-back URL is opened. This might be carried out by dynamically producing an HTML type within the backend Lambda operate to help the enter of extra data. In case your workload requires a excessive variety of write-backs that require increased throughput or concurrency, a purpose-built information retailer like Amazon Aurora PostgreSQL-Appropriate Version may be a more sensible choice. For extra data, discuss with Invoking an AWS Lambda operate from an Aurora PostgreSQL DB cluster. These updates can then be synchronized into Amazon Redshift tables utilizing federated queries.


This publish confirmed methods to use QuickSight together with Lambda, API Gateway, Secrets and techniques Supervisor, and Amazon Redshift to seize person enter information and securely replace your Amazon Redshift information warehouse with out leaving your QuickSight BI atmosphere. This answer eliminates the necessity to create an exterior utility or person interface for database replace or insert operations, and reduces associated growth and upkeep overhead. The API Gateway name can be secured utilizing a key or token to make sure solely calls originating from QuickSight are accepted by the API Gateway. This shall be coated in subsequent posts.

In regards to the Authors

Srikanth Baheti is a Specialised World Huge Principal Options Architect for Amazon QuickSight. He began his profession as a guide and labored for a number of personal and authorities organizations. Later he labored for PerkinElmer Well being and Sciences & eResearch Know-how Inc, the place he was liable for designing and creating excessive site visitors internet purposes, extremely scalable and maintainable information pipelines for reporting platforms utilizing AWS providers and Serverless computing.

Raji Sivasubramaniam is a Sr. Options Architect at AWS, specializing in Analytics. Raji is specialised in architecting end-to-end Enterprise Information Administration, Enterprise Intelligence and Analytics options for Fortune 500 and Fortune 100 firms throughout the globe. She has in-depth expertise in built-in healthcare information and analytics with extensive number of healthcare datasets together with managed market, doctor concentrating on and affected person analytics.

Related Articles


Please enter your comment!
Please enter your name here

Latest Articles