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() }}"