Esempi di query Hive: Order By, Group By & Cluster By

By: David Carter David Carter
Hours aggiornato

⚡ Riepilogo intelligente

Le query Hive utilizzano le clausole ORDER BY, GROUP BY, SORT BY, CLUSTER BY e DISTRIBUTE BY per ordinare, raggruppare e distribuire le righe tra i reducer, e ciascuna clausola viene illustrata qui su una singola tabella di esempio relativa ai dipendenti.

  • 🧱 Esempio di tabella da iniziare: La tabella employees_guru viene creata con sei colonne e caricata dal file Employees.txt prima dell'esecuzione di qualsiasi clausola.
  • 🔢 ORDINA PER totali: ORDER BY invia l'intero set di risultati a un singolo reducer, garantendo un ordinamento totale ma rallentando le query di grandi dimensioni.
  • 🔤 Ordinamento delle stringhe: Una colonna di tipo stringa, come ad esempio "Dipartimento", viene restituita in ordine lessicografico anziché numerico.
  • 📊 Il raggruppamento per conteggio: L'abbinamento di GROUP BY con count(*) restituisce una riga per reparto con il numero totale di dipendenti.
  • 🔀 L'ordinamento per è per ogni reducer: L'operatore SORT BY ordina le righe all'interno di ciascun reducer, pertanto più reducer producono un output parzialmente ordinato.
  • 🎯 CLSTER BY combina: CLUSTER BY agisce come DISTRIBUTE BY più SORT BY, mentre DISTRIBUTE BY da solo instrada le chiavi corrispondenti a un reducer senza ordinamento.
Scopri di più

Query Hive Ordina per, Raggruppa per, Cluster Da e distribuito da

Hive fornisce un linguaggio di interrogazione di tipo SQL per scopi ETL oltre al Hadoop file system.

Hive Query Language (HiveQL) fornisce un ambiente di tipo SQL all'interno di Hive per lavorare con tabelle, database e query.

A Hive sono associate diverse tipologie di clausole per eseguire diverse tipologie di manipolazione e interrogazione dei dati, e Hive offre connettività JDBC per connessioni più efficienti con nodi esterni all'ambiente.

Le query Hive offrono le seguenti funzionalità:

  • Modellazione dei dati, come la creazione di database, tabelle, ecc.
  • Funzionalità ETL come ad esempiotractrasformazione e caricamento dei dati nelle tabelle
  • Entra a far parte per unire diverse tabelle di dati
  • Script personalizzati specifici dell'utente per facilitare la codifica
  • Strumento di query più veloce su Hadoop

Creazione della tabella in Hive

Prima di iniziare con l'argomento principale di questo tutorial, creeremo una tabella che useremo come riferimento per le sezioni successive.

In questo tutorial, creeremo la tabella "employees_guru" con 6 colonne, come mostrato nello screenshot qui sotto.

Istruzione Hive CREATE TABLE per employees_guru e il comando load

Dalla schermata sopra,

  1. Stiamo creando la tabella "employees_guru" con 6 colonne contenenti i valori Id, Nome, Età, Indirizzo, Stipendio e Dipartimento, che appartengono ai dipendenti presenti nell'organizzazione "guru".
  2. In questa fase carichiamo i dati nella tabella employees_guru. I dati che andremo a caricare si trovano nel file Employees.txt.

Ordina per query

La sintassi ORDER BY in HiveQL è simile alla sintassi di ORDER BY in SQL Lingua.

ORDER BY è la clausola che utilizziamo con l'istruzione “SELECT” in Query alveare Per ordinare i dati. Utilizza le colonne delle tabelle Hive per ordinare i valori delle colonne specifiche indicate con ORDER BY, e la query visualizza i risultati in ordine crescente o decrescente di tali valori.

Se il campo ORDER BY menzionato è una stringa, il risultato viene visualizzato in ordine lessicografico. Nel back-end, l'intero set di risultati deve essere passato a un singolo reducer.

Quel singolo riduttore rende anche ORDER BY costoso su una tabella grande, quindi in modalità rigorosa (hive.mapred.mode=strictHive rifiuta un ORDER BY che non contiene una clausola LIMIT.

Lo screenshot qui sotto mostra la query ORDER BY e le relative righe ordinate.

Query ORDER BY su employees_guru ordinata per Dipartimento

Dallo screenshot qui sopra possiamo osservare quanto segue:

  1. Si tratta di una query eseguita sulla tabella "employees_guru" con la clausola ORDER BY e "Department" come nome della colonna definita nella clausola ORDER BY. "Department" è una stringa, quindi i risultati vengono visualizzati in ordine lessicografico.
  2. Questo è l'output effettivo della query. I risultati vengono visualizzati in base alla colonna Dipartimento, ad esempio AMMINISTRAZIONE, Finanza e così via, in ordine.

Query:

SELECT * FROM employees_guru ORDER BY Department;

Raggruppa per query

La clausola GROUP BY utilizza colonne sulle tabelle Hive per raggruppareping i valori della colonna specifica menzionati con GROUP BY, e la query seleziona e visualizza i risultati raggruppati in base a tali valori.

Ad esempio, la schermata seguente mostra il numero totale di dipendenti presenti in ciascun reparto. In questo caso, il valore di raggruppamento è "Reparto".

query GROUP BY che conta i dipendenti in ogni reparto

Dallo screenshot qui sopra, possiamo osservare quanto segue:

  1. Si tratta della query eseguita sulla tabella "employees_guru" con la clausola GROUP BY e Department come nome della colonna GROUP BY definita.
  2. L'output mostrato qui indica il nome del dipartimento e il numero di dipendenti nei diversi dipartimenti. Tutti i dipendenti appartenenti a uno specifico dipartimento sono raggruppati e visualizzati, quindi ogni riga del risultato corrisponde al nome del dipartimento con il numero totale di dipendenti.

Query:

SELECT Department, count(*) FROM employees_guru GROUP BY Department;

ARTICOLI CORRELATI

Ordina per

La clausola SORT BY opera sui nomi delle colonne delle tabelle Hive per ordinare l'output. Possiamo utilizzare DESC per l'ordinamento decrescente e ASC per l'ordinamento crescente.

L'opzione SORT BY ordina le righe prima di passarle al reducer, garantendo così l'ordinamento all'interno di ciascun reducer anziché nell'intero risultato. L'ordinamento dipende sempre dal tipo di colonna.

Ad esempio, se il tipo di colonna è numerico, l'ordinamento avviene in ordine numerico, mentre se il tipo di colonna è stringa, l'ordinamento avviene in ordine lessicografico.

Lo screenshot qui sotto mostra la query SORT BY utilizzando DESC.

Query SORT BY su employees_guru che restituisce l'ID in ordine decrescente

Dallo screenshot qui sopra possiamo osservare quanto segue:

  1. Si tratta della query eseguita sulla tabella "employees_guru" con la clausola SORT BY e "Id" come nome della colonna di ordinamento definita. Abbiamo utilizzato la parola chiave DESC.
  2. L'output visualizzato è quindi in ordine decrescente di "Id".

Query:

SELECT * from employees_guru SORT BY Id DESC;

Cluster By

CLUSTER BY viene utilizzato come alternativa sia alla clausola DISTRIBUTE BY che alla clausola SORT BY in HiveQL.

La clausola CLUSTER BY viene utilizzata sulle tabelle presenti in Hive. Hive utilizza le colonne specificate in CLUSTER BY per distribuire le righe tra i reducer, e le colonne CLUSTER BY vengono utilizzate da più reducer. Inoltre, garantisce l'ordinamento dei valori presenti in questi reducer.

Ad esempio, la clausola CLUSTER BY è menzionata nel nome della colonna Id della tabella employees_guru. L'esecuzione di questa query fornisce risultati a più reducer nel back-end, ma nel front-end rappresenta una clausola alternativa sia per SORT BY che per DISTRIBUTE BY.

Questo è il processo di back-end che avviene quando eseguiamo una query con SORT BY, GROUP BY o CLUSTER BY nell'ambito del framework MapReduce. Quindi, se vogliamo memorizzare i risultati in più reducer, utilizziamo CLUSTER BY.

La grammatica CLUSTER BY accetta solo nomi di colonna, quindi non è possibile associarvi ASC e DESC; un risultato decrescente richiede DISTRIBUTE BY con un SORT BY … DESC separato.

Lo screenshot qui sotto mostra la query CLUSTER BY sull'ID.

Query CLUSTER BY sulla colonna Id di employees_guru

Dallo screenshot qui sopra otteniamo le seguenti osservazioni:

  1. Si tratta della query che applica la clausola CLUSTER BY al valore del campo Id. In questo caso, verrà eseguito un ordinamento in base ai valori di Id.
  2. Visualizza i valori di Id e Name presenti in employees_guru in ordine ordinato.

Query:

SELECT  Id, Name from employees_guru CLUSTER BY Id;

Distribuisci per

La clausola DISTRIBUTE BY viene utilizzata sulle tabelle presenti in Hive. Hive utilizza le colonne specificate in DISTRIBUTE BY per distribuire le righe tra i reducer, in modo che tutte le righe che condividono lo stesso valore nella colonna DISTRIBUTE BY vengano assegnate allo stesso reducer.

  • Garantisce che ciascuno degli N riduttori riceva un non-sovrapposizioneping insieme dei valori della colonna
  • Non ordina l'output di ciascun reducer e non è garantito che le righe corrispondenti siano adiacenti.

Lo screenshot qui sotto mostra la query DISTRIBUTE BY sull'ID.

Query DISTRIBUTE BY sulla colonna Id di employees_guru

Dallo screenshot qui sopra, possiamo osservare quanto segue:

  1. La clausola DISTRIBUTE BY viene eseguita sull'ID della tabella "employees_guru".
  2. L'output mostra ID e Nome. Nel back-end, le righe con lo stesso ID vengono indirizzate allo stesso reducer.

Query:

SELECT  Id, Name from employees_guru DISTRIBUTE BY Id;

Le quattro clausole sono facili da confondere, quindi la tabella seguente le confronta.

Clausola riduttori Ciò che garantisce
ORDINATO DA Un Ordine totale sull'intero risultato
ORDINAMENTO Molti Ordinare solo all'interno di ciascun riduttore
DISTRIBUITO DA Molti La stessa chiave raggiunge lo stesso riduttore, non ordinato
GRUPPO PER Molti DISTRIBUIRE PER più ORDINA PER, crescente

DOMANDE FREQUENTI

Un singolo reducer deve ordinare ogni riga, operazione che può richiedere ore su una tabella di grandi dimensioni. Limiti che funzionano. Impostando hive.mapred.mode su nonstrict si rimuove completamente la restrizione.

Imposta `mapreduce.job.reduces` prima della query per impostare un conteggio fisso, altrimenti Hive lo stima in base alla dimensione dell'input. `ORDER BY` ignora questa impostazione, perché un ordine totale si concentra sempre su un singolo reducer.

No. La clausola accetta solo nomi di colonna e ordina sempre in ordine crescente. Scrivi DISTRIBUTE BY sulla colonna di partizione con una colonna SORT BY DESC separata; le due colonne possono anche essere diverse.

Sono imparentati, ma non identici. Svuotamento L'opzione `CLUSTER BY` memorizza le righe in modo permanente in un numero fisso di file, mentre `CLUSTER BY` distribuisce e ordina le righe solo per la durata di una singola query.

Gli assistenti di apprendimento automatico leggono il piano EXPLAIN e segnalano cause come un ORDER BY illimitato, un filtro di partizione mancante o una chiave non corretta. Confermano ogni suggerimento confrontandolo con l'esecuzione effettiva.

Disegna bene questi modelli da un breve commento. Verifica qualsiasi cosa specifica del motore, perché si mescola facilmente Spark Sintassi SQL o Presto che Hive rifiuta, come DESC dopo CLUSTER BY.

Un file di testo semplice denominato Employees.txt contiene i valori delle sei colonne e viene caricato nella tabella prima dell'esecuzione della prima query. Qualsiasi file delimitato con colonne corrispondenti funziona allo stesso modo.

La semantica è identica, perché si tratta di caratteristiche del linguaggio HiveQL e non di caratteristiche del motore. Cambia solo il piano di esecuzione fisico: i motori pianificano le fasi di ordinamento e riordino in modo diverso, quindi i tempi di esecuzione variano mentre i risultati rimangono invariati.

Riassumi questo post con: