Trong bài đăng blog này, Inda sẽ giải thích các tính năng phân vùng (partitioning) và phân cụm (Clustering) trong BigQuery là gì và cách tăng hiệu suất truy vấn và giảm chi phí truy vấn của bạn.

Partitioning

Phân vùng (Partitioning) bảng có thể làm cho các truy vấn của bạn chạy nhanh hơn trong khi chi phí ít hơn. Cho đến tháng 12 năm 2019, BigQuery chỉ hỗ trợ phân vùng bảng bằng cách sử dụng loại dữ liệu date. Bây giờ, bạn cũng có thể phân vùng trên kiểu dữ liệu là integer .Bạn muốn biết thêm về cách phân vùng các bảng theo cách này, có thể tham khảo bài đăng trên blog của Guillaume Blaquiere.

Trong bài viết này, Inda sẽ tập trung vào phân vùng kiểu date . Bạn có thể phân vùng dữ liệu của mình bằng 2 chiến lược chính: thứ nhất bạn có thể sử dụng cột bảng, thứ hai, bạn có thể sử dụng thời gian nhập dữ liệu.

Cách tiếp cận này đặc biệt hữu ích khi bạn có bộ dữ liệu rất lớn quay ngược thời gian trong nhiều năm. Trên thực tế, nếu bạn chỉ muốn chạy phân tích trong các khoảng thời gian cụ thể, việc phân vùng bảng theo thời gian cho phép BigQuery chỉ đọc và xử lý các hàng trong khoảng thời gian cụ thể đó. Do đó, các truy vấn sẽ chạy nhanh hơn do chúng đang đọc ít dữ liệu hơn, từ đó chi phí thấp hơn.

Tạo một bảng được phân vùng rất dễ dàng. Tại thời điểm tạo bảng, bạn có thể chỉ định cột nào sẽ được sử dụng để phân vùng, nếu không, bạn có thể thiết lập phân vùng theo thời gian nhập . 

select
  day,
  count(*)
from full_history
where sampling_date >= ‘2019–08–05’
and sampling_date < ‘2019–08–06’
group by 1

Giả sử rằng “ sampling_date ” là cột phân vùng, giờ đây BigQuery có thể sử dụng các giá trị được chỉ định trong “mệnh đề where” để chỉ đọc dữ liệu thuộc về các phân vùng phù hợp.

Bạn có thể sử dụng trình partition decorators để cập nhật, xóa và ghi đè toàn bộ phân vùng đơn lẻ như sau:

# overwrite single partition loading from file
bq load —-replace \
  project_id:dataset_name.table_name$20190805 \
  gs://my_input_bucket/data/from/20190805/* ./schema.json

và

# overwrite single partition from query results
bq query —- replace --use_legacy_sql=false \
  —-destination_table project_id:dataset.table$20190805 \
  ‘select * from project_id:dataset.another_table’ 

Trong các trường hợp trên, cả dữ liệu được tải và kết quả truy vấn phải thuộc về phân vùng được tham chiếu, nếu không công việc sẽ không thành công.

Clustering

Phân cụm (Clustering) là một cách khác để tổ chức dữ liệu lưu trữ , BigQuery tự động sắp xếp dữ liệu dựa trên các giá trị trong các cột nhóm và sắp xếp chúng trong các khối lưu trữ có kích thước tối ưu. Quá trình này làm tăng hiệu quả và hiệu suất truy vấn. Lưu ý rằng BigQuery chỉ hỗ trợ tính năng này trên các bảng được phân vùng.

BigQuery có thể tận dụng các bảng được phân nhóm để chỉ đọc dữ liệu có liên quan đến truy vấn, do đó, nó trở nên nhanh hơn và chi phí rẻ hơn.

Tại thời điểm tạo bảng, bạn có thể cung cấp tối đa 4 nhóm cột trong danh sách được phân tách bằng dấu phẩy, ví dụ: “ wiki ”, “ title ”. Bạn cũng nên nhớ rằng thứ tự của chúng là rất quan trọng .

Trong phần này, Inda sẽ sử dụng tập dữ liệu công khai “wikipedia_v3” của Felipe Hoffa, chứa thông tin về lượt xem hàng năm của trang Wikipedia. Chúng được phân chia theo cột “datehour ” và được nhóm lại trên các cột “ wiki ” và “ title”. Một hàng trông như thế này:

datehour, language, title, views
2019–08–10 03:00:00 UTC, en , Pizza, 106
...

Số lượng truy vấn sau đây, được chia nhỏ mỗi năm, tất cả các lượt xem trang cho wiki tiếng Ý từ 2015–01–01.

select
  _table_suffix as year,
  wiki,
  sum(views) / pow(10, 9) as Views
from `fh-bigquery.wikipedia_v3.pageviews_*`
where wiki = ‘it’and datehour >= ‘2015–01–01’
group by 1,2
order by 1 asc

Nếu bạn viết truy vấn này trong giao diện người dùng BigQuery, nó sẽ ước tính quét dữ liệu là 4,5 TB. Tuy nhiên, nếu bạn thực sự chạy nó, dữ liệu được quét cuối cùng sẽ chỉ có 160 GB.

Sao có thể như thế được?

Khi BigQuery chỉ đọc các hàng thuộc cụm chứa dữ liệu cho wiki tiếng Ý trong khi loại bỏ mọi thứ khác.

Tại sao thứ tự các cột rất quan trọng trong việc phân cụm?

Điều quan trọng là vì BigQuery sẽ tổ chức dữ liệu phân cấp theo thứ tự cột được chỉ định khi tạo bảng.

Hãy sử dụng ví dụ sau:

select
  wiki,
  sum(views) / pow(10, 9) as Views
from `fh-bigquery.wikipedia_v3.pageviews_2019`
where title = ‘Pizza’
and datehour >= ‘2019–01–01’
group by 1
order by 1 asc

Truy vấn này cần truy cập vào tất cả các cụm “wiki” và sau đó nó có thể sử dụng giá trị “title” để bỏ qua các cụm không phù hợp.

Điều này dẫn đến việc quét nhiều dữ liệu hơn nếu các cột phân nhóm theo thứ tự ngược lại ” title “, ” wiki “.

Tại thời điểm viết bài, truy vấn trên ước tính chi phí quét là 1,4 TB nhưng thực tế nó chỉ quét 875,6 GB dữ liệu.

Bây giờ, hãy đảo ngược thứ tự các cột phân cụm đặt “title ” đầu tiên và “ wiki ” thứ hai , bạn có thể làm như vậy bằng cách sử dụng lệnh sau:

bq query --allow_large_results --nouse_legacy_sql \
  --destination_table my_project_id:dataset_us.wikipedia_2019 \
  --time_partitioning_field datehour \
  --clustering_fields=title,wiki \
'select * from `fh-bigquery.wikipedia_v3.pageviews_2019`'

Chạy truy vấn “Pizza” trên bảng mới “ my_project_id: dataset_us.wikipedia_2019 ” sẽ rẻ hơn nhiều. Trên thực tế, trong khi ước tính vẫn là 1,4 TB, dữ liệu thực tế đọc được chỉ là 26,3 GB, tức là ít hơn 33 lần.

Trong bài kiểm tra cuối cùng, hãy thử lọc trên cột “ wiki ”:

select
  wiki,
  sum(views) / pow(10, 9) as Views_B
from `my_project_id:dataset_us.wikipedia_2019`
where wiki = ‘it’ and title is not null
and datehour >= ‘2019–01–01’
group by 1
order by 1 asc

Ước tính đọc dữ liệu luôn giống nhau nhưng bây giờ dữ liệu thực sự đọc đã tăng lên 1,4 TB (toàn bộ bảng) trong khi, trong ví dụ đầu tiên, dữ liệu thực sự đọc chỉ là 160 GB.

Lưu ý : Vì BigQuery sử dụng kho lưu trữ dạng cột, nên ” title is not null ” đảm bảo rằng luôn tham chiếu đến cùng một số cột trong mọi truy vấn. Nếu không, dữ liệu đọc từ truy vấn cuối cùng sẽ thấp hơn.

Rõ ràng là việc chọn đúng cụm cột và thứ tự của chúng tạo ra sự khác biệt lớn. Bạn nên lập kế hoạch phù hợp với khối lượng công việc của mình.

Hãy nhớ, luôn phân vùng và phân cụm các bảng của bạn! Nó miễn phí, không cần thay đổi bất kỳ truy vấn nào của bạn và nó sẽ làm cho chúng rẻ hơn và nhanh hơn.

Github và Twitter của tác giả .