Tutorial de unión y subconsulta de Hive con ejemplos
David Carter
Unirse a consultas
Las consultas de unión se pueden realizar en dos tablas presentes en Hive. Para entender Únete Concepts en claro aquí estamos creando dos tablas aquí,
- Sample_joins(Relacionado con los detalles del cliente)
- Sample_joins1 (Relacionado con los detalles de los pedidos realizados por los empleados)
Paso 1) Creación de la tabla “sample_joins” con los nombres de las columnas ID, nombre, edad, dirección y salario de los empleados.
Paso 2) Cargando y mostrando datos
De la captura de pantalla anterior
- Cargando datos en sample_joins desde Customers.txt
- Mostrando el contenido de la tabla sample_joins
Paso 3) Creación de la tabla sample_joins1 y carga, visualización de datos.
De la captura de pantalla anterior, podemos observar lo siguiente
- Creación de la tabla sample_joins1 con las columnas Orderid, Date1, Id, Amount
- Cargando datos en sample_joins1 desde pedidos.txt
- Mostrando registros presentes en sample_joins1
Más adelante veremos diferentes tipos de uniones que se pueden realizar en las tablas que hemos creado, pero antes de eso debes considerar los siguientes puntos para las uniones.
Algunos puntos a observar en Joins:
- Sólo se permiten uniones de igualdad en uniones
- Se pueden unir más de dos tablas en una misma consulta.
- Existen uniones IZQUIERDA, DERECHA y EXTERIOR COMPLETA para proporcionar más control sobre la cláusula ON para la que no hay coincidencia.
- Las uniones no son conmutativas
- Las uniones son asociativas por la izquierda independientemente de si son uniones IZQUIERDA o DERECHA
Diferentes tipos de uniones
Las uniones son de 4 tipos, estas son
- Unir internamente
- Izquierda combinación externa
- Unión exterior derecha
- Unión externa completa
Unir internamente:
Esta combinación interna recuperará los registros comunes a ambas tablas.
De la captura de pantalla anterior, podemos observar lo siguiente
- Aquí estamos realizando una consulta de unión usando la palabra clave JOIN entre las tablas sample_joins y sample_joins1 con la condición coincidente como (c.Id= o.Id).
- El resultado muestra registros comunes presentes en ambas tablas al verificar la condición mencionada en la consulta.
consulta:
SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);
Izquierda combinación externa:
- Lenguaje de consulta de colmena LEFT OUTER JOIN devuelve todas las filas de la tabla de la izquierda aunque no haya coincidencias en la tabla de la derecha
- Si la cláusula ON coincide con cero registros en la tabla de la derecha, las combinaciones aún devuelven un registro en el resultado con NULL en cada columna de la tabla de la derecha.
De la captura de pantalla anterior, podemos observar lo siguiente
- Aquí estamos realizando una consulta de unión utilizando la palabra clave “LEFT OUTER JOIN” entre las tablas sample_joins y sample_joins1 con la condición coincidente como (c.Id= o.Id).Por ejemplo: aquí estamos usando la identificación del empleado como referencia, verifica si la identificación es común tanto a la derecha como a la izquierda de la tabla o no. Actúa como condición coincidente.
- El resultado muestra registros comunes presentes en ambas tablas al verificar la condición mencionada en la consulta. Los valores NULL en el resultado anterior son columnas sin valores de la tabla derecha que es sample_joins1
consulta:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Unión exterior derecha:
- El lenguaje de consulta de Hive RIGHT OUTER JOIN devuelve todas las filas de la tabla derecha aunque no haya coincidencias en la tabla izquierda
- Si la cláusula ON coincide con cero registros en la tabla de la izquierda, las combinaciones aún devuelven un registro en el resultado con NULL en cada columna de la tabla de la izquierda.
- Las uniones DERECHAS siempre devuelven registros de una tabla de la derecha y registros coincidentes de la tabla de la izquierda. Si la tabla de la izquierda no tiene valores correspondientes a la columna, devolverá valores NULL en ese lugar.
De la captura de pantalla anterior, podemos observar lo siguiente
- Aquí estamos realizando una consulta de unión utilizando la palabra clave "RIGHT OUTER JOIN" entre las tablas sample_joins y sample_joins1 con la condición coincidente como (c.Id= o.Id).
- El resultado muestra registros comunes presentes en ambas tablas al verificar la condición mencionada en la consulta.
Consulta:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Unión exterior completa:
Combina registros de las tablas sample_joins y sample_joins1 según la condición JOIN proporcionada en la consulta.
Devuelve todos los registros de ambas tablas y completa valores NULL para las columnas con valores faltantes que coinciden en ambos lados.
De la captura de pantalla anterior podemos observar lo siguiente:
- Aquí estamos realizando una consulta de unión utilizando la palabra clave “FULL OUTER JOIN” entre las tablas sample_joins y sample_joins1 con la condición coincidente como (c.Id= o.Id).
- La salida muestra todos los registros presentes en ambas tablas al verificar la condición mencionada en la consulta. Los valores nulos en la salida aquí indican los valores faltantes en las columnas de ambas tablas.
Consulta
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Subconsultas
Una consulta presente dentro de una consulta se conoce como subconsulta. La consulta principal dependerá de los valores devueltos por las subconsultas.
Las subconsultas se pueden clasificar en dos tipos.
- Subconsultas en la cláusula FROM
- Subconsultas en la cláusula WHERE
Cuándo usar:
- Para obtener un valor particular combinado a partir de dos valores de columna de diferentes tablas
- Dependencia de los valores de una tabla en otras tablas
- Comprobación comparativa de los valores de una columna de otras tablas
Sintaxis:
Subquery in FROM clause SELECT <column names 1, 2…n>From (SubQuery) <TableName_Main > Subquery in WHERE clause SELECT <column names 1, 2…n> From<TableName_Main>WHERE col1 IN (SubQuery);
Ejemplo:
SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2
Aquí t1 y t2 son nombres de tablas. El coloreado es la subconsulta realizada en la tabla t1. Aquí a y b son columnas que se agregan en una subconsulta y se asignan a col1. Col1 es el valor de la columna presente en la tabla principal. Esta columna "col1" presente en la subconsulta es equivalente a la consulta de la tabla principal en la columna col1.
Incrustar scripts personalizados
Colmena Proporciona la posibilidad de escribir scripts específicos del usuario para los requisitos del cliente. Los usuarios pueden escribir su propio mapa y reducir los scripts según los requisitos. Estos se denominan scripts personalizados integrados. La lógica de codificación se define en los scripts personalizados y podemos usar ese script en el tiempo ETL.
Cuándo elegir scripts integrados:
- En los requisitos específicos del cliente, los desarrolladores deben escribir e implementar scripts en Hive.
- Dónde las funciones incorporadas de Hive no funcionarán para requisitos de dominio específicos
Para esto, en Hive utiliza la cláusula TRANSFORM para incrustar scripts de mapa y reductor.
En estos scripts personalizados integrados, debemos observar los siguientes puntos
- Las columnas se transformarán en cadenas y se delimitarán con TAB antes de entregárselas al script del usuario.
- La salida estándar del script de usuario se tratará como columnas de cadena separadas por TAB
Script incrustado de muestra,
FROM ( FROM pv_users MAP pv_users.userid, pv_users.date USING 'map_script' AS dt, uid CLUSTER BY dt) map_output INSERT OVERWRITE TABLE pv_users_reduced REDUCE map_output.dt, map_output.uid USING 'reduce_script' AS date, count;
Del guión anterior, podemos observar lo siguiente
Este es sólo el script de muestra para comprender
- pv_users es la tabla de usuarios que tiene campos como ID de usuario y fecha como se menciona en map_script
- Script reductor definido en la fecha y el recuento de las tablas pv_users
