Sustituyendo la tarea de Integration Services para obtener perfiles de datos

por código T-SQL

 

Introdución

El perfilado de datos es una tarea frecuentemente olvidada o considerada secundaria. Sin embargo es muy importante conocer el estado de los datos que deberemos procesar y anticipar los problemas y dificultades antes de que surjan por sorpresa con nuestra solución de data warehouse ya en producción. Si no disponemos de una herramienta especializada, tal vez la manera más eficaz sea obtener perfiles de datos por código T-SQL.
Podemos encontrar más información sobre el perfilado de datos en Kimball Design Tip #59: Surprising Value of Data Profiling.

Requisitos para obtener perfiles de datos por código T-SQL

Aunque SSIS dispone de una tarea de perfilado de datos hay algunos motivos para querer obtener perfiles de datos por código T-SQL. El primero y más importante es que sólo funciona contra SQL Server. Otro inconveniente es la necesidad de un programa especial para ver el contenido de los perfiles que se guardan en ficheros xml.
Aquí trabajaremos en T-SQL, pero el código y las estructuras de datos que usemos pueden adaptarse a cualquier otro dialecto SQL con facilidad.

Más información sobre cómo emplear la tarea SSIS en Using the Data Profiling SQL Server Integration Services SSIS task

Para prepararnos para obtener perfiles de datos por código T-SQL, necesitaremos una tabla para los resultados de cada perfil y un procedimiento almacenado que lo extraerá y guardará sus resultados en la tabla. Dado que se trata de una tarea de calidad de datos, lo ideal sería que toda tabla, procedimiento o vista que creemos para obtener perfiles de datos por código T-SQL, lo sean en la BD de nuestra solución data warehouse destinada al control de calidad de datos.

En este post ilustraremos nuestro plan con un procedimiento que extrae el porcentaje de valores NULL en las columnas de una tabla cualquiera. las pruebas las hemos realizado sobre SQL Server 2016.

Tabla de perfiles para obtener perfiles de datos por código T-SQL

La tabla de destino

Comenzaremos por crear una tabla donde guardar los resultados y que va a tener esta estructura.

CREATE TABLE [prf].[NullRatio](
[NullRatioKey] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](128) NOT NULL,
[DBName] [nvarchar](128) NOT NULL,
[SchemaName] [nvarchar](128) NOT NULL,
[TableName] [nvarchar](128) NOT NULL,
[ColumnName] [nvarchar](128) NOT NULL,
[ProfileTime] [datetime] NOT NULL,
[TotalRows] [decimal](38, 3) NOT NULL,
[NullTotals] [decimal](38, 3) NOT NULL,
[NullRatio] [decimal](5, 2) NOT NULL,
CONSTRAINT [PK_NullRatio] PRIMARY KEY CLUSTERED
(
[NullRatioKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [prf].[NullRatio] ADD CONSTRAINT [DF_NullRatio_TotalRows] DEFAULT ((0)) FOR [TotalRows]
GO

ALTER TABLE [prf].[NullRatio] ADD CONSTRAINT [DF_NullRatio_NullTotals] DEFAULT ((0)) FOR [NullTotals]
GO

ALTER TABLE [prf].[NullRatio] ADD CONSTRAINT [DF_NullRatio_NullRatio] DEFAULT ((0)) FOR [NullRatio]
GO

EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’Perfil de proporciones de valores NULL en las columnas de las tablas.’ , @level0type=N’SCHEMA’,@level0name=N’prf’, @level1type=N’TABLE’,@level1name=N’NullRatio’
GO

Las columnas son las siguientes

  • NullRatioKey – Clave única
  • ServerName – Nombre del servidor o instancia donde está la tabla a perfilar
  • DBName – Nombre de la base de datos donde está la tabla a perfilar
  • TableName – Nombre de la tabla
  • ColumnName – Nombre de la columna
  • ProfileTime – Fecha y hora en que se realiza el perfil
  • TotalRows – Número de registros de la tabla
  • NullTotals – Número de Valores NULL para esa columna
  • NullRatio – Proporción de valores NULL para esa columna

 

El procedimiento almacenado con el que obtener perfiles de datos por código T-SQL

El procedimiento almacenado quedará como sigue

— =============================================
— Author: José Miguel Calzada
— Create date: 03-12-2020
— Description: Calcula el porcentaje de valores NULL de las columnas de una tabla
— =============================================
CREATE PROCEDURE [prf].[spPercentNulls]

— 1 – Parámetros de entrada

@ServerName nvarchar(128) = N’MyServer’
,@DBName nvarchar(128)= N’AdventureWorks’
,@SchemaName nvarchar(128) = N’Production’
,@TableName nvarchar(128) = N’Product’
AS
BEGIN

SET NOCOUNT ON;

— 2 – Declaración de variables

DECLARE @SQL nvarchar(2000) — Para construir las sentencias de SQL dinámico
DECLARE @TotalRows decimal(38,3) — Recuento de registros de la tanbla
DECLARE @TotalNulls decimal(38,3) — Recuento de NULLs de la tabla
DECLARE @NullRatio decimal(5,2) — Proporción de NULLs de la tabla
DECLARE @ColumnName nvarchar(128) — Nombre de la columna que se procesa

— 3 – Contamos el número de registros

SET @SQL = ‘SET @TotalRows = (SELECT COUNT(*) FROM ‘ + @ServerName + ‘.’ + @DBName + ‘.’ + @SchemaName + ‘.’ + @TableName + ‘)’
EXEC sp_executesql @SQL, N’@TotalRows decimal(38,3) OUTPUT’, @TotalRows OUTPUT

— 4 – Obtenemos la lista de columnas que admiten valores NULL

SET @SQL = ‘DECLARE columns_cursor CURSOR GLOBAL FOR SELECT [name] FROM ‘ + @ServerName + ‘.’ + @DBName + ‘.’ + ‘sys.all_columns WHERE object_id = OBJECT_ID(N”’ + @DBName + ‘.’ + @SchemaName + ‘.’ + @TableName + ”’, N”U”) AND is_nullable = 1′
EXEC sp_executesql @SQL

OPEN columns_cursor

FETCH NEXT FROM columns_cursor INTO @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN

— 5 – Contamos los valores NULL

SET @SQL = ‘SET @TotalNulls = (SELECT COUNT(*) FROM ‘ + @ServerName + ‘.’ + @DBName + ‘.’ + @SchemaName + ‘.’ + @TableName + ‘ WHERE ‘ + @ColumnName + ‘ IS NULL)’
EXEC sp_executesql @SQL, N’@TotalNulls decimal(38,3) OUT’, @TotalNulls OUT

— 6 – Calculamos la proporción

SET @NullRatio = @TotalNulls/@TotalRows

— 7 – Grabamos el resultado en la tabla

INSERT INTO prf.NullRatio (ServerName, DBName, SchemaName, TableName, ColumnName, ProfileTime, TotalRows, NullTotals, NullRatio)
VALUES (@ServerName, @DBName, @SchemaName, @TableName, @ColumnName, GETDATE(), @TotalRows, @TotalNulls, @NullRatio)

FETCH NEXT FROM columns_cursor INTO @ColumnName
END
CLOSE columns_cursor
DEALLOCATE columns_cursor

END
GO

Partes del procedimiento almacenado

  1. Comenzamos con cuatro parámetros de entrada donde indicaremos el servidor, base de datos, esquema y tabla que queremos perfilar.
  2. Declaramos las variables de trabajo
  3. Montamos una sentencia SQL Server dinámica para contar el número de registros de la tabla y los guardamos en la variable @TotalRows
  4. Montamos otra sentencia SQL Server dinámica para llenar un cursor con las columnas de la tabla que admitan valores NULL. Para ellos recurriremos a la vista de sistema sys.all_columns y declararemos el cursor global para poder leerlo desde nuestro procedimiento sin que nos lo impida el diferente ámbito de la sentencia SQL dinámica.
  5. Comenzamos a leer el cursor y para cada columna montamos una sentencia SQL dinámica que nos devolverá el total de registros con valores NULL
  6. Calculamos la proporción de NULLs sobre el total de registros
  7. Grabamos los resultados para esta columna en la tabla que hemos creado al principio.

El resultado final debería ser algo así listo para consultarse desde el propio SSMS sinnecsidad de visores especiales.

Perfil en la tabla para obtener perfiles de datos por código T-SQL

Podéis consultar nuestra oferta de cursos sobre data warehouse en la página de SQL Server y BI de Certia

¡Hasta la próxima!