Đi nào.
Tôi đang phát triển một kịch bản để chuẩn bị các mẫu dữ liệu cho những khách hàng đã liên hệ để được tư vấn. Các mẫu có 100 hàng trong mỗi tệp và chúng được chia thành 55 ngôn ngữ. truy vấn của tôi trông như thế này
SELECT * FROM `project.dataset.table` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "US" LIMIT 100;
Dữ liệu được lưu trữ ở “europe-west-4” và giá truy vấn là $6 mỗi TB . Vì vậy, bằng cách chạy tập lệnh, tôi đã xử lý:
Rất đắt.
Tập lệnh được viết bằng các mô-đun JavaScript .
// bq-samples.mjs import { BigQuery } from "@google-cloud/bigquery"; import { Parser } from "@json2csv/plainjs"; import makeDir from "make-dir"; import { write } from "./write.mjs"; import { locales } from "./locales.mjs"; import { perf } from "./performance.mjs"; const q = (locale, start, end, limit) => `SELECT * FROM \`project.dataset.table\` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "${locale}" LIMIT ${limit}` async function main() { const timer = perf() const dir = await makeDir('samples') const bigquery = new BigQuery() const csvParser = new Parser({}) try { const jobs = locales.map((locale) => async () => { // get query result from BigQuery const [job] = await bigquery.createQueryJob({ query: q(locale, "2022-12-01", "2023-02-28", 100), }) const [rows] = await job.getQueryResults() // parse rows into csv format const csv = parse(csvParser, rows) // write data into csv files and store in the file system await write(csv, dir, locale, "2022-12-01", "2023-02-28", 100) }) await Promise.all(jobs.map((job) => job())) console.log(`✨ Done in ${timer.stop()} seconds.`) } catch (error) { console.error('❌ Failed to create sample file', error) } } await main()
Nó tạo một tệp mẫu ở định dạng CSV cho mỗi ngôn ngữ. Quá trình này rất đơn giản:
Hóa ra tôi đã làm sai một số điều trong truy vấn của mình. Nếu xem lại mô hình định giá, bạn sẽ nhận thấy chi phí chỉ liên quan đến lượng dữ liệu bạn xử lý. Vì vậy, rõ ràng là truy vấn của tôi đã tra cứu quá nhiều dữ liệu để tạo ra 100 hàng.
Với thông tin chi tiết này, hãy từng bước tối ưu hóa truy vấn.
Đó là một chút phản trực giác. Tại sao câu lệnh chọn của tôi lại liên quan đến lượng dữ liệu mà nó xử lý? Bất kể tôi chọn cột nào, tôi nên đọc từ cùng một tài nguyên và dữ liệu, phải không?
Nó chỉ đúng với cơ sở dữ liệu hướng hàng.
BigQuery thực sự là một cơ sở dữ liệu dạng cột . Nó được định hướng theo cột, nghĩa là dữ liệu được cấu trúc theo cột. BigQuery sử dụng Drillac làm công cụ điện toán cơ bản. Khi dữ liệu được di chuyển từ kho lưu trữ lạnh sang kho lưu trữ hoạt động trong Drillac, dữ liệu sẽ được lưu trữ trong cấu trúc cây.
Mỗi nút lá là một “bản ghi” hướng cột ở định dạng Protobuf .
Trong BigQuery, mỗi nút là một máy ảo. Việc thực thi truy vấn lan truyền từ máy chủ gốc (nút) qua các máy chủ trung gian đến các máy chủ lá để truy xuất các cột đã chọn.
Chúng tôi có thể sửa đổi truy vấn để chọn các cột riêng lẻ:
SELECT session_info_1, session_info_2, session_info_3, user_info_1, user_info_2, user_info_3, query_info_1, query_info_2, query_info_3, impression_info_1, impression_info_2, impression_info_3, ts FROM `project.dataset.table` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "US" LIMIT 100;
Chỉ cần chọn rõ ràng tất cả các cột, tôi đã có thể giảm dữ liệu được xử lý từ 3,08 TB xuống 2,94 TB. Đó là mức giảm 100 GB .
Google Cloud khuyên chúng ta nên phân vùng bảng theo ngày . Nó cho phép chúng tôi chỉ truy vấn một tập hợp con dữ liệu.
Để tối ưu hóa truy vấn hơn nữa, chúng ta có thể thu hẹp phạm vi ngày trong câu lệnh where vì bảng được phân vùng theo cột “ts”.
SELECT session_info_1, session_info_2, session_info_3, user_info_1, user_info_2, user_info_3, query_info_1, query_info_2, query_info_3, impression_info_1, impression_info_2, impression_info_3, ts FROM `project.dataset.table` WHERE ts = TIMESTAMP("2022-12-01") AND locale = "US" LIMIT 100;
Tôi đã thu hẹp phạm vi ngày xuống còn một ngày thay vì ba tháng. Tôi đã có thể cắt giảm dữ liệu đã xử lý xuống còn 37,43 GB . Nó chỉ là một phần của truy vấn ban đầu.
Một cách khác để giảm chi phí là giảm tập dữ liệu mà bạn đang truy vấn. BigQuery cung cấp bảng đích để lưu trữ kết quả truy vấn dưới dạng bộ dữ liệu nhỏ hơn. Bảng đích có hai dạng: tạm thời và vĩnh viễn.
Vì các bảng tạm thời có thời gian tồn tại lâu dài và nó không được thiết kế để chia sẻ và truy vấn, nên tôi đã tạo một bảng đích cố định để cụ thể hóa kết quả truy vấn:
// bq-samples.mjs const dataset = bigquery.dataset('materialized_dataset') const materialzedTable = dataset.table('materialized_table') // ... const [job] = await bigquery.createQueryJob({ query: q(locale, '2022-12-01', '2023-02-28', 100), destination: materialzedTable, })
Kết quả truy vấn sẽ được lưu trữ trong bảng đích. Nó sẽ phục vụ như một tài liệu tham khảo cho các truy vấn trong tương lai. Bất cứ khi nào có thể truy vấn từ bảng đích, BigQuery sẽ xử lý dữ liệu từ bảng. Nó sẽ làm giảm đáng kể kích thước dữ liệu mà chúng tôi tra cứu.
Đây là một nghiên cứu rất thú vị để giảm chi phí trong BigQuery. Chỉ với ba bước đơn giản:
Tôi đã có thể giảm kích thước dữ liệu đã xử lý từ 3 TB xuống 37,5 GB . Nó làm giảm đáng kể tổng chi phí từ $3.000 xuống còn $30.
Nếu bạn muốn tìm hiểu thêm về kiến trúc BigQuery, thì đây là những tài liệu tham khảo đã giúp ích cho tôi:
Bạn có thể đọc thêm về tối ưu hóa chi phí BigQuery trong tài liệu Google Cloud.
Đặc biệt cảm ơn Abu Nashir vì đã cộng tác với tôi trong nghiên cứu điển hình và cung cấp thông tin chuyên sâu có giá trị giúp tôi hiểu kiến trúc của BigQuery.
Muốn kết nối?
Bài viết này ban đầu được đăng trên trang web của Daw-Chih .