Macro to create Redshfit stored proc failing when run with on-run-start hook

Help
,
1

The problem I’m having

We’ve got a few User Defined Functions and Stored Procedures in Redshift that I’m trying to check into our dbt repo (as detailed here: Using dbt to manage user defined functions). When I run my macro from the command line using dbt run-operation it works like a charm. But I’ve got an on-run-start hook in dbt_project.yml that calls that same macro and that fails every time with a “query was empty” error. Anybody know where I might be going wrong?

The context of why I’m trying to do this

We currently just have UDFs in our Redshift cluster that were manually created and are not checked into source control anywhere. Getting them checked into dbt would make things more stable.

What I’ve already tried

I’ve tried messing around with spacing and other formatting. I’m sure there’s something simple that I’m missing, given that this works when the macro is called from the command line instead of via a webhook.

Some example code or error messages

create_add_table_to_datashare.sql creates the stored procedure:

{% macro create_add_table_to_datashare() %}

create or replace procedure {{target.schema}}.add_table_to_datashare(datashare_name varchar, schema_name varchar, table_name varchar)
    NONATOMIC
	language plpgsql
as $$
BEGIN
    BEGIN
        -- Attempt to add the table to the datashare
        -- This will raise an exception if the table already exists in the datashare
        -- or if there is any other issue with the operation
        EXECUTE 'ALTER DATASHARE ' || quote_ident(datashare_name) ||
                ' ADD TABLE ' || quote_ident(schema_name) || '.' || quote_ident(table_name);
    EXCEPTION
        WHEN OTHERS THEN
            -- Check if the error is due to the table already existing in the datashare
            -- SQLSTATE '42P06' indicates that the table already exists in the datashare
            -- This is a PostgreSQL-specific error code for "duplicate table"
            IF SQLSTATE = '42P06' THEN
                -- Handle the case where the table already exists in the datashare
                RAISE NOTICE 'Table %.% already exists in datashare %', schema_name, table_name, datashare_name;
                RETURN;
            ELSE
                -- If it's a different error, re-raise it
                -- This will allow you to handle other errors as needed
                RAISE EXCEPTION '%', SQLERRM;
            END IF;
    END;
END;
$$;

{% endmacro %}

create_udsfs.sql runs the create_add_to_datashare macro (and will run more too once I get this figured out):

{% macro create_udfs() %}

create schema if not exists {{ target.schema }};

{{ create_add_table_to_datashare() }};

{% endmacro %}

The on-run-start hook in dbt_project.yml calls the create_udfs macro:

on-run-start:
  - "{{ create_udfs() }}"
2

The on-run-start hook accepts a SQL statement (or a list of SQL statements) to be run at the start or end of the following commands:

  • dbt build
  • dbt compile
  • dbt docs generate
  • dbt run
  • dbt seed
  • dbt snapshot
  • dbt test

I see that the create_add_table_to_datashare() macro has semicolons in the returned query, and you are also adding another semicolon at the end when calling the macro inside the create_udfs() macro.

Please try the following options:

  1. Remove the semicolon at the end after the macro call create_add_table_to_datashare() and try again.
{% macro create_udfs() %}

create schema if not exists {{ target.schema }};

{{ create_add_table_to_datashare() }}

{% endmacro %}
  1. Try returning the two commands as a list from the macro create_udfs()
{% macro create_udfs() %}

{% set create_statement = 'create schema if not exists'~ target.schema  %}
{% set create_add_table =  create_add_table_to_datashare() %} 
{% set queries = [create_statement ,create_add_table] %}
{% do return(queries ) %}

{% endmacro %}