Perl

資料處理 Perl (1)

基本介紹

教學目標

了解如何透過 Perl 程式語言撰寫 ETL 批次程式。

重點概念

企業主要是以 Windows 平台之個人電腦為主,因此會透過編輯工具 (例如: UltraEdit) 以 SFTP 方式進行遠端程式碼的撰寫,再透過遠端工具 (例如: Putty) 連線至 Unix 主機直接執行 Perl 批次程式碼並且進行除錯。Perl 全名為 Practical Extraction and Report Language ,也就是實務擷取與報表語言,其開發者為 Larry Wall 是一位語言學家,語言學直接想到不是單字就是句子,透過單字和句子拆解和組合就能在日常生活中表達更豐富的內容,其所代表的意義完全取決於句子的結構、語意和文章中實際的位置,因此 Perl 能夠以言簡意賅地方式表達想法。

在 1987 年 Perl 被發表目前已經能夠支援 Unicode 萬國碼,其最大的特色性就是以 C 語言為基礎開發的腳本語言 (Script),不需編譯直接執行,此外 Perl 不需要宣告變數即可使用,可是建議使用 use strict 強制使用變數一定要宣告,此外 Perl 可以搭配 DBI 函式庫依據資料表動態產生變數,變數只是便於保存某些東西的地方,必須有個名字,好讓我們回來取用時知道何處尋找某個特定物件,並且為了要能立即有用的方法就是按照變數所能存放資料的類別分類,類似英文名詞中的單數與複數之分,在 Perl 中會將數字和字串以單數變數儲存,單數變數稱為純量 (Scalar),將數字列和字串以複數變數儲存,複數變數稱為陣列 (Array) ,且在宣告變數時會以「$」前置符號告訴 Perl 為單數變數,以及會以「@」前置符號告訴 Perl 為複數變數。

然而若要透過 Perl 設計以 Teradata 資料倉儲為基礎的 ETL 批次程式框架,必須具備藉由 Perl 透過 Teradata BTEQ 命令執行 SQL 陳述式和藉由 Perl 將批次程式進行模組化的功能,也就是能夠將程式邏輯與查詢語法獨立個別檔案,彼此之間的關聯可以透過檔案存取的方式處理,宣告共用的自動依環境轉之變數藉由子程式的方式載入應用,以及針對不同處理階段獨立個別檔案再透過 ETL 工具整合為可行的批次程式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# Perl 透過 BTEQ 命令執行 SQL 陳述式
#!/usr/bin/perl

use strict;

my $bteq_rc = open(BTEQ, "| bteq");

unless ($bteq_rc) {
print "無法正常驅動 BTEQ 命令。\n";
return -1;
}

print BTEQ << END_OF_BTEQ;

.SET ERROROUT STDOUT

# 相關 SQL 陳述式,其中可以透過 ${變數名稱} 自動置換 SQL 陳述式的內容
SELECT * FROM DBC.TABLES;

END_OF_BTEQ;

此外 Perl 還能夠搭配 DBI 函式庫執行 SQL 陳述式,並且透過資料表為基礎動態建立變數,更進一步動態修改批次中的 SQL 陳述式,也就是說 SQL 陳述式會根據資料表中的對應值進行動態調整,如此一來就能夠減少因為報表需求的頻繁更新,導致因為上線流程繁鎖所產生的困擾,但此應用只建議用於報表用途的批次程式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# Perl 透過 DBI 執行 SQL 陳述式
#!/usr/bin/perl

use strict;

my $hostname = "主機名稱";
my $username = "帳號";
my $password = "密碼";

my $dbh = DBI->connect("dbi:Teradata:${hostname}", $username, $password)

unless ( defined($dbh) ) {
print "無法正常連線至資料庫。\n";
return -1;
}

$sql = " SQL 陳述式";

$sth = $dbh->prepare(${sql});

unless ($sth) {
print "無法正常準備 SQL 陳述式。\n";
return -1;
}

$sth->execute();

# 透過資料表為基礎動態建立變數
while (@row = $sth->fetchrow()) {
my $key = trim($row[0]);
my $value = trim($row[1]);
$value =~ s/\"/\'/ig;
eval ("\${$key} = \"$value\"");
}

$sth->finish();

$dbh->disconnect();

最後 Perl 也支援正規表示法 (Regular Expressions) 和直接執行 Unix 指令等實用功能,因此能夠因應不同系統針對資料進行更強大的擷取應用,接著再搭配 Teradata BTEQ 進行快速載入和應用需求調整「動態」轉換為報表資料表的應用。雖然初學者來說的確很難上手,但在面對企業中龎大且複雜的系統時,以 Perl 為基礎撰寫的批次的確有能力解決所有複雜的問題,並且穩定執行,可惜維護與更新管理將會是其所面臨最大的挑戰。

相關資源

資料處理 NCR ETL Automation & Perl (1)

基本介紹

在 Teradata 建置系統時通常會採用 NCR ETL Automation 和 Perl DBI 腳本指令來完成 ETL 作業。

  1. ETL 最早是由 NCR 和 SPSS 一起制定的規範,因此 NCR ETL Automation 的運作模式會被業界所仿效。
  2. Perl 是 Larry Wall 為了更方便在 UNIX 上處理報表工作,而開發的通用腳本語言,透過 Database Interface (DBI) 標準即可進行資料庫的存取操作。

重點概念

在銀行進行資料倉儲的工作已達半年,已經有些經驗在不涉及商業機密的情況下進行技術分享。

NCR ETL Automation 伺服器中的目錄結構,主要有處理批次腳本的 APP、處理批次腳本狀態的 DATA、處理批次腳本記錄的 LOG 等相關目錄,代表著一個完整的 ETL 工具必須要有上述的內容才能正常穩定運作。

  • /APP
    • /$SYS
      • /$JOB
  • /DATA
    • /complete
    • /fail
    • /process
    • /queue
    • /receive
  • /LOG
    • /$SYS
      • /$DATE
  • /bin
  • /etc
  • /lock
  • /tmp

NCR ETL Automation 管理工具中我們會針對不同系統的批次設定執行時間,被哪些批次啟動 (Job Stream),相依哪些批次,在必要時會強制執行批次,然而每次執行必須帶日期 (YYYYMMDD),以及針對異常的批次會重新設定狀態 (Ready、Pending、Done),並且查看執行批次的腳本進行 Debug ,最常發生的狀況是執行 SQL 的 Session 不夠而導致批次中斷,以及不同批次同時存取相同資料表導致卡住 (LOCK) 無法往下執行,此時就必須透過 PMON 工具將其中一個查詢刪除才能繼續執行。

NCR ETL Automation 執行 ETL 作業主要有兩種方式,第一種是執行 Shell 處理指令,第二種是執行 Perl 的 DBI 驅動指令,基本上會以第二種為主對資料庫進行 CREATE TABLE 、 DROP TABLE 、 SELECT 、 INSERT 、 UPDATE 和 DELETE 等資料處理的 SQL 操作。總結 Teradata 最傳統的 ETL 處理方式為先以 NCR ETL Automation 為管理架構,接著以 Perl 為批次架構,最後以資料處理的 SQL 操作完成 ETL 作業。

相關資源