# Lenguaje Crono SQL

Crono SQL es un lenguaje de programación para facilitar el desarrollo ágil de proyectos ETL/DWH. En esta página se muestra la sintaxis de la sentencia SELECT mediante ejemplos.

# ¿Qué es Crono SQL?

Crono SQL es un lenguaje de programación creado por Pau Urquizu (fundador de Crono BI) 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 mucho más imperativo, más fácil de escribir, de leer, y de mantener que el SQL ISO.

En esta página se documenta el funcionamiento de la sentencia SELECT del lenguaje. La sintaxis SELECT de Crono SQL aporta algunas ventajas (algunas importantes) frente al SQL ISO. Sin embargo, el mayor beneficio del lenguaje se manifiesta en el resto de instrucciones DML (INSERT, UPDATE, MERGE, …), donde Crono SQL automatiza toda la lógica de carga. Por eso la sentencia SELECT es tan importante… ¡Es lo prácticamente lo único que tendrá que codificar el desarrollador de un proyecto ETL/DWH!

# Sentencia SELECT

En esta sección se documenta el funcionamiento de la sentencia SELECT del lenguaje. La sintaxis SELECT de Crono SQL aporta algunas ventajas (algunas importantes) frente al SQL ISO. Sin embargo, el mayor beneficio del lenguaje se manifiesta en el resto de instrucciones DML (INSERT, UPDATE, MERGE, …), donde Crono SQL automatiza toda la lógica de carga. Por eso la sentencia SELECT es tan importante… ¡Es lo prácticamente lo único que tendrá que codificar el desarrollador de un proyecto ETL/DWH!

A continuación se describen sistemáticamente todas las características soportadas en la sentencia SELECT del lenguaje Crono SQL.

# Basado en el lenguaje SQL

Proposición: Cualquier sentencia SELECT válida en SQL es válida también en Crono SQL

   

Si ninguna tabla participa en la consulta, se debe terminar la sentencia con el carácter punto y coma ";". En cualquier otro caso, el punto y coma es opcional.

   

Se pueden incluir las cláusulas JOIN, WHERE, GROUP BY, HAVING y/o ORDER BY

   

Se pueden utilizar las funciones propias del motor de base de datos o funciones definidas por el usuario.

   

# Referencia a columnas existentes

A diferencia del SQL ISO, en Crono SQL se puede hacer referencia a otra columna de la sentencia SELECT mediante el Alias de la columna.

   

# Prescindiendo del GROUP BY

Se puede utilizar la cláusula GROUP BY ALL para indicar que se agrupe por todas las columnas que no sean funciones de agregación.

   

Siempre se puede prescindir totalmente de la cláusula GROUP BY. Crono SQL incluirá las columnas necesarias en el SQL generado.

   

# USING

Se puede utilizar la cláusula USING para simplificar la sintaxis de los JOIN equi-join.

   

La cláusula USING también puede utilizarse cuando los campos de la equi-join tienen distinto nombre.

   

Si no se especifica el nombre de la tabla izquierda en la cláusula USING, se asume que es la tabla del FROM es la que participa en la relación.

   

Si la relación equi-join está formada por distintos campos, se pueden especificar en la cláusula USING separados por comas.

   

# CHECK SNOWFLAKE

La cláusula CHECK SNOWFLAKE, colocada justo después de todos los JOINs, verifica que las relaciones no pierden ni duplican ningún registro de la tabla del FROM. Se trata de una comprobación fundamental para validar que no estamos cometiendo ninguna equivocación al escribir la consulta y que los datos de origen son coherentes con lo esperado.

   

La cláusula CHECK SNOWFLAKE verifica que todas las ventas correspondan a un cliente y que ese cliente exista en la tabla de personas. Si no fuera así, la consulta no se ejecutaría y devolvería un error.

# Subconsultas

Se pueden incluir subconsultas.

   

# Subconsultas con FILTER y COLUMNS

Después del nombre de la tabla, se puede incluir la cláusula FILTER para seleccionar solo una parte de los registros de la tabla. El código SQL generado incluirá una subconsulta similar a la del Ejemplo anterior.

   

La cláusula FILTER es muy útil en combinación con la cláusula CHECK SNOWFLAKE. En el siguiente ejemplo, se verifica que cada persona tenga una única HomeAddress (o ninguna) y una única ShippingAddress (o ninguna). Si no fuera así, la consulta no duplicaría los registros porque devolvería un error previo.

   

Se puede utilizar la cláusula COLUMNS para seleccionar, renombrar, u operar sobre las columnas físicas de la tabla. El código SQL generado incluirá una subconsulta con esas columnas.

   

# ANTI JOIN

El lenguaje Crono SQL soporta todos los joins habituales:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • CROSS JOIN (también CROSS APPLY)

Además, implementa el ANTI JOIN. Un ANTI JOIN devuelve todos los registros de la izquierda que no aparecen en la parte derecha de la relación. Para ello, el SQL generado incluye un predicado NOT EXISTS IN (…)

La siguiente consulta devuelve todos los clientes que no tienen ninguna venta. Puede ampliar la información sobre los ANTI JOIN en el blog de SQL Server de Dale Burnett.

   

El ANTI JOIN se puede combinar con el resto de características del lenguaje.

Esta consulta devuelve todos las personas que no tienen Home Address.

   

# SEMI JOIN

El lenguaje Crono SQL implementa también la relación SEMI JOIN. Un SEMI JOIN devuelve todos los registros de la izquierda que aparecen en la parte derecha de la relación. Para ello, el SQL generado incluye un predicado EXISTS IN (…), por lo que a diferencia del INNER JOIN no duplica los registros en el resultado.

Esta consulta devuelve todos los clientes que tienen alguna venta (sin duplicados). Puede ampliar la información sobre los SEMI JOIN en el blog de SQL Server de Dale Burnett.

   

# UNPIVOT

Se puede utilizar el operador UNPIVOT (según la sintaxis de T-SQL) para despivotar las columnas de una tabla.

En este ejemplo, las columna “AddressLine1” y “AddressLine2” se han convertido en filas diferenciadas, duplicándose los registros.

   

# ORDER BY

Se puede utilizar la cláusula ORDER BY para forzar la ordenación del resultado.

   

El ORDER BY se puede escribir haciendo referencia a la posición de las columnas.

   

# SELECT DISTINCT

Se puede utilizar la palabra clave DISTINCT para obtener los valores distintos

   

# SELECT TOP

Se puede utilizar la palabra clave TOP para limitar el número de registros del resultado.

Esta consulta devuelve los 5 clientes con mayores ventas.

   

# OVER ()

Las funciones de ventana OVER (…) también están soportadas.

Esta consulta devuelve las ventas acumuladas desde el principio de cada año. La funciones de ventana, también llamadas funciones analíticas, tienen mucha utilidad en entornos ETL/DWH y permiten simplificar el desarrollo de muchos escenarios ETL comunes. Puede ampliar la información sobre las funciones de ventana en la documentación de la cláusula OVER en T-SQL.

   

# TOP OVER ()

Se puede incluir la cláusula OVER junto a la palabra clave TOP para limitar el número de registros por grupos de registros.

Esta consulta devuelve los tres clientes con más ventas en cada territorio.

   

La combinación TOP n OVER () tiene muchos usos en procesos ETL/DWH. La sentencia SQL generada es un consulta sobre una subconsulta de una subconsulta. La siguiente consulta devuelve la última venta de cada cliente.

   

# Subconsulta TIME_TABLE

La subconsulta predefinida TIME_TABLE permite obtener algunos de los campos habituales de una tabla de tiempo.

   

Se puede utilizar la subconsulta predefinida TIME_TABLE para construir la tabla de tiempo que sea necesaria en cada proyecto.

   

# Subconsulta INTEGERS BETWEEN

La subconsulta predefinida INTEGERS BETWEEN a AND b permite generar fácilmente una tabla de enteros consecutivos.

   

# Subconsultas ROWS, COLUMNS y ROW

Se puede utilizar ROWS para generar fácilmente una subconsulta con resultados estáticos. Se puede utilizar, por ejemplo, para generar un maestro que no existe en la base de datos. También existen los operadores ROW y COLUMN para generar una única fila o columna.

   

# Subconsulta FILE

Con el operador FILE, se puede leer un fichero plano (CSV) del sistema de archivos (o desde una URL) para generar una subconsulta. Se puede utilizar, por ejemplo, para generar un maestro que no existe en la base de datos. El fichero solo se lee en tiempo de compilación. El SQL generado es estático.

   

# WITH

Las sentencias CTE con cláusula WITH están soportadas.

   

# UNION y UNION ALL

Se pueden utilizar los operadores UNION y UNION ALL

Este ejemplo tiene únicamente fines didácticos. Para combinar de este modo dos o más consultas es preferible el operador COMBINE que se muestra continuación.

   

# COMBINE

El operador COMBINE BY permite combinar dos o más consultas en un único resultado.

   

Se pueden utilizar tablas distintas en cada consulta del COMBINE. En este ejemplo, se comparan las ventas y las compras por producto. El SQL generado combinará los resultados utilizando un FULL JOIN.

   

# MATERIALIZE

La cláusula MATERIALIZE permite crear una tabla temporal con el contenido de una subconsulta. Es decir, antes de la ejecución de la consulta, se crean las tablas temporales necesarias y finalmente se ejecuta la consulta utilizando dichas tablas. Esta estrategia de carga simplifica el plan de ejecución del motor de base de datos y se pueden obtener mejoras de rendimiento muy significativas, sin penalizar o dificultar la escritura de la consulta.

   

Con la cláusula MATERIALIZE, también se pueden materializar las consultas de una sentencia COMBINE. En este ejemplo, primero se ejecutará la consulta con las ventas, luego se ejecutará una consulta con las compras, y finalmente se combinarán en un único resultado.

   

# CAST automático

Se puede forzar el tipo de datos resultante de una columna especificándolo justo después del alias de la columna. El SQL generado incluirá una llamada a la función CAST.

   

# SELECTs anidados

Es posible incluir varios SELECT en una misma consulta. Esta sintaxis permite escribir rápidamente una consulta sobre el resultado de otra consulta. Son consultas encadenadas.

Este consulta devuelve la media de las ventas anuales de cada producto.

   

La cláusulas SELECT encadenadas permiten, por ejemplo, contar el número de registros que devuelve una consulta previa. La siguiente consulta ejecuta un count(*) sobre el resultado de la consulta inferior.

   

# Resumen

En resumen, si se conoce SQL, ya se conoce la parte más importante de Crono SQL. Crono SQL, simplemente, facilita la escritura de SQL y aporta algunas extensiones para necesidades comunes en ETL/DWH. Destacamos:

  • Posibilidad de referenciar a Alias de columnas de la consulta
  • No es necesario el GROUP BY
  • Sintaxis simplificada de los JOIN
  • Sentencia COMBINE
  • Cláusula MATERIALIZE
  • Cláusula CHECK SNOWFLAKE
  • Cláusulas COLUMNS y FILTER para reducir el número de subconsultas
  • Cláusula TOP OVER
  • Relaciones ANTI JOIN y SEMI JOIN
  • Subconsultas predefinidas de TIME_TABLE, INTEGERS, ROWS
  • Posibilidad de leer CSV desde la misma consulta mediante la extensión FILE
  • SELECTs anidados

# Sentencia INSERT

Crono SQL soporta la sintaxis estándar de la sentencia INSERT:

   

Sin embargo, especialmente cuando la tabla tiene muchas columnas, esta sintaxis es repetitiva e incómoda de mantener. Por ello, Crono SQL prescinde de la cláusula VALUES y asume que el nombre de los campos coincide con el alias de las columnas de la consulta de origen:

   

Por supuesto, para definir el origen de datos del INSERT se pueden utilizar todas las características de la sentencia SELECT de Crono SQL. En la siguiente consulta, por ejemplo, se verifica que las relaciones sean correctas antes de realizar la inserción. Es decir, si las relaciones pierden o duplican registros, no se ejecutará el INSERT.

   

También se puede utilizar, por ejemplo, la sentencia COMBINE o la funcionalidad de MATERIALIZE. En la siguiente consulta se verificará que todas las relaciones sean correctas, se crearán entonces las tablas temporales con la información de ventas y compras, y finalmente se unirán mediante un FULL JOIN en un único resultado a insertar.

   

Una necesidad habitual en ETL/DWH es insertar únicamente los registros que no existan en la tabla destino. Para ello, es necesarrio especificar la "clave de inserción". La clave de inserción se define mediante el carácter # delante del alias de cada campo. Como veremos a lo largo de este tutorial, las claves de todas las estrategias de carga se definen con el carácter #.

La siguiente consulta inserta los productos que no existan aún en la tabla dwh.DimProducts

   

Es es posible realizar una recarga completa mediante la sentencia DELETE AND INSERT. Esta sentencia elimina el contenido de la tabla y la recarga con los datos de la consulta de origen.

   

También es posible realizar un TRUNCATE AND INSERT

   

La opción PARTITION permite recargar solo una parte de la tabla. Por ejemplo, es habitual cargar solo los movimientos del mes en curso (si sabemos que los demás no han sido modificados). La siguiente sentencia recarga las ventas de los últimos 30 días:

   

Con el tipo de estrategias descritas en este apartado es posible conseguir que cada tabla se cargue desde una única sentencia, centralizando la lógica de negocio en un único punto, facilitando el desarrollo, y promocionando el seguimiento de buenas prácticas. Los mismos principios y métodos se utilizan en el resto de sentencias DML, como veremos a continuación.

# Sentencia UPDATE

Crono SQL soporta la sintaxis estándar de la sentencia UPDATE:

   

Sin embargo, este tipo de sentencia es fundamentalmente inútil en un proyecto ETL/DWH. Los datos que queremos actualizar habitualmente no están ni se pueden calcular a partir de la tabla de origen. Por ello, cada fabricante de base de datos ha resuelto esta limitación de distintas maneras. Por ejemplo:

   

Crono SQL soporta este tipo de sintaxis ISO, pero desaconseja claramente su uso.

En su lugar, el lenguaje Crono SQL propone una nueva sintaxis de UPDATE para actualizar una tabla en función del resultado de una consulta. Es una sintaxis similar a la del INSERT e igual al resto de instrucciones DML de Crono SQL. El codigo SQL generado corresponde a un MERGE.

   

De esta manera, seria trivial desnormalizar la información de productos en una única sentencia:

   

La anterior consulta actualiza los registros de DimProducts que hayan cambiado (y solo los que hayan cambiado). El caracter # sirve para indicar la clave de actualización, y suele coincidir con la "business key" de la consulta de origen.

Si se quieren actualizar todos los registros (hayan cambiado o no), se puede utilizar la sentencia UPDATE ALL, aunque raramente aportará ningún beneficio (¡Al contrario... penalizará el rendimiento por actualizar registros que no lo necesitan!).

   

Se puede utilizar la opción PARTITION para actualizar solo una parte de la tabla:

   

Aunque, evidentemente, en un escenario ETL/DWH es más habitual actualizar múltiples campos. La siguiente sentencia actualiza la información de las Bikes de AdventureWorks:

   

También se podrían actualizar los datos de una tabla a partir de sus propios datos. La siguiente sentencia es equivalente al primer UPDATE de este apartado:

   

# Sentencia MERGE

El lenguaje Crono SQL admite la sintaxis SQL estándar de la sentencia MERGE:

   

La sentencia MERGE, en función de una serie de condiciones definidas, ejecutará un UPDATE de los registros que hayan cambiado y un INSERT de los registros de la consulta origen que no existan en la tabla destino. Por este motivo se le conoce como UPSERT (UPDATE+INSERT).

Debería ser la "sentencia estrella" de cualquier proyecto ETL/DWH, porque en estos proyectos lo que en definitiva se necesita es replicar los datos del origen en la tabla destino (¡exactamente lo que promete hacer el MERGE!). Lamentablemente, la sintaxis ISO es compleja, está llena de repeticiones, y es muy difícil escribir correctamente. Por este motivo, muchos desarrolladores la desconocen, o no la usan, y buscan métodos alternativos para realizar el trabajo (con mucho peor rendimiento, habitualmente).

Crono SQL propone una nueva sintaxis del MERGE para solucionar estos problemas. Es una sintaxis idéntica a la del INSERT y el UPDATE anteriormente descritos.

   

Esta instrucción ejecutará un UPSERT (UPDATE+INSERT). Sin embargo, en ocasiones se necesita ejecutar también un DELETE para eliminar los registros que hayan desaparecido de la consulta original. Esta funcionalidad la podemos conseguir mediante la opción WHEN MISSING THEN DELETE

   

En ocasiones, para tratar las bajas, lo que se prefiere es realizar una baja lógica del registro. Es decir, marcar el registro como eliminado pero sin eliminarlo. Para cubrir este requerimiento tenemos la opción WHEN MISSING THEN SET

   

De hecho, esta instrucción es exactamente lo que se conoce como una carga "Slow Changing Dimension Type 1", tan habitual en proyectos ETL/DWH.

Se puede utilizar la opción PARTITION para realizar un MERGE sobre una parte de la tabla. La siguiente consulta actualiza únicamente la información de las Bikes:

   

En ocasiones, el optimizador de consultas del motor de base de datos realiza un mejor plan de ejecución si la consulta está materializada. En estos casos, podemos emplear la opción MATERIALIZE.

   

Por supuesto, también podemos utilizar la opción MATERIALIZE en las tablas o subconsultas interiores, y podemos utilizar todas las características de la sentencia SELECT (COMBINE, CHECK SNOWFLAKE, FILTER, etc.).

   

# Sentencia DELETE

Crono SQL soporta la sintaxis estándar de la sentencia DELETE de SQL:

   

Si intervienen otras tablas, se puede definir el predicado utilizando las expresiones IN() o EXISTS (). La sigiuente sentencia elimina las líneas de venta de un cliente en concreto:

   

De modo similar, la siguiente sentencia elimina las líneas de venta de otro cliente:

   

Crono SQL propone otra sintaxis de la sentencia DELETE (idéntica a la sintaxis de INSERT, UPDATE, y MERGE). La idea subyacente es que se ha de construir el SELECT de los datos que se quieren borrar. Solo el SELECT. Y Crono SQL eliminará precisamente esos registros:

   

De hecho, no es necesario que la consulta tenga ninguna referencia a la tabla de la que se quieren eliminar registros. El ejemplo anterior se puede simplificar de la siguiente manera:

   

En todos los casos, lo que marca los registros que se deben eliminar es la "clave de eliminación" (marcada con el carácter #).

La consulta del DELETE también puede utilizar todas las características del SELECT de Crono SQL. La siguiente sentencia elimina los clientes sin ninguna venta:

   

# Sentencia TRUNCATE

Crono SQL soporta la sintaxis estándar del a sentencia TRUNCATE de SQL:

   

Aunque no es propiamente una instrucción DML, se menciona aquí por su similitud con el DELETE.

# Sentencia LOAD

En apartados anteriores mostrábamos mediante ejemplos la sentencia SELECT y las sentencias INSERT, UPDATE, MERGE, DELETE y TRUNCATE para manipular datos. En este artículo se describe la sentencia LOAD del lenguaje Crono SQL.

La sentencia LOAD carga los datos en una tabla destino, siguiendo una estrategia de carga, a partir de una consulta de origen.

La sintaxis es muy sencilla. Por ejemplo, la siguiente sentencia carga tabla dimensión de DimProducts, creando su clave subrogada, a partir de una consulta de los datos fuente:

   

La lógica de carga está definida en la "estrategia de carga" (en el ejemplo, SCD1). En concreto, la estrategia de carga determina:

  • El modo de creación de los campos de la tabla destino (si no existe la tabla o no contiene todos los campos necesarios).
  • El nombre y el modo de actualización de los campos de auditoria (fecha de alta, fecha de baja, fecha de actualización, etc).
  • La sentencia DML que se utilizará para ejecutar la carga (INSERT, MERGE, UPDATE, etc...)

# Estrategias de carga

Un proyecto nuevo de Crono ETL ya incluye las estrategias de carga habituales. En concreto, al crear un proyecto ETL la siguientes estrategias ya están creadas y listas para ser utilizadas:

  • SCD1: Dimensión lentamente cambiante tipo 1
  • SCD2: Dimensión lentamente cambiante tipo 2
  • SNAPSHOT: Actualización completa
  • INCREMENTAL: Carga incremental
  • DELETE AND INSERT: Recarga parcial

Estas 5 estrategias, o un subconjunto de ellas, suelen ser suficientes para implementar cualquier proyecto ETL/DWH. Habitualmente, las estrategias SCD1 y SCD2 se utilizan para cargar tablas de dimensión, y SNAPSHOT, DELETE AND INSERT e INCREMENTAL se utilizan para cargar tablas de hechos. De todos modos, eso dependerá de las necesidades de cada tabla y de cada proyecto.

Las 5 estrategias predefinidas crean la tabla de destino si es necesario, así como la clave de negocio, y los campos de auditoria. Si es necesario, es posible definir nuevas estrategias o modificar las existentes fácilmente (la sintaxis para ello es muy compacta y se describirá en un artículo independiente).

A continuación se incluyen ejemplos de uso de estas 5 estrategias.

# Estrategia SCD1

La estrategia SCD1 es una carga de dimensión lentamente cambiante tipo 1. Esta estrategia de carga actualiza los registros que se han modificado desde la última carga y añade los nuevos, pero nunca elimina los registros existentes.

Se utiliza habitualmente para cargar tablas de dimensión, ya que se requiere mantener los registros antiguos para respetar la integridad (no queremos borrar un producto o un cliente que tal vez tienen ventas u otras transacciones asociadas).

   

Es importante observar que el campo ProductId está precedido por el carácter numeral #. Esta marca es importante ya que sirve para identificar la clave de actualización. Habitualmente coincide con la clave de negocio (código único que identifica a cada registro)

El código generado realiza un MERGE para actualizar o insertar cada registro. También informa convenientemente los campos FechaAlta, FechaBaja y FechaModificacion.

Si la tabla no existe en el momento de ejecución, la creará añadiendole el campo ProductSid como una clave subrogada (un entero autonumérico). También creará un UNIQUE CONSTRAINT para el campo ProductId (que debe ser único).

Si en el futuro se modifica la consulta, añadiendo más campos a la dimensión, Crono SQL generará también el código necesario para crear esos nuevos campos en la tabla.

En lugar de int IDENTITY(1,1) se puede utilizar el sinónimo increment, produciendo exactamente el mismo resultado:

   

Observad que en estos dos ejemplos, después de los JOIN, se ha incluido la clausula CHECK SNOWFLAKE. Por lo tanto, antes de realizar la carga, Crono SQL verificará que la consulta de origen no pierda ni duplique registros de staging.Products.

# Estrategia SCD2

La estrategia SCD2 es una carga de dimensión lentamente cambiante tipo 2. Esta estrategia guarda la historia completa de los cambios utilizando los campos de fecha de inicio y fecha de fin vigencia. De esta manera, es posible conocer que contenido que tenía cualquier registro en una fecha dada. En esta estrategia de carga, no se eliminan ni actualizan registros, únicamente se añaden los registros que han cambiado desde la última carga (y se actualizan los campos de vigencia que correspondan).

Se utiliza para cargar tablas de dimensión en las que es necesario guardar la historia de cambios.

El código es exactamente igual que el de las cargas SCD1, únicamente es necesario cambiar el nombre de la estrategia a utilizar: SCD2

   

El código generado realiza un MERGE para actualizar las fechas de fin vigencia, y un INSERT para añadir los registros que han cambiado y los nuevos registros.

También en este caso, Crono SQL genera automáticamente al código repetitivo:

  • Creación de la tabla y los campos necesarios
  • Creación de la clave subrogada
  • Creación y carga de los campos de auditoria
  • Comprobación de la corrección de los JOIN (ya que aparece la cláusula CHECK SNOWFLAKE).

Es interesante observar que la elección entre SCD1 y SCD2 depende únicamente de las necesidades de negocio. Técnicamente, utilizando Crono SQL, cuesta exactamente lo mismo programar una carga SCD de tipo 1 o de tipo 2.

# Estrategia SNAPSHOT

La estrategia SNAPSHOT realiza una actualización completa de la tabla. Es decir, añade, elimina o actualiza todos los registros de la tabla destino para que coincidan exactamente con el contenido de la consulta de origen.

Se utiliza en tablas de hechos pequeñas o en aquellas tablas de dimensión en que sabemos que no se eliminan registros, o que es aceptable que se eliminen registros obsoletos.

La sintaxis es idéntica a los casos anteriores. Únicamente es necesario cambiar el nombre de la estrategia de carga:

   

El ejemplo anterior, además de crear la tabla, e informar los campos de auditoria, actualizará el contenido de la tabla de DimProducts con los valores vigentes en el origen, eliminando los registros que se hayan eliminado en el origen.

El código generado es óptimo y presenta un gran rendimiento, por lo que esta estrategia es adecuada también en tablas de hechos no excesivamente grandes:

   

Incluso si la tabla de origen tuviese unos pocos millones de registros, la carga anterior se ejecutaría rápidamente (con el Hardware adecuado).

En cambio, si la tabla de origen tuviese varias decenas de millones de registros (o más) recomendaríamos utilizar alguna estrategia de carga incremental.

# Estrategia INCREMENTAL

La estrategia INCREMENTAL realiza una carga incremental de la la tabla. En general, las cargas incrementales son problemáticas. La mayor dificultad de las cargas incrementales es determinar los registros que se deben añadir. Es necesario identificar en origen los registros nuevos desde la última carga (mediante un timestamp, habitualmente). Otro riesgo de las cargas incrementales es que modificaciones extraordinarias en el origen pueden no reflejarse en en DWH (no siempre los responsables del ERP puede asegurar que ningún proceso o incidencia pueda modificar algún día registros antiguos). Por estos motivos, si el tiempo de ejecución es aceptable, es preferible utilizar SNAPSHOT siempre que sea posible, aunque el tiempo de ejecución sea algo mayor.

Las cargas incrementales son adecuadas para tablas de hechos con muchos millones de registros.

   

La anterior sentencia cargará las ventas del día anterior (y solo las del día anterior). Evidentemente, esta estrategia es débil y propensa a errores. Dejará de cargar registros si algún día no se ejecuta la carga, o podría duplicarlos si se ejecutase dos veces un mismo día. También fallaría si algún proceso del ERP se retrasase varios días en insertar los datos de alguna tienda... La siguiente estrategia resuelve parcialmente el problema:

   

En el caso anterior, se cargarían las ventas de los últimos 30 días que no se hayan cargado previamente. La marca # especifica la clave de inserción, es decir, que no se insertarán SalesOrderId que ya existan en la tabla de destino. Esta estrategia es recomendable si tenemos la seguridad de que ninguna venta tarda más de 30 días en cargarse en el sistema.

Otra opción sería añadir incrementalmente aquellos registros añadidos en el ERP desde la última carga (requiere un campo "timestamp" en el origen).

   

Observad como en este caso se ha declarado la variable @last con la fecha del último registro insertado.

En resumen, las cargas incrementales son adecuadas para aquellas situaciones en las que el origen tiene muchos millones de registros y solo se producen inserciones.

# Estrategia DELETE AND INSERT

La estrategia DELETE AND INSERT aplica una recarga total o parcial de la tabla destino. Es decir, elimina los registros existentes y los vuelve a cargar en función de los valores de la consulta de origen.

Es una buena alternativa a la carga INCREMENTAL en aquellos casos en que el ERP pueda aplicar cambios en registros "recientes" (en función del mes cerrado, por ejemplo).

El código Crono SQL se convierte en un DELETE seguido de un INSERT.

En este ejemplo, se elimina todo el contenido de la tabla y se recarga:

   

Evidentemente, esta estrategia seria muy ineficiente, sin embargo, en función del volumen de la tabla, podría ser aceptable eliminar y recargar el último mes o el último año. Por ejemplo, el siguiente ejemplo recarga las ventas del año en curso.

   

Se observa la clausula PARTITION que determina la porción de la tabla que se recargará.

# Resumen

La sentencia LOAD permite cargar una tabla aplicando alguna de las estrategias de carga predefinidas en el proyecto. Las estrategias predeterminadas son:

  • SCD1: Dimensión lentamente cambiante tipo 1
  • SCD2: Dimensión lentamente cambiante tipo 2
  • SNAPSHOT: Actualización completa
  • INCREMENTAL: Carga incremental
  • DELETE AND INSERT: Recarga parcial

El uso de un conjunto cerrado y reducido de estrategias aporta muchos beneficios:

  • El uso de estrategias asegura un código homogéneo y fácil de mantener, y permite que el desarrollador se centre en aquellas actividades que realmente aportan valor.
  • Una característica importante del lenguaje es que al programador le costará exactamente lo mismo aplicar cualquier estrategia de carga. Es decir, la decisión de utilizar una u otra no se verá influenciada por la dificultad de programarlo, por lo que la decisión se tomará en función de las necesidades del negocio.
  • Además, el código SQL generado está altamente optimizado para obtener siempre el máximo rendimiento. Crono SQL no actua como una caja negra sino que delega totalmente el trabajo a quien mejor sabe hacerlo (el motor de la base de datos).
  • Crono SQL genera automáticamente todo el código repetitivo (según la configuración de cada estrategia). No hemos de preocuparnos ni de crear la tabla. Todas las tablas tendrán campos de auditoría correctamente informados. Ni siquiera hemos de escribir el farragoso código necesario para ejecutar un MERGE, un UPDATE, o un INSERT.

Finalmente, recordar que es posible modificar o crear nuestras estrategias en función de nuestras necesidades particulares pero, en cualquier caso, será una tarea que deberá realizarse una vez al comenzar el proyecto, y se reutilizará a lo largo de todo el ciclo de vida del data warehouse.

# Sentencias DDL

En artículos anteriores hemos descritos la sintaxis de la sentecia SELECT de Crono SQL y el resto de sentencias de manipulación de datos (DML):

En este artículo se describe, mediante ejemplos, la sintaxis de:

  • CREATE PROCEDURE
  • CREATE FUNCTION
  • CREATE VIEW
  • CREATE TABLE
  • CREATE INDEX
  • CREATE DATABASE
  • CREATE SCHEMA

# CREATE PROCEDURE

Crono SQL admite la sintaxis estándar de SQL para crear procedimientos almacenados:

   

El código SQL generado, y sin que el programador tenga que escribir ni configurar nada, apunta en una tabla de log información sobre su ejecución (fecha de inicio, duración, etc.). De manera predeterminada, todos los procedimientos auditan el resultado de sus ejecuciones en una tabla de logs. Crono SQL promociona el uso de buenas prácticas y es una buena práctica mantener un log completo y fiable de todas las ejecuciones. La estrategia de log se puede configurar y personalizar a nivel de proyecto.

Por lo tanto, en el ejemplo anterior, la sentencia PRINT es innecesaria. De hecho, si el procedimiento tiene una sola instrucción, no es necesario tampoco crear el bloque BEGIN ... END. El siguiente código es equivalente

   

Se puede utilizar la instrucción CREATE OR REPLACE (también se admite CREATE OR ALTER) para que el mismo código sirva para crear inicialmente el procedimiento o modificarlo si ya existe:

   

Finalmente, si el procedimiento carga una única tabla (lo que recomendamos), se puede prescindir del nombre del procedimiento. Crono SQL escojerá un nombre apropiado sin que el desarollador tenga que elegir y memorizar uno.

   

Todos los anteriores ejemplos crearán tanto el procedimiento como la tabla DimProducts. Antes de cargar la tabla, se ejecutará la comprobación CHECK SNOWFLAKE para asegurar que las relaciones no pierden ni duplican registros. También se informarán los campos de auditoria durante la ejecución de la carga.

La sintaxis utilizada en el último ejemplo no es excepcional. De hecho, es el caso más normal, el recomendado, y el que puede utilizarse en prácticamente todas las tablas de un Data Warehouse implementado con Crono SQL.

Crono SQL facilita y promociona el principio de responsabilidad única (SRP). Por lo tanto, consideramos una buena práctica que cada procedimiento cargue una única tabla. Y que cada tabla se cargue desde un único procedimiento. Y que cada procedimiento tenga una única instrucción, y que esa instrucción sea un LOAD que utiliza una única estrategia. Idealmente, todas las tablas se deberían cargar de este modo.

Para ejecutar un procedimiento, se puede utilizar la sentencia EXECUTE (o el sinónimo EXEC):

   

En el caso de los procedimientos "anónimos" se debe utilizar EXECUTE LOAD (o EXEC LOAD):

   

El flujo normal de ejecución de la carga del DWH, se puede crear mediante un procedimiento que llame secuencialmente a la carga de todas las tablas:

   

Y, de este modo, la carga del DWH se ejecutaría llamando a este procedimiento desde el programador de tareas de Windows o mediante el programador propio de la base de datos:

   

Se puede utilizar DROP PROCEDURE para eliminar un procedimiento existente. También se puede utilizar DROP PROCEDURE IF EXISTS para eliminar un procedimiento en el caso de que efectivamente exista.

   

# CREATE FUNCTION

La sintaxis para crear una función escalar es la siguiente:

   

También pueden crearse funciones que devuelven tablas de este modo simplificado:

   

Se puede utilizar DROP FUNCTION o DROP FUNCTION IF EXISTS para eliminar una función.

   

# CREATE VIEW

Crono SQL admite la sintaxis estándar para crear vistas:

   

Se puede utilizar CREATE OR ALTER VIEW o CREATE OR REPLACE VIEW para actualizar la vista en el caso de que ya exista.

   

Para eliminar una vista existente se puede utilizar DROP VIEW o DROP VIEW IF EXISTS

   

# CREATE TABLE

En general, no es necesario escribir explícitamente el CREATE TABLE de las tablas de un data warehouse desarrollado con Crono SQL. Las estrategias de carga ya crean implícitamente las tablas y los campos necesarios.

De todos modos, si se prefiere, pueden crearse las tablas utilizando la sintaxis habitual de CREATE TABLE.

   

Se puede utilizar CREATE TABLE IF NOT EXISTS para crearla únicamente si no existe aún.

La sentencia CREATE OR REPLACE TABLE elimina la tabla si ya existe (DROP TABLE) y posteriormente la recrea.

   

También puede utilizarse CREATE OR ALTER TABLE para añadir nuevos campos, restricciones o índices a una tabla existente.

   

Crono SQL proporciona un método rápido de crear la clave primaria y la clave de negocio. Para ello, se utiliza la marca # para identificar la PRIMARY KEY y la marca ## para identificar la clave de negocio, y Crono SQL creará un UNIQUE CONSTRAINT. Por puspuesto, la clave primaria puede estar formada por varios campos (aunque no sea recomendable en entornos DWH) y para ello debe ponerse la marca # delante de cada uno de los campos de la PK. También la clave de negocio puede estar formada por varios campos.

   

Además de los campos de la tabla, la sintaxis de Crono SQL admite las siguientes restricciones y indíces:

  • Restricciones NULL y NOT NULL
  • Restricciones IDENTITY
  • Restricciones UNIQUE y NONUNIQUE (que pueden ser CLUSTERED o NONCLUSTERED)
  • Restricciones FOREIGN KEY/REFERENCES (con la opción de ON CASCADE DELETE)
  • Restricciones DEFAULT
  • Indices UNIQUE y NONUNIQUE (que pueden ser CLUSTERED o NONCLUSTERED, y con la opción INCLUDE)
   

Algunas características de esta sintaxis:

  • Es posible definir restricciones IDENTITY, NULL, UNIQUE, REFERENCES y DEFAULT en línea con el campo.
  • Es posible omitir el nombre de indices y restricciones. Crono SQL utilizará un criterio de nomenclatura predefinido.

Si se requiere alguna funcionalidad de la base de datos que no está soportada por la sintaxis de Crono SQL, se pueden utilizar los literales SQL. Por ejemplo, puede utilizarse un literal SQL para especificar el file group donde debe crearse un indice, o para definir el particionado, o crear indices columnares (Crono SQL no parseará ni traducirá el literal SQL).

   

También se puede crear una tabla directamente a partir del resultado de una consulta.

   

Para eliminar una tabla, Crono SQL proporciona las sentencias DROP TABLE y DROP TABLE IF EXISTS.

   

# CREATE INDEX

Los índices se pueden crear desde la misma sentencia CREATE TABLE pero también pueden definirse a postereri mediante las sentencias CREATE INDEX.

   

Se puede utilizar la instrucción CREATE INDEX IF NOT EXISTS para crear un índice si aún no existe.

   

También se puede utilizar CREATE OR REPLACE INDEX para crear un ínidice o recrearlo si ya existe. El siguiente ejemplo muestra, además, la posibilidad de utilizar la cláusla INCLUDE para añadir columnas adicionales al indice:

   

Se puede crear índices UNIQUE, CLUSTERED y NONCLUSTERED.

   

Mediante literales SQL se puede crear cualquier otro índice que admita la base de datos.

   

La instrucción DROP INDEX permite eliminar un índice.

   

# CREATE DATABASE

La sentencia CREATE DATABASE permite crear una base de datos con las opciones predeterminadas.

   

También se puede especificar la intercalación:

   

# CREATE SCHEMA

Se puede crear un esquema con las instrucciones CREATE SCHEMA y CREATE SCHEMA IF NOT EXISTS

   

Es posible establecer el propietario del esquema.