Voiced by Amazon Polly |
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.
Pioneers in Cloud Consulting & Migration Services
- Reduced infrastructural costs
- Accelerated application deployment
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.
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
Materialized view table
- 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.
1 2 3 4 5 6 7 8 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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:
Conclusion
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
About CloudThat
CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.
CloudThat is the first Indian Company to win the prestigious Microsoft Partner 2024 Award and is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 850k+ professionals in 600+ cloud certifications and completed 500+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, Microsoft Gold Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, AWS GenAI Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner, AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, Amazon ECS Service Delivery Partner, AWS Glue Service Delivery Partner, Amazon Redshift Service Delivery Partner, AWS Control Tower Service Delivery Partner, AWS WAF Service Delivery Partner, Amazon CloudFront Service Delivery Partner, Amazon OpenSearch Service Delivery Partner, AWS DMS Service Delivery Partner, AWS Systems Manager Service Delivery Partner, Amazon RDS Service Delivery Partner, AWS CloudFormation Service Delivery Partner, AWS Config, Amazon EMR and many more.
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.
Comments