Phân vùng và phân cụm trong BigQuery

1. Giới thiệu

BigQuery là một kho dữ liệu doanh nghiệp cỡ petabyte, có chi phí thấp và được quản lý hoàn toàn để phân tích. BigQuery là một giải pháp không máy chủ. Bạn không cần thiết lập và quản lý các cụm.

Một tập dữ liệu BigQuery nằm trong một dự án GCP và chứa một hoặc nhiều bảng. Bạn có thể truy vấn các tập dữ liệu này bằng SQL.

Trong lớp học lập trình này, bạn sẽ sử dụng giao diện người dùng BigQuery trên web trong Bảng điều khiển GCP để tìm hiểu về việc phân vùng và phân cụm trong BigQuery. Tính năng phân vùng và phân cụm bảng của BigQuery giúp cấu trúc dữ liệu của bạn sao cho phù hợp với các mẫu truy cập dữ liệu phổ biến. Phân vùng và phân cụm là chìa khoá để tối đa hoá hiệu suất và chi phí của BigQuery khi truy vấn trong một phạm vi dữ liệu cụ thể. Điều này giúp giảm lượng dữ liệu được quét cho mỗi truy vấn và việc cắt tỉa được xác định trước thời gian bắt đầu truy vấn.

Để biết thêm thông tin về BigQuery, hãy xem tài liệu về BigQuery.

Kiến thức bạn sẽ học được

  • Cách tạo và truy vấn các bảng được phân vùng và phân cụm
  • So sánh hiệu suất truy vấn với các bảng được phân vùng và phân cụm

Bạn cần có

Để hoàn tất bài thực hành này, bạn cần:

  • Phiên bản Google Chrome mới nhất
  • Tài khoản thanh toán Google Cloud Platform

2. Thiết lập

Để làm việc với BigQuery, bạn cần tạo một dự án trên Google Cloud Platform hoặc chọn một dự án hiện có.

Tạo một dự án

Để tạo một dự án mới, hãy làm theo các bước sau:

  1. Nếu bạn chưa có Tài khoản Google (Gmail hoặc Google Apps), hãy tạo một tài khoản.
  2. Đăng nhập vào bảng điều khiển Google Cloud Platform ( console.cloud.google.com) rồi tạo một dự án mới.
  3. Nếu bạn chưa có dự án nào, hãy nhấp vào nút tạo dự án:

870a3cbd6541ee86.png

Nếu không, hãy tạo một dự án mới trong trình đơn chọn dự án:

f6dff3437a20cf2.png

  1. Nhập tên dự án rồi chọn Tạo. Lưu ý rằng mã dự án là một tên riêng biệt trên tất cả các dự án của Google Cloud.

1884405a64ce5765.png

3. Làm việc với các tập dữ liệu công khai

BigQuery cho phép bạn làm việc với các tập dữ liệu công khai, bao gồm cả BBC News, kho lưu trữ GitHub, Stack Overflow và các tập dữ liệu của Cơ quan Quản lý Đại dương và Khí quyển Quốc gia (NOAA) của Hoa Kỳ. Bạn không cần tải các tập dữ liệu này vào BigQuery. Bạn chỉ cần mở các tập dữ liệu để duyệt xem và truy vấn chúng trong BigQuery. Trong lớp học lập trình này, bạn sẽ làm việc với tập dữ liệu công khai của Stack Overflow.

Duyệt xem tập dữ liệu Stack Overflow

Tập dữ liệu Stack Overflow chứa thông tin về bài đăng, thẻ, huy hiệu, bình luận, người dùng và nhiều thông tin khác. Để duyệt xem tập dữ liệu Stack Overflow trong giao diện người dùng BigQuery trên web, hãy làm theo các bước sau:

  1. Mở tập dữ liệu Stack Overflow. Giao diện người dùng web BigQuery sẽ mở ra trong Bảng điều khiển Google Cloud Platform và hiển thị thông tin về tập dữ liệu Stack Overflow.
  2. Trong bảng điều hướng , hãy chọn bigquery-public-data. Trình đơn này sẽ mở rộng để liệt kê các tập dữ liệu công khai. Mỗi tập dữ liệu bao gồm một hoặc nhiều bảng.
  3. Di chuyển xuống rồi chọn stackoverflow. Trình đơn này sẽ mở rộng để liệt kê các bảng trong tập dữ liệu Stack Overflow.
  4. Chọn huy hiệu để xem giản đồ cho bảng huy hiệu. Ghi lại tên của các trường trong bảng.
  5. Phía trên Tên trường, hãy nhấp vào Xem trước để xem dữ liệu mẫu cho bảng huy hiệu.

Để biết thêm thông tin về tất cả các tập dữ liệu công khai có trong BigQuery, hãy xem Tập dữ liệu công khai của Google BigQuery.

Truy vấn tập dữ liệu Stack Overflow

Việc duyệt xem tập dữ liệu là một cách hay để hiểu rõ dữ liệu mà bạn đang làm việc, nhưng truy vấn tập dữ liệu mới là điểm nổi bật của BigQuery. Phần này hướng dẫn bạn cách chạy các truy vấn BigQuery. Bạn không cần biết bất kỳ ngôn ngữ SQL nào tại thời điểm này. Bạn có thể sao chép và dán các cụm từ tìm kiếm bên dưới.

Để chạy một truy vấn, hãy hoàn tất các bước sau:

  1. Ở gần phía trên cùng bên phải của bảng điều khiển GCP, hãy chọn Soạn truy vấn mới.
  2. Trong vùng văn bản Query editor (Trình chỉnh sửa truy vấn), hãy sao chép và dán truy vấn SQL sau. BigQuery xác thực truy vấn và giao diện người dùng trên web sẽ hiển thị dấu kiểm màu xanh lục bên dưới vùng văn bản để cho biết cú pháp hợp lệ.
SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10
  1. Chọn Chạy. Truy vấn này trả về số lượng bài đăng hoặc câu hỏi trên Stack Overflow được đăng mỗi năm.

4. Tạo bảng mới

Trong phần trước, bạn đã truy vấn các tập dữ liệu công khai mà BigQuery cung cấp cho bạn. Trong phần này, bạn sẽ tạo một bảng mới trong BigQuery từ một bảng hiện có. Bạn sẽ tạo một bảng mới có dữ liệu được lấy mẫu từ bảng posts_questions của tập dữ liệu công khai Stack Overflow, sau đó truy vấn bảng này.

Tạo tập dữ liệu mới

Để tạo và tải dữ liệu bảng vào BigQuery, trước tiên, hãy tạo một tập dữ liệu BigQuery để lưu trữ dữ liệu bằng cách hoàn tất các bước sau:

  1. Trong bảng điều hướng của bảng điều khiển GCP, hãy chọn tên dự án được tạo trong quá trình thiết lập.
  2. Ở bên phải, trong bảng điều khiển chi tiết, hãy chọn Tạo tập dữ liệu.

acc6378c49622323.png

  1. Trong hộp thoại Tạo tập dữ liệu, đối với Mã tập dữ liệu, hãy nhập stackoverflow. Giữ nguyên tất cả các chế độ cài đặt mặc định khác rồi nhấp vào OK.

7a2dfd8bcb8f259a.png

Tạo bảng mới với các bài đăng trên Stack Overflow năm 2018

Giờ đây, sau khi tạo một tập dữ liệu BigQuery, bạn có thể tạo một bảng mới trong BigQuery. Để tạo một bảng có dữ liệu từ một bảng hiện có, bạn sẽ truy vấn tập dữ liệu bài đăng trên Stack Overflow năm 2018 và ghi kết quả vào một bảng mới bằng cách hoàn tất các bước sau:

  1. Ở gần phía trên cùng bên phải của bảng điều khiển GCP, hãy chọn Soạn truy vấn mới.

9ca55f544e8da8bd.png

  1. Trong vùng văn bản Trình chỉnh sửa truy vấn, hãy sao chép và dán truy vấn SQL sau đây để tạo một bảng mới. Đây là một câu lệnh DDL.
CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Chọn Chạy. Truy vấn này sẽ tạo một bảng mới questions_2018 trong tập dữ liệu stackoverflow trong dự án của bạn, chứa dữ liệu thu được từ việc chạy một truy vấn trên tập dữ liệu BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Truy vấn Bảng mới có bài đăng trên Stack Overflow năm 2018

Giờ đây, bạn đã tạo một bảng BigQuery. Hãy chạy một truy vấn để trả về các bài đăng trên Stack Overflow có câu hỏi và tiêu đề cùng với một số số liệu thống kê khác, chẳng hạn như số câu trả lời, bình luận, lượt xem và lượt yêu thích. Hãy hoàn tất các bước sau:

  1. Ở gần phía trên cùng bên phải của bảng điều khiển GCP, hãy chọn Soạn truy vấn mới.
  2. Trong vùng văn bản Query editor (Trình chỉnh sửa truy vấn), hãy sao chép và dán truy vấn SQL sau
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Chọn Chạy. Cụm từ tìm kiếm này trả về những câu hỏi trên Stack Overflow được tạo trong tháng 1 năm 2018, được gắn thẻ android cùng với câu hỏi và một số số liệu thống kê khác.
  2. Theo mặc định, BigQuery sẽ lưu kết quả truy vấn vào bộ nhớ đệm. Chạy truy vấn tương tự và bạn sẽ thấy BigQuery mất ít thời gian hơn nhiều để trả về kết quả vì BigQuery trả về kết quả từ bộ nhớ đệm.
  3. Chạy lại truy vấn tương tự nhưng lần này tắt tính năng lưu vào bộ nhớ đệm của BigQuery. Chúng ta sẽ tắt bộ nhớ đệm cho phần còn lại của phòng thí nghiệm để so sánh hiệu suất một cách công bằng với các bảng được phân vùng và phân cụm. Các bảng này sẽ chạy trong các phần tiếp theo. Trong trình chỉnh sửa truy vấn, hãy nhấp vào Tuỳ chọn khác rồi chọn Cài đặt truy vấn. Cài đặt truy vấn
  4. Trong phần Lựa chọn ưu tiên về bộ nhớ đệm, hãy bỏ đánh dấu Sử dụng kết quả trong bộ nhớ đệm. Lựa chọn kết quả được lưu vào bộ nhớ đệm
  5. Trong kết quả truy vấn, bạn sẽ thấy thời gian cần thiết để hoàn tất truy vấn và lượng dữ liệu được xử lý để nhận được kết quả.

f197b022b4276338.png

5. Tạo và truy vấn bảng phân vùng

Trong phần trước, bạn đã tạo một bảng mới trong BigQuery bằng dữ liệu từ bảng posts_questions bằng cách sử dụng tập dữ liệu công khai của Stack Overflow. Chúng tôi đã truy vấn tập dữ liệu này khi tắt tính năng lưu vào bộ nhớ đệm và quan sát hiệu suất truy vấn. Trong phần này, bạn sẽ tạo một bảng được phân vùng mới từ bảng posts_questions của cùng một tập dữ liệu công khai của Stack Overflow và quan sát hiệu suất truy vấn.

Bảng phân vùng là một bảng đặc biệt được chia thành các phân đoạn (gọi là phân vùng) giúp bạn dễ dàng quản lý và truy vấn dữ liệu. Thông thường, bạn có thể chia các bảng lớn thành nhiều phân vùng nhỏ hơn bằng cách sử dụng thời gian nhập dữ liệu hoặc cột DẤU THỜI GIAN/NGÀY hoặc cột SỐ NGUYÊN. Chúng ta sẽ tạo một bảng được phân vùng theo NGÀY.

Tìm hiểu thêm về bảng được phân vùng tại đây.

Tạo bảng phân vùng mới với các bài đăng trên Stack Overflow năm 2018

Để tạo một bảng được phân vùng có dữ liệu từ một bảng hoặc truy vấn hiện có, bạn sẽ truy vấn tập dữ liệu bài đăng trên Stack Overflow năm 2018 và ghi kết quả vào một bảng mới. Hãy hoàn tất các bước sau:

b9d0ca4df0881f58.png

  1. Ở gần phía trên cùng bên phải của bảng điều khiển GCP, hãy chọn Soạn truy vấn mới.

9ca55f544e8da8bd.png

  1. Trong vùng văn bản Trình chỉnh sửa truy vấn, hãy sao chép và dán truy vấn SQL sau đây để tạo một bảng mới. Đây là một câu lệnh DDL.
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Chọn Chạy. Truy vấn này sẽ tạo một bảng mới questions_2018_partitioned trong tập dữ liệu stackoverflow trong dự án của bạn, chứa dữ liệu thu được từ việc chạy một truy vấn trên tập dữ liệu BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions

Truy vấn Bảng được phân vùng bằng các bài đăng trên Stack Overflow năm 2018

Giờ đây, bạn đã tạo một bảng được phân vùng BigQuery. Hãy chạy truy vấn tương tự, lần này là trên bảng được phân vùng, để trả về các bài đăng trên Stack Overflow có câu hỏi và tiêu đề cùng với một số số liệu thống kê khác, chẳng hạn như số lượng câu trả lời, bình luận, lượt xem và lượt yêu thích. Hãy hoàn tất các bước sau:

  1. Ở gần phía trên cùng bên phải của bảng điều khiển GCP, hãy chọn Soạn truy vấn mới.
  2. Trong vùng văn bản Query editor (Trình chỉnh sửa truy vấn), hãy sao chép và dán truy vấn SQL sau
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Chọn Chạy khi tắt tính năng lưu vào bộ nhớ đệm của BigQuery (xem phần trước để biết cách tắt bộ nhớ đệm của BigQuery). Cụm từ tìm kiếm này trả về những câu hỏi trên Stack Overflow được tạo trong tháng 1 năm 2018, được gắn thẻ android cùng với câu hỏi và một số số liệu thống kê khác.
  2. Trong kết quả truy vấn, bạn sẽ thấy thời gian cần thiết để hoàn tất truy vấn và lượng dữ liệu được xử lý để nhận được kết quả.

ef01144374069823.png

Bạn sẽ thấy hiệu suất của truy vấn có bảng được phân vùng tốt hơn so với bảng không được phân vùng vì BigQuery cắt tỉa các phân vùng, tức là chỉ quét các phân vùng cần thiết để xử lý ít dữ liệu hơn và chạy nhanh hơn. Điều này giúp tối ưu hoá chi phí truy vấn và hiệu suất truy vấn.

6. Tạo và truy vấn bảng được phân cụm

Trong phần trước, bạn đã tạo một bảng được phân vùng trong BigQuery bằng dữ liệu từ bảng posts_questions trong tập dữ liệu công khai của Stack Overflow. Chúng tôi đã truy vấn bảng này khi tắt tính năng lưu vào bộ nhớ đệm và quan sát hiệu suất truy vấn với cả bảng không phân vùng và bảng phân vùng. Trong phần này, bạn sẽ tạo một bảng được phân cụm mới từ bảng posts_questions của cùng một tập dữ liệu công khai Stack Overflow và quan sát hiệu suất truy vấn.

Khi một bảng được phân cụm trong BigQuery, dữ liệu bảng sẽ tự động được sắp xếp dựa trên nội dung của một hoặc nhiều cột trong giản đồ của bảng. Các cột mà bạn chỉ định được dùng để sắp xếp dữ liệu có liên quan. Khi dữ liệu được ghi vào một bảng được phân cụm, BigQuery sẽ sắp xếp dữ liệu bằng cách sử dụng các giá trị trong cột phân cụm. Các giá trị này được dùng để sắp xếp dữ liệu thành nhiều khối trong bộ nhớ BigQuery. Thứ tự của các cột được phân cụm sẽ xác định thứ tự sắp xếp của dữ liệu. Khi dữ liệu mới được thêm vào một bảng hoặc một phân vùng cụ thể, BigQuery sẽ tự động phân cụm lại ở chế độ nền để khôi phục thuộc tính sắp xếp của bảng hoặc phân vùng.

Tìm hiểu thêm về cách sử dụng bảng được phân cụm tại đây.

Tạo bảng được phân cụm mới với các bài đăng trên Stack Overflow năm 2018

Trong phần này, bạn sẽ tạo một bảng mới được phân vùng trên creation_date và được phân cụm trên cột tags dựa trên mẫu truy cập truy vấn. Để tạo một bảng được phân cụm có dữ liệu từ một bảng hoặc truy vấn hiện có, bạn sẽ truy vấn bảng bài đăng trên Stack Overflow năm 2018 và ghi kết quả vào một bảng mới bằng cách hoàn tất các bước sau:

e7d9acc0dc3b9d79.png

  1. Ở gần phía trên cùng bên phải của bảng điều khiển GCP, hãy chọn Soạn truy vấn mới.

9ca55f544e8da8bd.png

  1. Trong vùng văn bản Trình chỉnh sửa truy vấn, hãy sao chép và dán truy vấn SQL sau đây để tạo một bảng mới. Đây là một câu lệnh DDL.
#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. Chọn Chạy. Truy vấn này sẽ tạo một bảng mới questions_2018_clustered trong tập dữ liệu stackoverflow trong dự án của bạn, chứa dữ liệu thu được từ việc chạy truy vấn trên bảng BigQuery Stack Overflowbigquery-public-data.stackoverflow.posts_questions. Bảng mới được phân vùng theo creation_date và được phân cụm theo cột tags.

Truy vấn Bảng được phân cụm bằng các bài đăng trên Stack Overflow năm 2018

Bây giờ bạn đã tạo một bảng được phân cụm BigQuery, hãy chạy lại truy vấn tương tự, lần này là trên bảng được phân vùng và phân cụm, để trả về các bài đăng trên Stack Overflow có câu hỏi và tiêu đề cùng với một số số liệu thống kê khác, chẳng hạn như số câu trả lời, bình luận, lượt xem và lượt yêu thích. Hãy hoàn tất các bước sau:

  1. Ở gần phía trên cùng bên phải của bảng điều khiển GCP, hãy chọn Soạn truy vấn mới.
  2. Trong vùng văn bản Query editor (Trình chỉnh sửa truy vấn), hãy sao chép và dán truy vấn SQL sau
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. Chọn Chạy khi tắt tính năng lưu vào bộ nhớ đệm của BigQuery (xem phần trước để biết cách tắt bộ nhớ đệm của BigQuery). Cụm từ tìm kiếm này trả về những câu hỏi trên Stack Overflow được tạo trong tháng 1 năm 2018, được gắn thẻ android cùng với câu hỏi và một số số liệu thống kê khác.
  2. Trong kết quả truy vấn, bạn sẽ thấy thời gian cần thiết để hoàn tất truy vấn và lượng dữ liệu được xử lý để nhận được kết quả.

85e3c30d6fb3d547.png

Với bảng được phân vùng và phân cụm, truy vấn đã quét ít dữ liệu hơn so với bảng được phân vùng hoặc bảng không được phân vùng. Cách tổ chức dữ liệu bằng cách phân vùng và phân cụm sẽ giảm thiểu lượng dữ liệu mà các worker của khe cắm quét, từ đó cải thiện hiệu suất truy vấn và tối ưu hoá chi phí.

7. Dọn dẹp

Nếu không có ý định tiếp tục làm việc với tập dữ liệu stackoverflow, bạn nên xoá tập dữ liệu đó và xoá dự án mà bạn đã tạo cho lớp học lập trình này.

Xoá tập dữ liệu BigQuery

Để xoá tập dữ liệu BigQuery, hãy thực hiện các bước sau:

  1. Chọn tập dữ liệu stackoverflow trong bảng điều hướng bên trái của BigQuery .
  2. Trong bảng chi tiết, hãy chọn Xoá tập dữ liệu. 67b0f5cb740cb2ec.png
  3. Trong hộp thoại Xoá tập dữ liệu, hãy nhập stackoverflow rồi chọn Xoá để xác nhận rằng bạn muốn xoá tập dữ liệu.

Xoá dự án

Để xoá dự án GCP mà bạn đã tạo cho lớp học lập trình này, hãy thực hiện các bước sau:

  1. Trong trình đơn điều hướng của GCP, hãy chọn IAM & Admin (Quản trị và quản lý danh tính và quyền truy cập).
  2. Trong bảng điều hướng, hãy chọn Cài đặt.
  3. Trong bảng chi tiết, hãy xác nhận rằng dự án hiện tại của bạn là dự án mà bạn đã tạo cho lớp học lập trình này rồi chọn Tắt.
  4. Trong hộp thoại Tắt dự án, hãy nhập mã dự án (không phải tên dự án) cho dự án của bạn rồi chọn Tắt để xác nhận.

Xin chúc mừng! Giờ thì bạn đã biết

  • Cách sử dụng giao diện người dùng web BigQuery để tạo bảng mới từ các bảng hiện có
  • Cách tạo và truy vấn bảng được phân vùng và phân cụm
  • Cách phân vùng và phân cụm giúp tối ưu hoá hiệu suất và chi phí truy vấn

Xin lưu ý rằng bạn không cần thiết lập hoặc quản lý các cụm để làm việc với tập dữ liệu.