[Oracle Client] Trích xuất dữ liệu từ xa thông qua Instant Client
Thông thường khi cần trích xuất một lượng dữ liệu nhỏ như báo cáo, chúng ta đã quen với việc truy cập vào DB thông qua công cụ trực quan như SQL Navigator, SQL Developer. Vậy nếu nhu cầu của chúng ta là tự động định kì trích xuất dữ liệu ra file thì sẽ như thế nào, hãy xem qua bài toán sau:
Đứng từ server client (không cài DB), xây dựng luồng tự động kết nối đến server DB, select dữ liệu báo cáo cần thiết và kết xuất thành file và gửi file đến FTP server khác.
Như trên, server DB thì dĩ nhiên là có cài đặt DB cùng với các công cụ, biến môi trường đi kèm nhưng về phía server client thì đây có thể chỉ là một máy chủ đảm nhận chức năng khác (API, APP) với các biến môi trường không giống với server DB, mối liên kết duy nhất là nó có kết nối đến server DB.Vậy để trích xuất dữ liệu báo cáo thì các developer có thể lựa chọn các ngôn ngữ lập trình mạnh nhưng với góc nhìn của một sys admin, tôi sẽ giải quyết bài toàn này chỉ bằng các công cụ sẵn có theo lưu trình như sau:
- Trên server client cài đặt thêm Oracle Instant Client để chạy được công cụ SQLPlus.
- Thông qua SQLPLus kết nối đến server DB, select dữ liệu báo cáo cần thiết và kết xuất thành file.
- Đến đây, ta có thể viết bash script để ghi file và gửi đến các hệ thống đích khác tùy theo yêu cầu nghiệp vụ.
Bắt đầu:
1.1. Cài đặt Oracle Instant Client và SQLPlus, cách đơn giản nhất là tải file .zip về và giải nén. Trong ví dụ này tôi đang cài đặt vào thư mục /opt/instantclient:
export LD_LIBRARY_PATH=/opt/instantclient_12_2:$LD_LIBRARY_PATH export PATH=/opt/instantclient_12_2:$PATH
Col1|Col2|Col3 acb|xyz|bcd
-- Hãy thực hiện định dạng nội dung file dữ liệu luôn trên câu lệnh SQL bằng các công cụ như nối chuỗi, không nên sử dụng bash script xuất file và định dạng nội dung ở bước sau. Ví dụ:
SELECT 'Col1|Col2|Col3' FROM DUAL UNION ALL SELECT Col1 || '|' || Col2 || '|' || Col3 FROM ...
3. Viết một bash script 'get_data_and_ftp.sh' thực hiện ghi kết quả query ra file và truyền đến FTP server nếu cần. Script này sẽ hoạt động theo lưu trình:
- Kích hoạt SQLPlus để bắt đầu phiên kết nối đến server DB
- Sử dụng câu lệnh SQL lưu trong file .sql để query dữ liệu trên DB, đồng thời định dạng luôn dữ liệu đầu ra (ngăn cách bằng dấu gì, tên trường như thế nào)
- Ghi kết quả query ra file, ghi log, truyền file đến hệ thống khác nếu cần.
-- Trong script này ta nên bổ sung đầy đủ cơ chế check tiến trình và ghi log, log thông báo cần chứa các giá trị DATE, INFO để phục vụ giám sát hoạt động của luồng này:
#!/bin/bash
# Lấy ngày hiện tại
LOG_DATE=$(date +'%Y%m%d_%H%M%S')
# Đường dẫn đến file log
LOG_FILE="/home/test/script/log/dldoanhthu_${LOG_DATE}.log"
# Thực hiện câu lệnh SQL và lưu kết quả vào biến result
result=$(sqlplus -S -L '********/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=zzzz)))' <<EOF
$(cat /home/test/script/query_data.sql)
EOF
)
sleep 3
# Kiểm tra xem kết quả đã được tạo thành công hay không
if [ -n "$result" ]; then
echo "$(date +'%Y-%m-%d %H:%M:%S') - Ket qua cua cau lenh SQL:" >> "$LOG_FILE"
echo "$result" >> "$LOG_FILE"
# Lấy ngày hiện tại
today=$(date +'%Y%m%d')
# Ghi kết quả vào file DATA_YYYYMMDD.txt
OUTPUT_FILE="/home/test/script/DATA_${today}.txt"
echo "$result" > "$OUTPUT_FILE"
echo "$(date +'%Y-%m-%d %H:%M:%S') - Ket qua da duoc ghi vao file $OUTPUT_FILE" >> "$LOG_FILE"
else
echo "$(date +'%Y-%m-%d %H:%M:%S') - [error] Khong co ket qua nao duoc tao ra." >> "$LOG_FILE"
fi
sleep 3
# Truyền file kết quả đến FTP server
HOST="aaa.aaa.aaa.aaa"
USERNAME="uuuuu"
PASSWORD="ppppp"
DATA_DIR="/home/test/script/data"
FILE_NAME="DATA_${today}.txt"
# Sử dụng lệnh ftp để truyền file
ftp -n $HOST <<END_SCRIPT
quote USER $USERNAME
quote PASS $PASSWORD
binary
put "$OUTPUT_FILE" "/$FILE_NAME"
quit
sleep 3
END_SCRIPT
# Kiểm tra xem lệnh ftp có thành công không
if [ $? -eq 0 ]; then
echo "$(date +'%Y-%m-%d %H:%M:%S') - File da duoc truyen thanh cong den FTP server." >> "$LOG_FILE"
# Di chuyển file đã truyền đến thư mục cụ thể
mv "$OUTPUT_FILE" "$DATA_DIR/$FILE_NAME"
if [ $? -eq 0 ]; then
echo "$(date +'%Y-%m-%d %H:%M:%S') - File da duoc di chuyen den thu muc $DATA_DIR thanh cong." >> "$LOG_FILE"
else
echo "$(date +'%Y-%m-%d %H:%M:%S') - [error] Khong the di chuyen file den thu muc $DATA_DIR" >> "$LOG_FILE"
fi
else
echo "$(date +'%Y-%m-%d %H:%M:%S') - [error] Khong the truyen file den FTP server" >> "$LOG_FILE"
fi4. Đặt crontab để định kì lấy dữ liệu và xuất file. Ở đây chúng ta sẽ có cơ hội gặp một lỗi khá thú vị, đó là khi chạy bash script thủ công thì không báo lỗi, dữ liệu được xuất ra file, gửi đến FTP, ghi log, lưu trữ bình thường nhưng khi đặt script này vào crontab thì bị lỗi luôn ở bước SQLPlus kết nối đến server DB.
Nguyên nhân là do khi chúng ta chạy script thủ công, session của chúng ta có đầy đủ các biến môi trường để script này có thể thực thi, trong trường hợp này thì biến môi trường cần thiết chính là đường dẫn của Oracle Instant Client + SQLPlus.
Mặt khác, khi script này được chạy tự động bởi crontab, phiên của crontab chưa chắc thừa hưởng các biến môi trường mà chúng ta đã khai báo -> Giải pháp là hãy load biến môi trường cần thiết ngay trong crontab. Ví dụ:
10 1 * * * . /home/test/.bash_profile; /home/test/script/get_data_and_ftp.sh > /home/test/script/script_result.log 2>&1
====
Nhận xét
Đăng nhận xét