Build a chat app with Conversational Analytics and Looker

1. Before you begin

This codelab will guide you through using the Conversational Analytics API (CA API) with Looker to build your own chat application for querying data. To get the most value, you should be familiar with BigQuery, Looker, LLMs and agents, and Python.

What you'll learn

After following this codelab, you'll have learned:

• How to use Conversational Analytics with Looker
• How to set up and use the Conversational Analytics Python quickstart app (and how the app works)
• The benefits of using Looker for semantic modeling with Conversational Analytics

What you'll need

To complete this codelab, you'll need:

• A Looker instance with the ASC Demographic Data Looker Block installed
• API and developer access to your Looker instance
• A local environment with Python v3.11+, Git, and gcloud installed.
• A cloud project with these IAM roles set up for your user account:
• roles/bigquery.dataViewer BigQuery Data Viewer
• roles/bigquery.user BigQuery User
• roles/looker.instanceUser Looker Instance User

2. Build and try out your chat app with BigQuery

Let's first set up and try out the Conversational Analytics quickstart app with BigQuery as a data source.

Authenticate and enable Cloud APIs

1. Authenticate locally with your user account:

gcloud auth login

2. Enable Cloud project APIs. Please replace YOUR_PROJECT_ID with the ID of your Google Cloud project:

gcloud services enable geminidataanalytics.googleapis.com bigquery.googleapis.com cloudaicompanion.googleapis.com --project=YOUR_PROJECT_ID

3. Set application default credentials (ADC) and billing:

gcloud auth application-default login
gcloud auth application-default set-quota-project YOUR_PROJECT_ID

Set up the quickstart app

4. Clone the quickstart github repo into your local environment. See the following example command below:

git clone "https://github.com/looker-open-source/ca-api-quickstarts"

5. Navigate into the ca-api-quickstarts directory you've cloned:

cd ca-api-quickstarts

6. Create a secrets.toml file in the .streamlit directory. YOUR_LOOKER_CLIENT_ID and YOUR_LOOKER_CLIENT_SECRET should be your Looker client ID and Looker client secret from your Looker instance. The file contents should be as follows:

[cloud]
project_id = "YOUR_PROJECT_ID"

[looker]
client_id = "YOUR_LOOKER_CLIENT_ID"
client_secret = "YOUR_LOOKER_CLIENT_SECRET"

You can use the following example command to create the file. Make sure to replace the values with your project ID, client ID, and client secret:

cat > .streamlit/secrets.toml <<'EOF'
[cloud]
project_id = "YOUR_PROJECT_ID"

[looker]
client_id = "YOUR_LOOKER_CLIENT_ID"
client_secret = "YOUR_LOOKER_CLIENT_SECRET"
EOF

7. Install the quickstart app's Python requirements:

pip install -r requirements.txt

8. Run the quickstart app:

streamlit run app.py

You are running a custom built Streamlit app, an open-source Python app framework that lets you prototype interactive data apps. Check out the Streamlit documentation to learn more.

9. Open https://localhost:8501 in your browser of choice.

Try out the quickstart app

You are now on the quickstart app's agents page. You have no data agents available.

What is a data AI agent? AI agents are software systems that use AI to pursue goals and complete tasks on behalf of users. They show reasoning, planning, and memory and have a level of autonomy to make decisions, learn, and adapt. In our use case we use AI agents to understand our data. An agent's behavior is dictated by the system instructions defined for it. Check out What are AI agents? for more info..

10. Now let's create a data agent that speaks like a pirate in the Create Agent form:
11. Set the Display name field:

Pirate BQ Agent

2. Set the System Instructions field:

You are a pirate. Answer questions like a pirate

3. Select the BigQuery option.
4. Set the Project field:

bigquery-public-data

5. Set the Dataset field:

google_trends

6. Set the Table field:

top_rising_terms

11. Select Create agent.

The quickstart app's Create Agent form calls the CA API's create dataAgent endpoint through gcloud's Python SDK, passing in the agent's system instructions and data source. The app uses the gcloud Python SDK for all CA API calls.

12. Now let's navigate to the chat page through the left main navigation. Our newly created agent is available to chat. Let's start a chat by adding a prompt/message: "Hello, how are you?"
13. Now let's learn a little bit about this dataset: "What's this dataset about?"

What is happening when you send a prompt (or message) in chat? The app sends your message as a payload to CA API's chat endpoint. The CA API behind the scenes uses the data agent's system instructions and info from the data source as context for your prompt and sends the prompt and context to Gemini. Then the CA API streams back Gemini's response.

14. Ask another question - something like this:
15. "What [Enter State] locations are in the dataset?"
16. "Can you give me the data for the last 2 weeks for [City, State?]"
17. "Can you create a visualization?"

You've just had a multi-turn conversation with your data agent and your BigQuery data in your own chat application.

3. Model and explore your data in Looker

Now let's model and explore the same BigQuery data in Looker.

Query BigQuery census data

What happens when we connect to a larger, relational dataset? First we'll create a new agent.

1. Navigate back to the agents page's Create Agent form.
2. Set the Display name field:

BQ Census Agent

2. Set the System Instructions field:

You are really excited about census data, you love learning about the demographics in your area because you want to come up with marketing campaigns for your company

3. Select the BigQuery option.
4. Set the Project field:

bigquery-public-data

5. Set the Dataset field:

census_bureau_acs

6. Set the Table field:

blockgroup_2018_5yr

2. Select Create agent.
3. Ask the newly created agent both of these questions:
4. "What data do you have?"
5. "Can you show me a preview of my data"?

When you ask the agent for a preview of the data, you can see that the table doesn't have a human readable location associated with it, just a geographic identifier (geo_id).

Use Looker to model the census data

Now we are going to use a LookML model to define the BigQuery census data. We will solve the issues we just saw previously.

4. Log in to your Looker instance
5. Navigate to your ACS demographic data block files by navigating to the left main navigation, selecting Develop, and then selecting marketplace_acs_census-v2.
6. Let's take a look at a Looker view. Open the imported_project directory, the acs_census-v2 directory, and the geography directory.
7. Finally, open the cbsa.view file.
8. On line 17, we can see that the LookML is creating a derived table. In other words, this code is creating a new, combined table by joining two public BigQuery datasets, one with census data and another with geographic boundary data. LookML allows us to join on multiple datasets by creating a persistent derived table to query.

A persistent derived table, or PDT, is a table created by Looker and stored in your database (in this case, BigQuery). It's a materialized view of a complex query. Instead of running the same expensive query every time a user requests a report, Looker can more efficiently query the pre-computed PDT to increase performance and decrease query time.

Additionally, the join is defined in versionable code (LookML) instead of system instructions, making agent development more scalable. With LookML, you can define specific assumptions and constraints around your data to support your users as they chat with your data through a data agent.

Explore the Looker modeled census data

Let's run a quick query in an Explore to demonstrate that we have one centralized data source to query.

9. In the left main navigation menu, select Explore, then Census Data Block, then State and County.
10. Select the measure Education > Associates Degree.
11. Select the following dimensions:
12. Geography > Block Group
13. Geography > County > County Name
14. Geography > State > State.
15. Run the query (top right) and you will now see that those Geography IDs (called "Block Group") are now associated with human readable strings.
16. Open the visualization tab and you will see this data in a map.

4. Try out the chat app with Looker

Now let's create a data agent for Looker.

1. Navigate to your quickstart app's agent page's Create Agent form:
2. Set the Display name field:

Cartoon character data analyst

2. Set the System Instructions field:

You are [fill in your favorite cartoon character, Bugs Bunny is a favorite]. Answer questions like that cartoon character

3. Select the Looker option.
4. Set the Url field to your looker instance's URL: "https://..."
5. Set the Model field:

data_block_acs_bigquery

6. Set the Explore field:

cbsa

7. Select Create agent.
8. Navigate to the chat page and select the newly created agent.
9. Let's start a chat by adding a prompt/message: "Hello, how are you?"
10. Let's ask the agent to describe the dataset: "What data do you have available?"
11. Let's ask a few more questions:
12. "Which county in NY has the biggest population?"
13. "What about the most graduates from high school per capita?"
14. "Can you give me the population by gender and state?"
15. "I would like to see a bar chart of the top 10 states with the most population?"

See that bar chart visualization in your conversation? The CA API sent a message to the app with a vega chart specification. The vega chart specification contains the visualization's data and configurations like colors, axis, chart type, etc. With the specification, the Streamlit app rendered it as an interactive vega chart for you to view and interact with. The CA API enables you to not only query but also visualize your data with data agents, by producing standard visualization configurations for easy visualizing.

5. What else can the quickstart app do?

You've now:

• Set up the chat app locally
• Created a BigQuery data agent and a Looker data agent
• Held conversations with your data agents
• Learned about the benefits of Looker semantic modeling as context for your chats

Let's try out the other capabilities of the quickstart app:

1. Reload the quickstart app's web page and navigate to the chat page.
2. You can see your past conversations with all their respective messages from both your BigQuery and Looker agents. The app's frontend is not storing the past convos or messages, but is fetching them from the CA API.

The CA API provides endpoints for all past conversations and their messages. The app fetches past conversations from the CA API's list conversations endpoint. The app then fetches all the past messages of conversation from the list messages endpoint. When you send a message to a conversation, the CA API can associate the messages with a conversation to include all the past messages in the conversation as context for more useful multi-turn conversation.

3. Now navigate to the agents page and select your Looker agent from the list.
4. You can update the agent's configurations or delete the agent if you'd like. No agents are stored in the app's frontend.

The app calls CA API's provided endpoints to list, create, update, and delete agents. The app makes use of CA API's ability to store and manage its agents. The CA API also provides other operations on agents too!

6. Optional challenges and further exploration

If you'd like, you can take this opportunity to familiarize yourself with Conversational Analytics and the quickstart app. Or give yourself a challenge to modify the quickstart app. You can try out some of the following suggestions and challenges or skip ahead to the final section.

Familiarize yourself more with the CA API and quickstart app

• Learn more about in depth system instructions to control your agent's behavior.
• Set up another data agent pointing to a new BigQuery dataset.
• Install a new Looker block in your Looker instance, create an agent, and point it to a new Looker dataset.

Quickstart app modification challenges

• Are you familiar with Looker embedding? If so, try embedding a Looker dashboard inside the quickstart app. Consider using Streamlit's iframe component.
• Find a public dataset containing video URLs, and update the app's chat rendering logic to show a video in chat.

7. Conclusion and takeaways

You made it to the end! You've built a chat app, created data agents, and chatted with your data all with the Conversational Analytics API. Let's review some key capabilities and concepts!

Conversational Analytics capabilities

We've covered a lot of the Conversational Analytics capabilities like the ability to:

• Create, update, list, get, and delete data agents.
• Point data agents to either BigQuery or Looker as a data source.
• Create, list, and get conversations and their respective messages.
• Produce vega chart specifications for immersive visualization in conversations.
• Use past messages in a conversation as useful context for future messages.

Benefits of using Looker with the CA API

Looker provides centralized, testable, and governable semantic modeling of your data. Combined with Conversational Analytics, Looker provides a scalable way to provide context for your data agents.

What's next

• Check out the Conversational Analytics documentation for more guides, features, and use cases.
• Stay up to date with and contribute to the quickstart application repository.