ASK: I want to see the president of each country. It is working if I am having single row in table but when I am having multiple rows or more than 1 row then it failing with below shared error.
SQL query
C_BIRTH_COUNTRY is different country name
Select OPENAI_EXT_FUN('Who is the president of '||C_BIRTH_COUNTRY)::VARIANT:choices[0]:text::VARCHAR as response
from country;
Error(When trying to access multiple rows data):
100351 (P0000): Request failed for external function OPENAI_EXT_FUN with remote service error: 400 '{"error": {"message": "'$.prompt' is invalid. Please check the API reference: https://platform.openai.com/docs/api-reference.","type": "invalid_request_error","param": null,"code": null}}'; requests batch-id: 01ad982d-0000-64a5-0005-3c220005d08e:1:2:0:0; request batch size: 10 rows; request retries: 0; response time (last retry): 1.08845s
/* Source Code */
/* Request Function */
CREATE OR REPLACE FUNCTION OPENAI_COMPLETIONS_REQUEST_TRANSLATOR("EVENT" OBJECT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT
AS '
return {"body": {
"model": "text-davinci-003",
"prompt": EVENT.body.data,
"temperature": 0.7,
"max_tokens": 3000,
"top_p": 1,
"frequency_penalty": 0.0,
"presence_penalty": 0.0
}
};';
/* Response Function */
CREATE OR REPLACE FUNCTION OPENAI_COMPLETIONS_RESPONSE_TRANSLATOR("EVENT" OBJECT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT
AS '
let array_of_rows_to_return = [[0, EVENT.body]];
return {"body": {"data": array_of_rows_to_return}};
';
/* External Function */
CREATE OR REPLACE EXTERNAL FUNCTION OPENAI_EXT_FUN("QUESTION" VARCHAR(16777216))
RETURNS VARIANT
API_INTEGRATION = AWS_OPENAI_INTEGRATION
MAX_BATCH_ROWS = 3000
REQUEST_TRANSLATOR = OPENAI_COMPLETIONS_REQUEST_TRANSLATOR
RESPONSE_TRANSLATOR = OPENAI_COMPLETIONS_RESPONSE_TRANSLATOR
AS '<My_END_POINT_URL>';
Below shared query should work find without any error for mutiple rows in a table as well
Select OPENAI_EXT_FUN('Who is the president of '||C_BIRTH_COUNTRY)::VARIANT:choices[0]:text::VARCHAR as response
from country;
Hint: I have find something related to Batching. But not sure how to implement in snowflake.
URL: https://platform.openai.com/docs/guides/rate-limits/error-mitigation
Path: Rate limits/ Error mitigation
