Question

Is it possible to have user input value to load in database/looker table/view?

  • 15 August 2017
  • 2 replies
  • 1879 views

Userlevel 2

Is it possible to provide user with the option in look to enter value and that value can be write back to DB table or in Looker persist table for further analysis? Like write back functionality in other BI tools like MicroStrategy ot Tableau?


I am using Looker V4.18 and Google BQ as DB.


Thanks,

Kunal


2 replies

Userlevel 3

We don’t have a specific example of that but it is certainly possible. The most common approach would be to use data actions. You would just need a server to ‘hear’ the data action which could then trigger a script that runs some INSERT command against your database.


We have some examples of data actions using Zapier here but if you’re running into issues with a particular use case feel free to visit us at help.looker.com where we’ll be happy to help you out.

Kunal,


This solution may only apply if you have an “on-premises” instance, but we achieved this by standing up a simple flask app “tagger” on our EC2 box, using a DB user that has INSERT permission to the tables we want the (Looker) user to be able to update. Since the tagger app is serving from the same place as the Looker application, it’s a little more secure, already sits behind our VPN (so can only be called by a user on the VPN, including a logged in Looker user), and it’s a little easier to restrict access only to the Looker app via whitelisted IP addresses.


Then using a data action dimension as Rufus mentioned (or even just a dimension that has an html field, if your “tagger” app can take GET requests), you can hard-code the IP address to reach the tagger, and push through the directions the tagger will need via url parameters or (or as form fields if using data actions). Often just having the column name, the new value, the current timestamp (for an last_updated_at field or for logging by the tagger) and the action type (update / add to an array, etc.) can achieve a lot.


Also, rather than directly updating a table, it can be helpful and a little less error-prone to have the user actually writing to a separate annotations/tags table and then join that back in to the source table to make a ‘final’ presentation table that combines the 2 – that way the user isn’t actually overwriting values and can roll back changes.


Using Zapier to do the connecting is probably a more user-friendly and scalable approach if you’ll be doing this from other non-Looker sources also, but if your database is behind a VPN it may not be possible (or very easy) to let Zapier write to it directly (and may not be advisable either).

Reply