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, NoOps và do Google toàn quyền quản lý. Với BigQuery, bạn có thể truy vấn hàng terabyte dữ liệu mà không cần quản lý cơ sở hạ tầng và 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 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 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

Bản khảo sát

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

Chỉ đọ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 bắt đầu 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ở giao diện người dùng web BigQuery trực tiếp bằng cách nhập URL sau 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. Bạn phải tạo một dự án thì mới có thể sử dụng BigQuery. 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à một tên riêng biệt cho tất cả các dự án trên Google Cloud. Sau này trong lớp học lập trình này, chúng ta sẽ gọi nó là PROJECT_ID.

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

Bạn sẽ 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 một tập dữ liệu, hãy nhấp vào tên dự án trong ngăn tài nguyên, rồi 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 một 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 chưa từng khởi động Cloud Shell, bạn sẽ thấy một màn hình trung gian (bên dưới phần hiển thị đầu tiên) mô tả về Cloud Shell. Nếu vậy, hãy nhấp vào Tiếp tục (và bạn sẽ không bao giờ thấy màn hình này nữa). Sau đây là giao diện của màn hình xuất hiện một lần:

Quá trình cung cấp và kết nối với Cloud Shell chỉ mất vài giây.

Máy ảo này được trang bị tất cả các công cụ phát triển mà bạn cần. Nền tảng này cung cấp một thư mục chính có dung lượng 5 GB và chạy trong Google Cloud, giúp tăng cường đáng kể hiệu suất mạng và hoạt động xác thực. Bạn có thể thực hiện hầu hết, nếu không muốn nói là tất cả, công việc trong lớp học lập trình này 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 rằng mình đã được xác thực và dự án đã được đặt thành mã dự án của bạ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

Đầu ra của 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. Công cụ này được cài đặt sẵn trong Cloud Shell. Bạn sẽ thấy tính năng hỗ trợ hoàn tất bằng phím Tab. Để biết thêm thông tin, hãy xem phần 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

Đầu ra của lệnh

[core]
project = <PROJECT_ID>

Nếu không, bạn có thể đặt nó bằng lệnh sau:

gcloud config set project <PROJECT_ID>

Đầu ra của 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 trong tập dữ liệu lượt xem trang của Wikimedia có 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 nén bằng GZip. Bạn có thể tải trực tiếp tệp này 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 qua dòng lệnh: "gcloud config list" và tìm giá trị của "project".

Ngoài ra, khi chạy bq lần đầu tiên, hệ thống có thể yêu cầu bạn 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ố lựa chọn 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 không bắt buộc vì CSV là định dạng mặc định.
• Đặt --field_delimiter " " để cho biết rằng một khoảng trắng được dùng để phân tách các trường.
• Đặt --allow_jagged_rows để thêm các hàng có ít cột hơn và bỏ qua lỗi trong khi tải tệp CSV.
• Đặt --quote "" để cho biết các chuỗi không được đặt trong dấu ngoặc kép.
• Đặt --max_bad_records 3 để bỏ qua tối đa 3 lỗi trong 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.

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 xem có bao nhiêu dữ liệu trong bảng bằng cách chuyển đến thẻ Chi tiết:

5. Mở thẻ Xem trước để xem một số hàng trong bảng.

6. Soạn 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 trên Wikimedia trong khoảng thời gian từ 2 giờ chiều đến 3 giờ chiều ngày 10 tháng 4 năm 2019 bằng cách viết truy vấn sau:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

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

Sau vài giây, kết quả sẽ xuất hiện ở dưới cùng và cho 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 có kích thước 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ể ít hơn đáng kể so với kích thước bảng. Với tính năng phân cụ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 1 TB dữ liệu miễn phí mỗi tháng. Hãy xem trang định giá BigQuery để biết thêm thông tin chi tiết.

7. Các truy vấn nâng cao khác

Tìm lượt xem trang trên Wikipedia

Tập dữ liệu Wikimedia chứa số lượt xem trang cho tất cả các dự án của Wikimedia (bao gồm cả Wikipedia, Wiktionary, Wikibooks và Wikiquotes). 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 bằng cách 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 trong những cách sau:

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 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 dải bảng để tạo hợp nhất bằng cách sử dụng bảng ký tự đại diện.

1. Trước tiên, hãy tạo một bảng thứ hai để truy vấn bằng cách tải 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 cả hai bảng mà bạn đã tải bằng cách truy vấn các bảng có "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 chọn lọc hơn bằng cột giả _TABLE_SUFFIX. 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

Nếu muốn, hãy xoá tập dữ liệu mà bạn đã tạo bằng lệnh bq rm. Sử dụng cờ -r để xoá mọi bảng mà cờ này chứa.

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 lượt xem trang Wikipedia trong thế giới thực. Bạn có thể truy vấn các tập dữ liệu có quy mô petabyte!

Tìm hiểu thêm

• Hãy xem subreddit BigQuery để biết cách người khác 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.