LinkedIn
Twitter
Hi SachinS
if I understand correctly, you want to be able to write back to BigQuery. I had put together a guide a while back, happy to share that with you. Please be aware: this has been created a while ago, so some things might have changed from wording/functionality, but it should explain the way on how to set this up!
The principle
- Add a dimension in Looker with a ‘cell’ action
- This action includes a form where the user adds notes
- This form is sent to a cloud function
- The cloud function inserts the form data into a BQ table
- This BQ table is joined to the Looker data model
Note on joins
- Assume you are annotating information that takes place for a specific geography and specific date
- Each note would exist on the grain of one note per geo + day
- Therefore the notes table needs to join to the original dataset on geo + day as primary key
- Therefore when you write to BigQuery each row must contain geo + day + note text
LookML for action
BigQuery Schema
- Create a table called 'annotations' in the ‘public’ schema
- Can change these but would need to update python code in the cloud function below
- Columns are ‘geo_id’, ‘date’ and ‘notes’
- If we use the column names and data types exactly we can use a helpful insert_rows_json method in Python to insert the new rows into BQ
Cloud Function (Python 3)
- Make a service account with the ability to invoke cloud functions and write to BigQuery
- Give ‘allUsers’ the ability to invoke cloud functions so we can run this without authentication
- Requires you to put the project somewhere non-google-owned (like a joonix domain)
- Make a new Cloud function using Python 3
- Must be invoked by HTTP without authentication
- Make sure it’s executed by the service account you just made
- No need for any special parameters or settings on the function
- Add the code below. Note that there are two files to add - a requirements.txt file and a main.py file once complete make note of the URL required to invoke the function - we need this in the action LookML above
- Make sure the ‘entry point’ function is the name of the function you wrote (i.e. ‘annotate’ here)
## requirements.txt
google-cloud-bigquery==1.27.2
## main.py
from google.cloud import bigquery
client = bigquery.Client()
def annotate(request):
"""Update a BQ table from a Looker action"""
request_json = request.get_json()
geo = request_json['data']['geo_id']
date = request_json['data']['date']
notes = request_json['form_params']['notes']
data = [{'geo_id': geo, 'date': date, 'notes': notes}]
table_ref = client.dataset('public').table('annotations') # Check schema + table name
table = client.get_table(table_ref)
try:
r = client.insert_rows_json(table, data)
return {"inserted_data": data[0],
"looker": {
"success": true,
"refresh_query": true
}
}
except Exception as e:
print(e) # Errors will get logged in Stackdriver
LookML for join
```
join: annotations { # Or whatever the table name is
relationship: one_to_one
type: left_outer
sql_on: ${annotations.geo_id} = ${table.geo_id} AND ${annotations.date_raw} = ${table.date_raw} ;;
}
```
