Oracle External Table #2

ครั้งที่แล้ว เราได้ทราบถึง หลักการของ External Table ใน Oracle External Oracle#1 ไปแล้ว คราวนี้เราจะกล่าวถึง ขั้นตอนในการทำ External Table กันดูบ้าง

• มีไฟล์ข้อความที่ต้องการ Select เพื่อดูข้อมูล ซึ่งอาจจะอยู่ในรูปแบบของไฟล์ CSV หรือจะเป็นไฟล์ที่มีข้อมูลในแต่ละบรรทัดมีความยาวที่เท่าๆกัน (Fixed Length) ก็ได้

• ทำขั้นตอนต่อจากไปนี้ใน SQL Plus

• การโหลดไฟล์ จะต้องทำการเก็บไฟล์ไว้ใน Directory ที่ Oracle รู้จัก ซึ่งอาจจะใช้ Directory ที่มีอยู่แล้วหรือว่าสร้างขึ้นมาใหม่ก็ได้ แต่จะต้องทำให้ Oracle รู้จักโดยการ Map ก่อน คำสั่งที่ใช้ในการดูว่า Oracle รู้จัก Directory ใดบ้างคือ (ใช้ User ที่มีสิทธิ์เป็น DBA)

SQL> select * from dba_directories;

จะเห็นว่ามี Directory ใดบ้างที่ Oracle รู้จัก และแสดงชื่อที่ใช้ในการ Map

• ถ้าต้องการสร้าง Directory ใหม่และ Map ให้ Oracle รู้จัก เช่นที่โฟลเดอร์ ‘C:\oracle\ext’ จะใช้คำสั่งดังนี้ (ใช้ User ที่มีสิทธิ์เป็น DBA)

SQL> create or replace directory ext_dir as ‘C:\oracle\ext’;

สังเกตได้ว่าชื่อที่ใช้ Mapในที่นี้คือ ext_dir ไม่จำเป็นต้องเป็นชื่อเดียวกับโฟลเดอร์คือ ext

• Grant สิทธิ์ในการอ่านและเขียน Directory ให้กับ User ที่จะใช้งาน ในที่นี้คือ test_user มีคำสั่งดังนี้ (ใช้ User ที่มีสิทธิ์เป็น DBA)

SQL> grant read, write on directory ext_dir to test_user;

จากนั้นให้ Log in ด้วย User ที่ต้องการโหลดข้อมูลและทำการสร้าง External Table ไว้เพื่อรองรับข้อมูล ใช้คำสั่งดังนี้

ตัวอย่างข้อมูลของไฟล์แบบ CSV

1, A, 001M

2, B, 002M
3, C, 003M

ตัวอย่างข้อมูลของไฟล์แบบ Fixed Length

1A001M

2B002M

3C003M

SQL> create table ext_table
(num number(1),
name varchar2(1),
code varchar2(4)
)
organization external
(type oracle_loader
default directory ext_dir
access parameters
(
records delimited by newline -- เริ่มขึ้น Record ใหม่เมื่อในไฟล์มีการขึ้นบรรทัดใหม่
badfile ‘bad_data’ -- ถ้ามีข้อมูลที่ไม่ถูกโหลดเข้ามาจะถูกบันทึกไว้ที่ไฟล์นี้
logfile ‘log_data’ -- Log ของการโหลดจะถูกบันทึกไว้ที่ไฟล์นี้
fields terminated by ',' -- เริ่มขึ้น Field ใหม่เมื่อในไฟล์มีเครื่องหมาย Comma (,)
)
location ('data.txt') -- ชื่อไฟล์ที่จะโหลดข้อมูล
)
reject limit 100; -- จำนวนที่ยอมให้มีข้อมูลผิดพลาดได้ ถ้าเกินจากนี้จะถือว่าสิ้นสุดการโหลดทันที

สำหรับไฟล์รูปแบบ Fixed Length ตามตัวอย่างด้านบน
(มีการแก้ไข Script เนื่องจากตอนแรกประกาศให้คอลัมน์เป็นแบบ Varchar2 แล้วเกิด Error รวมถึงการแก้ในส่วนของการขึ้นบรรทัดใหม่จากเดิมเป็นแบบกำหนดความยาวของแต่ละบรรทัดไปเลย ซึ่งเกิด Error เช่นกัน)

SQL> create table ext_table
(num char(1),
name char(1),
code char(4)
)
organization external
(type oracle_loader
default directory ext_dir
access parameters
(
records delimited by newline -- เริ่มขึ้น Record ใหม่เมื่อในไฟล์มีการขึ้นบรรทัดใหม่
fields
(
num char(1),
name char(1),
code char(4)
)
badfile ‘bad_data’ -- ถ้ามีข้อมูลที่ไม่ถูกโหลดเข้ามาจะถูกบันทึกไว้ที่ไฟล์นี้
logfile ‘log_data’ -- Log ของการโหลดจะถูกบันทึกไว้ที่ไฟล์นี้
)
location ('data.txt') -- ชื่อไฟล์ที่จะโหลดข้อมูล
)
reject limit 100; -- จำนวนที่ยอมให้มีข้อมูลผิดพลาดได้ ถ้าเกินจากนี้จะถือว่าสิ้นสุดการโหลดทันที

• ผลที่ได้จากตารางทั้ง 2 ก็คือข้อมูลจะถูกโหลดไว้ใน External Table ชื่อ ext_dir ซึ่งสามารถทำการ Query ดูข้อมูลได้ดังคำสั่งนี้

SQL> select * from ext_dir;

ผลลัพธ์จะได้ดังนี้

num name Code
-------------------------------------------
1 A 001M
2 B 002M
3 C 003M

• ถ้าต้องการนำข้อมูลเหล่านี้ไปเก็บไว้ในฐานข้อมูลจริงๆก็สามารถทำได้โดยการ Query จาก External Table นี้ ก็จะเปรียบเสมือนการโหลดข้อมูลจากไฟล์โดยตรง เช่น โหลดลงตารางชื่อ real_table ใช้คำสั่งดังนี้

SQL> insert into real_table(num1,name1,code1)

select * from ext_table;


สรุปประเด็น

• External Table ใช้ในการ Query ข้อมูลที่อยู่ภายนอกฐานข้อมูลจริงๆ เช่น Text File ต่างๆ
• External Table สามารถทำได้เพียงแค่ Select เท่านั้น ไม่สามารถ Insert หรือ Update ได้
• การจะโหลดข้อมูลจากไฟล์ลง External Table จะต้องเก็บไฟล์นั้นไว้ใน Directory ที่ Oracle รู้จัก ถ้าไม่มีจะสามารถสร้างใหม่และทำการ Map ได้
• หน้าที่หลักๆของ External Table คือเปรียบเสมือนตัวกลางระหว่างไฟล์กับฐานข้อมูลที่ใช้พักข้อมูลเพื่อเรียกดูหรือนำไปใช้ต่อ
• รูปแบบไฟล์ข้อความที่จะนำมาโหลดลง External Table มีด้วยกัน 2 แบบหลักๆคือ แบบ CSV และ Fixed Length
• การโหลดข้อมูลแบบ Fixed Length ควรจะกำหนดให้คอลัมน์ของ External Table เป็นประเภท Char ทั้งหมด จะได้ไม่เกิด Error ในขณะที่ Select ข้อมูล (เมื่อโหลดลงตารางจริงๆ ถึงแม้จะมีประเภทของคอลัมน์ไม่ตรงกันก็จะสามารถโหลดได้ตามปกติเช่น ตารางจริงๆมีคอลัมน์ num เป็นประเภท number แต่ External Table เป็นประเภท Char ก็ยังสามารถโหลดได้ตามปกติ)

Created By: Pinant Thammanajit

ข้อเขียนนี้ช่วยฉัน: