Hey everyone! Hoping some dbt wizards can help me out! We created a macro that creates a UDF which takes two bits columns and converts them into a UUID value. When I individually run the models that use this macro - dbt run --model INSERTMODELHERE - they are completely successful, the macro works, the function is callable and works in the query, and the UUID values are created as expected; however, when I do a straight dbt run, or when these models run on production via their selector, I will often get an error:
22:30:42 091016 (22000): Remote file ‘UUIDFunction.jar’ was not found. If you are running a copy command, please make sure files are not deleted when they are being loaded or files are not being loaded into two different tables concurrently with auto purge option.
Sometimes it’s one model, or several, but it results in some skipping downstream. Here is the macro:
{% macro create_get_uuid() %}
{% set sql %}
rm @~/UUIDFunction.jar;
create or replace function {{target.schema}}_STAGING.GET_UUID(lsb bigint, msb bigint)
returns varchar
language java
handler=‘UUIDFunction.getUuid’
target_path=‘@~/UUIDFunction.jar’
as
$$
import java.util.UUID;
class UUIDFunction {
public static String getUuid(Long lsb, Long msb) {
String uuid = null;
if (lsb != null && msb != null) {
uuid = new UUID(msb, lsb).toString();
}
return uuid;
}
}
$$;
{% endset %}
{% do run_query(sql) %}
{% endmacro %}
I have a feeling that it doesn’t like the approach of using a pre-hook and running the macro every time or something, but I can’t seem to figure it out.
EDIT: To clarify that I use {{ config(pre_hook="{{ create_get_uuid() }}") }} at the beginning of the models in question.