Teradata数据压缩

最新推荐文章于 2019-04-30 15:17:00 发布

转载最新推荐文章于 2019-04-30 15:17:00 发布 · 541 阅读

文章标签：

#数据库

本文介绍 Teradata 数据压缩的优势及实现方法，包括无损压缩算法的应用、压缩操作的具体步骤及批量压缩的脚本实现。

1 数据压缩的好处

Teradata数据压缩可以节省存储空间，从而让相同的存储单元存储更多的业务数据；
由于查询时需要检索的数据量相对要少，可以减少I/O,并且缓存中可以存储更多的压缩过的逻辑行，从而改善数据库性能；
Teradata使用的压缩算法非常有效，因为压缩原因，获取数据需要更少的的磁盘访问，算法将节省的CPU时间用来来执行压缩操作；
可以将压缩节省出来的空间创建高级索引；

[@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;
}

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16723161/viewspace-1013493/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16723161/viewspace-1013493/