ML.GENERATE_TEXT in BigQuery: Setup and a Working Query

If you are searching for ML.GENERATE_TEXT, the function you should type today is AI.GENERATE_TEXT. That is the first thing this page owes you, because every other detail is downstream of getting the name right.

The function is now called AI.GENERATE_TEXT

Google’s generative AI overview for BigQuery documents the current family under the AI. prefix: AI.GENERATE for scalar generation, AI.GENERATE_TEXT as the table-valued form, AI.GENERATE_TABLE for structured output against a supplied schema, and AI.GENERATE_EMBEDDING alongside AI.EMBED. The ML.-prefixed names came from the era when these sat with BigQuery’s classical ML functions, and a great deal of writing — including query snippets that still float around internal wikis — predates the change.

The practical guidance is simple. Write new queries against AI.GENERATE_TEXT. If an existing query uses ML.GENERATE_TEXT and works, it is fine to leave alone, but do not copy the old name into anything new. And when a query fails with an unrecognised-function error, check the prefix before you check anything else — that error is not always as legible as it should be.

A query that runs

This assumes a remote model already exists — if not, creating a remote model is the connection and grant that has to happen first. The table here is a support-ticket table with a free-text body column.

SELECT
  ticket_id,
  ml_generate_text_llm_result AS category,
  ml_generate_text_status      AS status
FROM
  AI.GENERATE_TEXT(
    MODEL `PROJECT_ID.analytics.gemini_flash`,
    (
      SELECT
        ticket_id,
        CONCAT(
          'Classify this support ticket as exactly one of: ',
          'BILLING, BUG, FEATURE_REQUEST, OTHER. ',
          'Reply with the label only.\n\n',
          body
        ) AS prompt
      FROM `PROJECT_ID.analytics.tickets`
      WHERE created_at >= '2026-08-01'
      LIMIT 200
    ),
    STRUCT(
      0.0  AS temperature,
      16   AS max_output_tokens,
      TRUE AS flatten_json_output
    )
  );

Three requirements are load-bearing and none of them is optional. The subquery must produce a column literally named prompt; the function looks for that name and fails if it is absent. Every other column in the subquery is passed through to the output, which is how ticket_id survives — without carrying a key through, you get answers you cannot join back. And the LIMIT is there on purpose: the first run of any generation query should be small, because the difference between a 200-row test and a 2-million-row accident is a bill.

The STRUCT arguments

The third argument is a STRUCT of named settings, written value-first with AS name, which is the BigQuery convention and reads backwards to everyone the first time. Google documents max_output_tokens, top_p, temperature, stop_sequences, ground_with_google_search and safety_settings among them.

• temperature — set it to 0.0 for classification and extraction. A classifier with temperature 0.7 will disagree with itself between runs over the same rows, and you will spend a day looking for a data bug that is a sampling setting.
• max_output_tokens — the direct lever on output cost, which is the expensive half. For a one-word label, 16 is generous. Leaving it at a default of hundreds means paying for a model that decides to explain itself.
• flatten_json_output — when TRUE, the result comes back as a string column instead of the raw JSON response. Set it unless you specifically need the safety ratings or the token counts out of the full payload.
• stop_sequences — an array of strings that end generation. Useful when the model reliably produces the answer and then keeps going.

The output shape, including the error column

The function returns your input columns plus generated ones. With flatten_json_output set, the important two are the result column and a status column that is an empty string on success and an error message on failure.

That status column is the single most important thing on this page. A generation query does not fail when individual rows fail. It returns successfully with a populated status on the affected rows, so a query that runs in 40 seconds and produces two million rows can have 80,000 of them containing nothing but a quota error, and nothing about the job’s state says so. Always check:

CREATE OR REPLACE TABLE `PROJECT_ID.analytics.tickets_classified` AS
SELECT
  ticket_id,
  ml_generate_text_llm_result AS category,
  ml_generate_text_status     AS status
FROM AI.GENERATE_TEXT(
  MODEL `PROJECT_ID.analytics.gemini_flash`,
  (SELECT ticket_id, CONCAT('...', body) AS prompt
   FROM `PROJECT_ID.analytics.tickets`),
  STRUCT(0.0 AS temperature, 16 AS max_output_tokens, TRUE AS flatten_json_output)
);

-- the check that has to run every time
SELECT
  COUNTIF(status = '')                        AS ok,
  COUNTIF(status != '')                       AS failed,
  ANY_VALUE(IF(status != '', status, NULL))   AS example_error
FROM `PROJECT_ID.analytics.tickets_classified`;

Writing to a table rather than reading the results directly is not incidental either. If you select from the function and then decide you want the results again, the second query re-runs every generation and bills for it. Materialise once, query the table afterwards.

Constraining the output to your label set

Nothing in the query above forces the model to return one of your four labels. The prompt asks for a label; the function returns a string. In practice a small fraction comes back as “BILLING.” with a full stop, or “Billing”, or a helpful sentence explaining the choice, and a downstream join on the label silently drops those rows. Treat the column as untrusted input, because it is.

The cheap defence is to normalise and then check membership, keeping the raw value so you can see what actually happened rather than only that something did:

SELECT category_raw, COUNT(*) AS n
FROM (
  SELECT UPPER(TRIM(REGEXP_REPLACE(category, r'[^A-Za-z_ ]', ''))) AS category_raw
  FROM `PROJECT_ID.analytics.tickets_classified`
  WHERE status = ''
)
WHERE category_raw NOT IN ('BILLING','BUG','FEATURE_REQUEST','OTHER')
GROUP BY category_raw
ORDER BY n DESC;

Run that before trusting any aggregate built on the column. If the off-list values are a handful of formatting variants, a normalisation expression fixes them permanently. If they are sentences, the prompt is the problem: adding “Reply with the label only” helps, max_output_tokens set tightly enough that a sentence cannot fit helps more, and a newline in stop_sequences truncates the explanation that follows an otherwise correct first line.

The stronger option is to stop asking for free text at all. AI.GENERATE_TABLE takes an output schema and returns typed columns rather than a string, which moves the constraint from your prompt into the request. It is more setup than a single label deserves, and it is clearly right the moment you want three fields out of one call — a category, a confidence and an extracted entity — because the alternative is parsing JSON out of a string column in SQL, which is exactly as unpleasant as it sounds. It also halves the token bill against making three separate generation passes over the same rows, since the input is sent once.

Running it over a large table

At scale, the failures are quota failures. The calls go against your project’s Vertex AI quota for the model, and BigQuery will happily issue more concurrent requests than that quota allows, which is where a large fraction of populated status columns comes from. Two things help: process in batches with a deterministic partition rather than one enormous query, and raise the quota before the run rather than after — requesting a Vertex AI quota increase covers the second.

-- batch by a hash of the key, so re-running a batch covers the same rows
SELECT ticket_id, CONCAT('...', body) AS prompt
FROM `PROJECT_ID.analytics.tickets`
WHERE MOD(ABS(FARM_FINGERPRINT(ticket_id)), 20) = 0

Then re-run only the rows whose status was non-empty, which is why you kept the column. A retry pass over the failures is a few thousand rows rather than the whole table, and it costs accordingly — the arithmetic for which is in BigQuery ML pricing for remote model calls.