Truy vấn tập dữ liệu Wikipedia trong BigQuery

1. Tổng quan

BigQuery là cơ sở dữ liệu phân tích chi phí thấp do Google quản lý hoàn toàn, NoOps. Với BigQuery, bạn có thể truy vấn nhiều terabyte và terabyte dữ liệu mà không cần có bất kỳ cơ sở hạ tầng nào để quản lý mà không cần quản trị viên cơ sở dữ liệu. BigQuery sử dụng SQL quen thuộc và có thể tận dụng mô hình trả tiền theo mức dùng. BigQuery giúp bạn tập trung vào việc phân tích dữ liệu để tìm ra những thông tin chi tiết có ý nghĩa.

Trong lớp học lập trình này, bạn sẽ khám phá tập dữ liệu Wikipedia bằng BigQuery.

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

• Cách sử dụng BigQuery
• Cách tải một tập dữ liệu thực tế vào BigQuery
• Cách viết truy vấn để thu thập thông tin chi tiết về một tập dữ liệu lớn

Bạn cần có

• Một dự án trên Google Cloud
• Một trình duyệt, chẳng hạn như Google Chrome hoặc Firefox

Khảo sát

Bạn sẽ sử dụng hướng dẫn này như thế nào?

Chỉ có thể đọc Đọc và hoàn thành bài tập

Bạn đánh giá thế nào về trải nghiệm sử dụng Google Cloud?

Người mới tập Trung cấp Thành thạo

2. Thiết lập và yêu cầu

Bật BigQuery

Nếu chưa có Tài khoản Google, bạn phải tạo một tài khoản.

1. Đăng nhập vào Google Cloud Console rồi chuyển đến BigQuery. Bạn cũng có thể mở trực tiếp giao diện người dùng web BigQuery bằng cách nhập URL sau đây vào trình duyệt.

https://console.cloud.google.com/bigquery

2. Chấp nhận điều khoản dịch vụ.
3. Trước khi có thể sử dụng BigQuery, bạn phải tạo một dự án. Làm theo lời nhắc để tạo dự án mới.

Chọn tên dự án và ghi lại mã dự án.

Mã dự án là tên riêng biệt của tất cả dự án trên Google Cloud. Lớp này sẽ được đề cập sau trong lớp học lập trình này là PROJECT_ID.

Lớp học lập trình này sử dụng tài nguyên BigQuery trong hạn mức hộp cát BigQuery. Không bắt buộc phải có tài khoản thanh toán. Nếu sau này muốn loại bỏ giới hạn hộp cát, bạn có thể thêm tài khoản thanh toán bằng cách đăng ký dùng thử miễn phí Google Cloud.

Bạn tải tập dữ liệu Wikipedia trong phần tiếp theo.

3. Tạo tập dữ liệu

Trước tiên, hãy tạo một tập dữ liệu mới trong dự án. Một tập dữ liệu bao gồm nhiều bảng.

1. Để tạo tập dữ liệu, hãy nhấp vào tên dự án trong ngăn tài nguyên, sau đó nhấp vào Tạo tập dữ liệu:

2. Nhập lab làm Mã tập dữ liệu:

3. Nhấp vào Tạo tập dữ liệu để tạo tập dữ liệu trống.

4. Tải dữ liệu bằng chương trình dòng lệnh bq

Kích hoạt Cloud Shell

1. Trong Cloud Console, hãy nhấp vào Kích hoạt Cloud Shell .

Nếu trước đây bạn chưa từng khởi động Cloud Shell, thì bạn sẽ thấy một màn hình trung gian (dưới màn hình đầu tiên) mô tả về ứng dụng này. Nếu trường hợp đó xảy ra, hãy nhấp vào Tiếp tục (và bạn sẽ không thấy thông báo đó nữa). Màn hình một lần đó sẽ có dạng như sau:

Quá trình cấp phép và kết nối với Cloud Shell chỉ mất vài phút.

Máy ảo này chứa tất cả các công cụ phát triển mà bạn cần. Dịch vụ này cung cấp thư mục gốc 5 GB ổn định và chạy trong Google Cloud, giúp nâng cao đáng kể hiệu suất và khả năng xác thực của mạng. Trong lớp học lập trình này, đa số mọi người đều có thể thực hiện chỉ bằng một trình duyệt hoặc Chromebook.

Sau khi kết nối với Cloud Shell, bạn sẽ thấy mình đã được xác thực và dự án đã được đặt thành mã dự án.

2. Chạy lệnh sau trong Cloud Shell để xác nhận rằng bạn đã được xác thực:

gcloud auth list

Kết quả lệnh

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Lưu ý: Công cụ dòng lệnh gcloud là công cụ dòng lệnh mạnh mẽ và hợp nhất trong Google Cloud. Nó được cài đặt sẵn trong Cloud Shell. Bạn sẽ thấy công cụ này hỗ trợ hoàn thành thẻ. Để biết thêm thông tin, hãy xem bài viết tổng quan về công cụ dòng lệnh gcloud.

3. Chạy lệnh sau trong Cloud Shell để xác nhận rằng lệnh gcloud biết về dự án của bạn:

gcloud config list project

Kết quả lệnh

[core]
project = <PROJECT_ID>

Nếu chưa, bạn có thể thiết lập chế độ này bằng lệnh sau:

gcloud config set project <PROJECT_ID>

Kết quả lệnh

Updated property [core/project].

Tải dữ liệu vào BigQuery

Để thuận tiện cho bạn, một số dữ liệu cho ngày 10 tháng 4 năm 2019, từ tập dữ liệu lượt xem trang Wikimedia có sẵn trên Google Cloud Storage tại gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz. Tệp dữ liệu là tệp CSV của GZip. Bạn có thể tải tệp này trực tiếp bằng tiện ích dòng lệnh bq. Trong lệnh tải, bạn cũng mô tả giản đồ của tệp.

Bạn có thể tìm thấy Mã dự án trong Bảng điều khiển API hoặc từ dòng lệnh: "gcloud config list" và tìm giá trị của "project".

Ngoài ra, trong lần đầu chạy bq, có thể bạn sẽ được yêu cầu chọn một dự án mặc định. Sau khi chọn, bạn có thể chạy lại lệnh trên để tải dữ liệu.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

Quá trình tải dữ liệu có thể mất vài phút. Chờ quá trình này hoàn tất.

Bạn đã sử dụng một số cách nâng cao để tải tệp số lượt xem trang:

• Đặt --source_format CSV để cho biết tệp cần được phân tích cú pháp dưới dạng tệp CSV. Bước này là bước không bắt buộc vì CSV là định dạng mặc định.
• Đặt --field_delimiter " " để cho biết rằng có một dấu cách được dùng để phân cách các trường.
• Đặt --allow_jagged_rows để bao gồm các hàng có ít cột hơn và bỏ qua các lỗi khi tải tệp CSV.
• Đặt --quote "" để cho biết các chuỗi không được trích dẫn.
• Đặt --max_bad_records 3 để bỏ qua tối đa 3 lỗi khi phân tích cú pháp tệp CSV.

Bạn có thể tìm hiểu thêm về dòng lệnh bq trong tài liệu này.

5. Xem trước tập dữ liệu

Trong bảng điều khiển BigQuery, hãy mở một trong các bảng mà bạn vừa tải.

1. Mở rộng dự án.
2. Mở rộng tập dữ liệu.
3. Chọn bảng.

Bạn có thể xem giản đồ bảng trong thẻ Giản đồ. 4. Tìm hiểu lượng dữ liệu trong bảng bằng cách chuyển đến thẻ Chi tiết:

5. Mở thẻ Xem trước để xem các hàng được chọn trong bảng.

6. Soạn một truy vấn

1. Nhấp vào Soạn truy vấn mới:

Thao tác này sẽ mở Trình chỉnh sửa truy vấn:

2. Tìm tổng số lượt xem Wikimedia trong khoảng thời gian từ 2 giờ đến 3 giờ chiều ngày 10 tháng 4 năm 2019 bằng cách viết truy vấn này:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Nhấp vào Run (Chạy):

Trong vài giây, kết quả sẽ xuất hiện ở dưới cùng và cũng cho bạn biết lượng dữ liệu đã được xử lý:

Truy vấn này đã xử lý 123,9 MB, mặc dù bảng là 691,4 MB. BigQuery chỉ xử lý các byte từ những cột được dùng trong truy vấn, vì vậy, tổng lượng dữ liệu được xử lý có thể nhỏ hơn đáng kể so với kích thước bảng. Nhờ tính năng nhóm và phân vùng, lượng dữ liệu được xử lý có thể giảm hơn nữa.

Bạn có thể xử lý/truy vấn tối đa 1TB dữ liệu miễn phí mỗi tháng. Hãy xem trang giá của BigQuery để biết thêm chi tiết.

7. Truy vấn nâng cao khác

Tìm chế độ xem trang Wikipedia

Tập dữ liệu Wikimedia chứa lượt xem trang của tất cả các dự án trên Wikimedia (bao gồm cả Wikipedia, Wiktionary, Wikibooks và WikiTrích dẫn). Thu hẹp truy vấn chỉ còn các trang Wikipedia tiếng Anh bằng cách thêm câu lệnh WHERE:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

Xin lưu ý rằng khi truy vấn một cột bổ sung wiki, lượng dữ liệu được xử lý đã tăng từ 124 MB lên 204 MB.

BigQuery hỗ trợ nhiều mệnh đề SQL quen thuộc, chẳng hạn như CONTAINS, GROUP BY, ORDER BY và một số hàm tổng hợp. Ngoài ra, bạn cũng có thể sử dụng biểu thức chính quy để truy vấn các trường văn bản! Hãy thử một đáp án:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Tìm hiểu thêm về cú pháp của BigQuery trong tài liệu tham khảo về truy vấn BigQuery.

Truy vấn trên nhiều bảng

Bạn có thể chọn một loạt bảng để tạo liên kết bằng cách sử dụng bảng ký tự đại diện.

1. Trước tiên, tạo bảng thứ hai để truy vấn bằng cách tải số lượt xem trang của giờ tiếp theo vào một bảng mới:

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. Trong Trình chỉnh sửa truy vấn, hãy truy vấn trên cả hai bảng mà bạn đã tải bằng cách truy vấn các bảng bằng "pageviews_2019" làm tiền tố:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Bạn có thể lọc các bảng một cách có chọn lọc hơn bằng cột giả _TABLE_DOCUMENT. Truy vấn này giới hạn ở các bảng tương ứng với ngày 10 tháng 4.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. Dọn dẹp

Bạn có thể xoá tập dữ liệu bạn đã tạo bằng lệnh bq rm nếu muốn. Sử dụng cờ -r để xoá mọi bảng có trong đó.

bq rm -r lab

9. Xin chúc mừng!

Bạn đã sử dụng BigQuery và SQL để truy vấn tập dữ liệu về lượt xem trang trên Wikipedia trong thực tế. Bạn có quyền truy vấn các tập dữ liệu cỡ petabyte!

Tìm hiểu thêm

• Hãy tham khảo subreddit của BigQuery để biết cách những người khác đang sử dụng BigQuery hiện nay.
• Tìm các tập dữ liệu công khai có trong BigQuery.
• Tìm hiểu cách tải dữ liệu vào BigQuery.