Call a dataset persistent function with a ref

Help
1

I’m migrating a view from bigquery and it uses a couple persistent functions written in javascript. Since they aren’t written in sql, I cannot use a macro to reproduce them, so I probably have to call them in my dbt model. Since I have to write my project_id and dataset_id in order to call the function, is there a way to add these as source and call the function using a ref, so the model respects modularity of sources?

Thank you!

1 Like
2

@dreinon

  1. Define your function as source
  2. Use source() function in your dbt model, it compiles to the full object name in the database.

src_view.yml

version: 2

sources:
  - name: function_source_name# this is the source_name
    database: function_project_id 
    schema: function_dataset_id
    tables:
      - name: function_name # this is the your function_name

model.sql

SELECT
col1,
col2,
{{source('function_source_name','function_name ')}}(parm1,parm2) # Compiles to the full object name in the database

FROM table
1 Like
3

Thanks for answering! Good to know that method works, but isn’t it a bit confusing to have functions and tables both in the same place treated as the same type of thing?

4

to avoid confusion create a separate source only for functions in your yml file

1 Like
5

I’m not familiar with BQ JS functions, but could you not also create a macro that just returns the name of the macro and the arguments, which would then be called as normal inside of your project? Something like this:

{% macro js_function(arg1, arg2) %}
    {{ this.database }}.{{this.schema}}.js_function({{ arg1 }}, {{ arg2 }})
{% endmacro %}

When you call it like this:

select col_1, 
{{ js_function('col_2', 'col_3') }}
from {{ ref('some_model') }}

it will render as

select col_1,
ANALYTICS_DEV.dbt_jlabes.js_function(col_2, col_3)
from ANALYTICS_DEV.dbt_jlabes.some_model
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.