Mantieni tutto organizzato con le raccolte
Salva e classifica i contenuti in base alle tue preferenze.
Gestire gli errori di quota chiamando ML.GENERATE_TEXT in modo iterativo
Questo tutorial mostra come utilizzare la stored procedure pubblica bqutil.procedure.bqml_generate_text di BigQuery per scorrere le chiamate alla funzione ML.GENERATE_TEXT.
Chiamando la funzione in modo iterativo, puoi risolvere eventuali errori riprovabili che si verificano a causa del superamento delle quote e dei limiti applicati alla funzione.
Per esaminare il codice sorgente della procedura archiviata bqutil.procedure.bqml_generate_text
in GitHub, consulta
bqml_generate_text.sqlx.
Per ulteriori informazioni sui parametri e sull'utilizzo della stored procedure, consulta il
file README.
Questo tutorial ti guiderà attraverso le seguenti attività:
Iterando le chiamate alla funzione ML.GENERATE_TEXT, utilizzando il modello remoto e la tabella di dati pubblici bigquery-public-data.bbc_news.fulltext con la stored procedure bqutil.procedure.bqml_generate_text.
Autorizzazioni obbligatorie
Per eseguire questo tutorial, devi disporre dei seguenti ruoli IAM (Identity and Access Management):
Crea e utilizza set di dati, connessioni e modelli BigQuery:
Amministratore BigQuery (roles/bigquery.admin).
Concedi le autorizzazioni al account di servizio della connessione: Project IAM Admin
(roles/resourcemanager.projectIamAdmin).
Questi ruoli predefiniti contengono le autorizzazioni necessarie per eseguire le attività descritte in questo documento. Per vedere quali sono esattamente le autorizzazioni richieste, espandi la sezione
Autorizzazioni obbligatorie:
Autorizzazioni obbligatorie
Crea un set di dati: bigquery.datasets.create
Crea, delega e utilizza una connessione:
bigquery.connections.*
Imposta la connessione predefinita: bigquery.config.*
Imposta le autorizzazioni dell'account di servizio:
resourcemanager.projects.getIamPolicy e
resourcemanager.projects.setIamPolicy
Il completamento della query richiede diversi secondi, dopodiché il modello generate_text
viene visualizzato nel set di dati sample nel riquadro Explorer. Poiché la
query utilizza un'istruzione CREATE MODEL per creare un modello, non sono presenti risultati
della query.
Esegui la stored procedure
Esegui la stored procedure bqutil.procedure.bqml_generate_text,
che scorre le chiamate alla funzione ML.GENERATE_TEXT
utilizzando il modello sample.generate_text e la
tabella dei dati pubblici bigquery-public-data.bbc_news.fulltext:
Nella console Google Cloud , vai alla pagina BigQuery.
Sostituisci PROJECT_ID con l'ID progetto del progetto che utilizzi per questo tutorial.
La stored procedure crea una tabella sample.news_generated_text per
contenere l'output della funzione ML.GENERATE_TEXT.
Al termine dell'esecuzione della query, verifica che non siano presenti righe
nella tabella sample.news_generated_text che contengono un errore riprovabile.
Nell'editor di query, esegui la seguente istruzione:
[[["Facile da capire","easyToUnderstand","thumb-up"],["Il problema è stato risolto","solvedMyProblem","thumb-up"],["Altra","otherUp","thumb-up"]],[["Difficile da capire","hardToUnderstand","thumb-down"],["Informazioni o codice di esempio errati","incorrectInformationOrSampleCode","thumb-down"],["Mancano le informazioni o gli esempi di cui ho bisogno","missingTheInformationSamplesINeed","thumb-down"],["Problema di traduzione","translationIssue","thumb-down"],["Altra","otherDown","thumb-down"]],["Ultimo aggiornamento 2025-09-04 UTC."],[[["\u003cp\u003eThis tutorial demonstrates how to use the \u003ccode\u003ebqutil.procedure.bqml_generate_text\u003c/code\u003e stored procedure to iteratively call the \u003ccode\u003eML.GENERATE_TEXT\u003c/code\u003e function in BigQuery, which is useful for managing quota limits and retryable errors.\u003c/p\u003e\n"],["\u003cp\u003eThe process involves creating a remote model over a \u003ccode\u003egemini-1.5-flash-002\u003c/code\u003e model, establishing necessary connections and permissions, and then using the stored procedure to process data from a public dataset.\u003c/p\u003e\n"],["\u003cp\u003eProper permissions, including \u003ccode\u003ebigquery.datasets.create\u003c/code\u003e, \u003ccode\u003ebigquery.connections.create\u003c/code\u003e, and \u003ccode\u003eresourcemanager.projects.setIamPolicy\u003c/code\u003e, are required for creating datasets, connections, and managing service accounts, with additional permissions necessary for model creation and inference.\u003c/p\u003e\n"],["\u003cp\u003eUtilizing the \u003ccode\u003ebqutil.procedure.bqml_generate_text\u003c/code\u003e stored procedure results in a table that contains the output of \u003ccode\u003eML.GENERATE_TEXT\u003c/code\u003e, and this table can be queried to ensure no retryable errors occurred during processing.\u003c/p\u003e\n"],["\u003cp\u003eThere are costs associated with using BigQuery ML and Vertex AI, and the tutorial provides direction on where to learn more about pricing and how to use the pricing calculator to generate estimates.\u003c/p\u003e\n"]]],[],null,["Handle quota errors by calling ML.GENERATE_TEXT iteratively\n\nThis tutorial shows you how to use the BigQuery\n`bqutil.procedure.bqml_generate_text` public stored procedure to iterate\nthrough calls to the\n[`ML.GENERATE_TEXT` function](/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-text).\nCalling the function iteratively lets you address any retryable errors that occur\ndue to exceeding the\n[quotas and limits](/bigquery/quotas#cloud_ai_service_functions) that apply to\nthe function.\n\nTo review the source code for the `bqutil.procedure.bqml_generate_text`\nstored procedure in GitHub, see\n[`bqml_generate_text.sqlx`](https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/stored_procedures/definitions/bqml_generate_text.sqlx).\nFor more information about the stored procedure parameters and usage, see the\n[README file](https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/stored_procedures/README.md#bqml_generate_text-source_table-string-target_table-string-ml_model-string-prompt_column-string-key_columns-array-options_string-string).\n\nThis tutorial guides you through the following tasks:\n\n- Creating a [remote model over a `gemini-2.0-flash` model](/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-remote-model).\n- Iterating through calls to the `ML.GENERATE_TEXT` function, using the remote model and the `bigquery-public-data.bbc_news.fulltext` public data table with the `bqutil.procedure.bqml_generate_text` stored procedure.\n\nRequired permissions\n\nTo run this tutorial, you need the following Identity and Access Management (IAM)\nroles:\n\n- Create and use BigQuery datasets, connections, and models: BigQuery Admin (`roles/bigquery.admin`).\n- Grant permissions to the connection's service account: Project IAM Admin (`roles/resourcemanager.projectIamAdmin`).\n\nThese predefined roles contain the permissions required to perform the tasks in\nthis document. To see the exact permissions that are required, expand the\n**Required permissions** section: \n\nRequired permissions\n\n- Create a dataset: `bigquery.datasets.create`\n- Create, delegate, and use a connection: `bigquery.connections.*`\n- Set the default connection: `bigquery.config.*`\n- Set service account permissions: `resourcemanager.projects.getIamPolicy` and `resourcemanager.projects.setIamPolicy`\n- Create a model and run inference:\n - `bigquery.jobs.create`\n - `bigquery.models.create`\n - `bigquery.models.getData`\n - `bigquery.models.updateData`\n - `bigquery.models.updateMetadata`\n\nYou might also be able to get these permissions with\n[custom roles](/iam/docs/creating-custom-roles) or other\n[predefined roles](/iam/docs/understanding-roles).\n\nCosts\n\n\nIn this document, you use the following billable components of Google Cloud:\n\n\n- **BigQuery ML**: You incur costs for the data that you process in BigQuery.\n- **Vertex AI**: You incur costs for calls to the Vertex AI model.\n\n\nTo generate a cost estimate based on your projected usage,\nuse the [pricing calculator](/products/calculator). \nNew Google Cloud users might be eligible for a [free trial](/free). \n\n\u003cbr /\u003e\n\nFor more information about BigQuery pricing, see\n[BigQuery pricing](/bigquery/pricing).\n\nFor more information about Vertex AI pricing, see\n[Vertex AI pricing](/vertex-ai/generative-ai/pricing).\n\nBefore you begin\n\n1. In the Google Cloud console, on the project selector page,\n select or create a Google Cloud project.\n\n | **Note**: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.\n\n [Go to project selector](https://console.cloud.google.com/projectselector2/home/dashboard)\n2.\n [Verify that billing is enabled for your Google Cloud project](/billing/docs/how-to/verify-billing-enabled#confirm_billing_is_enabled_on_a_project).\n\n3.\n\n\n Enable the BigQuery, BigQuery Connection, and Vertex AI APIs.\n\n\n [Enable the APIs](https://console.cloud.google.com/flows/enableapi?apiid=bigquery.googleapis.com,bigqueryconnection.googleapis.com,aiplatform.googleapis.com)\n\nCreate a dataset\n\nCreate a BigQuery dataset to store your models and sample data:\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to the **BigQuery** page](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** pane, click your project name.\n\n3. Click more_vert **View actions \\\u003e Create dataset**.\n\n4. On the **Create dataset** page, do the following:\n\n 1. For **Dataset ID** , enter `sample`.\n\n 2. For **Location type** , select **Multi-region** , and then select\n **US (multiple regions in United States)**.\n\n 3. Leave the remaining default settings as they are, and click\n **Create dataset**.\n\nCreate the text generation model\n\nCreate a remote model that represents a hosted Vertex AI\n`gemini-2.0-flash` model:\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the query editor, run the following statement:\n\n ```googlesql\n CREATE OR REPLACE MODEL `sample.generate_text`\n REMOTE WITH CONNECTION DEFAULT\n OPTIONS (ENDPOINT = 'gemini-2.0-flash');\n ```\n\n The query takes several seconds to complete, after which the `generate_text`\n model appears in the `sample` dataset in the **Explorer** pane. Because the\n query uses a `CREATE MODEL` statement to create a model, there are no query\n results.\n\nRun the stored procedure\n\nRun the `bqutil.procedure.bqml_generate_text` stored procedure,\nwhich iterates through calls to the `ML.GENERATE_TEXT` function\nusing the `sample.generate_text` model and the\n`bigquery-public-data.bbc_news.fulltext` public data table:\n\n1. In the Google Cloud console, go to the **BigQuery** page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the query editor, run the following statement:\n\n ```googlesql\n CALL `bqutil.procedure.bqml_generate_text`(\n \"bigquery-public-data.bbc_news.fulltext\", -- source table\n \"\u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e.sample.news_generated_text\", -- destination table\n \"\u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e.sample.generate_text\", -- model\n \"body\", -- content column\n [\"filename\"], -- key columns\n '{}' -- optional arguments\n );\n ```\n\n Replace \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e with the project ID of the\n project you are using for this tutorial.\n\n The stored procedure creates a `sample.news_generated_text` table to\n contain the output of the `ML.GENERATE_TEXT` function.\n3. When the query is finished running, confirm that there are no rows\n in the `sample.news_generated_text` table that contain a retryable error.\n In the query editor, run the following statement:\n\n ```googlesql\n SELECT *\n FROM `sample.news_generated_text`\n WHERE ml_generate_text_status LIKE '%A retryable error occurred%';\n ```\n\n The query returns the message `No data to display`.\n\nClean up\n\n| **Caution** : Deleting a project has the following effects:\n|\n| - **Everything in the project is deleted.** If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.\n| - **Custom project IDs are lost.** When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an `appspot.com` URL, delete selected resources inside the project instead of deleting the whole project.\n|\n|\n| If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects\n| can help you avoid exceeding project quota limits.\n1. In the Google Cloud console, go to the **Manage resources** page.\n\n [Go to Manage resources](https://console.cloud.google.com/iam-admin/projects)\n2. In the project list, select the project that you want to delete, and then click **Delete**.\n3. In the dialog, type the project ID, and then click **Shut down** to delete the project.\n\n\u003cbr /\u003e"]]
