Write the result of a Looker query to BigQuery with Cloud Functions (workaround for System Activity ETL)

Hi @Hari-P, from the IDE, I made a new file called “looker.ini”. Inside that looker.ini file, I declare the following variables 

api_version=4.0
base_url=foo
client_id=foo
client_secret=foo

Hi @Srinivasarao,

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:
1. Use the lookml model explore endpoint to grab all of the column names from an explore in system activity
2. Use this function from BigQuery client to automatically create table with column names

Note: System Activity only runs 1 query at time, so you probably want to space out, and not run all queries at once. 

I am also developing a custom action hub to send a query result to BigQuery directly from Looker UI, and hopefully I can share soon. 

Hi @mattia.mancassola, 

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)