0
Shares
0
0
0

En la entrada de hoy vamos a proponer una serie de scripts que pueden servir como herramienta de utilidad a la hora de desarrollar una solución de BI, o cualquier otro escenario en donde tengamos que integrar datos de ficheros en SQL Server. Con estos scripts simplificaremos la generación de vistas que nos permitirán validar el tipo de dato y formato.

Escenario

  • Tenemos una serie de ficheros planos que necesitamos llevar a base de datos en SQL Server.
  • Los ficheros pueden traer campos mal informados, necesitamos validar los tipos de datos antes de hacer las transformaciones pertinentes o cargar en las tablas finales.
  • La cantidad de ficheros distintos a integrar es grande y/o se espera que aumente, por lo que buscamos automatizar parte del trabajo.
escenario sql server BI

Pasos a Seguir

  • Carga de los ficheros en las tablas de Landing forzando todas las columnas a nvarchar, de esta forma evitaremos posibles errores en la carga.
  • Crear una tabla auxiliar para los metadatos que utilizaremos en la creación de vistas.
  • Insertar los metadatos de la tabla de Landing en la tabla auxiliar.
  • Generar el código de las vistas de casteo y validación con los scripts que proponemos a partir de los metadatos de la tabla de landing.

Ejemplos de Aplicación

Vamos a trabajar con un ejemplo simple, asumiendo que tenemos ya cargado el fichero a la base de datos de Landing. En esta carga hemos cargado todo en nvarchar(1000), es decir sin inferir ni castear tipos de datos para evitar posibles problemas si el fichero trae alguna columna mal informada. Los casteos y detección de errores los haremos en la vista que generaremos más adelante.

Cómo simplificar la creación de vistas de validación

Metadatos

Creamos una tabla auxiliar en la que insertaremos los metadatos de la tabla de landing para utilizarla en la generación del código de las vistas.

CREATE TABLE [md].[source_schema](
[table_schema_name] [varchar](3) NOT NULL,
[Table_Name] [sysname] NOT NULL,
[Column_name] [nvarchar](4000) NULL,
[Destination_column_name] [nvarchar](4000) NULL,
[Data_type] [nvarchar](191) NULL,
[Destination_Data_Type] [nvarchar](191) NULL,
[ordinal_position] [int] NULL,
[active_for_creation] [nvarchar](1) NULL
) ON [PRIMARY]
GO
 

Utilizando el script que veremos a continuación cargaremos la tabla con los metadatos de la tabla de Landing. Tenemos que pasarle el nombre de la tabla y estamos filtrando por el esquema ldg que es donde tenemos las tablas en nuestro ejemplo.

DECLARE @table_name nvarchar(100) = ‘samplecsv’
 
 
;WITH mycolumns
AS (
SELECT (TABLE_SCHEMA) table_schema_name
,(TABLE_NAME) Table_Name
,(replace(replace(COLUMN_NAME, ‘>’, ‘>’), ‘>’, ‘<‘)) Column_name
,DATA_TYPE +
CASE DATA_TYPE
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) + ‘)’
END AS DATA_TYPE
,ORDINAL_POSITION AS ordinal_position
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA IN (‘ldg’)
AND TABLE_NAME = @table_name
)
–insert into md.source_schema
SELECT
‘ldg’ table_schema_name
,Table_Name
,Column_name
,Column_name AS Destination_column_name
,DATA_TYPE AS Data_type
,DATA_TYPE AS Destination_Data_Type
,ordinal_position
,‘Y’ AS active_for_creation
FROM mycolumns
escenario sql server schema table

Como vemos el script consulta sobre la tabla INFORMATION_SCHEMA.COLUMNS para traerse las columnas de la tabla que contiene la carga del fichero.

Los campos de la tabla se describen por si mismos, aunque cabe aclarar ‘active_for_creation’. Este campo simplemente es un flag que utilizaremos en el script de generación de vistas para indicar si la columna en cuestión debe ser incluida. Modificaremos Destination_Data_Type para mapear los tipos de entrada con los tipos de salida.

escenario sql server schema table name

Generación Vista de Casteo

Con los metadatos cargados el siguiente paso es generar la vista de casteos. Esta vista va a realizar un try_cast sobre las columnas y en caso de no ser posible (try_cast nos devuelve null) marcaremos la fila como errónea, lo que nos sirve para poder controlar errores en el fichero de entrada y tomar acciones de cara a la siguiente fase de la carga como puede ser enviar una notificación de fichero incorrecto etc…

SELECT
–Instrucción para crear la vista de casteo
‘CREATE VIEW ctg.’ + table_name + ‘ as ‘ +
–Primera CTE donde tenemos los nombres originales y los try_cast de las columnas
‘WITH Source as ( Select ‘ + STRING_AGG(quoteName(column_name) + ‘ as ‘ + QUOTENAME(Column_name + ‘_Original’), ‘,’) + ‘,’ + STRING_AGG(‘try_cast(‘ + quotename(Column_Name) + ‘ as ‘ + Destination_Data_Type + ‘) as ‘ + quotename(column_name), ‘,’) + ‘ FROM ‘ + QUOTENAME(TABLE_SCHEMA_NAME) + ‘.’ + QUOTENAME(TABLE_NAME) + ‘),’
–Segunda CTE donde indicamos que columna ha fallado en cada fila
,‘AddFlags AS ( SELECT *, ‘ + STRING_AGG(‘case when ‘ + quotename(column_name) + ‘ is null and ‘ + quotename(column_name + ‘_Original’) + ‘ is not null then 1 else 0 end as ‘ + quotename(column_name + ‘_error’), ‘,’) + ‘ FROM Source)’
— Query final donde sacamos el total de columnas con error en la línea
,‘ Select *,’ + STRING_AGG(quoteName(column_name + ‘_error’), ‘+’) + ‘AS [$sq_transformation_error_count] FROM AddFlags ‘
FROM md.Source_Schema
WHERE active_for_creation = ‘Y’
GROUP BY TABLE_SCHEMA_NAME
,TABLE_NAME
 

Generación Vista de Validación

Por último, se genera una segunda vista de validación en las que añadiremos reglas para validar el formato de cada campo que nos interese. Una vez generado el código de la vista se actualiza el flag active_for_creation = ‘N’ para evitar generar por error el código de una vista generada previamente.

SELECT
–Instrucción para crear la vista de validación
‘CREATE VIEW val.’ + table_name + ‘ as ‘ +
–Template para añadir la lógica de validación
‘/*Template to add the validation logic*/’ +
–Primera cte para poner las reglas de validación
‘WITH Source as ( SELECT ‘ + STRING_AGG(quoteName(column_name) + ‘ as ‘ + QUOTENAME(Column_name), ‘,’) + ‘,’ +
–Columnas de validación
STRING_AGG(‘IIF(1=1,0,1) as ‘ + QUOTENAME(Column_name + ‘_error’), ‘,’) + ‘ FROM ctg.’ + QUOTENAME(TABLE_NAME) + ‘ WHERE [$sq_transformation_error_count] = 0)’
— Query final donde sacamos el total de columnas con error en la línea
+ ‘ Select *,’ + STRING_AGG(quoteName(column_name + ‘_error’), ‘+’) + ‘AS [$sq_validation_error_count] FROM Source ‘
FROM md.Source_Schema
WHERE active_for_creation = ‘Y’
GROUP BY TABLE_SCHEMA_NAME
,TABLE_NAME
 
–Desactivamos active_for_creation
UPDATE md.source_schema
SET active_for_creation = ‘N’
WHERE active_for_creation = ‘Y’
 

Resultado

Formateando el resultado de las queries anteriores llegamos a la definición de las vistas a continuación.

En la primera tenemos las columnas tal y como ven’ian en el fichero más una versión en la que se realiza el try_cast que convertirá al tipo deseado o devolverá null de no ser posible. Luego vemos como se comprueban los casos de null para macar el flag de error y por último se suman los flags  para formar el valor del número de errores en la fila que aparece informado en la columna [$sq_transformation_error_count].

CREATE VIEW ctg.samplecsv
AS
WITH Source
AS (
SELECT [DueDate] AS [DueDate_Original]
,[SalesOrderNumber] AS [SalesOrderNumber_Original]
,[PurchaseOrderNumber] AS [PurchaseOrderNumber_Original]
,[AccountNumber] AS [AccountNumber_Original]
,[CustomerID] AS [CustomerID_Original]
,[TotalDue] AS [TotalDue_Original]
,try_cast([DueDate] AS DATETIME) AS [DueDate]
,try_cast([SalesOrderNumber] AS INT) AS [SalesOrderNumber]
,try_cast([PurchaseOrderNumber] AS INT) AS [PurchaseOrderNumber]
,try_cast([AccountNumber] AS NVARCHAR(100)) AS [AccountNumber]
,try_cast([CustomerID] AS INT) AS [CustomerID]
,try_cast([TotalDue] AS MONEY) AS [TotalDue]
FROM [ldg].[samplecsv]
)
,AddFlags
AS (
SELECT *
,CASE
WHEN [DueDate] IS NULL
AND [DueDate_Original] IS NOT NULL
THEN 1
ELSE 0
END AS [DueDate_error]
,CASE
WHEN [SalesOrderNumber] IS NULL
AND [SalesOrderNumber_Original] IS NOT NULL
THEN 1
ELSE 0
END AS [SalesOrderNumber_error]
,CASE
WHEN [PurchaseOrderNumber] IS NULL
AND [PurchaseOrderNumber_Original] IS NOT NULL
THEN 1
ELSE 0
END AS [PurchaseOrderNumber_error]
,CASE
WHEN [AccountNumber] IS NULL
AND [AccountNumber_Original] IS NOT NULL
THEN 1
ELSE 0
END AS [AccountNumber_error]
,CASE
WHEN [CustomerID] IS NULL
AND [CustomerID_Original] IS NOT NULL
THEN 1
ELSE 0
END AS [CustomerID_error]
,CASE
WHEN [TotalDue] IS NULL
AND [TotalDue_Original] IS NOT NULL
THEN 1
ELSE 0
END AS [TotalDue_error]
FROM Source
)
SELECT *
,[DueDate_error] + [SalesOrderNumber_error] + [PurchaseOrderNumber_error] + [AccountNumber_error] + [CustomerID_error] + [TotalDue_error] AS [$sq_transformation_error_count]
FROM AddFlags
 

En la segunda vista se consulta sobre la vista de casteos, lógicamente sobre los valores que antes pasaron por el try_cast. Vemos una serie de condicioanles que dan lugar a los flags de error para cada columna. En la generación de las vista se utiliza IIF(1 = 1, 0, 1) de forma que no se controla ningún tipo de error, en este punto toca aplicar las reglas que correspondan como podría ser el formato de SalesOrderNumber o AccountNumber.

Por último, se filtran las filas que no hayan conseguido castear alguna de las columnas. [$sq_transformation_error_count] = 0

 

CREATE VIEW val.samplecsv
AS /*Template to add the validation logic*/
WITH Source
AS (
SELECT [DueDate] AS [DueDate]
,[SalesOrderNumber] AS [SalesOrderNumber]
,[PurchaseOrderNumber] AS [PurchaseOrderNumber]
,[AccountNumber] AS [AccountNumber]
,[CustomerID] AS [CustomerID]
,[TotalDue] AS [TotalDue]
,IIF(1 = 1, 0, 1) AS [DueDate_error]
,IIF(1 = 1, 0, 1) AS [SalesOrderNumber_error]
,IIF(1 = 1, 0, 1) AS [PurchaseOrderNumber_error]
,IIF(1 = 1, 0, 1) AS [AccountNumber_error]
,IIF(1 = 1, 0, 1) AS [CustomerID_error]
,IIF(1 = 1, 0, 1) AS [TotalDue_error]
FROM ctg.[samplecsv]
WHERE [$sq_transformation_error_count] = 0
)
SELECT *
,[DueDate_error] + [SalesOrderNumber_error] + [PurchaseOrderNumber_error] + [AccountNumber_error] + [CustomerID_error] + [TotalDue_error] AS [$sq_validation_error_count]
FROM Source
 

Conclusión

Hasta aquí llega la entrada de hoy. Hemos creado vistas de validación para la carga de ficheros, para ello utilizamos una serie de scripts que nos ahorrarán un trabajo tedioso y repetitivo en el caso de tener que desarrollar una solución para integrar múltiples ficheros.

Creemos que os puede ser de utilidad ya que para nosotros está siendo una herramienta de uso habitual en proyectos en los que estamos trabajando.

Utilizando estas vistas junto a una serie de comprobaciones en la carga en Integration Services hemos conseguido reducir la necesidad de dar soporte ante incidencias en los ficheros de entrada, acotando los errores y notificando al usuario con el caso concreto, ayudando a que las acciones relacionadas con la corrección de los ficheros puedan iniciarse sin dependencia de personal técnico.

¿Necesitas asesoramiento para impulsar tu proyecto de BI?

Acelera tus procesos de Business Analytics. Toma mejores decisiones, optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica. Trabajaremos juntos para proporcionarte información basada en tus datos. Pon en marcha tu proyecto de analítica y comienza a tomar decisiones estratégicas.

Más Info
0 Shares:
Share 0
Tweet 0
Share 0

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

— Previous article

Análisis de Tempdb con Power BI

Next article —

Implementando Databricks (SaaS) en Azure para evitar accesos no autorizados a los datos

You May Also Like