Chapter 2: Data Management
Amazon Redshift is a data warehousing service optimized for online analytical processing (OLAP) applications. You can start with just a few hundred gigabytes (GB) of data and scale to a petabyte (PB) or more. Designing your database for analytical processing lets you take full advantage of Amazon Redshift's columnar architecture.
An analytical schema forms the foundation of your data model. This chapter explores how you can set up this schema, thus enabling convenient querying using standard Structured Query Language (SQL) and easy administration of access controls.
The following recipes are discussed in this chapter:
- Managing a database in an Amazon Redshift cluster
- Managing a schema in a database
- Managing tables
- Managing views
- Managing materialized views
- Managing stored procedures
- Managing user-defined functions (UDFs)
Managing a database in an Amazon Redshift cluster
Amazon Redshift consists of at least one database, and it is the highest level in the namespace hierarchy for the objects in the cluster. This recipe will guide you through the steps needed to create and manage a database in Amazon Redshift.
Getting ready
To complete this recipe, you will need the following:
- Access to any SQL interface such as a SQL client or query editor
- An Amazon Redshift cluster endpoint
How to do it…
Let's now set up and configure a database on the Amazon Redshift cluster. Use the SQL client to connect to the cluster and execute the following commands:
- We will create a new database called
qa
in the Amazon Redshift cluster. To do this, use the following code:CREATE DATABASE qa WITH OWNER awsuser CONNECTION LIMIT 50;
- To view the details of the database, you will query the
PG_DATABASE_INFO
, as shown in the following code snippet:SELECT datname, datdba, datconnlimit FROM pg_database_info WHERE datdba > 1;
This is the expected output:
datname datdba datconnlimit qa 100 UNLIMITED
This query will list the databases that exist in the cluster. If a database is successfully created, it will show up in the query result.
- To make changes to the database—such as database name, owner, and connection limit—use the following command, replacing
<qauser>
with the respective Amazon Redshift username:/* Change database owner */ ALTER DATABASE qa owner to <qauser>; /* Change database connection limit */ ALTER DATABASE qa CONNECTION LIMIT 100; /* Change database name */ ALTER DATABASE qa RENAME TO prod;
- To verify that the changes have been successfully completed, you will query the system table
pg_database_info
, as shown in the following code snippet, to list all the databases in the cluster:SELECT datname, datdba, datconnlimit FROM pg_database_info WHERE datdba > 1;
This is the expected output:
datname datdba datconnlimit prod 100 100
- You can connect to the
prod
database using the connection endpoint, as follows:<RedshiftClusterHostname>:<Port>/prod
Here,
prod
refers to the database you would like to connect to. - To delete the previously created database, execute the following query:
DROP DATABASE prod;
Important note
It is best practice to have only one database in production per Amazon Redshift cluster. Multiple databases could be created in a development environment to enable separation of functions such a development/unit testing/quality assurance (QA). Within the same session, it is not possible to access objects across multiple databases, even though they are present in the same cluster. The only exception to this rule is database users and groups that are available across the databases.
Managing a schema in a database
In Amazon Redshift, a schema is a namespace that groups database objects such as tables, views, stored procedures, and so on. Organizing database objects in a schema is good for security monitoring and also logically groups the objects within a cluster. In this recipe, we will create a sample schema that will be used to hold all the database objects.
Getting ready
To complete this recipe, you will need access to any SQL interface such as a SQL client or query editor.
How to do it…
- Users can create a schema using the
CREATE SCHEMA
command. The following steps will enable you to set up a schema with the namefinance
and add the necessary access to the groups. - Create
finance_grp
,audit_grp
, andfinance_admin_user
groups using the following command:create group finance_grp; create group audit_grp; create user finance_admin_usr with password '<PasswordOfYourChoice>';
- Create a schema named
finance
with a space quota of 2 terabytes (TB), with afinance_admin_usr
schema owner:CREATE schema finance authorization finance_admin_usr QUOTA 2 TB;
You can also modify an existing schema using
ALTER SCHEMA
orDROP SCHEMA
. - For the
finance
schema, grant access privileges ofUSAGE
andALL
to thefinance_grp
group. Further, grant read access to the tables in the schema using aSELECT
privilege for theaudit_grp
group:GRANT USAGE on SCHEMA finance TO GROUP finance_grp; GRANT USAGE on SCHEMA finance TO GROUP audit_grp; GRANT ALL ON schema finance to GROUP finance_grp; GRANT SELECT ON ALL TABLES IN SCHEMA finance TO GROUP audit_grp;
- You can verify that the schema and owner group have been created by using the following code:
select nspname as schema, usename as owner from pg_namespace, pg_user where pg_namespace.nspowner = pg_user.usesysid and pg_namespace.nspname ='finance';
- Create a
foo
table (orview/database object
) within the schema by prefixing the schema name along with the table name, as shown in the following command:CREATE TABLE finance.foo (bar int);
- Now, in order to select the
foo
table from thefinance
schema, you will have to prefix the schema name along with the table name, as shown in the following command:select * from finance.foo;
The preceding SQL code will not return any rows.
- Assign a search path to conveniently reference the database objects directly, without requiring the complete namespace of the schema qualifier. The following command sets the search path as
finance
so that you don't need to qualify the schema name every time when working with database objects:set search_path to '$user', finance, public;
Important note
The search path allows a convenient way to access the database objects without having to specify the target schema in the namespace when authoring the SQL code. The search path can be configured using the
search_path
parameter with a comma-separated list of schema names. When referencing the database object in a SQL when no target schema is provided, the database object that is in the first available schema list is picked up. You can configure the search path by using theSET search_path
command at the current session level or at the user level. - Now, executing the following
SELECT
query without the schema qualifier automatically locates thefoo
table in thefinance
schema:select * from foo;
The preceding SQL code will not return any rows.
Now, the new finance
schema is ready for use and you can keep creating new database objects in this schema.
Important note
A database is automatically created by default with a PUBLIC
schema. Identical database object names can be used in different schemas of the database. For example, finance.customer
and marketing.customer
are valid table definitions that can be created without any conflict, where finance
and marketing
are schema names and customer
is the table name. Schemas serve the key purpose of easy management through this logical grouping—for example, you can grant SELECT
access to all the objects at a schema level instead of individual tables.
Managing tables
In Amazon Redshift, you can create a collection of tables within a schema with related entities and attributes. Working backward from your business requirements, you can use different modeling techniques to create tables in Amazon Redshift. You can choose a star or snowflake schema by using Normalized, Denormalized, or Data Vault data modeling techniques.
In this recipe, we will create tables in the finance
schema, insert data into those tables and cover the key concepts to leverage the massively parallel processing (MPP) and columnar architecture.
Getting ready
To complete this recipe you will need a SQL client, or you can use the Amazon Redshift query editor.
How to do it…
Let's explore how to create tables in Amazon Redshift.
- Let's create a
customer
table in thefinance
schema withcustomer_number
,first_name
,last_name
, anddate_of_birth
related attributes:CREATE TABLE finance.customer ( customer_number INTEGER, first_name VARCHAR(50), last_name VARCHAR(50), date_of_birth DATE );
Note
The key ingredient when creating a customer table is to define columns and their corresponding data types. Amazon Redshift supports data types such as numeric, character, date, datetime with time zone, boolean, geometry, HyperLogLog, and super.
- We will now insert 10 records into the customer table using a multi-value
insert
statement:insert into finance.customer values (1, 'foo', 'bar', '1980-01-01'), (2, 'john', 'smith', '1990-12-01'), (3, 'spock', 'spock', '1970-12-01'), (4, 'scotty', 'scotty', '1975-02-01'), (5, 'seven', 'of nine', '1990-04-01'), (6, 'kathryn', 'janeway', '1995-07-01'), (7, 'tuvok', 'tuvok', '1960-06-10'), (8, 'john', 'smith', '1965-12-01'), (9, 'The Doctor', 'The Doctor', '1979-12-01'), (10, 'B Elana', 'Torres', '2000-08-01');
- You can now review the information on the customer table using the
svv_table_info
system view. Execute the following query:select "schema", table_id, "table", encoded, diststyle, sortkey1, size, tbl_rows from svv_Table_info where "table" = 'customer' and "schema" = 'finance';
This is the expected output:
schema table_id table encoded diststyle sortkey1 size tbl_rows finance 167482 customer Y AUTO(ALL) AUTO(SORTKEY) 14 10
Table_id
is the object ID and the number of records in the table is 10 rows. The encoded column indicates the table is compressed. Amazon Redshift stores columns in 1 megabyte (MB) immutable blocks. The size of the table is 14 MB. Let's dive into the terminology and concept ofdiststyle
andsortkey
. The customer table is created with default sort key ofAUTO
, where Amazon Redshift handles the distribution style of the table on the computer nodes.diststyle
is a table property that dictates how that table's data is distributed throughout the cluster.KEY
: The value is hashed, and the same value goes to same location (slice) on the compute node.ALL
: The full table data goes to the first slice of every compute node.EVEN
: Round-robin across all the compute nodes.AUTO
: When the table is small, it starts with anAUTO
style, and when it becomes larger in size, Amazon Redshift converts it to anEVEN
style.
Further information about distribution styles can be found at the following link:
https://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html
- Let's run a query against the
customer
table to list customers who were born before1980
:select * from finance.customer where extract(year from date_of_birth) < 1980;
- You can also create a copy of the permanent table using create table as (CTAS). Let's execute the following query to create another table for a customer born in
1980
:create table finance.customer_dob_1980 as select * from finance.customer where extract(year from date_of_birth) = 1980 ;
- You can also create temporary tables—for example, to generate IDs in a data loading operation. The temporary tables can only be queried during the current session and are automatically dropped when the session ends. The temporary tables are created in the session-specific schema and are not visible to any other user. You can use a
create temporary table
command to do this. Execute the following three queries in single session:create temporary table #customer(custid integer IDENTITY(1,1), customer_number integer); insert into #customer (customer_number) values(1); select * from #customer;
This is the expected output:
custid customer_number 1 1
- Reconnect to the Amazon Redshift cluster using the SQL client. Reconnecting will create a new session. Now, try to execute the following query against the
#customer
temporary table. You will get an ERROR: 42P01: relation "#customer" does not exist error message as the temporary tables are only visible to the current session:select * from #customer;
How it works…
When you create a table in Amazon Redshift, it stores the data on disk, column by column, on 1 MB blocks. Amazon Redshift by default compresses the columns, which reduces the storage footprint and the input/output (I/O) when you execute a query against the table. Amazon Redshift provides different distribution styles to spread the data across all the compute nodes, to leverage the MPP architecture for your workload. The metadata and the table summary information can be queried using the catalog table and summary view.
Amazon Redshift stores metadata about the customer table. You can query the pg_table_def
catalog table to retrieve this information. You can execute the following query to view the table/column structure:
select * from pg_table_def where schemaname = 'finance';.
Important note
When data is inserted into a table, Amazon Redshift automatically builds, in memory, the metadata of the minimum and maximum values of each block. This metadata, known as a zone map, is accessed before a disk scan in order to identify which blocks are relevant to a query. Amazon Redshift does not have indexes; it does, however, have sort keys. Sort key columns govern how data is physically sorted for a table on disk and can be used as a lever to improve query performance. Sort keys will be covered in depth in the performance-tuning best practices chapter.
Managing views
View database objects allow the result of a query to be stored. In Amazon Redshift, views run each time a view is mentioned in a query. The advantage of using a view instead of a table is that it can allow access to only a subset of data on a table, join more than one table in a single virtual table, and act as an aggregated table, and it takes up no space on the database since only the definition is saved, hence making it convenient to abstract complicated queries. In this recipe, we will create views to store queries for the underlying tables.
Getting ready
To complete this recipe, you will need access to any SQL interface such as a SQL client or query editor.
How to do it…
Let's create a view using the CREATE VIEW
command. We will use the following steps to create a view:
- Create a
finance.customer_vw
view based on the results of the query onfinance.customer
:CREATE VIEW finance.customer_vw AS SELECT customer_number, first_name, last_name, EXTRACT(year FROM date_of_birth) AS year_of_birth FROM finance.customer;
- To verify that a view has been created, you can use the following command:
SELECT table_schema as schema_name, table_name as view_name, view_definition FROM information_schema.views WHERE table_schema not in ('information_schema', 'pg_catalog') ORDER by schema_name, view_name;
Note
This script will provide an output of the views created under a particular schema and the SQL script for the view.
- We can now select directly from the
finance.customer_vw
view, just like with any another database object, like so:SELECT * from finance.customer_vw limit 5;
Note
Here, the
finance.customer_vw
view abstracts thedate_of_birth
personally identifiable information (PII) from the underlying table and provides the user an abstracted view of only the essential data for that year to determine the age group.This is the expected output:
outputcustomer_number,first_name,last_name,year_of_birth 1 foo bar 1980 2 john smith 1990 3 spock spock 1970 4 scotty scotty 1975 5 seven of nine 1990
- To delete the previously created view, you can use the following command:
DROP VIEW finance.customer_vw ;
Managing materialized views
A materialized view is a database object that persists the results of a query to disk. In Amazon Redshift, materialized views allow frequently used complex queries to be stored as separate database objects, allowing you to access these database objects directly, and enabling faster query responses.
Employing materialized views is a common approach to powering repeatable queries in a business intelligence (BI) dashboard, and avoids expensive computation each time. Furthermore, materialized views allow an incremental refresh of the results, using the underlying table data. In this recipe, we will create a materialized view to query the tables and also to persist the results to fetch the data more quickly.
Getting ready
To complete this recipe, you will need access to any SQL interface such as a SQL client or a query editor.
How to do it…
Let's create a materialized view using the CREATE MATERIALIZED VIEW
command. We will use the following steps to create a materialized view, in order to store the precomputed results of an analytical query and also see how to refresh it:
- Create a
finance.customer_agg_mv
materialized view using the results of the query based onfinance.customer
:CREATE MATERIALIZED VIEW finance.customer_agg_mv AS SELECT EXTRACT(year FROM date_of_birth) AS year_of_birth, count(1) customer_cnt FROM finance.customer group by EXTRACT(year FROM date_of_birth);
- We can now select directly from
finance.customer
, just like with any another database object, like so:select * from finance.customer limit 5;
This is the expected output:
outputyear_of_birth,customer_cnt 1975 1 1979 1 1995 1 1970 1 1965 1
- You can verify the state of a materialized view by using a
STV_MV_INFO
system table (https://docs.aws.amazon.com/redshift/latest/dg/r_STV_MV_INFO.html):select * from STV_MV_INFO where name='customer_agg_mv';
This is the expected output:
outputdb_name,schema,name,updated_upto_xid,is_stale,owner_user_name,state,autorefresh, autorewrite vdwpoc finance customer_agg_mv 24642401 f vdwadmin 1 f t
Here,
stale='f'
indicates the data is current, reflecting thedaily_product_reviews
underlying base table. This column can be used to refresh the materialized view when needed. Another key column in theSTV_MV_INFO
table is thestate
column, which indicates if an incremental refresh is possible(state=1)
or not(state=0)
. In the materialized view we created astate=1
state, which indicates a faster incremental refresh is possible. - Now, let's load more data into the underlying table
finance.customer,
using the following command, and check theSTV_MV_INFO
table:insert into finance.customer values (11, 'mark', 'bar', '1980-02-01'), (12, 'pete', 'smith', '1990-2-01'), (13, 'woofy', 'spock', '1980-11-01'), (14, 'woofy jr', 'scotty', '1975-03-01'), (15, 'eleven', 'of nine', '1990-07-01');
- Query the
STV_MV_INFO
table again to check the status of the materialized view:select name,is_stale,state from STV_MV_INFO where name='customer_agg_mv';
This is the expected output:
name,is_stale,state customer_agg_mv t 1
Note that
stale = 't'
indicates that the underlying data for the materialized view has changed, but it is possible to refresh it incrementally. - Refresh the materialized view using the
REFRESH MATERIALIZED VIEW
command and check the status again:REFRESH MATERIALIZED VIEW finance.customer_agg_mv; select name,is_stale, state from STV_MV_INFO where name='customer_agg_mv';
This is the expected output:
name,is_stale,state customer_agg_mv f 1
As we can see from the preceding code snippet,
customer_agg_mv
is now updated to reflect the underlying table data.
How it works…
A materialized view can be updated with the latest data from the underlying tables by using the REFRESH MATERIALIZED VIEW
command. When the materialized view is being refreshed, it executes a separate transaction to update the dataset. Amazon Redshift also supports an autorefresh option to keep the materialized view up to date as soon as possible after base tables change.
Managing stored procedures
Stored procedures in Amazon Redshift are user-created objects using a Procedural Language/PostgreSQL (PL/pgSQL) procedural programming language. Stored procedures support both data definition language (DDL) and data manipulation language (DML). Stored procedures can take in input arguments but do not necessarily need to return results. PL/pgSQL also supports conditional logic, loops, and case statements. Stored procedures are commonly used to build reusable extract, transform, load (ETL) data pipelines and enable the database administrator (DBA) to automate routine administrative activities—for example, periodically dropping unused tables.
The SECURITY attribute controls who has privileges to access certain database objects.
Stored procedures can be created with security definer controls to allow execution of a procedure without giving access to underlying tables—for example, they can drop a table created by another user and enable the DBA to automate administrative activities.
Getting ready
To complete this recipe, you will need the following:
- Access to the Amazon Web Services (AWS) Management Console
- Access to any SQL interface such as a SQL client or query editor
How to do it…
In this recipe, we will start with creating a scalar Python-based UDF that will be used to parse an Extensible Markup Language (XML) input:
- Connect to Amazon Redshift using the SQL client, and copy and paste the following code to create a
sp_cookbook
stored procedure:Create schema cookbook; create or replace procedure sp_cookbook(indate in date, records_out INOUT refcursor) as $$ declare integer_var int; begin RAISE INFO 'running first cookbook storedprocedure on date %', indate; drop table if exists cookbook.cookbook_tbl; create table cookbook.cookbook_tbl (recipe_name varchar(50), recipe_date date ); insert into cookbook.cookbook_tbl values('stored procedure', indate); GET DIAGNOSTICS integer_var := ROW_COUNT; RAISE INFO 'rows inserted into cookbook_tbl = %', integer_var; OPEN records_out FOR SELECT * FROM cookbook.cookbook_tbl; END; $$ LANGUAGE plpgsql;
This stored procedure is taking two parameters:
indate
is the input, andrecords_out
serves as both an input and output parameter. This stored procedure uses DDL and DML statements. The current user is the owner of the stored procedure and is also the owner of thecookbook.cookbook_tbl
table.Note
Some older versions of SQL client tools may produce an "unterminated dollar-
quoted string at or near "$$"
error. Ensure that you have the latest version of the SQL client—for example, ensure you are using version 124 or higher for the SQL Workbench/J client. - Now, let's execute the
sp_cookbook
stored procedure using the following statements:call sp_cookbook(current_date, 'inputcursor'); fetch all from inputcursor;
This is the expected output:
Message running first cookbook storedprocedure on date 2020-12-13 rows inserted into cookbook_tbl = 1 recipe_name recipe_date stored procedure 2020-12-13 00:00:00
- To view a definition of the previously created stored procedure, you can run the following statement:
SHOW PROCEDURE sp_cookbook(indate in date, records_out INOUT refcursor);
- We will now create another stored procedure with a security definer privilege:
create or replace procedure public.sp_self_service(tblName in varchar(60)) as $$ begin RAISE INFO 'running sp_self_service to drop table %', tblName; execute 'drop table if exists cookbook.' || tblName; RAISE INFO 'table dropped %', tblName; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
- Let's create a user and check whether they have a permission to drop the
cookbook.cookbook_tbl
table. Theuser1
user does not have a permission to drop the table:create user user1 with password 'Cookbook1'; grant execute on procedure public.sp_self_service(tblName in varchar(60)) to user1; set SESSION authorization user1; select current_user; drop table cookbook.cookbook_tbl;
This is the expected output:
ERROR: 42501: permission denied for schema cookbook
- When
user1
executes thesp_self_service
stored procedure, the procedure runs with the security context of the owner of the procedure:set SESSION authorization user1; select current_user; call public.sp_self_service('cookbook_tbl');
This is the expected output:
running sp_self_service to drop table cookbook_tbl table
This allows the user to drop the table without providing the full permissions for the tables in the cookbook schema.
How it works…
Amazon Redshift uses the PL/pgSQL procedural language for authoring the stored procedures. PL/pgSQL provides programmatic access that can be used to author control structures to the SQL language and allow complex computations. For example, you have a stored procedure that can create users and set up necessary access that meets your organizational needs—hence, rather than invoking several commands, this can now be done in a single step. You can find the complete reference to the PL/pgSQL procedural language at https://www.postgresql.org/docs/8.0/plpgsql.html and ready-to-use stored useful procedures at https://github.com/awslabs/amazon-redshift-utils/tree/master/src/StoredProcedures. The SECURITY access attribute of a stored procedure defines the privileges to access underlying database objects used. By default, an INVOKER is used to access the user privileges and the SECURITY DEFINER allows the procedure user to inherit the privileges of the owner.
Managing UDFs
Scalar UDF functions in Amazon Redshift are routines that are able to take parameters, perform calculations, and return the results. UDFs are handy when performing complex calculations that can be stored and reused in a SQL statement. Amazon Redshift supports UDFs that can be authored using either Python or SQL. In addition, Amazon Redshift also supports AWS Lambda UDFs that open up further possibilities to invoke other AWS services. For example, let's say the latest customer address information is stored in AWS DynamoDB—you can invoke an AWS Lambda UDF to retrieve this using a SQL statement in Amazon Redshift.
Getting ready
To complete this recipe, you will need the following:
- Access to the AWS console
- Access to any SQL interface such as a SQL client or query editor
- Access to create an AWS Lambda function
- Access to create an Identity and Access Management (IAM) role that can invoke AWS Lambda and attach it to Amazon Redshift
How to do it…
In this recipe, we will start with a scalar Python-based UDF that will be used to parse an XML input:
- Connect to Amazon Redshift using the SQL client, and copy and paste the following code to create an
f_parse_xml
function:CREATE OR REPLACE FUNCTION f_parse_xml (xml VARCHAR(MAX), input_rank int) RETURNS varchar(max) STABLE AS $$ import xml.etree.ElementTree as ET root = ET.fromstring(xml) res = '' for country in root.findall('country'): rank = country.find('rank').text if rank == input_rank: res = name = country.get('name') + ':' + rank break return res $$ LANGUAGE plpythonu;
Important note
The preceding Python-based UDF takes in the XML data and uses the
xml.etree.ElementTree
library to parse it to locate an element, using the input rank. See https://docs.python.org/3/library/xml.etree.elementtree.html for more options that are available with this XML library. - Now, let's validate the
f_parse_xml
function using the following statement, by locating the country name that has the rank 2:select f_parse_xml('<data> <country name="Liechtenstein"> <rank>2</rank> <year>2008</year> <gdppc>141100</gdppc> <neighbor name="Austria" direction="E"/> <neighbor name="Switzerland" direction="W"/> </country></data>', '2') as col1
This is the expected output:
col1 Liechtenstein:2
- We will now create another AWS Lambda-based UDF. Navigate to the AWS Management Console and pick the AWS Lambda service and click on Create function, as shown in the following screenshot:
Figure 2.1 – Creating a Lambda function using the AWS Management Console
- In the Create function screen, enter
rs_lambda
under Function name, choose a Python 3.6 runtime, and click on Create function. - Under the Function code textbox, copy and paste the following code and press the Deploy button:
import json def lambda_handler(event, context): ret = dict() ret['success'] = True ret['results'] = ["bar"] ret['error_msg'] = "none" ret['num_records'] = 1 return json.dumps(ret)
In the preceding Python-based Lambda function, a sample result is returned. This function can further be integrated to call any other AWS service—for example, you can invoke AWS Key Management Service (KMS) to encrypt input data.
- Navigate to AWS IAM in the AWS Management Console and create a new role,
RSInvokeLambda
, using the following policy statement by replacing[Your_AWS_Account_Number], [Your_AWS_Region]
with your AWS account number/region and attaching the role to the Amazon Redshift cluster:{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": "lambda:InvokeFunction", "Resource": "arn:aws:lambda:[Your_AWS_Region]: [Your_AWS_Account_Number]:function:rs_lambda" } ] }
- Connect to Amazon Redshift using the SQL client, and copy and paste the following code to create a
f_redshift_lambda
function that links the AWS Lambdars_lambda
function:CREATE OR REPLACE EXTERNAL FUNCTION f_redshift_lambda (bar varchar) RETURNS varchar STABLE LAMBDA 'rs_lambda' IAM_ROLE 'arn:aws:iam::[Your_AWS_Account_Number]:role/RSInvokeLambda';
- You can validate the
f_redshift_lambda
function by using the following SQL statement:select f_redshift_lambda ('input_str') as col1 --output col1 bar
Amazon Redshift is now able to invoke the AWS Lambda function using a SQL statement.
How it works…
Amazon Redshift allows you to create a scalar UDF using either a SQL SELECT
clause or a Python program in addition to the AWS Lambda UDF illustrated in this recipe. The scalar UDFs are stored with Amazon Redshift and are available to any user when granted the required access. You can find a collection of several ready-to-use UDFs that can be used to implement some of the complex reusable logic within a SQL statement at the following link: https://github.com/aws-samples/amazon-redshift-udfs.