使用 SQL 查詢即時匯率：以文字解析方式整合 HTML 網頁資料

背景與挑戰

網際網路上海量資料以 HTML 格式呈現，若能將 HTML 修正為標準 XML 再用 XPath 查詢最為理想。但現實中 HTML 結構靈活、常缺標籤、巢狀錯誤，難以可靠轉換為 XML。此時採用文字解析方式擷取資料較為務實。

實作範例：從中國銀行網站查詢匯率

以下展示如何使用類 SQL 語法定義文字結構描述，並查詢即時匯率資料。

步驟 1：定義文字結構描述

CREATE OR REPLACE SCHEMA txt TYPE txt URL '';

步驟 2：建立外部資料表映射目標網頁

CREATE OR REPLACE TABLE txt.huilv
URL 'https://www.boc.cn/cn/common/service.JSP?querytype=bydate&t_oneday=2007/10/01'
CHARSET 'gb2312'
ROW_XPATH '/html/body/table/tr' (
    P1 VARCHAR ELEMENT 'td[1]',
    P2 VARCHAR ELEMENT 'td[2]',
    P3 VARCHAR ELEMENT 'td[3]',
    P4 VARCHAR ELEMENT 'td[4]',
    P5 VARCHAR ELEMENT 'td[5]',
    P6 VARCHAR ELEMENT 'td[6]'
);

• URL：目標網頁位址，含查詢參數 (日期 2007/10/01)
• CHARSET：網頁編碼 (gb2312)
• ROW_XPATH：定位每筆資料的列節點 (這裡假設表格結構)
• ELEMENT：以 CSS 選擇器方式提取欄位文字內容

步驟 3：查詢匯率資料

SELECT * FROM txt.huilv;

引擎將自動下載網頁、以文字方式解析表格列、提取六欄資料並回傳結果集。

核心技術要點

文字解析 vs XML 解析

常見文字解析策略

1. 固定位置擷取：適合格式極度固定的報表 (如銀行對帳單)
2. 關鍵字定位 + 相對偏移：尋找標題列，再向下固定行數取值
3. 正則表達式擷取：regexp_extract(html, '<td>([^<]+)</td>')
4. 表格結構化：識別 <table>、<tr>、<td> 標籤層級，轉為行列模型 (如本範例)

進階應用：動態日期參數化查詢

CREATE OR REPLACE PROCEDURE get_huilv(p_date VARCHAR) RETURN ROWSET AS
BEGIN
    EXECUTE IMMEDIATE '
        ALTER TABLE txt.huilv SET
        URL ''https://www.boc.cn/cn/common/service.JSP?querytype=bydate&t_oneday='' || p_date;
    SELECT * FROM txt.huilv;
END;

-- 呼叫範例
SELECT * FROM get_huilv('2024/01/15');

注意事項與最佳實踐

• 遵守 robots.txt 與服務條款：高頻爬蟲可能被封鎖，建議快取結果、降低請求頻率
• 編碼處理：確認 CHARSET 正確，否則中文會亂碼
• 結構變更風險：網頁版面改版會導致解析失效，建立監控告警機制
• 替代方案：優先尋找官方 API (JSON/XML)、RSS、CSV 匯出，穩定性遠優於網頁解析
• 工具選擇：生產環境建議使用成熟 ETL 工具 (SSIS、Talend、Python Scrapy/BeautifulSoup、Apache Nifi) 而非自製 SQL 引擎

擴展思路

同樣技術可應用於：

• 股票即時行情擷取
• 氣象資料整合
• 電商競品價格監控
• 政府開放資料門戶 (HTML 表格) 自動化匯入

核心模式皆為：定義外部結構描述 → 映射目標 URL/編碼/解析規則 → 標準 SQL 查詢。