User attributes and derived tables causes regenerator errors

attributes
pdt

(Ezra Wolfe) #1

Hi all,

We are using Looker as an embedded reporting solution for our application. All our customers have a separate database in our PostgreSQL data warehouse and we use the Looker user attribute to set the connection string. This works fine.

However with derived tables, when the regenerator runs, there is no user attribute so the query fails.

The LookML for the PDT might look something like this:

    select pl.instance, {{ _user_attributes['schema']  }}.course_object.nid::varchar, pl.parents|| {{ _user_attributes['schema']  }}.course_object.nid::varchar
from pl
join {{ _user_attributes['schema']  }}.course_object on pl.nid = {{ _user_attributes['schema']  }}.course_object.instance
where module='course_relationships'

)

and when the query runs by regenerator it uses:

 select pl.instance, .course_object.nid::varchar, pl.parents|| .course_object.nid::varchar
from pl
join .course_object on pl.nid = .course_object.instance
where module='course_relationships'

So, the schema attribute is empty and as a result the query log is full of “derived_table course_relationships creation failed” errors.

I assume this means the derived tables are not being regenerated in the background based on the trigger and only done at runtime, when a user loads a report.

  1. Is that assumption correct?
  2. If so, are there any suggested approaches for addressing this? The PDTs are coming from a shared model that is included in all our customer models, so it’s feasible to move them there, but just wondering if this could be done in a way that keeps them in our shared model.

Thanks!


Unable to schedule "All Results" to be delivered with the option “and results changed since last run”
(jeremy.eckman) #2

Hey @Ezra_Wolfe

You are correct, derived tables injecting a user attribute with liquid into the sql will not work with a sql_trigger_value but will work when generated at runtime from a user actually running a query dependent on that table.

We generally don’t recommend persisting derived tables that use liquid parameters or templated filters because they would need to be run again each time the value changed, which often means the table will be rebuild in most queries so persistence doesn’t really help and adds the overhead of writing the table to the database. In the rare cases where it seems to make sense to persist these tables, they will only work when persisted with a persist_for parameter.

Usually the biggest factor would be how long the query takes to run, if it’s not long, then just not persisting the table is probably the best option.