Stored Procedure

Stored procedure: a definition

A "stored procedure" is a database programming concept that refers to a set of precompiled and stored SQL (Structured Query Language) statements and procedural logic within a database management system (DBMS). These stored procedures can be executed by invoking a single command or function call, making it easier to perform common database operations and complex data manipulations.

Stored procedures are a valuable tool in data engineering for streamlining database operations, improving performance, enhancing security, and promoting code reusability. They are particularly useful for managing and processing large volumes of data efficiently and consistently within a database environment.

Key aspects of stored procedures

Here are some key aspects of stored procedures in data engineering:

Precompiled Logic: Stored procedures contain one or more SQL statements and procedural code written in a database-specific language (e.g., PL/SQL for Oracle, T-SQL for SQL Server, or PL/pgSQL for PostgreSQL). The code is precompiled and stored in the database for improved performance compared to executing individual SQL queries.

Encapsulation: Stored procedures encapsulate database operations into a single unit. This means that complex data operations, such as data transformations, data validation, or data aggregation, can be encapsulated within a stored procedure, simplifying our interactions with the database.

Reusability: Stored procedures can be reused across applications or parts of the same application. This promotes code modularity and reduces redundancy, as developers don't need to rewrite the same SQL logic in multiple places.

Security and Access Control: DBMSs often provide access control mechanisms for stored procedures. Developers can grant specific permissions to execute stored procedures while restricting direct access to tables or views. This helps enhance security by limiting the potential for unauthorized data access or modification.

Performance Optimization: Since stored procedures are precompiled and stored, they can lead to improved query optimization and execution plans. This can result in faster query execution, especially for complex operations.

Transaction Management: Stored procedures can be used to manage transactions within the database. Developers can define custom transaction boundaries, ensuring data consistency and integrity.

Version Control: Stored procedures can be versioned, allowing for easier management and tracking of changes over time. This can be valuable when maintaining complex database systems.

Maintenance and Troubleshooting: Centralizing database logic within stored procedures can make it easier to maintain and troubleshoot database-related issues. Developers can focus on optimizing and debugging the stored procedures themselves rather than hunting down scattered SQL queries in application code.

An example of Stored Procedure in Python and PostgreSQL

To illustrate Stored Procedures using Python, here is a simple example using PostgreSQL as our database, and using psycopg2, the most popular PostgreSQL database adapter for the Python programming language.

In the PostgreSQL setup steps, we created a band_members table with the five members of the band Radiohead. In this example, we'll create a stored procedure that calculates the average age of Radiohead band members based on their birthdates and returns the result.

First, let's create the stored procedure directly in PostgreSQL:

As a reminder, you need to log in to your Postgres instance as follows (See database instructions for details.)

psql -U your_username -d dagsterdb

Once connected to the database, execute the following command to create a stored procedure:

CREATE OR REPLACE FUNCTION calculate_average_age()
RETURNS FLOAT AS $$
DECLARE
    total_age INT := 0;
    member_count INT := 0;
    average_age FLOAT := 0.0;
    member_record RECORD;
BEGIN
    -- Create a cursor to fetch rows from the band_members table
    FOR member_record IN (SELECT EXTRACT(YEAR FROM age(current_date, created_date)) AS age_diff FROM band_members)
    LOOP
        total_age := total_age + member_record.age_diff;
        member_count := member_count + 1;
    END LOOP;

    -- Calculate then return the average age
    IF member_count > 0 THEN
        average_age := total_age / member_count;
    END IF;

    RETURN average_age;
END;
$$ LANGUAGE PLPGSQL;

Now, let's write a Python script to call this stored procedure and retrieve the average age:

import psycopg2

# Database connection parameters
db_params = {
    'dbname': 'your_database_name',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',
    'port': 'your_port',
}

try:
    # Connect to the PostgreSQL database
    connection = psycopg2.connect(**db_params)
    cursor = connection.cursor()

    # Call the stored procedure and retrieve the result
    cursor.callproc('calculate_average_age')
    result = cursor.fetchone()[0]

    print(f'The average age of band members is: {result:.2f} years')

except psycopg2.DatabaseError as e:
    print(f'Error: {e}')
finally:
    if connection:
        cursor.close()
        connection.close()

Make sure to replace 'your_database_name', 'your_username', 'your_password', 'your_host', and 'your_port' with the actual connection details for your PostgreSQL database.

If you have followed this guide, the example would use:

• 'dbname': 'dagsterdb',
• 'host': 'localhost',
• 'port': '5432',
• 'user': will be the one you retrieved with the command whoami
• you can skip 'your_password' if you have not set one.

This example demonstrates how to create a stored procedure in PostgreSQL to perform a specific data analysis task and then use a Python script to call that stored procedure and retrieve the result. We defined the logic in the database, and our application just had to call the Postgresql function with cursor.callproc('calculate_average_age'). Data engineers can use such stored procedures to encapsulate complex data operations in the database and leverage them for various analytical tasks.

And so, what is the average age of the band members? Here is the output:

The average age of band members is: 54.00 years

You can list all stored procedures in your Postgres database with the following SQL command:

SELECT proname AS procedure_name,
       pg_get_function_identity_arguments(oid) AS arguments
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

About Dagster and stored procedures:

Since Dagster is a Python framework, you can use Python code to call stored procedures as we have done above. That being said, depending on your use case, it is worth considering running your SQL queries directly from within Dagster and storing the queries as files/text. This will provide version control for your stored procedures and keep the logic close to the scheduling rather than needing to search through multiple places.