Capítulo 4. Explorar los comandos, tipos de datos y funciones SQL de Snowflake

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: translation-feedback@oreilly.com

Como hemos aprendido en capítulos anteriores, Snowflake se construyó para almacenar datos en un formato columnar optimizado y comprimido dentro de una base de datos relacional. Los usuarios finales de los datos de Snowflake necesitan acceder a los datos almacenados y poder dar instrucciones para realizar tareas, llamar a funciones y ejecutar consultas sobre los datos. La manera de conseguirlo es con el lenguaje de programación estándar para bases de datos relacionales, el Lenguaje de Consulta Estructurado (SQL). Snowflake es compatible con SQL:ANSI, la versión normalizada más común de SQL. Además de la compatibilidad con SQL para datos estructurados, Snowflake ofrece compatibilidad nativa con formatos de datos semiestructurados como JSON y XML. Snowflake también admite datos no estructurados.

El enfoque principal de este capítulo es aprender los fundamentos de la utilización de las hojas de trabajo de Snowflake para ejecutar una variedad de comandos SQL utilizando diferentes tipos de datos y funciones. Además de utilizar hojas de trabajo en la interfaz de usuario web de Snowflake, es posible utilizar un cliente de línea de comandos nativo de Snowflake, conocido como SnowSQL, para crear y ejecutar comandos SQL. En el Capítulo 6 se ofrecerán más detalles sobre SnowSQL.

Además de conectarte a Snowflake mediante la interfaz de usuario web o SnowSQL, puedes utilizar controladores ODBC y JDBC para acceder a los datos de Snowflake a través de aplicaciones externas como Tableau y Looker. Exploraremos las conexiones con Tableau y Looker en el Capítulo 12. También pueden utilizarse conectores nativos como Python y Spark para desarrollar aplicaciones de conexión a Snowflake.

Para ayudarte a prepararte para dominar los temas avanzados de los próximos capítulos, primero deberemos centrarnos en aprender los conceptos básicos de los comandos SQL de Snowflake, los tipos de datos y las funciones de Snowflake.

Trabajo de preparación

Crea una nueva hoja de trabajo titulada Capítulo4 Ejemplos de sintaxis, tipos de datos y funciones. Consulta "Navegar por las hojas de trabajo de Snowsight" si necesitas ayuda para crear una nueva hoja de trabajo. Para establecer el contexto de la hoja de trabajo, asegúrate de que estás en la hoja de trabajo Ejemplos de sintaxis y utilizando el rol SYSADMIN y el almacén virtual COMPUTE_WH .

Trabajar con comandos SQL en Snowflake

SQL puede dividirse en cinco tipos de comandos de lenguaje diferentes. Para crear objetos Snowflake, necesitas utilizar comandos del Lenguaje de Definición de Datos (DDL). Para dar acceso a esos objetos necesitas el Lenguaje de Control de Datos (DCL). A continuación, utilizarás comandos del Lenguaje de Manipulación de Datos (DML) para manipular los datos dentro y fuera de Snowflake. Los comandos del Lenguaje de Control de Transacciones (TCL) te permiten gestionar bloques de transacciones. Las sentencias del Lenguaje de Consulta de Datos (DQL) se utilizan para consultar realmente los datos. A continuación se muestra una lista de comandos SQL comunes, organizados por tipo:

Comandos DDL:
  • CREATE
  • ALTER
  • TRUNCATE
  • RENAME
  • DROP
  • DESCRIBE
  • SHOW
  • USE
  • SET/UNSET
  • COMMENT
Comandos DCL:
  • GRANT
  • REVOKE
Comandos DML:
  • INSERT
  • MERGE
  • UPDATE
  • DELETE
  • COPY INTO
  • PUT
  • GET
  • LIST
  • VALIDATE
  • REMOVE
Comandos TCL:
  • BEGIN
  • COMMIT
  • ROLLBACK
  • CREATE
Comando DQL:
  • SELECT

Cada uno de estos cinco tipos diferentes de lenguaje de comandos, y sus comandos asociados, se tratarán brevemente en las secciones siguientes. Puedes encontrar una lista completa de todos los comandos SQL de Snowflake en la documentación en línea de Snowflake.

Comandos DDL

Los comandos DDL son los comandos SQL utilizados para definir el esquema de la base de datos. Los comandos crean, modifican y eliminan estructuras de base de datos. Además, los comandos DDL se pueden utilizar para realizar operaciones de sesión a nivel de cuenta, como establecer parámetros, como veremos más adelante en el capítulo cuando hablemos de los comandos SET y UNSET. Los comandos DDL incluyen CREATE, ALTER, TRUNCATE, RENAME, DROP, DESCRIBE, SHOW, USE y COMMENT. A excepción del comando COMMENT, cada comando DDL toma un tipo de objeto y un identificador.

Los comandos DDL de Snowflake manipulan objetos como bases de datos, almacenes virtuales, esquemas, tablas y vistas; sin embargo, no manipulan datos. Consulta el Capítulo 3, dedicado a la demostración de los comandos DDL de Snowflake, para obtener explicaciones detalladas y muchos ejemplos prácticos.

Comandos DCL

Los comandos DCL son los comandos SQL utilizados para activar el control de acceso. Algunos ejemplos de comandos DCL son GRANT y REVOKE. El Capítulo 5 te llevará a través de una serie completa y detallada de ejemplos que utilizan comandos DCL para mostrarte cómo asegurar los objetos Snowflake.

Comandos DML

Los comandos DML son los comandos SQL utilizados para manipular los datos. Los comandos DML tradicionales como INSERT, MERGE, UPDATE y DELETE existen para ser utilizados en la manipulación general de datos. Para la carga y descarga de datos, Snowflake proporciona COPY INTO <table> y COPY INTO <location> comandos. Además, los comandos DML de Snowflake incluyen algunos comandos que no realizan ninguna manipulación real de los datos, sino que se utilizan para organizar y gestionar los archivos almacenados en las ubicaciones de Snowflake. Algunos ejemplos son VALIDATE, PUT, GET, LIST y REMOVE. El Capítulo 6 explorará muchos de los comandos DML de Snowflake.

Comandos TCL

Los comandos TCL son los comandos SQL utilizados para gestionar bloques de transacciones dentro de Snowflake. Se pueden utilizar comandos como BEGIN, COMMIT, y ROLLBACK para transacciones de varias sentencias en una sesión. Una transacción Snowflake es un conjunto de sentencias SQL de lectura y escritura que se procesan juntas como una unidad. Por defecto, y tras el éxito de la consulta, una sentencia DML que se ejecute por separado se consignará individualmente o se revertirá al final de la sentencia, si la consulta falla.

Comando DQL

El comando DQL es el comando SQL que se utiliza como sentencia o cláusula para recuperar los datos que cumplen los criterios especificados en el comando SELECT. Ten en cuenta que el comando SELECT es el único comando DQL; se utiliza para recuperar datos y lo hace especificando la ubicación y utilizando después la sentencia WHERE para incluir los atributos necesarios para la inclusión de la selección de datos.

El comando Snowflake SELECT funciona en tablas externas y puede utilizarse para consultar datos históricos. En determinadas situaciones, el uso de la sentencia SELECT no requerirá un almacén virtual en ejecución para devolver los resultados; esto se debe al almacenamiento en caché de Snowflake, como se describe en el Capítulo 2. Puedes encontrar ejemplos de la sentencia SELECT, la sentencia SQL más común, a lo largo de la mayoría de los capítulos de este libro. La siguiente sección proporciona detalles sobre cómo sacar el máximo partido al comando SELECT .

Desarrollo de consultas SQL, sintaxis y operadores en Snowflake

En Snowflake, el desarrollo SQL puede realizarse de forma nativa, utilizando las hojas de trabajo de la interfaz de usuario de Snowflake o SnowSQL, así como a través de las numerosas herramientas SQL de terceros disponibles.

Sintaxis de consulta es la forma en que se estructuran o construyen las consultas SQL de Snowflake. A menudo hay muchas formas distintas de escribir una consulta SQL que produzca el resultado deseado. Es importante considerar cómo optimizar la consulta para obtener el mejor rendimiento de la base de datos y el menor coste. El Capítulo 9 incluye una sección dedicada a tratar el tema del análisis del rendimiento de las consultas y las técnicas de optimización.

Operadores de consulta incluyen términos reservados para especificar condiciones en una sentencia de consulta SQL y se utilizan con más frecuencia en la cláusula WHERE. También pueden utilizarse como conjunciones para múltiples condiciones en una sentencia. Exploraremos los operadores de consulta más adelante en esta sección.

Desarrollo y gestión de SQL

Hay dos opciones nativas de Snowflake para desarrollar y consultar datos. Es fácil iniciarse en el desarrollo SQL de Snowflake utilizando el editor SQL basado en navegador Worksheets dentro de la interfaz de Snowflake. Utilizar Snowflake Worksheets no requiere instalación ni configuración. Hasta ahora, sólo hemos utilizado las Hojas de Cálculo de Snowflake para crear objetos y consultar datos.

Una alternativa a Worksheets es SnowSQL, un cliente basado en Python que puede descargarse del repositorio de clientes de Snowflake y utilizarse para realizar tareas de Snowflake, como realizar consultas o ejecutar comandos DDL y DML. SnowSQL se utiliza con frecuencia para cargar y descargar datos. Tendremos alguna experiencia práctica con SnowSQL en el Capítulo 6.

Snowflake ofrece versiones de SnowSQL para Linux, macOS y Microsoft Windows. El SnowSQL ejecutable puede ejecutarse como shell interactivo o por lotes. Snowflake proporciona instrucciones completas sobre cómo descargar e instalar SnowSQL para todas las plataformas compatibles.

Puedes ver las versiones de cliente utilizadas recientemente, incluida la versión SnowSQL, en tu cuenta de Snowflake consultando el historial de consultas de Snowflake. Para ver esa información, haz clic en Actividad → Historial de consultas si estás utilizando la nueva interfaz web de Snowsight. Si no ves enseguida la información sobre el controlador de cliente, haz clic en el botón Columnas y selecciona Controlador de cliente (como se muestra en la Figura 4-1).

Figura 4-1. Columnas disponibles para revisar la actividad del historial de consultas en Snowsight

Alternativamente, haz clic en la pestaña Historial de la interfaz web de la Consola Clásica. Desde ahí, puedes ver la columna Información del cliente haciendo clic en el botón Columna de la parte superior derecha y seleccionando Controlador de cliente. Curiosamente, la columna Información del Cliente de la interfaz web de la Consola Clásica incluye un icono para indicar si la versión del cliente es compatible, no compatible o está próxima al fin de la compatibilidad. Hemos estado utilizando la interfaz web de Snowsight, por lo que veremos que se ha utilizado el controlador de cliente Go y que es compatible, como indica una marca de verificación (como se muestra en la Figura 4-2).

Figura 4-2. La columna Información del Cliente de la pestaña Historial (interfaz web de la Consola Clásica)

Además de las herramientas nativas de Snowflake, existe una amplia variedad de herramientas SQL de terceros para modelar, desarrollar e implementar código SQL en aplicaciones Snowflake. Algunas de estas herramientas de terceros, como DataOps.live y SqlDBM, están disponibles para una prueba gratuita mediante Snowflake Partner Connect. Puedes visitar la documentación en línea de Snowflake para obtener una lista más completa de herramientas SQL de terceros disponibles para su uso con Snowflake.

Nota

Para los controladores y conectores que admiten el envío de una sentencia SQL para su preparación antes de la ejecución, Snowflake preparará los comandos DML y ejecutará SELECT y SHOW. <objects> las sentencias SQL recibidas de esos controladores y conectores. Otros tipos de sentencias SQL recibidas de controladores y conectores serán ejecutadas por Snowflake sin preparación.

Sintaxis de consulta

Snowflake Las consultas SQL comienzan con la cláusula WITH o con la instrucción SELECT. La cláusula WITH, una cláusula opcional que precede a la sentencia SELECT, se utiliza para definir expresiones comunes de tabla (CTE) a las que se hace referencia en la cláusula FROM. La mayoría de las consultas a, sin embargo, comienzan con el comando SELECT y otra sintaxis que aparece después. El resto de la sintaxis, descrita en la Tabla 4-1, se evalúa en el siguiente orden:

  • FROM

  • WHERE

  • GROUP BY

  • HAVING

  • WINDOW

  • QUALIFY

  • ORDER BY

  • LIMIT

Tabla 4-1. Sintaxis de la consulta Snowflake
Sintaxis de la consulta Cláusula de consulta Comentarios
WITH Cláusula opcional que precede al cuerpo de la declaración SELECT
TOP<n> Contiene el número máximo de filas devueltas, se recomienda incluir ORDER BY
FROM AT | BEFORE, CHANGES, CON⁠NECT BY, JOIN, MATCH_RECOGNIZE, PIVOT o UNPIVOT, SAMPLE o TABLESAMPLE_VALUE Especifica las tablas, vistas o funciones de tabla que se van a utilizar en una sentencia SELECT
WHERE Especifica una condición que coincide con un subconjunto de filas; puede filtrar el resultado de la cláusula FROM; puede especificar sobre qué filas operar en una UPDATE, MERGE o DELETE
GROUP BY GROUP BY CUBE, GROUP BY GROUPING SETS, GROUP BY ROLLUP, HAVING Agrupa filas con las mismas expresiones de agrupar por elementos y calcula funciones agregadas para el grupo resultante; puede ser un nombre de columna, un número que haga referencia a una posición en la lista SELECT o una expresión general
QUALIFY Filtra los resultados de las funciones de ventana
ORDER BY Especifica una ordenación de las filas de la tabla de resultados a partir de una lista SELECT
LIMIT/FETCH Limita el número máximo de filas devueltas; se recomienda incluir ORDER BY

Ten en cuenta que QUALIFY se evalúa después de una función ventana; QUALIFY funciona con las funciones ventana de forma muy parecida a como lo hace HAVING con las funciones agregadas y las cláusulas GROUP BY. Más adelante en este capítulo encontrarás más información sobre las funciones ventana.

Subconsultas, columnas derivadas y CTEs

Una subconsulta es una consulta dentro de otra consulta y puede utilizarse para calcular valores que se devuelven en una lista SELECT, se agrupan en una cláusula GROUP BY o se comparan con otras expresiones en la cláusula WHERE o HAVING.

Una subconsulta Snowflake es una sentencia SELECT anidada que se admite como bloque en una o varias de las siguientes sentencias SQL Snowflake:

  • CREATE TABLE AS

  • SELECT

  • INSERT

  • INSERT INTO

  • UPDATE

  • DELETE

Para prepararnos para nuestros ejercicios prácticos sobre subconsultas y columnas derivadas, necesitamos crear unas cuantas tablas sencillas e insertar algunos valores en ellas. Crearemos una base de datos para este capítulo. También crearemos un esquema y una tabla para nuestros ejemplos de subconsultas y columnas derivadas. Navega a la hoja de cálculo Capítulo4 en Snowsight para ejecutar las siguientes sentencias:

USE ROLE SYSADMIN;
USE WAREHOUSE COMPUTE_WH;
CREATE OR REPLACE DATABASE DEMO4_DB;
CREATE OR REPLACE SCHEMA SUBQUERIES;
CREATE OR REPLACE TABLE DEMO4_DB.SUBQUERIES.DERIVED
    (ID integer, AMT integer, Total integer);
INSERT INTO DERIVED (ID, AMT, Total)
VALUES (1,1000,4000),(2,2000,3500),(3,3000, 9900),(4,4000,3000),
    (5,5000,3700),(6,6000,2222);
SELECT * FROM DEMO4_DB.SUBQUERIES.DERIVED;

Tus resultados deben coincidir con lo que se muestra en la Figura 4-3.

Figura 4-3. Resultados de la inserción de valores en una nueva tabla Snowflake DERIVED

Necesitaremos una segunda tabla en el esquema SUBQUERIES; tras añadir la tabla, veremos los resultados que se muestran en la Figura 4-4:

CREATE OR REPLACE TABLE DEMO4_DB.SUBQUERIES.TABLE2
    (ID integer, AMT integer, Total integer);
INSERT INTO TABLE2 (ID, AMT, Total)
VALUES (1,1000,8300),(2,1001,1900),(3,3000,4400),(4,1010,3535),
    (5,1200,3232),(6,1000,2222);
SELECT * FROM DEMO4_DB.SUBQUERIES.TABLE2;
Figura 4-4. Resultados de la inserción de valores en una nueva tabla Snowflake TABLE2

Una vez creadas ambas tablas, podemos escribir una subconsulta no correlacionada:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2);

Verás en que una subconsulta no correlacionada es una consulta independiente, en la que el valor devuelto no depende de ninguna columna de la consulta externa. Una subconsulta no correlacionada devuelve un único resultado que la consulta externa utiliza una sola vez. En cambio, una subconsulta correlacionada hace referencia a una o varias columnas externas. Una subconsulta correlacionada se evalúa en cada fila de la tabla de la consulta externa y devuelve un resultado por cada fila evaluada.

Intentemos ahora ejecutar una subconsulta correlacionada:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT AMT
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

Recibimos un mensaje de error que nos dice que una subconsulta de una sola fila devuelve más de una fila (como se muestra en la Figura 4-5). Esto probablemente no es lo que esperabas.

Figura 4-5. Mensaje de error recibido al ejecutar una subconsulta correlacionada sin agregado

Lógicamente, sabemos que sólo hay una fila por ID; por tanto, la subconsulta no devolverá más de una fila en el conjunto de resultados. Sin embargo, el servidor no puede saberlo. Debemos utilizar una función MIN, MAX, o AVG para que el servidor pueda saber con certeza que sólo se devolverá una fila cada vez que se ejecute la subconsulta.

Sigamos adelante y añadamos MAX a la declaración para ver por nosotros mismos cómo funciona:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

¡Éxito! Obtenemos un conjunto de resultados de una fila con el ID igual al valor 3. Veamos qué ocurre si cambiamos el signo igual por un signo mayor que:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT > (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

Ahora obtenemos un conjunto de resultados con tres valores (como se muestra en la Figura 4-6).

Figura 4-6. Conjunto de resultados de una consulta correlacionada con tres valores

Veamos qué ocurre si cambiamos MAX por AVG:

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT > (SELECT AVG(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

Hay cinco registros en el conjunto de resultados. Puedes probar con distintos operadores en la cláusula WHERE y distintos agregadores en la cláusula SELECT para ver por ti mismo cómo funcionan realmente las subconsultas correlacionadas.

Las subconsultas correlacionadas se utilizan con poca frecuencia porque dan lugar a una consulta por fila, lo que probablemente no sea el mejor enfoque escalable para la mayoría de los casos de uso.

Las subconsultas pueden utilizarse para múltiples propósitos, uno de los cuales es calcular o derivar valores que luego se utilizan de diversas formas. Las columnas derivadas también pueden utilizarse en Snowflake para calcular otra columna derivada, pueden ser consumidas por la consulta externa SELECT o pueden utilizarse como parte de la cláusula WITH. Estos valores de columna derivada, a veces llamados valores de columna calculados o valores de columna virtuales, no se almacenan físicamente en una tabla, sino que se recalculan cada vez que se hace referencia a ellos en una consulta.

Nuestro siguiente ejemplo demuestra cómo se puede utilizar una columna derivada en Snowflake para calcular otra columna derivada. También descubriremos cómo podemos utilizar columnas derivadas en una consulta, en subconsultas y con CTEs.

Vamos a crear una columna derivada, AMT1, a partir de la columna AMT y luego utilizaremos directamente la primera columna derivada para crear la segunda columna derivada, AMT2:

SELECT ID, AMT, AMT * 10 as AMT1, AMT1 + 20 as AMT2
FROM DEMO4_DB.SUBQUERIES.DERIVED;

Los resultados de ejecutar esa consulta se pueden ver en la Figura 4-7.

Figura 4-7. Resultados de ejecutar la consulta con dos columnas derivadas

Podemos conseguir los mismos resultados creando una columna derivada, AMT1, que luego puede ser consumida por una consulta externa SELECT. La subconsulta de nuestro ejemplo es una subconsulta escalar no correlacionada Snowflake. Como recordatorio, la subconsulta se considera una subconsulta no correlacionada porque el valor devuelto no depende de ninguna columna de la consulta externa:

SELECT sub.ID, sub.AMT, sub.AMT1 + 20 as AMT2
FROM (SELECT ID, AMT, AMT * 10 as AMT1
    FROM DEMO4_DB.SUBQUERIES.DERIVED) AS sub;

Por último, obtenemos los mismos resultados utilizando una columna derivada como parte de la cláusula WITH. Observarás que hemos incluido una subconsulta CTE que podría ayudar a aumentar la modularidad y simplificar el mantenimiento. La CTE define un nombre de vista temporal, que es CTE1 en nuestro ejemplo. En la CTE se incluyen los nombres de las columnas y una expresión de consulta, cuyo resultado es básicamente una tabla:

WITH CTE1 AS (SELECT ID, AMT, AMT * 10 as AMT2
    FROM DEMO4_DB.SUBQUERIES.DERIVED)
SELECT a.ID, b.AMT, b.AMT2 + 20 as AMT2
FROM DEMO4_DB.SUBQUERIES.DERIVED a
    JOIN CTE1 b ON(a.ID = b.ID);

Una gran ventaja de utilizar una CTE es que puede hacer que tu código sea más legible. Con una CTE, puedes definir una tabla temporal una vez y referirte a ella siempre que la necesites, en lugar de tener que declarar la misma subconsulta cada vez que la necesites. Aunque no se demuestra aquí, una CTE también puede ser recursiva. Una CTE recursiva puede unir una tabla a sí misma muchas veces para procesar datos jerárquicos.

Advertencia

Siempre que existan los mismos nombres para un CTE y una tabla o vista, el CTE tendrá preferencia. Por tanto, se recomienda elegir siempre un nombre único para tus CTEs.

Precaución con los insertos de varias filas

Ahora es un buen momento para hacer una breve pausa y aprender un poco más sobre las inserciones de varias filas. Se pueden insertar una o varias filas de datos mediante una consulta de selección, o se pueden insertar como valores indicados explícitamente en una lista separada por comas. Para simplificar las cosas, en este capítulo hemos estado insertando valores en listas separadas por comas.

En hay una cosa importante que debes tener en cuenta respecto a las inserciones de varias filas. Al insertar varias filas de datos en un tipo de datos VARCHAR, cada tipo de datos que se inserte en las columnas VARCHAR debe ser el mismo o, de lo contrario, la inserción fallará. Un tipo de datos VARCHAR puede aceptar valores de datos como la palabra uno o el número 1, pero nunca ambos tipos de valores en la misma sentencia INSERT. Podemos ver mejor esto con algunos ejemplos.

Primero crearemos un esquema y una tabla nuevos para hacer algunas pruebas de inserción de varias filas. En el primer ejemplo, insertaremos el valor uno en la columna VARCHAR DEPT:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one');
SELECT * FROM DEMO4_DB.TEST.TEST1;

Como era de esperar, el valor se introdujo correctamente. Veamos qué ocurre si en su lugar introducimos un valor numérico en la columna VARCHAR:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,1);
SELECT * FROM DEMO4_DB.TEST.TEST1;

De nuevo, el valor se introdujo correctamente. Ahora probemos a insertar ambos tipos en la columna dentro de la misma sentencia INSERT:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one'), (2,2);
SELECT * FROM DEMO4_DB.TEST.TEST1;

Cuando intentamos insertar dos tipos de datos diferentes en la columna VARCHAR al mismo tiempo, se produce un error, como se muestra en la Figura 4-8.

Figura 4-8. El mensaje de error que se recibe al intentar insertar dos tipos de datos diferentes en una columna VARCHAR en una sentencia de varias filas INSERT

Intentémoslo de nuevo, pero esta vez insertaremos dos valores con el mismo tipo de datos:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one'), (2,'two');
SELECT * FROM DEMO4_DB.TEST.TEST1;

También tenemos éxito si insertamos dos valores numéricos en la columna VARCHAR:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,1), (2,2);
SELECT * FROM DEMO4_DB.TEST.TEST1;

Observarás que podemos cargar con éxito dos tipos de datos diferentes en la columna VARCHAR, pero no al mismo tiempo. Y una vez que tenemos dos tipos de datos diferentes en la columna VARCHAR, aún podemos añadir valores adicionales:

INSERT INTO TEST1 (ID, DEPT)
VALUES (5, 'five');
SELECT * FROM DEMO4_DB.TEST.TEST1;

Las inserciones de varias filas son una forma de introducir datos en Snowflake. El Capítulo 6 está dedicado a la carga y descarga de datos, e incluye un análisis en profundidad de las opciones de carga masiva de datos y de carga continua de datos.

Operadores de consulta

En existen varios tipos de operadores de consulta, como los aritméticos, los de comparación, los lógicos, los de subconsulta y los de conjunto.

Los operadores aritméticos, incluyendo +, –, *, /, y %, producen una salida numérica a partir de una o más entradas. La escala y la precisión de la salida dependen de la escala y la precisión de la(s) entrada(s). Ten en cuenta que la resta es la única operación aritmética permitida en las expresiones DATE.

Los operadores de comparación, que suelen aparecer en una cláusula WHERE, se utilizan para comprobar la igualdad de dos entradas. Los operadores de comparación incluyen los siguientes:

  • Igual (=)

  • No es igual (!= o <>)

  • Menos de (<)

  • Menor o igual (<=)

  • Mayor que (>)

  • Mayor o igual (>=)

Consejo

Recuerda que los valores de TIMESTAMP_TZ se comparan en función de sus horas en UTC, lo que no tiene en cuenta el horario de verano. Esto es importante porque, en el momento de la creación, TIMESTAMP_TZ almacena el desfase de una zona horaria determinada, no la zona horaria real.

Los operadores lógicos sólo pueden utilizarse en la cláusula WHERE. El orden de precedencia de estos operadores es NOT luego AND luego OR. Los operadores de subconsulta incluyen [NOT] EXISTS, ANY o ALL, y [NOT] IN. Las consultas pueden combinarse cuando se utilizan operadores de conjunto como INTERSECT, MINUS o EXCEPT, UNION y UNION ALL.

El orden de preferencia por defecto de los operadores de conjuntos es INTERSECT como mayor precedencia, seguido de EXCEPT, MINUS, y UNION, y finalmente UNION ALL como menor precedencia. Por supuesto, siempre puedes utilizar paréntesis para anular el valor predeterminado. Ten en cuenta que la operación de conjunto UNION es costosa porque necesita ordenar los registros para eliminar las filas duplicadas.

Advertencia

Cuando utilices operadores de conjunto, asegúrate de que cada consulta selecciona el mismo número de columnas y de que el tipo de datos de cada columna es coherente, aunque se puede utilizar una conversión de tipos explícita si los tipos de datos son incoherentes.

Consultas de larga duración, y Rendimiento y optimización de las consultas

El sistema Snowflake cancelará las consultas de larga duración. La duración por defecto de las consultas de larga duración es de dos días, pero el valor de duración de STATEMENT_TIMEOUT_IN_SECONDS siempre puede establecerse a nivel de cuenta, sesión, objeto o almacén virtual.

Durante el proceso de consulta SQL de Snowflake, una de las cosas que ocurre es que los motores de optimización encuentran el plan de ejecución más eficiente para una consulta concreta. En el Capítulo 9, aprenderemos más sobre el análisis del rendimiento de las consultas y las técnicas de optimización, así como a utilizar el perfilador de consultas de Snowflake.

Límites de consulta del copo de nieve

Las sentencias SQL enviadas a través de los clientes Snowflake tienen un límite de tamaño de texto de consulta de 1 MB. En ese límite se incluyen los literales, tanto de cadena como binarios. El límite de tamaño del texto de la consulta se aplica al tamaño comprimido de la consulta. Sin embargo, como la relación de compresión de los datos varía mucho, se recomienda mantener el tamaño del texto de la consulta sin comprimir por debajo de 1 MB.

Además, Snowflake limita el número de expresiones permitidas en una consulta a 16.384. Hay formas de resolver este tipo de error dependiendo de lo que estés intentando hacer con tu sentencia de consulta SQL. Si estás intentando insertar datos cuando recibes el error, intenta dividir la sentencia en consultas más pequeñas. Sin embargo, una opción aún mejor sería probablemente utilizar el comando COPY INTO en lugar del comando INSERT.

Otro tipo de error de límite de consulta se produce cuando se utiliza una sentencia SELECT con una cláusula IN que tiene más de 16.384 valores. Aquí tienes un ejemplo de cómo podría ser ese código:

SELECT  <column_1> FROM  <table_1>
WHERE <column_2> IN  (1, 2, 3, 4, 5,...);

Una solución sería utilizar un comando JOIN o UNION después de colocar esos valores en una segunda tabla. El código SQL podría tener este aspecto:

SELECT <column_1>
FROM  <table_1> a
    JOIN  <table_2> b ON a.<column_2>  = b.<column_2>;

Introducción a los tipos de datos admitidos por Snowflake

Snowflake admite los tipos de datos SQL básicos, incluidos los tipos de datos geoespaciales, y un tipo de datos lógico booleano que permite la lógica ternaria. El tipo de datos BOOLEAN de Snowflake puede tener un valor desconocido, o un valor TRUE o FALSE. Si el booleano se utiliza en una expresión, como una sentencia SELECT, un valor desconocido devuelve un NULL. Si el booleano se utiliza como predicado, como en una cláusula WHERE, los resultados desconocidos se evaluarán a FALSE. Hay algunos tipos de datos no admitidos por Snowflake, como los objetos grandes (LOB), que incluyen BLOB y CLOB, así como ENUM y tipos de datos definidos por el usuario.

Snowflake ofrece soporte nativo para características geoespaciales como puntos, líneas y polígonos de la superficie terrestre. El tipo de datos Snowflake GEOGRAPHY sigue el estándar WGS. Los puntos de la Tierra se representan como grados de longitud y latitud. Actualmente no se admite la altitud.

Nota

Si tienes datos geoespaciales como longitud y latitud, WKT, WKB o GeoJSON, se recomienda que conviertas y almacenes estos datos en columnas GEOGRAPHY en lugar de mantener los datos en sus formatos originales en columnas VARCHAR, VARIANT o NUMBER. Esto podría mejorar significativamente el rendimiento de las consultas que utilizan la funcionalidad geoespacial.

En esta sección, profundizaremos en varios tipos de datos Snowflake, como los numéricos, los de cadena y binarios, los de fecha y hora, los semiestructurados y los no estructurados.

Tipos de datos numéricos

Los tipos de datos numéricos de Snowflake incluyen números de coma fija y números de coma flotante, como se detalla en la Tabla 4-2. En la tabla se incluye información sobre la precisión y la escala de cada tipo de dato numérico. La precisión, el número total de dígitos, afecta al almacenamiento, mientras que la escala, el número de dígitos que siguen al punto decimal, no. Sin embargo, procesar valores de datos numéricos con una escala mayor podría causar un procesamiento más lento.

Tabla 4-2. Tipos de datos numéricos Snowflake
Tipos de datos numéricos en coma fija Precisión Comentarios
NUMBER Opcional (38, 0) Números de hasta 38 dígitos; la escala máxima es 37
DECIMAL, NUMERIC Opcional (38,0) Sinónimo de NUMBER
INT, INTEGER, BIGINT, SMALLINT, TINYINT, BYTEINT No se puede especificar; siempre (38,0) Valores posibles: -99999999999999999999999999999999999999 to +99999999999999999999999999999999999999 (inclusive)
Tipos de datos de números en coma flotante Comentarios
FLOAT, FLOAT4, FLOAT8 Aproximadamente 15 dígitos Los valores oscilan aproximadamente entre 10-308 y 10+308
DOUBLE, DOUBLE PRECISION, REAL Aproximadamente 15 dígitos Sinónimo de FLOAT
Nota

Es un problema conocido que las columnas DOUBLE, DOBLE PRECISIÓN y REAL se almacenan como DOUBLE pero se muestran como FLOAT.

Los números de coma fija son valores numéricos exactos y, como tales, se utilizan a menudo para los números naturales y los valores decimales exactos, como las cantidades monetarias. En cambio, los tipos de datos de coma flotante se utilizan sobre todo para las matemáticas y la ciencia.

Puedes ver cómo varían los números en coma fija en función del tipo de datos. Asegúrate de ir a la hoja de cálculo del Capítulo 4 y prueba el siguiente ejemplo:

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE NUMFIXED (
    NUM NUMBER,
    NUM12 NUMBER(12, 0),
    DECIMAL DECIMAL (10, 2),
    INT INT,
    INTEGER INTEGER
);

Para ver lo que se ha creado, puedes ejecutar la sentencia DESC TABLE NUMFIXED y obtener los resultados que se muestran en la Figura 4-9.

Figura 4-9. Resultados que muestran el tipo de datos numéricos de coma fija Snowflake

Ahora puedes comparar números de coma fija con números de coma flotante utilizando el siguiente ejemplo:

USE ROLE SYSADMIN; USE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE NUMFLOAT (
    FLOAT FLOAT,
    DOUBLE DOUBLE,
    DP DOUBLE PRECISION,
    REAL REAL
);

Una vez más, utiliza el comando Desc para ver los resultados, como se muestra en la Figura 4-10:

DESC TABLE NUMFLOAT;
Figura 4-10. Resultados que muestran el tipo de datos de número en coma flotante Snowflake

En la informática tradicional, se sabe que los tipos de datos flotantes son más rápidos para el cálculo. Pero, ¿sigue siendo esa una afirmación correcta sobre los tipos de datos flotantes en las plataformas de datos modernas como Snowflake? No necesariamente. Es importante tener en cuenta que los valores enteros pueden almacenarse en formato comprimido en Snowflake, mientras que los tipos de datos flotantes no. Esto supone menos espacio de almacenamiento y menos coste para los enteros. Consultar las filas de un tipo de tabla de enteros también lleva mucho menos tiempo.

Advertencia

Debido a la naturaleza inexacta de los tipos de datos en coma flotante, las operaciones en coma flotante podrían tener pequeños errores de redondeo y esos errores pueden acumularse, especialmente cuando se utilizan funciones agregadas para procesar un gran número de filas.

Los tipos de datos numéricos de Snowflake están soportados por constantes numéricas. Las constantes, también llamadas literales, representan valores de datos fijos. Los dígitos numéricos del 0 al 9 pueden ir precedidos de un signo positivo o negativo. Los exponentes, indicados por e o E, también se admiten en las constantes numéricas de Snowflake.

Tipos de datos de cadena y binarios

Snowflake admite tipos de datos de cadena de texto y binarios, cuyos detalles puedes ver en la Tabla 4-3.

Tabla 4-3. Tipos de datos de texto y cadena binaria de Snowflake
Tipos de datos de cadena de texto Parámetros Comentarios
VARCHAR Parámetro opcional (N), número máximo de caracteres Contiene caracteres Unicode; no hay diferencia de rendimiento entre utilizar la longitud completa VARCHAR (16.777.216) o una longitud menor
CHAR, CHARACTERS Sinónimo de VARCHAR; la longitud es CHAR(1) si no se especifica
STRING, TEXT Sinónimo de VARCHAR
Tipos de datos de cadena binaria Comentarios
BINARY No tiene noción de los caracteres Unicode, por lo que la longitud siempre se mide en bytes; si no se especifica la longitud, el valor por defecto es 8 MB (la longitud máxima)
VARBINARY Sinónimo de BINARY

Puedes ver cómo varían los tipos de datos de cadena de texto intentando el siguiente ejemplo, que crea los campos de cadena de texto y luego describe la tabla:

USE ROLE SYSADMIN; USE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE TEXTSTRING(
    VARCHAR VARCHAR,
    V100 VARCHAR(100),
    CHAR CHAR,
    C100 CHAR(100),
    STRING STRING,
    S100 STRING(100),
    TEXT TEXT,
    T100 TEXT(100)
);

DESC TABLE TEXTSTRING;

Si has seguido el ejemplo, deberías ver la salida que se muestra en la Figura 4-11.

Figura 4-11. Resultados de la creación de una tabla TEXTSTRING

Los tipos de datos de cadena de Snowflake están soportados por constantes de cadena, que siempre van encerradas entre delimitadores, ya sean comillas simples o signos de dólar. Utilizar símbolos de signo de dólar como delimitadores es especialmente útil cuando la cadena contiene muchos caracteres de comillas .

Tipos de datos de entrada/salida de fecha y hora

Snowflake utiliza el calendario gregoriano, en lugar del calendario juliano, para todas las fechas y marcas de tiempo. Los tipos de datos de fecha y hora de Snowflake se resumen en la Tabla 4-4.

Tabla 4-4. Tipos de datos de fecha y hora de Snowflake
Tipos de datos de fecha y hora Asignación por defecto Comentarios
DATE Tipo único DATE; se aceptan las formas de fecha más comunes; todas las marcas de tiempo aceptadas son entradas válidas con TIME truncado; se supone que la hora asociada es medianoche
DATETIME Alias para TIMESTAMP_NTZ
TIME Tipo TIME único en el formulario HH:MI:SS, almacenado internamente como hora del reloj de pared; no se tienen en cuenta los husos horarios
TIMESTAMP Por defecto es TIMESTAMP_NTZ Alias especificado por el usuario de una de las tres variantes de TIMESTAMP_
TIMESTAMP_LTZ Hora UTC interna con una precisión especificada; TIMESTAMP con la zona horaria local
TIMESTAMP_NTZ Hora del reloj de pared interno; TIMESTAMP sin huso horario
TIMESTAMP_TZ Hora UTC interna con un desfase de zona horaria; TIMESTAMP con zona horaria

Los tipos de datos de tiempo y datos de Snowflake están soportados por constantes de intervalo, así como por constantes de fecha y hora. Las constantes de intervalo se pueden utilizar para sumar o restar un periodo de tiempo específico a o de una fecha, hora o marca de tiempo. El intervalo no es un tipo de dato; sólo puede utilizarse en la aritmética de fecha, hora o marca de tiempo y representará segundos si no se especifica la parte de fecha u hora.

Nota

El orden de los incrementos de intervalo es importante porque los incrementos se suman o restan en el orden en que aparecen en la lista. Esto puede ser importante para los cálculos afectados por los años bisiestos.

Tipos de datos semiestructurados

Los datos estructurados de, conocidos como datos cuantitativos, pueden almacenarse fácilmente en una tabla de base de datos como filas y columnas, mientras que los datos semiestructurados, como los datos XML, no dependen del esquema, lo que dificulta su almacenamiento en una base de datos. En algunas situaciones, sin embargo, los datos semiestructurados pueden almacenarse en una base de datos relacional.

Snowflake admite tipos de datos para importar y operar con datos semiestructurados, como datos JSON, Avro, ORC, Parquet y XML. Snowflake lo hace a través de su tipo de datos universal VARIANT, un tipo de columna especial que te permite almacenar datos semiestructurados. La Tabla 4-5 proporciona más información sobre los tipos de datos semiestructurados de Snowflake. Ten en cuenta que es posible que falte un valor VARIANT, lo que se considera diferente de un valor nulo verdadero.

Tabla 4-5. Tipos de datos semiestructurados Snowflake
Tipos de datossemiestructurados Características Comentarios
VARIANT Puede almacenar OBJECT y ARRAY Almacena valores de cualquier otro tipo, hasta un máximo de 16 MB sin comprimir; internamente se almacena en representación binaria columnar comprimida
OBJECT Representa colecciones de pares clave-valor con la clave como cadena no vacía y el valor de tipo VARIANT
ARRAY Representa matrices de tamaño arbitrario cuyo índice es un entero no negativo y los valores tienen el tipo VARIANT
Advertencia

Cuando se cargan en una columna VARIANT, los valores no nativos, como fechas y marcas de fecha y hora, se almacenan como cadenas. Almacenar los valores de esta forma probablemente hará que las operaciones sean más lentas y consuman más espacio en comparación con almacenar los valores de fecha y hora en una columna relacional con el tipo de datos correspondiente.

Los ejercicios prácticos sobre datos semiestructurados utilizarán los datos meteorológicos de muestra de Snowflake, que se almacenan en formato JSON nativo. En dedicaremos algún tiempo a hacernos una idea de los datos existentes y luego aprenderemos a utilizar la función FLATTEN para obtener una vista lateral de los datos semiestructurados.

Aviso
En el momento de escribir esto, el conjunto de datos meteorológicos estaba disponible en las cuentas de prueba gratuitas de Snowflake. Sin embargo, es posible que Snowflake deje de utilizar este conjunto de datos con el tiempo. Consulta https://github.com/SnowflakeDefinitiveGuide para obtener más información.

Primero echemos un vistazo rápido a unas cuantas filas de datos:

USE ROLE SYSADMIN; 
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.WEATHER;
SELECT * FROM DAILY_16_TOTAL
LIMIT 5;

Deberías ver que hay dos columnas: una columna VARIANTE (V) y una TIMESTAMP columna (T), como se muestra en la Figura 4-12.

Figura 4-12. Dos columnas de la tabla de datos de la muestra meteorológica Copo de nieve

Centrémonos en los datos de la columna VARIANTE:

SELECT v:city
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Cuando aparezcan los resultados, haz clic en V:CIUDAD en la parte superior de la columna. Esto resaltará la columna y te dará los detalles que necesitas para ver que hay cuatro claves de objeto distintas en esta columna (como se muestra en la Figura 4-13). Por orden, las claves de objeto relativas a V:CIUDAD son coordenadas, país, ID y nombre.

Figura 4-13. Cuatro claves de objeto distintas para los datos CIUDAD en la columna VARIANTE

Ahora vamos a desglosar manualmente algunos de los datos de la CIUDAD y a enumerarlos en un orden más lógico (como se muestra en la Figura 4-14):

SELECT v:city:id, v:city:name, v:city:country, v:city:coord
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;
Figura 4-14. Detalle de los datos de CIUDAD en la columna VARIANTE

Los detalles de latitud y longitud están anidados en la información de coordenadas. Separémoslos y demos a las columnas nombres apropiados:

SELECT v:city:id AS ID, v:city:name AS CITY,
    v:city:country AS COUNTRY, v:city:coord:lat AS LATITUDE,
    v:city:coord:lon AS LONGITUDE
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Podemos convertir un tipo de datos variante en otro tipo de datos. En el siguiente ejemplo, convertiremos los datos de ciudad y país VARIANT en un tipo de datos VARCHAR, y asignaremos etiquetas significativas a las columnas:

SELECT v:city:id AS ID, v:city:name::varchar AS city,
    v:city.country::varchar AS country, v:city:coord:lon
    AS longitude, v:city:coord:lat AS latitude
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Los resultados se muestran en la Figura 4-15.

Figura 4-15. Conversión de los datos de ciudad y país VARIANT a un tipo de datos VARCHAR

Podemos confirmar que hemos fundido correctamente las dos columnas pidiendo a Snowflake que describa los resultados de nuestra última consulta:

DESC RESULT LAST_QUERY_ID();

A continuación, vamos a ver más datos en la columna VARIANTE:

SELECT v:data
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Cuando aparezcan los resultados, haz clic en V:DATOS en la parte superior de la columna. Esto resaltará la columna y te dará los detalles de la columna que verás a la derecha (como se muestra en la Figura 4-16). Observarás que en esta columna hay una matriz relativa a la información de DATOS.

Figura 4-16. La matriz de datos meteorológicos en la columna VARIANTE

Como la información de los DATOS se almacena como una matriz, podemos mirar un elemento concreto de la matriz. Asegúrate de hacer clic en cada fila de resultados para ver que sólo se ha seleccionado un elemento por fila:

SELECT v:data[5]
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Podemos acotar aún más la información devuelta consultando el valor de humedad de un día concreto para una ciudad y un país determinados:

SELECT v:city:name AS city, v:city:country AS country,
    v:data[0]:humidity AS HUMIDITY
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

Ahora hagamos un repaso rápido. Cuando observamos la matriz de DATOS, v:data AS DATA en la siguiente declaración, nos damos cuenta de que cada fila contiene una matriz de datos completa. Dentro de cada matriz de datos hay 16 elementos para cada dato distinto (como se muestra en la Figura 4-17). En nuestra consulta SQL, incluiremos los dos primeros elementos de datos para la humedad y la temperatura diurna:

SELECT v:data[0]:dt::timestamp AS TIME,
v:data[0]:humidity AS HUMIDITY0, v:data[0]:temp:day AS DAY_TEMP0,
v:data[1]:humidity AS HUMIDITY1, v:data[1]:temp:day AS DAY_TEMP1,  
v:data AS DATA
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 100;
Figura 4-17. Un elemento de la matriz DATA (ruta) por fila

Veamos en cómo podemos aprovechar la función de tabla FLATTEN. La función FLATTEN produce una vista lateral de una columna VARIANTE, OBJETO o ARRAY. Demostraremos cómo funciona FLATTEN en la matriz DATA de la tabla de datos meteorológicos de ejemplo:

SELECT d.value:dt::timestamp AS TIME,
    v:city:name AS CITY, v:city:country AS COUNTRY,
    d.path AS PATH, d.value:humidity AS HUMIDITY,
	d.value:temp:day AS DAY_TEMP,v:data AS DATA
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL,
LATERAL FLATTEN(input => daily_16_total.v:data) d
LIMIT 100;

Observarás que aparece la misma matriz de DATOS para cada una de las 16 filas aplanadas, pero las direcciones HUMIDITY y DAY_TEMP indicadas en cada fila están asociadas a la RUTA específica de la matriz (como se muestra en la Figura 4-18).

Figura 4-18. La matriz aplanada de DATOS

La información sobre la temperatura en la matriz DATOS tiene seis valores anidados: day, eve, max, min, morn, y night. Podemos utilizar un FLATTEN anidado para aplanar aún más la matriz DATOS. Cuando hacemos esto, cada fila DATA aparece 96 veces, seis veces por cada uno de los 16 valores PATH (como se muestra en la Figura 4-19):

SELECT d.value:dt::timestamp AS TIME,
t.key,
v:city:name AS CITY, v:city:country AS COUNTRY,
d.path AS PATH,
d.value:humidity AS HUMIDITY,
d.value:temp:day AS DAY_TEMP,
d.value:temp:night AS NIGHT_TEMP,
v:data AS data
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL,
LATERAL FLATTEN(input => daily_16_total.v:data) d,
LATERAL FLATTEN(input => d.value:temp) t
WHERE v:city:id = 1274693 
LIMIT 100;
Figura 4-19. La matriz de DATOS aplanada y anidada

Como acabamos de ver, la función Snowflake FLATTEN se utiliza para convertir datos semiestructurados en una representación relacional.

Nota

Es posible combinar una función LATERAL JOIN con una función FLATTEN para separar los eventos en objetos JSON individuales conservando los datos globales .

Tipos de datos no estructurados

En hay muchas ventajas de utilizar datos no estructurados para obtener información. Los datos no estructurados suelen ser cualitativos, pero también pueden ser datos cuantitativos que carecen de filas, columnas o delimitadores, como sería el caso de un archivo PDF que contiene datos cuantitativos. Los registros de medios de comunicación, las imágenes médicas, los archivos de audio de grabaciones de centros de llamadas, las imágenes de documentos y muchos otros tipos de datos no estructurados pueden utilizarse con fines analíticos y para el análisis de sentimientos. Almacenar y gobernar los datos no estructurados no es fácil. Los datos no estructurados no se organizan de forma predefinida, lo que significa que no se adaptan bien a las bases de datos relacionales. Normalmente, los datos no estructurados se han almacenado en ubicaciones de almacenamiento blob, lo que tiene varias desventajas inherentes, que dificultan y alargan la búsqueda de archivos.

Para mejorar la capacidad de búsqueda de datos no estructurados, Snowflake ha lanzado recientemente tablas de directorios integradas. Utilizar un catálogo tabular de archivos para realizar búsquedas de datos no estructurados es ahora tan sencillo como utilizar un comando SELECT * en la tabla de directorios. Los usuarios también pueden construir un flujo de tablas sobre una tabla de directorios, lo que permite crear canalizaciones para procesar datos no estructurados. Además, los usuarios de Snowflake pueden crear vistas seguras en las tablas de directorios y, por tanto, también pueden compartir esas vistas seguras con otras personas.

Cómo Snowflake apoya el uso de datos no estructurados

Los datos no estructurados representan un porcentaje cada vez mayor de los datos que se generan hoy en día. Algunos ejemplos de tipos de datos no estructurados son los archivos de vídeo, audio o imagen, los archivos de registro, los datos de sensores y las publicaciones en redes sociales. Los datos no estructurados, que pueden ser generados por humanos o por máquinas, tienen una estructura interna, pero no almacenable en un formato de base de datos estructurada.

En hay muchas razones por las que querrías hacer uso de todos estos datos no estructurados. Esos casos de uso podrían incluir la obtención de información como el análisis de sentimientos a partir de grabaciones de centros de llamadas; la extracción de texto para análisis mediante procesos de reconocimiento óptico de caracteres en tarjetas de seguros o pastillas de recetas; el uso del aprendizaje automático en imágenes médicas DICOM; o la extracción de pares clave-valor de documentos PDF almacenados.

No es ningún secreto que los datos no estructurados son complejos; por lo tanto, su almacenamiento, búsqueda y análisis plantean muchos retos. Los almacenes y lagos de datos tradicionales han sido incapaces de soportar adecuadamente las demandas de carga de trabajo de los formatos de datos actuales, especialmente los datos no estructurados. Sin embargo, Snowflake no es un almacén o lago de datos tradicional. En su lugar, es una plataforma de datos construida desde cero para la nube; por tanto, ha eliminado gran parte de la dificultad asociada al almacenamiento, búsqueda y análisis o procesamiento de datos no estructurados.

La primera consideración al utilizar datos no estructurados es cómo y dónde almacenar los archivos no estructurados. Al utilizar Snowflake, hay dos formas de hacerlo: etapas internas y etapas externas. Utilizaríamos una etapa interna si quisiéramos almacenar datos internamente en Snowflake; especialmente si buscáramos una solución sencilla y fácil de gestionar. Esto se debe a que Snowflake gestiona automáticamente la escalabilidad, el cifrado, la compresión de datos y otros aspectos del almacenamiento. Como alternativa, utilizaríamos un escenario externo, conocido como "trae tu propio almacenamiento", si tuviéramos datos heredados almacenados en otro lugar de la nube, ya que no es necesario trasladar todos los datos a Snowflake.

Aunque es posible almacenar datos no estructurados internamente en una tabla Snowflake utilizando el tipo de columna VARIANT, normalmente no se recomienda porque existe una limitación de almacenamiento de archivos de 16 MB. Si en lugar de ello utilizamos un escenario, no hay más limitaciones de tamaño que las impuestas por los principales proveedores de la nube sobre los que se construye tu instancia Snowflake: 5 TB de datos para AWS y GCP o 256 GB de datos para Azure.

Tanto si utilizas etapas Snowflake internas como externas, el control de acceso a los datos se consigue fácilmente mediante controles de acceso basados en roles. Utilizando las sentencias GRANT y REVOKE, se pueden otorgar privilegios a los recursos de Snowflake, como las etapas, concediendo permisos a roles que, a su vez, se otorgan a individuos. Es fácil entender y aprender cómo dar acceso de grano fino a los datos de una etapa interna o externa, o a un subconjunto de los datos almacenados en las vistas, que son objetos Snowflake creados sobre las etapas. Para un repaso sobre los controles de acceso de Snowflake, consulta el Capítulo 5.

Con Snowflake, el almacenamiento y la concesión de acceso a datos no estructurados puede hacerse de tres formas distintas: URL de archivos por etapas, URL de ámbito o URL preasignadas.

Etapa de acceso a la URL del archivo

Una URL de archivo de la etapa se utiliza para crear una URL permanente a un archivo en una etapa Snowflake y se utiliza con mayor frecuencia para aplicaciones personalizadas. El acceso a una URL de archivo se realiza a través de una solicitud GET al punto final de la API REST junto con el token de autorización. Ten en cuenta que el usuario debe tener privilegios de lectura en el escenario. Las URL de archivos de escenario tienen una característica única, y es que pueden listarse en una tabla de directorios de Snowflake.

La capacidad de de crear una tabla de directorios, como un catálogo de archivos, en la que puedes buscar fácilmente para recuperar las URL de los archivos para acceder a los archivos organizados, así como otros metadatos, es una característica única que Snowflake ofrece para los datos no estructurados. Los roles de Snowflake a los que se han concedido privilegios pueden consultar una tabla de directorios para recuperar las URL de acceso a los archivos organizados.

Si quieres, por ejemplo, ordenar por tamaño de archivo o por fecha de última modificación, o tomar sólo los 100 archivos principales o los archivos más grandes, es posible hacerlo con las tablas de directorios de Snowflake. También puedes utilizar flujos y tareas Snowflake con tablas de directorios para obtener una potente combinación. Utilizando flujos de tablas, por ejemplo, puedes encontrar fácilmente todos los archivos nuevos que se hayan añadido recientemente. Como una tabla de directorios es una tabla, puedes realizar operaciones de selección y búsqueda de grano fino. Las operaciones de búsqueda en almacenes de blobs normales son extremadamente difíciles porque no tienen la información del catálogo en formato tabular.

Una tabla de directorios Snowflake es una tabla integrada de sólo lectura. Como tal, no puedes añadir más columnas ni modificar las columnas de una tabla de directorio. Lo que puedes hacer en es utilizar los flujos y tareas de Snowflake para calcular valores y ponerlos en una nueva tabla con una columna que contenga los resultados del cálculo. Luego podrás unir esa tabla con la tabla de directorios creando una vista. También puedes añadir etiquetas, si lo deseas.

Acceso a URL de ámbito limitado

Una URL de alcance se utiliza con frecuencia para aplicaciones personalizadas; especialmente en situaciones en las que se dará acceso a los datos a otras cuentas que utilicen la funcionalidad de compartir datos o cuando se realicen análisis ad hoc internamente utilizando Snowsight. Compartir datos no estructurados de forma segura en la nube es fácil con Snowflake. No se requieren privilegios en el escenario. En su lugar, crearías una vista segura, y utilizando la URL de ámbito, compartirías el contenido de la vista segura. La URL de alcance está codificada, por lo que no es posible determinar la cuenta, la base de datos, el esquema u otros detalles de almacenamiento a partir de la URL.

El acceso a los archivos de un escenario mediante el acceso a URL de ámbito se consigue de dos formas. Una es que un usuario de Snowflake haga clic en una URL de ámbito en la tabla de resultados de Snowsight. La otra forma es enviar la URL de alcance en una solicitud, lo que hace que Snowflake autentique al usuario, verifique que la URL de alcance no ha caducado y, a continuación, redirija al usuario al archivo escenificado en el servicio de almacenamiento en la nube. Recuerda que la ubicación del archivo organizado en el almacenamiento en la nube está codificada, por lo que el usuario no puede determinar la ubicación. La URL de alcance en la salida de la llamada a la API es válida durante 24 horas, el tiempo actual de existencia de la caché de resultados.

Nota

Por razones de seguridad, es imposible compartir una URL de alcance que se haya compartido contigo. Si compartieras el enlace con otra persona que no tuviera concedido un acceso similar, aparecería el mensaje acceso denegado.

Acceso a URL preasignadas

Una URL preasignada se utiliza más a menudo para aplicaciones de inteligencia empresarial o herramientas de elaboración de informes que necesitan mostrar el contenido no estructurado de archivos abiertos. Como las URL preasignadas ya están autenticadas, un usuario o aplicación puede acceder directamente a los archivos o descargarlos sin necesidad de pasar un token de autorización.

La función GET_PRESIGNED_URL genera la URL preasignada a un archivo de la etapa utilizando el nombre de la etapa y la ruta relativa del archivo como entradas. El acceso a los archivos de una etapa utilizando una URL preasignada puede realizarse de tres formas distintas: utilizar la URL preasignada en un navegador web para navegar directamente al archivo, hacer clic en una URL preasignada en la tabla de resultados de Snowsight o enviar la URL preasignada en una solicitud de llamada a la API REST.

Tratamiento de datos no estructurados con funciones Java y funciones externas

La capacidad de de ejecutar procesos en los datos no estructurados del interior de los archivos es una de las características más interesantes que ofrece Snowflake. Actualmente, hay dos formas de procesar datos no estructurados utilizando Snowflake: Funciones Java y funciones externas. En el futuro, Snowflake planea añadir la capacidad de procesar datos no estructurados mediante funciones Python.

Si ya tienes código Java que has escrito para utilizarlo con datos no estructurados, tiene sentido utilizar una función Java definida por el usuario (UDF). Ten en cuenta que las UDF de Java se ejecutan directamente en Snowflake, utilizando un almacén virtual de Snowflake. Como tales, las UDF de Java no hacen ninguna llamada a la API fuera de los límites de Snowflake. Todo está estrechamente asegurado y gestionado dentro del entorno Snowflake.

Si hay servicios API externos, como modelos de aprendizaje automático, geocodificadores u otro código personalizado que quieras utilizar, se pueden utilizar funciones externas. Las funciones externas permiten utilizar servicios de aprendizaje automático existentes para extraer texto de imágenes, o procesar archivos PDF para extraer pares clave-valor. En una función externa, puedes utilizar cualquiera de las funcionalidades de AWS, Azure o GCP, incluidos AWS Rekognition o Azure Cognitive Services. Las funciones externas ejecutadas sobre datos no estructurados, ya estén almacenados en etapas internas o externas, pueden utilizarse para eliminar la necesidad de exportar y reimportar datos .

Funciones SQL y variables de sesión Snowflake

Snowflake ofrece a los usuarios la posibilidad de crear UDF y de utilizar funciones externas, así como de acceder a muchas funciones incorporadas diferentes. Las variables de sesión también amplían las capacidades SQL de Snowflake.

Utilizar funciones definidas por el sistema (incorporadas)

Ejemplos de funciones integradas en Snowflake incluyen funciones escalares, agregadas, de ventana, de tabla y de sistema.

Las funciones escalares aceptan una única fila o valor como entrada y devuelven un valor como resultado, mientras que las funciones agregadas también devuelven un único valor pero aceptan varias filas o valores como entradas.

Funciones escalares

Algunas funciones escalares de operan sobre una cadena o un valor binario de entrada. Algunos ejemplos son CONCAT, LEN, SPLIT, TRIM, UPPER y LOWER conversión de casos, y REPLACE. Otras funciones escalares de archivos, como GET_STAGE_LOCATION, te permiten acceder a archivos almacenados en la nube Snowflake.

Además, puedes hacer muchas cosas en Snowflake con tipos de datos de fecha y hora. Algunos ejemplos de funciones escalares de fecha y hora y funciones de generación de datos son los siguientes:

  • Construye/deconstruye (extrae) utilizando los componentes de mes, día y año.

  • Trunca o "redondea" las fechas a un nivel superior.

  • Analiza y formatea fechas utilizando cadenas.

  • Suma/resta para encontrar y utilizar las diferencias de fecha.

  • Generar fechas del sistema o una tabla de fechas.

Funciones agregadas

Una función agregada Snowflake siempre devolverá una fila aunque la entrada no contenga filas. La fila devuelta por una función agregada cuando la entrada contiene cero filas puede ser un cero, una cadena vacía o cualquier otro valor. Las funciones agregadas pueden ser de naturaleza general, como MIN, MAX, MEDIAN, MODE y SUM. Las funciones agregadas también incluyen regresión lineal, estadística y probabilidad, estimación de frecuencias, estimación de percentiles y muchas más.

Las funciones ventana copo de nieve son un tipo especial de función agregada que puede operar sobre un subconjunto de filas. Este subconjunto de filas relacionadas se denomina ventana. A diferencia de las funciones agregadas, que devuelven un único valor para un grupo de filas, una función ventana devolverá una fila de salida por cada fila de entrada. La salida depende no sólo de la fila individual pasada a la función, sino también de los valores de las demás filas de la ventana pasada a la función.

Las funciones de ventana se utilizan habitualmente para encontrar un cambio porcentual interanual, una media móvil y un total acumulado o en ejecución, así como para clasificar filas por agrupaciones o criterios personalizados.

Vamos a comparar una función agregada con una función ventana. En este primer ejemplo, crearemos una función agregada utilizando las vocales del alfabeto y sus ubicaciones correspondientes:

SELECT LETTER, SUM(LOCATION) as AGGREGATE
FROM (SELECT 'A' as LETTER, 1 as LOCATION
        UNION ALL (SELECT 'A' as LETTER,1 as LOCATION)
        UNION ALL (SELECT 'E' as LETTER,5 as LOCATION)
     ) as AGG_TABLE
GROUP BY LETTER;

Los resultados de esta consulta se muestran en la Figura 4-20.

Figura 4-20. Resultados de una consulta de función agregada

A continuación, crearemos una función de ventana utilizando la misma lógica:

SELECT LETTER, SUM(LOCATION) OVER (PARTITION BY LETTER) as WINDOW_FUNCTION
FROM (SELECT 'A' as LETTER, 1 as LOCATION
        UNION ALL (SELECT 'A' as LETTER, 1 as LOCATION)
        UNION ALL (SELECT 'E' as LETTER, 5 as LOCATION)
     ) as WINDOW_TABLE;

Observa, en la Figura 4-21, cómo la letra A tiene el mismo valor de suma en la función ventana que en la función agregada, pero se repite en los resultados porque la entrada tiene dos listados A distintos.

Figura 4-21. Resultados de una función ventana

Funciones de la mesa

Las funciones de tabla, a menudo llamadas funciones tabulares, devuelven resultados en un formato tabular con una o más columnas y ninguna, una o muchas filas. La mayoría de las funciones de tabla de Snowflake son funciones de 1 a N, en las que cada fila de entrada genera N filas de salida, pero existen algunas funciones de tabla de M aN, en las que un grupo de M filas de entrada produce un grupo de N filas de salida. Las funciones de tabla pueden estar definidas por el sistema o por el usuario. Algunos ejemplos de funciones de tabla definidas por el sistema son VALIDATE, GENERATOR, FLATTEN, RESULT_SCAN, LOGIN_HISTORY y TASK_HISTORY.

Funciones del sistema

Las funciones integradas del sistema devuelven información a nivel de sistema o información de consulta, o realizan operaciones de control.

Una función de información del sistema muy utilizada es SYSTEM$CLUSTERING_INFORMATION, que devuelve información de agrupación, incluida la profundidad media de agrupación, sobre una o varias columnas de una tabla.

Las funciones de control del sistema te permiten ejecutar acciones en el sistema. Un ejemplo de función de control es SYSTEM$CANCEL_ALL_QUERIES y requiere el identificador de sesión. Puedes obtener el ID de sesión accediendo como ACCOUNTADMIN. En el menú principal de Snowsight, ve a Actividad → Historial de consultas y, a continuación, utiliza el botón Columna para seleccionar el ID de sesión y que se muestre. Alternativamente, ve a Cuenta → Sesiones en la interfaz de la Consola Clásica:

SELECT SYSTEM$CANCEL_ALL_QUERIES(<session_id>);

Si necesitas cancelar consultas para un almacén virtual o un usuario concretos y no para la sesión, deberás utilizar el comando ALTER junto con ABORT ALL QUERIES en lugar de una función de control del sistema.

Creación de UDFs SQL y JavaScript y Uso de Variables de Sesión

La funcionalidad de SQL puede ampliarse mediante UDFs de SQL, UDFs de Java, UDFs de Python y variables de sesión. En el Capítulo 3 profundizamos en las UDF de SQL y JavaScript, así que en esta sección nos centraremos en aprender más sobre las variables de sesión.

Snowflake admite variables SQL declaradas por el usuario, mediante el comando SET. Estas variables de sesión existen mientras está activa una sesión Snowflake. Las variables se distinguen en una sentencia SQL Snowflake por un prefijo $ y también pueden contener nombres de identificadores cuando se utilizan con objetos. Debes envolver una variable dentro del identificador, como por ejemplo IDENTIFIER($Variable)para utilizar una variable como identificador. Alternativamente, puedes envolver la variable dentro de un objeto en el contexto de una cláusula FROM.

Para ver todas las variables definidas en la sesión actual, utiliza el comando SHOW VARIABLES .

Algunos ejemplos de funciones de variables de sesión son los siguientes:

  • SYS_CONTEXT y SET_SYS_CONTEXT

  • SESSION_CONTEXT y SET_SESSION_CONTEXT

  • GETVARIABLE y SETVARIABLE

Todas las variables creadas durante una sesión se eliminan cuando se cierra una sesión Snowflake. Si quieres destruir una variable durante una sesión, puedes utilizar el comando UNSET.

Funciones externas

Una función externa es un tipo de UDF que llama a código que se almacena y ejecuta fuera de Snowflake. Snowflake admite funciones externas escalares, lo que significa que el servicio remoto debe devolver exactamente una fila por cada fila recibida. Dentro de Snowflake, la función externa se almacena como un objeto de base de datos que Snowflake utiliza para llamar al servicio remoto.

En es importante señalar que, en lugar de llamar directamente a un servicio remoto, Snowflake suele llamar a un servicio proxy para retransmitir los datos al servicio remoto. Amazon API Gateway y el servicio de gestión de API de Microsoft Azure son dos ejemplos de servicios proxy que pueden utilizarse. Un servicio remoto puede implementarse como una función AWS Lambda, una función Microsoft Azure o un servidor HTTPS (por ejemplo, Node.js) que se ejecuta en una instancia EC2.

Cualquier cargo de por proveedores de servicios remotos se facturará por separado. Snowflake cobra los costes normales asociados a la transferencia de datos y al uso del almacén virtual cuando se utilizan funciones externas.

Utilizar funciones externas tiene muchas ventajas. Las funciones externas pueden crearse para ser llamadas desde otros programas de software, además de ser llamadas desde dentro de Snowflake. Además, el código de los servicios remotos puede escribirse en lenguajes como Go o C#, lenguajes que no pueden utilizarse en otras UDF admitidas por Snowflake. Una de las mayores ventajas es que los servicios remotos para las funciones externas de Snowflake pueden interconectarse con bibliotecas de terceros disponibles comercialmente, como las bibliotecas de puntuación de aprendizaje automático.

Limpieza del código

La limpieza del código de este capítulo es sencilla. Puedes utilizar el siguiente comando para eliminar la base de datos que creamos anteriormente:

DROP DATABASE DEMO4_DB;

Observa que no tenemos que eliminar primero todas las tablas, porque al eliminar la base de datos se eliminarán automáticamente las tablas asociadas.

Resumen

En este capítulo, hemos creado y ejecutado todas nuestras consultas Snowflake utilizando el rol SYSADMIN. Esto se hizo intencionadamente para que pudiéramos centrarnos en aprender los fundamentos de los comandos, funciones, sentencias y tipos de datos SQL de Snowflake sin añadir la complejidad de tener que navegar por los controles de acceso de Snowflake. Ahora es el momento de construir sobre estos conocimientos básicos, junto con lo que aprendimos en el Capítulo 3 sobre la creación y gestión de objetos de arquitectura.

En el próximo capítulo, profundizaremos en el aprovechamiento de los controles de acceso de Snowflake. Si esperas que se te asignen responsabilidades de administrador para una de las funciones principales de administrador, el próximo capítulo será probablemente uno de los más importantes para ti en tu viaje de aprendizaje de Snowflake. Aunque nunca esperes desempeñar funciones de administrador, necesitarás saber cómo aprovechar toda la funcionalidad de Snowflake dentro de los permisos que se te asignen. Además, aunque no se te asigne un papel de administrador de Snowflake, es probable que se te dé acceso para realizar algunas funciones antes reservadas sólo a los administradores.

Snowflake ha puesto mucho cuidado en diseñar y construir controles de acceso que aborden algunos de los puntos débiles de otras plataformas. Un ejemplo de ello es que Snowflake ha diseñado a propósito un sistema de control de acceso que elimina el concepto de superusuario, un riesgo importante de muchas plataformas. Dicho esto, es importante reconocer que todavía hay mucho que puedes aprender sobre los controles de acceso únicos de Snowflake, incluso si tienes experiencia con controles de acceso construidos para otras plataformas.

Comprobación de conocimientos

Las siguientes preguntas se basan en la información contenida en este capítulo:

  1. ¿Qué puedes utilizar para asegurarte de que una línea de texto es un comentario en lugar de que se trate como código?

  2. Los tipos de datos de cadena de Snowflake están soportados por constantes de cadena. ¿Qué delimitadores pueden utilizarse para encerrar cadenas?

  3. ¿Qué ventajas tiene utilizar funciones externas?

  4. ¿Cuál es la duración por defecto que utiliza Snowflake para determinar cuándo cancelar las consultas de larga duración? ¿Puedes cambiar esa duración y, en caso afirmativo, cómo lo harías?

  5. ¿Cuáles son los riesgos de utilizar tipos de datos de números en coma flotante?

  6. ¿En qué se diferencia una función ventana de una función agregada?

  7. ¿Soporta Snowflake tipos de datos no estructurados?

  8. ¿Qué tipos de datos semiestructurados admite Snowflake?

  9. ¿Admite el tipo de datos TIMESTAMP de Snowflake las zonas horarias locales y el horario de verano? Explícalo.

  10. ¿Qué son las columnas derivadas y cómo pueden utilizarse en Snowflake?

  11. ¿Cuáles son las tres formas de acceder a los archivos de datos no estructurados en Snowflake?

  12. Enumera algunos ejemplos de datos no estructurados.

  13. ¿Qué tipo de tabla es una tabla de directorio?

Las respuestas a estas preguntas están disponibles en el Apéndice A.

Get Copo de nieve: La Guía Definitiva now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial