Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Amazon Redshift Cookbook

You're reading from   Amazon Redshift Cookbook Recipes for building modern data warehousing solutions

Arrow left icon
Product type Paperback
Published in Jul 2021
Publisher Packt
ISBN-13 9781800569683
Length 384 pages
Edition 1st Edition
Languages
Arrow right icon
Authors (3):
Arrow left icon
Shruti Worlikar Shruti Worlikar
Author Profile Icon Shruti Worlikar
Shruti Worlikar
Harshida Patel Harshida Patel
Author Profile Icon Harshida Patel
Harshida Patel
Thiyagarajan Arumugam Thiyagarajan Arumugam
Author Profile Icon Thiyagarajan Arumugam
Thiyagarajan Arumugam
Arrow right icon
View More author details
Toc

Table of Contents (13) Chapters Close

Preface 1. Chapter 1: Getting Started with Amazon Redshift 2. Chapter 2: Data Management FREE CHAPTER 3. Chapter 3: Loading and Unloading Data 4. Chapter 4: Data Pipelines 5. Chapter 5: Scalable Data Orchestration for Automation 6. Chapter 6: Data Authorization and Security 7. Chapter 7: Performance Optimization 8. Chapter 8: Cost Optimization 9. Chapter 9: Lake House Architecture 10. Chapter 10: Extending Redshift's Capabilities 11. Other Books You May Enjoy Appendix

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:

  1. 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, and records_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 the cookbook.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.

  2. 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
  3. 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);
  4. 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;
  5. Let's create a user and check whether they have a permission to drop the cookbook.cookbook_tbl table. The user1 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
  6. When user1 executes the sp_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.

You have been reading a chapter from
Amazon Redshift Cookbook
Published in: Jul 2021
Publisher: Packt
ISBN-13: 9781800569683
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image