In diesem Dokument werden Aufgaben beschrieben, die für alle Routinetypen in BigQuery üblich sind.
Berechtigungen
Zum Ausführen einer Routine benötigen Sie die Berechtigung bigquery.routines.get für das Dataset, das die Routine enthält.
Standardmäßig benötigen Sie auch die Berechtigung für den Zugriff auf alle Ressourcen, auf die die Routine verweist, z. B. auf Tabellen oder Ansichten. Für UDFs und Tabellenfunktionen können Sie die Funktion autorisieren, im Namen des Aufrufers auf diese Ressourcen zuzugreifen. Weitere Informationen finden Sie unter Autorisierte Funktionen.
Routine erstellen
Zum Erstellen einer Routine benötigen Sie die Berechtigung bigquery.routines.create.
SQL
Führen Sie je nach Routinetyp eine der folgenden DDL-Anweisungen aus:
Führen Sie den Befehl bq show
mit dem Flag --routine aus:
bqshow--routineDATASET.ROUTINE_NAME
Dabei gilt:
DATASET: Der Name eines Datasets in Ihrem Projekt.
ROUTINE_NAME: Der Name der Routine
Beispiel:
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
Rufen Sie die Methode routines.get mit der Dataset-ID und dem Namen der Routine auf. Der Text der Routine wird im Objekt Routine zurückgegeben.
Routine löschen
Zum Löschen einer Routine benötigen Sie die Berechtigung bigquery.routines.delete.
Console
Öffnen Sie in der Google Cloud Console die Seite „BigQuery“.
[[["Leicht verständlich","easyToUnderstand","thumb-up"],["Mein Problem wurde gelöst","solvedMyProblem","thumb-up"],["Sonstiges","otherUp","thumb-up"]],[["Schwer verständlich","hardToUnderstand","thumb-down"],["Informationen oder Beispielcode falsch","incorrectInformationOrSampleCode","thumb-down"],["Benötigte Informationen/Beispiele nicht gefunden","missingTheInformationSamplesINeed","thumb-down"],["Problem mit der Übersetzung","translationIssue","thumb-down"],["Sonstiges","otherDown","thumb-down"]],["Zuletzt aktualisiert: 2025-03-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."]]
