Transforming Amazon Redshift Materialized Views into JSON with AWS Lambda

Overview

Materialized views in Amazon Redshift offer a powerful way to precompute and store aggregated or transformed data, enhancing query performance. However, there are scenarios where you might need to export this materialized view data in a different format, such as JSON. This blog shows how AWS Lambda makes turning complex Amazon Redshift data into simple JSON format easy. It’s like a smart assistant automating the process, making data more friendly, usable, and ready for web applications or APIs.

Introduction

Amazon Redshift is a powerful and scalable data warehousing service that allows you to analyze vast amounts of data. Materialized views in Amazon Redshift are precomputed tables that can greatly improve query performance by storing the results of complex queries. In this blog post, we will explore how to transform the data from Amazon Redshift materialized view into JSON format using AWS Lambda.

AWS Lambda is a serverless computing service that lets you run code without provisioning or managing servers. With AWS Lambda, you can automate extracting data from Amazon Redshift and transforming it into JSON, making it more accessible and easier to work with.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Advantages

Simplified Data Representation: JSON provides a simplified and flexible way to represent data. Transforming materialized views into JSON structures makes the data more readable and easily consumable.
Interoperability: JSON is a widely used data interchange format, facilitating seamless integration with various programming languages, applications, and web services. It enhances interoperability across different systems.
Web Compatibility: JSON is a common format for web applications. Transforming materialized views into JSON enables easy integration with web-based interfaces, supporting dynamic and interactive user experiences.
API Integration: JSON is a standard format for many APIs. Converting materialized views into JSON makes it straightforward to integrate the data into external applications, services, or third-party platforms via APIs.

Pre-requisites

Before you can transform Amazon Redshift Materialized Views into JSON, ensure you have the following pre-requisites in place:

You need an AWS account with a user having the required permissions.
Amazon Redshift Cluster: You should have an Amazon Redshift cluster set up and running within a private subnet. This cluster should already contain the required tables and materialized views you intend to query and transform into JSON.

Customer table

pre

Materialized view table

pre2

Amazon Redshift Cluster Access: Ensure you have access and credentials to connect to your Amazon Redshift cluster. You will need the Amazon Redshift cluster’s endpoint, port, username, and password.

Steps to Convert Amazon Redshift Materialized Views into JSON using AWS Lambda

Go to the AWS Lambda console.
Click “Create function” or select an existing function you want to place in the private subnet.
In the “Function configuration” section:

Set the runtime as Python 3.9 and other function settings as needed.
In the “Execution role” section, ensure your AWS Lambda function has the necessary permissions to access the resources within your VPC.
In the “Network” section, select the same VPC as Amazon Redshift, then select the private subnet you created.
Configure the security group you created in the “Advanced settings” section.

Next, Click on “Create function” to create the function.

Create a layer named psycopg2 and attach it to the AWS Lambda function.

mkdir layer
cd layer
pip install aws-psycopg2 -t .
mkdir -p python/lib/python3.9/site-packages
mv aws_psycopg2-1.3.8.dist-info python/lib/python3.9/site-packages
mv psycopg2 python/lib/python3.9/site-packages
mv psycopg2_binary.libs python/lib/python3.9/site-packages
zip -r layer.zip python

mkdir layer

cd layer

pip install aws-psycopg2 -t .

mkdir -p python/lib/python3.9/site-packages

mv aws_psycopg2-1.3.8.dist-info python/lib/python3.9/site-packages

mv psycopg2 python/lib/python3.9/site-packages

mv psycopg2_binary.libs python/lib/python3.9/site-packages

zip -r layer.zip python

Code snippet

Note: Replace REDSHIFT_ENDPOINT, REDSHIFT_PORT, REDSHIFT_USER, REDSHIFT_PASSWORD, REDSHIFT_PASSWORD REDSHIFT_PASSWORD with your database credentials

import boto3
import psycopg2
import json
import os

def lambda_handler(event, context):
    # Set up your Redshift connection parameters
    redshift_endpoint = REDSHIFT_ENDPOINT
    redshift_port = REDSHIFT_PORT    
    redshift_user = REDSHIFT_USER
    redshift_password = REDSHIFT_PASSWORD
    redshift_database = REDSHIFT_PASSWORD

    # Connect to Redshift  
    conn = psycopg2.connect(
        host=redshift_endpoint,
        port=redshift_port,
        user=redshift_user,
        password=redshift_password,
        database=redshift_database
    )

    # Query the materialized view
    cursor = conn.cursor()
    print(cursor)
    cursor.execute('SELECT * FROM view_customer')
    result = cursor.fetchall()
    print(result)
    
    cursor.close()
    columns = [desc[0] for desc in cursor.description]
    data = [dict(zip(columns, row)) for row in result]

    # Format the data as JSON
    json_data = json.loads(json.dumps(data))
    # Close the Redshift connection
    conn.close()
    return json_data

import boto3

import psycopg2

import json

import os

def lambda_handler(event, context):

# Set up your Redshift connection parameters

redshift_endpoint = REDSHIFT_ENDPOINT

redshift_port = REDSHIFT_PORT

redshift_user = REDSHIFT_USER

redshift_password = REDSHIFT_PASSWORD

redshift_database = REDSHIFT_PASSWORD

# Connect to Redshift

conn = psycopg2.connect(

host=redshift_endpoint,

port=redshift_port,

user=redshift_user,

password=redshift_password,

database=redshift_database

)

# Query the materialized view

cursor = conn.cursor()

print(cursor)

cursor.execute('SELECT * FROM view_customer')

result = cursor.fetchall()

print(result)

cursor.close()

columns = [desc[0] for desc in cursor.description]

data = [dict(zip(columns, row)) for row in result]

# Format the data as JSON

json_data = json.loads(json.dumps(data))

# Close the Redshift connection

conn.close()

return json_data

Output:

output

Conclusion

In summary, utilizing AWS Lambda to convert Amazon Redshift Materialized Views into JSON provides a scalable, integrated, serverless, and cost-efficient solution for automating data transformations and processing within your AWS environment. This approach streamlines the process, making working with data in the desired JSON format easier.

Drop a query if you have any questions regarding Amazon Redshift Materialized Views and we will get back to you quickly.

Making IT Networks Enterprise-ready – Cloud Management Services

Accelerated cloud migration
End-to-end view of the cloud environment

Get Started

About CloudThat

CloudThat is an official AWS (Amazon Web Services) Advanced Consulting Partner and Training partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner, and Microsoft Gold Partner, helping people develop knowledge of the cloud and help their businesses aim for higher goals using best-in-industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.

To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.

FAQs

1. What is the purpose of transforming Amazon Redshift materialized views into JSON?

ANS: – Transforming Amazon Redshift materialized views into JSON makes the data more accessible for various use cases by converting it into a widely used and easily workable data format, facilitating sharing, analysis, and processing.

2. Can I schedule this transformation process to run at specific intervals?

ANS: – Yes, you can schedule the transformation process with AWS Lambda using Amazon EventBridge to automate updates at specific intervals.

3. How can I secure the sensitive Amazon Redshift connection parameters in my AWS Lambda function?

ANS: – Secure Amazon Redshift connection parameters in your AWS Lambda function using AWS Secrets Manager for safe storage and access.

WRITTEN BY Anusha

Anusha works as Research Associate at CloudThat. She is an enthusiastic person about learning new technologies and her interest is inclined towards AWS and DataScience.