Is it possible to get the configuration in a macro other than the "create" macro?

Archive
1

Is it possible to get the configuration in a macro other than the create_table_as macro?

{% macro MYADAPTER__create_table_as(relation) -%}
  {%- set myt = config.get('MY_CONFING', none) -%}
  {{ myt }} <-- is 'VALUE'
{% endmacro %}
{% macro MYADAPTER__drop_relation(relation) -%}
  {%- set myt = config.get('MY_CONFING', none) -%}
  {{ myt }} <-- is none, why???
{% endmacro %}
1 Like
2

Wanted to start by saying that getting custom adapters up and running is hard! I definitely needed a lot of help to get dbt-synapse off the ground. So if I’m not able to answer your question, hopefully my empathy will be of value.

Some follow-up questions:

  1. what info are you looking to get from the relation config dictionary?
  2. can you get this info from any of the db’s system tables or views, given that the relation exists if dbt is trying to drop it?
  3. can you get info from any value of the relation object dictionary?

Relation is a dictionary with the following keys:

  • relation
  • path
  • type
  • quote_character
  • include_policy
  • quote_policy
  • dbt_created

If you add the following snippet to the top of your MYADAPTER__drop_relation() macro, you print these values to the console to see if their valuable.

  {% for key, value in relation.items() %}
    {{ log(key ~ ': ' ~ value, info=True) }}

On the off chance that you’re trying to determine if the relation is a table or view, we use the relation.type in sqlserver__drop_relation() to determine if the relation is a table or a view like this

{% macro sqlserver__drop_relation(relation) -%}
  {% if relation.type == 'view' -%}
   {% set object_id_type = 'V' %}
   {% elif relation.type == 'table'%}
   {% set object_id_type = 'U' %}
   {%- else -%} invalid target name
   {% endif %}
  {% call statement('drop_relation', auto_begin=False) -%}
    if object_id ('{{ relation.include(database=False) }}','{{ object_id_type }}') is not null
      begin
      drop {{ relation.type }} {{ relation.include(database=False) }}
      end
  {%- endcall %}
{% endmacro %}
3

Thank you.

what info are you looking to get from the relation config dictionary?

I want to get a global value. It like index and dist in dbt-synapse

can you get this info from any of the db’s system tables or views, given that the relation exists if dbt is trying to drop it?

No

can you get info from any value of the relation object dictionary?

No, it is user configuration

I want it because DB has a special statement ON CLUSTER. It use in a create query and a drop query. And only users know when to use it

CREATE TABLE t1 ON CLUSTER ...
DROP TABLE t1 ON CLUSTER ...
4

Ah ok, interesting. This is for Clickhouse right? Might be kludgy be there might be a way to get the info you want via system.distributed_ddl_queue. But you’d have to do some fun query parsing and sort by date descending to get the newest version?

Is it a requirement that you specify the cluster on which the table was created in order to drop it?

Another random thought would be to specify the cluster in the profile. Is the idea that a dbt user can have a clickhouse project where they can work with not only multiple clusters, but that some tables will be distributed and other only in a single database?

5

I though about it. But it not secure … One small bug and drop production table (a dbt user can have not dbt managmented tables) ((

Is it a requirement that you specify the cluster on which the table was created in order to drop it?

Yep

Another random thought would be to specify the cluster in the profile.

Good idea, thanks!

I think mb it is a dbt’s bug? Because the config pass to context, but not render…