Trabajar con procedimientos almacenados de SQL

Un procedimiento almacenado es un conjunto de instrucciones que se pueden llamar desde otras consultas u otros procedimientos almacenados. Un procedimiento puede recibir argumentos de entrada y devolver valores como salida. Asigna un nombre a un procedimiento y lo almacenas en un conjunto de datos de BigQuery. Un procedimiento almacenado puede acceder a datos o modificarlos en varios conjuntos de datos por varios usuarios. También puede contener una consulta con varias instrucciones.

Algunos procedimientos almacenados están integrados en BigQuery y no es necesario crearlos. Se denominan procedimientos del sistema y puedes consultar más información sobre ellos en la referencia de procedimientos del sistema.

Los procedimientos almacenados admiten instrucciones de lenguaje de procedimiento, que te permiten hacer cosas como definir variables e implementar el flujo de control. Puedes obtener más información sobre las instrucciones del lenguaje de procedimiento en la referencia del lenguaje de procedimiento.

Crear un procedimiento almacenado

Elige una de las siguientes opciones para crear un procedimiento almacenado:

SQL

Para crear un procedimiento, usa la instrucción CREATE PROCEDURE.

En el siguiente ejemplo conceptual, procedure_name representa el procedimiento y el cuerpo del procedimiento aparece entre las instrucciones BEGIN y END:

CREATE PROCEDURE dataset_name.procedure_name()
BEGIN
-- statements here
END

En el siguiente ejemplo se muestra un procedimiento que contiene una consulta con varias instrucciones. La consulta de varias instrucciones define una variable, ejecuta una instrucción INSERT y muestra el resultado como una cadena de texto con formato.

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

En el ejemplo anterior, el nombre del procedimiento es mydataset.create_customer y el cuerpo del procedimiento aparece entre las instrucciones BEGIN y END.

Para llamar al procedimiento, usa la instrucción CALL:

CALL mydataset.create_customer();

Terraform

Usa el recurso google_bigquery_routine.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta Configurar la autenticación para bibliotecas de cliente.

En el siguiente ejemplo se crea un procedimiento almacenado denominado my_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`;"
}

Para aplicar la configuración de Terraform en un proyecto, sigue los pasos que se indican en las siguientes secciones. Google Cloud

Preparar Cloud Shell

  1. Abre Cloud Shell.

  2. Define el Google Cloud proyecto predeterminado en el que quieras aplicar tus configuraciones de Terraform.

    Solo tiene que ejecutar este comando una vez por proyecto y puede hacerlo en cualquier directorio.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Las variables de entorno se anulan si defines valores explícitos en el archivo de configuración de Terraform.

Preparar el directorio

Cada archivo de configuración de Terraform debe tener su propio directorio (también llamado módulo raíz).

  1. En Cloud Shell, crea un directorio y un archivo nuevo en ese directorio. El nombre del archivo debe tener la extensión .tf. Por ejemplo, main.tf. En este tutorial, nos referiremos al archivo como main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Si estás siguiendo un tutorial, puedes copiar el código de ejemplo de cada sección o paso.

    Copia el código de ejemplo en el archivo main.tf que acabas de crear.

    También puedes copiar el código de GitHub. Se recomienda cuando el fragmento de Terraform forma parte de una solución integral.

  3. Revisa y modifica los parámetros de ejemplo para aplicarlos a tu entorno.
  4. Guarda los cambios.
  5. Inicializa Terraform. Solo tienes que hacerlo una vez por directorio. 
    terraform init

    Si quieres usar la versión más reciente del proveedor de Google, incluye la opción -upgrade: 

    terraform init -upgrade

Aplica los cambios

  1. Revisa la configuración y comprueba que los recursos que va a crear o actualizar Terraform se ajustan a tus expectativas: 
    terraform plan

    Haga las correcciones necesarias en la configuración.

  2. Aplica la configuración de Terraform ejecutando el siguiente comando e introduciendo yes en la petición: 
    terraform apply

    Espera hasta que Terraform muestre el mensaje "Apply complete!".

  3. Abre tu Google Cloud proyecto para ver los resultados. En la Google Cloud consola, ve a tus recursos en la interfaz de usuario para comprobar que Terraform los ha creado o actualizado.

Enviar un valor con un parámetro de entrada

Un procedimiento puede tener parámetros de entrada. Un parámetro de entrada permite introducir datos en un procedimiento, pero no permite obtener datos.

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Enviar un valor con un parámetro de salida

Un procedimiento puede tener parámetros de salida. Un parámetro de salida devuelve un valor del procedimiento, pero no permite la entrada del procedimiento. Para crear un parámetro de salida, usa la palabra clave OUT antes del nombre del parámetro.

Por ejemplo, esta versión del procedimiento devuelve el nuevo ID de cliente a través del parámetro id:

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Para llamar a este procedimiento, debes usar una variable para recibir el valor de salida:

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM mydataset.customers
WHERE customer_id = id;

Enviar y recibir un valor con un parámetro de entrada/salida

Un procedimiento también puede tener parámetros de entrada o salida. Un parámetro de entrada/salida devuelve un valor del procedimiento y también acepta entradas para el procedimiento. Para crear un parámetro de entrada o salida, usa la palabra clave INOUT antes del nombre del parámetro. Para obtener más información, consulta Modo de argumento.

Autorizar rutinas

Puedes autorizar procedimientos almacenados como rutinas. Las rutinas autorizadas te permiten compartir los resultados de las consultas con usuarios o grupos específicos sin darles acceso a las tablas subyacentes que han generado los resultados. Por ejemplo, una rutina autorizada puede calcular una agregación de datos o buscar un valor en una tabla y usarlo en un cálculo.

Las rutinas autorizadas pueden crear, eliminar y manipular tablas, así como invocar otros procedimientos almacenados en la tabla subyacente.

Para obtener más información, consulta Rutinas autorizadas.

Llamar a un procedimiento almacenado

Para llamar a un procedimiento almacenado después de crearlo, usa la instrucción CALL. Por ejemplo, la siguiente instrucción llama al procedimiento almacenado create_customer:

CALL mydataset.create_customer();

Llamar a un procedimiento del sistema

Para llamar a un procedimiento del sistema integrado, usa la instrucción CALL. Por ejemplo, la siguiente instrucción llama al procedimiento del sistema BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;