Dynamically adjusting connection based on user attributes

[burns44]

Hi!  I'm working on a project to move our looker workloads from SQL Server to Snowflake.  I'd like to be able to use our existing model file but dynamically change the connection between SQL/Snowflake based on user attributes that I set.  I want to do this because it means I can gradually shift users to the new backend (and I can avoid doing a hard cutover).

How can I accomplish this?  I've tried:

1. Using Liquid to alter the connection of the model, e.g.:

{% if _user_attributes['use_snowflake'] %}
connection: "snowflake"
{% else %}
connection: "sql_server"
{% endif %}

 That doesn't work since apparently Liquid can't be used there.  

2.  Altering the connection attributes based on user_attributes:

This doesn't work.  Although you can dynamically route users to different databases within (say) SQL Server using attributes, you can't point them to a connection with a different dialect.

Is this possible using Looker?

[sam8]

Awesome use case, I love the idea of gradually shifting users to use the new connection.

I think both options will lead to dead ends. I don't think it's possible to get Liquid anywhere near the connection name in the LookML, and I know the connection attributes are only meant to change between different instances of the same DB type, as you mentioned.

I think this is a feature request. One workaround could be to change over individual Looks/dashboards in a gradual manner rather than the users.

[burns44]

Thanks Sam - glad I wasn't missing anything since the individual looker/dashboard migration is a lot more daunting (a lot of these are shared objects!).  But I think I'm all set either way, thanks!!

[P1eter1]

Hello Sam, I have the same issue and just want to make sure if it is feasible. I am connecting Looker to Big Query via Service Account. I have 2 service account (Fine Grain Role and Masked Role).

1-I need then to create 2 connections on Looker for each service account correct?

2-I need to dynamically switch the connection based on the user attribute. Is it possible in anyway or is my cased the same as burns?

[sam8]

That's correct. There are some fields in the BQ connection page that can be set up with user attributes directly - such as Billing Project ID and Dataset - but the service account is not one of them.

One way to get dynamic permissions on a single BQ database is to set up OAuth for the BQ connection. Then every user Looker user authenticates in to Google with their own Google OAuth credentials, and you can manage those permissions outside of Looker. Not sure if that'd work for your use case, but wanted to share just in case it helps!

[P1eter1]

Hi sam, 

This point can help but regarding an embed user, is there a way to authenticate to bigquery ? Since to run a query the user must login to bigquery but i want it dynamic through embed url?

[sam8]

I think the most you could do would be to present users with a BigQuery authentication screen. From the docs:

"Once the Looker connection to BigQuery is set up for OAuth, Looker will prompt users to sign in with their Google Account before running queries that use the BigQuery connection. Looker shows this prompt from Explores, dashboards, Looks, and SQL Runner."

As far as I know, it's not possible to pre-authenticate users into BQ using an embed URL.

[P1eter1]

Hi Sam,

I appreciate your support because this is something we are working on it and the dynamic change is needed.

1-Can you confirm that the log in to big query cannot work through API for embed users without login page?(maybe call a google api to get token and pass it with the embed url to looker or something similar)

2- If an embed user logs in to Big query, does his token gets saved like a normal user?

3-Just a thought but if a Looker user is logged in, his token is saved. Is there a way to link any embed user to this logged in user (the normal users) through the url?

Thank you.

[P1eter1]

Hi Sam,

Thank you for the response! I will try the OAuth and see if this works .