Understanding Redshift Dynamic SQL Simplified 101

Companies use Data Warehouses to store and analyze their business data to make data-driven business decisions. Querying through huge volumes of data and reach to a specific piece of data can be challenging if the queries are not optimized or data is not well organized.

Amazon Redshift is a fully managed Cloud Data Warehouse widely used by companies. It allows business users to store data in a unified format and run complex queries quickly. Sometimes it is convenient to run Amazon Redshift Dynamic SQL queries that make it easier for users to query data in an automated fashion.

Amazon Redshift Dynamic SQL use variables to execute queries in runtime which automates the process and saves time. In this article, you will learn about Amazon Redshift Dynamic SQL, how to use it and what are different ways to create Amazon Redshift Dynamic SQL with examples.

Prerequisites

• An active AWS account. AWS offers free tier services for new users for 12 months. You can get more details about the AWS free tier services here. If you’re new to AWS, then sign up.
• Advance knowledge of constructing and executing SQL queries.

What is Amazon Redshift?

Amazon Redshift is a Cloud-based serverless Data Warehouse provided by Amazon as a part of Amazon Web Services. It is a fully managed and cost-effective Data Warehouse solution. AWS Redshift is designed to store petabytes of data and perform real-time analysis to generate insights.

AWS Redshift is a column-oriented Database that stores the data in a columnar format compared to traditional Databases stored in a row format. Amazon Redshift has its own compute engine to perform computing and generate critical insights. 

Amazon Redshift Architecture

AWS Redshift has straightforward Architecture. It contains a leader node and cluster of compute nodes that perform analytics on data. The below snap depicts the schematics of AWS Redshift architecture: 

AWS Redshift offers JDBC connectors to interact with client applications using major programming languages like Python, Scala, Java, Ruby, etc.

Key Features of Amazon Redshift

• Amazon Redshift allows users to write queries and export the data back to Data Lake.
• Amazon Redshift can seamlessly query the files like CSV, Avro, Parquet, JSON, ORC directly with the help of ANSI SQL.
• Amazon Redshift has exceptional support for Machine Learning, and developers can create, train and deploy Amazon Sagemaker models using SQL.
• Amazon Redshift has an Advanced Query Accelerator (AQUA) which performs the query 10x faster than other cloud data warehouses.
• Redshift’s Materialistic view allows you to achieve faster query performance for ETL, batch job processing, and dashboarding.
• Amazon Redshift has a petabyte scalable architecture, and it scales quickly as per need.
• Amazon Redshift enables secure sharing of the data across Redshift clusters.
• Amazon Redshift provides consistently fast performance, even with thousands of concurrent queries.

To learn more about Amazon Redshift.

What is Amazon Redshift Dynamic SQL?

Amazon Redshift Dynamic SQL is a technique to build parameterized SQL queries that can attain the variable values at runtime. Amazon Redshift Dynamic SQL offers flexibility and multi-use of SQL where the key parameters on the SQL’s are variables and attain their value at compilation/runtime.

For example, consider an application that performs repetitive queries on the database, where the user inputs the table names. You construct an Amazon Redshift Dynamic SQL that takes the key parameter, i.e., table name, as an argument and can only be synthesized at runtime to accommodate this behavior.

Benefits of Using Amazon Redshift Dynamic SQL 

• Amazon Redshift Dynamic SQL allows users to parameterize the key parameters of the table.
• Amazon Redshift Dynamic SQL is more flexible and parameterized for usage.
• Users need to create one single SQL which can serve multiple purposes.

How to Create Amazon Redshift Dynamic SQL?

Now that we have a basic understanding of Redshift and Dynamic SQL, in this section, we will see how to construct Amazon Redshift Dynamic SQL. 

Amazon Redshift offers two ways to create Amazon Redshift Dynamic SQL, listed below: 

• Prepared Statement
• Stored Procedure

1) Prepared Statement

The PREPARE statement in Redshift is used to prepare a SQL statement for execution, and it supports SELECT, INSERT, UPDATE or DELETE statements. PREPARED statements take parameters whose values are substituted into the SQL statement when executed.

The PREPARED statement takes a list of datatype as the argument and refers to them via $1, $2,… The scope of the PREPARED statement is within the session. PREPARED statements are discarded automatically when the session ends. In the case of multiple sessions, you cannot use single PREPARED statements, and a separate PREPARED statement needs to be created for use.

The PREPARED statement has a high-performance advantage when used on a single session for executing a large number of similar statements.

Statements

PREPARED statements have three different statements as defined below – 

1. Prepare: This statement creates a PREPARED statement and follows the below syntax.

PREPARE plan_name [ (datatype [, ...] ) ] AS statement

2. Execute: This statement is to execute the above created PREPARED statement.

EXECUTE plan_name (value1, value2, ..)

3. Deallocate: This statement is to deallocate or destroy the prepared statement from the session.

DEALLOCATE plan_name

Syntax

PREPARE plan_name [ (datatype [, ...] ) ] AS statement

where, 

• plan_name: A name for the prepared statement by which it can be called. 
• datatype: The data type of a parameter to the prepared statement. To refer to the parameters in the prepared statement itself, use $1, $2, and so on.
• statement: Any SELECT, INSERT, UPDATE, or DELETE statement.

Example

1. An example of using an INSERT statement in a PREPARED statement.

CREATE TABLE emp (id int, name char(20));

PREPARE prep_insert_plan (int, char)
AS insert into emp values ($1, $2);

EXECUTE prep_insert_plan (1, 'Cam');
EXECUTE prep_insert_plan (2, 'Harry');
EXECUTE prep_insert_plan (3, 'Kris');

DEALLOCATE prep_insert_plan;

2. An example of using SELECT statement in a PREPARED statement.

PREPARE prep_select_plan (int) 
AS select * from emp where id = $1;

EXECUTE prep_select_plan (2);
EXECUTE prep_select_plan (3);

DEALLOCATE prep_select_plan;

2) Stored Procedure

Stored Procedures are mostly used to encapsulate the logic inside a procedure to provide the dynamic arguments to the query at runtime. The Stored Procedure allows users to encapsulate logic for data transformation, validation, business rules, and many more under one single Stored Procedure.

Stored Procedures can also be used to enable users to perform functions without giving access to actual functions users. For example, only the admin can truncate the table, and instead of granting a user permission to the table, they can create a Stored Procedure and give access to the users for that Procedure.

Syntax

The syntax for creating a Stored Procedure is as follows:

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name 
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
AS $$
  procedure_body
$$ LANGUAGE plpgsql 

where, 

• sp_procedure_name – The name of the procedure
• [argname] [ argmode] argtype – A list of argument names, argument modes, and data types.
• AS $$ procedure_body $$ – A construct that encloses the procedure to be run. 

Example

A simple example of a Stored Procedure is as follows:

CREATE PROCEDURE delete_record(id_value INOUT VARCHAR, table_name INOUT VARCHAR)
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
  EXECUTE 'DELETE FROM ' || table_name || ' WHERE id = ' || quote_literal(id_value);
END;
$$;

In the above example, the Stored Procedure delete_record takes table_name and id_value as a parameter that can be passed during the Stored Procedure call.

Calling Stored Procedure

Once the Stored Procedures are created, they can be called by using the CALL function.

Syntax

CALL sp_name ( [ argument ] [, ...] )

Example

CALL delete_record(5, emp);

Conclusion

In this article, you learnt about Amazon Redshift Dynamic SQL and how to create Amazon Redshift Dynamic SQL by using Prepared statements and Stored Procedures. You also went through a few examples to get a better understanding of Amazon Redshift Dynamic SQL queries. With the help of Amazon Redshift Dynamic SQL, users can automate and speed up the process. 

Visit our Website to Explore Hevo

Amazon Redshift stores data from multiple sources and every source follows a different schema. Instead of manually writing scripts for every source to perform the ETL (Extract Transform Load) process, one can automate the whole process. Hevo Data is a No-code Data pipeline solution that can help you transfer data from 150+ data sources to Amazon Redshift or other Data Warehouse of your choice. Its fault-tolerant and user-friendly architecture fully automates the process of loading and transforming data to destination without writing a single line of code. Learn how to execute dynamic SQL queries in Amazon Redshift for flexible and dynamic data retrieval.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about the Amazon Redshift Dynamic SQL in the comments section below!