Ce document décrit les tâches communes à tous les types de routines de BigQuery.
Autorisations
Pour référencer une routine dans une requête SQL, vous devez disposer de l'autorisation bigquery.routines.get. Pour accorder l'accès aux routines, vous pouvez attribuer un rôle IAM avec l'autorisation bigquery.routines.get sur l'ensemble de données ou sur la routine individuelle. Accorder l'accès au niveau de l'ensemble de données permet au compte principal d'accéder à toutes les routines de l'ensemble de données. Pour en savoir plus, consultez Contrôler l'accès aux ressources avec IAM.
Par défaut, vous avez également besoin d'une autorisation pour accéder aux ressources auxquelles la routine fait référence, telles que les tables ou les vues. Dans les fonctions définies par l'utilisateur et les fonctions table, vous pouvez autoriser la fonction à accéder à ces ressources pour le compte de l'appelant. Pour en savoir plus, consultez la section Fonctions autorisées.
Créer une routine
Pour créer une routine, vous devez disposer de l'autorisation bigquery.routines.create.
SQL
Selon le type de routine, exécutez l'une des instructions LDD suivantes:
DATASET : nom d'un ensemble de données dans votre projet.
ROUTINE_NAME : Nom de la routine.
Exemple :
bqshow--routinemydataset.AddFourAndDivide
Id Routine Type Language Signature Definition Creation Time Last Modified Time
------------------ ----------------- ---------- ------------------------------- ------------- ----------------- --------------------
AddFourAndDivide SCALAR_FUNCTION SQL (x INT64, y INT64) -> FLOAT64 (x + 4) / y 05 May 01:12:03 05 May 01:12:03
API
Appelez la méthode routines.get avec l'ID de l'ensemble de données et le nom de la routine. Le corps de la routine est renvoyé dans l'objet Routine.
Supprimer une routine
Pour supprimer une routine, vous devez disposer de l'autorisation bigquery.routines.delete.
Console
Dans la console Google Cloud , ouvrez la page "BigQuery".
DATASET : nom d'un ensemble de données dans votre projet.
ROUTINE_NAME : Nom de la routine.
Exemple :
bq rm --routine mydataset.AddFourAndDivide
API
Appelez la méthode routines.delete avec l'ID de l'ensemble de données et le nom de la routine.
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\u003eBigQuery routines include stored procedures, user-defined functions (UDFs), and table functions, each with specific creation and management methods.\u003c/p\u003e\n"],["\u003cp\u003eExecuting, listing, and viewing the body of a routine requires specific permissions such as \u003ccode\u003ebigquery.routines.get\u003c/code\u003e, \u003ccode\u003ebigquery.routines.list\u003c/code\u003e, and the user needs to have permissions to access any resources referenced by the routine, such as tables or views.\u003c/p\u003e\n"],["\u003cp\u003eRoutines can be created, viewed, listed, and deleted using SQL DDL statements, the \u003ccode\u003ebq\u003c/code\u003e command-line tool, or through the BigQuery API using a method call.\u003c/p\u003e\n"],["\u003cp\u003eTo list routines, you can use the Google Cloud console, query the \u003ccode\u003eINFORMATION_SCHEMA.ROUTINES\u003c/code\u003e view, use the \u003ccode\u003ebq ls\u003c/code\u003e command with the \u003ccode\u003e--routines\u003c/code\u003e flag, or call the \u003ccode\u003eroutines.list\u003c/code\u003e API method.\u003c/p\u003e\n"],["\u003cp\u003eTo view the body of a routine you can use the Google Cloud console, query the \u003ccode\u003eINFORMATION_SCHEMA.ROUTINES\u003c/code\u003e view, use the \u003ccode\u003ebq show\u003c/code\u003e command with the \u003ccode\u003e--routine\u003c/code\u003e flag, or use the \u003ccode\u003eroutines.get\u003c/code\u003e method.\u003c/p\u003e\n"]]],[],null,["# Manage routines\n===============\n\nIn BigQuery, *routines* are a resource type that includes the\nfollowing:\n\n- [Stored procedures](/bigquery/docs/procedures#writing_a_procedure).\n- [User-defined functions](/bigquery/docs/user-defined-functions) (UDFs), including [remote functions](/bigquery/docs/remote-functions).\n- [Table functions](/bigquery/docs/table-functions).\n\nThis document describes tasks that are common to all routine types in\nBigQuery.\n\nPermissions\n-----------\n\nTo reference a routine in a SQL query, you must have the `bigquery.routines.get`\npermission. To grant access to routines you can grant an IAM role\nwith the `bigquery.routines.get` permission on the dataset or on the individual\nroutine. Granting access at the dataset level gives the principal access to all\nroutines in the dataset. For more information, see\n[Control access to resources with IAM](/bigquery/docs/control-access-to-resources-iam).\n\nBy default, you also need permission to access any resources that the routine\nreferences, such as tables or views. For UDFs and table functions, you can\n*authorize* the function to access those resources on the caller's behalf. For\nmore information, see\n[Authorized functions](/bigquery/docs/authorized-functions).\n\nCreate a routine\n----------------\n\nTo create a routine, you must have the `bigquery.routines.create` permission. \n\n### SQL\n\nDepending on the routine type, run one of the following DDL statements:\n\n- [Stored procedure: `CREATE PROCEDURE`](/bigquery/docs/reference/standard-sql/data-definition-language#create_procedure)\n- [User-defined function: `CREATE FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#create_function_statement)\n- [Table function: `CREATE TABLE FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#create_table_function_statement)\n\n### API\n\nCall the [`routines.insert` method](/bigquery/docs/reference/rest/v2/routines/insert)\nwith a defined\n[`Routine` resource](/bigquery/docs/reference/rest/v2/routines#Routine).\n\nList routines\n-------------\n\nTo list the routines in a dataset, you must have the `bigquery.routines.get` and\n`bigquery.routines.list` permissions. \n\n### Console\n\n1. In the Google Cloud console, open the BigQuery page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** panel, expand your project and select a dataset.\n\n3. Expand the **Routines** list.\n\n### SQL\n\nQuery the [`INFORMATION_SCHEMA.ROUTINES` view](/bigquery/docs/information-schema-routines):\n\n\u003cbr /\u003e\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, enter the following statement:\n\n ```googlesql\n SELECT\n COLUMN_LIST\n FROM\n { DATASET | REGION }.INFORMATION_SCHEMA.ROUTINES;\n ```\n\n \u003cbr /\u003e\n\n3. Click play_circle **Run**.\n\n \u003cbr /\u003e\n\nFor more information about how to run queries, see [Run an interactive query](/bigquery/docs/running-queries#queries).\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eCOLUMN_LIST\u003c/var\u003e: a comma-separated list of columns from the [`INFORMATION_SCHEMA.ROUTINES` view](/bigquery/docs/information-schema-routines).\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: a [region qualifier](/bigquery/docs/information-schema-intro#region_qualifier).\n\nExample: \n\n```googlesql\nSELECT\n routine_name, routine_type, routine_body\nFROM\n mydataset.INFORMATION_SCHEMA.ROUTINES;\n``` \n\n +------------------+----------------+--------------+\n | routine_name | routine_type | routine_body |\n +------------------+----------------+--------------+\n | AddFourAndDivide | FUNCTION | SQL |\n | create_customer | PROCEDURE | SQL |\n | names_by_year | TABLE FUNCTION | SQL |\n +------------------+----------------+--------------+\n\n### bq\n\nUse the [`bq ls` command](/bigquery/docs/reference/bq-cli-reference#bq_ls)\nwith the `--routines` flag: \n\n```bash\nbq ls --routines DATASET\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n\nExample: \n\n```bash\nbq ls --routines mydataset\n``` \n\n Id Routine Type Language Creation Time Last Modified Time\n ------------------ ----------------------- ---------- ----------------- --------------------\n AddFourAndDivide SCALAR_FUNCTION SQL 05 May 01:12:03 05 May 01:12:03\n create_customer PROCEDURE SQL 21 Apr 19:55:51 21 Apr 19:55:51\n names_by_year TABLE_VALUED_FUNCTION SQL 01 Sep 22:59:17 01 Sep 22:59:17\n\n### API\n\nCall the [`routines.list` method](/bigquery/docs/reference/rest/v2/routines/list)\nwith the dataset ID.\n\nView the body of a routine\n--------------------------\n\nTo view the body of a routine, you must have the `bigquery.routines.get` permission. \n\n### Console\n\n1. In the Google Cloud console, open the BigQuery page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** panel, expand your project and select a dataset.\n\n3. Expand the **Routines** list.\n\n4. Select the routine. The body of the routine is listed under **Routine\n query**.\n\n### SQL\n\nSelect the `routine_definition` column of the\n[`INFORMATION_SCHEMA.ROUTINES` view](/bigquery/docs/information-schema-routines):\n\n\u003cbr /\u003e\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, enter the following statement:\n\n ```googlesql\n SELECT\n routine_definition\n FROM\n { DATASET | REGION }.INFORMATION_SCHEMA.ROUTINES\n WHERE\n routine_name = ROUTINE_NAME;\n ```\n\n \u003cbr /\u003e\n\n3. Click play_circle **Run**.\n\n \u003cbr /\u003e\n\nFor more information about how to run queries, see [Run an interactive query](/bigquery/docs/running-queries#queries).\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: a [region qualifier](/bigquery/docs/information-schema-intro#region_qualifier).\n- \u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e: the name of the routine.\n\nExample: \n\n```googlesql\nSELECT\n routine_definition\nFROM\n mydataset.INFORMATION_SCHEMA.ROUTINES\nWHERE\n routine_name = 'AddFourAndDivide';\n``` \n\n +--------------------+\n | routine_definition |\n +--------------------+\n | (x + 4) / y |\n +--------------------+\n\n### bq\n\nUse the [`bq show` command](/bigquery/docs/reference/bq-cli-reference#bq_show)\nwith the `--routine` flag: \n\n```bash\nbq show --routine DATASET.ROUTINE_NAME\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n- \u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e: the name of the routine.\n\nExample: \n\n```bash\nbq show --routine mydataset.AddFourAndDivide\n``` \n\n Id Routine Type Language Signature Definition Creation Time Last Modified Time\n ------------------ ----------------- ---------- ------------------------------- ------------- ----------------- --------------------\n AddFourAndDivide SCALAR_FUNCTION SQL (x INT64, y INT64) -\u003e FLOAT64 (x + 4) / y 05 May 01:12:03 05 May 01:12:03\n\n### API\n\nCall the [`routines.get` method](/bigquery/docs/reference/rest/v2/routines/get)\nwith the dataset ID and the name of the routine. The body of the\nroutine is returned in the\n[`Routine` object](/bigquery/docs/reference/rest/v2/routines#Routine).\n\nDelete a routine\n----------------\n\nTo delete a routine, you must have the `bigquery.routines.delete` permission. \n\n### Console\n\n1. In the Google Cloud console, open the BigQuery page.\n\n [Go to BigQuery](https://console.cloud.google.com/bigquery)\n2. In the **Explorer** panel, expand your project and select a dataset.\n\n3. Expand the **Routines** list.\n\n4. Select the routine.\n\n5. Expand the\n more_vert\n **Actions** option and click **Delete**.\n\n6. Type `\"delete\"` in the dialog, then click **Delete** to confirm.\n\n### SQL\n\nDepending on the routine type, run one of the following DDL statements:\n\n- [Stored procedure: `DROP PROCEDURE`](/bigquery/docs/reference/standard-sql/data-definition-language#drop_procedure_statement)\n- [User-defined function: `DROP FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#drop_function_statement)\n- [Table function: `DROP TABLE FUNCTION`](/bigquery/docs/reference/standard-sql/data-definition-language#drop_table_function)\n\nExample: \n\n DROP FUNCTION IF EXISTS mydataset.AddFourAndDivide\n\n### bq\n\nUse the [`bq rm` command](/bigquery/docs/reference/bq-cli-reference#bq_rm)\nwith the `--routine` flag: \n\n```bash\nbq rm --routine DATASET.ROUTINE_NAME\n```\n\nReplace the following:\n\n- \u003cvar translate=\"no\"\u003eDATASET\u003c/var\u003e: the name of a dataset in your project.\n- \u003cvar translate=\"no\"\u003eROUTINE_NAME\u003c/var\u003e: the name of the routine.\n\nExample: \n\n bq rm --routine mydataset.AddFourAndDivide\n\n### API\n\nCall the [`routines.delete` method](/bigquery/docs/reference/rest/v2/routines/delete)\nwith the dataset ID and the name of the routine."]]
