เมื่อใดจึงควรใช้ Google BigQuery

ตลอดเส้นทางอาชีพของฉัน ฉันต้องเขียนคำถามเชิงวิเคราะห์ที่ซับซ้อนสำหรับรายงานและแผนภูมิประเภทต่างๆ ส่วนใหญ่มักจะเป็นแผนภูมิที่แสดงข้อมูลที่รวบรวมตามวันที่ สัปดาห์ ไตรมาส และอื่นๆ โดยปกติ รายงานดังกล่าวจะถูกสร้างขึ้นเพื่อช่วยให้ลูกค้าระบุแนวโน้มและแสดงให้เห็นว่าธุรกิจของพวกเขามีประสิทธิภาพในระดับสูงอย่างไร แต่จะเกิดอะไรขึ้นเมื่อนักวิทยาศาสตร์ข้อมูลและวิศวกรจำเป็นต้องสร้างรายงานที่ครอบคลุมมากขึ้น โดยอิงจากชุดข้อมูลขนาดใหญ่

ในกรณีที่รายงานอิงตามชุดข้อมูลขนาดเล็ก งานสามารถแก้ไขได้โดยการเขียนแบบสอบถาม SQL ภายใต้ฐานข้อมูลเชิงสัมพันธ์ ในขั้นตอนนี้ สิ่งสำคัญคือต้องรู้พื้นฐานสำหรับการเขียนข้อความค้นหาและวิธีทำให้เร็วขึ้นและมีประสิทธิภาพ อย่างไรก็ตาม บางครั้งรายงานขึ้นอยู่กับชุดข้อมูลที่มีขนาดใหญ่กว่า (เช่น หลายล้านแถวในตาราง) แต่รายงานไม่ได้ขึ้นอยู่กับตัวแปรอินพุต (พารามิเตอร์) หรือคุณอาจพบว่าจำนวนค่าค่อนข้างน้อย ในสถานการณ์เช่นนี้ คิวรี SQL อาจทำงานช้า ดังนั้นจึงไม่เหมาะสมสำหรับผู้ใช้ที่จะรอจนกว่าคิวรีจะดำเนินการ แนวทางปฏิบัติที่พบบ่อยที่สุดในกรณีเช่นนี้คือการเรียกใช้แบบสอบถามล่วงหน้า ก่อนที่ลูกค้าจะร้องขอรายงาน

นอกจากนี้ ยังต้องใช้ฟังก์ชันการแคชบางอย่าง ดังนั้นไคลเอ็นต์จึงสามารถใช้ข้อมูลจากแคชแทนการเรียกใช้แบบสอบถามในแบบเรียลไทม์ วิธีนี้ใช้ได้ผลดีโดยที่คุณไม่ต้องแสดงข้อมูลแบบเรียลไทม์ มันสามารถแสดงข้อมูลที่คำนวณได้หนึ่งชั่วโมงหรือหนึ่งวันก่อนหน้านั้น ดังนั้น รายงาน/แผนภูมิจริงจะแสดงโดยใช้ข้อมูลที่แคชไว้ ไม่ได้อิงตามข้อมูลแบบเรียลไทม์

เปลี่ยนไปใช้ Google BigQuery

ขณะที่ฉันทำงานในโครงการวิเคราะห์ในอุตสาหกรรมยา ฉันต้องการแผนภูมิที่ใช้รหัสไปรษณีย์และชื่อยาเป็นพารามิเตอร์อินพุต ฉันยังต้องแสดงการเปรียบเทียบระหว่างยาในบางภูมิภาคของสหรัฐอเมริกาด้วย

แบบสอบถามเชิงวิเคราะห์มีความซับซ้อนมากและจบลงด้วยการทำงานประมาณ 50 นาทีบนเซิร์ฟเวอร์ Postgres ของเรา (ซีพียูควอดคอร์พร้อม RAM ขนาด 16 GB) ฉันไม่สามารถเรียกใช้ล่วงหน้าและแคชผลลัพธ์ได้ เนื่องจากข้อความค้นหาใช้รหัสไปรษณีย์และยาเป็นพารามิเตอร์อินพุต จึงมีชุดค่าผสมหลายพันรายการ และไม่สามารถคาดการณ์ได้ว่าลูกค้ารายใดจะเลือก

แม้ว่าฉันต้องการพยายามเรียกใช้ชุดค่าผสมพารามิเตอร์อินพุตทั้งหมด ฐานข้อมูลของฉันก็น่าจะมีปัญหามากที่สุด ดังนั้นจึงถึงเวลาที่จะต้องเลือกวิธีการอื่นและเลือกวิธีแก้ปัญหาที่ใช้งานง่าย แผนภูมินั้นมีความสำคัญสำหรับลูกค้า อย่างไรก็ตาม ลูกค้าไม่พร้อมที่จะทำการเปลี่ยนแปลงครั้งใหญ่ในสถาปัตยกรรมหรือโยกย้ายไปยังฐานข้อมูลอื่นโดยสิ้นเชิง

ในโครงการนั้น เราได้ลองใช้แนวทางที่แตกต่างกันสองสามวิธี:

• การปรับขนาดแนวตั้งของเซิร์ฟเวอร์ (การเพิ่ม RAM และ CPU ให้กับเซิร์ฟเวอร์ Postgres)
• การใช้ฐานข้อมูลสำรอง เช่น Amazon Redshift และอื่นๆ
• เรายังทำการวิจัยโซลูชัน NoSQL อีกด้วย แต่ส่วนใหญ่ค่อนข้างซับซ้อนและต้องการการเปลี่ยนแปลงมากมายในสถาปัตยกรรม ซึ่งส่วนมากจะใหญ่เกินไปสำหรับลูกค้า

สุดท้าย เราได้ลองใช้ Google BigQuery เป็นไปตามความคาดหวังของเราและช่วยให้เราสามารถทำงานให้เสร็จได้โดยไม่ต้องทำการเปลี่ยนแปลงครั้งใหญ่ซึ่งลูกค้าจะไม่เต็มใจที่จะอนุมัติ แต่ Google BigQuery คืออะไรและทำงานอย่างไร

BigQuery คือบริการเว็บที่ใช้ REST ซึ่งช่วยให้คุณเรียกใช้การสืบค้นข้อมูล SQL เชิงวิเคราะห์ที่ซับซ้อนภายใต้ชุดข้อมูลขนาดใหญ่ หลังจากที่เราอัปโหลดข้อมูลไปยัง BigQuery และดำเนินการค้นหาแบบเดียวกับที่เราทำ Postgres (ไวยากรณ์คล้ายกันมาก) การสืบค้นของเราทำงานเร็วขึ้นมากและใช้เวลาประมาณหนึ่งนาทีจึงจะเสร็จสมบูรณ์ ในที่สุด เราก็ได้ประสิทธิภาพเพิ่มขึ้น 50 เท่าเพียงแค่ใช้บริการอื่น เป็นที่น่าสังเกตว่า DB อื่นๆ ไม่ได้ให้ประสิทธิภาพที่เพิ่มขึ้นแบบเดียวกัน และให้ใจกว้างและบอกว่าไม่ได้ใกล้เคียงกันด้วยซ้ำ พูดตามตรง ฉันประทับใจมากกับประสิทธิภาพที่เพิ่มขึ้นของ BigQuery เนื่องจากตัวเลขนั้นดีกว่าที่เราคาดหวังไว้

อย่างไรก็ตาม ฉันจะไม่โฆษณา BigQuery เป็นโซลูชันฐานข้อมูลที่ดีที่สุดในโลก แม้ว่าจะทำงานได้ดีสำหรับโครงการของเรา แต่ก็ยังมีข้อจำกัดมากมาย เช่น จำนวนการอัปเดตในตารางต่อวันที่จำกัด การจำกัดขนาดข้อมูลต่อคำขอ และอื่นๆ คุณต้องเข้าใจว่า BigQuery ไม่สามารถใช้แทนฐานข้อมูลเชิงสัมพันธ์ได้ และเน้นที่การเรียกใช้แบบสอบถามเชิงวิเคราะห์ ไม่ใช่สำหรับการดำเนินการ CRUD ทั่วไปและการสืบค้น

ในบทความนี้ ฉันจะพยายามเปรียบเทียบโดยใช้ Postgres (ฐานข้อมูลเชิงสัมพันธ์ที่ฉันชอบ) กับ BigQuery สำหรับสถานการณ์กรณีการใช้งานจริง นอกจากนี้ ฉันยังจะให้คำแนะนำสองสามข้อ กล่าวคือความคิดเห็นของฉันว่าเมื่อใดควรใช้ BigQuery อย่างเหมาะสม

ข้อมูลตัวอย่าง

ในการเปรียบเทียบ Postgres กับ Google BigQuery ฉันใช้ข้อมูลประชากรสาธารณะสำหรับแต่ละประเทศที่จัดกลุ่มตามประเทศ อายุ ปี และเพศ (คุณสามารถดาวน์โหลดข้อมูลเดียวกันได้จากลิงก์นี้)

ฉันเพิ่มข้อมูลลงในสี่ตาราง:

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

ตารางสุดท้ายเป็นเพียงข้อมูลที่รวบรวมจากสามตารางก่อนหน้า นี่คือสคีมา DB:

ตาราง populations ฉันลงเอยด้วยมีมากกว่า 6.9 ล้านแถว ไม่มาก แต่ก็เพียงพอสำหรับการทดสอบของฉัน

จากข้อมูลตัวอย่าง ฉันพยายามสร้างการสืบค้นที่สามารถใช้สำหรับสร้างรายงานและแผนภูมิการวิเคราะห์ในชีวิตจริง ดังนั้นฉันจึงเตรียมคำค้นหาสำหรับรายงานต่อไป:

1. ประชากรในสหรัฐอเมริการวมตามปี
2. ประชากรในปี 2562 ของทุกประเทศเริ่มต้นจากประเทศที่ใหญ่ที่สุด
3. ประเทศที่ "เก่าแก่ที่สุด" ห้าอันดับแรกในแต่ละปี “เก่าที่สุด” หมายถึงประเทศที่เปอร์เซ็นต์ของผู้ที่มีอายุมากกว่า 60 ถึงจำนวนคนทั้งหมดสูงที่สุด แบบสอบถามควรให้ผลลัพธ์ห้าครั้งต่อปี
4. ประเทศ 5 อันดับแรกที่รวบรวมตามปี โดยที่ความแตกต่างระหว่างประชากรชายและหญิงมากที่สุด
5. รับอายุมัธยฐาน (เฉลี่ย) ต่อประเทศในแต่ละปีโดยเริ่มจากประเทศที่ "เก่าที่สุด" ถึง "อายุน้อยที่สุด"
6. ค้นหาประเทศที่ "กำลังจะตาย" ห้าอันดับแรกในแต่ละปี “การตาย” หมายถึงประเทศที่ประชากรลดลง (จำนวนประชากรลดลงสูงสุด)

ข้อความค้นหา #1, #2 และ #6 ค่อนข้างง่ายและตรงไปตรงมา แต่ข้อความค้นหา #3, #4 และ #5 นั้นเขียนไม่ง่ายนัก อย่างน้อยสำหรับฉัน โปรดทราบว่าฉันเป็นวิศวกรส่วนหลังและการเขียนข้อความค้นหา SQL ที่ซับซ้อนนั้นไม่ใช่ความเชี่ยวชาญเฉพาะด้านของฉัน ดังนั้นผู้ที่มีประสบการณ์ SQL มากกว่าจึงอาจสร้างการสืบค้นที่ชาญฉลาดขึ้นได้ อย่างไรก็ตาม ในขณะนี้ เราต้องตรวจสอบว่า Postgres และ BigQuery ประมวลผลการสืบค้นข้อมูลเดียวกันด้วยข้อมูลเดียวกันอย่างไร

ฉันสร้างข้อความค้นหาทั้งหมด 24 รายการ:

• 6 สำหรับ Postgres DB ซึ่งใช้ตารางแบบไม่รวม ( populations locations , กลุ่ม age_groups )
• 6 สำหรับ Postgres DB ซึ่งใช้ตาราง populations_aggregated _aggregated
• 6+6 คำค้นหาสำหรับ BigQuery ซึ่งใช้ตารางแบบรวมและแบบไม่รวม

ให้ฉันแบ่งปันการค้นหา BigQuery #1 และ #5 สำหรับข้อมูลรวม เพื่อให้คุณเข้าใจความซับซ้อนของการสืบค้นธรรมดา (#1) และ #5 ที่ซับซ้อน

ประชากรในสหรัฐอเมริการวมตามแบบสอบถามปี:

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

คำค้นหาสำหรับอายุมัธยฐานของแต่ละประเทศในแต่ละปี เรียงจากเก่าสุดไปหาอายุน้อยที่สุด:

 --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

หมายเหตุ: คุณสามารถค้นหาข้อความค้นหาทั้งหมดในที่เก็บ bitbucket ของฉัน (ลิงก์อยู่ท้ายบทความ)

ผลการทดสอบ

สำหรับการเรียกใช้แบบสอบถาม ฉันใช้เซิร์ฟเวอร์ Postgres ที่แตกต่างกันสองเซิร์ฟเวอร์ อันแรกมีแกน CPU 1 คอร์และ RAM 4GB สำรองโดยไดรฟ์ SSD อันที่สองมี 16 คอร์ CPU, 64GB RAM และยังใช้ไดรฟ์ SSD (เซิร์ฟเวอร์ที่สองมีศักยภาพของ CPU และ RAM 16x)

นอกจากนี้ โปรดทราบว่าไม่มีการโหลดบนฐานข้อมูลระหว่างรันการทดสอบ ฉันสร้างขึ้นเพื่อเรียกใช้แบบสอบถามเท่านั้น ในสถานการณ์จริง คิวรีจะใช้เวลานานกว่า เนื่องจากคิวรีอื่นๆ อาจทำงานพร้อมกัน และนอกจากนี้ คิวรีเหล่านั้นที่ทำงานพร้อมกันสามารถล็อกตารางได้ สำหรับการตรวจสอบความเร็วของแบบสอบถาม ฉันใช้ pgAdmin3 และเว็บอินเตอร์เฟสของ BigQuery

ในการทดสอบของฉัน ฉันได้ผลลัพธ์เหล่านี้:

ให้ฉันแสดงผลลัพธ์เหล่านั้นในแผนภูมิแท่งสำหรับคิวรี #1 และคิวรี #5

หมายเหตุ: ฐานข้อมูล Postgres ตั้งอยู่บนเซิร์ฟเวอร์ในสหรัฐอเมริกา และฉันอยู่ในยุโรป จึงมีความล่าช้าเพิ่มเติมในการส่งข้อมูล Postgres

ประสิทธิภาพและข้อสรุปของ BigQuery

จากผลลัพธ์ที่ฉันได้รับ ฉันได้ข้อสรุปดังต่อไปนี้:

• ในกรณีของการปรับขนาด Postgres ในแนวตั้ง แม้ที่ 16x ครั้ง ก็ให้ประสิทธิภาพเพียง 10-25% ในการเรียกใช้แบบสอบถามเดียว กล่าวอีกนัยหนึ่งเซิร์ฟเวอร์ Postgres ที่มีแกนประมวลผล CPU เพียงตัวเดียวและ RAM ขนาด 4GB กำลังเรียกใช้แบบสอบถามโดยมีเวลาใกล้เคียงกับเวลาที่จำเป็นสำหรับเซิร์ฟเวอร์ที่มีแกน CPU 16 ตัวและ RAM ขนาด 64GB แน่นอนว่าเซิร์ฟเวอร์ขนาดใหญ่กว่าสามารถประมวลผลชุดข้อมูลที่มีขนาดใหญ่กว่าได้ อย่างไรก็ตาม การดำเนินการนี้ไม่ได้ช่วยปรับปรุงเวลาในการดำเนินการค้นหามากนัก
• สำหรับ Postgres ที่เข้าร่วมกับตารางขนาดเล็ก ( ตาราง locations มีประมาณ 400 แถว และ age_groups มี 100 แถว) ไม่ได้ให้ผลแตกต่างกันมากเมื่อเปรียบเทียบกับการเรียกใช้แบบสอบถามภายใต้ข้อมูลรวมที่อยู่ในตารางเดียว นอกจากนี้ ฉันพบว่าสำหรับการสืบค้นที่ใช้เวลาหนึ่งถึงสองวินาที การสืบค้นที่มีการรวมภายในนั้นเร็วกว่า แต่สำหรับการสืบค้นที่ใช้เวลานาน สถานการณ์จะแตกต่างออกไป
• ในสถานการณ์ BigQuery ที่มีการรวมจะแตกต่างกันโดยสิ้นเชิง BigQuery ไม่ชอบเข้าร่วม ความแตกต่างของเวลาระหว่างการสืบค้นซึ่งใช้ข้อมูลแบบรวมและแบบไม่รวมนั้นค่อนข้างมาก (สำหรับคำค้นหา #3 ถึง $5 ประมาณสองวินาที) หมายความว่าสำหรับ BigQuery คุณสามารถทำแบบสอบถามย่อยได้มากเท่าที่ต้องการ แต่เพื่อประสิทธิภาพที่ดี แบบสอบถามควรใช้ตารางเดียว
• Postgres นั้นเร็วกว่าสำหรับการค้นหาที่ใช้การรวมหรือการกรองอย่างง่าย หรือใช้ชุดข้อมูลขนาดเล็ก ฉันพบว่าข้อความค้นหาที่ใช้เวลาน้อยกว่าห้าวินาทีใน Postgres นั้นทำงานช้าลงใน BigQuery
• BigQuery ทำงานได้ดีขึ้นมากสำหรับการสืบค้นที่ใช้เวลานาน เมื่อความแตกต่างของขนาดชุดข้อมูลเพิ่มขึ้น ความแตกต่างของเวลาที่ใช้ในการสืบค้นเหล่านี้ก็จะเพิ่มขึ้นเช่นกัน

เมื่อใดที่ควรใช้ BigQuery

ตอนนี้ กลับมาที่ปัญหาหลักที่กล่าวถึงในบทความนี้: คุณควรใช้ Google BigQuery จริงเมื่อใด จากข้อสรุปของฉัน ฉันขอแนะนำให้ใช้ BigQuery เมื่อตรงตามเงื่อนไขต่อไปนี้:

• ใช้เมื่อคุณมีคำถามที่ทำงานมากกว่าห้าวินาทีในฐานข้อมูลเชิงสัมพันธ์ แนวคิดของ BigQuery กำลังเรียกใช้การสืบค้นข้อมูลเชิงวิเคราะห์ที่ซับซ้อน ซึ่งหมายความว่าไม่มีประเด็นใดในการเรียกใช้การสืบค้นที่ทำการรวมหรือกรองอย่างง่าย BigQuery เหมาะสำหรับการสืบค้นที่ "หนัก" ซึ่งดำเนินการโดยใช้ข้อมูลชุดใหญ่ ยิ่งชุดข้อมูลมีขนาดใหญ่เท่าใด คุณก็ยิ่งมีโอกาสได้รับประสิทธิภาพมากขึ้นโดยใช้ BigQuery ชุดข้อมูลที่ฉันใช้มีเพียง 330 MB (เมกะไบต์ ไม่ใช่กิกะไบต์)
• BigQuery ไม่ชอบการรวม ดังนั้นคุณควรรวมข้อมูลของคุณไว้ในตารางเดียวเพื่อให้มีเวลาดำเนินการดีขึ้น BigQuery อนุญาตให้บันทึกผลลัพธ์การสืบค้นในตารางใหม่ ดังนั้นในการสร้างตารางรวมใหม่ เพียงแค่อัปโหลดข้อมูลทั้งหมดของคุณไปยัง BigQuery เรียกใช้การสืบค้นที่จะรวมข้อมูลทั้งหมด และบันทึกลงในตารางใหม่
• BigQuery นั้นดีสำหรับสถานการณ์ที่ข้อมูลไม่ได้เปลี่ยนแปลงบ่อยและคุณต้องการใช้แคช เนื่องจากมีแคชในตัว สิ่งนี้หมายความว่า? หากคุณเรียกใช้การสืบค้นข้อมูลเดียวกันและข้อมูลในตารางไม่เปลี่ยนแปลง (อัปเดต) BigQuery จะใช้ผลลัพธ์ที่แคชไว้และจะไม่พยายามดำเนินการค้นหาอีกครั้ง นอกจากนี้ BigQuery จะไม่เรียกเก็บเงินสำหรับการค้นหาที่แคชไว้ หมายเหตุ: แม้แต่การสืบค้นที่แคชยังใช้เวลา 1-1.2 วินาทีในการส่งคืนผลลัพธ์
• คุณยังสามารถใช้ BigQuery เมื่อคุณต้องการลดภาระงานในฐานข้อมูลเชิงสัมพันธ์ของคุณ การสืบค้นเชิงวิเคราะห์นั้น “หนัก” และการใช้มากเกินไปภายใต้ฐานข้อมูลเชิงสัมพันธ์สามารถนำไปสู่ปัญหาด้านประสิทธิภาพได้ ดังนั้น ในที่สุด คุณอาจถูกบังคับให้คิดเกี่ยวกับการปรับขนาดเซิร์ฟเวอร์ของคุณ อย่างไรก็ตาม ด้วย BigQuery คุณสามารถย้ายการสืบค้นที่ทำงานอยู่เหล่านี้ไปยังบริการของบุคคลที่สาม ดังนั้นจึงไม่ส่งผลต่อฐานข้อมูลเชิงสัมพันธ์หลักของคุณ

สุดท้าย อีกสองสามคำเกี่ยวกับการใช้ BigQuery ในชีวิตจริง ในโครงการจริงของเรา ข้อมูลสำหรับรายงานมีการเปลี่ยนแปลงเป็นรายสัปดาห์หรือรายเดือน ดังนั้นเราจึงสามารถอัปโหลดข้อมูลไปยัง BigQuery ได้ด้วยตนเอง อย่างไรก็ตาม หากข้อมูลของคุณมีการเปลี่ยนแปลงบ่อยครั้ง การซิงค์ข้อมูลระหว่างฐานข้อมูลเชิงสัมพันธ์กับ BigQuery อาจไม่ง่ายนัก และนี่เป็นข้อควรจำที่ควรคำนึงถึง

ลิงค์

คุณสามารถค้นหาข้อมูลตัวอย่างที่ใช้ในบทความนี้ได้ที่นี่ ในขณะที่แบบสอบถามและข้อมูลในรูปแบบ CSV สามารถเข้าถึงได้ที่นี่