Hive Dołącz & Samouczek podQuery z przykładami

Spisie treści:

Anonim

W tym samouczku nauczysz się:

  • Dołącz do zapytań
  • Różne typy złączeń
  • Zapytania podrzędne
  • Osadzanie niestandardowych skryptów
  • UDF (funkcje definiowane przez użytkownika)

Dołącz do zapytań:

Zapytania łączące mogą być wykonywane na dwóch tabelach znajdujących się w gałęzi. Aby lepiej zrozumieć Join Concepts, tutaj tworzymy dwie tabele,

  • Sample_joins (związane ze szczegółami klientów)
  • Sample_joins1 (związane ze szczegółami zamówień wykonanymi przez pracowników)

Krok 1) Utworzenie tabeli „sample_joins” z nazwami kolumn ID, imieniem i nazwiskiem, wiekiem, adresem i wynagrodzeniem pracowników

Krok 2) Ładowanie i wyświetlanie danych

Z powyższego zrzutu ekranu

  1. Ładowanie danych do sample_joins z pliku Customers.txt
  2. Wyświetlanie zawartości tabeli sample_joins

Krok 3) Utworzenie tabeli sample_joins1 i załadowanie, wyświetlenie danych

Na powyższym zrzucie ekranu możemy zauważyć, co następuje

  1. Utworzenie tabeli sample_joins1 z kolumnami Orderid, Date1, Id, Amount
  2. Ładowanie danych do sample_joins1 z pliku orders.txt
  3. Wyświetlanie rekordów obecnych w sample_joins1

Idąc dalej, zobaczymy różne typy sprzężeń, które można wykonać na stworzonych przez nas tabelach, ale przedtem należy wziąć pod uwagę następujące punkty dotyczące łączenia.

Kilka punktów, na które należy zwrócić uwagę w połączeniach:

  • W połączeniach dozwolone są tylko łączenia według równości
  • W jednym zapytaniu można połączyć więcej niż dwie tabele
  • ŁĄCZENIA LEFT, RIGHT, FULL OUTER istnieją w celu zapewnienia większej kontroli nad klauzulą ​​ON, dla której nie ma dopasowania
  • Połączenia nie są przemienne
  • Połączenia są lewostronne, niezależnie od tego, czy są to łączenia LEWE czy PRAWE

Różne typy złączeń

Są to 4 typy połączeń

  • Połączenie wewnętrzne
  • Lewe połączenie zewnętrzne
  • Prawe połączenie zewnętrzne
  • Pełne połączenie zewnętrzne

Połączenie wewnętrzne:

Rekordy wspólne dla obu tabel zostaną pobrane przez to połączenie wewnętrzne.

Na powyższym zrzucie ekranu możemy zauważyć, co następuje

  1. Tutaj wykonujemy zapytanie łączące za pomocą słowa kluczowego JOIN między tabelami sample_joins i sample_joins1 z dopasowanym warunkiem jako (c.Id = o.Id).
  2. Dane wyjściowe wyświetlające wspólne rekordy obecne w obu tabelach przez sprawdzenie warunku wymienionego w zapytaniu

Pytanie:

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

Lewe połączenie zewnętrzne:

  • Język zapytania Hive LEFT OUTER JOIN zwraca wszystkie wiersze z lewej tabeli, mimo że nie ma żadnych dopasowań w prawej tabeli
  • Jeśli klauzula ON pasuje do zero rekordów w prawej tabeli, łączenia nadal zwracają rekord w wyniku z NULL w każdej kolumnie z prawej tabeli

Na powyższym zrzucie ekranu możemy zauważyć, co następuje

  1. Tutaj wykonujemy zapytanie łączące za pomocą słowa kluczowego "LEFT OUTER JOIN" między tabelami sample_joins i sample_joins1 z dopasowanym warunkiem jako (c.Id = o.Id).

    Na przykład tutaj używamy identyfikatora pracownika jako odniesienia, sprawdza, czy identyfikator jest wspólny zarówno po prawej, jak i po lewej stronie tabeli, czy nie. Działa jako warunek dopasowania.

  2. Dane wyjściowe wyświetlające wspólne rekordy obecne w obu tabelach przez sprawdzenie warunku wymienionego w zapytaniu.

    Wartości NULL w powyższym wyjściu to kolumny bez wartości z prawej tabeli, czyli sample_joins1

Pytanie:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Prawe połączenie zewnętrzne:

  • Język zapytania gałęzi RIGHT OUTER JOIN zwraca wszystkie wiersze z prawej tabeli, mimo że nie ma żadnych dopasowań w lewej tabeli
  • Jeśli klauzula ON pasuje do zero rekordów w lewej tabeli, łączenia nadal zwracają rekord w wyniku z NULL w każdej kolumnie z lewej tabeli
  • Łączenia PRAWE zawsze zwracają rekordy z prawej tabeli i dopasowane rekordy z lewej tabeli. Jeśli w lewej tabeli nie ma żadnych wartości odpowiadających kolumnie, zwróci w tym miejscu wartości NULL.

Na powyższym zrzucie ekranu możemy zauważyć, co następuje

  1. Tutaj wykonujemy zapytanie łączące za pomocą słowa kluczowego "RIGHT OUTER JOIN" między tabelami sample_joins i sample_joins1 z dopasowanym warunkiem jako (c.Id = o.Id).
  2. Dane wyjściowe wyświetlające wspólne rekordy obecne w obu tabelach przez sprawdzenie warunku wymienionego w zapytaniu

Zapytanie :

 SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Pełne połączenie zewnętrzne:

Łączy rekordy obu tabel sample_joins i sample_joins1 na podstawie warunku JOIN podanego w zapytaniu.

Zwraca wszystkie rekordy z obu tabel i wypełnia wartości NULL dla kolumn z brakującymi wartościami pasującymi po obu stronach.

Na powyższym zrzucie ekranu możemy zauważyć, co następuje:

  1. Tutaj wykonujemy zapytanie łączące za pomocą słowa kluczowego „FULL OUTER JOIN” między tabelami sample_joins i sample_joins1 z dopasowanym warunkiem jako (c.Id = o.Id).
  2. Dane wyjściowe wyświetlające wszystkie rekordy obecne w obu tabelach przez sprawdzenie warunku wymienionego w zapytaniu. Wartości puste w danych wyjściowych wskazują tutaj brakujące wartości z kolumn obu tabel.

Pytanie

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Zapytania podrzędne:

Zapytanie obecne w zapytaniu jest znane jako zapytanie podrzędne. Główne zapytanie będzie zależeć od wartości zwracanych przez podzapytania.

Podzapytania można podzielić na dwa typy

  • Podzapytania w klauzuli FROM
  • Podzapytania w klauzuli WHERE

Kiedy użyć:

  • Aby uzyskać określoną wartość połączoną z dwóch wartości kolumn z różnych tabel
  • Zależność wartości jednej tabeli od innych tabel
  • Porównawcze sprawdzanie wartości jednej kolumny z innych tabel

Składnia:

Subquery in FROM clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE col1 IN (SubQuery);

Przykład:

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

Tutaj t1 i t2 to nazwy tabel. Kolorowym jest Podzapytanie wykonywane na tabeli t1. Tutaj a i b są kolumnami, które są dodawane w podzapytaniu i przypisywane do col1. Col1 to wartość kolumny obecna w tabeli głównej. Ta kolumna „col1” obecna w podzapytaniu jest odpowiednikiem głównego zapytania tabeli w kolumnie col1.

Osadzanie niestandardowych skryptów:

Hive zapewnia możliwość pisania skryptów specyficznych dla użytkownika zgodnie z wymaganiami klienta. Użytkownicy mogą napisać własną mapę i zredukować skrypty do wymagań. Są to tak zwane wbudowane skrypty niestandardowe. Logika kodowania jest zdefiniowana w niestandardowych skryptach i możemy używać tego skryptu w czasie ETL.

Kiedy wybrać wbudowane skrypty:

  • W przypadku specyficznych wymagań klienta programiści muszą pisać i wdrażać skrypty w Hive
  • Gdzie wbudowane funkcje Hive nie będą działać w przypadku określonych wymagań domeny

W tym celu w Hive używa klauzuli TRANSFORM, aby osadzić skrypty mapowania i reduktora.

W tych wbudowanych skryptach niestandardowych musimy przestrzegać następujących punktów

  • Kolumny zostaną przekształcone w łańcuch i rozdzielone TAB przed przekazaniem ich do skryptu użytkownika
  • Standardowe dane wyjściowe skryptu użytkownika będą traktowane jako kolumny z ciągami znaków oddzielonymi tabulatorami

Przykładowy skrypt osadzony,

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;

Z powyższego skryptu możemy zauważyć, co następuje

To jest tylko przykładowy skrypt do zrozumienia

  • pv_users to tabela użytkowników, która zawiera pola takie jak identyfikator użytkownika i data, jak wspomniano w map_script
  • Skrypt redukujący zdefiniowany na podstawie daty i liczby tabel pv_users