Hive Bergabung & Tutorial SubQuery dengan Contoh

Daftar Isi:

Anonim

Dalam tutorial ini, Anda akan belajar-

  • Bergabunglah dengan kueri
  • Jenis gabungan yang berbeda
  • Sub pertanyaan
  • Menyematkan skrip kustom
  • UDFs (User Define Functions)

Gabung kueri:

Kueri gabungan dapat dilakukan di dua tabel yang ada di Hive. Untuk memahami Konsep Gabungan secara jelas di sini kami membuat dua tabel di sini,

  • Sample_joins (Terkait dengan Detail Pelanggan)
  • Sample_joins1 (Terkait dengan detail pesanan yang dilakukan oleh Karyawan)

Langkah 1) Pembuatan tabel "sample_joins" dengan nama Kolom ID, Nama, Umur, alamat dan gaji karyawan

Langkah 2) Memuat dan Menampilkan Data

Dari tangkapan layar di atas

  1. Memuat data ke sample_joins dari Customers.txt
  2. Menampilkan isi tabel sample_joins

Langkah 3) Pembuatan tabel sample_joins1 dan memuat, menampilkan data

Dari tangkapan layar di atas, kita dapat mengamati yang berikut ini

  1. Pembuatan table sample_joins1 dengan kolom Orderid, Date1, Id, Amount
  2. Memuat data ke sample_joins1 dari orders.txt
  3. Menampilkan catatan yang ada di sample_joins1

Ke depan kita akan melihat berbagai jenis gabungan yang dapat dilakukan pada tabel yang telah kita buat tetapi sebelumnya Anda harus mempertimbangkan poin berikut untuk bergabung.

Beberapa hal yang perlu diperhatikan di Joins:

  • Hanya Gabungan Kesetaraan yang diperbolehkan Dalam Gabungan
  • Lebih dari dua tabel dapat digabungkan dalam kueri yang sama
  • LEFT, RIGHT, FULL OUTER bergabung ada untuk memberikan kontrol lebih besar atas Klausa ON yang tidak ada kecocokannya
  • Gabungan tidak bersifat Komutatif
  • Gabungan adalah asosiasi kiri terlepas dari apakah gabungan tersebut LEFT atau RIGHT

Jenis gabungan yang berbeda

Gabungan terdiri dari 4 jenis, ini

  • Bergabung batin
  • Gabung luar kiri
  • Gabung Luar Kanan
  • Gabung Luar Penuh

Gabung Batin:

Record yang umum untuk kedua tabel akan diambil oleh Inner Join ini.

Dari tangkapan layar di atas, kita dapat mengamati yang berikut ini

  1. Di sini kita melakukan query join menggunakan kata kunci JOIN antara tabel sample_joins dan sample_joins1 dengan kondisi yang cocok sebagai (c.Id = o.Id).
  2. Output yang menampilkan catatan umum yang ada di kedua tabel dengan memeriksa kondisi yang disebutkan dalam kueri

Pertanyaan:

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

Gabungan Luar Kiri:

  • Bahasa kueri sarang LEFT OUTER JOIN mengembalikan semua baris dari tabel kiri meskipun tidak ada kecocokan di tabel kanan
  • Jika Klausa ON cocok dengan catatan nol di tabel kanan, gabungan masih mengembalikan catatan di hasil dengan NULL di setiap kolom dari tabel kanan

Dari tangkapan layar di atas, kita dapat mengamati yang berikut ini

  1. Di sini kita melakukan query join menggunakan kata kunci "LEFT OUTER JOIN" antara tabel sample_joins dan sample_joins1 dengan kondisi yang cocok sebagai (c.Id = o.Id).

    Misalnya di sini kita menggunakan id karyawan sebagai referensi, ini memeriksa apakah id umum di kanan dan kiri tabel atau tidak. Ini bertindak sebagai kondisi yang cocok.

  2. Output yang menampilkan catatan umum yang ada di kedua tabel dengan memeriksa kondisi yang disebutkan dalam kueri.

    Nilai NULL pada output di atas adalah kolom tanpa nilai dari tabel Kanan yaitu sample_joins1

Pertanyaan:

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

Gabung luar kanan:

  • Bahasa kueri sarang RIGHT OUTER JOIN mengembalikan semua baris dari tabel Kanan meskipun tidak ada kecocokan di tabel kiri
  • Jika Klausa ON cocok dengan catatan nol di tabel kiri, gabungan masih mengembalikan catatan di hasil dengan NULL di setiap kolom dari tabel kiri
  • RIGHT join selalu mengembalikan rekaman dari tabel Kanan dan rekaman cocok dari tabel kiri. Jika tabel kiri tidak memiliki nilai yang sesuai dengan kolom, itu akan mengembalikan nilai NULL di tempat itu.

Dari tangkapan layar di atas, kita dapat mengamati yang berikut ini

  1. Di sini kita melakukan query join menggunakan kata kunci "RIGHT OUTER JOIN" antara tabel sample_joins dan sample_joins1 dengan kondisi yang cocok sebagai (c.Id = o.Id).
  2. Output yang menampilkan catatan umum yang ada di kedua tabel dengan memeriksa kondisi yang disebutkan dalam kueri

Pertanyaan :

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

Gabungan luar penuh:

Ini menggabungkan rekaman dari tabel sample_joins dan sample_joins1 berdasarkan pada Kondisi GABUNG yang diberikan dalam kueri.

Ini mengembalikan semua catatan dari kedua tabel dan mengisi Nilai NULL untuk kolom yang hilang nilai yang cocok di kedua sisi.

Dari tangkapan layar di atas, kita dapat mengamati hal-hal berikut:

  1. Di sini kita melakukan query join menggunakan kata kunci "FULL OUTER JOIN" antara tabel sample_joins dan sample_joins1 dengan kondisi yang cocok sebagai (c.Id = o.Id).
  2. Output menampilkan semua record yang ada di kedua tabel dengan memeriksa kondisi yang disebutkan dalam kueri. Nilai nol dalam keluaran di sini menunjukkan nilai yang hilang dari kolom kedua tabel.

Pertanyaan

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

Sub pertanyaan:

Kueri yang ada dalam Kueri dikenal sebagai sub kueri. Kueri utama akan bergantung pada nilai yang dikembalikan oleh subkueri.

Subkueri dapat diklasifikasikan menjadi dua jenis

  • Subkueri dalam klausa FROM
  • Subkueri di klausa WHERE

Kapan menggunakan:

  • Untuk mendapatkan nilai tertentu yang digabungkan dari dua nilai kolom dari tabel yang berbeda
  • Ketergantungan satu nilai tabel pada tabel lainnya
  • Pemeriksaan perbandingan satu nilai kolom dari tabel lain

Sintaksis:

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

Contoh:

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

Di sini t1 dan t2 adalah nama tabel. Yang berwarna adalah Subquery yang dilakukan pada tabel t1. Di sini a dan b adalah kolom yang ditambahkan dalam subkueri dan ditugaskan ke col1. Col1 adalah nilai kolom yang ada di tabel Utama. Kolom "col1" yang ada di subkueri ini sama dengan kueri tabel utama di kolom col1.

Menyematkan skrip khusus:

Hive memberikan kelayakan untuk menulis skrip khusus pengguna untuk kebutuhan klien. Pengguna dapat menulis peta mereka sendiri dan mengurangi skrip untuk kebutuhan. Ini disebut skrip Kustom Tersemat. Logika pengkodean ditentukan dalam skrip khusus dan kita dapat menggunakan skrip itu dalam waktu ETL.

Kapan memilih Embedded Scripts:

  • Dalam persyaratan khusus klien, pengembang harus menulis dan menerapkan skrip di Hive
  • Di mana fungsi bawaan Hive tidak akan berfungsi untuk persyaratan domain tertentu

Untuk ini di Hive, ia menggunakan klausa TRANSFORM untuk menyematkan skrip peta dan peredam.

Dalam skrip kustom yang disematkan ini, kita harus mengamati poin-poin berikut

  • Kolom akan diubah menjadi string dan dibatasi oleh TAB sebelum diberikan ke skrip pengguna
  • Keluaran standar dari skrip pengguna akan diperlakukan sebagai kolom string yang dipisahkan oleh TAB

Contoh Skrip Tersemat,

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;

Dari skrip di atas, kita dapat mengamati yang berikut ini

Ini hanya contoh skrip untuk memahami

  • pv_users adalah tabel pengguna yang memiliki bidang seperti userid dan tanggal seperti yang disebutkan dalam map_script
  • Skrip peredam ditentukan pada tanggal dan jumlah tabel pv_users