Giới thiệu SQL Loader là gì
SQL Loader là công cụ để nạp dữ liệu từ các file ngoại vi vào các bảng trong cơ sở dữ liệu Oracle. Nó có một bộ máy phân tích dữ liệu mạnh mẽ, không giới hạn định dạng dữ liệu trong file dữ liệu. SQL*Loader sử dụng các file sau đây:
File dữ liệu đầu vào(Input data file)
SQL*Loader đọc dữ liệu từ một hoặc nhiều file (hoặc tương đương file trong hệ điều hành) được chỉ định trong control file. Đối với SQL*Loader, dữ liệu trong file được tổ chức thành các record. Một file dữ liệu cụ thể có thể có định dạng record cố định, định dạng record biến thiên hoặc định dạng record luồng. Định dạng record có thể được chỉ định trong control file bằng tham số INFILE. Nếu không có định dạng record được chỉ định, mặc định là định dạng record luồng.
Control file(Control file)
Control file là một file văn bản được viết bằng ngôn ngữ mà SQL*Loader hiểu. Control file cho biết cho SQL*Loader nơi tìm dữ liệu, cách phân tích và hiểu dữ liệu, nơi chèn dữ liệu, vv. Mặc dù không được định nghĩa cụ thể, control file có thể được coi là có ba phần.
Phần đầu tiên chứa thông tin phạm vi phiên làm việc như sau
- Các tùy chọn toàn cầu, chẳng hạn như tên file dữ liệu đầu vào và số record cần bỏ qua.
- Các mệnh đề INFILE để chỉ định vị trí của dữ liệu đầu vào.
- Dữ liệu cần được nạp.
Phần thứ hai bao gồm một hoặc nhiều khối INTO TABLE
Mỗi khối này chứa thông tin về bảng (chẳng hạn như tên bảng và các cột của bảng) mà dữ liệu sẽ được nạp vào.
Phần thứ ba là tùy chọn và nếu có, chứa dữ liệu đầu vào.
File nhật ký(Log file)
Khi SQL*Loader bắt đầu thực hiện, nó tạo ra một file nhật ký. Nếu không thể tạo file nhật ký, quá trình thực thi sẽ kết thúc. File nhật ký chứa một bản tóm tắt chi tiết về quá trình nạp, bao gồm mô tả về bất kỳ lỗi nào xảy ra trong quá trình nạp.
File lỗi (bad file)
File lỗi chứa các record bị từ chối, do SQL*Loader hoặc cơ sở dữ liệu Oracle từ chối. Các record trong file dữ liệu bị từ chối bởi SQL*Loader khi định dạng đầu vào không hợp lệ. Sau khi một record trong file dữ liệu được chấp nhận để xử lý bởi SQL*Loader, nó được gửi đến cơ sở dữ liệu Oracle để chèn vào bảng như một hàng. Nếu cơ sở dữ liệu Oracle xác định rằng hàng là hợp lệ, hàng sẽ được chèn vào bảng. Nếu hàng được xác định là không hợp lệ, record sẽ bị từ chối và SQL*Loader đưa nó vào file lỗi.
File loại bỏ (discard file)
File này chỉ được tạo ra khi cần thiết và chỉ khi bạn đã chỉ định rằng một file loại bỏ (discard file) nên được kích hoạt. File loại bỏ chứa các record bị loại bỏ khỏi quá trình nạp do không khớp với bất kỳ tiêu chí chọn record nào được chỉ định trong control file.
SQL * Loader control file
Control file SQL*Loader là một file văn bản chứa các lệnh ngôn ngữ định nghĩa dữ liệu (DDL). DDL được sử dụng để điều khiển các khía cạnh sau của một phiên làm việc SQL*Loader:
- Nơi SQL*Loader tìm dữ liệu để nạp.
- Cách mà SQL*Loader mong đợi dữ liệu được định dạng.
- Cách mà SQL*Loader được cấu hình (bao gồm quản lý bộ nhớ, tiêu chí chọn và từ chối, xử lý nạp bị gián đoạn, vv.) khi nạp dữ liệu.
- Cách mà SQL*Loader thực hiện xử lý dữ liệu đang được nạp.
Dưới đây là một ví dụ về control file (control file) trong SQL*Loader:
-- Sample Control File LOAD DATA INFILE 'data.csv' BADFILE 'data.bad' DISCARDFILE 'data.dsc' APPEND INTO TABLE employees FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( employee_id INTEGER EXTERNAL, first_name CHAR(50), last_name CHAR(50), hire_date DATE "DD-MON-YYYY", salary DECIMAL EXTERNAL(10,2), department_id INTEGER EXTERNAL NULLIF department_id=BLANKS )
Trong ví dụ này:
- File dữ liệu đầu vào được đặt tên là ‘data.csv’.
- Các record bị từ chối sẽ được ghi vào file ‘data.bad’.
- Các record bị loại bỏ sẽ được ghi vào file ‘data.dsc’.
- Dữ liệu sẽ được nạp vào bảng ’employees’.
- Các trường trong file dữ liệu được phân tách bằng dấu ‘,’ và có thể được bao quanh bởi dấu ‘”‘.
- Cột ’employee_id’ được xác định là số nguyên.
- Cột ‘first_name’ và ‘last_name’ được xác định là ký tự có độ dài tối đa là 50.
- Cột ‘hire_date’ được xác định là ngày tháng có định dạng ‘DD-MON-YYYY’.
- Cột ‘salary’ được xác định là số thập phân với 10 chữ số tổng cộng và 2 chữ số thập phân.
- Cột ‘department_id’ được xác định là số nguyên, có giá trị null nếu giá trị của department_id trong file dữ liệu là BLANKS (trường rỗng).
Ví dụ 2:
Dưới đây là một ví dụ tương tự với control file (control file) dựa trên ví dụ ban đầu:
-- Sample Control File
LOAD DATA
INFILE 'SAMPLE.DAT'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(
hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(3) NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL TERMINATED BY WHITESPACE NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND ')' ":comm * 100"
)
Trong ví dụ này:
- File dữ liệu đầu vào được đặt tên là ‘SAMPLE.DAT’.
- Các record bị từ chối sẽ được ghi vào file ‘sample.bad’.
- Các record bị loại bỏ sẽ được ghi vào file ‘sample.dsc’.
- Dữ liệu sẽ được nạp vào bảng ’emp’.
- Các trường trong file dữ liệu được chỉ định theo vị trí (POSITION) và các điều kiện (WHEN).
- Cột ‘hiredate’ sẽ được gán giá trị của ngày hiện tại (SYSDATE).
- Cột ‘deptno’ là một số nguyên có độ dài 3 ký tự, và sẽ được gán giá trị null nếu giá trị của ‘deptno’ trong file dữ liệu là BLANKS (trường rỗng).
- Cột ‘job’ là một ký tự từ vị trí 7 đến 14, được kết thúc bởi khoảng trắng. Nếu giá trị của ‘job’ là BLANKS, nó sẽ được chuyển thành chữ hoa (UPPER).
- Cột ‘mgr’ là một số nguyên từ vị trí 28 đến 31, được kết thúc bởi khoảng trắng. Nếu giá trị của ‘mgr’ là BLANKS, nó sẽ được gán giá trị null.
- Cột ‘ename’ là một ký tự từ vị trí 34 đến 41, được kết thúc bởi khoảng trắng và sẽ được chuyển thành chữ hoa (UPPER).
- Cột ’empno’ là một số nguyên từ vị trí 45, được kết thúc bởi khoảng trắng.
- Cột ‘sal’ là một ký tự từ vị trí 51, được kết thúc bởi khoảng trắng và sẽ được chuyển đổi thành số thập phân.
- Cột ‘comm’ là một số nguyên được bao quanh bởi dấu ngoặc đơn và dấu phần trăm, và sẽ được nhân với 100.
Phương thức Loading data trong Oracle database bằng SQL*Loader
Quá trình tải dữ liệu theo đường dẫn thông thường sử dụng các câu lệnh SQL INSERT để thêm dữ liệu vào cơ sở dữ liệu Oracle. Trong quá trình này, Oracle phải xử lý nhiều công việc liên quan đến việc thực hiện các câu lệnh SQL và lưu trữ dữ liệu.
Khi sử dụng quá trình tải dữ liệu theo đường dẫn trực tiếp, các khối dữ liệu Oracle được định dạng và ghi trực tiếp vào các file cơ sở dữ liệu mà không cần thông qua các bước xử lý phức tạp. Điều này giúp giảm thiểu tài nguyên hệ thống cần thiết và tăng tốc độ tải dữ liệu.
Trong quá trình tải dữ liệu theo đường dẫn trực tiếp, không có sự cạnh tranh với các người dùng khác trong việc sử dụng tài nguyên cơ sở dữ liệu. Do đó, quá trình tải dữ liệu có thể được thực hiện với tốc độ gần bằng tốc độ đĩa, cho phép tải dữ liệu nhanh chóng và hiệu quả.
Một điểm khác biệt quan trọng giữa quá trình tải dữ liệu theo đường dẫn thông thường và đường dẫn trực tiếp là cách dữ liệu được lưu trữ. Trong quá trình thông thường, dữ liệu được lưu trữ thông qua các câu lệnh INSERT và giao dịch COMMIT.
Tuy nhiên, trong quá trình tải dữ liệu theo đường dẫn trực tiếp, dữ liệu được ghi trực tiếp vào các khối dữ liệu của cơ sở dữ liệu Oracle mà không cần thông qua các câu lệnh SQL. Điều này giúp tăng tốc độ tải dữ liệu vì không cần thực hiện các bước xử lý phức tạp như trong quá trình thông thường.
Một lưu ý quan trọng là quá trình tải dữ liệu theo đường dẫn trực tiếp sử dụng các lưu trữ dữ liệu để ghi các khối dữ liệu vào các file cơ sở dữ liệu Oracle. Các lưu trữ dữ liệu chỉ ghi các khối dữ liệu đầy đủ và diễn ra sau điểm cao nhất của bảng dữ liệu.
Sau khi lưu trữ dữ liệu hoàn tất, điểm cao nhất của bảng được di chuyển để tiếp tục việc ghi dữ liệu mới. Quá trình này giúp tối ưu hóa việc ghi dữ liệu và tăng tốc độ tải dữ liệu so với quá trình thông thường.
Tóm lại, quá trình tải dữ liệu theo đường dẫn trực tiếp trong Oracle giúp tăng tốc độ tải dữ liệu bằng cách tránh các bước xử lý phức tạp và sử dụng các lưu trữ dữ liệu hiệu quả. Điều này giúp cải thiện hiệu suất và tăng tốc độ tải dữ liệu vào cơ sở dữ liệu Oracle.
Ví dụ Loading data bằng cách sử dụng SQL Plus:
Để tải dữ liệu bằng công cụ SQL*Plus, bạn có thể sử dụng câu lệnh `LOAD DATA` và cung cấp một control file để chỉ định các file dữ liệu và cách chúng được tải vào cơ sở dữ liệu. Dưới đây là một ví dụ:
1. Tạo control file (ví dụ: `data_load.ctl`):
LOAD DATA INFILE 'data.csv' -- Tên file dữ liệu đầu vào BADFILE 'data.bad' -- Tên file lưu trữ các record bị lỗi DISCARDFILE 'data.dsc' -- Tên file lưu trữ các record bị loại bỏ APPEND INTO TABLE employees -- Tên bảng để tải dữ liệu vào FIELDS TERMINATED BY ',' -- Dấu phân cách giữa các trường TRAILING NULLCOLS ( emp_id, emp_name, emp_salary )
2. Tạo file dữ liệu (ví dụ: `data.csv`):
1,John Doe,5000 2,Jane Smith,6000 3,Michael Johnson,5500
3. Mở cửa sổ dòng lệnh và kết nối với cơ sở dữ liệu Oracle bằng SQL*Plus.
4. Chạy lệnh sau để tải dữ liệu từ file `data.csv` vào bảng `employees`:
SQL> @data_load.ctl
Lệnh trên sẽ đọc control file `data_load.ctl` và thực hiện tải dữ liệu vào cơ sở dữ liệu. Kết quả và thông báo lỗi (nếu có) sẽ được hiển thị trên cửa sổ dòng lệnh.
Lưu ý: Trước khi chạy lệnh `LOAD DATA`, hãy đảm bảo rằng bạn đã đăng nhập vào SQL*Plus với quyền truy cập và quyền ghi vào bảng mà bạn muốn tải dữ liệu vào.
Với ví dụ trên, các record trong file `data.csv` sẽ được tải vào bảng `employees` trong cơ sở dữ liệu Oracle. Mỗi record chứa các trường `emp_id`, `emp_name`, và `emp_salary` tương ứng với mã nhân viên, tên nhân viên và lương nhân viên. Các trường được phân cách bằng dấu phẩy (,) như được chỉ định trong control file.
SQL*Loader Express Mode
Khi bạn kích hoạt Chế độ SQL*Loader Express, bạn chỉ cần chỉ định tên người dùng và tham số TABLE, và các thiết lập mặc định sẽ được sử dụng cho các tham số khác. Tuy nhiên, bạn có thể ghi đè lên các thiết lập mặc định này bằng cách chỉ định các tham số khác trên dòng lệnh.
Chế độ SQL*Loader Express sẽ tạo ra hai file:
File log (log file): Tên file log được lấy từ tên của bảng. File log này bao gồm:
- Đầu ra của control file (control file).
- Một file script SQL để tạo bảng ngoại (external table) và thực hiện việc tải dữ liệu bằng câu lệnh SQL INSERT AS SELECT.
File log tương tự như file log của SQL*Loader, nó mô tả kết quả của quá trình tải dữ liệu. Trong file log này, “%p” đại diện cho ID quá trình của quá trình SQL*Loader.
Ví dụ:
Giả sử bạn có một file dữ liệu đầu vào có tên là “employees.csv” chứa thông tin về nhân viên như sau:
emp_id,emp_name,emp_salary 1,John Smith,5000 2,Jane Doe,6000
Bạn muốn tải dữ liệu từ file này vào bảng “Employees” trong cơ sở dữ liệu của bạn bằng SQL*Loader Express Mode. Để làm điều này, bạn có thể chạy lệnh sau trong SQL*Plus:
sqlldr username TABLE=Employees DATA=employees.csv
Kết quả là SQL*Loader Express Mode sẽ tạo ra hai file log: một file log chứa đầu ra của control file và một file log mô tả kết quả của quá trình tải dữ liệu.
File log đầu tiên có thể chứa nội dung sau:
-- Control file (control file) đầu ra OPTIONS (SKIP=1) LOAD DATA INFILE 'employees.csv' INTO TABLE Employees FIELDS TERMINATED BY ',' (emp_id, emp_name, emp_salary)
File log thứ hai có thể chứa nội dung tương tự như file log của SQL*Loader:
SQL*Loader: Release 12.2.0.1.0 - Production on Mon Apr 26 09:14:58 2021 ... Table Employees: 2 Records successfully loaded. Check the log file for more details.
Thông qua việc sử dụng SQL*Loader Express Mode, bạn có thể tải dữ liệu từ file vào bảng một cách nhanh chóng và thuận tiện mà không cần viết control file (control file) và script SQL phức tạp.
Trong SQL*Loader Express Mode, hai file log được tạo ra có các vị trí sau:
- File log chứa đầu ra của control file (control file) và script SQL: File log này được tạo ra trong thư mục hiện tại (current directory) hoặc thư mục được chỉ định trong câu lệnh khi chạy SQL*Loader Express Mode. Tên file log sẽ được lấy từ tên của bảng.
- File log tương tự như file log của SQL*Loader: File log này được tạo ra trong thư mục hiện tại hoặc thư mục được chỉ định. Tên file log sẽ có định dạng “%p.log”, trong đó “%p” đại diện cho ID quá trình của quá trình SQL*Loader.
Ví dụ: Nếu bạn chạy SQL*Loader Express Mode trong thư mục “/home/user/”, file log chứa đầu ra của control file và script SQL có thể có đường dẫn “/home/user/Employees.log”, và file log tương tự có thể có đường dẫn “/home/user/1234.log” (với 1234 là ID quá trình của SQL*Loader).
Lưu ý rằng địa chỉ chính xác của các file log phụ thuộc vào thư mục hiện tại hoặc thư mục được chỉ định khi chạy SQL*Loader Express Mode.