In the previous session, you got familiar with the Redshift console and its query editor. In this segment, you will learn how to implement complex custom queries in Redshift. Let’s watch the next video where our SME will explain this in detail.
The concepts that you learnt in this video are summarised below.
User-Defined Functions (UDFs)
- Redshift supports custom user-defined functions (scalar) using Python language or a single SELECT statement.
- Its ability to import custom Python modules makes it a useful feature.
- UDFs allow you to create reusable components for tasks, such as complex arithmetic calculations.
- Cannot use SQL queries within a UDF.
Stored procedures
- Redshift supports stored procedures in the PL/pgSQL format, including loops, conditionals, case statements and IN/OUT/INOUT argument passing.
- Stored procedures allow better ‘lift-and-shift’ from traditional data warehouses.
- Loops and conditional logic run on the leader node of an Amazon Redshift cluster, and any SQL within it is distributed to compute nodes.
Let’s learn how to create a User-Defined Function.
The reference for the UDF that was created by our SME is given below.
Additional Reading
- User-defined functions