1 資料壓縮的好處

1. Teradata資料壓縮可以節省儲存空間，從而讓相同的儲存單元儲存更多的業務資料；
2. 由於查詢時需要檢索的資料量相對要少，可以減少I/O,並且快取中可以儲存更多的壓縮過的邏輯行，從而改善資料庫效能；
3. Teradata使用的壓縮演算法非常有效，因為壓縮原因，獲取資料需要更少的的磁碟訪問，演算法將節省的CPU時間用來來執行壓縮操作；
4. 可以將壓縮節省出來的空間建立高階索引；

[@more@]

2.teradata資料壓縮

Teradata版本v2r5採用無失真壓縮演算法，允許在一列上對多達255個資料值進行壓縮，資料壓縮不會造成資訊的丟失。

一般資料壓縮的粒度可以分為資料行和資料塊。Teradata資料壓縮的粒度是資料行，這是日常資料操作的粒度，資料行壓縮可以獨立的針對列進行，資料塊壓縮不行，並且資料塊壓縮都會造成額外的壓縮/解壓開銷，會降低資料庫效能。Teradata可以直接訪問壓縮後的資料行—訪問資料行時它不需要對資料做重構或者解壓操作。

前面說過，Teradata可以獨立的對行中的某列做壓縮操作。如果某列允許有空值，空值也允許被壓縮。最好的壓縮候選項為此列中出現頻率最高的值，壓縮後的值存放在表頭。每行前面有1 bit資料來標識此行有沒有被壓縮。非主索引的定長列都可以是Teradata壓縮的候選項。如下的資料型別可以被壓縮，括號中為該資料型別的長度（限制）：

- Integer Date (4)

- CHAR (N, where N < 256)

- BYTEINT (1)

- SMALLINT (2)

- INTEGER (4)

- FLOAT/REAL (8)

- DOUBLE (8)

- DECIMAL (1, 2, 4 or 8)

- BYTE (N, where N < 256)

如果某列中有高頻率出現的值，那麼該列可以有很高的壓縮比，常見的有下面一些情形：

- NULLs

- Zeros

- Default values

- Flags

- Spaces

- Binary indicators (e.g., T/F)

在經分系統中，一些產品欄位，如Brand_Id,prd_id等，都會有很高的壓縮比。

Teradata壓縮對應用程式，ETL,查詢等操作是完全透明的，並且其操作相當方便，使用者只需要在表定義的時候加上壓縮資訊就可以了。例如，下面是資料壓縮的語法：

CREATE TABLE Properties (

Address VARCHAR(40),

City CHAR(20) COMPRESS (‘WuHan’),

StateCode CHAR(2)

);

當記錄中出現“WuHan”的頻率很高時，資料壓縮將非常有效。

3. **系統資料壓縮操作方法

基於以上資料壓縮原理，我們開發了指令碼create_comp_ddl.pl，用來依照壓縮規則生成包含壓縮資訊的建表DDL.

建立資料庫表指令碼檔案使用者配置檔名稱 輸出指令碼目標檔案 壓縮值數目 起始壓縮表大小 壓縮值佔比(百分數)；

對引數的解釋如下：

Ø 建立資料庫表指令碼檔案，老表ddl名稱(必須將ddl匯出放在指定目錄)；

Ø 使用者配置檔名稱，配置資訊主要是除varchar,pi,ppi以外的不希望被壓縮的列,各列逗號分開

Ø 將要生成的新表ddl檔名稱；

Ø 30，壓縮值數目，表示取多少個壓縮來做壓縮操作；

Ø 100000000，壓縮表大小閥值，當表大小小於此閾值時，不進行壓縮；

Ø 20，當前所取壓縮記錄佔表空間比例的閥值，當欲壓縮記錄佔表空間比例小於此閾值時不做壓縮處理；

操作步驟：

1)、獲取壓縮前的建表ddl，儲存為src_table.ddl，檔名將作為引數；

2)、執行create_comp_ddl.pl指令碼，

如:perl create_comp_ddl.plc sr_table.ddl config.txt target_table.sql 30 100000000 30

執行結果將生成新的建表ddl(即target_table.sql)；

3)、以新的ddl建表，將老表的資料匯入新表，確認無誤後，刪除老表；

4. 批次壓縮

若要壓縮的表較多，也可以採取批次壓縮的方式。這裡採用批處理執行perl指令碼的方法來批次壓縮。如需要相繼對prd_prd_inst_hist表和pty_cust_hist表做壓縮，建立批處理compression.bat,其內容如下:

perl create_comp_ddl.plc src_table_prd.ddl config.txt tar_table_prd.sql 30 100000000 30>1.log

perl create_comp_ddl.pl src_table_pty.ddl config.txt tar_table_pty.sql 30 100000000 30>2.log

注意，上述引數裡面的源表ddl名和目標ddl名不能相同，否則第二次的結果可能覆蓋第一次結果.

指令碼主體如下：

######################################################################
# Date Time : 2008-9-9 04:45下午
# Create : minjun
# Function : 自動生成壓縮表的指令碼,生成條件：
# a)欄位型別為D--Decimal,DA--Date,F,I1 BYTEINT,I2 SMALLINT,I INTEGER,CF CHARACTER FIXED (CHAR);
# b)欄位不是pi,ppi;
# c)欄位不在使用者配置資訊中
# parameters : a)未壓縮過的源表DDL檔名稱；
# b)使用者配置資訊,配置資料主要是除varchar,pi,ppi以外的不希望被壓縮的列,各列逗號分開
# c)將生成的帶compress資訊的目標DDL檔名稱；
# d)壓縮值數目，對出現頻率最高的X(引數)個值做壓縮處理；
# e)起始壓縮表大小，表大小閥值，只有表大於此閥值才壓縮；
# f)壓縮值佔比，所選取的壓縮值必須要大於此佔比才做壓縮
#
######################################################################

use DBI;
use File::Basename;
use strict;

my $DSN = $ENV{"AUTO_DSN"};
my ($PID, $dbcon);
my ($DATABASE,$DB_VIEW,$tablename,$tbname,$cpflag,$zb,$sm,$Count)=("","","","",0,0,0,0);

# To see if there is one parameter,
if ( $#ARGV < 5 ) {
print "Usage: $0 源表ddl檔案 配置檔名稱 目標ddl檔名 壓縮值數目 表大小閥值 壓縮值空間佔比n";
print "如：$0 p1.ddl config.txt p1.sql 15 500000000 20 >>p1.logn";
exit(1);
}
my ($DDLFile,$cfgfile,$OutFile,$compval,$yz,$cpratio) = ($ARGV[0],$ARGV[1],$ARGV[2],$ARGV[3],$ARGV[4],$ARGV[5]);

# 掃描標誌檔案，獲取屬性
sub readDDLFile
{
my ($DDLFile1) = shift ;
open(DDLFILE, "${DDLFile1}") || die "cann't open ${DDLFile1}:$!";
my @ddlline = ;
close(DDLFILE);
my ($i,$tmpline);
#print $#ddlline; print "n";
for ($i=0;$i<=$#ddlline;$i++){
$tmpline=$ddlline[$i];
&getCompress($tmpline);
}
}

#不分表情況下,獲取表大小,只有大小超過閾值情況下才需要壓縮：
sub needCompress
{
my ($tb)=@_;

if ($tb=~/_F$/i){
return 1;
}
elsif($tb=~/_[A-EG-QZ]$/i){
return 0;
}
else{
my $sqlText =<

SELECT * FROM
(SELECT
TRIM(DatabaseName) AS DATABASENAME
,TRIM(TableName) AS TABLENAME
,SUM(CurrentPerm) (FORMAT 'zzz,zzz,zzz,999') AS permspace
FROM DBC.tablesize
WHERE DatabaseName in ('${DATABASE}')
AND tablename='${tb}'
GROUP BY 1,2
HAVING permspace >= ${yz} ) a
ORDER BY CASE WHEN SUBSTR(tablename,CHARS(tablename)-1,1) = '_'
THEN SUBSTR(tablename,chars(tablename),1)
ELSE 'Z' END,tablename;

ENDOFSQL
#print "判斷表的大小：".$sqlText;
my $sth = $dbcon->prepare($sqlText);
$sth->execute();
my @res = $sth->fetchrow;
$sth->finish();

return $#res;
}
}

#分析資料，生成帶壓縮資訊的建表ddl
sub getCompress
{
my($line)=shift;
my($tmp1)=chomp($line);
my($tmp1)=$line;
my($fieldname,$fieldtype,$lastcomma);
my @atmp1;
my @atmp2;
my ($tmp2);
my $isppi;#是否為ppi
my $cmpflag='TRUE';#是否需要壓縮
my $incofig=0;

#取資料庫名，表名：
if ($tmp1 =~/CREATEs+/i){
@atmp1=split(/s+/,$tmp1);
$tablename=$atmp1[3];
$tablename=~s/(//g; #去括號
$tablename=~s/s+//g; #去空白字元
@atmp2=split(/./,$tablename);
$DATABASE=$atmp2[0];
$tablename=$atmp2[1];
&showTime();print "開始處理表:${tablename}n";
}
#得到相應的檢視庫
if($DATABASE eq 'PD_DATA'){$DB_VIEW ='PV_DATA_Z';}
elsif($DATABASE eq 'PD_MART'){$DB_VIEW ='PV_MART_Z';}

if ($tablename ne $tbname){
GetCount();
showTime();
print "表${tablename}的總紀錄數為:${Count}n";
if ((&needCompress($tablename)>=1) && ($Count>0)){
$cpflag=1;
}
else {
$cpflag=0;
}
$tbname=$tablename;
}

#需要壓縮
if ($cpflag==1){
#如果沒有"TITLE"字串，直接寫到目標檔案中：
if ($tmp1 !~/s+TITLEs+/i){
print OF $tmp1;print OF "n";
}

#取欄位資訊：
if ($tmp1 =~/s+TITLEs+/i){
#最後的空格去掉：
$tmp1=~s/s+$//;
@atmp1=split(/s+/,$tmp1);
$fieldname=$atmp1[1];
$fieldtype=$atmp1[2];
$lastcomma=$atmp1[$#atmp1];

@atmp1=split(/(/,$fieldtype);
$fieldtype=$atmp1[0];
$fieldtype=uc($fieldtype); #全部轉換成大寫
if ($lastcomma=~/,/){
$lastcomma=1;
}
else{
$lastcomma=0;
}

@atmp2=&isIndex( $fieldname);
$isppi = isPPI($fieldname);
my $cfgstr = GetConfig($cfgfile);
if ($cfgstr =~ /${fieldname}/){
$incofig = 1;
}

#如果：是P/Q/K索引之一或資料型別不是'D', 'DA', 'F', 'I1', 'I2', 'I','CF'之一或是ppi，則不能壓縮；
if($#atmp2<2 or $isppi eq 1 or $incofig eq 1){$cmpflag = 'FALSE';}

if ($cmpflag eq 'FALSE' ){
print OF $tmp1;print OF "n";
&showTime();print "欄位${fieldname},型別${fieldtype}；不被壓縮n";
}
#否則根據資料根據資料分佈取
else{
&showTime();print "欄位${fieldname},型別${fieldtype}; 做壓縮候選列n";
$tmp2=&getMax(${fieldname},${fieldtype});
#判斷是否滿足佔比,否則不進行壓縮
if (&getZb(${fieldname},${tmp2},${fieldtype})>=$cpratio){
print " 超過佔比閥值$cpration";
print "壓縮語句：${tmp1} COMPRESS(${tmp2})n";
$tmp1=&eraseCompress($tmp1);
if ($lastcomma==0){
print OF $tmp1;print OF " COMPRESS(".${tmp2}.")"; print OF "n";
}
else{
#最後的逗號去掉：
$tmp1=~s/,$//;
print OF $tmp1;print OF " COMPRESS(".${tmp2}."),"; print OF "n";
}
}
else{
print OF $tmp1;print OF "n";
}
}
}
}
}

#檢查將被壓縮的值佔總資料量之比,低於使用者指定值不進行壓縮
sub getZb
{
my ($field1,$cplist,$ftype)=@_;
my ($incon);
if ($Count==0) {
return 0;
}
$cplist="(".$cplist.")";

if ($ftype eq "DATE"){
$incon="CAST(${field1} AS INTEGER)";
}
else{
$incon=$field1;
}

my $sqlText =<SELECT COUNT(1) FROM ${DATABASE}.${tablename} WHERE ${incon} in ${cplist} ;
ENDOFSQL
print "取壓縮數量語句:${sqlText}";
my $sth = $dbcon->prepare($sqlText);
$sth->execute();
my @res = $sth->fetchrow;
$sth->finish();
$sm=$res[0];
$zb=($sm/$Count)*100;
print "壓縮資料記錄數${sm}/總記錄數${Count}=壓縮資料佔比${zb}.";
return $zb;
}

#獲取表總資料量
sub GetCount
{
my $sqlText =<SELECT COUNT(1) FROM ${DB_VIEW}.${tablename};
ENDOFSQL

my $sth = $dbcon->prepare($sqlText);
$sth->execute();
my @res = $sth->fetchrow;
$sth->finish();
$Count=$res[0];
return $Count;
}

#去掉語句中的壓縮相關的字元，為的是可以二次壓縮
sub eraseCompress
{
my($tmp)=@_;
if ($tmp =~/s+COMPRESS/i){
$tmp=~s/COMPRESS[A-Z0-9-.,()'?>}
return $tmp;
}

#判斷欄位是否為索引中的P/Q/K之一，如果是則不取壓縮資訊：
#判斷欄位型別是否為D, DA, F, I1, I2, I ,CF，如果不是則不壓縮
sub isIndex
{
my( $field1)=@_;

my $sqlText =<

SELECT
TRIM(DATABASENAME)
,TRIM(TABLENAME)
,TRIM(COLUMNNAME)
FROM dbc.columns
WHERE databasename = '${DATABASE}'
AND tablename='${tablename}'
AND columnname='${field1}'
AND columntype IN ('D', 'DA', 'F', 'I1', 'I2', 'I','CF')
AND (databasename, tablename, columnname)
NOT IN (SELECT databasename, tablename, columnname FROM DBC.indices where indextype in ('K', 'P','Q')
AND databasename = '${DATABASE}'
AND tablename='${tablename}'
AND columnname='${field1}'
)
AND (DATABASENAME, TABLENAME)
IN (SELECT DATABASENAME, TABLENAME FROM DBC.TABLES WHERE TABLEKIND = 'T'
AND databasename = '${DATABASE}'
AND tablename='${tablename}'
)
ORDER BY databasename,tablename,columnname,columnid;

ENDOFSQL
my $sth = $dbcon->prepare($sqlText);
$sth->execute();
my @res = $sth->fetchrow();
$sth->finish();
return @res;
}

#判斷是否為分割槽欄位，是的話不壓縮
sub isPPI
{
my $colname =shift;
my @ppi;
open(DDLFILE, "${DDLFile}") || die "cann't open ${DDLFile}:$!";
my @ddlline = ;
close(DDLFILE);
my ($i,$tmpline);
#print $#ddlline; print "n";
for ($i=0;$i<=$#ddlline;$i++){
$tmpline=$ddlline[$i];

if ($tmpline =~ /PARTITION BY/){
@ppi =split(/(/,$tmpline);
@ppi = split(/s+/,$ppi[1]);
}
}
if($ppi[0] eq $colname){return 1;}
else{return 0;}

}

#獲得統計資訊，根據統計資訊生成要壓縮的值，如：'5','2','','-1'
sub getMax
{
my( $field1,$fieldtype1)=@_;
my ($cols,$tmpval);
my ($sqlText);
if (${fieldtype1} eq "DATE"){
$sqlText =<

SELECT CAST(${field1} AS INTEGER) AS ${field1},rank(num) AS rankno FROM (
SELECT ${field1},count(1) AS num FROM ${DB_VIEW}.${tablename} GROUP BY 1 WHERE ${field1} IS NOT NULL
) tmp
qualify rankno<=${compval};
ENDOFSQL
}
else{
$sqlText =<select ${field1},rank(num) AS rankno FROM (SELECT ${field1},count(1) AS num FROM ${DB_VIEW}.${tablename} GROUP BY 1 WHERE ${field1} IS NOT NULL
) tmp QUALIFY rankno<=${compval};
ENDOFSQL1
}

print "收集資訊sql:n".$sqlText."n";
my $sth = $dbcon->prepare($sqlText);
$sth->execute();

my @row;
my $map_info="";
my $linecount = 0;
my $hasdata = 0;
while(@row = ($sth->fetchrow())){
$hasdata=1;
$tmpval=$row[0];
$tmpval=~s/s+//g; #踢出空格等
if ($linecount == 0) { #第一個壓縮值，沒有前導逗號
if (${fieldtype1} eq "CHAR"){ #字串型，需新增單引號
$cols="'".${tmpval}."'";
}
elsif (${fieldtype1} eq "DATE"){ #日期型
$cols=${tmpval};
}
else{ #其他類數值型：
$cols=${tmpval};
}

} else {
if (${fieldtype1} eq "CHAR"){ #字元型，需新增單引號
$cols=$cols.",'".${tmpval}."'";
}
elsif (${fieldtype1} eq "DATE"){ #日期型
$cols=$cols.",".${tmpval};
}
else{ #其他類數值型：
$cols=$cols.",".${tmpval};
}
}
$linecount ++;
last if (${linecount}>=${compval}) ;
};

$sth->finish();

#最後連線上0值空值空預設日期等資料：
if ($hasdata==1){
if (${fieldtype1} eq "CHAR"){ #串字元型，需新增單引號
if ($cols!~/''/){ #若沒有空值壓縮，加上
$cols=$cols.",''";
}
#若沒有'-1'壓縮，加上
if (($cols!~/,'-1'$/) && ($cols!~/^'-1',/) && ($cols!~/,'-1',/) && ($cols!~/^'-1'$/) ){
$cols=$cols.",'-1'";
}
# 進一步對char型資料中含有的日期資料做壓縮
$cols=&addMonth($fieldtype1,$cols);
}
elsif (${fieldtype1} eq "DATE"){ #日期型
if (($cols!~/,101$/) && ($cols!~/^101,/) && ($cols!~/,101,/) && ($cols!~/^101$/)){
$cols=$cols.",101";
}
if ($cols!~/11001231/){
$cols=$cols.",11001231";
}
if ($cols!~/1070430/){
$cols=$cols.",1070430";
}
}
else{ #其他類數值型：
if (($cols!~/^0$/) && ($cols!~/,0$/) && ($cols!~/^0,/) && ($cols!~/,0,/) && ($cols!~/^0.0+$/) && ($cols!~/,0.0+$/) && ($cols!~/^0.0+,/) && ($cols!~/,0.0+,/) ){
$cols=$cols.",0";
}
# 進一步對其他數值型資料中含有的日期資料做壓縮
$cols=&addMonth($fieldtype1,$cols);
}
}
else{
if (${fieldtype1} eq "CHAR"){ #字串型，需新增單引號
$cols=$cols."'','-1'";
}
elsif (${fieldtype1} eq "DATE"){ #日期型
$cols=$cols."101,11001231,1070430";
}
else{ #其他類數值型：
$cols=$cols."0,1";
}
}
return $cols;
print "cols:${cols}n";
}

#進一步對getMax中得到的要壓縮的值中的年月資訊做壓縮
sub addMonth
{
my ($fieldtype2,$compcon)=@_; #fieldtype2 CHAR,數值
my ($i,$j,$mon)=(0,0,'');

if ( ${fieldtype2} eq "CHAR" ) {
if ( $compcon=~/'200[5-8][01][0-9]'/ ) {
for ($i=2007;$i<=2010;$i++) {
for ($j=1;$j<=12;$j++) {
if ($j<=9) {
$mon="0${j}";
}
else {
$mon=$j;
}
if ($compcon!~/'${i}${mon}'/) {
$compcon=$compcon.",'${i}${mon}'";
}
}
}
}
}
else {
if (($compcon=~/^200[5-8][01][0-9]$/) || ($compcon=~/,200[5-8][01][0-9]$/)
||($compcon=~/^200[5-8][01][0-9],/) ||($compcon=~/,200[5-8][01][0-9],/) ) {
for ($i=2007;$i<=2010;$i++) {
for ($j=1;$j<=12;$j++) {
if ($j<=9) {
$mon="0${j}";
}
else {
$mon=$j;
}

if ($compcon!~/${i}${mon}/){
$compcon=$compcon.",${i}${mon}";
}
}
}
}
}
return $compcon;
}

#獲取使用者配置資料,配置資料作為壓縮語句生成指導,提高sql生成效率
#配置資料主要是除varchar,pi,ppi以外的不希望被壓縮的列,各列逗號分開
sub GetConfig
{
my $cfgfile =shift;
my @ppi;
open(CFGFILE, "${cfgfile}") || die "cann't open ${cfgfile}:$!";
my @colline = ;
close(CFGFILE);
my ($i,$tmpline);
for ($i=0;$i<=$#colline;$i++){
$tmpline=$tmpline.",".$colline[$i];
}
return $tmpline;
}