Restez organisé à l'aide des collections
Enregistrez et classez les contenus selon vos préférences.
Gérer les erreurs de quota en appelant ML.GENERATE_TEXT de manière itérative
Ce tutoriel explique comment utiliser la procédure stockée publique BigQuery bqutil.procedure.bqml_generate_text pour effectuer une itération par le biais d'appels à la fonction ML.GENERATE_TEXT.
L'appel de la fonction de manière itérative vous permet de résoudre les erreurs renouvelables qui se produisent en raison du dépassement des quotas et des limites qui s'appliquent à la fonction.
Pour examiner le code source de la procédure stockée bqutil.procedure.bqml_generate_text dans GitHub, consultez bqml_generate_text.sqlx.
Pour en savoir plus sur les paramètres et l'utilisation de la procédure stockée, consultez le fichier README.
Ce tutoriel vous guide à travers les tâches suivantes :
Effectuer des itérations par le biais d'appels à la fonction ML.GENERATE_TEXT, à l'aide du modèle distant et de la table de données publiques bigquery-public-data.bbc_news.fulltext avec la procédure stockée bqutil.procedure.bqml_generate_text.
Autorisations requises
Pour exécuter ce tutoriel, vous devez disposer des rôles IAM (Identity and Access Management) suivants :
Créer et utiliser des ensembles de données, des connexions et des modèles BigQuery : administrateur BigQuery (roles/bigquery.admin).
Accordez des autorisations au compte de service de la connexion : Administrateur IAM du projet (roles/resourcemanager.projectIamAdmin).
Ces rôles prédéfinis contiennent les autorisations requises pour effectuer les tâches décrites dans ce document. Pour afficher les autorisations exactes requises, développez la section Autorisations requises :
Autorisations requises
Créez un ensemble de données : bigquery.datasets.create
Créer, déléguer et utiliser une connexion :
bigquery.connections.*
Définissez la connexion par défaut : bigquery.config.*
Définissez les autorisations du compte de service :
resourcemanager.projects.getIamPolicy et
resourcemanager.projects.setIamPolicy.
L'exécution de la requête prend plusieurs secondes, après quoi le modèle generate_text apparaît dans l'ensemble de données sample dans le volet Explorateur. Étant donné que la requête utilise une instruction CREATE MODEL pour créer un modèle, il n'y a aucun résultat de requête.
Exécuter la procédure stockée
Exécutez la procédure stockée bqutil.procedure.bqml_generate_text, qui effectue une itération par le biais d'appels à la fonction ML.GENERATE_TEXT à l'aide du modèle sample.generate_text et de la table de données publiques bigquery-public-data.bbc_news.fulltext :
Dans la console Google Cloud , accédez à la page BigQuery.
Remplacez PROJECT_ID par l'ID du projet que vous utilisez pour ce tutoriel.
La procédure stockée crée une table sample.news_generated_text pour contenir le résultat de la fonction ML.GENERATE_TEXT.
Une fois la requête exécutée, vérifiez qu'aucune ligne de la table sample.news_generated_text ne contient d'erreur pouvant être corrigée.
Dans l'éditeur de requête, exécutez l'instruction suivante :
In the project list, select the project that you
want to delete, and then click Delete.
In the dialog, type the project ID, and then click
Shut down to delete the project.
Sauf indication contraire, le contenu de cette page est régi par une licence Creative Commons Attribution 4.0, et les échantillons de code sont régis par une licence Apache 2.0. Pour en savoir plus, consultez les Règles du site Google Developers. Java est une marque déposée d'Oracle et/ou de ses sociétés affiliées.
Dernière mise à jour le 2025/09/04 (UTC).
[[["Facile à comprendre","easyToUnderstand","thumb-up"],["J'ai pu résoudre mon problème","solvedMyProblem","thumb-up"],["Autre","otherUp","thumb-up"]],[["Difficile à comprendre","hardToUnderstand","thumb-down"],["Informations ou exemple de code incorrects","incorrectInformationOrSampleCode","thumb-down"],["Il n'y a pas l'information/les exemples dont j'ai besoin","missingTheInformationSamplesINeed","thumb-down"],["Problème de traduction","translationIssue","thumb-down"],["Autre","otherDown","thumb-down"]],["Dernière mise à jour le 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"]]
