Leveraging Looker's APIs

Looker offers a powerful Application Programming Interface (API) that enables developers to interact programmatically with many of the features available in the Looker platform. This functionality extends the flexibility and usability of Looker, making it possible to automate tasks, integrate with other applications, and even develop new features or applications based on Looker's capabilities.

Looker's API is RESTful, which means it follows the principles of Representational State Transfer (REST). This makes the API easy to use and understand, as it's based on standard HTTP methods, like GET, POST, PUT, and DELETE.

Setting up the API

Before using the API, it's necessary to create an API3 key. This key is used to authenticate your application to the Looker API. You can create an API3 key from Looker's Admin panel. After logging in as an admin, go to the Users page, find your user, and click Edit. Here, you can create a new API3 key by clicking the "Add API3 Key" button.

Once you have your API3 key, you can use it to authenticate your API requests. Looker's API uses token-based authentication. You must first use your API3 key to generate an access token, which you then use to authenticate subsequent API requests. Here's an example using curl:

# Replace <CLIENT_ID> and <CLIENT_SECRET> with your API3 key
$ curl 'https://<YOUR_LOOKER_INSTANCE>/api/3.1/login' \
  --data-urlencode 'client_id=<CLIENT_ID>' \
  --data-urlencode 'client_secret=<CLIENT_SECRET>'

The response includes an access_token which you can use to authenticate your API requests.

Fetching Data with the API

You can fetch data from Looker using the run_inline_query endpoint. This endpoint runs a SQL query and returns the results. The query is defined in LookML, Looker's proprietary modeling language.

Here's an example of how to fetch data from Looker's API using curl:

# Replace <ACCESS_TOKEN>, <MODEL_NAME>, and <VIEW_NAME> with your values
$ curl 'https://<YOUR_LOOKER_INSTANCE>/api/3.1/queries/run/json' \
  -X POST \
  -H 'Authorization: Bearer <ACCESS_TOKEN>' \
  -H 'Content-Type: application/json' \
  -d '{
    "model": "<MODEL_NAME>",
    "view": "<VIEW_NAME>",
    "fields": ["<VIEW_NAME>.field1", "<VIEW_NAME>.field2"],
    "filters": {"<VIEW_NAME>.field1": "value"}
  }'

In this example, <MODEL_NAME> and <VIEW_NAME> refer to the names of a model and view in your Looker project, respectively. <VIEW_NAME>.field1 and <VIEW_NAME>.field2 are the fields you want to include in your query, and "<VIEW_NAME>.field1": "value" is a filter you apply to the query.

Integrating with Other Applications

Looker's API makes it possible to integrate Looker with other applications. For example, you might want to fetch data from Looker and send it to a data warehouse, an application database, or a data visualization tool.

Here's an example of how you might fetch data from Looker and send it to a PostgreSQL database using Python and the psycopg2 library:

import requests
import psycopg2
import json

# Fetch data from Looker
url = 'https://<YOUR_LOOKER_INSTANCE>/api/3.1/queries/run/json'
headers = {'Authorization': 'Bearer <ACCESS_TOKEN>'}
data = {
  'model': '<MODEL_NAME>',
  'view': '<VIEW_NAME>',
  'fields': ['<VIEW_NAME>.field1', '<VIEW_NAME>.field2'],
  'filters': {'<VIEW_NAME>.field1': 'value'}
}
response = requests.post(url, headers=headers, json=data)
data = response.json()

# Send data to PostgreSQL
conn = psycopg2.connect(database='<DB_NAME>', user='<DB_USER>', password='<DB_PASSWORD>', host='<DB_HOST>', port='<DB_PORT>')
cur = conn.cursor()

for row in data:
  # Assume that data is a list of dictionaries and that <VIEW_NAME>.field1 and <VIEW_NAME>.field2 are the keys
  cur.execute("INSERT INTO <TABLE_NAME> (field1, field2) VALUES (%s, %s)", (row['<VIEW_NAME>.field1'], row['<VIEW_NAME>.field2']))

conn.commit()
conn.close()

In this example, <DB_NAME>, <DB_USER>, <DB_PASSWORD>, <DB_HOST>, and <DB_PORT> refer to your PostgreSQL database connection details.

In conclusion, Looker's API provides powerful functionality that allows you to extend Looker's capabilities and integrate with other applications. By understanding and leveraging Looker's API, you can automate tasks, fetch and manipulate data programmatically, and create integrations that suit your specific needs.