Procedimientos almacenados mediante Synapse SQL en Azure Synapse Analytics

• Iniciar una prueba gratuita de Fabric.
• Asistente de migración para almacén de datos en tejido.

Los grupos aprovisionados y sin servidor de Synapse SQL permiten colocar una lógica compleja de procesamiento de datos en procedimientos almacenados de SQL. Los procedimientos almacenados son una excelente manera de encapsular el código SQL y almacenarlos cerca de los datos en el almacenamiento de datos. Los procedimientos almacenados ayudan a los desarrolladores a modularizar sus soluciones mediante la encapsulación del código en unidades administrables y la facilitación de una mayor reutilización del código. Cada procedimiento almacenado también puede aceptar parámetros para que sean aún más flexibles. En este artículo encontrará algunas sugerencias para implementar procedimientos almacenados en el grupo de SQL de Synapse para desarrollar soluciones.

Qué esperar

Synapse SQL admite muchas de las características de T-SQL que se usan en SQL Server. Lo más importante es que haya características específicas de escalabilidad horizontal que puede usar para maximizar el rendimiento de la solución. En este artículo, obtendrá información sobre las características que puede colocar en procedimientos almacenados.

Para mantener la escala y el rendimiento del grupo de SQL, hay algunas características y funcionalidades que presentan diferencias de comportamiento y otras que no son compatibles.

Procedimientos almacenados en Synapse SQL

En el ejemplo siguiente, puede ver los procedimientos que quitan objetos externos si existen en la base de datos:

CREATE PROCEDURE drop_external_table_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_tables WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL TABLE ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END
GO
CREATE PROCEDURE drop_external_file_format_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_file_formats WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL FILE FORMAT ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END
GO
CREATE PROCEDURE drop_external_data_source_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_data_sources WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL DATA SOURCE ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END

Estos procedimientos se pueden ejecutar mediante la instrucción , EXEC donde puede especificar el nombre y los parámetros del procedimiento:

EXEC drop_external_table_if_exists 'mytest';
EXEC drop_external_file_format_if_exists 'mytest';
EXEC drop_external_data_source_if_exists 'mytest';

Synapse SQL proporciona una implementación de procedimientos almacenados sencilla y optimizada. La mayor diferencia en comparación con SQL Server es que el procedimiento almacenado no es código precompilado. En los almacenes de datos, el tiempo de compilación es pequeño en comparación con el tiempo necesario para ejecutar consultas en grandes volúmenes de datos. Es más importante asegurarse de que el código del procedimiento almacenado está optimizado correctamente para consultas de gran tamaño. El objetivo es ahorrar horas, minutos y segundos, no milisegundos. Por lo tanto, resulta más útil pensar en procedimientos almacenados como contenedores para la lógica de SQL.

Cuando Synapse SQL ejecuta el procedimiento almacenado, las instrucciones SQL se analizan, traducen y optimizan en tiempo de ejecución. Durante este proceso, cada instrucción es transformada en consultas distribuidas. El código SQL que se ejecuta en los datos es diferente de la consulta enviada.

Encapsular reglas de validación

Los procedimientos almacenados permiten localizar la lógica de validación en un único módulo almacenado en SQL Database. En el ejemplo siguiente, puede ver cómo validar los valores de los parámetros y cambiar sus valores predeterminados.

CREATE PROCEDURE count_objects_by_date_created 
                            @start_date DATETIME2,
                            @end_date DATETIME2
AS BEGIN 

    IF( @start_date >= GETUTCDATE() )
    BEGIN
        THROW 51000, 'Invalid argument @start_date. Value should be in past.', 1;  
    END

    IF( @end_date IS NULL )
    BEGIN
        SET @end_date = GETUTCDATE();
    END

    IF( @start_date >= @end_date )
    BEGIN
        THROW 51000, 'Invalid argument @end_date. Value should be greater than @start_date.', 2;  
    END

    SELECT
         year = YEAR(create_date),
         month = MONTH(create_date),
         objects_created = COUNT(*)
    FROM
        sys.objects
    WHERE
        create_date BETWEEN @start_date AND @end_date
    GROUP BY
        YEAR(create_date), MONTH(create_date);
END

La lógica del procedimiento sql validará los parámetros de entrada cuando se llame al procedimiento.

EXEC count_objects_by_date_created '2020-08-01', '2020-09-01'

EXEC count_objects_by_date_created '2020-08-01', NULL

EXEC count_objects_by_date_created '2020-09-01', '2020-08-01'
-- Error
-- Invalid argument @end_date. Value should be greater than @start_date.

EXEC count_objects_by_date_created '2120-09-01', NULL
-- Error
-- Invalid argument @start_date. Value should be in past.

Anidar procedimientos almacenados

Cuando los procedimientos almacenados llaman a otros procedimientos almacenados, o ejecutan SQL dinámico, se considera que el procedimiento almacenado interno o la invocación de código está anidada. En el código siguiente se muestra un ejemplo de procedimiento anidado:

CREATE PROCEDURE clean_up @name SYSNAME
AS BEGIN
    EXEC drop_external_table_if_exists @name;
    EXEC drop_external_file_format_if_exists @name;
    EXEC drop_external_data_source_if_exists @name;
END

Este procedimiento acepta un parámetro que representa algún nombre y, a continuación, llama a otros procedimientos para quitar los objetos con este nombre. El grupo de Synapse SQL admite un máximo de ocho niveles de anidamiento. Esta funcionalidad es ligeramente diferente de SQL Server. El nivel de anidamiento en SQL Server es 32.

La llamada al procedimiento almacenado de nivel superior equivale al nivel de anidamiento 1.

EXEC clean_up 'mytest'

Si el procedimiento almacenado también realiza otra llamada EXEC, el nivel de anidamiento aumenta de uno a dos.

CREATE PROCEDURE clean_up @name SYSNAME
AS
    EXEC drop_external_table_if_exists @name  -- This call is nest level 2
GO
EXEC clean_up 'mytest'  -- This call is nest level 1

Si el segundo procedimiento ejecuta SQL dinámico, el nivel de anidamiento aumenta a tres.

CREATE PROCEDURE drop_external_table_if_exists @name SYSNAME
AS BEGIN
    /* See full code in the previous example */
    EXEC sp_executesql @tsql = @drop_stmt;  -- This call is nest level 3
END
GO
CREATE PROCEDURE clean_up @name SYSNAME
AS
    EXEC drop_external_table_if_exists @name  -- This call is nest level 2
GO
EXEC clean_up 'mytest'  -- This call is nest level 1

INSERTAR.. EXECUTE

El grupo de SQL de Synapse aprovisionado no permite consumir el conjunto de resultados de un procedimiento almacenado con una instrucción INSERT. Hay un enfoque alternativo que puede usar. Para obtener un ejemplo, consulte el artículo sobre tablas temporales para el grupo de Synapse SQL aprovisionado.

Limitations

Hay algunos aspectos de Transact-SQL procedimientos almacenados que no se implementan en Synapse SQL, como:

Contenido relacionado

Para obtener más sugerencias sobre desarrollo, vea la información general sobre desarrollo.