测试压缩编码 - Amazon Redshift
示例

测试压缩编码

如果您决定手动指定列编码，则您可能需要针对自己的数据测试不同的编码。

注意

我们建议您尽可能使用 COPY 命令加载数据，并允许 COPY 命令根据您的数据选择最佳的编码。或者，您也可以使用 ANALYZE COMPRESSION 命令查看推荐针对现有数据采用的编码。有关应用自动压缩的详细信息，请参阅使用自动压缩加载表。

要执行有意义的数据压缩测试，您必须有大量的行。在本示例中，我们将使用从两个表 VENUE 和 LISTING 中进行选择的语句来创建表并插入行。我们将省略通常会联接两个表的 WHERE 子句。结果是 VENUE 表中的每行都联接到 LISTING 表中的所有行（总计超过 3200 万行）。这称作笛卡尔联接，通常不建议使用。但是，对于本例，这是创建多个行的简便方法。如果您现有的表包含想要测试的数据，则您可以跳过这一步。

在我们有一个包含示例数据的表格后，我们创建一个包含七列的表格。每个表格都有不同的压缩编码：raw、bytedict、lzo、run length、text255、text32k 和 zstd。我们通过运行从第一个表选择数据的 INSERT 命令，用完全相同的数据填充每一列。

要测试压缩编码，请执行以下操作：

  1. （可选）首先，使用笛卡尔联接创建一个包含大量行的表。如果您想要测试现有的表，则请跳过这一步。

    create table cartesian_venue(
venueid smallint not null distkey sortkey,
venuename varchar(100),
venuecity varchar(30),
venuestate char(2),
venueseats integer);

insert into cartesian_venue
select venueid, venuename, venuecity, venuestate, venueseats
from venue, listing;

  2. 接下来，使用您要进行比较的编码创建表。

    create table encodingvenue (
venueraw varchar(100) encode raw,
venuebytedict varchar(100) encode bytedict,
venuelzo varchar(100) encode lzo,
venuerunlength varchar(100) encode runlength,
venuetext255 varchar(100) encode text255,
venuetext32k varchar(100) encode text32k,
venuezstd varchar(100) encode zstd);

  3. 使用带 SELECT 子句的 INSERT 语句将相同的数据插入到所有列中。

    insert into encodingvenue
select venuename as venueraw, venuename as venuebytedict, venuename as venuelzo, venuename as venuerunlength, venuename as  venuetext32k, venuename as  venuetext255, venuename as venuezstd
from cartesian_venue;

  4. 验证新表中的行数。

    select count(*) from encodingvenue

  count
----------
 38884394
(1 row)

  5. 查询 STV_BLOCKLIST 系统表，比较每列使用的 1MB 磁盘数据块的数量。

    MAX 聚合函数返回每列的最大数据块数。STV_BLOCKLIST 表包含三个系统生成的列的详细信息。本示例在 WHERE 子句中使用 col < 6 来排除系统生成的列。

    select col, max(blocknum)
from stv_blocklist b, stv_tbl_perm p
where (b.tbl=p.id) and name ='encodingvenue'
and col < 7
group by name, col
order by col;

    查询返回以下结果。列从零开始编号。根据集群的配置方式，您的结果可能具有不同的编号，但相对大小应相差不大。对于该数据集，您会看到第二列上的 BYTEDICT 编码能够产生最佳结果。这种方法的压缩比优于 20:1。LZO 和 ZSTD 编码也生成了极佳的结果。当然，不同的数据集会导致不同的结果。当列包含较长的文本字符串时，LZO 往往能够产生最佳的压缩结果。

     col | max
-----+-----
   0 | 203
   1 |  10
   2 |  22
   3 | 204
   4 |  56
   5 |  72
   6 |  20
(7 rows)

如果您现有的表中包含数据，则您可以使用 ANALYZE COMPRESSION 命令查看针对该表的建议编码。例如，下面的示例显示了对于 VENUE 表的副本 CARTESIAN_VENUE（包含 3800 万行数据）建议的编码。注意，ANALYZE COMPRESSION 为 VENUENAME 列推荐 LZO 编码。ANALYZE COMPRESSION 基于多个因素选择最佳压缩，包括减少百分比。在这种特定情况下，BYTEDICT 提供更好的压缩，但 LZO 也会生成大于 90% 的压缩。

analyze compression cartesian_venue;

Table          | Column     | Encoding | Est_reduction_pct
---------------+------------+----------+------------------
reallybigvenue | venueid    | lzo      | 97.54            
reallybigvenue | venuename  | lzo      | 91.71            
reallybigvenue | venuecity  | lzo      | 96.01            
reallybigvenue | venuestate | lzo      | 97.68            
reallybigvenue | venueseats | lzo      | 98.21

示例

下面的示例创建了一个 CUSTOMER 表，该表中的列具有各种数据类型。此 CREATE TABLE 语句显示了适用于这些列的众多压缩编码可能组合中的一个。

create table customer(
custkey int encode delta,
custname varchar(30) encode raw,
gender varchar(7) encode text255,
address varchar(200) encode text255,
city varchar(30) encode text255,
state char(2) encode raw,
zipcode char(5) encode bytedict,
start_date date encode delta32k);

下表显示了为 CUSTOMER 表选择的列编码并解释了如此选择的理由：

列 数据类型 编码 说明
CUSTKEY int 增量 CUSTKEY 由唯一、连续的整数值组成。差值只是一个字节，因此，DELTA 是很好的选择。
CUSTNAME varchar(30) raw CUSTNAME 拥有包含少量重复值的大型域。任何压缩编码可能都无效。
GENDER varchar(7) text255 GENDER 是一个包含许多重复值的小型域。Text255 非常适合用于重复出现相同单词的 VARCHAR 列。
ADDRESS varchar(200) text255 ADDRESS 是一个大型域，但包含许多重复单词，如 Street Avenue、North、South 等。Text 255 和 text 32k 对于压缩重复出现相同单词的 VARCHAR 列很有用。列长度较短，因此，text255 是很好的选择。
CITY varchar(30) text255 CITY 是一个包含部分重复值的大型域。某些城市名称较其他城市名称常用得多。Text255 是很好的选择，理由与 ADDRESS 相同。
STATE char(2) raw 在美国，STATE 是一个由 50 个双字符值组成的精确域。Bytedict 编码能够产生一定的压缩效果，但由于列大小只有两个字符，压缩可能还抵不上解压数据产生的开销。
ZIPCODE char(5) bytedict ZIPCODE 是由不足 50000 个唯一值组成的已知域。某些邮政编码较其他邮政编码常用得多。当列包含有限数量的唯一值时，Bytedict 编码非常有效。
START_DATE date delta32k 编码 增量编码对于日期时间列非常有用，特别是当行以日期顺序加载时。