Tutorial Hive Join & SubQuery dengan Contoh

Bergabunglah dengan kueri

Kueri penggabungan dapat dilakukan pada dua tabel yang ada di Sarang lebahUntuk memahami konsep join dengan jelas, kita akan 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, Usia, alamat, dan gaji karyawan. Tangkapan layar di bawah menunjukkan pernyataan CREATE TABLE dan konfirmasinya.

Langkah 2) Memuat dan menampilkan data. Tangkapan layar berikutnya menunjukkan perintah pemuatan diikuti oleh isi tabel.

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, kemudian memuat dan menampilkan datanya, seperti yang terlihat pada tangkapan layar di bawah ini.

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

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

Selanjutnya, kita akan melihat berbagai jenis join yang dapat dilakukan pada tabel yang telah kita buat. Sebelum itu, Anda harus mempertimbangkan poin-poin berikut tentang join.

Beberapa hal yang perlu diperhatikan dalam operasi join:

• Hanya penggabungan berdasarkan kesamaan yang diperbolehkan dalam penggabungan.
• Lebih dari dua tabel dapat digabungkan dalam kueri yang sama
• LEFT, RIGHT, dan FULL OUTER join ada untuk memberikan kontrol lebih besar atas klausa ON yang tidak memiliki kecocokan.
• Operasi join tidak bersifat komutatif.
• Gabungan bersifat asosiatif kiri terlepas dari apakah gabungan tersebut KIRI atau KANAN

Pembatasan kesamaan mencerminkan Hive seperti yang ada selama bertahun-tahun. Mulai dari Hive 2.2.0 dan seterusnya, ekspresi kompleks dalam klausa ON didukung (HIVE-15211), sehingga kondisi non-kesamaan diterima pada rilis terbaru. Pada rilis yang lebih lama, kondisinya harus berupa uji kesamaan, dengan hal lainnya dipindahkan ke klausa WHERE.

Jenis gabungan yang berbeda

Ada 4 jenis penggabungan (join). Jenis-jenis tersebut adalah:

• Bergabung batin
• Gabung luar kiri
• Gabungan kanan luar
• Gabung luar penuh

Setiap tipe ditunjukkan di bawah ini dengan menggunakan dua tabel yang sama, jadi satu-satunya hal yang berubah antara contoh-contoh tersebut adalah baris mana yang tidak cocok yang tetap ada.

Gabung Batin

Data yang sama untuk kedua tabel akan diambil melalui inner join ini. Hasil pada tangkapan layar di bawah ini hanya berisi pelanggan yang memiliki pesanan yang cocok.

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

1. Di sini kita melakukan query join menggunakan kata kunci JOIN antara tabel sample_joins dan sample_joins1, dengan kondisi pencocokan (c.Id = o.Id).
2. Output tersebut menampilkan catatan umum yang ada di kedua tabel, yang dipilih 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

• sarang QL LEFT OUTER JOIN mengembalikan semua baris dari tabel sebelah kiri meskipun tidak ada kecocokan di tabel sebelah kanan.
• Jika klausa ON cocok dengan nol catatan di tabel kanan, penggabungan (join) tetap akan mengembalikan catatan dalam hasil dengan nilai NULL di setiap kolom dari tabel kanan.

Tangkapan layar di bawah menunjukkan bahwa setiap pelanggan muncul, termasuk mereka yang tidak memiliki pesanan.

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

1. Di sini kita melakukan query join menggunakan kata kunci “LEFT OUTER JOIN” antara tabel sample_joins dan sample_joins1, dengan kondisi pencocokan (c.Id = o.Id). Misalnya, di sini kita menggunakan id karyawan sebagai referensi; query ini memeriksa apakah id tersebut sama untuk tabel kanan maupun tabel kiri. Ini bertindak sebagai kondisi pencocokan.
2. Output tersebut menampilkan catatan yang dipilih berdasarkan kondisi yang disebutkan dalam kueri. Nilai NULL pada output di atas adalah kolom yang tidak memiliki nilai dari tabel sebelah 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

• Perintah HiveQL 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, penggabungan (join) tetap mengembalikan catatan dalam hasil dengan nilai NULL di setiap kolom dari tabel kiri.
• RIGHT join selalu mengembalikan data dari tabel kanan dan data yang cocok dari tabel kiri. Jika tabel kiri tidak memiliki nilai yang sesuai dengan kolom tersebut, maka akan mengembalikan nilai NULL di tempat tersebut.

Tangkapan layar di bawah ini menunjukkan gambar cermin dari hasil sebelumnya: setiap pesanan muncul, baik yang cocok maupun tidak.

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

1. Di sini kita melakukan query join menggunakan kata kunci “RIGHT OUTER JOIN” antara tabel sample_joins dan sample_joins1, dengan kondisi pencocokan (c.Id = o.Id).
2. Output tersebut menampilkan catatan yang dipilih dengan memeriksa kondisi yang disebutkan dalam kueri.

Query:

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

Gabung Luar Penuh

Fungsi ini menggabungkan data dari kedua tabel sample_joins dan sample_joins1 berdasarkan kondisi JOIN yang diberikan dalam kueri.

Fungsi ini mengembalikan semua data dari kedua tabel dan mengisi nilai NULL untuk kolom yang nilai pasangannya hilang di kedua sisi, seperti yang ditunjukkan pada tangkapan layar di bawah ini.

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 pencocokan (c.Id = o.Id).
2. Output menampilkan semua data yang ada di kedua tabel, yang dipilih dengan memeriksa kondisi yang disebutkan dalam kueri. Nilai NULL dalam output ini menunjukkan nilai yang hilang dari kolom di kedua tabel.

Query:

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 kueri

Join menempatkan tabel berdampingan. Subquery melakukan sesuatu yang berbeda: ia menyusun satu query di dalam query lain sehingga query luar dapat bekerja dari hasil yang telah dihitung sebelumnya.

Sebuah query yang terdapat di dalam query lain dikenal sebagai subquery. Query utama akan bergantung pada nilai yang dikembalikan oleh subquery tersebut.

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 suatu tabel pada tabel lainnya.
• Perbandingan nilai suatu kolom dengan tabel lainnya.

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. Pernyataan dalam adalah subkueri 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 dalam subkueri ini setara dengan kueri tabel utama pada kolom col1.

Menyematkan skrip khusus

Jika subkueri mengubah bentuk data hanya dengan HiveQL, skrip yang disematkan akan meneruskan baris data ke kode yang ditulis di luar Hive.

Hive memungkinkan penulisan skrip khusus pengguna untuk kebutuhan klien. Pengguna dapat menulis skrip map dan reduce mereka sendiri untuk kebutuhan tersebut. Ini disebut skrip kustom tertanam. Logika pengkodean didefinisikan dalam skrip kustom, dan kita dapat menggunakan skrip tersebut pada saat ETL.

Kapan harus memilih skrip yang disematkan:

• Di mana persyaratan khusus klien mengharuskan pengembang untuk menulis dan menyebarkan skrip di Hive.
• Di mana fungsi bawaan Hive tidak akan berfungsi untuk kebutuhan domain tertentu.

Untuk itu, Hive menggunakan klausa TRANSFORM untuk menyematkan skrip map dan reducer.

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

• Kolom akan diubah menjadi string dan dipisahkan oleh TAB sebelum diberikan ke skrip pengguna.
• Output standar dari skrip pengguna akan diperlakukan sebagai kolom string yang dipisahkan oleh 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-hal berikut. Ini hanya contoh skrip untuk pemahaman.

• pv_users adalah tabel pengguna, yang memiliki kolom seperti userid dan tanggal seperti yang disebutkan dalam map_script.
• Skrip reducer didefinisikan berdasarkan tanggal dan jumlah data dalam tabel pv_users.

Pertanyaan Umum Demo Slot