Bảng ngoài external table trong Oracle database, load data từ CSV vào database

Trong cơ sở dữ liệu Oracle, bảng ngoài (external table) cho phép bạn truy cập dữ liệu bên ngoài Oracle Database như thể nó là một bảng trong cơ sở dữ liệu. Đây là một cách thuận tiện để đọc dữ liệu từ các tệp bên ngoài mà không cần phải nhập dữ liệu đó vào cơ sở dữ liệu Oracle. Bạn có thể sử dụng bảng ngoài để thực hiện các thao tác như SELECT, JOIN, và FILTER dữ liệu bên ngoài mà không cần nạp dữ liệu đó vào các bảng thông thường của Oracle.

• Oracle có thể liên kết file bảng ngoài load vào trong database
• File này có thể sử dụng cho nhiều database
• Có 2 driver để hỗ trợ load data bảng ngoài là ORACLE_LOADER và ORACLE_DATAPUMP
• Tên cột trong file ngoài phải giống với trường dữ liệu tạo trong bảng
• Kiểu dữ liệu file ngoài cũng phải giống với kiểu dữ liệu bảng
• Bảng ngoài chỉ để đọc dữ liệu từ file bên ngoài vào, ko thể update, insert, delete được trên bảng ngoài

Dưới đây là cách tạo bảng ngoài trong Oracle:

1. Tạo user và thư mục Oracle: Đầu tiên, bạn cần tạo một đối tượng DIRECTORY chỉ ra đường dẫn của thư mục chứa tệp dữ liệu bên ngoài.

Tạo user và gán quyền:

create user duong IDENTIFIED by oracle
DEFAULT tablespace duong_tbs;
alter user duong quota unlimited on duong_tbs;
grant connect, resource to duong;

CREATE DIRECTORY my_ext_dir AS '/home/oracle/';
GRANT READ, WRITE ON DIRECTORY my_ext_dir TO duong;

1. Tạo bảng ngoài: Sử dụng lệnh CREATE TABLE với từ khóa ORGANIZATION EXTERNAL để tạo bảng ngoài.

CREATE TABLE my_external_table (
column1 datatype,
column2 datatype,
...
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_ext_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
column1 CHAR(255),
column2 CHAR(255),
...
)
)
LOCATION ('external_file.csv')
)
REJECT LIMIT UNLIMITED;

1. Sử dụng bảng ngoài: Sau khi tạo bảng ngoài, bạn có thể thực hiện các truy vấn SQL trên bảng đó như trên các bảng thông thường khác.

SELECT * FROM my_external_table;

Ví dụ thực tế

Giả sử bạn có một tệp CSV với tên employees.csv có nội dung sau:

employee_id,first_name,last_name,email

100,Steven,King,steven.king@oracle.com

101,Neena,Kochhar,neena.kochhar@oracle.com

Bạn có thể tạo bảng ngoài để đọc dữ liệu từ tệp này như sau:

CREATE DIRECTORY ext_tab_dir AS '/home/oracle/';
GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO duong;

CREATE TABLE duong.employees_ext (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
employee_id CHAR(10),
first_name CHAR(50),
last_name CHAR(50),
email CHAR(100)
)
)
LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

Bây giờ bạn có thể thực hiện truy vấn:

SELECT * FROM duong.employees_ext;

Kết quả sẽ là:

EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL

———————————————

100 | Steven | King | steven.king@oracle.com

101 | Neena | Kochhar | neena.kochhar@oracle.com

Từ bảng ngoài này chúng ta có thể copy data và tạo mới bảng khác (không phải bảng ngoài => đây là 1 cách để load data từ ngoài, CSV file vào database):

create table duong.new_table AS select * from duong.employees_ext;

select * from duong.new_table;

Bảng ngoài là một công cụ mạnh mẽ trong Oracle Database cho phép bạn dễ dàng tích hợp dữ liệu bên ngoài mà không cần phải nạp trực tiếp vào cơ sở dữ liệu, giúp tiết kiệm tài nguyên và tăng tính linh hoạt trong quản lý dữ liệu.