Puntos clave: Consultar datos JSON en Snowflake facilita la gestión y el análisis efectivos de datos estructurados. El tutorial trata sobre la creación de tablas con columnas JSON, la inserción de varios tipos de JSON y la consulta de datos mediante diferentes métodos.
- Pasos para Crea una tabla con una columna JSON e insertar datos.
- Métodos para consultar datos JSON, incluyendo el acceso a atributos anidados y el uso de la función de aplanamiento.
Ya te hemos mostrado cómo Crea una columna variante en una tabla Snowflake, donde variante significa JSON. En este tutorial, mostramos cómo consultar esas columnas JSON.
Crea una tabla con una columna JSON
Primero Crea una base de datos o utiliza la que creamos en el último post y luego Crea una tabla con una columna de variante tipográfica:
use database inventory;
create table jsonRecord(jsonRecord variant);
Añadir datos JSON a Snowflake
Luego, añade algunos datos. Añadiremos JSON simple, JSON anidado y arreglos JSON (i.e. JSON objetos dentro de corchetes []) para mostrar cómo consultar cada tipo. Fíjate en la función parse_json().
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON('{"customer": "Walker"}');
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON('{"customer": "Stephen"}');
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON('{"customer": "Aphrodite", "age": 32}');
Estos registros incluyen una matriz JSON, órdenes.
i
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON(' {
"customer": "Aphrodite",
"age": 32,
"orders": [{
"product": "socks",
"quantity": 4
},
{
"product": "shoes",
"quantity": 3
}
]
}');
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON(' {
"customer": "Nina",
"age": 52,
"orders": [{
"product": "socks",
"quantity": 3
},
{
"product": "shirt",
"quantity": 2
}
]
}');
Este registro incluye JSON anidado, es decir, un atributo, dirección, cuyo valor está otro objeto JSON.
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON(' {
"customer": "Maria",
"age": 22,
"address" : { "city": "Paphos", "country": "Cyprus"},
"orders": [{
"product": "socks",
"quantity": 3
},
{
"product": "shirt",
"quantity": 2
}
]
}');
Ahora selecciona * de JSONRECORD para mostrar todos los registros. Ten en cuenta que son sensibles a mayúsculas y mayúsculas:
- Función
- Columna
- Nombres de tablas
Cómo seleccionar datos JSON en Snowflake
El formato para seleccionar datos incluye todo lo siguiente:
- tableName:attribute
- tableName.attribute.JsonKey
- tableName.attribute.JsonKey[arrayIndex]
- tableName.attribute['JsonKey']
- get_path(tableName, attribute)
Aquí seleccionamos la clave cliente del registro JSON. En JSON llamamos a los pares clave-valor de los elementos, como: {"clave": "valor"}.
select jsonrecord:customer from JSONRECORD;
Así:
También podemos utiliza la función get_path():
select get_path(jsonrecord, 'address') from JSONRECORD;
Aquí añadimos una cláusula where, usando la misma notación dos puntos (:) y punto (.) que en el otro lado de la sentencia select.
select jsonrecord:address.city from JSONRECORD where jsonrecord:customer = 'Maria';
Utilizamos un enfoque alternativo. Obtén objetos JSON anidados poniendo las teclas entre corchetes [].
select jsonrecord['address']['city'] from JSONRECORD where jsonrecord:customer = 'Maria';
Los valores que no existen son indicados como NULL.
Aquí elegimos el primer elemento de un array dado el índice del array (comienza en 0).
select jsonrecord['orders'][0] from JSONRECORD where jsonrecord:customer = 'Maria';
Aquí utiliza el dos puntos (:) a Obtén la misma columna.
select jsonrecord:orders[0] from JSONRECORD where jsonrecord:customer = 'Maria';
Resultados:
{ "product": "socks", "quantity": 3 }
Aquí, aplanamos el array. Este registro tiene dos registros JSON de orden. Así que muestra dos filas en los resultados, con cada registro adjunto a los otros atributos. En otras palabras, lo expande a array_size filas, llenando las otras columnas con las columnas que no son del array en la instrucción select. Piénsalo como una forma sencilla de mostrar todos los pedidos que hizo un cliente donde los datos de pedidos y los datos del cliente son repetidos para Haz fácil de Ve:
select jsonrecord:customer, jsonrecord:orders from JSONRECORD ,
lateral flatten(input => jsonrecord:orders) prod ;
BMC y Control-M apoyan a Snowflake
BMC está miembro del programa de socios de la Alianza Tecnológica Snowflake. La plataforma de datos en la nube de Snowflake ayuda a los clientes a acelerar la empresa basada en datos con el almacén de datos líder en el mercado y construido para la nube de Snowflake, y Control-M, nuestra plataforma líder en el mercado de orquestación de flujos de trabajo de aplicaciones empresariales.
Recursos adicionales
Para más tutoriales como este, explora estos recursos:
- Blog de Aprendizaje Automático y Big Data de BMC
- Guía de AWS, con 15 artículos y tutoriales
- Computación cuántica de Amazon Braket: Cómo Obtén
Guía de copos de nieve
- Snowflake 101: Introducción a la Nube de Datos Snowflake
- Cómo importar datos de Amazon S3 a Snowflake
- Cómo consultar datos JSON en Snowflake
- Cargando archivos CSV de S3 a Snowflake
- Snowflake: Uso de Analítica y Funciones Estadísticas
- Funciones de la ventana copo de nieve: Partición por y orden por
- Función de retraso de copo de nieve y medias móviles
- Funciones agregadas SQL de Snowflake y uniones de tablas
- Funciones definidas por el usuario (UDFs) en Snowflake
- Creación y uso de arroyos de copos de nieve
- Uso de procedimientos almacenados en Snowflake
- Copo de nieve vs Redshift: ¿Cuál es la diferencia y cómo elegir?
Estas publicaciones son mías y no representan necesariamente la posición, estrategias u opinión de BMC.