CODEWITHSUNDEEP
sqlpostgresqlsnowflake-cloud-data-platformdbtduckdb
Piotr
Piotr

Reputation: 268

DBT dispatch method is not seen by the compiler

I'm running a code written for Snowflake SQL dialect on database with DuckDB(Postgres) dialect. I need to dispatch the functions that do not exist in the target one. For example I have error regarding endswith function - it's available in Snowflake, but in Postgres it's ends_with. That's how error looks like:

Catalog Error: Scalar Function with name endswith does not exist!
Did you mean "ends_with"?

So I wrote a dispatcher method and I stored it in macros/endswith.sql:

{% macro endswith (expr_1, expr_2) -%}
    {{ adapter.dispatch('endswith')(expr_1, expr_2) }}
{%- endmacro %}

{% macro default__endswith (expr_1, expr_2) -%}
    {{ return(dbt_utils.endswith(expr_1, expr_2)) }}
{%- endmacro %}

{% macro snowflake__endswith (expr_1, expr_2) -%}
    {{ return(dbt_utils.endswith(expr_1, expr_2)) }}
{%- endmacro %}

{% macro duckdb__endswith (expr_1, expr_2) -%}
    {{ return(ends_with(expr_1, expr_2)) }}
{%- endmacro %}

{% macro postgres__endswith (expr_1, expr_2) -%}
    {{ return(ends_with(expr_1, expr_2)) }}
{%- endmacro %}

I tried also:

{% macro endswith (expr_1, expr_2) -%}
    {{ adapter.dispatch('endswith')(expr_1, expr_2) }}
{%- endmacro %}

{% macro default__endswith (expr_1, expr_2) -%}
    dbt_utils.endswith(expr_1, expr_2)
{%- endmacro %}

{% macro snowflake__endswith (expr_1, expr_2) -%}
    dbt_utils.endswith(expr_1, expr_2)
{%- endmacro %}

{% macro duckdb__endswith (expr_1, expr_2) -%}
    ends_with(expr_1, expr_2)
{%- endmacro %}

{% macro postgres__endswith (expr_1, expr_2) -%}
    ends_with(expr_1, expr_2)
{%- endmacro %}

It throws no errors, but also acts as it's not seen by the compiler. When I compile, I get normal endswith in the output and when I run I get the same error as I did.

I expected it might be that there is something wrong with macro visibility, but when I delete some macros from directory I have missing dependencies.

There are 3 sites with documentation regarding that:

  1. about dispatch config
  2. dispatch
  3. enter link description here

they provide this tool:

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['my_project', 'dbt_utils']

in theory it shouldn't be necessary, as the default method should be one in my project folder, but I tried, and it didn't change anything (maybe I did it wrong?). To help it out I also tried what is visible in the dbt_utils source code:

adapter.dispatch('endswith', 'dbt_utils')

but it also didn't change anything. During trials, I reinstalled the adapter, updated the dbt also to experimental versions, manipulated with profile. I'm still not sure if dbt knows what is the target dialect, but I assumed it's being chosen by the profile, which in my case looks like this:

duck_db: target: dev outputs: dev: type: duckdb path: '~/data/mock.db' extensions: - httpfs - parquet

At this point I don't have any other ideas on how to solve this issue. How should I use the adapter?

Upvotes: 1

Views: 412

Answers (3)

Piotr
Piotr

Reputation: 268

OP:

Update: There was no dispatcher built-in and I don't know what was the reason I got that error, I could not reproduce it after reinstallation of duck and postgres adapters, but I found a definitive answer: to make adapter work, I had to wrap every instance of the problematic function being called in macros in the curly braces. Example:

{{endswith(column_alias, '__test')}}

so it's rendered in jinja during compilation.

Old prost: I don't know if it's correct solution as it led me to another error, but as for now the macro is seen by the compiler (which rises macro duplicated names error) - the problem was I didn't add endswith to the models.yml file. Also, when I had it modified incorrectly - without the arguments specified - there was no error, but compiler didn't see it.

My current theory is that the new error (separate for every dispatcher option) is due to the fact that there is already some dispatcher of endswith built-in and the dbt compiler doesn't know it should be using that (I can't find it in project files nor external libraries). I'll edit this post when I'll know.

Upvotes: 0

Sharon
Sharon

Reputation: 11

By examining the audit_helper package example, it's apparent that the absence of a return statement within their functions implies that the root cause may reside elsewhere.

{% macro get_columns_in_relation_sql(relation) %}

{{ adapter.dispatch('get_columns_in_relation_sql', 'audit_helper')(relation) }}

{% endmacro %}

What about running a single custom adapter dispatch like this:

{% macro endswith(expr_1, expr_2, adapter='default') -%}
    {%- if adapter == 'default' -%}
        {{ return(dbt_utils.endswith(expr_1, expr_2)) }}
    {%- elif adapter == 'snowflake' -%}
        {{ return(dbt_utils.endswith(expr_1, expr_2)) }}
    {%- elif adapter == 'duckdb' -%}
        {{ return(ends_with(expr_1, expr_2)) }}
    {%- elif adapter == 'postgres' -%}
        {{ return(ends_with(expr_1, expr_2)) }}
    {%- else -%}
        {{ return('Unsupported adapter') }}
    {%- endif -%}
{%- endmacro %}

Upvotes: 1

Josh Wills
Josh Wills

Reputation: 56

dbt-duckdb developer here. I think the syntax for adapter.dispatch needs to be like this:

{% macro endswith (expr_1, expr_2) -%}
    {{ return(adapter.dispatch('endswith')(expr_1, expr_2)) }}
{%- endmacro %}

That is, the return statement is doing actual work here, I don't think you can leave it out.

Upvotes: 1

Related Questions