Upsert bukan merupakan fungsi ataupun perintah bawaan dari SQL/DuckDB, upsert merupakah kepanjangan dari Update Insert, atau bisa dibilang bila sesuai dengan kriteria tertentu data di update, selain itu di insert.
Upsert tidak terbatas pada DuckDB, tetapi bisa digunakan di semua database (DBMS). Yang perlu diingat dari upsert ini adalah logicnya
if match merge else insert
Contoh kasus yang umum menggunakan upsert adalah menggabungkan data harian dengan data utama, sinkronisasi data antara data customer dengan data harian customer.
Pada contoh kali ini kita memiliki data customer sebanyak 10 juta data, dan data harian (delta) ada sebanyak 5 juta. Data harian disini bisa merupakan data customer yang melakukan pengkinian data, atapun nasabah yang melakukan pembelian di hari kemarin
-rw-r--r--@ 1 tommy staff 554M Aug 3 20:32 customers-20260803.csv -rw-r--r--@ 1 tommy staff 1.1G Aug 1 16:33 customers.csv # total baris/row 5000006 customers-20260803.csv 10000001 customers.csv 15000007 total
Logik yang akan kita gunakan adalah update semua data customer yang ada di customer.csv dengan data customer dari data harian (customers-20260803.csv) bila customer_id tidak sama, maka insert data tersebut ke customer.
Buat table penampung untuk kedua file tersebut, table customers untuk customers.csv dan customers_staging untuk customers-20260803.csv
CREATE TABLE customers AS SELECT * FROM read_csv('/Users/tommy/git/duckdb/generator/customers.csv'); CREATE TABLE customers_staging AS SELECT * FROM read_csv('/Users/tommy/git/duckdb/generator/customers-20260803.csv');
kita membuat table di DuckDB untuk memudahkan proses merger data, karena bila memproses data CSV itu sendiri akan butuh waktu lama, fungsi dari read_csv() sebagai informasi kepada duckdb bahwa kita akan me-load file CSV, biasanya bila file yang diload menggunakan ekstensi .csv tidak perlu memanggil fungsi tersebut, contohnya
create table customers_staging as SELECT * from '/Users/tommy/git/duckdb/generator/customers-20260803.csv'; # atau create table customers_staging as from '/Users/tommy/git/duckdb/generator/customers-20260803.csv';
tetapi buat saya sudah menjadi kebiasaan untuk memanggil fungsi tersebut dan membuat query lebih mudah dicerna.
Lihat nama kolom yang ada di table customers dan customers_staging
select * from customers limit 10; ┌──────────────┬──────────────────────┬────────────┬───────────┬────────────────────────────┬─────────────────────┬───────────────┬───────────┬─────────────┬────────────┐ │ customer_id │ nik │ first_name │ last_name │ email │ occupation │ annual_income │ aml_score │ risk_rating │ kyc_status │ ├──────────────┼──────────────────────┼────────────┼───────────┼────────────────────────────┼─────────────────────┼───────────────┼───────────┼─────────────┼────────────┤ │ CUST00000001 │ 86426737233752480063 │ Dewi │ Santoso │ Dewi.Santoso1@example.com │ Business Owner │ 507363783 │ 36 │ MEDIUM │ VERIFIED │ │ CUST00000002 │ 4014692691920971457 │ Siti │ Lestari │ Siti.Lestari2@example.com │ Teacher │ 990874310 │ 52 │ MEDIUM │ VERIFIED │ │ CUST00000003 │ 5039961615518443433 │ Budi │ Kusuma │ Budi.Kusuma3@example.com │ Government Employee │ 927786345 │ 27 │ LOW │ VERIFIED │ │ CUST00000004 │ 4902226749561653363 │ Budi │ Nugroho │ Budi.Nugroho4@example.com │ Business Owner │ 771198382 │ 36 │ MEDIUM │ VERIFIED │ │ CUST00000005 │ 12283938685504891528 │ Badi │ Hidayat │ Badi.Hidayat5@example.com │ Farmer │ 732865651 │ 33 │ MEDIUM │ VERIFIED │ │ CUST00000006 │ 57803678760381626269 │ Putri │ Wijaya │ Putri.Wijaya6@example.com │ Farmer │ 158002625 │ 5 │ LOW │ VERIFIED │ │ CUST00000007 │ 32031525386391614419 │ Rizki │ Hidayat │ Rizki.Hidayat7@example.com │ Government Employee │ 639381061 │ 46 │ MEDIUM │ VERIFIED │ │ CUST00000008 │ 890138177540624232 │ Guni │ Kusuma │ Guni.Kusuma8@example.com │ Student │ 121495017 │ 3 │ LOW │ VERIFIED │ │ CUST00000009 │ 87859418700110855694 │ Fajar │ Wijaya │ Fajar.Wijaya9@example.com │ Farmer │ 169610103 │ 64 │ MEDIUM │ VERIFIED │ │ CUST00000010 │ 91679143740333945897 │ Dani │ Hidayat │ Dani.Hidayat10@example.com │ Doctor │ 883746027 │ 39 │ MEDIUM │ VERIFIED │ └──────────────┴──────────────────────┴────────────┴───────────┴────────────────────────────┴─────────────────────┴───────────────┴───────────┴─────────────┴────────────┘
Cek Data Tabel
Check data
SELECT MAX(customer_id) FROM customer; // CUST10000000 SELECT MAX(customer_id) FROM customers_staging; // CUST10000006
dari hasil diatas maksimum customer_id yang ada di customers_staging lebih besar dibandingkan customers, jadi nanti bakalan ada proses insert.
Hitung jumlah data di tabel
SELECT COUNT(*) FROM customers; // hasilnya 10000000 SELECT COUNT(*) FROM customers_staging; // hasilnya 5000006
Cek data customer yang tidak ada di customer_staging, begitu juga sebaliknya
SELECT COUNT(target.customer_id) FROM customers_staging target WHERE NOT EXISTS ( SELECT 1 FROM customers SOURCE WHERE SOURCE.customer_id = target.customer_id ); // hasilnya 6 SELECT COUNT(SOURCE.customer_id) FROM customers SOURCE WHERE NOT EXISTS ( SELECT 1 FROM customers_staging target WHERE SOURCE.customer_id = target.customer_id ); // hasilnya 50000000
cukup menjalankan salah satu perintah diatas harusnya sudah terlihat perbedaan dari datanya, di table customers_staging ada 5000006 (lima juta enam data), begitu dibandingin dengan tabel customer yang punya customer_id yang sama 5,000,000, berarti ada 6 customer_id yang belum ada (baru) di customer.
sebelum kita melakukan upsert, hal yang perlu kita ketahui adalah primary key dari data customer tersebut dan harus unik di setiap data. Dari data diatas primary key (PK) adalah customer_id, ini karena aplikasi ecommerce, sumber dari file ini menggunakan customer_id sebagai PK. Ada baiknya disini kita harus benar-benar paham tiap kolom yang ada, tipe data dan panjang (lenght) agar memudahkan untuk pembuatan upsert.
MERGE INTO customers AS target USING customers_staging AS SOURCE ON target.customer_id = SOURCE.customer_id WHEN MATCHED THEN UPDATE SET nik = SOURCE.nik, first_name = SOURCE.first_name, last_name = SOURCE.last_name, email = SOURCE.email, occupation = SOURCE.occupation, annual_income = SOURCE.annual_income, aml_score = SOURCE.aml_score, risk_rating = SOURCE.risk_rating, kyc_status = SOURCE.kyc_status WHEN NOT MATCHED THEN INSERT (customer_id,nik,first_name,last_name,email,occupation,annual_income,aml_score,risk_rating,kyc_status) VALUES ( SOURCE.customer_id, SOURCE.nik, SOURCE.first_name, SOURCE.last_name, SOURCE.email, SOURCE.occupation, SOURCE.annual_income, SOURCE.aml_score, SOURCE.risk_rating, SOURCE.kyc_status );
pada contoh diatas, bila target.customer_id = source.customer_id maka akan menjalankan WHEN MATCHED THEN, bila tidak menjalankan bagian WHEN NOT MATCHED THEN.
Di system yang saya gunakan, butuh 4 detik untuk menjalankan upsert tersebut
Run Time (s): real 4.610 user 3.085370 sys 1.856091
bila dilihat jumlah baris customers 10000006
memory D select count(*) from customers; ┌──────────────┐ │ count_star() │ ├──────────────┤ │ 10000006 │ └──────────────┘
prosesnya sangat cepat karena semua table di load ke memory, dan memory yang tersebut cukup untuk menampung data table tersebut.
Untuk ukuran data yang lebih besar dari memory, ada beberapa cara yang bisa digunakan:
1. Hapus semua data yang match antara tabel customer dan customer_staging, baru insert
2. Hanya pilih kolom yang akan di update, contoh nik dan annual_income
3. Gunakan SSD ataupun simpan data temporary di SSD (SET temp_directory = '/data/ssd/10TB';)
Terakhir simpan file tersebut ke CSV
COPY (SELECT * FROM customers) TO '/Users/tommy/git/duckdb/generator/customers-full.csv' (FORMAT csv);