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 hook up 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 software.

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

Use case overview

To display the way to allow a write-back functionality with QuickSight, let’s contemplate a fictional firm, AnyCompany Inc. AnyCompany is knowledgeable companies agency that focuses on offering workforce options to their prospects. AnyCompany decided that working workloads within the cloud to assist its rising international enterprise wants is a aggressive benefit and makes use of the cloud to host all its workloads. AnyCompany determined to reinforce the way in which 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 trying 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 presently 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, they usually 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. This can simplify characteristic rollouts as a result of their workers already use QuickSight dashboards and benefit from the easy-to-use interface that QuickSight offers.
• 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 means 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.

Resolution overview

This resolution makes use of the next AWS companies:

• 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 resolution makes use of Amazon Redshift as the information retailer, an analogous method might 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 (out there within the Python library hashlib). To additional safe the hash, keyed hashing is used, which is a sooner and easier various to hash-based message authentication code (HMAC). This secret is generated and saved by Secrets and techniques Supervisor and ought to be accessible solely to allowed functions. 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 technique out there within the HMAC module.
4. Upon profitable validation, write the file 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

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 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 consumer
    session = boto3.session.Session()
    consumer = session.consumer(service_name="secretsmanager", region_name=<aws_region_name>    )

    # On this pattern we solely deal with the particular 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 = consumer.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. Examine 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. Verify for the presence of a file 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 primarily based on the outcomes of the question:
   1. If no file exists for the previous mixture, generate and run an insert question utilizing all parameters with the standing as generated.
   2. If a file exists for the previous mixture, generate and run an insert question with the standing as in overview. The quote_Id for the prevailing mixture shall be reused.

Create a QuickSight visible

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

1. Add a QuickSight calculated discipline named Generate Quote to carry the API Gateway hosted URL that shall be triggered to write 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 reminiscent of Buyer, Talent, and Value.
4. Add the Generate Quote calculated discipline and magnificence this as a hyperlink.

Selecting this hyperlink will write the file 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 characterize the latest data for the quote.

Concerns and subsequent steps

Utilizing safe hashes prevents the tampering of payload parameters which can be seen within the browser window when the write-back URL is invoked. To additional safe the write-back URL, you’ll be able to 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 might 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 permit 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 may additional improve this resolution to render a web-based kind when the write-back URL is opened. This may very well be carried out by dynamically producing an HTML kind within the backend Lambda operate to assist the enter of extra info. In case your workload requires a excessive variety of write-backs that require larger throughput or concurrency, a purpose-built information retailer like Amazon Aurora PostgreSQL-Suitable Version could be a more sensible choice. For extra info, consult 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 publish confirmed the way 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 surroundings. This resolution eliminates the necessity to create an exterior software or person interface for database replace or insert operations, and reduces associated growth and upkeep overhead. The API Gateway name will also be secured utilizing a key or token to make sure solely calls originating from QuickSight are accepted by the API Gateway. This shall be lined 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 answerable for designing and creating excessive visitors net functions, extremely scalable and maintainable information pipelines for reporting platforms utilizing AWS companies 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 corporations throughout the globe. She has in-depth expertise in built-in healthcare information and analytics with large number of healthcare datasets together with managed market, doctor focusing on and affected person analytics.