Tutorial Hive Join & SubQuery dengan Contoh

Oleh: David Carter David Carter
Hours Updated

Bergabunglah dengan kueri

Kueri gabungan dapat dijalankan pada dua tabel yang ada di Hive. Untuk memahami, Bergabunglah Concepts jelasnya di sini kita 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 Kolom nama ID, Nama, Umur, alamat dan gaji karyawan

Bergabunglah dengan Kueri

Langkah 2) Memuat dan Menampilkan Data

Bergabunglah dengan Kueri

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

Bergabunglah dengan Kueri

Dari gambar di atas, kita dapat mengamati hal berikut

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

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

Beberapa hal yang perlu diperhatikan dalam Bergabung:

  • Hanya Gabung Kesetaraan yang diperbolehkan dalam Gabungan
  • Lebih dari dua tabel dapat digabungkan dalam kueri yang sama
  • Gabungan LEFT, RIGHT, FULL OUTER ada untuk memberikan kontrol lebih besar terhadap Klausa ON yang tidak ada kecocokannya
  • Gabungan tidak bersifat komutatif
  • Gabungan bersifat asosiatif kiri terlepas dari apakah gabungan tersebut KIRI atau KANAN

Jenis gabungan yang berbeda

Gabungan ada 4 jenis, yaitu

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

Gabung Dalam:

Catatan yang umum untuk kedua tabel akan diambil oleh Gabungan Dalam ini.

Gabung Batin

Dari gambar di atas, kita dapat mengamati hal berikut

  1. Disini 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 menampilkan catatan umum yang ada di kedua tabel dengan memeriksa kondisi yang disebutkan dalam kueri

Query:

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

Gabung 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 nol catatan di tabel kanan, gabungan masih mengembalikan catatan dalam hasil dengan NULL di setiap kolom dari tabel kanan

Gabung Luar Kiri

Dari gambar di atas, kita dapat mengamati hal berikut

  1. Disini kita melakukan query join menggunakan kata kunci “LEFT OUTER JOIN” antara tabel sample_joins dan sample_joins1 dengan kondisi pencocokan sebagai (c.Id= o.Id).Misalnya di sini kita menggunakan id karyawan sebagai referensi, ini memeriksa apakah id umum di tabel kanan dan kiri atau tidak. Ini bertindak sebagai kondisi yang cocok.
  2. Output menampilkan record umum yang ada di kedua tabel dengan memeriksa kondisi yang disebutkan dalam query. Nilai NULL pada output di atas adalah kolom tanpa nilai dari tabel Kanan yaitu sample_joins1

Query:

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 nol catatan di tabel kiri, gabungan masih mengembalikan catatan dalam hasil dengan NULL di setiap kolom dari tabel kiri
  • Gabungan KANAN selalu mengembalikan rekaman dari tabel Kanan dan rekaman yang cocok dari tabel kiri. Jika tabel sebelah kiri tidak memiliki nilai yang sesuai dengan kolomnya, maka tabel tersebut akan mengembalikan nilai NULL di tempat tersebut.

Gabung Luar Kanan

Dari gambar di atas, kita dapat mengamati hal berikut

  1. Disini 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 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 catatan tabel sample_joins dan sample_joins1 berdasarkan Kondisi GABUNG yang diberikan dalam kueri.

Ia mengembalikan semua catatan dari kedua tabel dan mengisi Nilai NULL untuk kolom yang tidak memiliki nilai yang cocok di kedua sisi.

Gabungan Luar Penuh

Dari gambar layar di atas kita dapat mengamati hal berikut:

  1. Disini 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. Outputnya menampilkan semua record yang ada di kedua tabel dengan memeriksa kondisi yang disebutkan dalam query. Nilai nol pada 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)

ARTIKEL TERKAIT

Sub kueri

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

Subkueri dapat diklasifikasikan menjadi dua jenis

  • Subkueri dalam klausa FROM
  • Subkueri dalam klausa WHERE

Kapan harus menggunakan:

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

sintaks:

Subquery in FROM clause
SELECT <column names 1, 2…n>From (SubQuery) <TableName_Main >
Subquery in WHERE clause
SELECT <column names 1, 2…n> From<TableName_Main>WHERE 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 ditetapkan ke col1. Col1 adalah nilai kolom yang ada di tabel Utama. Kolom “col1” yang ada di subkueri ini setara dengan kueri tabel utama di kolom col1.

Menyematkan skrip khusus

Sarang lebah memberikan kelayakan penulisan skrip khusus pengguna untuk kebutuhan klien. Pengguna dapat menulis peta mereka sendiri dan mengurangi skrip untuk persyaratan. Ini disebut skrip Kustom Tertanam. Logika pengkodean ditentukan dalam skrip khusus dan kita dapat menggunakan skrip tersebut dalam waktu ETL.

Kapan memilih Skrip Tersemat:

  • Dalam persyaratan khusus klien, pengembang harus menulis dan menyebarkan skrip di Hive
  • Dimana 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 Embedded ini, kita harus memperhatikan poin-poin berikut

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

Contoh Skrip Tersemat,

FROM (
	FROM pv_users
	MAP pv_users.userid, pv_users.date
	USING 'map_script'
	AS dt, uid
	CLUSTER BY dt) map_output

INSERT OVERWRITE TABLE pv_users_reduced
	REDUCE map_output.dt, map_output.uid
	USING 'reduce_script'
	AS date, count;

Dari skrip di atas, kita dapat mengamati hal berikut

Ini hanya contoh skrip untuk pemahaman

  • 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

Anda mungkin ingin: