Un SQL con superpoderes (parte 4)

El problema

En artículos anteriores criticaba al lenguaje SQL por ser demasiado “redundante”:

• Cada vez que repites código Dios mata un gatito (parte 1)
• Una crítica al lenguaje SQL (parte 2)
• Una crítica al lenguaje SQL (parte 3)

En realidad, solo estaba apuntando a la punta del iceberg cuando mencionaba estos defectos del lenguaje SQL:

1. Repetición de las mismas fórmulas en las distintas cláusulas de la sentencia
2. La insensatez de las cláusulas GROUP BY. ¿En qué estarían pensando los creadores del lenguaje cuando vieron la necesidad de incluir la cláusula GROUP BY?
3. Repetición de los JOIN en las distintas sentencias
4. Dificultad de realizar operaciones entre filas
5. Dificultad de realizar operaciones entre tablas de hecho

Estos problemas se agravan exponencialmente cuando se necesita realizar la carga incremental de una tabla, o una carga de una dimensión lentamente cambiante… De hecho, tanto se agrava el problema que, habitualmente, se convierte en inviable construir un DWH solo con procedimientos SQL y se requiere el uso de caras y complejas herramientas ETL…

Crono SQL

Nos llena de orgullo y satisfacción presentar el lenguaje Crono SQL.

• Crono SQL es un lenguaje de programación creado para facilitar el desarrollo ágil de proyectos ETL/DWH.
• Crono SQL es un lenguaje que compila en SQL. Crono SQL y SQL tienen la misma relación, por ejemplo, que TypeScript y JavaScript, o Markdown y HTML.
• Crono SQL extiende la sintaxis de SQL, por tanto cualquier sentencia SELECT existente debería funcionar sin problemas.
• El lenguaje Crono SQL pretende simplificar la sintaxis del SQL evitando las repeticiones de código y automatizando la generación del código más farragoso y repetitivo.
• Crono SQL es un lenguaje más imperativo, más fácil de escribir, de leer, y de mantener que el SQL ISO.

Para mostrar las características del lenguaje, vamos a ver algunos algunos ejemplos sencillos. Son las mismas consultas que utilizamos en los artículos anteriores para mostrar las “deficiencias” del SQL.

Para empezar, la siguiente consulta muestra como es innecesario incluir la cláusula GROUP BY. También se observa una sintaxis simplificada de los JOIN y vemos que se han reutilizado varias veces las fórmulas de Unidades, Importe y Precio Medio.

-- Ejemplo 1
SELECT
  DimProduct.EnglishProductName AS Producto,
  sum(sales.OrderQuantity) AS Unidades,
  sum(sales.SalesAmount) AS Importe,
  Importe/Unidades AS [Precio Medio]
FROM FactResellerSales sales
INNER JOIN DimProduct USING ProductKey
INNER JOIN DimProductSubCategory USING DimProduct(ProductSubcategoryKey)
INNER JOIN DimProductCategory USING DimProductSubCategory(ProductCategoryKey)
INNER JOIN DimDate USING (OrderDateKey DateKey)
WHERE
  DimProductCategory.SpanishProductCategoryName='Bicicleta'
  AND DimDate.SpanishMonthName='Enero'
  AND DimDate.CalendarYear=2008
ORDER BY [Precio Medio] DESC

SELECT
  DimProduct.EnglishProductName AS Producto,
  sum(sales.OrderQuantity) AS Unidades,
  sum(sales.SalesAmount) AS Importe,
  sum(sales.SalesAmount)/sum(sales.OrderQuantity) AS [Precio Medio]
FROM FactResellerSales sales
INNER JOIN DimProduct ON (sales.ProductKey=DimProduct.ProductKey)
INNER JOIN DimProductSubCategory ON (DimProduct.ProductSubcategoryKey=DimProductSubCategory.ProductSubcategoryKey)
INNER JOIN DimProductCategory ON (DimProductSubCategory.ProductCategoryKey=DimProductCategory.ProductCategoryKey)
INNER JOIN DimDate ON (sales.OrderDateKey=DimDate.DateKey)
WHERE
  DimProductCategory.SpanishProductCategoryName='Bicicleta'
  AND DimDate.SpanishMonthName='Enero'
  AND DimDate.CalendarYear=2008
GROUP BY DimProduct.EnglishProductName
ORDER BY (sum(sales.SalesAmount)/sum(sales.OrderQuantity)) DESC

Los ejemplos 2 y 3 serían similares, por lo que los omitiremos de momento, y veremos directamente el ejemplo 4. En este caso, utilizamos la instrucción COMBINE BY para comparar las ventas de 2 años distintos (operaciones entre filas).

-- Ejemplo 4
COMBINE  BY EnglishProductName
  sales1 AS (
    SELECT
      DimProduct.EnglishProductName AS EnglishProductName,
      sum(sales.OrderQuantity) AS Quantity2007
    FROM FactResellerSales sales
    INNER JOIN DimProduct USING ProductKey
    INNER JOIN DimProductSubCategory USING DimProduct(ProductSubcategoryKey)
    INNER JOIN DimProductCategory USING DimProductSubCategory(ProductCategoryKey)
    INNER JOIN DimDate USING (OrderDateKey DateKey)
    WHERE
      DimDate.CalendarYear=2007
      AND DimProductCategory.SpanishProductCategoryName='Bicicleta'
      AND DimDate.SpanishMonthName='Enero'),
  sales2 AS (
    SELECT
      DimProduct.EnglishProductName AS EnglishProductName,
      sum(sales.OrderQuantity) AS Quantity2008
    FROM FactResellerSales sales
    INNER JOIN DimProduct USING ProductKey
    INNER JOIN DimProductSubCategory USING DimProduct(ProductSubcategoryKey)
    INNER JOIN DimProductCategory USING DimProductSubCategory(ProductCategoryKey)
    INNER JOIN DimDate USING (OrderDateKey DateKey)
    WHERE
      DimDate.CalendarYear=2008
      AND DimProductCategory.SpanishProductCategoryName='Bicicleta'
      AND DimDate.SpanishMonthName='Enero')

SELECT
  coalesce(sales1.EnglishProductName,sales2.EnglishProductName) EnglishProductName,
  sales1.Quantity2007 Quantity2007,
  sales2.Quantity2008 Quantity2008
FROM 
    (SELECT
      DimProduct.EnglishProductName AS EnglishProductName,
      sum(sales.OrderQuantity) AS Quantity2007
    FROM FactResellerSales sales
    INNER JOIN DimProduct ON (sales.ProductKey=DimProduct.ProductKey)
    INNER JOIN DimProductSubCategory ON (DimProduct.ProductSubcategoryKey=DimProductSubCategory.ProductSubcategoryKey)
    INNER JOIN DimProductCategory ON (DimProductSubCategory.ProductCategoryKey=DimProductCategory.ProductCategoryKey)
    INNER JOIN DimDate ON (sales.OrderDateKey=DimDate.DateKey)
    WHERE
      DimDate.CalendarYear=2007
      AND DimProductCategory.SpanishProductCategoryName='Bicicleta'
      AND DimDate.SpanishMonthName='Enero'
    GROUP BY DimProduct.EnglishProductName) sales1
FULL JOIN 
    (SELECT
      DimProduct.EnglishProductName AS EnglishProductName,
      sum(sales.OrderQuantity) AS Quantity2008
    FROM FactResellerSales sales
    INNER JOIN DimProduct ON (sales.ProductKey=DimProduct.ProductKey)
    INNER JOIN DimProductSubCategory ON (DimProduct.ProductSubcategoryKey=DimProductSubCategory.ProductSubcategoryKey)
    INNER JOIN DimProductCategory ON (DimProductSubCategory.ProductCategoryKey=DimProductCategory.ProductCategoryKey)
    INNER JOIN DimDate ON (sales.OrderDateKey=DimDate.DateKey)
    WHERE
      DimDate.CalendarYear=2008
      AND DimProductCategory.SpanishProductCategoryName='Bicicleta'
      AND DimDate.SpanishMonthName='Enero'
    GROUP BY DimProduct.EnglishProductName) AS sales2 ON (sales1.EnglishProductName=sales2.EnglishProductName)

También se puede utilizar la instrucción COMBINE BY para hacer operaciones entre tablas de hecho diferentes. En el ejemplo 5, combinamos las ventas de internet con las ventas en tienda:

--Ejemplo 5 
COMBINE BY CalendarYear
  isales1 AS (
    SELECT
      DimDate.CalendarYear AS CalendarYear,
      sum(isales.SalesAmount) AS ventasInternet
    FROM FactInternetSales isales
    INNER JOIN DimDate ON (isales.OrderDateKey=DimDate.DateKey)
    INNER JOIN DimCustomer ON (isales.CustomerKey=DimCustomer.CustomerKey)
    INNER JOIN DimGeography ON (DimCustomer.GeographyKey=DimGeography.GeographyKey)
    WHERE DimGeography.SpanishCountryRegionName='Francia'
    GROUP BY DimDate.CalendarYear),
  sales1 AS (
    SELECT
      DimDate.CalendarYear AS CalendarYear,
      sum(sales.SalesAmount) AS ventasReseller
    FROM FactResellerSales sales
    INNER JOIN DimReseller ON (sales.ResellerKey=DimReseller.ResellerKey)
    INNER JOIN DimGeography ON (DimReseller.GeographyKey=DimGeography.GeographyKey)
    INNER JOIN DimDate ON (sales.OrderDateKey=DimDate.DateKey)
    WHERE DimGeography.SpanishCountryRegionName='Francia'
    GROUP BY DimDate.CalendarYear)

SELECT
  coalesce(isales1.CalendarYear,sales1.CalendarYear) CalendarYear,
  isales1.ventasInternet ventasInternet,
  sales1.ventasReseller ventasReseller
FROM 
    (SELECT
      DimDate.CalendarYear AS CalendarYear,
      sum(isales.SalesAmount) AS ventasInternet
    FROM FactInternetSales isales
    INNER JOIN DimDate ON (isales.OrderDateKey=DimDate.DateKey)
    INNER JOIN DimCustomer ON (isales.CustomerKey=DimCustomer.CustomerKey)
    INNER JOIN DimGeography ON (DimCustomer.GeographyKey=DimGeography.GeographyKey)
    WHERE DimGeography.SpanishCountryRegionName='Francia'
    GROUP BY DimDate.CalendarYear) isales1
FULL JOIN 
    (SELECT
      DimDate.CalendarYear AS CalendarYear,
      sum(sales.SalesAmount) AS ventasReseller
    FROM FactResellerSales sales
    INNER JOIN DimReseller ON (sales.ResellerKey=DimReseller.ResellerKey)
    INNER JOIN DimGeography ON (DimReseller.GeographyKey=DimGeography.GeographyKey)
    INNER JOIN DimDate ON (sales.OrderDateKey=DimDate.DateKey)
    WHERE DimGeography.SpanishCountryRegionName='Francia'
    GROUP BY DimDate.CalendarYear) AS sales1 ON (isales1.CalendarYear=sales1.CalendarYear)

Como véis, se trata de una sintaxis similar a la del SQL estándar, pero aporta algunas variantes que facilitan la escritura y evitan las repeticiones. Hemos suprimido la necesidad del GROUP BY, y hemos simplificado algo el resto de la consulta. Aún no se aprecia cómo resolver la problemática de los JOIN: ¿Por qué hemos de codificar las mismas relaciones una y otra vez en cada consulta? Esta cuestión, sin duda importante, la comentaremos mañana aquí mismo.

De hecho, la sentencia SELECT del lenguaje Crono SQL aporta algunas ventajas (algunas importantes), pero donde se muestra toda la potencia del lenguaje es en el resto de intrucciones: INSERT, UPDATE, MERGE, … Por ejemplo, este es todo el código necesario para construir una carga de dimension lentamente cambiante tipo 1 (SCD1):

MERGE dwh.DimProducts WHEN MISSING THEN SET FechaBaja=getdate(), Deleted=1
select 
  ProductID #ProductId,
  Product.Name Product,
  ProductCategory.name ProductCategory,
  ProductSubCategory.name ProductSubCategory,
  ProductNumber,
  ProductModel.name ProductModel,
  Product.Color,
  Product.StandardCost ProductCost,
  NULL FechaBaja,
  0 Deleted
FROM staging.Product
LEFT JOIN staging.ProductSubCategory using ProductSubcategoryID
LEFT JOIN staging.ProductCategory using ProductSubCategory(ProductCategoryId)
LEFT JOIN staging.ProductModel using ProductModelID

;WITH
query AS (
  SELECT
    ProductID AS ProductId,
    Product.Name AS Product,
    ProductCategory.name AS ProductCategory,
    ProductSubCategory.name AS ProductSubCategory,
    ProductNumber,
    ProductModel.name AS ProductModel,
    Product.Color AS Color,
    Product.StandardCost AS ProductCost,
    null AS FechaBaja,
    0 AS Deleted
  FROM staging.Product
  LEFT JOIN staging.ProductSubCategory ON (Product.ProductSubcategoryID=ProductSubCategory.ProductSubcategoryID)
  LEFT JOIN staging.ProductCategory ON (ProductSubCategory.ProductCategoryId=ProductCategory.ProductCategoryId)
  LEFT JOIN staging.ProductModel ON (Product.ProductModelID=ProductModel.ProductModelID)
)
MERGE dwh.DimProducts AS DimProducts
USING query ON query.ProductId=DimProducts.ProductId
WHEN MATCHED AND ((DimProducts.Product<>query.Product OR (DimProducts.Product IS NULL AND query.Product IS NOT NULL) OR  (DimProducts.Product IS NOT NULL AND query.Product IS NULL)
                  OR DimProducts.ProductCategory<>query.ProductCategory OR (DimProducts.ProductCategory IS NULL AND query.ProductCategory IS NOT NULL) OR  (DimProducts.ProductCategory IS NOT NULL AND query.ProductCategory IS NULL)
                  OR DimProducts.ProductSubCategory<>query.ProductSubCategory OR (DimProducts.ProductSubCategory IS NULL AND query.ProductSubCategory IS NOT NULL) OR  (DimProducts.ProductSubCategory IS NOT NULL AND query.ProductSubCategory IS NULL)
                  OR DimProducts.ProductNumber<>query.ProductNumber OR (DimProducts.ProductNumber IS NULL AND query.ProductNumber IS NOT NULL) OR  (DimProducts.ProductNumber IS NOT NULL AND query.ProductNumber IS NULL)
                  OR DimProducts.ProductModel<>query.ProductModel OR (DimProducts.ProductModel IS NULL AND query.ProductModel IS NOT NULL) OR  (DimProducts.ProductModel IS NOT NULL AND query.ProductModel IS NULL)
                  OR DimProducts.Color<>query.Color OR (DimProducts.Color IS NULL AND query.Color IS NOT NULL) OR  (DimProducts.Color IS NOT NULL AND query.Color IS NULL)
                  OR DimProducts.ProductCost<>query.ProductCost OR (DimProducts.ProductCost IS NULL AND query.ProductCost IS NOT NULL) OR  (DimProducts.ProductCost IS NOT NULL AND query.ProductCost IS NULL)
                  OR DimProducts.FechaBaja<>query.FechaBaja OR (DimProducts.FechaBaja IS NULL AND query.FechaBaja IS NOT NULL) OR  (DimProducts.FechaBaja IS NOT NULL AND query.FechaBaja IS NULL)
                  OR DimProducts.Deleted<>query.Deleted OR (DimProducts.Deleted IS NULL AND query.Deleted IS NOT NULL) OR  (DimProducts.Deleted IS NOT NULL AND query.Deleted IS NULL))) THEN
  UPDATE SET
    Product=query.Product,
    ProductCategory=query.ProductCategory,
    ProductSubCategory=query.ProductSubCategory,
    ProductNumber=query.ProductNumber,
    ProductModel=query.ProductModel,
    Color=query.Color,
    ProductCost=query.ProductCost,
    FechaBaja=query.FechaBaja,
    Deleted=query.Deleted
WHEN NOT MATCHED THEN
  INSERT (ProductId,Product,ProductCategory,ProductSubCategory,ProductNumber,ProductModel,Color,ProductCost,FechaBaja,Deleted) VALUES (
    query.ProductId,
    query.Product,
    query.ProductCategory,
    query.ProductSubCategory,
    query.ProductNumber,
    query.ProductModel,
    query.Color,
    query.ProductCost,
    query.FechaBaja,
    query.Deleted)
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET
    FechaBaja=getdate(),
    Deleted=1;

Pensamos que Crono SQL supone un cambio de paradigma en la manera de afrontar los proyectos ETL/DWH. Con Crono SQL, construir un DWH es mucho más sencillo que con una herramienta tradicional, y por supuesto es incomparablemente más asequible que hacerlo programando en solitario SQL tradicional. Pero no es solo una cuestión de complejidad o rapidez. Crono SQL promociona el seguimiento de buenas prácticas y aporta herramientas para detectar pronto los errores en los datos o los errores en la codificación. Todo ello provoca que el resultado sea más mantenible y que contenga menos errores.

Todo eso, junto con las objeciones que podáis plantear, será material para los próximos artículos.

Si queréis descubrir el resto de características del lenguaje Crono SQL, podéis consultar la documentación en la web de soporte de Crono:

• Sentencia SELECT de Crono SQL
• Sentencias INSERT, UPDATE, MERGE, DELETE, TRUNCATE

En próximos artículos, aquí mismo, mostraremos más ejemplos, desgranaremos el resto de características del lenguaje, y daremos instrucciones sobre cómo podéis probarlo de primera mano, si queréis.

Recuerda que podéis apuntaros a nuestra newletter sobre Business Intelligence o seguirnos en redes sociales:

• La newsletter de Business Intelligence fácil
• Twitter @bifacil
• Linkdin Crono Business Intelligence
• Facebook Crono Business Intealligence

Gracias por la atención. Gracias por difundir.