Google Cloud Advice AI expects a particular BigQuery desk scheme. Subsequently, now we have to create a desk with the required format and insert the catalog information into it.
Say our catalog desk in BigQuery has the next fields
- title: title of the product
- id: id of the product
- class: assigned class of the product.
- description: description concerning the product
- url: URL of the product on web site
- image_link: publically accessible picture hyperlink of the product.
- metropolis: town by which the product is out there.
Primarily based on the above product catalog particulars the schema for our Advice AI desk will probably be as follows
[
{
"name": "name",
"type": "STRING",
"description": "name of the product",
"mode": "NULLABLE"
},
{
"name": "id",
"type": "STRING",
"description": "id of the product",
"mode": "REQUIRED"
},
{
"name": "type",
"type": "STRING",
"description": "type of the product. Primary or Variant here",
"mode": "NULLABLE"
},
{
"name": "primaryProductId",
"type": "STRING",
"description": "product id here",
"mode": "NULLABLE"
},
{
"name": "collectionMemberIds",
"type": "STRING",
"description": "",
"mode": "REPEATED"
},
{
"name": "gtin",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"name": "categories",
"type": "STRING",
"description": "category here in > format",
"mode": "REPEATED"
},
{
"name": "title",
"type": "STRING",
"description": "title of the product here",
"mode": "REQUIRED"
},
{
"name": "brands",
"type": "STRING",
"description": "brand name here",
"mode": "REPEATED"
},
{
"name": "description",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"name": "languageCode",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"name": "attributes",
"type": "RECORD",
"description": "you can have custom attribites here",
"mode": "REPEATED",
"fields": [
{
"name": "key",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"name": "value",
"type": "RECORD",
"description": "",
"mode": "NULLABLE",
"fields": [
{
"name": "text",
"type": "STRING",
"description": "",
"mode": "REPEATED"
},
{
"name": "numbers",
"type": "FLOAT",
"description": "",
"mode": "REPEATED"
},
{
"name": "searchable",
"type": "BOOLEAN",
"description": "",
"mode": "NULLABLE"
},
{
"name": "indexable",
"type": "BOOLEAN",
"description": "",
"mode": "NULLABLE"
}
]
}
]
},
{
"title": "tags",
"sort": "STRING",
"description": "tags primarily based on the product",
"mode": "REPEATED"
},
{
"title": "priceInfo",
"sort": "RECORD",
"description": "",
"mode": "NULLABLE",
"fields": [
{
"name": "currencyCode",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"name": "price",
"type": "FLOAT",
"description": "",
"mode": "NULLABLE"
},
{
"name": "originalPrice",
"type": "FLOAT",
"description": "",
"mode": "NULLABLE"
},
{
"name": "cost",
"type": "FLOAT",
"description": "",
"mode": "NULLABLE"
},
{
"name": "priceEffectiveTime",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"name": "priceExpireTime",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
}
]
},
{
"title": "score",
"sort": "RECORD",
"description": "",
"mode": "NULLABLE",
"fields": [
{
"name": "ratingCount",
"type": "INTEGER",
"description": "",
"mode": "NULLABLE"
},
{
"name": "averageRating",
"type": "FLOAT",
"description": "",
"mode": "NULLABLE"
},
{
"name": "ratingHistogram",
"type": "INTEGER",
"description": "",
"mode": "REPEATED"
}
]
},
{
"title": "expireTime",
"sort": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"title": "ttl",
"sort": "RECORD",
"description": "",
"mode": "NULLABLE",
"fields": [
{
"name": "seconds",
"type": "INTEGER",
"description": "",
"mode": "NULLABLE"
},
{
"name": "nanos",
"type": "INTEGER",
"description": "",
"mode": "NULLABLE"
}
]
},
{
"title": "availableTime",
"sort": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"title": "availability",
"sort": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"title": "availableQuantity",
"sort": "INTEGER",
"description": "",
"mode": "NULLABLE"
},
{
"title": "fulfillmentInfo",
"sort": "RECORD",
"description": "",
"mode": "REPEATED",
"fields": [
{
"name": "type",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"name": "placeIds",
"type": "STRING",
"description": "",
"mode": "REPEATED"
}
]
},
{
"title": "uri",
"sort": "STRING",
"description": "product url right here",
"mode": "NULLABLE"
},
{
"title": "photographs",
"sort": "RECORD",
"description": "",
"mode": "REPEATED",
"fields": [
{
"name": "uri",
"type": "STRING",
"description": "",
"mode": "REQUIRED"
},
{
"name": "height",
"type": "INTEGER",
"description": "",
"mode": "NULLABLE"
},
{
"name": "width",
"type": "INTEGER",
"description": "",
"mode": "NULLABLE"
}
]
},
{
"title": "viewers",
"sort": "RECORD",
"description": "",
"mode": "NULLABLE",
"fields": [
{
"name": "genders",
"type": "STRING",
"description": "",
"mode": "REPEATED"
},
{
"name": "ageGroups",
"type": "STRING",
"description": "",
"mode": "REPEATED"
}
]
},
{
"title": "colorInfo",
"sort": "RECORD",
"description": "",
"mode": "NULLABLE",
"fields": [
{
"name": "colorFamilies",
"type": "STRING",
"description": "",
"mode": "REPEATED"
},
{
"name": "colors",
"type": "STRING",
"description": "",
"mode": "REPEATED"
}
]
},
{
"title": "sizes",
"sort": "STRING",
"description": "",
"mode": "REPEATED"
},
{
"title": "supplies",
"sort": "STRING",
"description": "",
"mode": "REPEATED"
},
{
"title": "patterns",
"sort": "STRING",
"description": "",
"mode": "REPEATED"
},
{
"title": "circumstances",
"sort": "STRING",
"description": "",
"mode": "REPEATED"
},
{
"title": "retrievableFields",
"sort": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"title": "publishTime",
"sort": "STRING",
"description": "",
"mode": "NULLABLE"
},
{
"title": "promotions",
"sort": "RECORD",
"description": "",
"mode": "REPEATED",
"fields": [
{
"name": "promotionId",
"type": "STRING",
"description": "",
"mode": "NULLABLE"
}
]
}
]
There are a bunch of fields that we are able to set for our catalog. Extra information you’ll be able to produce the higher. For this demonstration objective, I’ll follow the commonest ones. Word nullable fields are optionally available.
The sortarea right here is the place we resolve if the product is a variant or major. I’ll go together with PRIMARY for this text. Read here for more details.
As soon as our desk is prepared we are able to insert the catalog information from the principle desk to this desk utilizing the under question.
insert into `recommendersystem.product_data` (
title,id,sort,primaryProductId,collectionMemberIds,gtin,classes,title,manufacturers,description,languageCode,attributes, tags,
priceInfo,score,expireTime,ttl,availableTime,availability,availableQuantity,fulfillmentInfo, uri, photographs,viewers,colorInfo,sizes,supplies,patterns,circumstances,retrievableFields,publishTime,promotions
)
SELECT
title,
solid(id as string) as id,
"PRIMARY" as sort,
solid(id as string) as primaryProductId,
null as collectionMemberIds,
null as gtin,
array [categories] as classes,
title as title,
arrayBuild a Recommender System Utilizing Google Cloud Advice AI | by muffaddal qutbuddin | Jun, 2023 as manufacturers,
ifnull(description,title) as description,
null as languageCode,
[
struct(
'product_location' as key, STRUCT(array[ifnull(city,"empty")] as textual content, solid(null as ARRAY<FLOAT64>) as numbers, true as searchable, true as indexable ) as worth
)]
as attributes,
ARRAY_CONCAT(
[ifnull(location,"empty")],
[ifnull(categories,"empty")]
) as tags,
null as priceInfo,
null as score,
null as expireTime,
null as ttl,
null as availableTime,
null as availability,
null as availableQuantity,
null as fulfillmentInfo,
url,
array[struct(image_url) as uri, null as height, null as width)] as photographs,
null as viewers,
null as colorInfo,
null as sizes,
null as supplies,
null as patterns,
null as circumstances,
null as retrievableFields,
null as publishTime,
null as promotions
FROM `product.product_details`
As quickly as the information is out there in our new desk we’re able to import them into Advice AI.
Within the Knowledge tab of retail ai in Google Cloud click on Import on the highest left to import the information.
