Return number of rows affected error dbt.context.providers.RuntimeDatabaseWrapper

[Mnegrisoli]

Hi !! I try tro get the number of affected rows on a post_hook query and getting this error:

dbt.context.providers.RuntimeDatabaseWrapper.

Is thiss the better way or exists another ? Thanks !

My macro is:

{% macro teste_log_post_hook() %}

    {% if execute %}

        {% set cod_processamento_var = log_processamento_inicializa('teste_post_hook') %}
       

        {% set update_query = "update mydataset.mytable set value_column = 'changed';" %}

        {% set affected_rows = get_affected_rows(update_query) %}

        {% set log_debug = '**** Debug **** ' ~ affected_rows %}

        {{ log(log_debug, info=True) }}


    {% endif %}

{% endmacro %}```

and my second macro to run a query is:

{% macro get_affected_rows(query) %}
{% set sql = adapter.quote_as_config(query) %}
{% set result = run_query(sql) %}
{% set affected_rows = result.num_rows_affected %}
{{ return(affected_rows) }}
{% endmacro %}```

[Surya]

@Mnegrisoli

quote_as_config

@Mnegrisoli You got the error because the adapter object has no method called quote_as_config,
for the available methods please check this

If you are using snowflake u can get updated records count using EXECUTE IMMEDIATE and SQLROWCOUNT
i have tried the below code and its working to me

{% macro teste_log_post_hook() %}

    {% if execute %}
       
        {% set update_query %}

            EXECUTE IMMEDIATE $$
            BEGIN
            UPDATE my_values SET ID =2 WHERE ID >1;
            RETURN SQLROWCOUNT;
            END;
            $$;

        {% endset %}

        {% if execute %}
            {% set affected_rows = run_query(update_query).rows[0][0] %}
        {% else %}
            {% set affected_rows = 0 %}
        {% endif %}

        {% set log_debug = '**** Debug **** ' ~ affected_rows %}
        {{ log(log_debug, info=True) }}

    {% endif %}

{% endmacro %}

let me know if it works

[Mnegrisoli]

Thanks @Surya ! I have success using a call statement. I’ll leave it here on record. My database is BigQuery:

 {%- call statement ('atualiza_teste',fetch_result=true) -%}
            update mydataset.mytable set valor = "mudou" where 1=1;
        {%- endcall -%}
     
        {%- set states = load_result('atualiza_teste') -%}
   
        {{ log(states['response'].rows_affected,info=true) }}