Use Generative AI to get personalized recommendations in an ecommerce application

Objective

In this tutorial, you learn how to:

Costs

This tutorial uses billable components of Google Cloud, including:

  • Spanner
  • Vertex AI

For more information about Spanner costs, see theSpanner pricing page.

For more information about Vertex AI costs, see theVertex AI pricing page.

Create the ecommerce website schema

For this tutorial, we use the following schema and data:

CREATETABLEProducts(idINT64,nameSTRING(MAX),descriptionSTRING(MAX),category_idINT64,)PRIMARYKEY(id);CREATETABLECategories(idINT64,nameSTRING(MAX))PRIMARYKEY(id);CREATETABLEUsers(idINT64,ageINT64,likesSTRING(MAX))PRIMARYKEY(id);INSERTINTOCategories(id,name)VALUES(1,"Toys"),(2,"Tools");INSERTINTOProducts(id,name,description,category_id)VALUES(1,"Plush Bear","Really fluffy. Safe for infants.",1),(2,"Bike","Bike for teenagers.",1),(3,"Drill","Cordless.",2);INSERTINTOUsers(id,age,likes)VALUES(1,30,"DIY"),(2,14,"Toys");

Register a Generative AI model in a Spanner schema

In this tutorial, we use the Vertex AItext-bison modelto provide personalized product recommendations to end customers.To register this model in a Spanner database,execute the following DDLstatement:

CREATEMODELTextBisonINPUT(promptSTRING(MAX))OUTPUT(contentSTRING(MAX))REMOTEOPTIONS(endpoint='//aiplatform.googleapis.com/projects/PROJECT/locations/LOCATION/publishers/google/models/text-bison');

Replace the following:

  • PROJECT: the project ID
  • LOCATION: the region where you are using Vertex AI

Schema discovery and validation isn't available for Generative AImodels. Therefore, you must provideINPUT andOUTPUT clauses thatmatch the model's schema. You can find the full schema of thetext-bisonmodel on the Vertex AIModel API reference page.

As long as both the database and endpoints are in the same project,Spanner should grant appropriate permissionsautomatically. Otherwise, review themodel endpoint access control sectionof theCREATE MODEL reference page.

To verify the model was registered correctly, query it with theML.PREDICT function. The model expects a singleSTRING column namedprompt. You can use aSpanner subqueryto generate theprompt column. TheTextBison modelrequires you to specify amaxOutputTokens model parameter.Other parameters are optional. The Vertex AItext-bison model doesn't support batching, so you must use the@{remote_udf_max_rows_per_rpc=1} parameter to set the batch size to 1.

SELECTcontentFROMML.PREDICT(MODELTextBison,(SELECT"Is 13 prime?"ASprompt),STRUCT(256ASmaxOutputTokens,0.2AStemperature,40astopK,0.95AStopP))@{remote_udf_max_rows_per_rpc=1};+--------------------+|content|+--------------------+|"Yes, 13 is prime"|+--------------------+

Use theTextBison Model to answer customer questions

Generative AI text models can solve a wide array of problems.For example, a user on an ecommerce website might be browsing forproducts that are safe for infants. With a single query, we canpass their question to theTextBison model. All we need to do isprovide relevant context for the question by fetching product detailsfrom the database.

NOTE: Some model answers were edited for brevity.

SELECTproduct_id,product_name,contentFROMML.PREDICT(MODELTextBison,(SELECTproduct.idasproduct_id,product.nameasproduct_name,CONCAT("Is this product safe for infants?","\n","Product Name: ",product.name,"\n","Category Name: ",category.name,"\n","Product Description:",product.description)ASpromptFROMProductsASproductJOINCategoriesAScategoryONproduct.category_id=category.id),STRUCT(100ASmaxOutputTokens))@{remote_udf_max_rows_per_rpc=1};-- The model correctly recommends a Plush Bear as safe for infants.-- Other products are not safe and the model provides justification why.+------------+-----------------+--------------------------------------------------------------------------------------------------+|product_id|product_name|content|+------------+-----------------+--------------------------------------------------------------------------------------------------+|1|"Plush Bear"|"Yes, this product is infant safe. [...] "||||"The product description says that the product is safe for infants. [...]"|+------------+-----------------+--------------------------------------------------------------------------------------------------+|2|"Bike"|"No, this product is not infant safe. [...] "||||"It is not safe for infants because it is too big and heavy for them to use. [...]"|+------------+-----------------+--------------------------------------------------------------------------------------------------+|3|"Drill"|"No, this product is not infant safe. [...]"||||" If an infant were to grab the drill, they could pull it on themselves and cause injury. [...]"|+------------+-----------------+--------------------------------------------------------------------------------------------------+

You can replace the question literal with a query parameter, such as@UserQuestion, if you want to directly populate the parameter with acustomer question. This gives the customer an AI-powered onlineshopping experience.

Provide personalized product recommendations to customers

In addition to product details, we can also add information about thecustomer to theprompt. This lets the model take user preferences intoconsideration so that it can provide fully personalized product recommendations.

SELECTproduct_id,product_name,contentFROMML.PREDICT(MODELTextBison,(SELECTproduct.idasproduct_id,product.nameasproduct_name,CONCAT("Answer with YES or NO only: Is this a good fit for me?","My age:",CAST(user.ageASSTRING),"\n","I like:",user.likes,"\n","Product name: ",product.name,"\n","Category mame: ",category.name,"\n","Product description:",product.description)ASprompt,FROMProductsASproductJOINCategoriesAScategoryONproduct.category_id=category.idJOINUsersASuserONuser.id=1),STRUCT(256ASmaxOutputTokens))@{remote_udf_max_rows_per_rpc=1};-- The model correctly guessed that the user might be interested in a Drill-- as they are interested in DIY.+------------+-----------------+-------------+|product_id|product_name|content|+------------+-----------------+-------------+|1|"Plush Bear"|"NO"|+------------+-----------------+-------------+|2|"Bike"|"NO"|+------------+-----------------+-------------+|3|"Drill"|"YES"|+------------+-----------------+-------------+

To look for a gift for their child, the user can create a profile for theirteenager and see a different list of recommendations:

SELECTproduct_id,product_name,contentFROMML.PREDICT(MODELTextBison,(SELECTproduct.idasproduct_id,product.nameasproduct_name,CONCAT("Answer with YES or NO only: Is this a good fit for me?","\nMy's age:",CAST(user.ageASSTRING),"\nI like:",user.likes,"\nProduct Name: ",product.name,"\nCategory Name: ",category.name,"\nProduct Description:",product.description)ASprompt,FROMProductsASproductJOINCategoriesAScategoryONproduct.category_id=category.idJOINUsersASuserONuser.id=2),STRUCT(40ASmaxOutputTokens))@{remote_udf_max_rows_per_rpc=1};-- The model correctly guesses that a teenager is interested in a Bike,-- but not a plush bear for infants or spicy peppers.+------------+-----------------+---------+|product_id|product_name|content|+------------+-----------------+---------+|1|"Plush Bear"|"NO"|+------------+-----------------+---------+|2|"Bike"|"YES"|+------------+-----------------+---------+|3|"Spicy peppers"|"NO"|+------------+-----------------+---------+

You can add purchase history or other relevant details to the prompt to givethe customer a more customized experience.

Spanner Vertex AI integration helps you assemble complex prompts containing live dataand use them to build AI-enabled applications.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-19 UTC.