"Function not found: ML.GENERATE_TEXT" - Unable to use ML.GENERATE_TEXT in BigQuery

[riku_lfn]

Hello Google Cloud Community,

I am trying to use the ML.GENERATE_TEXT function in BigQuery to classify search queries using the Gemini model from Vertex AI. However, I am encountering the following error:

Function not found: ML.GENERATE_TEXT at [6:3]

What I am trying to achieve
I want to classify search queries based on their intent into categories like "Do", "Know", "Go", "Buy", and "Other" using the Gemini model (gemini-1.5-flash-002). Below is the SQL query I am using:

SELECT
campaign,
group,
query,
criterion,
ML.GENERATE_TEXT(
MODEL_NAME = 'projects/adrepo-430310/locations/us/publishers/google/models/gemini-1.5-flash-002',
PROMPT = CONCAT(
'以下のtextをintentに分類しなさい。intentのみ出力すること。\n\n',
'intent:\n',
'Do 何らかのアクションをとりたいという意図を持っている\n',
'Know 情報を知りたいという意図を持っている\n',
'Go 自分の目的とするWebサイトに行きたい、という意図を持っている\n',
'Buy 特定の物、商品などを買いたいという意図を持っている\n',
'Other 分類に迷うもの\n\n',
'text: "', query, '"\n',
'intent: '
),
PARAMETERS = STRUCT(
0.2 AS temperature,
50 AS max_output_tokens
)
) AS intent
FROM
`adrepo-430310.adrepo.BG_query_ML_test`;

What I have tried so far
Enabled APIs:

Verified that both the Vertex AI API and the BigQuery Connection API are enabled for my project.
External Connection:

Created an external connection in BigQuery and ensured that the region matches the model's location (US).
Connection ID: projects/adrepo-430310/locations/us/connections/vertex_US

Questions
What might be causing the Function not found: ML.GENERATE_TEXT error?
Are there additional settings, permissions, or configurations I need to enable?
How can I verify if my project is properly set up to use experimental features or the Gemini model in BigQuery?
Any guidance or suggestions would be greatly appreciated. Thank you!

[MJane]

Hi @riku_lfn,

Welcome to Google Cloud Community!

The error Function not found: ML.GENERATE_TEXT indicates that the BigQuery ML function you're trying to use is not recognized by the system. 

Here are potential troubleshooting tips that might resolve the issue: 

Check Function Availability - Ensure that the  function is available in your BigQuery environment. This function is part of BigQuery ML and might not be enabled by default.

Check Permissions- Ensure that your BigQuery service account (or user) has the necessary permissions for accessing both Vertex AI and the established connection. 

Verify Syntax for Function Call - Ensure that the syntax of your SQL query is correct.

In addition as per documentation :

Note : Certain Gemini in BigQuery features in Preview are part of the trusted tester program. To request access to these features, an administrator must complete the Gemini in BigQuery Pre-GA Sign-up form. Gemini in BigQuery pre-GA feature access is enabled periodically in batches.

---

For more detailed information regarding how to use the ML.GENERATE_TEXT function in BigQuery you can read this documentation.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.