Adding images to your Looks and dashboards looks great, and is easy to do with the html
LookML parameter. In this article we take things a step further by using Google’s API to perform image searches for us.
Adding dozens or even hundreds of image links manually can be quite tedious. Take for example this simple video game console lookup dashboard:
It’s worth noting here that there are certain websites that will do this for you, depending on your data. For example, if you have a dimension with Pokémon numbers, you could use an online Pokédex that takes in the numbers. The LookML might look like this:
dimension: image {
sql: CONCAT("https://assets.pokemon.com/assets/cms2/img/pokedex/full/", ${number}, ".png") ;;
html: <img src='{{ value }}'> ;; }
If you don’t have such a website available, that’s OK! We can gather all the resources needed to run a Google image search from Looker in a few minutes and at no cost.
In order to to achieve this, we’ll take advantage of Google’s robust API offerings. First, we’ll build up an API call to get a link to our desired image, then we will take its JSON response and serve up an image.
The actual API call we’ll use in this example looks like this:
https://www.googleapis.com/customsearch/v1?q=[QUERY_PARAMETERS]&cx=[CUSTOM_SEARCH_ENGINE_ID]&searchType=image&key=[GOOGLE_API_KEY]&num=1&fields=items%2Flink
This endpoint will search for image results for the given query parameters, and return a link to the first image in a Google Image search. If you already have a custom search engine and Google API key, great! You can skip the next two parts.
First we need a Google Custom Search Engine (CSE) to tell the API how it should search the web.
Next we need to get an API key from Google.
Now we have all the components to make our API call!
https://www.googleapis.com/customsearch/v1?q=[QUERY_PARAMETERS]&cx=[CUSTOM_SEARCH_ENGINE_ID]&searchType=image&key=[GOOGLE_API_KEY]&num=1&fields=items%2Flink
If you run this endpoint with the values filled in, you’ll get back a json blob with a link to your image.
The last step is to take that link and serve it up as an image. There are several ways to do this; I decided to write a simple php script and host it on a free Heroku instance (setup instructions here). If you’d like, you can see the script here:
<?php
//First set query parameters or fill with default
if(isset($_GET['q']))
$q = urlencode($_GET['q']);
else
$q = 'sandvich';
//Call the API with given query parameters
$response = file_get_contents('https://www.googleapis.com/customsearch/v1?q='.$q.'&cx=[CUSTOM_SEARCH_ENGINE_ID]&searchType=image&key=[GOOGLE_API_KEY]&num=1&fields=items%2Flink');
//Decode API response to get image URL
$url = (((json_decode($response))->items)[0])->link;
// Display the image
header('Content-type: image/png');
echo file_get_contents($url);
?>
This allows me to call the Heroku address with query parameters and get back an image. In my example, the LookML looks like this:
dimension: console_image {
sql: ${name} ;;
html: <img src="http://[MY_HEROKU_INSTANCE].herokuapp.com/api.php?q={{value | url_param_escape }}" />
;; }
Here name
is the dimension that has the name of the video game console.
That’s all there is to it! Leave a comment if I can elaborate on any steps for you or if you’ve implemented something similar yourself!