Truy cập dữ liệu sử dụng để thực hành cho lesson này theo 1 trong các cách sau:

Download và cài đặt mysql (google hướng dẫn trên mạng, nên cài đặt thêm MySQL Workbench để viết lệnh và sử dụng MySQL). Bạn vẫn hoàn toàn có thể sử dụng 1 hệ quản trị cơ sở dữ liệu khác (VD: SQL Server, PostgreSQL, …) tuy nhiên có thể sẽ có 1 vài điểm khác biệt nho nhỏ (nhưng không ảnh hưởng trọng yếu tới kiến thức và thực hành của bài học này). Sau đó tải các file .csv và .txt tại link sau và import vào cơ sở dữ liệu MySQL để tạo các bảng: https://github.com/tuanphan92/datatute-datasets/tree/main/JobSeeker/Lesson1
Sử dụng Google Colab notebook sau đây để truy vấn dữ liệu mà không cần cài đặt gì trên máy tính cá nhân: https://colab.research.google.com/drive/1uXaxzmPLI_EcXq4vRo0D9rFKeeuWvAn4?usp=sharing

1 Vì sao phải kiểm tra dữ liệu

Trước khi sử dụng bất kỳ một bộ dữ liệu nào ta đều cần thực hiện các bước kiểm tra nhằm:

Tìm hiểu về nội dung của những dữ liệu được lưu trữ trong các bảng
Xác thực xem những thông tin về bộ dữ liệu mà ta được cung cấp có chính xác hay không. Thường ta sẽ lấy thông tin về bộ dữ liệu thông qua từ điển dữ liệu, Entity Relationship Diagram, các bảng hoặc công cụ lưu trữ metadata của bộ dữ liệu, …và từ những người có kinh nghiệm đã từng sử dụng bộ dữ liệu đó
Kiểm tra các vấn đề về chất lượng dữ liệu

Nếu không kiểm tra kĩ bộ dữ liệu, ta sẽ dễ:

Lấy, xử lý và sử dụng dữ liệu sai cách, sai mục đích do không hiểu rõ dữ liệu hoặc do bỏ sót các vấn đề về chất lượng dữ liệu
Phân tích đúng phương pháp nhưng kết quả phân tích sai do chưa xử lý các vấn đề về chất lượng dữ liệu

2 Primary Key (khóa chính) và Foreign Keys (các khóa ngoại)

2.1 Khóa chính

là một cột hoặc tập hợp các cột mà chứa các giá trị duy nhất cho mỗi bản ghi trong một bảng. Nói cách khác thì khóa chính giúp xác định tính duy nhất của mỗi bản ghi.

Giá trị của Khóa chính trong mỗi bản ghi (dòng) phải là duy nhất và không được phép chứa giá trị NULL.
Mỗi bảng chỉ có 1 khóa chính, được tạo thành từ 1 hoặc nhiều cột của bảng

2.1.1 Surrogate key and Natural key

Surrogate key là khóa mà không có ý nghĩa nghiệp vụ, chỉ đơn giản dùng để định danh cho bản ghi, và thường ở dạng số nguyên tăng tuần tự

Ví dụ: Với bảng dữ liệu dưới đây, CustomerID là surrogate key

CustomerID	CustomerName	NationalIdentityCard	CustomerAddress
1	Nguyen Van A	001095061288	Thanh Xuan, Hanoi
2	Nguyen Thi B	001085081148	Quan 1, HCM City
3	Le Van C	001087081241	Tay Ho, Hanoi

Natural key (hay còn gọi là business key) là khóa chính mà sử dụng 1 hoặc nhiều cột sẵn có của bảng (các cột này thường mang ý nghĩa về mặt business)

Ví dụ: trong bảng lưu trữ thông tin về các phòng ban của 1 công ty dưới đây, bộ cột CompanyID + DepartmentName đóng vai trò làm khóa chính và là 1 natural key/business key

CompanyID DepartmentName NoEmployees

1 Finance 12

2 Operation 25

3 Finance 8

CompanyID	DepartmentName	NoEmployees
1	Finance	12
2	Operation	25
3	Finance	8

2.1.2 Cách kiểm tra khóa chính

Dùng UI hoặc lệnh SQL để kiểm tra các table constraint của bảng, từ đó ta thấy được cột nào/nhóm cột nào được khai báo là khóa chính
Nếu bảng không được khai báo khóa chính hoặc ta không có quyền truy cập để xem constraints của bảng, ta kiểm tra để tìm ra cột/nhóm cột đóng vai trò làm khóa chính thủ công. Cách kiểm tra như sau:

SELECT
  Col1, Col2, Col3 --đây là nhóm cột ta dự đoán là khóa chính
FROM Table1
GROUP BY Col1, Col2, Col3
HAVING COUNT(1) > 1 --nếu kết quả trả ra không có gì thì tức là ta dự đoán đúng khóa chính

2.2 Khóa ngoại (FK)

Là 1 cột (hoặc một nhóm cột) trong 1 bảng mà dùng để trỏ tới khóa chính của 1 bảng khác.

2.2.1 Cách kiểm tra khóa ngoại

Ta kiểm tra bằng cách JOIN 2 bảng với nhau

SELECT
  Tbl1.PkCol,
  Tbl1.FkCol,
  Tbl2.PkCol
FROM Tbl1
LEFT JOIN Tbl2
  ON Tbl1.FkCol = Tbl2.PkCol

/*
Sau khi JOIN 2 bảng với nhau, ta kiểm tra số lượng bản ghi đã JOIN thành công.
Nếu một lượng lớn bản ghi ở 2 bảng không JOIN được với nhau thì ta dựa trên
ý nghĩa của 2 bảng này mà điều tra thêm
*/

2.3 Bài tập kiểm tra PK, FK

Cho 2 bảng dữ liệu sau, hãy:

chỉ ra các khóa chính và khóa ngoại của từng bảng
chúng là natural key hay surrogate key

TRANSACTION table
AmountUSD	CustomerID	Timestamp	AmountLocalCurrency	UserID	TransactionStatus
292.8	133	2023-04-01 12:46:00	244	2	1
1097.6	342	2023-04-01 16:27:00	45,000,000	16	1
5.1	133	2023-04-02 09:05:00	123,000	1	0
48	25	2023-04-02 09:05:00	null	7	1
5.1	133	2023-04-02 09:05:00	123,000	1	1
48	41	2023-04-02 09:08:00	null	8	3

USER table
UserID	CustomerID	Email	Phone
2	133	nguyenvana@gmail.com	0922837165
16	342	nguyenvanb@gmail.com	0924455526
7	25	nguyenthic@hotmail.com	0922839488
8	41	lethid@yahoo.com	0945837263
1	133	hoangthie@gmail.com	0945998888

2 bảng dữ liệu này có vấn đề gì?

Đáp án:

/*
TRANSACTION table:
- khóa chính: không có bộ cột nào đảm bảo tính duy nhất của mỗi bản ghi, vì vậy bảng này thiếu khóa chính
- khóa ngoại: UserID

USER table:
- khóa chính: UserID hoặc Email. Nếu Email là khóa chính thì nó là 1 natural key. Nếu UserID là khóa chính thì nó có thể là 1 natural key (nếu nó thể hiện mã định danh khách hàng tại 1 hệ thống nguồn nào đó) hoặc là 1 surrogate key (nếu nó chỉ được tạo ra để định danh bản ghi cho bảng TRANSACTION)

*/

3 Data types - loại dữ liệu

Nắm được loại dữ liệu của từng cột trong bảng là rất quan trọng, nó giúp ta:

Viết lệnh để query và biến đổi dữ liệu được chính xác
Đôi khi có thể gợi ý cho ta về các lỗi dữ liệu tiềm ẩn. Ví dụ cột CustomerName ở bảng CUSTOMER có độ dài 50 ký tự, nó có thể hoạt động tốt với tên của khách hàng cá nhân, nhưng nếu bảng CUSTOMER chứa cả khách hàng tổ chức (doanh nghiệp) thì rất có thể tên khách hàng sẽ không được thể hiện đầy đủ vì tên của một số doanh nghiệp rất dài

3.1 Cách kiểm tra loại dữ liệu của bảng

3.1.1 Cách 1: sử dụng UI

3.1.2 Cách 2: sử dụng câu lệnh SQL

--MySQL
DESCRIBE yourSchemaName.yourTableName;

--SQL Server
SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'yourSchemaName'
  AND TABLE_NAME = 'youTableName'

4 Data completeness and data distribution - Kiểm tra tính đầy đủ và sự phân phối của dữ liệu

Để hiểu rõ về dữ liệu của bảng, ta cần nắm được mức độ đầy đủ của dữ liệu là đến đâu và phân phối dữ liệu của các cột dữ liệu chính. Các cột dữ liệu quan trọng luôn bao gồm các cột chỉ thời gian (Date, Timestamp).

Kiểm tra tính đầy đủ của dữ liệu thường được thực hiện bằng cách:

Kiểm tra số lượng bản ghi bị NULL/None/NaN/NA trong các cột quan trọng
JOIN các bảng với nhau để kiểm tra xem có thiếu dữ liệu hay không

Kiểm tra phân phối của dữ liệu bằng cách:

Nhóm dữ liệu theo các cột nhất định và đếm số lượng bản ghi (GROUP BY + COUNT)
Tính các quantiles (nên dùng Python hoặc R sẽ dễ hơn SQL)
Vẽ biểu đồ Histogram hoặc Boxplot (cần dùng thêm công cụ khác ngoài SQL)

4.1 Bài tập

Kiểm tra tính đầy đủ và phân phối dữ liệu của bộ dataset gồm 2 bảng CONTRACT và CUSTOMER:

CONTRACT: https://raw.githubusercontent.com/tuanphan92/datatute-datasets/main/JobSeeker/Lesson1/CONTRACT.csv

CUSTOMER: https://raw.githubusercontent.com/tuanphan92/datatute-datasets/main/JobSeeker/Lesson1/CUSTOMER.csv

Đáp án

--# CUSTOMER
select *
from CUSTOMER;

--completeness and PK
--there are 2 cases of duplicated CustomerID
select
  count(1), --41
  count(distinct CustomerID) --39 --> duplicated CustomerID
from CUSTOMER
where 1=1
  --and CustomerID is null --> 0 rows
  --and Name is null --> 0 rows
  --and Age is null --> 0 rows
;

select
  CustomerID, --111, 150
  count(1)
from CUSTOMER
group by CustomerID
having count(1) > 1
;

--# CONTRACT
select *
from CONTRACT;

--completeness and PK
--4 records with null MaturityDate
select
  count(1), --39
  count(distinct ContractID) --39
from CONTRACT
where 1=1
  --and Amount is null --> 0
  --and EffectiveDate is null --> 0
  --and MaturityDate is null --> 4
  --and CustomerID is null --> 0
;

--check Amount column
select
  Amount,
  count(1) as NoContracts
from CONTRACT
group by Amount
order by count(1) desc

--check date columns
--distribution
--there is 1 contract where EffectiveDate > MaturityDate
select
  EffectiveDate,
  count(1) as NoContracts
from CONTRACT
group by EffectiveDate
order by EffectiveDate
--order by count(1) desc
;

select
  MaturityDate,
  count(1) as NoContracts
from CONTRACT
group by MaturityDate
order by MaturityDate
;

select --> 1 case
  *
from CONTRACT
where EffectiveDate > MaturityDate

--CustomerID
select
  CustomerID,
  count(1) as NoContracts
from CONTRACT
group by CustomerID
order by count(1) desc
;

--# Join 2 tables to check completeness
--there are CustomerIDs in CONTRACT table but not in CUSTOMER table --> data issue
--there are CustomerIDs that have not had any contract --> probably not data issue
select
  a.ContractID,
  a.CustomerID as CustomerID_contract,
  b.CustomerID as CustomerID_customer
from CONTRACT a
full outer join CUSTOMER b
  on a.CustomerID = b.CustomerID
where a.CustomerID is null
  or b.CustomerID is null
order by a.CustomerID, b.CustomerID
;

5 Data accuracy - tính chính xác của dữ liệu

Dùng kiến thức nghiệp vụ (business knowledge) để kiểm tra tính chính xác của dữ liệu, thường thông qua việc:

Kiểm tra các giá trị của các cột (như đã làm ở phần data distribution)
Sử dụng công thức tính toán để kiểm tra các cột có kiểu dữ liệu định lượng (integer, float, …)
Sử dụng một số quy tắc để kiểm tra các cột có kiểu dữ liệu định tính (text, boolean, …với text nếu muốn kiểm tra các pattern phức tạp, ta sử dụng REGEX)

5.1 Bài tập 1

Kiểm tra tính chính xác của dữ liệu trong bảng CUSTOMER_FULL: https://raw.githubusercontent.com/tuanphan92/datatute-datasets/main/JobSeeker/Lesson1/CUSTOMER_FULL.csv

Đáp án

--# CUSTOMER_FULL data accuracy check

--Age and DateOfBirth
--there are 3 cases of customers having Age = 123, this is because of the default DateOfBirth value 1900-01-01
select
  Age,
  count(distinct CustomerID) as NoCustomers  
from datatute1.job_seekers.CUSTOMER_FULL
group by Age
order by Age
;

select *
from datatute1.job_seekers.CUSTOMER_FULL
where Age = 123
;

--City
--there is inconsistent naming of cities (e.g. HA NOI, Ha Noi, HN, Hanoi)
select
  City,
  count(1) as NoRows
from datatute1.job_seekers.CUSTOMER_FULL
group by City
order by City

5.2 Bài tập 2:

Ta có bảng EMAIL_LIST lưu trữ danh sách email của khách hàng. Ta cần đánh dấu những email nào có định dạng đúng và những email nào có định dạng sai hoặc thực tế không phải email của khách hàng trước khi gửi email marketing để tránh phát sinh chi phí vì gửi thư tới các địa chỉ email không tồn tại.

Sử dụng các hàm liên quan tới xử lý text để thực hiện, gợi ý sử dụng Regular Expression.

EMAIL_LIST: <https://raw.githubusercontent.com/tuanphan92/datatute-datasets/main/JobSeeker/Lesson1/EMAIL_LIST.csv>

Note: Để dạy về Regular Expression (REGEX) thì sẽ mất 1 vài lesson, tuy nhiên các bạn có thể tham khảo 1 số nguồn sau để tự bổ túc nhanh về REGEX. Đây là 1 nội dung kiến thức quan trọng và sẽ giúp ích các bạn rất nhiều khi xử lý các cột dạng text/string:

Nguồn học rất hay và kèm thực hành về REGEX: https://regexlearn.com/learn/regex101
Xây dựng và test các pattern về REGEX: https://regexr.com/

Đáp án

--# Use Regex to find wrong emails in EMAIL_LIST table
with to_lowercase as (
  select
    CustomerID,
    lower(Email) as Email
  from datatute1.job_seekers.EMAIL_LIST
)
, check1 as (
select
  *,
  case
    when regexp_contains(Email, r".+@.+[.].+") = true then 'correct'
    else 'wrong'
  end as check_email_format
from to_lowercase
),
check2 as (
select
  *,
  case
    when regexp_contains(Email, r"khkhong|khachhangkhong") = true then 'wrong'
    else 'correct'
  end as check_email_dummy_values
from check1
)
select
  CustomerID,
  Email,
  case
    when check_email_format = 'correct' and check_email_dummy_values = 'correct' then 'correct'
    else 'wrong'
  end as check_email
from check2
;

6 Data consistency

Trong trường hợp 1 dữ liệu tồn tại ở nhiều hơn 1 cột hoặc 1 bảng thì ta cần kiểm tra xem dữ liệu ở các cột, bảng đó có giống nhau không và cột, bảng nào mới là nguồn tốt nhất để lấy dữ liệu

7 Data insert/update mechanism - cơ chế cập nhật dữ liệu

Các bảng dữ liệu thường sẽ được cập nhật theo 1 tần suất nhất định. Để sử dụng chính xác dữ liệu trong bảng, ta cần biết bảng đó được cập nhật theo cơ chế và tần suất như thế nào, bao gồm:

Cơ chế insert dữ liệu mới vào bảng: incremental load (load thêm dữ liệu mới) hay overwrite (mỗi lần load thì xóa toàn bộ dữ liệu của bảng đi và load lại)
Có update - cập nhật - dữ liệu cũ hay không, nếu có thì cơ chế như thế nào. Ví dụ:
- cập nhật dữ liệu cũ khi có yêu cầu từ đơn vị kinh doanh, hoặc
- mỗi lần load dữ liệu thì ngoài dữ liệu mới ra thì load lại cả dữ liệu của 3 ngày gần nhất,
- …
Tần suất insert, cập nhật dữ liệu và thời gian dữ liệu sẵn sàng. Ví dụ: cập nhật hàng ngày, dữ liệu sẵn sàng lúc 5h sáng

Job Seeker - Lesson 1
Kiểm tra dữ liệu trước khi sử dụng

Tuan Phan
https://datatute.vn
tuanphan.datatute@gmail.com

06/2023

1 Vì sao phải kiểm tra dữ liệu

2 Primary Key (khóa chính) và Foreign Keys (các khóa ngoại)

2.1 Khóa chính

2.1.1 Surrogate key and Natural key

2.1.2 Cách kiểm tra khóa chính

2.2 Khóa ngoại (FK)

2.2.1 Cách kiểm tra khóa ngoại

2.3 Bài tập kiểm tra PK, FK

3 Data types - loại dữ liệu

3.1 Cách kiểm tra loại dữ liệu của bảng

3.1.1 Cách 1: sử dụng UI

3.1.2 Cách 2: sử dụng câu lệnh SQL

4 Data completeness and data distribution - Kiểm tra tính đầy đủ và sự phân phối của dữ liệu

4.1 Bài tập

5 Data accuracy - tính chính xác của dữ liệu

5.1 Bài tập 1

5.2 Bài tập 2:

6 Data consistency

7 Data insert/update mechanism - cơ chế cập nhật dữ liệu

Job Seeker - Lesson 1 Kiểm tra dữ liệu trước khi sử dụng

Tuan Phan https://datatute.vn tuanphan.datatute@gmail.com

06/2023

1 Vì sao phải kiểm tra dữ liệu

2 Primary Key (khóa chính) và Foreign Keys (các khóa ngoại)

2.1 Khóa chính

2.1.1 Surrogate key and Natural key

2.1.2 Cách kiểm tra khóa chính

2.2 Khóa ngoại (FK)

2.2.1 Cách kiểm tra khóa ngoại

2.3 Bài tập kiểm tra PK, FK

3 Data types - loại dữ liệu

3.1 Cách kiểm tra loại dữ liệu của bảng

3.1.1 Cách 1: sử dụng UI

3.1.2 Cách 2: sử dụng câu lệnh SQL

4 Data completeness and data distribution - Kiểm tra tính đầy đủ và sự phân phối của dữ liệu

4.1 Bài tập

5 Data accuracy - tính chính xác của dữ liệu

5.1 Bài tập 1

5.2 Bài tập 2:

6 Data consistency

7 Data insert/update mechanism - cơ chế cập nhật dữ liệu

Job Seeker - Lesson 1
Kiểm tra dữ liệu trước khi sử dụng

Tuan Phan
https://datatute.vn
tuanphan.datatute@gmail.com