Hace unos meses escribía sobre la importancia de validación de datos. Es imprescindible asegurarnos de que los datos con los que trabajamos proporcionan información de la manera correcta para conseguir un análisis de la información fiel a la realidad y un correcto procesado.
En este primer artículo os detallaba las situaciones en las que recomendamos validar los datos, así como los aspectos fundamentales a tener en cuenta en el proceso de validación con T-SQL.
A veces podemos realizar conteos a tablas muy grandes que llevan mucho tiempo, o necesitamos comprobar si existe una tabla o un campo dentro de una tabla, o poder comparar los resultados de 2 consultas distintas. Hoy veremos ejemplos de estos casos empleando diferentes técnicas y ejemplos prácticos con T-SQL para detectar posibles errores y su validación.
Cómo validar el tamaño del dato
Tenemos una tabla origen con los siguientes tipos de dato:
Tenemos un proceso ETL simple que trae los datos desde esa tabla y los inserta en una tabla de staging:
Si ejecutamos la ETL, vemos que ejecuta correctamente a pesar de tener un aviso de que puede truncarse el dato en PostalCode
Vemos que parece haber insertado el dato correctamente.
Pero si comprobamos esos códigos postales en la vista origen:
Vemos que solo nos salieron estos códigos postales:
En la vista origen, el código postal es un nvarchar(15), mientras que en la tabla destino es un nvarchar(5). Esto provocó un truncado de datos lo cual hizo que hace que solo se recojan los 5 primeros caracteres de los códigos postales, a pesar de tener códigos postales con mayor tamaño.
Si corregimos la tabla destino el tipo de dato de nvarchar(5) a nvarchar(15), podemos comprobar que ya no aparece el warning en el proceso ETL:
Y al comprobar el dato en la tabla, podemos ver que ahora algunos códigos postales tienen mayor tamaño:
Así que comprobamos la existencia de esos códigos postales en la vista origen:
SELECT DISTINGS(PostalCode)
FROM bi.[Address_blog]
WHERE postalcode IN('32804', 'K4B 1T7', 'H1Y 2H5','T2P 2G8','33000','14111','3000') 
Con lo cual ya vemos que ahora si que sale el dato correcto, cosa que también podemos hacer con esta consulta a las tablas del sistema:
SELECT (TABLE_SCHEMA) table_schema_name,
(TABLE_NAME) Table_Name,
(REPLACE(REPLACE(COLUMN_NAME, '>', '>'), '>', '<')) Column_name,
DATA_TYPE + CASE DATA_TYPE
WHEN 'bit' THEN ''
WHEN 'smallint' THEN ''
WHEN 'int' THEN ''
WHEN 'bigint' THEN ''
WHEN 'bit' THEN ''
WHEN 'smallint' THEN ''
WHEN 'int' THEN ''
WHEN 'bigint' THEN ''
WHEN 'varchar' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) + ')'
WHEN 'char' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) + ')'
WHEN 'nvarchar' THEN '(' + CASE
WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'Max'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS varchar)
END + ')'
WHEN 'nchar' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) + ')'
WHEN 'decimal' THEN '(' + CAST(NUMERIC_PRECISION AS varchar) + ',' + CAST(NUMERIC_SCALE AS varchar) + ')'
WHEN 'numeric' THEN '(' + CAST(NUMERIC_PRECISION AS varchar) + ',' + CAST(NUMERIC_SCALE AS varchar) + ')'
WHEN 'money' THEN ''
WHEN 'datetime' THEN ''
WHEN 'uniqueidentifier' THEN ''
ELSE ''
END AS DATA_TYPE,
ORDINAL_POSITION AS ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'BI'
AND TABLE_NAME = 'Address_Blog' 
Cómo comparar el resultado de 2 consultas
A menudo podemos pensar en optimizar nuestras propias consultas, o necesitamos comprobar si el resultado de una consulta que nos envían es el mismo que otra, y por el volumen de datos no es posible comprobarlo a mano. Con esta consulta podemos validar qué datos están en una tabla y no en la otra.
SELECT * FROM(
SELECT ad.Prov_Nombre,’not in b’ y
FROM [dbo].[Prov] ad
WHERE ad.Prov_Id>2 –a
EXCEPT
SELECT ad.Prov_nombre, ‘not in b’ y
FROM [dbo].[Prov] ad
WHERE ad.Prov_Id<7 –b
UNION
SELECT ad.Prov_nombre, ‘not in a’ y
FROM [dbo].[Prov] ad
WHERE ad.Prov_Id<7 –b
EXCEPT
SELECT ad.Prov_Nombre,’not in a’ y
FROM [dbo].[Prov] ad
WHERE ad.Prov_Id>2 –a
) cars
Método alternativo para contar el número de filas de una tabla
Realizar conteos de tablas es muy habitual, pero realizarlos sobre tablas muy grandes resulta muy costoso en tiempo y recursos. Esta es una consulta típica para contar el número de filas de una tabla:
SELECT COUNT(*)AS rowcnt
FROM [fact].[Invoice] 
Lleva 3 segundos en cargar. Sin embargo, esta otra consulta:
SELECT o.name,
i.rowcnt
FROM sysobjects o
JOIN sysindexes I
ON o.id = i.id
WHERE o.type = 'u'
AND i.indid IN (0,1)
AND o.name IN
('Invoice')
ORDER BY o.name 
Esta consulta no llega a 1 segundo pues ya está almacenada la información del número de registros en las tablas del sistema que podemos usar para comprobar rápidamente esos conteos. Hay que tener en cuenta que tienen que estar actualizadas las estadísticas de las tablas del sistema para que funcione correctamente esta segunda consulta. En caso de que no estén actualizadas podemos lanzar el comando “UPDATE STATISTICS nombre_tabla”.
Cómo comprobar si existe una tabla en una base de datos
Para simplemente ver si existe una tabla, podemos consultar en la tabla del sistema INFORMATION_SCHEMA.TABLES filtrando por el nombre de la(s) tabla(s) que buscamos.
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME in ('UNIDAD_VENDIDA', 'EXTRA_QUITAR') 
Búsqueda de un nombre de campo sin saber en qué tabla está
Existen ocasiones en las que por falta de documentación o no estar familiarizado con el proyecto, no sabemos con qué tablas hay que cruzar datos y comprobar manualmente puede conllevar bastante tiempo de búsqueda. Existen métodos para buscar, como ver si existen nombres similares a ese campo en otras tablas existentes:
Pongamos que necesitamos saber qué tablas contienen un id de proveedor, pero nuestro DWH tiene cientos de tablas. Con esta consulta podemos buscar en las tablas del sistema la metadata que contenga Proveedor y que nos diga el nombre del campo y la tabla en la que está:
SELECT t.name, c.name FROM sys.tables t
LEFT JOIN sys.columns c ON t.object_id=c.object_id
WHERE c.name LIKE '%Proveedor%' 
También se podrían buscar en INFORMATION_SCHEMA.COLUMNS filtrando por COLUMN_NAME like ‘%Proveedor%’ y el nombre del esquema.
Cómo comprobar si existen campos nulos
DECLARE @tabla NVARCHAR(255), @sql NVARCHAR(MAX);
SET @tabla = '[etl].[Proveedor]';
SET @sql = N'SELECT IdProveedores, Cedula, EmpresaId, [$sq_row_hash_T2] FROM ' + @tabla + ' WHERE 1 = 0';
SELECT @sql = @sql + N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tabla);
EXEC sp_executesql @sql; 
Si en lugar de tablas queremos consultar sobre vistas, la consulta sería la siguiente:
DECLARE @tabla NVARCHAR(255), @sql NVARCHAR(MAX);
SET @tabla = N’Proveedor’;
SET @sql = N’SELECT * FROM etl.’ + @tabla + ‘ WHERE 1 = 0’;
SELECT @sql = @sql + N’ OR ‘ + QUOTENAME(c.name) + ‘ IS NULL’
FROM sys.columns c join sys.views v on v.object_id = c.object_id
WHERE v.name =@tabla;
PRINT @sql;
EXEC sp_executesql @sql;
Validaciones de SolidQ ABA Framework
ABA Framework es una solución desarrollada por SolidQ que utilizamos principalmente para descubrir patrones en los proyectos de Business Intelligence y optimizar procesos. Dentro de sus posibilidades de uso, ofrece test automáticos.
Al cargar datos, hace una comparación del conteo de las filas entre origen y destino, lo notifica y deja documentaod en caso de que no coincida, quedando marcado si se da algún error e ingormación sobre este mismo para poder arreglarlo.
Más información en https://www.solidq.com/wiki/aba/automatedtesting/
1- Aquí se puede apreciar la diferencia entre filas de origen y destino:
2- Comprobando estos casos, podemos ver la información del error:
1 comment
Muy interesante!