Tutorial de unión y subconsulta de Hive con ejemplos

Unirse a consultas

Las consultas de unión se pueden realizar en dos tablas presentes en ColmenaPara comprender claramente los conceptos de unión, crearemos aquí dos tablas:

• muestras_uniones (relacionadas 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 las columnas Id, Nombre, Edad, dirección y salario de los empleados. La siguiente captura de pantalla muestra la instrucción CREATE TABLE y su confirmación.

Paso 2) Cargando y mostrando los datos. La siguiente captura de pantalla muestra el comando de carga seguido del contenido de la tabla.

Según la captura de pantalla anterior:

1. Cargando datos en sample_joins desde Customers.txt
2. Mostrando el contenido de la tabla sample_joins

Paso 3) Creación de la tabla sample_joins1, y posteriormente carga y visualización de sus datos, tal como se muestra en la captura de pantalla a continuación.

En la captura de pantalla anterior, podemos observar lo siguiente:

1. Creación de la tabla sample_joins1 con las columnas Orderid, Date1, Id y Amount.
2. Cargando datos en sample_joins1 desde pedidos.txt
3. Mostrando registros presentes en sample_joins1

A continuación, veremos los diferentes tipos de uniones que se pueden realizar en las tablas que hemos creado. Antes de eso, debe tener en cuenta los siguientes puntos sobre las uniones.

Algunos puntos a tener en cuenta en las uniones:

• Solo se permiten uniones de igualdad en las uniones.
• Se pueden unir más de dos tablas en una misma consulta.
• Las uniones LEFT, RIGHT y FULL OUTER existen para proporcionar un mayor control sobre la cláusula ON para la cual no hay coincidencia.
• Las uniones no son conmutativas.
• Las uniones son asociativas por la izquierda independientemente de si son uniones IZQUIERDA o DERECHA

La restricción de igualdad refleja el funcionamiento de Hive durante muchos años. A partir de Hive 2.2.0, se admiten expresiones complejas en la cláusula ON (HIVE-15211), por lo que se acepta una condición de no igualdad en la versión actual. En versiones anteriores, la condición debe ser una prueba de igualdad, y cualquier otra condición debe incluirse en una cláusula WHERE.

Diferentes tipos de uniones

Existen 4 tipos de uniones. Estas son:

• Unir internamente
• Izquierda combinación externa
• Unión exterior derecha
• Unión externa completa

Cada tipo se muestra a continuación comparándolo con las mismas dos tablas, por lo que lo único que cambia entre los ejemplos es qué filas no coincidentes se conservan.

Unir internamente

Mediante esta unión interna se recuperarán los registros comunes a ambas tablas. El resultado que se muestra en la captura de pantalla a continuación contiene solo los clientes que tienen un pedido coincidente.

En la captura de pantalla anterior, podemos observar lo siguiente:

1. Aquí estamos realizando una consulta de unión utilizando la palabra clave JOIN entre las tablas sample_joins y sample_joins1, con la condición de coincidencia (c.Id = o.Id).
2. El resultado muestra los registros comunes presentes en ambas tablas, seleccionados al comprobar 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

• ColmenaQL LEFT OUTER JOIN devuelve todas las filas de la tabla izquierda aunque no haya coincidencias en la tabla derecha.
• Si la cláusula ON no encuentra ningún registro en la tabla derecha, la unión aún devuelve un registro en el resultado con NULL en cada columna de la tabla derecha.

La captura de pantalla que aparece a continuación muestra que aparecen todos los clientes, incluidos aquellos que no han realizado ningún pedido.

En la captura de pantalla anterior, podemos observar lo siguiente:

1. Aquí realizamos una consulta de unión utilizando la palabra clave "LEFT OUTER JOIN" entre las tablas sample_joins y sample_joins1, con la condición de coincidencia (c.Id = o.Id). Por ejemplo, aquí usamos el ID del empleado como referencia; se comprueba si el ID es común tanto a la tabla derecha como a la izquierda. Esto actúa como condición de coincidencia.
2. El resultado muestra los registros seleccionados según la condición especificada en la consulta. Los valores NULL en el resultado anterior corresponden a columnas sin valores de la tabla correcta, es decir, 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

• HiveQL RIGHT OUTER JOIN devuelve todas las filas de la tabla derecha aunque no haya coincidencias en la tabla izquierda.
• Si la cláusula ON no encuentra ningún registro en la tabla izquierda, la unión aún devuelve un registro en el resultado con NULL en cada columna de la tabla izquierda.
• Las uniones RIGHT siempre devuelven registros de la tabla derecha y registros coincidentes de la tabla izquierda. Si la tabla izquierda no tiene ningún valor que corresponda a la columna, devolverá valores NULL en ese lugar.

La captura de pantalla que aparece a continuación muestra la imagen especular del resultado anterior: aparecen todos los pedidos, coincidan o no.

En la captura de pantalla anterior, podemos observar lo siguiente:

1. 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 de coincidencia (c.Id = o.Id).
2. El resultado muestra los registros seleccionados al comprobar 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 externa completa

Combina los registros de las tablas sample_joins y sample_joins1 según la condición JOIN especificada en la consulta.

Devuelve todos los registros de ambas tablas y rellena con valores NULL las columnas cuyos valores coincidentes faltan en cualquiera de los lados, como se muestra en la captura de pantalla a continuación.

En la captura de pantalla anterior, podemos observar lo siguiente:

1. 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 de coincidencia (c.Id = o.Id).
2. El resultado muestra todos los registros presentes en ambas tablas, seleccionados según la condición especificada en la consulta. Los valores NULL en este resultado indican que faltan valores 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

Las uniones colocan las tablas una al lado de la otra. Una subconsulta hace algo diferente: anida una consulta dentro de otra para que la consulta externa pueda trabajar con un resultado que ya ha sido calculado.

Una consulta dentro de otra consulta se conoce como subconsulta. La consulta principal dependerá de los valores que devuelva la subconsulta.

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 respecto a otras tablas.
• Verificación comparativa de los valores de una columna con respecto a 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. La instrucción interna es la subconsulta realizada sobre la tabla t1. Aquí, a y b son columnas que se agregan en la 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

Mientras que una subconsulta remodela los datos únicamente con HiveQL, un script incrustado transfiere las filas a código escrito fuera de Hive.

Hive permite escribir scripts personalizados para satisfacer las necesidades de los clientes. Los usuarios pueden crear sus propios scripts de mapeo y reducción para dichos requisitos. Estos se denominan scripts personalizados integrados. La lógica de codificación se define en el script personalizado, y podemos utilizarlo durante el proceso ETL.

Cuándo elegir scripts incrustados:

• Cuando los requisitos específicos del cliente implican que los desarrolladores tienen que escribir e implementar scripts en Hive.
• Dónde las funciones integradas de Hive no van a funcionar para requisitos de dominio específicos

Para ello, Hive utiliza la cláusula TRANSFORM para incrustar tanto los scripts de mapeo como los de reducción.

En estos scripts personalizados integrados, debemos observar los siguientes puntos:

• Las columnas se transformarán en cadenas de texto y se delimitarán con tabulaciones antes de ser entregadas al script del usuario.
• La salida estándar del script del usuario se tratará como columnas de cadena separadas por tabulaciones.

Ejemplo de script incrustado:

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;

A partir del script anterior, podemos observar lo siguiente. Este es solo un script de ejemplo para fines de comprensión.

• pv_users es la tabla de usuarios, que tiene campos como userid y date como se menciona en map_script.
• El script reductor se define en función de la fecha y el recuento de la tabla pv_users.

Preguntas Frecuentes