Skip to main content

Using Capella iQ

Lets see how to use AI to generate SQL++ Queries
For this, we will use the travel-sample bucket and inventory scope.

workbench-iq-ui The UI has 3 panels:

  • The Chat Panel
  • The Prompt Panel
  • The Side Panel: This is common to Query Workbench and has already been discussed previously.

The Prompt Panel​

You can enter your natural language prompt.
In this example this prompt was entered:

How can I select a list of 10 cities with most landmarks?
Also, arrange them in descending order.

Prompt ToolBar​

  • Run Button: Button tom execute prompt. Note that pressing [ENTER] button does not automatically run the prompt.
  • Delete Button: Clears Prompt Panel
  • Send Schema Switch: Send's the schema (same as that shown in Side Panel) to AI

The Chat Panel​

This is Chat result sent back by iQ.

  • Thumbs: Not available currently.
  • SQL Box: Any SQL++ queries suggested by iQ
  • Copy to Query: Button to copy the SQL box contents to Query.

Chat ToolBar​

  • Statistics: A few interesting stats.
  • Delete Button: Clears the chat thread.

Chat Implementation​

Let's discuss how this Chat is implemented.

  • First, when sending your Prompt, choose to send schemas (on by default).
    • This helps Chat produce relevant results
  • The Chat Panel displays the results
  • You can now have more Prompts to fine tune the results if required
    • This sends back the full thread back to iQ with the additional Prompt
    • This means sending more tokens. Remember there is a limit of 4K tokens per Chat thread.
    • So, its a good idea to Clear the Chat thread for new prompts

Excuting the Query​

Once you copy the Query, you can navigate to the Query Tab and execute the copied Query from there: workbench-iq-query This is the normal Query Workbench functionality as discussed previously.

tip

You can choose to favorite this query.
Remember to format the query first with the favorite comments style first.

  • This has been discussed previously in the documentation.

Interesting Queries​

Now, lets put iQ to work and have it generate a few intersting queries for us. For this, let's give it this Prompt:

Can you suggest about 6 interesting queries for the provided schema?
Try not to make it simple queries.
Throw in examples using UNNEST and JOINs.

iQ Replies​

workbench-iq-interesting You can scroll and find all the 6 queries.
You can copy any of the 6 queries to the Query Workbench using the Copy Button
When you come back to to the Ask iQ, the state is maintained, so you can copy and execute the queries as desired.

For the sake of completeness, these are the queries which iQ generated

  1. Find all hotels that offer free breakfast, free internet, and free parking, and list the top 3 public likes for each hotel.
SELECT h.name, h.address, h.city, h.state, ARRAY_AGG(pl) AS top_public_likes
FROM hotel h
UNNEST h.public_likes AS pl
WHERE h.free_breakfast = TRUE AND h.free_internet = TRUE AND h.free_parking = TRUE
GROUP BY h.name, h.address, h.city, h.state
HAVING COUNT(pl) > 0
LIMIT 3;
  1. Retrieve all airports in a specific country along with the airlines that operate from those airports.
SELECT a.airportname, a.city, a.country, ARRAY_AGG(al.name) AS airlines_operating
FROM airport a
JOIN airline al ON a.country = al.country
GROUP BY a.airportname, a.city, a.country;
  1. List all landmarks in a city along with any hotels in the same city that have a vacancy.
SELECT l.name AS landmark_name, l.address AS landmark_address, h.name AS hotel_name, h.address AS hotel_address
FROM landmark l
JOIN hotel h ON l.city = h.city
WHERE h.vacancy = TRUE;
  1. Get a list of hotels with their average overall rating from reviews.
SELECT h.name, h.address, AVG(r.ratings.Overall) AS average_rating
FROM hotel h
UNNEST h.reviews AS r
GROUP BY h.name, h.address;
  1. Find all routes from a specific airport, including the airline information and the schedule for each route.
SELECT r.sourceairport, r.destinationairport, r.distance, al.name AS airline_name, s.schedule
FROM route r
JOIN airline al ON r.airlineid = al.id
UNNEST r.schedule AS s
WHERE r.sourceairport = "JFK";
  1. Retrieve all hotels that have been reviewed by a specific author and list the details of those reviews.
SELECT h.name, h.address, r.*
FROM hotel h
UNNEST h.reviews AS r
WHERE r.author = "John Doe";
warning

Caution: Responses generated by iQ are not guranteed!
Always run and check them!