Consultas de Hive | 15 consultas básicas de Hive para ingenieros de datos

Visión general

• Conozca 15 consultas básicas de colmena que incluyen-
  • Selecciones simples: selección de columnas
  • Selecciones simples: selección de filas
  • Creando nuevas columnas
  • Funciones de Hive
• De ninguna manera esta lista es exhaustiva. Siéntase libre de agregar más en la sección de comentarios.

Introducción

Tabla de contenido:

• Selecciones simples: selección de columnas
• Selecciones simples: selección de filas
• Creando nuevas columnas
• Funciones de Hive
  • Funciones simples
  • Agregaciones
  • Funciones de fecha

Selecciones simples: selección de columnas

En Hive, la consulta de datos se realiza a través de una instrucción SELECT. Una declaración de selección tiene 6 componentes clave;

• SELECCIONAR nombres de columna
• FROM nombre-tabla
• GROUP BY nombres de columna
• DONDE condiciones
• TENIENDO condiciones
• ORDENAR por nombres de columna

En la práctica, muy pocas consultas tendrán todas estas cláusulas, lo que simplifica muchas consultas. Por otra parte, las condiciones en la cláusula WHERE pueden ser muy complejas y si necesita unir dos o más tablas juntas, se necesitan más cláusulas (JOIN y ON).

Todos los nombres de las cláusulas anteriores se han escrito en mayúsculas para mayor claridad. HQL no distingue entre mayúsculas y minúsculas. Tampoco es necesario escribir cada cláusula en una nueva línea, pero a menudo es más claro hacerlo para todas las consultas menos las más simples.

En esta lección, comenzaremos con lo muy simple y avanzaremos hasta lo más complejo.

Selecciones simples: selección de columnas

Entre todas las consultas de Hive, la consulta más simple es efectivamente aquella que devuelve el contenido de toda la tabla.

SELECT *
FROM geog_all;
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...)  ]

Es mejor practicar y, en general, más eficaz enumerar explícitamente los nombres de columna que desea que se devuelvan.

SELECT anonid, fueltypes, acorn_type
FROM geog_all;

Selecciones simples: selección de filas

Al mismo tiempo de limitar las columnas devueltas por una consulta, además puede limitar las filas devueltas. El caso más simple dicho de otra forma cuántas filas se desean usando la cláusula Limit.

SELECT anonid, fueltypes, acorn_type
FROM geog_all
LIMIT 10;

Esto es útil si solo desea tener una idea de cómo se ven los datos. Por lo general, querrá restringir las filas devueltas en función de algunos criterios. dicho de otra forma, ciertos valores o rangos dentro de una o más columnas.

SELECT anonid, fueltypes, acorn_type
FROM geog_all
WHERE fueltypes = "ElecOnly";

La expresión en la cláusula where puede ser más compleja e incluir más de una columna.

SELECT anonid, fueltypes, acorn_type
FROM geog_all
WHERE fueltypes = "ElecOnly" AND acorn_type > 42;

SELECT anonid, fueltypes, acorn_type
FROM geog_all
WHERE fueltypes = "ElecOnly" AND acorn_type > 42 AND nuts1 <> "--";

Observe que las columnas utilizadas en las condiciones de la cláusula Where no disponen que aparecer en la cláusula Select. Además se pueden usar otros operadores en la cláusula where. Para expresiones complejas, se pueden utilizar corchetes para imponer precedencia.

SELECT anonid, fueltypes, acorn_type, nuts1, ldz
FROM geog_all
WHERE
fueltypes = "ElecOnly"
AND acorn_type BETWEEN 42 AND 47
AND (nuts1 NOT IN ("UKM", "UKI") OR ldz = "--");

Creando nuevas columnas

Es factible crear nuevas columnas en la salida de la consulta. Estas columnas pueden provenir de combinaciones de otras columnas que usan operadores y / o funciones integradas de Hive.

SELECT anonid, eprofileclass, acorn_type, (eprofileclass * acorn_type) AS multiply, (eprofileclass + acorn_type) AS added
FROM edrp_geography_data b;

Puede hallar una lista completa de los operadores y funciones disponibles dentro de Hive en el documentación.

Cuando crea una nueva columna, es usual proporcionar un ‘alias’ para la columna. Este es esencialmente el nombre que desea darle a la nueva columna. El alias se da inmediatamente después de la expresión a la que se refiere. Opcionalmente, puede agregar la keyword AS para mayor claridad. Si no proporciona un alias para sus nuevas columnas, Hive generará un nombre para usted.

Aún cuando el término alias puede parecer un poco extraño para una nueva columna que no tiene un nombre natural, alias ‘además se puede utilizar con cualquier columna existente para proporcionar un nombre más significativo en la salida.

A las tablas además se les puede dar un alias, esto es concretamente común en consultas de combinación que involucran múltiples tablas donde es necesario distinguir entre columnas con el mismo nombre en diferentes tablas. Al mismo tiempo de utilizar operadores para crear nuevas columnas, además hay muchas funciones integradas de Hive que se pueden utilizar.

Funciones de Hive

Funciones simples

Concat se puede utilizar para agregar cadenas

SELECT anonid, acorn_category,
acorn_group,
acorn_type,
concat (acorn_category, ",", acorn_group, ",", acorn_type)  AS acorn_code
FROM geog_all;

substr se puede usar para extraer una parte de una cadena

SELECT anon_id,
advancedatetime,
substr (advancedatetime, 1, 2) AS day,
substr (advancedatetime, 3, 3) AS month,
substr (advancedatetime, 6, 2) AS year
FROM elec_c;

Ejemplos de longitud, instrucción e inversión

SELECT anonid,
     acorn_code,
     length (acorn_code),
     instr (acorn_code, ',') AS a_catpos,
     instr (reverse (acorn_code), "," ) AS reverse_a_typepo

Donde las funciones imprescindibles se pueden anidar entre sí y conversiones de tipos

SELECT anonid,
substr (acorn_code, 7, 2) AS ac_type_string,
cast (substr (acorn_code, 7, 2) AS INT) AS ac_type_int,
substr (acorn_code, 7, 2) +1 AS ac_type_not_sure
FROM geog_all;

Agregaciones

Las funciones agregadas se usan para realizar algún tipo de cálculo matemático o estadístico en un grupo de filas. Las filas de cada grupo están determinadas por los diferentes valores en una columna o columnas específicas. Una lista de todas las funciones disponibles está habilitada en la documentación de apache.

SELECT anon_id,
              count (eleckwh) AS total_row_count,
              sum (eleckwh) AS total_period_usage,
              min (eleckwh) AS min_period_usage,
              avg (eleckwh) AS avg_period_usage,
             max (eleckwh) AS max_period_usage
       FROM elec_c
GROUP BY anon_id;

En el ejemplo anterior, se realizaron cinco agregaciones en una sola columna anon_id. Es factible agregar en varias columnas especificándolas tanto en la cláusula select como en la cláusula group by. La agrupación se llevará a cabo según el orden de las columnas enumeradas en la cláusula group by. Lo que no está permitido es especificar una columna no agregada en la cláusula select que no se menciona en la cláusula group by.

SELECT anon_id,
              substr (advancedatetime, 6, 2) AS reading_year,
              count (eleckwh) AS total_row_count,
              sum (eleckwh) AS total_period_usage,
              min (eleckwh) AS min_period_usage,
              avg (eleckwh) AS avg_period_usage,
              max (eleckwh) AS max_period_usage
       FROM elec_c
GROUP BY anon_id, substr (advancedatetime, 6, 2);

Desafortunadamente, la cláusula group by no aceptará alias ‘.

SELECT anon_id,
              substr (advancedatetime, 6, 2) AS reading_year,
              count (eleckwh) AS total_row_count,
              sum (eleckwh) AS total_period_usage,
              min (eleckwh) AS min_period_usage,
              avg (eleckwh) AS avg_period_usage,
              max (eleckwh) AS max_period_usage
      FROM elec_c
GROUP BY anon_id, substr (advancedatetime, 6, 2)
ORDER BY anon_id, reading_year;

Pero la cláusula Order by sí lo hace.

La keyword Distinct proporciona un conjunto de una combinación única de valores de columna dentro de una tabla sin ningún tipo de agregación.

SELECT DISTINCT eprofileclass, fueltypes
FROM geog_all;

Funciones de fecha

En las tablas elec_c y gas_c, la columna de fecha y hora de avance, aún cuando contiene información sobre el tipo de marca de tiempo, se establece como un tipo de cadena. Durante la mayor parte del tiempo, esto puede resultar bastante conveniente. A pesar de esto, habrá ocasiones en las que verdaderamente necesitemos poder tratar la columna como una marca de tiempo. Tal vez el ejemplo más obvio es cuando necesita ordenar las filas según la columna avanzada en el tiempo.

Hive proporciona una gama de funciones asociadas con la fecha que le posibilitan convertir cadenas en marca de tiempo y, al mismo tiempo, extraer partes de la marca de tiempo.

unix_timestamp devuelve los datos y la hora actuales, ¡como un número entero!

from_unixtime toma un número entero y lo convierte en una cadena de marca de tiempo reconocible

SELECT unix_timestamp () AS currenttime
FROM sample_07
LIMIT 1;

SELECT from_unixtime (unix_timestamp ()) AS currenttime
FROM sample_07
LIMIT 1;

Hay varias herramientas de parte de fecha que extraerán las partes relevantes de una cadena de marca de tiempo

SELECT anon_id,
             from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy'))
                  AS proper_date,
            year (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy')))
                 AS full_year,
            month (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy')))
                AS full_month,
            day (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy')))
               AS full_day,
           last_day (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy')))
              AS last_day_of_month,
           date_add ( (from_unixtime (UNIX_TIMESTAMP (reading_date, 'ddMMMyy'))),10)
              AS added_days
FROM elec_days_c
ORDER BY proper_date;

Conclusión:

En el post, cubrimos algunas funciones y consultas básicas de Hive. Ejecutar consultas en datos distribuidos no es muy distinto de ejecutar consultas en MySQL. Esto seguirá a algunos posts en los que cubriremos funciones y consultas más avanzadas. Espero que te haya gustado el post. No olvide dejar sus comentarios en la sección de comentarios a continuación.

Le recomiendo que lea estos posts para familiarizarse con las herramientas para big data:

Háganos saber sus pensamientos en los comentarios a continuación.

Relacionado