How to set up GenAI x Looker extension under 15 minutes | Explore Assistant

Introduction

This blog is part of a series of blogs on the Looker Opensource GenAi Extensions. In this series, we will have How-to blogs — like this one — which cover Step-by-step setup guides after our trials and errors with the newly released extension packages, in some other posts we will cover the Why (value added) of these extensions to your organization’s data stack. The resources for this guide are the Google released github repo , the documentation of various technologies, and my fellow superstar Joon Engineers. The goal of this guide is to:

• Give you a basic understanding of what the Explore Assistant is.
• Give you a detailed walkthrough on how to deploy this in your own Looker instance with an emphasis on simplicity, maintainability, and scalability.

As this is a very detailed deployment guide it made sense to explore various Use Cases in a different blog.

What is the Explore Assistant?

The Explore Assistant allows users to generate Looks using natural language inputs. Simply put, anyone can generate reports by chatting with your Enterprise Dataplatform. Under the hood, the Explore assistant utilizes the generative capabilities of an LLM and all the context Lookers Semantic model to power up the self-service capabilities.

What is powerful is that because of the semantic model in LookML, there is no need for the LLM to read actual data, it will generate the Explore result on metadata only and pass it on to the user with all established access and security protocols in place. Now, users are empowered to ask questions and extract answers directly from the database in a governed, secured, and scalable way.

What does Explore Assistant do?

• Customizable for organizational use cases
• Saves queries in the browser’s local storage and quick access to previous queries
• Insight Summarization
• Open-source, use any LLM model of choice
• Uses the business context already available in LookML
• Applies all security settings as defined in Looker Admin Portal

What Explore Assistant does under the hood?

This extension is built to help your average non-SQL business users communicate with Looker using natural language to generate visuals by themselves. Under the hood, this extension uses Terraform to enable necessary APIs, grant permissions on assets, and create BigQuery dataset that stores LLM’s examples for your specific Looker Explore. As developers, you will need to decide on which explore for your business use case, modify examples input and output if needed. Then, you set up the Looker extension frontend using npm and Looker project connection to deploy it via Looker platform.

Here is a graph which show the bird-eye view for Looker Explore Assistant setup:

Prerequisite Knowledge

• Looker extension
• Terraform
• Cloud Shell
• Vertex AI & Bigquery
• GCP IAM

Setup Instructions

Open Cloud shell by clicking on the Activate Cloud shell on the top right of your monitor:

Download a copy of this repository to your machine:

git clone https://github.com/looker-open-source/looker-explore-assistant.git

Click on Open Editor and navigate to the repository you have just downloaded.

After the step above, your screen should look like this:

Repository structure

looker-explore-assistant
├── explore-assistant-backend
│ └── terraform
├── explore-assistant-examples
│ ├── examples.json
│ ├── load_examples.py
│ ├── README.md
│ ├── refinement_examples.json
│ └── requirements.txt
├── explore-assistant-extension
│ ├── src
│ ├── babel.config.js
│ ├── manifest.lkml
│ ├── package-lock.json
│ ├── package.json
│ ├── README.md
│ ├── tsconfig.json
│ ├── webpack.config.js
│ ├── webpack.develop.js
│ └── webpack.prod.js
└── README.md

The repository contains three main folders we will be using in our setup:

1. A folder named explore-assistant-backend: This is to set up the backend of the extension

• terraform: the folder to trigger the service deployed on BigQuery

2. A folder named explore-assistant-examples: This is the examples and refinement samples we will upload to BigQuery to fine-tune the extension

• .json files: contains the examples and refinements
• .py files: the code to upload the examples and refinements to BigQuery

3. A folder named explore-assistant-extension: This is to run the frontend of the extension on Nodejs

Backend Setup

I. Granting Permission to your account

This is a crucial part for the extension to run smoothly and keep the permission at minimum.

For this backend setup, your account needs the following roles:

1. BigQuery Connection Admin
2. Service Account Admin
3. Service Account Token Creator
4. Storage Admin
5. Service Usage Consumer
6. Secret Manager Admin

II. Step-by-step setup

This Terraform configuration sets up a backend for the Looker Explore Assistant on GCP, using the Gemini Pro model from Vertex AI. There are 2 approaches to setting up the extension, BigQuery and Cloudrun Backend, in this post I will guide you through the BigQuery approach, the CloudRun approach allows for more customization — not covered in this blog and is more advanced.

Step 1

In your Cloud Shell, open a new terminal by Navigation bar > Terminal > New Terminal:

In your terminal, navigate to thelooker-explore-assistant folder:

cd looker-explore-assistant

Then run this command:

openssl rand -base64 32 > .vertex_cf_auth_token

From your terminal, navigate to the backend folder:

cd explore-assistant-backend/terraform

The above command will take you to the explore-assistant-backend folder for the backend setup:

Step 2

• Setting variables in variables.tfvars:

The purpose of this step is to specify the variables in variables.tfvars which will then be used by Terraform to manage infrastructure. You can refer to the documentation for Terraform variables . Create a new variables.tf vars in terraform folder with the following content:

deployment_region = “asia-southeast1” (repleace with your gcp_deployment_region)
project_id = “joon-sandbox” (replace with your project_id)
use_bigquery_backend=1
use_cloud_function_backend=0

• Running Terraform by entering these commands into your Cloud Shell terminal

export TF_VAR_project_id= “joon-sandbox” (replace with your project_id)
export TF_VAR_use_bigquery_backend=1
export TF_VAR_use_cloud_function_backend=0
terraform init
terraform plan
terraform apply

When prompted with Enter a value (yes/no) , enter yes

When prompted with GCP Project ID, fill in your project ID

When prompted with Authorize window, do authorize

Step 3

Check the Service Account that has been created by opening the file terraform.tfstate in the terraform folder. This file contains the infrastructure that Terraform manages

Scroll down to around line 33, look for the Service Account with pattern looker-explore-assistant-sa@. Write down this service account

Next, you need to create a key for this service account to authenticate it. From your GCP interface, go to the Navigation menu on the left hand, then scroll down to IAM & Admin > Service Accounts:

In the list of the service accounts belonging to your project, find the service account you wrote down earlier. You will find that it has no key yet, click on it then switch to tab KEYS, select Add key > Create new key

Pick JSON for key type and save that key. We will use the key later.

Step 4

This step is to check and make sure the necessary resources are created. Firstly, switch to the BigQuery dataset for the examples to make sure the LLM model has been created. If that is the case then you can skip this step.

If that is not the case then switch back to the Cloud Shell, and navigate to the terraform.tfstate file in the terraform folder. Find the command that creates the model explore_assistant_llm and run it directly in Big Query (it should be around line 465):

Once the query has been run, you should be able to see the model in your dataset like this:

Created Resources:

• A Google BigQuery dataset and table for storing examples
• A Google BigQuery connection and a Gemini Pro model

Reference

Input and Output Examples Setup

This step is to generate and upload JSON examples to Big Query. The example’s purpose is to fine-tune the model and return better results. It assumes that you already have a Looker instance available with Looker models and explores configured.

In your Cloud shell, from the root folder, navigate to the example folder:

cd looker-explore-assistant/explore-assistant-examples

Start with installing all dependencies using a virtual environment:

python3 -m venv .venv
source .venv/bin/activate
cd ./explore-assistant-examples
pip3 install -r requirements.txt

Your terminal should look like thipython3 -m venv .venv
source .venv/bin/activate
cd ./explore-assistant-examples
pip3 install -r requirements.txts:

Next, upload the examples using these two commands in Cloud Shell terminal:

python load_examples.py — project_id joon-sandbox (fill in your project id here) — explore_id looker_genai_project:payment (fill in your explore id here) — table_id explore_assistant_examples — json_file examples.json

python load_examples.py — project_id joon-sandbox (fill in your project id here) — explore_id looker_genai_project:payment (fill in your explore id here) — table_id explore_assistant_refinement_examples — json_file refinement_examples.json

The script accepts several command line flags to specify the details required for loading data into Big Query:

• project_id : Required. The GCP project you are using where your BigQuery dataset resides
• explore_id : Required. This is the explore_id for the explore you are going to use and explore insights with the extension. Insert using format: looker_project_id::explore_id

After running the script, the result should look like this:

Now that you have finally added the examples of input and output for the extension, let’s move on to the final setup.

Reference

Frontend Setup

I. Granting Permission to your account

For this frontend setup, your account needs the following role:

• Looker Admin

II. Step-by-step setup

This stage is to deploy the Explore Assistant with the Big Query backend to generate the Looker Explore URLs based on Natural Language. It assumes that you already have a Looker instance available with Looker models and explores configured.

Step 1

• In Looker, create a new BigQuery connection that will allow you to get the examples you uploaded in from Big Query in the previous section. You will use that connection name as input in step 2 below. First, go to Looker Navigation > Admin > Connections then click on Add Connection:

• Input as follows, replace Name with your desired name for the connection and joon-sandbox with your project id:

After that, upload the json key file created in the Backend setup | Step 3 to the connection via the button Upload file and test to make sure the connection is working.

Step 2

• In your Cloud shell, navigate to the extension folder:

cd looker-explore-assistant/explore-assistant-extension

• Create new .env file the explore-assistant-extension folder with the following contents, this file defines the extension’s variables during deployment:

LOOKER_MODEL= test_phuong_genai_tmp1 (replace with your Looker project’s model)

LOOKER_EXPLORE = payment (replace with your Looker project’s explore)

VERTEX_BIGQUERY_LOOKER_CONNECTION_NAME= phuong_test_genai_conn2 (replace with the connection you created in )

VERTEX_BIGQUERY_MODEL_ID= explore_assistant.explore_assistant_llm (this is the LLM you created in this )

In your Cloud shell, run this command

npm install

• Next, we will use npm run build to create a bundle.js file which is then used to deploy the extension. There is another option which is npm run start but we strongly recommend the first approach for ease of use:

npm run build

Step 3

• You will notice a new folder dist has been created in the folder explore-assistant-extension. Drag and drop the bundle.js file from dist folder in your Cloud Shell Editor to your Looker project folder (do not forget to toggle on the Development mode in Looker).

• Then in the same Looker project folder, create a manifest.lkml with this content:

application: explore_assistant {
label: “Explore Assistant”
# url: “https://localhost:8080/bundle.js"
file: “bundle.js”
entitlements: {
core_api_methods: [“lookml_model_explore”,”create_sql_query”,”run_sql_query”,”run_query”,”create_query”]
navigation: yes
use_embeds: yes
use_iframes: yes
new_window: yes
new_window_external_urls: [“https://developers.generativeai.google/*"]
local_storage: yes
# external_api_urls: [“cloud function url”]
}
}

• Validate your LookML, commit your changes, and deploy them to production
• Exit development mode on Looker UI to verify the extension
• Finally, navigate to the Extension on the left Navigation Tab to find the Explore Assistant extension ready to use. In case you do not have Extension settings enabled, you can refer to this documentation

Note: In case the extension do not work from on your Looker., you can debug the error by your browser. In your browser, right click to Inspect > Network tab, then re-run the extension to check and resolve the error.

Conclusion

Congratulations, now you can experiment with the extension just like in the video I shared at the beginning of the blog! There can be a few adjustments to make in the Setup steps, which will affect your output and user experience and that’s where some effort is needed. Currently, this extension only works for one Explore at a time but that is expected to change in the future. If you want to try out this feature in your Looker instance, feel free to reach out to Joon Solutions for help and you can browse to understand more about applicable use cases in business. For additional tools to enhance your data consumption, explore our great blogs on Vertex AI and Dashboard Summarization.