Teradata 教學

Teradata 基本介紹 (11)

基本介紹

教學目標

初步了解 Teradata 查詢模式的基本概念。

重點概念

首先在 Teradata 中主要有兩種模式分別為 Teradata 模式和 ANSI 模式。當我們在透過 Teradata 系統查詢資料時,需要選擇適當的模式進行查詢,建議採用 Teradata 模式。

接著所謂 Teradata 模式主要是針對資料比較不區分大小寫,允許顯示資料進行 Truncation,交易隱含 Commit ,以及建立資料表預設為 Set。

再來所謂 ANSI 模式主要是針對資料比較區分大小寫,不允許顯示資料進行 Truncation,交易不含 Commit,以及建立資料表預設為 Multiset。

Teradata 模式 ANSI 模式
比較資料 區分大小寫。 不區分大小寫。
顯示資料 允許 Trunction。 不允許 Truncation。
交易作業 內含 Commit。 不含 Commit。
建立資料表 預設 Set。 預設 Multiset。

最後許多資料分析與管理平台若與 Teradata 系統整合查詢時通常預設皆為 ANSI 模式,此時將會造成一些問題發生,像是「Error - Right truncation of string data - Teradata Community」的錯誤訊息。若要解決此問題除了修改讀取的資料表設定之外,我們就只能要想辦法將其設定為 Teradata 模式進行查詢。

相關資源

Teradata 基本介紹 (10)

基本介紹

教學目標

初步了解解決 Teradata 效能問題的基本三步驟。

重點概念

最近在銀行工作快滿二年了,最近對於主要負責的系統開始進行更深入的問題處理與效能優化,如何有效率的維護系統是一門學問,以前當學生時很排斥,但是到了職場在主管的指導下漸漸地了解這件事情真的非常重要。

然而在這三個月的優化過程中最常遇到的問題就是 Teradata 發生 Lock 導致效能低落,因此我們透過工具找出根本原因,修改批次程式,使得相關業務系統操作更順利,更進一步協助業務單位取得更好的業績。在此我們針對資料倉儲 Lock 簡化成三步驟進行處理。

步驟一、觀察原因

首先開啟 Teradata Viewpoint 工具中 Lock Viewer 的功能找出一週之後被 Lock 的所有資訊,並且根據負責的不同系統對應的使用者進行篩選,找出 Lock 時間最久的項目,或者匯出數據轉成 CSV 檔案搭配 Excel 進行觀察,並且記錄與可能原因相關的時間點。

步驟二、找出問題

接著透過 Teradata Viewpoint 工具中的 Query Monitor 的功能再搭配 Rewind 的功能,找出被 Lock 的相關時間點,同時找出發生 Lock 的項目,點選詳細內容查看是哪個 SQL 語法造成 Lock 問題。

步驟三、效能優化

最後根據 SQL 語法找出對應的 ETL 批次,同時針對該 ETL 批次中直接存取實體資料庫的 Read Lock 方式改成 Access Lock 方式,一般來說我們會搭配 View 進行 Access Lock 的存取方式。此時完成之後,再透過 Teradata ViewPoint 工具中的 Lock Viewer 觀察一至二星期就能透過匯出數據直接驗證是否完成效能優化。

總結看似只要透過簡單的三步驟卻能夠帶很大的效益,背後我們資訊人員可是花了很多時間針對許多假設情況進行驗證,最終才能夠找出最簡而有力的解決方式的步驟,更進一步成為標準處理流程撰寫至系統維護文件中。

相關資源

Teradata 認證考試 (1)

基本介紹

教學目標

初步了解 Teradata 認證考試基本概念。

重點概念

Teradata 資料庫主要是在伺服器端,並且藉由平行 (Parallelism) 運算的能力允許 Teradata 資料庫快速處理大量資料,此外為了讓查詢速度更快因此我們會在關聯式資料表中,透過主鍵值 (Primary Key) 欄位或欄位集合為獨立識別該列資料。

接著對於使用者而言,因為 Teradata 資料庫為關聯式資料庫,所以會透過 SQL 結構化查詢語言存取在 Teradata 資料庫中的資料表 (Tables)。當然除了資料表之外,在 Teradata 資料庫中還有檢視表 (Views)、巨集 (Macros)、觸發 (Triggers)、預儲程序 (Stored Procedures)、使用者定義函數 (User Defined Functions,UDFs)、聯集和雜湊索引 (Join and Hash Indexes),以及永久日誌 (Permanent Journals) 等主要物件。其中所謂檢視表為針對一個或多個資料表進行欄或列的過濾,以及所謂巨集為預先定義儲存執行單一交易的 SQL 敘述集合,上述這兩者皆不會佔用 PERM 空間。

最後 Teradata 架構主要有四個元件,分別為 PEs、BYNET、AMPs、Vdisks,首先在客戶端的使用者會透過連接工具,像是 CLI、ODBC、JDBC、.NET、TDP (Teradata Director Program) 和 MTDP (Micros Teradta Director Program) 進行連接將 SQL 指令傳送給 Teradata 伺服器,接著會先由 PE 檢查 SQL 語法 (每個 PE 最多僅支援 120 Sessions,此外會搭配 TDP 平衡 Session,以及 MTDP 管理例行性工作。),以及針對 SQL 語法進行轉換 (Parse)、化(Optimize) 和分派 (Dispatch) 查詢計劃,再來透過訊息傳送層 BYNET 與 AMP 連線,再由 AMP 管理和擷取來自於虛擬磁碟機的資料,進行聚合和鎖定等相關工作。

相關資源

Teradata 查詢指令 (8)

基本介紹

教學目標

初步了解 Macro 和 Stored Procedure 的差別和相關語法。

重點概念

比較表

Macro Stored Procedure
佔用資料庫空間
多筆結果回傳 是 (必須搭配 Cursors 語法)
多筆參數傳遞
控制敘述 (邏輯、迴圈和錯誤處理)
適用情境 SQL語法進行多筆資料回傳 需要透過判斷或迴圈進行資料動態更新

Macro

建立

1
2
3
4
5
CREATE MACRO COUNT_EMPOYEE (jobtitle CHAR(30)) AS (
SELECT COUNT(DINSTINCT EMP_NO) AS EMP_CNT
FROM EMPLOYEE
WHERE JOB_TITLE LIKE '%:jobtitle%';

);

執行

1
EXEC COUNT_EMPOYEE('ENGINEER');

Stored Procedure

建立

1
2
3
4
5
6
7
CREATE PROCEDURE EMP (IN jobtitle CHAR(30),OUT emp_cnt INTEGER)
BEGIN
SELECT COUNT(DINSTINCT EMP_NO)
INTO :emp_cnt

FROM EMPLOYEE
WHERE JOB_TITLE LIKE '%:jobtitle%';
END

執行

1
CALL COUNT_EMPOYEE('ENGINEER',EMP_CNT);

相關資源

Teradata 查詢指令 (7)

基本介紹

教學目標

初步了解 Teradata 中建立資料表的設計方式。

重點概念

在 Teradata 中會透過 AMP (Access Module Processors) 管理 Teradata 資料庫與磁碟系統之間的互動,主要透過雜湊將所有資料隨機平均的分散至所有 AMP 中,我們則可以在建立資料表之前以更有效率的設計平均分散資料,主要可以分為有主索引和沒有主索引兩大類。

資料表中的主索引 (Primary Index) 相當於關聯式資料模型中的主鍵值 (Primary Key),主要差異在於主索引的建立是為了更有效率的存取資料表中的資料。

有主索引 (Primary Index)

大部份的資料表主要會針對每列資料進行有主索引的資料表設計,這些資料表將會透過一致且簡單的雜湊公式分散資料在 AMP 主要會以主索引進行列的排序,當使用者以主索引查詢資料時將會直接將結果回傳。

建立不重複主索引資料表

1
2
3
4
5
6
7
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) UNIQUE PRIMARY INDEX(PRODUCT_NBR);

建立不重複多重主索引資料表

1
2
3
4
5
6
7
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) UNIQUE PRIMARY INDEX(TXN_DATE,PRODUCT_NBR);

在 Teradata 中更允許資料表建立分區主索引,PPI (Partition Primary Index) 資料表仍然會有主索引分散至 AMP 上,但是每個 AMP 排序資料在分區欄位中。取得結果時所有 AMP 將會包含其中,但是每個 AMP 只有讀取一個或多個相關分區,並不會進行完整資料表掃描。

建立分區主索引資料表 (By RANGE)

1
2
3
4
5
6
7
8
9
10
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) PRIMARY INDEX(PRODUCT_NBR)
PARTITION BY RANGE_N (
TXN_DATE BETWEEN DATE '2015-01-01' AND DATE '2015-12-31'
EACH INTERVAL '1' MONTH) ;

建立分區主索引資料表 (By CASE)

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) PRIMARY INDEX(PRODUCT_NBR)
PARTITION BY CASE_N (
TXN_TOTAL< 1000,
TXN_TOTAL< 5000,
TXN_TOTAL< 10000,
NO CASE, UNKNOWN
) ;

沒有主索引 (No Primary Index)

小部份的資料表主要會針對每欄資料進行沒有主索引的資料表設計,每列資料會被隨機分散在 AMP 中,因為此資料表沒有主索引,也就是 Teradata Columnar,此時若只需針對少數欄位進行查詢,則 AMP 只需要針對欄資料對應的快取中就能將結果回傳。

建立沒有主索引資料表

1
2
3
4
5
6
7
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) NO PRIMARY INDEX

可是有趣的是雖然以欄為主的資料表沒有主索引,但還是能夠建立以欄為主的索引。

建立分區索引資料表 (By RANGE)

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) PARTITION BY (COLUMN,CASE_N (
TXN_TOTAL< 1000,
TXN_TOTAL< 5000,
TXN_TOTAL< 10000,
NO CASE, UNKNOWN
)),
UNIQUE INDEX(PRODUCT_NBR);

建立分區索引資料表 (By CASE)

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE PAYMENT
(
PRODUCT_NBR CHAR(30)
,CUSTOMER_ID CHAR(30)
,TXN_DATE DATE
,TXN_TOTAL DECIMAL (10,2)
) PARTITION BY (COLUMN,CASE_N (
TXN_TOTAL< 1000,
TXN_TOTAL< 5000,
TXN_TOTAL< 10000,
NO CASE, UNKNOWN
)),
UNIQUE INDEX(PRODUCT_NBR);

事實上若只單純處理非結構化的資料透過 Teradata 進行 SQL 查詢速度是很理想,但往往面臨的問題主要是空間不足和記憶體不足等問題,因此除了設計最佳化的資料表之外,撰寫最佳化的 SQL 查詢也是非常重要的一件事。

相關資源