Carry out safe database write-backs with Amazon QuickSight

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

A write-back is the power to replace an information mart, information warehouse, or another database backend from inside BI dashboards and analyze the up to date information in near-real time inside the dashboard itself. On this put up, we present find out how to carry out safe database write-backs with QuickSight.

Use case overview

To display find out how to allow a write-back functionality with QuickSight, let’s think about a fictional firm, AnyCompany Inc. AnyCompany is knowledgeable providers agency that makes a speciality of offering workforce options to their prospects. AnyCompany decided that operating workloads within the cloud to help its rising world enterprise wants is a aggressive benefit and makes use of the cloud to host all its workloads. AnyCompany determined to reinforce the best way its branches present quotes to its prospects. At present, the branches generate buyer quotes manually, and as a primary step on this innovation journey, AnyCompany is seeking to develop an enterprise resolution for buyer quote era with the aptitude to dynamically apply native pricing information on the time of quote era.

AnyCompany at the moment makes use of Amazon Redshift as their enterprise information warehouse platform and QuickSight as their BI resolution.

Constructing a brand new resolution comes with the next challenges:

• AnyCompany needs an answer that’s simple to construct and keep, and so they don’t wish to put money into constructing a separate consumer interface.
• AnyCompany needs to increase the capabilities of their current QuickSight BI dashboard to additionally allow quote era and quote acceptance. This may simplify characteristic 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 put up goes by way of the steps to allow write-back performance to Amazon Redshift from QuickSight. Word that the standard BI instruments are read-only with little to no choices to replace supply information.

Answer overview

This resolution makes use of the next AWS providers:

• Amazon API Gateway – Hosts and secures the write-back REST API that might 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 – Gives 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 resolution makes use of Amazon Redshift as the information retailer, an analogous method 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 resolution 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 (obtainable within the Python library hashlib). To additional safe the hash, keyed hashing is used, which is a sooner and less complicated different to hash-based message authentication code (HMAC). This secret’s generated and saved by Secrets and techniques Supervisor and ought to 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 cross requests to the write-back operate. The write-back operate performs the next actions:

1. Generate the hash based mostly 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. Evaluate the generated hash with the hash acquired from the enter parameters utilizing the compare_digest methodology obtainable within the HMAC module.
4. Upon profitable validation, write the report 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’s 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

2. 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's 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 https://docs.aws.amazon.com/secretsmanager/newest/apireference/API_GetSecretValue.html
    # We rethrow the exception by default.

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

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

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

SECRET_KEY = get_secret()
AUTH_SIZE = 16 

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

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

3. Outline an Amazon Redshift UDF to name the Lambda operate to create a hash:

CREATE OR REPLACE EXTERNAL FUNCTION udf_get_digest (par1 varchar)
RETURNS varchar STABLE
LAMBDA 'redshift_get_digest'
IAM_ROLE 'arn:aws:iam::<AWSACCOUNTID>position/service-role/<role_name>';

The AWS Identification and Entry Administration (IAM) position within the previous step ought to have the next coverage connected 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"
        }
}

4. 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.

2. Evaluate the hashed output with the hash parameter.

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

3. If the hashes match, carry out a write-back. Test for the presence of a report 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 + "'"

4. Full the next motion based mostly on the outcomes of the question:
   1. If no report exists for the previous mixture, generate and run an insert question utilizing all parameters with the standing as generated.
   2. If a report exists for the previous mixture, generate and run an insert question with the standing as in evaluation. The quote_Id for the prevailing mixture might be reused.

Create a QuickSight visible

This step entails making a desk visible that makes use of a calculated subject to cross 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 might be triggered to jot down again the quote historical past into Amazon Redshift:

concat("https://xxxxx.execute-api.us-east-1.amazonaws.com/stage_name/apiresourcename/?cust=",customername,"&bgc=",bgcheckrequired,"&billrate=",toString(billrate),"&skilledtrades=",skilledtrades,"&shift=",shift,"&jobdutydescription=",jobdutydescription,"&hash=",hashvalue)

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

Selecting this hyperlink will write the report 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’s fetched and utilized by the Lambda operate to generate the hash for comparability. The write-back might 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. Data in inexperienced characterize the latest data for the quote.

Issues 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 methods:

• Deploy the REST API in a personal VPC that’s accessible solely to QuickSight customers.
• To forestall 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 finest practices.
• Mitigate potential Denial of Service for public-facing APIs.

You possibly can additional improve this resolution to render a web-based type when the write-back URL is opened. This may very well be carried out by dynamically producing an HTML type within the backend Lambda operate to help the enter of further info. In case your workload requires a excessive variety of write-backs that require greater throughput or concurrency, a purpose-built information retailer like Amazon Aurora PostgreSQL-Appropriate Version is likely to be a better option. For extra info, 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.

Conclusion

This put up confirmed find out how to use QuickSight together with Lambda, API Gateway, Secrets and techniques Supervisor, and Amazon Redshift to seize consumer enter information and securely replace your Amazon Redshift information warehouse with out leaving your QuickSight BI setting. This resolution eliminates the necessity to create an exterior software or consumer interface for database replace or insert operations, and reduces associated improvement 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 might be coated in subsequent posts.

In regards to the Authors

Srikanth Baheti is a Specialised World Vast 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 answerable for designing and creating excessive 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 Knowledge Administration, Enterprise Intelligence and Analytics options for Fortune 500 and Fortune 100 corporations throughout the globe. She has in-depth expertise in built-in healthcare information and analytics with vast number of healthcare datasets together with managed market, doctor concentrating on and affected person analytics.