Issue with timestamps in BigQuery when importing to Vertex AI Retail

[tararelan]

I am trying to import my product data into the catalogue of Vertex AI. I have tried to follow the schema given [here][1]. It says the publishTime should be in the Timestamp format (e.g.
"2014-10-02T15:01:23Z" and "2014-10-02T15:01:23.045123456Z").

This is my schema:

[
{
"name": "id",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "type",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "categories",
"type": "STRING",
"mode": "REPEATED"
},
{
"name": "title",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "brands",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "priceInfo",
"type": "RECORD",
"mode": "REQUIRED",
"fields": [
{
"name": "currencyCode",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "price",
"type": "FLOAT",
"mode": "REQUIRED"
}
]
},
{
"name": "rating",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "publishTime",
"type": "TIMESTAMP",
"mode": "NULLABLE"
},
{
"name": "promotions",
"type": "BOOLEAN",
"mode": "NULLABLE"
}
]

And this is my query:

INSERT INTO `project.products.test` (
id,
type,
categories,
title,
brands,
priceInfo,
rating,
publishTime,
promotions
) VALUES (
'IXbgQctuTx9',
'PRIMARY',
ARRAY["Shopping", "Fashion", "Women"],
'GSM W',
'onitsukatiger',
STRUCT("GBP" AS currencyCode, 90 AS price), -- Price info with currency and price
NULL, -- Rating
TIMESTAMP('2023-10-09T00:00:00Z'), -- Publish time with proper format
FALSE -- Promotions
);

However, when I try to import the table to Vertex, I get the following error:

{
"code": 3,
"message": "Invalid value at 'publish_time' (type.googleapis.com/google.protobuf.Timestamp): Field 'publishTime', Illegal timestamp format; timestamps must end with 'Z' or have a valid timezone offset.",
"details": [
{
"@type": "type.googleapis.com/google.rpc.ResourceInfo",
"resourceName": "gs://530498506955_eu_import_product/staging11436364033615815385/retail_products_000000000004.json:1"
}
]
}

I would greatly appreciate if anyone could help me fix this issue.

[1]: https://cloud.google.com/retail/docs/reference/rest/v2/projects.locations.catalogs.branches.products...

[QASIMALOUDAT]

Hello

I am Qasim Aloudat

I am expect there is wrong in the code time with the need you import

[tararelan · Accepted solution]

I realised the problem was with how I was inputting the timestamp - it needs to be in string format, but written as a timestamp.

The schema should be:

[
{
"name": "id",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "type",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "categories",
"type": "STRING",
"mode": "REPEATED"
},
{
"name": "title",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "brands",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "priceInfo",
"type": "RECORD",
"mode": "REQUIRED",
"fields": [
{
"name": "currencyCode",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "price",
"type": "FLOAT",
"mode": "REQUIRED"
}
]
},
{
"name": "rating",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "publishTime",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "promotions",
"type": "BOOLEAN",
"mode": "NULLABLE"
}
]

And when inputting it:

INSERT INTO `project.products.test` (
id,
type,
categories,
title,
brands,
priceInfo,
rating,
publishTime,
promotions
) VALUES (
'IXbgQctuTx9',
'PRIMARY',
ARRAY["Shopping", "Fashion", "Women"],
'GSM W',
'onitsukatiger',
STRUCT("GBP" AS currencyCode, 90 AS price), -- Price info with currency and price
NULL, -- Rating
'2023-10-09T00:00:00Z', -- Publish time with proper format
FALSE -- Promotions
);