Hive Join & Tutorial de subconsultas con ejemplos

Tabla de contenido:

Anonim

En este tutorial, aprenderá:

  • Unir consultas
  • Diferentes tipos de uniones
  • Subconsultas
  • Incrustación de scripts personalizados
  • UDF (funciones definidas por el usuario)

Unir consultas:

Las consultas de unión se pueden realizar en dos tablas presentes en Hive. Para entender claramente los conceptos de unión aquí, estamos creando dos tablas aquí,

  • Sample_joins (relacionado con los detalles de los clientes)
  • 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) Carga y visualización de datos

De la captura de pantalla anterior

  1. Cargando datos en sample_joins desde Customers.txt
  2. Visualización del contenido de la tabla sample_joins

Paso 3) Creación de la tabla sample_joins1 y carga, mostrando datos

De la captura de pantalla anterior, podemos observar lo siguiente

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

En el futuro, veremos diferentes tipos de combinaciones que se pueden realizar en las tablas que hemos creado, pero antes de eso, debe considerar los siguientes puntos para las combinaciones.

Algunos puntos a observar en Joins:

  • Solo se permiten combinaciones de igualdad en combinaciones
  • Se pueden unir más de dos tablas en la misma consulta
  • Las uniones LEFT, RIGHT, FULL OUTER existen 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, estos son

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

Unir internamente:

Los Registros comunes a ambas tablas serán recuperados por esta Inner Join.

De la captura de pantalla anterior, podemos observar lo siguiente

  1. Aquí estamos realizando una consulta de unión usando la palabra clave JOIN entre las tablas sample_joins y sample_joins1 con una condición coincidente como (c.Id = o.Id).
  2. La salida que 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:

  • El lenguaje de consulta de Hive LEFT OUTER JOIN devuelve todas las filas de la tabla izquierda aunque no haya coincidencias en la tabla 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

  1. Aquí estamos realizando una consulta de combinación usando la palabra clave "LEFT OUTER JOIN" entre las tablas sample_joins y sample_joins1 con una 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 de coincidencia.

  2. La salida que muestra registros comunes presentes en ambas tablas al verificar la condición mencionada en la consulta.

    Los valores NULL en la salida 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 DERECHA siempre devuelven registros de una tabla Derecha y registros coincidentes de la tabla 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

  1. Aquí estamos realizando una consulta de combinación usando la palabra clave "RIGHT OUTER JOIN" entre las tablas sample_joins y sample_joins1 con una condición coincidente como (c.Id = o.Id).
  2. La salida que 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 externa completa:

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

Devuelve todos los registros de ambas tablas y completa los valores NULL para las columnas que faltan valores coincidentes en ambos lados.

De la captura de pantalla anterior podemos observar lo siguiente:

  1. Aquí estamos realizando una consulta de combinación usando la palabra clave "FULL OUTER JOIN" entre las tablas sample_joins y sample_joins1 con una condición coincidente como (c.Id = o.Id).
  2. La salida que muestra todos los registros presentes en la tabla al verificar la condición mencionada en la consulta. Los valores nulos en la salida aquí indican los valores faltantes de 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 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 clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE col1 IN (SubQuery);

Ejemplo:

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

Aquí t1 y t2 son nombres de tablas. La de color es la subconsulta realizada en la tabla t1. Aquí ayb 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.

Incrustación de scripts personalizados:

Hive ofrece 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 para los requisitos. Estos se denominan secuencias de comandos personalizadas incrustadas. La lógica de codificación se define en los scripts personalizados y podemos usar ese script en el tiempo ETL.

Cuándo elegir secuencias de comandos integradas:

  • En los requisitos específicos del cliente, los desarrolladores deben escribir e implementar scripts en Hive.
  • Donde las funciones integradas de Hive no van a funcionar para requisitos de dominio específicos

Para esto, en Hive usa la cláusula TRANSFORM para incrustar los scripts de mapa y de reducción.

En este scripts personalizados incrustados, tenemos que observar los siguientes puntos

  • Las columnas se transformarán en una cadena y se delimitarán con TAB antes de dárselas al script del usuario.
  • La salida estándar del script de usuario se tratará como columnas de cadena separadas por TAB.

Ejemplo de secuencia de comandos incrustada,

FROM (FROM pv_usersMAP pv_users.userid, pv_users.dateUSING 'map_script'AS dt, uidCLUSTER BY dt) map_outputINSERT OVERWRITE TABLE pv_users_reducedREDUCE map_output.dt, map_output.uidUSING 'reduce_script'AS date, count;

Del script anterior, podemos observar lo siguiente

Este es solo el script de muestra para comprender

  • pv_users es la tabla de usuarios que tiene campos como userid y date como se menciona en map_script
  • Script reductor definido en la fecha y recuento de las tablas pv_users