Bài học này nhằm hệ thống hoá các câu lệnh SQL cần thiết cho data engineering. Đây không phải là một bài học dạy cơ bản cách sử dụng từng câu lệnh một mà sẽ chỉ đưa ra thêm thông tin và ví dụ với những câu lệnh mà mình cho là không quá dễ hiểu đối với người mới bắt đầu với lĩnh vực data engineering hoặc có những điểm lưu ý quan trọng mà người mới cần nắm được.
Đây là nhóm các câu lệnh dùng để định nghĩa và thay đổi các cấu trúc trong cơ sở dữ liệu.
Nhóm này gồm các câu lệnh CREATE, DROP,
ALTER, TRUNCATE dùng để tạo mới, thay đổi, xoá
schema, bảng, views, index, partition, constraint.
Đây là nhóm các câu lệnh dùng để tạo mới dữ liệu, biến đổi và xoá dữ liệu.
Nhóm này gồm các câu lệnh như INSERT,
UPDATE, DELETE, MERGE,
UPSERT, …
Ta sẽ cùng xem một ví dụ về MERGE và UPSERT
(các câu lệnh khác các bạn có thể tự đọc và tìm hiểu
Đây là nhóm câu lệnh dùng để lấy dữ liệu ra khỏi database và thưc hiện một số biến đổi dữ liệu.
Nhóm này gồm các câu lệnh:
SELECT
DISTINCT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
Khi viết lệnh để query dữ liệu từ các bảng trong database, ta cần lưu ý Order of execution/operation trong SQL. Xem thêm tại bài học “Lesson 2” ở khoá học Tổng ôn SQL cho người mới bắt đầu.
Đây là nhóm câu lệnh để thực hiện và quản lý các giao dịch (transaction).
Một transaction (giao dịch) bao gồm một hoặc nhiều tác vụ. Mỗi transaction bắt đầu bằng một tác vụ và kết thúc khi tất cả các tác vụ đều được thực hiện thành công. Nếu có bất kỳ tác vụ nào thất bại thì cả transaction sẽ thất bại (fail). Vì vậy, một transaction chỉ có thể có một trong hai kết quả là thành công hoặc thất bại (chứ không có thành công một phần). Việc sử dụng transaction giúp đảm bảo ETL pipeline không lưu dữ liệu không hoàn chỉnh vào database.
Nhóm này bao gồm các câu lệnh như BEGIN TRANSACTION,
COMMIT, ROLLBACK, SAVEPOINT,
…
Ví dụ: Giả sử ta có bảng dữ liệu giao dịch thanh toán hoá đơn của khách hàng như sau trong SQL Server database:
DROP TABLE IF EXISTS mydb.dbo.payment;
DROP TABLE IF EXISTS mydb.dbo.etl_log;
-- Create payment table
CREATE TABLE mydb.dbo.payment(
payment_id INT PRIMARY KEY,
customer_id INT,
message VARCHAR(50)
)
-- Insert data to table
INSERT INTO mydb.dbo.payment
(payment_id, customer_id, message)
VALUES
(1, 11, 'thanh toan hop dong'),
(2, 11, 'thanh toan hop dong'),
(3, 22, 'thanh toan hop dong'),
(4, 33, 'thanh toan hop dong'),
(5, 9999, 'testing')
;
SELECT * FROM mydb.dbo.payment;| payment_id | customer_id | message |
|---|---|---|
| 1 | 11 | thanh toan hop dong |
| 2 | 11 | thanh toan hop dong |
| 3 | 22 | thanh toan hop dong |
| 4 | 33 | thanh toan hop dong |
| 5 | 9999 | testing |
Giả sử ta cần hot fix cập nhật dữ liệu ở bảng này, do ta phát hiện có
lỗi dữ liệu. Ta cần xoá giáo dịch có payment_id = 5 và cần
cập nhật lại cột message của giao dịch có
payment_id = 4
DELETE FROM mydb.dbo.payment WHERE payment_id = 5
UPDATE mydb.dbo.payment
SET message = 'thanh toan hop dong XYZ12345 cho khach hang VIP voi chiet khau 20 phan tram'
WHERE payment_id = 4Tuy nhiên, ta sẽ thấy một vấn đề nghiêm trọng ở đây, đó là câu lệnh
DELETE FROM đã được chạy thành công, xoá đi bản ghi có
payment_id = 5, nhưng câu lệnh UPDATE thì lại
báo lỗi sau:
String or binary data would be truncated in table 'mydb.dbo.payment', column 'message'. Truncated value: 'thanh toan hop dong XYZ12345 cho khach hang VIP vo'.
The statement has been terminated.Kiểm tra dữ liệu ở bảng payment ta thấy
message của payment_id = 4 đúng là vẫn chưa
được update.
| payment_id | customer_id | message |
|---|---|---|
| 1 | 11 | thanh toan hop dong |
| 2 | 11 | thanh toan hop dong |
| 3 | 22 | thanh toan hop dong |
| 4 | 33 | thanh toan hop dong |
Lí do là vì cột message có kiểu dữ liệu VARCHAR(50) và
chỉ chứa được 50 ký tự, trong khi message ta muốn cập nhật vào lại quá
dài.
Như vậy ta rơi vào tình huống bảng bị cập nhật nửa vời và sẽ có thể phát sinh nhiều vấn đề.
Để tránh tình trạng này xảy ra, ta sẽ cho tất cả các ETL logic vào
trong một “transaction” của SQL Server database. Ta sử dụng lệnh
BEGIN TRANSACTION để bắt đầu transaction và
COMMIT TRANSACTION hoặc ROLLBACK TRANSACTION
để kết thúc transaction. Ta dùng thêm các lệnh try
(BEGIN TRY...END TRY), catch
(BEGIN CATCH...END CATCH) để xử lý exception. Khi logic
trong (BEGIN TRY...END TRY) chạy bình thường thì ta sẽ
COMMIT TRANSACTION , còn khi có lỗi (exception) xảy ra thì
logic trong (BEGIN CATCH...END CATCH) sẽ được thực hiện.
Tại đây ta thực hiện ROLLBACK TRANSACTION.
DROP TABLE IF EXISTS mydb.dbo.etl_log
CREATE TABLE mydb.dbo.etl_log(
log_id INT PRIMARY KEY IDENTITY,
job_name VARCHAR(50),
start_time DATETIME,
end_time DATETIME,
status VARCHAR(10),
error_message VARCHAR(MAX)
)
DECLARE @log_id INT;
-- log etl job start
INSERT INTO mydb.dbo.etl_log
(job_name, start_time, end_time, status, error_message)
VALUES('hot fix payment table', GETDATE(), NULL, 'RUNNING', NULL)
BEGIN TRANSACTION
BEGIN TRY
-- capture inserted identity
SET @log_id = SCOPE_IDENTITY();
SELECT @log_id
-- update payment table
DELETE FROM mydb.dbo.payment WHERE payment_id = 5
UPDATE mydb.dbo.payment
SET message = 'thanh toan hop dong XYZ12345 cho khach hang VIP voi chiet khau 20 phan tram'
WHERE payment_id = 4
-- log etl job end
UPDATE mydb.dbo.etl_log
SET end_time = GETDATE(), status = 'SUCCESS'
WHERE log_id = @log_id
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- log etl job end
UPDATE mydb.dbo.etl_log
SET end_time = GETDATE(), status = 'FAILED', error_message = ERROR_MESSAGE()
WHERE log_id = @log_id
END CATCHKiểm tra bảng payment ta thấy dữ liệu không có gì thay
đổi (payment_id = 5 vẫn tồn tại và payment_id
= 4 vẫn chưa bị update). Kiểm tra bảng etl_log ta thấy như
sau:
| log_id | job_name | start_time | end_time | status | error_message |
|---|---|---|---|---|---|
| 1 | hot fix payment table | 2025-09-19 04:25:54.623 | 2025-09-19 04:25:54.633 | FAILED | String or binary data would be truncated in table ‘mydb.dbo.payment’, column ‘message’. Truncated value: ‘thanh toan hop dong XYZ12345 cho khach hang VIP vo’. |
Vậy là lỗi phát sinh khi chạy lệnh UPDATE đã khiến cho
transaction thất bại và SQL Server roll back lại các câu lệnh đã thực
hiện và dữ liệu của bảng payment trở về như cũ, ta không
còn bị rơi vào tình trạng một vài câu lệnh đã được thực hiện còn các câu
lệnh khác thì chưa được thực hiện nữa.
Đây là nhóm câu lệnh liên quan tới quản trị và bảo mật. VD: tạo, cấp và phân quyền truy cập, che dấu (masking) dữ liệu, …
Nhóm này bao gồm các câu lệnh như GRANT,
REVOKE, …
Việc nắm vững cách sử dụng JOIN và WINDOW function sẽ giúp Data Engineer xử lý được các logic biến đổi dữ liệu phức tạp.
Xem bài học “Lesson 02” ở khoá học Tổng ôn SQL cho người mới bắt đầu để nắm được một số lưu ý quan trọng khi sử dụng JOIN và WINDOW function.
CTE giúp ta viết code dễ hơn và rõ ràng hơn, đặc biệt là trong những trường hợp phải dùng nhiều subquery để xử lý các logic biến đổi dữ liệu phức tạp.
Bảng tạm cho phép ta lưu trữ dữ liệu tạm thời để có thể sử dụng nhiều lần trong 1 phiên làm việc (session), và cũng giúp ta xử lý các logic biến đổi dữ liệu phức tạp một cách dễ dàng hơn.
Xem bài học “Lesson 01 extra” ở khoá học Tổng ôn SQL cho người mới bắt đầu để ôn tập (mức độ cơ bản) về 2 khái niệm này.
Trong các hệ thống cơ sở dữ liệu quan hệ (vd: MySQL, SQL Server, …) ta có thể chia các function (hàm) thành 2 loại như sau:
Built-in function: Các hàm có sẵn trong hệ thống
User-defined function: Các hàm được tạo ra bởi người dùng
Ví dụ với SQL Server, ta có các built-in function thường dùng như
CAST, COALESCE, DATEADD,
DATEDIFF, COUNT, SUM,
SUBSTRING, LOWER, …
Sau đây ta sẽ tập trung nói về việc tạo các function mà chưa có sẵn trong hệ thống (các user-defined function) để phục vụ cho công việc data engineering. Ta sẽ tập trung vào các loại function mà ta dùng lệnh SQL để tạo ra (chúng sẽ được lưu dưới dạng các object trong database), và sẽ không nói đến các function được tạo ra bằng cách viết code sử dụng các ngôn ngữ lập trình khác (VD: “loadable function” trong MySQL phải được viết bằng C++).
Với user-defined function, ta có thể chia thành các loại chính sau:
Scalar function: Hàm trả ra một giá trị duy nhất, thường được dùng để tính toán hoặc để format dữ liệu.
Table-returning function/Set-returning function: Hàm trả ra kết quả dưới dạng bảng (tức là có thể gồm nhiều dòng, cột), thường sẽ có performance tốt hơn scalar function vì hoạt động tốt với optimiser của database.
Ví dụ trong SQL Server:
Ta có bảng dữ liệu test_customers như sau
USE mydb
DROP TABLE IF EXISTS mydb.dbo.test_customers
CREATE TABLE mydb.dbo.test_customers(
id INT,
name VARCHAR(100),
type VARCHAR(50),
gender VARCHAR(20)
)
INSERT INTO mydb.dbo.test_customers
(id, name, type, gender)
VALUES
(1, 'Tuan', 'VIP', 'Male'),
(2, 'Thanh', 'Normal', 'Male'),
(3, 'Trang', 'VIP', 'Female'),
(4, 'Tung', 'Normal', 'Other'),
(5, 'Linh', 'VIP', 'Female')Ta tạo một số function như sau:
USE mydb
GO
DROP FUNCTION IF EXISTS dbo.fn_GetCustomerByTypeAndGender
DROP FUNCTION IF EXISTS dbo.StandardizeGender
GO
-- Scalar function
CREATE FUNCTION dbo.StandardizeGender
(
@Gender VARCHAR(20)
)
RETURNS VARCHAR(1)
AS
BEGIN
/****************************************************************************************************
Description:
A scalar user-defined function that standardizes various textual representations of gender
into a single-character code. It is designed to ensure consistent gender encoding across
source systems and downstream analytical layers.
Input Parameters:
@Gender VARCHAR(20)
The gender value as provided by the source system (e.g., 'Male', 'Female', 'M', 'F', 'Unknown').
Return Type:
VARCHAR(1)
Returns one of the following standardized gender codes:
'M' – Male
'F' – Female
'O' – Other or Unknown
****************************************************************************************************/
RETURN
CASE
WHEN @Gender = 'Male' THEN 'M'
WHEN @Gender = 'Female' THEN 'F'
ELSE 'O'
END
END
GO
-- table-returning function (the name in SQL Server is table-valued function)
CREATE FUNCTION dbo.fn_GetCustomerByTypeAndGender
(
@CustType VARCHAR(50),
@Gender VARCHAR(20)
)
RETURNS @FilteredCustomers TABLE
(
id INT,
name VARCHAR(100),
type VARCHAR(50),
gender VARCHAR(20)
)
AS
BEGIN
/****************************************************************************************************
Description:
A table-valued user-defined function (TVF) that filters and returns customers
from the dbo.test_customers table based on customer type and gender.
Input Parameters:
@CustType VARCHAR(50)
The customer type to filter on (e.g., 'VIP', 'Normal', 'VVIP', ...).
@Gender VARCHAR(20)
The gender of the customer to filter on (e.g., 'Male', 'Female', 'Other').
Return Type:
TABLE
Returns a table containing customers that match both input parameters.
The returned table schema includes:
- id INT : Unique customer identifier
- name VARCHAR(100) : Customer name
- type VARCHAR(50) : Customer type
- gender VARCHAR(20) : Customer gender
Usage Example:
SELECT *
FROM dbo.fn_GetCustomerByTypeAndGender('VIP', 'Female');
Notes:
Ensure indexes exist on dbo.test_customers.type and dbo.test_customers.gender for optimal performance.
****************************************************************************************************/
INSERT INTO @FilteredCustomers
(id, name, type, gender)
SELECT
id,
name,
type,
gender
FROM dbo.test_customers
WHERE type = @CustType
AND gender = @Gender
RETURN
END
GOTa sử dụng các function vừa tạo như sau:
SELECT
id,
name,
type,
dbo.StandardizeGender(gender) AS gender
FROM dbo.fn_GetCustomerByTypeAndGender('VIP', 'Female')| id | name | type | gender |
|---|---|---|---|
| 3 | Trang | VIP | FALSE |
| 5 | Linh | VIP | FALSE |
Stored procedure là một hoặc một chuỗi các câu lệnh SQL được lưu trữ lại trong database để ta có thể tái sử dụng nhiều lần. Chính vì vậy, stored procedure giúp ích rất nhiều cho các data engineer khi cần triển khai các logic biến đổi dữ liệu và tự động hoá các logic trong ETL pipeline.
Ngoài lợi ích về tái sử dụng code thì stored procedure còn có các lợi ích khác như giúp ta dễ maintain code hơn, giảm tải network traffic giữa server và client (vì tất cả code được thực hiện trong cùng 1 batch thay vì client phải gửi code nhiều lần sang server), … Bạn có thể đọc thêm tại đây: https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver17
Cũng tương tự như với function, ta cũng có những built-in stored procedure và user-defined stored procedure.
Stored procedure có thể có:
các input parameter (tham số đầu vào) để ta truyền vào cho stored procedure một số giá trị
các output parameter để chứa các giá trị mà ta muốn stored procedure trả ra
Ví dụ:
Ta có một ETL pipeline để load dữ liệu hàng ngày cho 1 bảng dim
customer và một bảng fact order. Ta load dữ liệu từ một hệ thống nguồn
vào các bảng staging (có tiền tố stg trong tên bảng), sau
đó load dữ liệu từ bảng staging vào bảng dim/fact tương ứng.
Với bảng dim customer, ta thực hiện full load (mỗi lần load là xoá toàn bộ dữ liệu ở bảng đi và load mới lại).
Với bảng fact order, ta thực hiện incremental load (mỗi lần load chỉ load thêm dữ liệu mới vào bảng, không động đến dữ liệu cũ).
Ta tạo các bảng như sau:
USE mydb
DROP TABLE IF EXISTS mydb.dbo.stg_customers
DROP TABLE IF EXISTS mydb.dbo.d_customers
DROP TABLE IF EXISTS mydb.dbo.stg_orders
DROP TABLE IF EXISTS mydb.dbo.f_orders
DROP TABLE IF EXISTS mydb.dbo.etl_run_log
CREATE TABLE mydb.dbo.stg_orders(
source_order_id INT,
source_customer_id INT,
order_amt FLOAT,
order_datetime DATETIME,
insert_timestamp DATETIME
)
CREATE TABLE mydb.dbo.f_orders(
source_order_id INT PRIMARY KEY,
customer_id INT,
order_amt FLOAT,
order_datetime DATETIME,
insert_timestamp DATETIME
)
CREATE TABLE mydb.dbo.stg_customers(
source_customer_id INT,
name VARCHAR(100),
type VARCHAR(50),
gender VARCHAR(20),
insert_timestamp DATETIME
)
CREATE TABLE mydb.dbo.d_customers(
customer_id INT IDENTITY(1,1) PRIMARY KEY,
source_customer_id INT UNIQUE,
name VARCHAR(100),
type VARCHAR(50),
gender VARCHAR(20),
insert_timestamp DATETIME
)
INSERT INTO mydb.dbo.stg_orders
(source_order_id, source_customer_id, order_amt, order_datetime, insert_timestamp)
VALUES
(11, 333, 2500, '2025-01-18 08:02:00', GETDATE()),
(22, 333, 10000, '2025-01-18 13:25:00', GETDATE()),
(33, 555, 3000, '2025-01-18 14:08:00', GETDATE()),
(44, 111, 1500, '2025-01-19 10:11:00', GETDATE())
INSERT INTO mydb.dbo.stg_customers
(source_customer_id, name, type, gender, insert_timestamp)
VALUES
(111, 'Tuan', 'VIP', 'Male', GETDATE()),
(222, 'Thanh', 'Normal', 'Male', GETDATE()),
(333, 'Trang', 'VIP', 'Female', GETDATE()),
(444, 'Tung', 'Normal', 'Other', GETDATE()),
(555, 'Linh', 'VIP', 'Female', GETDATE())
CREATE TABLE mydb.dbo.etl_run_log(
log_id INT IDENTITY(1,1) PRIMARY KEY,
job_name VARCHAR(200),
status VARCHAR(10),
finished_datetime DATETIME,
error_message VARCHAR(MAX)
)Kiểm tra dữ liệu bảng stg_customers:
| source_customer_id | name | type | gender | insert_timestamp |
|---|---|---|---|---|
| 111 | Tuan | VIP | Male | 2025-09-21 06:48:13.413 |
| 222 | Thanh | Normal | Male | 2025-09-21 06:48:13.413 |
| 333 | Trang | VIP | Female | 2025-09-21 06:48:13.413 |
| 444 | Tung | Normal | Other | 2025-09-21 06:48:13.413 |
| 555 | Linh | VIP | Female | 2025-09-21 06:48:13.413 |
Kiểm tra dữ liệu bảng stg_orders:
| source_order_id | source_customer_id | order_amt | order_datetime | insert_timestamp |
|---|---|---|---|---|
| 11 | 333 | 2500 | 2025-01-18 08:02:00.000 | 2025-09-21 06:48:13.410 |
| 22 | 333 | 10000 | 2025-01-18 13:25:00.000 | 2025-09-21 06:48:13.410 |
| 33 | 555 | 3000 | 2025-01-18 14:08:00.000 | 2025-09-21 06:48:13.410 |
| 44 | 111 | 1500 | 2025-01-19 10:11:00.000 | 2025-09-21 06:48:13.410 |
Ta tạo các stored procedure như sau:
LogETL: để lữu dữ liệu vào bảng
etl_run_log giúp ta theo dõi các pipeline chạy thành công
hay thất bại và lỗi phát sinh là gì
LoadDimCustomers: chứa các logic để load dữ liệu từ
bảng stg_customers vào bảng
d_customers
LoadFactOrders: chứa các logic để load dữ liệu từ
bảng stg_orders vào bảng f_orders
RunDailyETL: stored procedure này dùng để điều phối
thứ tự chạy của các pipeline thông qua việc gọi các stored procedure
khác. Theo thông lệ thì ta nên load dữ liệu cho các bảng dim trước rồi
mới load dữ liệu cho bảng fact. Thứ tự chạy: dim customers -> fact
orders.
DROP PROCEDURE IF EXISTS dbo.LogETL
DROP PROCEDURE IF EXISTS dbo.LoadDimCustomers
DROP PROCEDURE IF EXISTS dbo.LoadFactOrders
DROP PROCEDURE IF EXISTS dbo.RunDailyETL
GO
CREATE PROCEDURE dbo.LogETL
@JobName VARCHAR(200),
@Status VARCHAR(10),
@ErrorMessage VARCHAR(MAX) = NULL
AS
SET NOCOUNT ON
INSERT INTO dbo.etl_run_log
(job_name, status, finished_datetime, error_message)
VALUES
(@JobName, @Status, GETDATE(), @ErrorMessage)
GO
CREATE PROCEDURE dbo.LoadDimCustomers
AS
BEGIN
SET NOCOUNT ON
DECLARE @ETLJobName VARCHAR(100) = 'Load Customers Dimension Table'
DECLARE @ErrorMessage VARCHAR(MAX)
BEGIN TRANSACTION
BEGIN TRY
-- truncate table dim customer as we are using full load
TRUNCATE TABLE mydb.dbo.d_customers
-- insert data into dim customer
INSERT INTO mydb.dbo.d_customers
(source_customer_id, name, type, gender, insert_timestamp)
SELECT
source_customer_id,
name,
type,
gender,
GETDATE() AS insert_timestamp
FROM mydb.dbo.stg_customers
-- log ETL success
EXEC dbo.LogETL @ETLJobName, 'Success'
-- commit transaction
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- log ETL failure
SET @ErrorMessage = ERROR_MESSAGE()
EXEC dbo.LogETL @ETLJobName, 'Failed', @ErrorMessage
END CATCH
END
GO
CREATE PROCEDURE dbo.LoadFactOrders
AS
BEGIN
SET NOCOUNT ON
DECLARE @ETLJobName VARCHAR(100) = 'Load Orders Fact Table'
DECLARE @ErrorMessage VARCHAR(MAX)
BEGIN TRANSACTION
BEGIN TRY
-- insert data into orders fact table using incremental load strategy
WITH new_stg_orders AS(
SELECT
source_order_id,
source_customer_id,
order_amt,
order_datetime
FROM mydb.dbo.stg_orders AS stg_orders
-- logic to get all new records compared to the last time the fact orders table was updated
WHERE order_datetime >= (SELECT COALESCE(MAX(order_datetime), '1900-01-01') FROM mydb.dbo.f_orders)
)
INSERT INTO mydb.dbo.f_orders
(source_order_id, customer_id, order_amt, order_datetime, insert_timestamp)
SELECT
new_stg_orders.source_order_id,
d_customers.customer_id,
new_stg_orders.order_amt,
new_stg_orders.order_datetime,
GETDATE() AS insert_timestamp
FROM new_stg_orders
LEFT JOIN mydb.dbo.f_orders AS f_orders
ON new_stg_orders.source_order_id = f_orders.source_order_id
LEFT JOIN mydb.dbo.d_customers AS d_customers
ON new_stg_orders.source_customer_id = d_customers.source_customer_id
-- filter out duplicated order_id(s) because we implemented >= condition when creating new_stg_orders
WHERE f_orders.source_order_id IS NULL
-- log ETL success
EXEC dbo.LogETL @ETLJobName, 'Success'
-- commit transaction
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- log ETL failure
SET @ErrorMessage = ERROR_MESSAGE()
EXEC dbo.LogETL @ETLJobName, 'Failed', @ErrorMessage
END CATCH
END
GO
CREATE PROCEDURE dbo.RunDailyETL
AS
BEGIN
BEGIN TRY
EXEC dbo.LoadDimCustomers
EXEC dbo.LoadFactOrders
EXEC dbo.LogETL 'Daily ETL', 'Success'
END TRY
BEGIN CATCH
EXEC dbo.LogETL 'Daily ETL', 'Failed'
END CATCH
END
GOTa chạy store procedure RunDailyETL
Kiểm tra dữ liệu bảng etl_run_log
| log_id | job_name | status | finished_datetime | error_message |
|---|---|---|---|---|
| 1 | Load Customers Dimension Table | Success | 2025-09-21 06:49:17.127 | NULL |
| 2 | Load Orders Fact Table | Success | 2025-09-21 06:49:17.153 | NULL |
| 3 | Daily ETL | Success | 2025-09-21 06:49:17.157 | NULL |
Kiểm tra dữ liệu bảng f_orders
| source_order_id | customer_id | order_amt | order_datetime | insert_timestamp |
|---|---|---|---|---|
| 11 | 3 | 2500 | 2025-01-18 08:02:00.000 | 2025-09-21 06:49:17.153 |
| 22 | 3 | 10000 | 2025-01-18 13:25:00.000 | 2025-09-21 06:49:17.153 |
| 33 | 5 | 3000 | 2025-01-18 14:08:00.000 | 2025-09-21 06:49:17.153 |
| 44 | 1 | 1500 | 2025-01-19 10:11:00.000 | 2025-09-21 06:49:17.153 |
Kiểm tra dữ liệu bảng d_customers
| customer_id | source_customer_id | name | type | gender | insert_timestamp |
|---|---|---|---|---|---|
| 1 | 111 | Tuan | VIP | Male | 2025-09-21 06:49:17.123 |
| 2 | 222 | Thanh | Normal | Male | 2025-09-21 06:49:17.123 |
| 3 | 333 | Trang | VIP | Female | 2025-09-21 06:49:17.123 |
| 4 | 444 | Tung | Normal | Other | 2025-09-21 06:49:17.123 |
| 5 | 555 | Linh | VIP | Female | 2025-09-21 06:49:17.123 |
Index là một loại object giúp việc sắp xếp dữ liệu và tìm kiếm dữ liệu nhanh và hiệu quả hơn trong cơ sở dữ liệu quan hệ. Hiểu nôm na thì nếu coi bảng dữ liệu là 1 cuốn sách thì Index cũng giống như mục lục của cuốn sách đó. Index là 1 loại cấu trúc mà giúp ta tìm kiếm dữ liệu trong các data file (là các file mà thực sự chứa các dòng dữ liệu của bảng) nhanh hơn. Index thường được tạo trên table hoặc view.
Một số câu lệnh SQL:
Tạo index:
# Single-column index
CREATE INDEX idx_name ON table_name(column_name);
# Composite index
CREATE INDEX idx_name ON table_name(column_one_name, column_two_name);Xoá index:
Vô hiệu hoá index:
Cách tạo và sử dụng index sao cho chính xác sẽ được trình bày ở một bài học riêng do tính chất phức tạp của chủ đề này.
Trong bài học này, ta sẽ chỉ lưu ý trước tới một số nội dung mang tính chất tổng quát sau:
B-tree index là loại index phổ biến nhất trong các cơ sở dữ liệu quan hệ
Tạo và sử dụng index chỉ là một trong số những cách tối ưu performance của hệ thống cơ sở dữ liệu quan hệ. Không phải bất cứ vấn đề nào về performance cũng có thể được xử lý bằng index, thậm chí nếu tạo và sử dụng index không đúng còn làm giảm performance.
Một cơ sở dữ liệu được thiết kế không tốt thì dù có cố dùng index cũng khó có thể cải thiện được đáng kể performance.
Việc tạo và sử dụng index phải được thực hiện đúng cách và chỉ thực hiện khi cần thiết. Khi ta tạo quá nhiều index (thuật ngữ gọi là “over-indexing”) thì có thể khi đọc (read) dữ liệu thì query chạy nhanh, nhưng mỗi khi ghi (write) dữ liệu thì sẽ có rất nhiều index có liên quan phải thay đổi theo, dẫn tới giảm performance của hệ thống. Trước khi tạo index, ta cần khảo sát và monitor nhu cầu sử dụng của người dùng hay các hệ thống mà sử dụng dữ liệu trong database của ta.
Các query cũng cần được viết đúng cách để tận dụng được index. Một query mà khiến cho hệ quản trị cơ sở dữ liệu có thể sử dụng index để tăng tốc độ tìm kiếm thì gọi là một SARGable query (viết tắt của Search ARGument ABLE).
Xem thông tin về các bảng:
Xem danh sách bảng và các cột của mỗi bảng
# MySQL
SELECT * FROM information_schema.processlist WHERE COMMAND = 'Query';
# SQL Server
SELECT
r.sql_handle,
r.session_id,
r.status,
r.command,
r.start_time,
r.total_elapsed_time,
s.login_name,
s.host_name,
s.program_name,
t.text AS query_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t