SQL

To create a procedure, use theCREATE PROCEDUREstatement.

In the following conceptual example,procedure_name representsthe procedure and the body of the procedure appears betweenBEGIN andEND statements:

CREATEPROCEDUREdataset_name.procedure_name()BEGIN-- statements hereEND

The following example shows a procedure that contains a multi-statement query.The multi-statement query sets a variable, runs anINSERT statement, anddisplays the result as a formatted text string.

CREATEORREPLACEPROCEDUREmydataset.create_customer()BEGINDECLAREidSTRING;SETid=GENERATE_UUID();INSERTINTOmydataset.customers(customer_id)VALUES(id);SELECTFORMAT("Created customer %s",id);END

In the preceding example, the name of the procedure ismydataset.create_customer, and the body of procedure appears betweenBEGIN andEND statements.

To call the procedure, use theCALLstatement:

CALLmydataset.create_customer();

Terraform

Use thegoogle_bigquery_routine resource.

To authenticate to BigQuery, set up Application DefaultCredentials. For more information, seeSet up authentication for client libraries.

The following example creates a stored procedure namedmy_stored_procedure:

# Creates a SQL stored procedure.# Create a dataset to contain the stored procedure.resource "google_bigquery_dataset" "my_dataset" {  dataset_id = "my_dataset"}# Create a stored procedure.resource "google_bigquery_routine" "my_stored_procedure" {  dataset_id      = google_bigquery_dataset.my_dataset.dataset_id  routine_id      = "my_stored_procedure"  routine_type    = "PROCEDURE"  language        = "SQL"  definition_body = "SELECT * FROM `bigquery-public-data.ml_datasets.penguins`;"}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. LaunchCloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (alsocalled aroot module).

1. InCloud Shell, create a directory and a new file within that directory. The filename must have the.tf extension—for examplemain.tf. In this tutorial, the file is referred to asmain.tf.

   mkdirDIRECTORY && cdDIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly createdmain.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the-upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and enteringyes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.