Neste documento, descrevemos tarefas comuns a todos os tipos de rotina no
BigQuery.
Permissões
Para referenciar uma rotina em uma consulta SQL, é necessário ter a permissão bigquery.routines.get. Para conceder acesso a rotinas, atribua um papel do IAM
com a permissão bigquery.routines.get no conjunto de dados ou na rotina
individual. Conceder acesso no nível do conjunto de dados dá ao principal acesso a todas as rotinas nele. Para mais informações, consulte
Controlar o acesso a recursos com o IAM.
Por padrão, você também precisa de permissão para acessar todos os recursos aos quais a rotina
faz referência, como tabelas ou visualizações. Para UDFs e funções de tabela, é possível
autorizar que a função acesse esses recursos em nome do autor da chamada. Para
mais informações, consulte
Funções autorizadas.
Criar uma rotina
Para criar uma rotina, é necessário ter a permissão bigquery.routines.create.
SQL
Dependendo do tipo de rotina, execute uma das seguintes instruções DDL:
DATASET: o nome de um conjunto de dados no projeto.
ROUTINE_NAME: o nome da rotina.
Exemplo:
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
[[["Fácil de entender","easyToUnderstand","thumb-up"],["Meu problema foi resolvido","solvedMyProblem","thumb-up"],["Outro","otherUp","thumb-up"]],[["Difícil de entender","hardToUnderstand","thumb-down"],["Informações incorretas ou exemplo de código","incorrectInformationOrSampleCode","thumb-down"],["Não contém as informações/amostras de que eu preciso","missingTheInformationSamplesINeed","thumb-down"],["Problema na tradução","translationIssue","thumb-down"],["Outro","otherDown","thumb-down"]],["Última atualização 2025-08-17 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."]]
