Kapan Masuk akal untuk Menggunakan Google BigQuery?

Selama karir saya, saya harus menulis kueri analitik yang kompleks untuk berbagai jenis laporan dan bagan. Paling sering, itu adalah beberapa bagan yang menampilkan data yang dikumpulkan berdasarkan tanggal, minggu, kuartal, dan sebagainya. Biasanya, laporan semacam itu dibuat untuk membantu klien mengidentifikasi tren dan menggambarkan bagaimana kinerja bisnis mereka di tingkat tinggi. Tetapi apa yang terjadi ketika para ilmuwan dan insinyur data perlu membuat laporan yang jauh lebih luas, berdasarkan kumpulan data yang besar?

Jika laporan didasarkan pada kumpulan data kecil, tugas dapat diselesaikan dengan menulis kueri SQL di bawah basis data relasional. Pada langkah ini, penting untuk mengetahui dasar-dasar penulisan query dan bagaimana membuatnya lebih cepat dan efisien. Namun, terkadang laporan bergantung pada kumpulan data yang lebih besar (misalnya jutaan dan lebih banyak baris dalam tabel), tetapi laporan tidak bergantung pada variabel input (parameter), atau Anda mungkin menemukan jumlah nilai yang cukup kecil. Dalam skenario seperti itu, kueri SQL bisa lambat, sehingga tidak akan optimal bagi pengguna untuk menunggu hingga kueri dieksekusi. Praktik yang paling umum dalam kasus seperti itu adalah menjalankan kueri sebelumnya–sebelum klien meminta laporan.

Selain itu, ini memerlukan penerapan beberapa fungsi caching, sehingga klien dapat mengambil data dari cache alih-alih menjalankan kueri secara real-time. Pendekatan ini bekerja dengan sempurna, asalkan Anda tidak perlu menampilkan data waktu nyata. Itu dapat menampilkan data yang dihitung satu jam atau bahkan sehari sebelumnya. Jadi, laporan/grafik sebenarnya ditampilkan menggunakan data cache, bukan berdasarkan data waktu nyata.

Beralih ke Google BigQuery

Ketika saya sedang mengerjakan proyek analitik di industri farmasi, saya membutuhkan bagan yang menggunakan kode pos dan nama obat sebagai parameter input. Saya juga perlu menunjukkan beberapa perbandingan antara obat-obatan di wilayah tertentu di Amerika Serikat.

Kueri analitik sangat kompleks dan akhirnya berjalan sekitar 50 menit di server Postgres kami (CPU quad-core dengan RAM 16 GB). Saya tidak dapat menjalankannya sebelumnya dan menyimpan hasilnya, karena kueri menggunakan kode pos dan obat-obatan sebagai parameter input, jadi ada ribuan kombinasi, dan tidak mungkin untuk memprediksi klien mana yang akan dipilih.

Bahkan jika saya ingin mencoba mengeksekusi semua kombinasi parameter input, database saya kemungkinan besar akan crash. Jadi sudah waktunya untuk memilih pendekatan yang berbeda dan memilih beberapa solusi yang mudah digunakan. Bagan itu penting bagi klien, namun, klien tidak siap berkomitmen untuk membuat perubahan besar dalam arsitektur atau bermigrasi ke DB lain sepenuhnya.

Pada proyek khusus itu, kami mencoba beberapa pendekatan berbeda:

• Penskalaan vertikal server (menambahkan RAM dan CPU ke server Postgres)
• Menggunakan DB alternatif seperti Amazon Redshift dan lainnya.
• Kami juga meneliti solusi NoSQL, tetapi kebanyakan dari mereka cukup kompleks dan memerlukan banyak perubahan dalam arsitektur, banyak di antaranya akan terlalu besar untuk klien.

Akhirnya, kami mencoba Google BigQuery. Itu memenuhi harapan kami dan memungkinkan kami untuk menyelesaikan pekerjaan tanpa membuat perubahan besar yang tidak akan disetujui oleh klien. Tapi apa itu Google BigQuery dan bagaimana kinerjanya?

BigQuery adalah layanan web berbasis REST yang memungkinkan Anda menjalankan kueri berbasis SQL analitik yang kompleks di bawah kumpulan data yang besar. Setelah kami mengunggah data ke BigQuery dan menjalankan kueri yang sama seperti yang kami lakukan di Postgres (sintaksnya sangat mirip), kueri kami berjalan lebih cepat dan membutuhkan waktu sekitar satu menit untuk diselesaikan. Pada akhirnya, kami mendapatkan peningkatan kinerja 50x hanya dengan menggunakan layanan yang berbeda. Perlu dicatat bahwa DB lain tidak memberikan peningkatan kinerja yang sama, dan mari kita bermurah hati dan hanya mengatakan mereka bahkan tidak dekat. Sejujurnya, saya sangat terkesan dengan peningkatan kinerja yang diberikan oleh BigQuery, karena angkanya lebih baik dari yang kami harapkan.

Meskipun demikian, saya tidak akan mengiklankan BigQuery sebagai solusi database terbaik di dunia. Meskipun bekerja dengan baik untuk proyek kami, itu masih memiliki banyak batasan, seperti jumlah pembaruan yang terbatas dalam tabel per hari, batasan ukuran data per permintaan, dan lainnya. Anda perlu memahami bahwa BigQuery tidak dapat digunakan untuk menggantikan database relasional, dan berorientasi pada menjalankan kueri analitik, bukan untuk operasi dan kueri CRUD sederhana.

Pada artikel ini, saya akan mencoba membandingkan menggunakan Postgres (database relasional favorit saya) dan BigQuery untuk skenario kasus penggunaan di dunia nyata. Selain itu, saya akan memberikan beberapa saran, yaitu pendapat saya tentang kapan sebaiknya menggunakan BigQuery.

Contoh data

Untuk membandingkan Postgres dan Google BigQuery, saya mengambil informasi demografis publik untuk setiap negara yang dikelompokkan berdasarkan negara, usia, tahun, dan jenis kelamin (Anda dapat mengunduh data yang sama dari tautan ini).

Saya menambahkan data ke empat tabel:

1. populations
2. locations
3. age_groups
4. populations_aggregated

Tabel terakhir hanyalah kumpulan data dari tiga tabel sebelumnya. Berikut adalah skema DB:

Tabel populations yang saya dapatkan berisi lebih dari 6,9 juta baris. Itu tidak terlalu banyak, tapi itu sudah cukup untuk tes saya.

Berdasarkan data sampel, saya mencoba membuat kueri yang dapat digunakan untuk membuat laporan dan bagan analitis kehidupan nyata. Jadi saya menyiapkan pertanyaan untuk laporan berikutnya:

1. Populasi di AS dikumpulkan berdasarkan tahun.
2. Populasi tahun 2019 untuk semua negara dimulai dari negara terbesar.
3. Lima negara "tertua" teratas setiap tahun. "Tertua" menunjukkan negara-negara di mana persentase penduduk di atas 60 tahun terhadap jumlah total penduduk adalah yang tertinggi. Kueri harus memberikan lima hasil per setiap tahun.
4. Lima negara teratas dikumpulkan berdasarkan tahun, di mana perbedaan antara populasi pria dan wanita adalah yang terbesar.
5. Dapatkan median (rata-rata) usia per negara untuk setiap tahun mulai dari negara "tertua" hingga "termuda".
6. Temukan lima negara "sekarat" teratas setiap tahun. "Sekarat" berarti negara-negara di mana populasinya menurun (depopulasi adalah yang tertinggi).

Pertanyaan #1, #2, dan #6 cukup mudah dan lugas, tetapi pertanyaan #3, #4, dan #5 tidak begitu mudah untuk ditulis–setidaknya bagi saya. Harap dicatat bahwa saya seorang insinyur back-end dan menulis kueri SQL yang kompleks bukanlah keahlian saya, jadi seseorang dengan lebih banyak pengalaman SQL mungkin dapat membuat kueri yang lebih cerdas. Namun, saat ini kami perlu memeriksa bagaimana Postgres dan BigQuery memproses kueri yang sama dengan data yang sama.

Saya membuat total 24 kueri:

• 6 untuk Postgres DB, yang menggunakan tabel non agregat ( populations , locations , age_groups )
• 6 untuk Postgres DB, yang menggunakan tabel populations_aggregated
• 6+6 kueri untuk BigQuery yang menggunakan tabel agregat dan non-agregat.

Izinkan saya membagikan kueri BigQuery #1 dan #5 untuk data gabungan sehingga Anda dapat memahami kerumitan kueri #5 sederhana (#1) dan kompleks.

Populasi di AS diagregasi menurut kueri tahun:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

Kueri untuk usia rata-rata per negara per setiap tahun diurutkan dari terlama ke termuda:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Catatan: Anda dapat menemukan semua pertanyaan di repositori bitbucket saya (tautan ada di akhir artikel).

Hasil tes

Untuk menjalankan kueri, saya menggunakan dua server Postgres yang berbeda. Yang pertama memiliki 1 inti CPU dan RAM 4GB, didukung oleh drive SSD. Yang kedua memiliki 16 core CPU, 64GB RAM dan juga menggunakan drive SSD (server kedua memiliki 16x potensi CPU dan RAM).

Juga, perhatikan bahwa tidak ada beban pada database selama menjalankan tes. Saya membuatnya hanya untuk menjalankan kueri. Dalam situasi kehidupan nyata, kueri akan memakan waktu lebih lama, karena kueri lain mungkin berjalan pada waktu yang sama, dan selain itu, kueri yang berjalan secara paralel dapat mengunci tabel. Untuk memeriksa kecepatan kueri, saya menggunakan pgAdmin3 dan antarmuka web BigQuery.

Dalam pengujian saya, saya mendapatkan hasil ini:

Biarkan saya menunjukkan hasil tersebut dalam diagram batang untuk kueri #1 dan kueri #5.

Catatan: Database Postgres terletak di server yang berbasis di AS, dan saya berbasis di Eropa, jadi ada penundaan tambahan dalam pengiriman data Postgres.

Kinerja dan Kesimpulan BigQuery

Berdasarkan hasil yang saya dapatkan, saya membuat kesimpulan sebagai berikut:

• Dalam hal penskalaan Postgres secara vertikal, bahkan pada 16x kali, itu hanya memberikan 10-25% kinerja dalam menjalankan satu kueri. Dengan kata lain, server Postgres dengan hanya satu inti CPU dan RAM 4GB menjalankan kueri dengan waktu yang sangat mirip dengan waktu yang dibutuhkan untuk server dengan 16 inti CPU dan RAM 64GB. Tentu saja, server yang lebih besar dapat memproses kumpulan data yang jauh lebih besar, namun, ini tidak memberikan banyak peningkatan dalam waktu eksekusi kueri.
• Untuk Postgres bergabung dengan tabel kecil ( tabel locations memiliki sekitar 400 baris dan age_groups memiliki 100 baris) tidak menghasilkan perbedaan besar dibandingkan dengan menjalankan kueri di bawah data agregat yang terletak di satu tabel. Juga, saya menemukan bahwa untuk kueri yang berjalan satu hingga dua detik, kueri dengan gabungan dalam lebih cepat, tetapi untuk kueri yang berjalan lama, situasinya berbeda.
• Dalam situasi BigQuery dengan bergabung sama sekali berbeda. BigQuery tidak suka bergabung. Perbedaan waktu antara kueri, yang menggunakan data agregat dan non-agregat, cukup besar (untuk kueri #3 dan $5 sekitar dua detik). Artinya, untuk BigQuery, Anda dapat melakukan subkueri sebanyak yang Anda inginkan, tetapi untuk kinerja yang baik, kueri tersebut harus menggunakan satu tabel.
• Postgres lebih cepat untuk kueri yang menggunakan agregasi atau pemfilteran sederhana atau menggunakan kumpulan data kecil. Saya menemukan bahwa kueri yang membutuhkan waktu kurang dari lima detik di Postgres bekerja lebih lambat di BigQuery.
• Performa BigQuery jauh lebih baik untuk kueri yang berjalan lama. Saat perbedaan dalam ukuran kumpulan data meningkat, perbedaan waktu yang dibutuhkan untuk menyelesaikan kueri ini juga akan meningkat.

Kapan Masuk akal untuk Menggunakan BigQuery

Sekarang, mari kembali ke masalah inti yang dibahas dalam artikel ini: kapan sebaiknya Anda benar-benar menggunakan Google BigQuery? Berdasarkan kesimpulan saya, saya akan menyarankan penggunaan BigQuery jika kondisi berikut terpenuhi:

• Gunakan saat Anda memiliki kueri yang berjalan lebih dari lima detik dalam database relasional. Ide BigQuery adalah menjalankan kueri analitik yang kompleks, yang berarti tidak ada gunanya menjalankan kueri yang melakukan agregasi atau pemfilteran sederhana. BigQuery cocok untuk kueri "berat", kueri yang beroperasi menggunakan kumpulan data yang besar. Semakin besar set data, semakin besar kemungkinan Anda memperoleh performa dengan menggunakan BigQuery. Dataset yang saya gunakan hanya 330 MB (megabyte, bahkan tidak gigabyte).
• BigQuery tidak suka bergabung, jadi Anda harus menggabungkan data ke dalam satu tabel untuk mendapatkan waktu eksekusi yang lebih baik. BigQuery memungkinkan penyimpanan hasil kueri di tabel baru, jadi untuk membuat tabel gabungan baru, cukup unggah semua data Anda ke BigQuery, jalankan kueri yang akan menggabungkan semua data, dan simpan saja di tabel baru.
• BigQuery cocok untuk skenario di mana data tidak sering berubah dan Anda ingin menggunakan cache, karena memiliki cache bawaan. Apa artinya ini? Jika Anda menjalankan kueri yang sama dan data dalam tabel tidak diubah (diperbarui), BigQuery hanya akan menggunakan hasil yang di-cache dan tidak akan mencoba menjalankan kueri lagi. Selain itu, BigQuery tidak membebankan biaya untuk kueri yang di-cache. Catatan: Bahkan kueri yang di-cache membutuhkan 1-1,2 detik untuk mengembalikan hasil.
• Anda juga dapat menggunakan BigQuery saat ingin mengurangi beban pada database relasional Anda. Kueri analitik "berat" dan menggunakannya secara berlebihan di bawah basis data relasional dapat menyebabkan masalah kinerja. Jadi, Anda akhirnya bisa dipaksa untuk berpikir tentang penskalaan server Anda. Namun, dengan BigQuery, Anda dapat memindahkan kueri yang berjalan ini ke layanan pihak ketiga, sehingga tidak akan memengaruhi database relasional utama Anda.

Terakhir, beberapa kata lagi tentang penggunaan BigQuery di kehidupan nyata. Pada proyek dunia nyata kami, data untuk laporan berubah setiap minggu atau setiap bulan, sehingga kami dapat mengunggah data ke BigQuery secara manual. Namun, jika data Anda sering berubah, menyinkronkan data antara database relasional dan BigQuery mungkin tidak sesederhana itu, dan ini adalah peringatan yang perlu diingat.

Tautan

Anda dapat menemukan contoh data yang digunakan dalam artikel ini di sini, sedangkan kueri dan data dalam format CSV dapat diakses di sini.