การสืบค้นข้อมูลจำนวนมากในไม่กี่วินาที: คลังข้อมูลสมัยใหม่ของ Google BigQuery ตั้งแต่เริ่มต้นจนถึงบทช่วยสอนหลัก

ในยุคปัจจุบันที่ข้อมูลระดับ TB และ PB เริ่มต้นทีมอินเทอร์เน็ตเกือบทุกทีมจะต้องเผชิญกับปัญหาคอขวดทางเทคนิคที่สำคัญ:

รายงานข้อมูลช้าเกินไป

เมื่อฐานข้อมูลธุรกิจแบบดั้งเดิม (เช่น MySQL, PostgreSQL) ต้องเผชิญกับการวิเคราะห์บันทึกหรือขั้นตอนอีคอมเมิร์ซหลายร้อยล้านรายการแม้ว่าคุณจะสร้างดัชนีให้สมบูรณ์แบบแต่ก็มีความซับซ้อน

GROUP BY

แบบสอบถามการรวมถูกทุบและซีพียูของเซิร์ฟเวอร์สามารถทะยานขึ้นเป็น100% ได้ทันทีจากนั้นดอกเบญจมาศจะหมุนเป็นวงกลมเป็นเวลาสองสามนาทีหรือหลายชั่วโมงและในที่สุดก็เกิดการล่มสลายของ OOM (หน่วยความจำล้น) โดยตรงเพื่อแก้ปัญหานี้หลายทีมต้องจ่ายเงินในราคาสูงเพื่อตั้ง Hadoop หรือแม้แต่สร้างคลัสเตอร์ ClickHouse ของตัวเองด้วยเหตุนี้ไม่เพียงแต่เกณฑ์การดำเนินการและการบำรุงรักษาจะสูงมากแต่ค่าฮาร์ดแวร์เซิร์ฟเวอร์รายเดือนยังทำให้เจ้านายเจ็บปวดโดยตรง

ในระบบนิเวศของ Google Cloud(GCP, Google Cloud) มีการโจมตีแบบลดมิติที่ออกแบบมาเพื่อแก้ปัญหาการวิเคราะห์จำนวนมากเรียกว่า

Google BigQuery

。

ตรรกะหลักของมันนั้นบริสุทธิ์อย่างยิ่ง:

สถาปัตยกรรม Serverless (ไม่มีเซิร์ฟเวอร์) ที่โฮสต์อย่างสมบูรณ์ + พื้นที่จัดเก็บข้อมูลแบบกระจายขนาดใหญ่

。คุณไม่จำเป็นต้องจัดการการกำหนดค่าเซิร์ฟเวอร์พื้นฐานใดๆคุณไม่จำเป็นต้องสร้างดัชนีคุณสามารถโยนไฟล์หลายร้อย GB ลงไปได้โดยตรงและสามารถใช้คำสั่ง SQL มาตรฐานเพื่อคายผลลัพธ์การรวมขั้นสุดท้ายให้คุณได้ภายในไม่กี่วินาที

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

ขั้นตอนที่1: การถอดชิ้นส่วนเชิงลึกทำไม BigQuery จึงสามารถ "แบบสอบถามที่สอง" ได้?

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

ชั้นล่างสุดของ BigQuery ใช้

การคำนวณและการจัดเก็บข้อมูลแยกออกจากกันโดยสิ้นเชิง

สถาปัตยกรรมก่อกวน:

คอนเทนเนอร์เทอร์มินัล (Colossus กระจายการจัดเก็บ): ข้อมูลของคุณอยู่ในตำแหน่ง BigQuery ใช้การจัดเก็บคอลัมน์ (รูปแบบ Capacitor) ฐานข้อมูลแบบดั้งเดิม (ที่เก็บข้อมูลบรรทัด): ในการตรวจสอบอายุของผู้ใช้ทั้งหมดข้อมูลทั้งบรรทัดที่มีชื่อที่อยู่รหัสผ่านฯลฯจะต้องอ่านออกจากฮาร์ดดิสก์ทำให้เสีย I/O จำนวนมาก BigQuery (การจัดเก็บคอลัมน์): ข้อมูลจะถูกเก็บไว้ในกลุ่มคอลัมน์เมื่อคุณตรวจสอบอายุจะอ่านข้อมูลในคอลัมน์ "อายุ" อย่างแม่นยำเท่านั้นและคอลัมน์อื่นๆจะไม่แตะต้องด้วยซ้ำฮาร์ดไดรฟ์ I/O ถูกตัดโดยตรงมากกว่า90%

ซูเปอร์เอนจิ้น (คลัสเตอร์คอมพิวเตอร์ Dremel): เมื่อคุณพิมพ์บรรทัดของ SQL แบบสอบถามที่ซับซ้อนบนคอนโซลและคลิกเพื่อดำเนินการ Google จะกำหนดเวลารายการที่เรียกว่า Slot หลายร้อยรายการในพื้นหลังทันที

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

ข้อสรุปหลัก: คุณจ่ายตาม ** เพื่อค้นหาปริมาณข้อมูลที่สแกน (ข้อมูลสแกน) ** (ประมาณ $5ต่อการสแกน1 TB) หรือซื้อทรัพยากรคอมพิวเตอร์คงที่ดังนั้นวิธีการเขียน SQL ที่ "ประหยัดเงินและมีประสิทธิภาพ" จึงเป็นแหล่งต้นน้ำในการแยกความแตกต่างระหว่างมือใหม่และสถาปนิกรายใหญ่

ขั้นตอนที่สอง: การฝึกซ้อมการต่อสู้จริงการนำเข้าข้อมูลและประสบการณ์ครั้งแรกของการสืบค้นวินาที

ให้แน่ใจว่าคุณมีบัญชี GCP อยู่แล้วก่อนอื่นเราต้องนำเข้าบันทึกพฤติกรรมผู้ใช้มากกว่าห้าล้านบรรทัดในรูปแบบ CSV ดั้งเดิมไปยัง BigQuery

1.สร้างชุดข้อมูล (Dataset)

ใน BigQuery โครงสร้างข้อมูลมีความชัดเจนมาก: Project-> ชุดข้อมูล (Dataset เทียบเท่ากับฐานข้อมูล)-> แผ่นข้อมูล (ตาราง)

เข้าสู่คอนโซล GCP ค้นหาและเข้าสู่หน้า BigQuery 。

ในเมนู Explorer ทางด้านซ้ายให้คลิกจุดสามจุดทางด้านขวาของโครงการของคุณแล้วเลือก "สร้างชุดข้อมูล"

ชุดข้อมูล ID: ชื่อ ecommerce_analytics 。

ตำแหน่งข้อมูล: แนะนำให้เลือก asia-east1 (ไต้หวัน) ซึ่งอยู่ใกล้กับประเทศและรวดเร็วคลิกสร้าง

2.คลิกเดียวนำเข้าข้อมูลที่มีโครงสร้าง

คลิกชุดข้อมูล ecommerce_analytics ที่คุณเพิ่งสร้างขึ้นแล้วเลือก "สร้างตาราง"

แหล่งที่มา: เลือกจาก "Google Cloud Storage (ที่เก็บวัตถุ GCS)" หรือ "อัปโหลด" ไฟล์ในเครื่องโดยตรง

รูปแบบไฟล์: เลือก CSV 。

ชื่อตารางเป้าหมาย: ป้อน user_logs 。

สถาปัตยกรรม (Schema): ติ๊ก "Auto Detect" BigQuery จะสแกนบรรทัดแรกของไฟล์ของคุณโดยอัตโนมัติอย่างชาญฉลาดโดยระบุว่าคอลัมน์ใดคือสตริงและคอลัมน์ใดคือตัวเลขหรือการประทับเวลา

คลิกสร้างตารางไม่กี่วินาทีต่อมาข้อมูลมากกว่าห้าล้านบรรทัดก็อยู่ในที่เก็บข้อมูลแบบกระจายคอลัมน์บนคลาวด์ของ Google

3.วินาทีดึงการตรวจสอบกระแส

ในตัวแก้ไขแบบสอบถามให้พิมพ์ SQL รวมมาตรฐานต่อไปนี้เพื่อดูว่าหมวดหมู่ผลิตภัณฑ์10อันดับแรกที่มียอดซื้อสูงสุดในช่วง30วันที่ผ่านมาคือใคร:

เอสคิวแอล

SELECT

Product_category,

COUNT(order_id) AS total_sales,

SUM(price) AS total_revenue

FROM

'Ecommerce _ analytics.us er_logs'

WHERE

Event_time> = TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30วัน)

กลุ่มตาม

Product_category

ORDER BY

Total_revenue DESC

LIMIT 10;

คลิกเรียกใช้จ้องมองไปที่ตัวจับเวลาที่มุมขวาบนของหน้าจอ:

0.8วินาที!

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

ขั้นตอนที่สาม: การปรับแต่งประสิทธิภาพระดับไฮเอนด์ระดับองค์กร-การประกันสองชั้นของ "การประหยัดเงินและการเร่งความเร็ว"

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

ในฐานะหัวหน้าสถาปนิกข้อมูลคุณต้องติดตั้งแนวป้องกันทางกายภาพสองชุดต่อไปนี้ทันทีเมื่อสร้างตาราง:

1.แนวป้องกันแรก: พาร์ทิชัน (Partitioning)-ตัดการสแกนที่ไม่ถูกต้อง

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

การกำหนดค่าข้อกำหนดฮาร์ดคอร์: เมื่อสร้างตารางหรือออกแบบไปป์ไลน์ให้ระบุ "พาร์ติชัน" ตามไทม์ไลน์ (เช่น event_date)

การเปรียบเทียบเอฟเฟกต์: หลังจากเปิดพาร์ติชันเมื่อคุณจำกัด WHERE event_date = '2026-05-30 'ในเงื่อนไข WHERE BigQuery จะเหมือนกับการพลิกหนังสือที่ชั้นล่างสุดและตรงไปยังลิ้นชักแยกทางกายภาพในวันที่30พฤษภาคมและอื่นๆอีกหลายพันลิ้นชักของวันที่ไม่ได้มองด้วยซ้ำปริมาณการสแกนลดลงทันทีจาก100GB เป็น1GB และค่าใช้จ่ายในการเรียกเก็บเงินลดลง99% โดยตรง

2.แนวป้องกันที่สอง: Clustering-ให้ข้อมูล "รวบรวมสิ่งต่างๆเข้าด้วยกัน"

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

การกำหนดค่าข้อกำหนดฮาร์ดคอร์: บนพื้นฐานของพาร์ติชันเวลาระบุ "คลัสเตอร์" บนคอลัมน์ประเทศและแหล่งที่มา

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

ขั้นตอนที่สี่: ข้อมูลขนาดใหญ่เชิงพาณิชย์

บรรทัดฐานการพัฒนาและประวัติการหลีกเลี่ยงเลือดและน้ำตาในแต่ละวัน

เครื่องมือนี้ใช้งานได้ง่ายมากแต่ในฉากจริงมือใหม่จำนวนนับไม่ถ้วนในการดำเนินการบำรุงรักษาและการพัฒนาเนื่องจากพวกเขาไม่เข้าใจกฎพื้นฐานที่ไม่ได้พูดของ BigQuery พวกเขามักจะก้าวเข้าไปในหลุมนองเลือดสองหลุมต่อไปนี้:

1.ห้ามใช้โดยเด็ดขาด

SELECT*

(ที่มาของความชั่วร้ายทั้งหมดเจอกัน)

ใน MySQL แบบดั้งเดิมเราคุ้นเคยกับการพิมพ์

SELECT* FROM table LIMIT 10

มาดูกันว่าหน้าตาเป็นอย่างไร

ภัยพิบัติร้ายแรง: ในการจัดเก็บคอลัมน์ของ BigQuery LIMIT 10ไม่สามารถช่วยคุณประหยัดเงินได้เลย! เนื่องจาก BigQuery อ่านตามคอลัมน์เมื่อคุณเขียน SELECT * มันจะบังคับให้ดึงคอลัมน์พื้นฐานทั้งหมดและข้อมูลทั้งหมดออกจากฮาร์ดดิสก์แม้ว่าคุณจะมีเพียง10บรรทัดในตอนท้ายก็ตามหากตารางนี้มี100 GB คำสั่งปกตินี้จะสร้างการหักเงินการสแกน100 GB โดยตรง

วิธีแก้ปัญหามาตรฐาน Dachang: หากคุณต้องการดูโครงสร้างและตัวอย่างข้อมูลของตารางเท่านั้นอย่าคลิกแบบสอบถาม! คลิกที่ชื่อของตารางโดยตรงและเปลี่ยนเป็นแท็บ "ดูตัวอย่าง" ฟังก์ชั่นดูตัวอย่างเพื่อดูข้อมูลนั้นฟรีและไม่มีการสแกนหากคุณต้องเขียน SQL คุณต้องเขียนคอลัมน์ที่คุณต้องการอย่างชัดเจน (เช่น SELECT user_id, age)

2.ยอมรับ "การแบนอำนาจทุกอย่าง" (ละทิ้งสามกระบวนทัศน์ดั้งเดิมที่เข้มงวด)

นักเรียนหลายคนที่ย้ายมาจากฐานข้อมูลเชิงสัมพันธ์แบบเดิมคุ้นเคยกับการแบ่งตารางที่ละเอียดมาก: ตารางสำหรับผู้ใช้ตารางสำหรับคำสั่งซื้อและตารางสำหรับผลิตภัณฑ์และสุดท้ายใช้ห้าหรือหกรายการเมื่อเขียนและวิเคราะห์ SQL

JOIN

บังคับให้รวมเข้าด้วยกัน

สถาปนิกปรับแต่งเรื่องราวภายใน: ในคลังข้อมูลแบบกระจายสมัยใหม่ JOIN เป็นการดำเนินการที่มีราคาแพงซึ่งใช้พลังการประมวลผลแบบคลัสเตอร์อย่างมาก (เนื่องจากจะทำให้เกิดการสับเปลี่ยนข้อมูลระหว่างโหนดจำนวนมาก) โครงสร้างที่ชื่นชอบของ BigQuery คือตารางกว้าง (De-normalized Table)

ท่าทางที่ถูกต้อง: ใช้ประเภท RECORD (ฟิลด์ที่ซ้อนกัน) และ REPEATED (ฟิลด์ที่ซ้ำกัน) ที่เป็นเอกลักษณ์ของ BigQuery 。คุณสามารถใส่คำสั่งซื้อทั้งหมดและแทร็กในอดีตทั้งหมดของผู้ใช้โดยตรงเป็นอาร์เรย์ที่ซ้อนกัน (อาร์เรย์) ลงในบรรทัดนี้ในระหว่างการสืบค้นให้ใช้ฟังก์ชัน UNNEST เพื่อแกะกล่องแบบฟ้าผ่าเพื่อกำจัด JOIN ของประสิทธิภาพอย่างสมบูรณ์

ขั้นตอนที่5: วิวัฒนาการขั้นสูงผ่านการประมวลผลสตรีมแบบเรียลไทม์และรายงาน BI ขนาดใหญ่

หากธุรกิจของบริษัทของคุณต้องการดู "ตลาดแบบเรียลไทม์" (เช่นการเปลี่ยนแปลง GMV แบบเรียลไทม์ทุกวินาทีในช่วงโปรโมชั่นใหญ่) BigQuery ยังสามารถเล่นได้อย่างง่ายดาย:

Streaming Inserts: แอพแบ็คเอนด์หรือเครื่องมือประมวลผลสตรีมของคุณ (เช่น Apache Beam, Cloud Dataflow) สามารถส่งผ่าน BigQuery

Storage Write API สร้างบันทึกพฤติกรรมผู้ใช้นับหมื่นทุกวินาทีและฉีดลงในตาราง user_logs อย่างต่อเนื่องแบบเรียลไทม์เหมือนน้ำไหล

การเข้าถึงหน้าจอ BI ขนาดใหญ่เป็นศูนย์: คลิก "สำรวจข้อมูล" ที่ด้านบนของ BigQuery โดยตรงเพื่อเปิด Looker Studio ของ Google หรือ Tableau ของบุคคลที่สามด้วยคลิกเดียว

เนื่องจาก BigQuery มีอยู่ในตัว

BI Engine (เครื่องยนต์เร่งหน่วยความจำ)

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

เสร็จสิ้นในหลายสิบมิลลิวินาที

, ประสบความสำเร็จอย่างแท้จริง "ดูข้อมูลทันทีที่ลงจอด, การสังเกตระดับโลกในไม่กี่วินาที"

สรุป

การใช้ Google BigQuery เพื่อสร้างคลังข้อมูลที่ทันสมัยระดับองค์กรสาระสำคัญระดับอุตสาหกรรมหลักคือ16คำ:

การเร่งการจัดเก็บคอลัมน์การล็อกพาร์ติชันการแสดงตัวอย่างโสเภณีสีขาวความกว้างสากล

。

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