Sentiment Chef Agent App with Google Cloud and MongoDB Atlas

1
--Gemini Remote Model Creation
2
CREATE MODEL `restaurant_review.gemini_remote_model`
3
REMOTE WITH CONNECTION `us-central1.bq_llm_connection`
4
OPTIONS(ENDPOINT = 'gemini-1.5-pro');
5
6
--Embeddings Model Creation
7
create or replace model restaurant_review.embeddings_model 
8
REMOTE WITH CONNECTION `us-central1.bq_llm_connection`
9
OPTIONS(ENDPOINT = 'textembedding-gecko@latest');

1
select id, name, address, description, description as content from(  
2
    SELECT id, name, address, IFNULL (prompt || IFNULL('The grades received as feedback and score against each grade: ' || STRING_AGG(overall_grade_and_score), ' '), 'None') as description FROM ( 
3
select DISTINCT a._id id, a.name, IFNULL(a.address.building, ' ') || ', ' || IFNULL(a.address.street,' ') || ', ' || IFNULL(a.address.zipcode, 0) || ', ' || IFNULL(a.borough, ' ') address, 
4
'This is a restaurant named: ' || a.name || ' situated at: ' || IFNULL(a.address.building, ' ') || ', ' || IFNULL(a.address.street,' ') || ', ' || IFNULL(a.address.zipcode, 0) || ', ' || ifnull(a.borough, ' ') || '. They serve the cuisine: ' || a.cuisine || '.' || ifnull('Some additional information on this restaurant: ' || a.summary || '.', ' ') || ifnull('Some additional information on this restaurant: ' || b.text || '.', ' ') as prompt,  grades.grade || ': ' || SUM(grades.score) OVER (PARTITION BY a._id, grades.grade) as overall_grade_and_score from restaurant_review.restaurants a left outer join restaurant_review.raw_reviews b
5
on a._id = b.restaurant_id, UNNEST(grades) AS grades ) GROUP BY id, name, address, prompt
6
 )

1
CREATE TABLE restaurant_review.restaurant_summary as (
2
SELECT id, name, address, description, ml_generate_embedding_result 
3
FROM ML.GENERATE_EMBEDDING(
4
 MODEL `restaurant_review.embeddings_model`,
5
 ( select id, name, address, description, description as content from(  
6
    SELECT id, name, address, IFNULL (prompt || IFNULL('The grades received as feedback and score against each grade: ' || STRING_AGG(overall_grade_and_score), ' '), 'None') as description FROM ( 
7
select DISTINCT a._id id, a.name, IFNULL(a.address.building, ' ') || ', ' || IFNULL(a.address.street,' ') || ', ' || IFNULL(a.address.zipcode, 0) || ', ' || IFNULL(a.borough, ' ') address, 
8
'This is a restaurant named: ' || a.name || ' situated at: ' || IFNULL(a.address.building, ' ') || ', ' || IFNULL(a.address.street,' ') || ', ' || IFNULL(a.address.zipcode, 0) || ', ' || ifnull(a.borough, ' ') || '. They serve the cuisine: ' || a.cuisine || '.' || ifnull('Some additional information on this restaurant: ' || a.summary || '.', ' ') || ifnull('Some additional information on this restaurant: ' || b.text || '.', ' ') as prompt,  grades.grade || ': ' || SUM(grades.score) OVER (PARTITION BY a._id, grades.grade) as overall_grade_and_score from restaurant_review.restaurants a left outer join restaurant_review.raw_reviews b
9
on a._id = b.restaurant_id, UNNEST(grades) AS grades ) GROUP BY id, name, address, prompt
10
 ))));

1
with user_query as (
2
   select 'Indian cuisine with the best rating' as query
3
)
4
select name, description, response,score,perc
5
 from (
6
SELECT name, description, ml_generate_text_llm_result as response,
7
SAFE_CAST(JSON_VALUE(replace(replace(ml_generate_text_llm_result,'```',''),'json',''), '$.match_score') AS FLOAT64) as score,
8
SAFE_CAST(JSON_VALUE(replace(replace(ml_generate_text_llm_result,'```',''),'json',''), '$.percentage_match') AS FLOAT64) as perc
9
FROM ML.GENERATE_TEXT(MODEL `abis-345004.restaurant_review.gemini_remote_model`,
10
   (select name, description, distance, concat('Your objective is to respond with a JSON object of 2 values (percentage_match and match_score) by comparing the result_description: ', description,' against the user_search_query: ', (SELECT query AS content from user_query), ' Your goal is to result: 1) Percentage of match of response to the user_search_query (percentage_match) and 2) Score for the match on a scale of 1 to 10  (match_score). Also remember that the grade and score value in the result_description represents customer feedback for the restaurant and a grade on a scale of A to Z with the corresponding score being the number of votes on it. You can use this information as the restaurant score for comparing against the user_search_query in addition to other details you have in the result_description field. Do not return any other text. Just the JSON object with 2 values - percentage_match and match_score on a scale of 1 to 10.') as prompt from (
11
select  query, base.id id, base.name name, base.description description, distance
12
from VECTOR_SEARCH(
13
 TABLE restaurant_review.restaurant_summary,
14
 'ml_generate_embedding_result',
15
(SELECT text_embedding, content 
16
     FROM ML.GENERATE_TEXT_EMBEDDING(
17
         MODEL `restaurant_review.embeddings_model`,
18
         (SELECT query AS content from user_query)) ),top_k => 25) )  X ),
19
STRUCT(
20
 TRUE AS flatten_json_output))
21
) order by perc desc;

1
openapi: 3.0.0
2
info:
3
  title: Restaurant Agent from BigQuery
4
  description: A Cloud Function to search for restaurants based on user search text.
5
  version: 1.0.0
6
servers:
7
  - url: https://us-central1-YOUR_PROJECT.cloudfunctions.net/YOUR_FUNCTION
8
paths:
9
  /:
10
    post:
11
      summary: Find matching restaurants based on search text.
12
      operationId: restaurantSearch
13
      requestBody:
14
        description: JSON object containing the search text.
15
        required: true
16
        content:
17
          application/json:
18
            schema:
19
              type: object
20
              properties:
21
                search:
22
                  type: string
23
                  description: The user's search query for restaurant matching.
24
      responses:
25
        '200':
26
          description: Successful response with a JSON array of matching restaurants.
27
          content:
28
            application/json:
29
              schema:
30
                type: array
31
                items:
32
                  type: object
33
                  properties:
34
                    name:
35
                      type: string
36
                      description: Restaurant name.
37
                    description:
38
                      type: string
39
                      description: Restaurant description, including location and cuisine.
40
                    grades:
41
                      type: object
42
                      description: Grades received as feedback and score against each grade.
43
                      properties:
44
                        A:
45
                          type: integer
46
                          description: Number of A grades.
47
                        B:
48
                          type: integer
49
                          description: Number of B grades.
50
                        P:
51
                          type: integer
52
                          description: Number of P grades.
53
                        C:
54
                          type: integer
55
                          description: Number of C grades.
56
        '500':
57
          description: Internal server error.