How to Dynamically Connect Looker to Different BigQuery Reservations Using User Attributes?

[omidos]

Hi Looker Community,

I'm working on a project where I need to dynamically connect to different BigQuery reservations based on the user's identity.

Specifically, I want to use Looker user attributes to switch between different BigQuery connections that correspond to different reservations.

Here’s the approach I’m considering:

Create Multiple BigQuery Connections:

bq_reservation1: Points to BigQuery reservation with 100 slots.

bq_reservation2: Points to BigQuery reservation with 200 slots.

Define User Attributes in Looker:

Created a user attribute called reservation_name.

Assign User Attributes:

For users who should use reservation1, set reservation_name to bq_reservation1.

For users who should use reservation2, set reservation_name to bq_reservation2.

Dynamic Connection in LookML Model:

Use the user attribute to dynamically set the connection in the LookML model.

is there a working example I can used?

[andy4 · Accepted solution]

For this, use a parameterized connection. This article reply has a screenshot of the icon to click next to your project ID in the connection interface.

When you do this, the default project for all tables will match whatever user attribute you use there. It will also set the compute project. That way your billing will be associated with a different compute project based on each user's user attribute value.

This does pose a challenge to the use of PDTs. There is a feature request currently under development to make this more flexible - where the storage of PDTs and compute of PDT building is still done by one central billing project, but you can still parameterize the compute of normal usage. No ETA for that yet, but watch the release notes.