I am sharing the code to send a Looker query to BigQuery with Cloud Functions.
Static solution (using Looker Python SDK, BigQuery API, and Cloud Functions): There is no option in Looker UI, and the query id or look id is hardcoded into Cloud Functions. Use this option to only allow certain queries to be sent to BigQuery (i.e.: you want to send data from System Activity to BigQuery as a workaround for ETL). Link to code example
Dynamic solution (using Looker Action API, BigQuery API, and Cloud Functions): There is an option for Looker users to select “BigQuery” as a destination for their queries in the UI (same menu with “Schedule” or “Send). Use this option to allow users to send any queries to BigQuery using the Looker UI. Link to video and code example
Potential use cases
Workaround for System Activity ETL: Get data from Looker’s System Activity and write to BigQuery directly. These BigQuery tables can then be registered as a connection in Looker for additional LookML data modeling. Currently, Looker’s System Activity stores a maximum of 100k rows, or 90 days of historical query and event data
Transfer data from a different database to BigQuery
Considerations
Cloud Function is easy to set up, and suitable for light-weighted, on-the-fly tasks. For heavy workloads, consider using Looker’s native actions (sending to Google Cloud Storage, S3) or additional ETL/ELT tools (such as GCP’s Dataflow)
def main(request):
get_data_from_looker()
write_to_file()
load_to_bq()
return("Successfully loaded data from Looker to BigQuery")
get_data_from_looker(query_id=1): # I hardcoded the query id here since this is a prototype. What I did is that I run a query in the UI and go to system activity to grab the query id. In your case, I think you could manually select all fields in an explore, and run that query once, then go to system activity, and get the query id (query id is unchanged in Looker if there is no change to the query itself). This step would probably require manual work.
def load_to_bq(): # I know the fields in my query id, so I prepared the table with the exact column name in advanced. In your case, you could:
Thanks for this contribution! I am doing something very similar for a project I’m working on with my team. However, we got stuck for an authentication problem. I described my problem in more details here.
Essentially, it seems that the Cloud Function needs to allow unauthenticated access in order to be triggered from Looker. Is this true? It strikes me as strange, especially if you have to put this in production.
I didn’t write this in the linked post, but when I try to add my custom action hub I receive the following error: “Your client does not have permission to get URL /list_actions/ from this server. 403 Forbidden”.
It then asks me to refresh the authorization token, but I have no idea what that could be.
I’m asking you since you may have gone through similar problems. Thanks in any case!
Essentially, it seems that the Cloud Function needs to allow unauthenticated access in order to be triggered from Looker → Yes, this is true (as of writing, March 2022). One workaround is to use GCP secret manager to generate an access token to prevent non-Looker users from triggering the function.
Step 1: Generate Secret Key from Cloud Functions UI
Step 2: Go to Admin → Actions, and you would be able to input the secret in authorization token here.
For your specific use case: There is a parameter called “user_attribute_param” for action hub, so you could (1) change your GCP secret keys on a schedule (2) use Looker API to update the value of the user attributes to match that secret keys and add a logic inside your code (if secret == user_attributes then execute)
Passes a user attribute to the receiving server. You must add the data action’s url value to the URL Allowlist for Data Actions if you are using a user_attribute_param for your data action.
If these all do not fulfill your security requirements, then you can spin up a whole new server and use IP allowlist. We do have source code (node js) for action hubs in our repository here
I am trying to invoke cloud function from Looker, but function is not getting triggered.
I have set allow_unathenticated access as well. But I am not able to set principle allusers for cloud invoker access. When I tried, I am getting pop up window as IAM policy update failed.
Can you please confirm without setting allusers principle, we will not be able to trigger from Looker?
@lantrann Any update on how to invoke a Cloud Function from Looker’s Action Hub without opening the endpoint to “allUsers”? This is a blocker for our project, we can’t use “allUsers”.
I’m trying to send email via SendGrid from Looker dimension (the way it is implemented here ).
the problem is that after I submit the form I receive the error: “The form was not accepted”. Any advice?