GCP: Crear Stored Procedures y Funciones en BigQuery

En esta ocasión veremos cómo crear Stored Procedures y funciones en BigQuery. Un procedimiento almacenado es un conjunto de instrucciones que se pueden llamar desde otras consultas o desde otros procedimientos almacenados.

• Puede tomar argumentos de entrada y mostrar valores como resultados.
• Puede acceder a los datos o modificarlos en varios conjuntos de datos de varios usuarios.
• También puede contener una consulta de varias instrucciones.

Ejemplo. Crear un Stored Procedure que mande un simple mensaje de "Hola, mundo". En este caso también le pasamos un párametro de nombre (name).

CREATE OR REPLACE PROCEDURE `myproject.mydataset.decirHola`(name STRING)
BEGIN
  SELECT FORMAT("Hola, %s",name);
END;

Para invocarlo:

CALL`myproject.mydataset.decirHola`('Thomas Muller');

Ejemplo. Teniendo esta sentencia SQL que nos devuelve la definición de un Stored Procedure en BigQuery:

-- Obtener la definición de un Stored Procedure en BigQuery
SELECT 
  routine_catalog, 
  routine_schema, 
  routine_name, 
  routine_type, 
  routine_definition
FROM your_dataset.INFORMATION_SCHEMA.ROUTINES    
WHERE routine_name = 'nombre_stored_procedure';

Crear un Stored Procedure que nos devuelva cada uno de los campos.

CREATE OR REPLACE PROCEDURE `myproject.mydataset.getDetallesStored`(nameStored STRING)
BEGIN
  -- Ejecutar la consulta y retornar los resultados
  DECLARE routine_catalog STRING;
  DECLARE routine_schema STRING;
  DECLARE routine_name STRING;
  DECLARE routine_type STRING;
  DECLARE routine_definition STRING;

  SELECT
    routine_catalog,
    routine_schema,
    routine_name,
    routine_type,
    routine_definition
  INTO 
    routine_catalog,
    routine_schema,
    routine_name,
    routine_type,
    routine_definition
  FROM
    myproject.mydataset.INFORMATION_SCHEMA.ROUTINES
  WHERE
    routine_name = nameStored;

  -- Seleccionar los resultados para retornarlos
  SELECT
    routine_catalog,
    routine_schema,
    routine_name,
    routine_type,
    routine_definition;
END;

Hacemos el llamado:

CALL `myproject.mydataset.getDetallesStored`('decirHola');

El resultado de este llamado nos arrojará los detalles del Stored Procedure indicado.

¿Y las funciones?

Una función es similar a un Stored Procedure (Procedimiento Almacenado), pero devolverá un valor. Además cambia la manera en la que lo invocamos. Mientras que un Stored Procedure es invocado con CALL, a una función la invocaremos con SELECT.

Ejemplo. Crear una función para dividir dos números.

CREATE OR REPLACE FUNCTION `myproject.mydataset.getDiv`(
  x FLOAT64, y FLOAT64) RETURNS FLOAT64
AS (
  SELECT DIV(X, Y) AS RESULT;
);

Para usar la función:

SELECT `myproject.mydataset.getDiv`(3.4,5.2);

Para quienes han usado PL/pgSQL (PostgreSQL) o PL/SQL (Oracle) la sintaxis es muy similar, por no decir idéntica. Salvo por algunas excepciones.

En próximas entregas continuaremos con este tema.

Enlaces:

https://cloud.google.com/bigquery/docs/procedures?hl=es-419