Mengubah data dengan operator gabungan dan operator himpunan

Selesai

Alur kerja rekayasa data sering memerlukan penggandaan informasi dari beberapa himpunan data. Gabungan menggabungkan tabel secara horizontal dengan mencocokkan baris pada kolom terkait, sementara operator yang diatur menggabungkan tabel secara vertikal dengan menambahkan, bersinggungan, atau mengecualikan baris. Memahami kapan menggunakan setiap pendekatan membantu Anda membangun alur transformasi yang efisien.

Di unit ini, Anda mempelajari cara menggabungkan tabel berdasarkan hubungan dan menerapkan operator set untuk menggabungkan himpunan data dengan skema yang cocok.

Menggabungkan tabel dengan join

Penggabungan (Join) mengintegrasikan baris dari dua tabel berdasarkan kolom yang terkait. Jenis gabungan menentukan baris mana yang muncul dalam hasil dan bagaimana baris yang tidak cocok ditangani.

Memahami jenis gabungan

Setiap jenis gabungan melayani tujuan tertentu dalam transformasi data:

Jenis join	Description	Skenario penggunaan
INNER	Mengembalikan hanya baris yang cocok dari kedua tabel	Menautkan pesanan dengan pelanggan yang valid
LEFT	Mengembalikan semua baris tabel kiri; mengisi NULL untuk tabel yang kanan jika tidak ada kecocokan	Sertakan semua pelanggan, bahkan tanpa pesanan
RIGHT	Mengembalikan semua baris tabel kanan; menambahkan NULL untuk tabel kiri jika tidak ada kecocokan	Sertakan semua departemen, bahkan tanpa karyawan
FULL	Mengembalikan semua baris dari kedua tabel; menambahkan nilai NULL untuk non-kecocokan	Menggabungkan himpunan data untuk menemukan celah di kedua sisi
SEMI	Mengembalikan baris tabel kiri yang memiliki kecocokan di tabel kanan	Memfilter pelanggan yang melakukan pesanan
ANTI	Mengembalikan baris tabel kiri yang tidak memiliki kecocokan di tabel kanan	Temukan pelanggan yang tidak pernah melakukan pesanan
CROSS	Mengembalikan hasil perkalian Kartesius dari semua baris	Hasilkan semua kombinasi yang mungkin

Menggabungkan tabel dengan SQL

Klausa JOIN menggabungkan tabel berdasarkan kondisi yang ditentukan dengan ON atau USING. Contoh berikut menggunakan tabel karyawan dan departemen:

CREATE TEMP VIEW employee(id, name, deptno) AS
    VALUES (105, 'Chloe', 5),
           (103, 'Paul',  3),
           (101, 'John',  1),
           (102, 'Lisa',  2),
           (104, 'Evan',  4),
           (106, 'Amy',   6);

CREATE TEMP VIEW department(deptno, deptname) AS
    VALUES (3, 'Engineering'),
           (2, 'Sales'),
           (1, 'Marketing');

Untuk mencocokkan karyawan dengan departemen mereka, gunakan inner join:

-- Inner join: only employees with matching departments
SELECT e.id, e.name, e.deptno, d.deptname
FROM employee e
INNER JOIN department d ON e.deptno = d.deptno;

Gabungan dalam hanya mengembalikan tiga baris—karyawan yang nomor departemennya ada di tabel departemen. Karyawan di departemen 4, 5, dan 6 tidak muncul karena departemen tersebut tidak berada di tabel departemen.

Ketika Anda perlu menyertakan semua karyawan terlepas dari kecocokan departemen, gunakan "left join":

-- Left join: all employees, NULL for missing departments
SELECT e.id, e.name, e.deptno, d.deptname
FROM employee e
LEFT JOIN department d ON e.deptno = d.deptno;

Ini mengembalikan keenam karyawan. Entitas tanpa pencocokan departemen akan ditampilkan sebagai NULL di kolom deptname.

Menggabungkan DataFrames dengan PySpark

Metode PySpark join() menyediakan fungsionalitas yang sama. Parameter how menentukan jenis gabungan:

df_employee = spark.table("employee")
df_department = spark.table("department")

# Inner join
df_inner = df_employee.join(
    df_department,
    on=df_employee.deptno == df_department.deptno,
    how="inner"
)
display(df_inner)

Untuk gabungan kiri, kanan, atau gabungan luar penuh, ubah parameter how.

# Left join: keep all employees
df_left = df_employee.join(
    df_department,
    on="deptno",
    how="left"
)

# Full outer join: keep all rows from both tables
df_full = df_employee.join(
    df_department,
    on="deptno",
    how="full"
)

Saat kolom gabungan memiliki nama yang sama di kedua DataFrames, berikan nama kolom sebagai string. Pendekatan ini menghindari kolom duplikat dalam hasilnya.

Memfilter data dengan gabungan semi dan anti

Semi dan anti join memfilter data berdasarkan keberadaan di tabel lain. Semi join mengembalikan baris dari tabel kiri yang sesuai, tanpa menyertakan kolom dari tabel kanan:

-- Find employees who belong to listed departments
SELECT *
FROM employee
LEFT SEMI JOIN department ON employee.deptno = department.deptno;

Gabungan anti mengembalikan kebalikannya—baris tanpa kecocokan:

-- Find employees in departments not listed
SELECT *
FROM employee
LEFT ANTI JOIN department ON employee.deptno = department.deptno;

Di PySpark, gunakan how="semi" atau how="anti":

# Employees with matching departments
df_semi = df_employee.join(df_department, on="deptno", how="semi")

# Employees without matching departments
df_anti = df_employee.join(df_department, on="deptno", how="anti")

Menggabungkan baris dengan operator set

Operator himpunan bekerja secara vertikal, menggabungkan baris dari kueri dengan struktur kolom yang sama. Tidak seperti join, operator set tidak bergantung pada kondisi—operator tersebut menumpuk atau membandingkan seluruh kumpulan hasil.

Memahami operator himpunan

Azure Databricks mendukung tiga operator set:

Operator	Description	Perilaku
UNION	Menggabungkan baris dari kedua kueri	Menghapus duplikat secara default; gunakan ALL untuk menyimpannya
INTERSECT	Mengembalikan baris yang terdapat di kedua query	Hanya baris yang cocok yang muncul
EXCEPT	Mengembalikan baris dari kueri pertama yang tidak ada dalam kueri kedua	Menghapus duplikat secara default

Kedua kueri harus memiliki jumlah kolom yang sama dengan jenis data yang kompatibel.

Menggabungkan himpunan data dengan UNION

UNION menambahkan baris dari satu kueri ke kueri lainnya. Secara default, ini menghapus duplikat:

-- Combine active and archived customers (no duplicates)
SELECT customer_id, name, email FROM active_customers
UNION
SELECT customer_id, name, email FROM archived_customers;

Untuk mempertahankan semua baris termasuk duplikat, gunakan UNION ALL:

-- Combine all rows, keeping duplicates
SELECT customer_id, name, email FROM active_customers
UNION ALL
SELECT customer_id, name, email FROM archived_customers;

Gunakan UNION ALL ketika Anda tahu tidak ada duplikat atau ketika duplikat bermakna. Performanya lebih baik karena melewati langkah deduplikasi.

Di PySpark, gunakan union() untuk menggabungkan DataFrames. Perhatikan bahwa perilaku PySpark union() seperti UNION ALL—menyimpan duplikat:

df_active = spark.table("active_customers")
df_archived = spark.table("archived_customers")

# Combine both tables (keeps duplicates)
df_combined = df_active.union(df_archived)

# Remove duplicates if needed
df_distinct = df_active.union(df_archived).distinct()

Temukan baris yang sama dengan fungsi INTERSECT

INTERSECT mengembalikan hanya baris yang muncul di kedua kueri. Ini membantu mengidentifikasi data yang tumpang tindih:

-- Find customers in both active and archived tables
SELECT customer_id FROM active_customers
INTERSECT
SELECT customer_id FROM archived_customers;

Kueri ini mengidentifikasi pelanggan yang entah bagaimana muncul di kedua tabel—berguna untuk pemeriksaan kualitas data atau mengidentifikasi rekaman yang memerlukan rekonsiliasi.

Mengecualikan baris dengan EXCEPT

EXCEPT mengembalikan baris dari kueri pertama yang tidak ditemukan di kueri kedua. Ini membantu mengidentifikasi kesenjangan atau rekaman unik:

-- Find active customers not in the archived table
SELECT customer_id FROM active_customers
EXCEPT
SELECT customer_id FROM archived_customers;

Anda juga dapat menggunakan MINUS sebagai sinonim untuk EXCEPT:

-- Same result using MINUS syntax
SELECT customer_id FROM active_customers
MINUS
SELECT customer_id FROM archived_customers;

Seperti UNION, baik INTERSECT dan EXCEPT hapus duplikat secara default. Tambahkan ALL untuk mempertahankan duplikat saat diperlukan.

Petunjuk / Saran

Saat mengatur operasi berantai, ingatlah bahwa INTERSECT memiliki prioritas yang lebih tinggi daripada UNION dan EXCEPT. Gunakan tanda kurung untuk mengontrol urutan evaluasi.

Memilih antara gabungan dan mengatur operator

Pilihan antara join dan operator set tergantung pada tujuan transformasi Anda. Join menggabungkan kolom dari tabel terkait secara horizontal, sementara operator himpunan menggabungkan baris secara vertikal.

Gunakan gabungan saat Anda perlu:

• Memperkaya rekaman dengan menambahkan kolom dari tabel terkait
• Mencocokkan rekaman berdasarkan hubungan kunci
• Memfilter data berdasarkan keberadaan dalam tabel lain (gabungan semi/anti)

Gunakan operator set ketika Anda memerlukan:

• Menambahkan rekaman dari beberapa sumber dengan skema yang identik
• Menemukan rekaman umum di seluruh himpunan data
• Mengidentifikasi rekaman yang unik untuk satu himpunan data

Kedua pendekatan membentuk fondasi alur transformasi data yang efektif. Di unit berikutnya, Anda menjelajahi cara memuat data yang diubah menjadi tabel target dalam Unity Catalog.