Học Qlik Sense qua ví dụ: Load dữ liệu từ website với web crawler

Một vấn để phổ biến khi xây dựng BI dashboard là tuy hệ thống nguồn có cung cấp kết nối dữ liệu, nhưng không có cơ chế cho phép tải dữ liệu hàng loạt mà chỉ trả về một phần nhỏ dữ liệu mỗi lần truy vấn. Trường hợp này thường gặp ở các nguồn dữ liệu dạng web. Ví dụ, một website quản lý bán hàng có danh sách các đơn hàng, mỗi khi click vào một đơn hàng sẽ hiển thị chi tiết đơn hàng đó. Tuy nhiên, vì lý do nào đó website lại không có tính năng trích xuất hàng loạt đơn hàng để lập báo cáo hoặc trao đổi dữ liệu với hệ thống khác.

Với những hệ thống như vậy, giải pháp lý tưởng nhất là có thể lấy dữ liệu trực tiếp từ database của website, hoặc từ API mà website cung cấp (tham khảo bài viết Load dữ liệu từ web service với Qlik REST connector). Tuy nhiên, nếu 2 giao diện dữ liệu này đều không có sẵn, phương án duy nhất còn lại là vào từng link trong website, thu thập các phần dữ liệu và tổng hợp lại thành bộ dữ liệu hoàn chỉnh.

Một phần mềm hoặc đoạn mã để tự động hóa công việc này được gọi là một web crawler (hay còn có tên gọi khác là bot hay spider). Cơ chế của web crawler là truy cập vào một hoặc một nhóm website ban đầu (seeds), từ đó duyệt qua các liên kết trong trang đến khi toàn bộ website được duyệt hết. Ở mỗi lần mở liên kết, web crawler phân tích cấu trúc HTML của trang để thu thập dữ liệu cần thiết và nhận diện liên kết đến các trang con.

Trong bài viết này, chúng ta sẽ cùng xây dựng một web crawler đơn giản chỉ với webfile connection và các hàm có sẵn trong Qlik Sense. Web crawler sẽ duyệt qua website Numbeo (numbeo.com/cost-of-living/) để lấy thông tin về chi phi sinh hoạt của hơn 200 quốc gia trên thế giới.

Hiểu về nguồn dữ liệu

Numbeo là một website tham khảo các chỉ số xã hội như chi phí sinh hoạt, tỷ lệ tội phạm, mức độ ô nhiễm … theo quốc gia và theo thành phố. Website cũng cho phép so sánh các chỉ số này giữa các quốc gia và thành phố với nhau.

Website có cung cấp data API cho mục đích tải dữ liệu hàng loạt (numbeo.com/common/api.jsp), tuy nhiên Numbeo API tính phí từ 220-2000 USD/ tháng tùy gói license. Trong ví dụ ở bài viết này, chúng ta không cần dùng toàn bộ dữ liệu của Numbeo mà chỉ muốn tải nhóm tiêu chí chi phí sinh hoạt (cost of living) ở tất cả các quốc gia.

Trong trang chủ numbeo.com, mục Countries chứa liên kết đến dữ liệu cost of living chi tiết của từng nước. Có hơn 200 link tương ứng với 200 quốc gia và vùng lãnh thổ (ví dụ numbeo.com/cost-of-living/country_result.jsp?country=Argentina ). Ta sẽ viết một đoạn script duyệt qua 200 link này và tổng hợp dữ liệu từ mỗi trang.

Xây dựng ứng dụng

Lấy danh sách URL từ trang chủ

Để nhận diện được liên kết đến các trang con, từ trang chủ củanumbeo.com, right click > View page source (hoặc Ctrl+U trong Chrome) để xem trang ở dạng raw HTML. Ở khoảng dòng 670, có thể thấy link đến các quốc gia có dạng như sau:

 <a href = "country_result.jsp?country=Argentina">Argentina</a><br/>

Ở bước đầu tiên, chúng ta sẽ nhận diện các link này từ dạng HTML của trang gốc, sau đó tách ra tên quốc gia (Argentina) và liên kết tương ứng (country_result.jsp?country=Argentina), ghép với địa chỉ của trang gốc (https://www.numbeo.com/cost-of-living/) để tạo thành URL hoàn chỉnh. Thực hiện các bước sau:

1. Tạo app mới từ Qlik Sense Desktop. Mở Data Load Editor, tạo webfile connection mới với URL tới trang gốc (https://www.numbeo.com/cost-of-living/), đặt tên là WebFile.

2. Click  trên WebFile để mở cửa sổ Select Data. Chọn File format = Fixed record. Tùy chọn này giúp webfile connection tải trang gốc ở dạng raw HTML và không cố gắng nhận diện bảng dữ liệu từ trang gốc. Click Insert script.

Đoạn script vừa tạo có dạng như sau:

LOAD
     "@1:n"
 FROM [lib://WebFile]
 (fix, utf8, no labels);

3. Trong trang raw HTML tải về, ta chỉ quan tâm đến các dòng <a href = “country_result.jsp?… như trình bày ở trên. Ở mỗi dòng, hàm TextBetween() sẽ được sử dụng để tách tên quốc gia và link tương ứng. Sửa lại đoạn script trên như sau:

Countries:
LOAD
     "@1:n" as ColText,
     TextBetween("@1:n", '">', '</a><br/>') as Country,
     TextBetween("@1:n", '<a href = "', '">') as CountryLink
     FROM [lib://WebFile] (fix, utf8, no labels)
WHERE WildMatch("@1:n",'<a href = "country_result.jsp?*');

4. Reload app vừa tạo, mở Data Model Viewer. Ta có bảng Countries như sau

Duyệt danh sách URL để lấy dữ liệu từ các trang con

1. Trước hết ta sẽ load thử một nước để tìm hiểu cấu trúc mỗi trang con. Tạo một webfile connection mới tới link của 1 quốc gia, ví dụ Argentina (https://www.numbeo.com/cost-of-living/country_result.jsp?country=Argentina).

2. Trong cửa sổ Select Data của connection mới, chọn File format = HTML, ta thấy Qlik Sense nhận diện được 5 bảng HTML trong trang, đánh số từ @1 đến @5. Lần lượt chọn thử từng bảng, lưu ý dữ liệu cần load nằm ở bảng @3. Click chọn bảng @3 , click Insert script.

Đoạn script được tạo ra bởi Select Data wizard:

LOAD
     @1,
     @2,
     @3
 FROM [lib://test]
 (html, utf8, no labels, table is @3);

3. Sửa lại đoạn script trên như sau (phần in nghiêng đậm là phần giữ lại từ đoạn script tự động ở trên):

Data_Temp:
 LOAD * INLINE [Item, Price, Range];
 FOR i=0 to NoOfRows('Countries')-1
   LET vCountry=Peek('Country',$(i),'Countries');
   LET vCountryURL=Peek('CountryLink',$(i),'Countries');
   Trace 'Loading $(vCountry)';
   Concatenate(Data_Temp) 
       LOAD 
         Item,
         Num(SubField(Price,' ',1)) as Price,
         SubField(Price,' ',2) as Currency,
         '$(vCountry)' as Country,
         If(Price='[ Edit ]',Item,Peek(Category)) as Category;
     LOAD
         @1 AS Item,
         @2 AS Price,
         @3 AS Range
     FROM [lib://WebFile] 
     (URL IS [https://www.numbeo.com/cost-of-living/$(vCountryURL)], html, utf8, no labels, table is @3);
 NEXT;

Một số thay đổi trong đoạn script mới:

  • Tạo bảng dữ liệu Data và header (tên 3 cột) cho bảng bằng câu lệnh LOAD * INLINE
  • Tách phần text ở cột thứ 2 (ví dụ ‘100.00 USD’ ) thành 2 cột: giá trị số (100) và loại tiền (USD) bằng các hàm SubField
  • Đặt câu lệnh Load ở bước (2) vào vòng lặp FOR…NEXT. Ở mỗi vòng lặp:
    • URL gốc https://www.numbeo.com/cost-of-living/ được ghép với một giá trị của field CountryLink trong bảng Countries để tạo thành URL hoàn chỉnh đến trang dữ liệu của 1 quốc gia. URL này được cập nhật động vào webfile với câu lệnh URL IS
    • Dữ liệu Cost of Living được load từ trang con và ghép (concatenate) vào bảng Data
  • Trong bảng Data_Temp, do cấu trúc của bảng HTML gốc, có một số dòng không chứa thông tin về một chi phí cụ thể (item) mà là nhóm chi phí (category). Ví dụ Restaurants hay Markets trong hình dưới.

Đây là bảng dữ liệu dạng header – details, thường gặp khi load dữ liệu nguồn dạng báo cáo vào Qlik Sense. Cách biến đổi về bảng dữ liệu phẳng tương tự như ví dụ trong bài viết Xử lý tập dữ liệu thưa (sparse data) trong Qlik: khi môt giá trị xuất hiện ở dòng header, giá trị đó sẽ lặp lại ở các dòng details cho đến dòng header kế tiếp. Dấu hiệu nhận biết các dòng header trong ví dụ này là đoạn text [ Edit ] ở cột thứ 2. Câu lệnh If(Price='[ Edit ]’… chính là để nhận diện các dòng header.

4. Các giá trị header đã được copy vào các dòng details tương ứng. Bước còn lại là xóa các dòng header khỏi bảng dữ liệu cuối cùng với đoạn script sau:

Data:
   NoConcatenate LOAD * Resident Data_Temp WHERE Currency <> 'Edit';
Drop Table Data_Temp;

Thiết kế dashboard

Dữ liệu Cost of Living của các quốc gia đã được tải về từ Numbeo như mục tiêu ban đầu. Bạn có thể lựa chọn biểu đồ phù hợp tùy theo nhu cầu phân tích. Ở đây mình chỉ trình bày dữ liệu trên một pivot table đơn giản với filter pane để lựa chọn quốc gia:

Bạn có thể áp dụng kiến thức trong các bài viết trước để mở rộng ứng dụng. Một số ý tưởng:

  • Load dữ liệu tỷ giá ngoại tệ từ website ngân hàng (xem ví dụ trong bài viết Kết nối Qlik với nguồn dữ liệu (P1) ) để có cơ sở so sánh giá cả sinh hoạt giữa các quốc gia với nhau và ước lượng chi phí tương đương giữa các quốc gia (ví dụ 1kg gạo ở Hàn Quốc có giá khoảng 80k tiền Việt Nam)
  • Áp dụng Alternate State (xem bài viết Master Items trong Qlik Sense (P2) ), cho phép người dùng so sánh bằng cách lựa chọn 2 quốc gia từ 2 filter pane của cùng field Country.

Tham khảo ứng dụng mẫu trong bài viết tại VietQlikies Public Share – Blog 31