关于存储:Amazon-Redshift-表设计优化-–-优化已有数据表中的列大小

Amazon Redshift 数据仓库是种疾速且齐全托管的数据仓库服务，让您能够应用规范 SQL 和现有的商业智能工具经济高效地剖析您的所有数据，提供优质的数据仓库解决方案。

在布局 Amazon Redshift 数据库时，某些要害表的设计对整体查问性能影响很大。这些设计的优化能够缩小 I/O 操作数和尽量减少解决查问所需的内存，因此对存储需要以至查问性能也有很大的影响。

对 Amazon Redshift 表设计的最佳实际包含了以下几点：

抉择最佳的排序键
抉择最佳的调配形式
让 COPY 抉择压缩编码
定义主键和外键束缚
应用尽可能小的列大小
在日期列中应用日期 / 工夫数据类型

本文中，咱们会聚焦于“应用尽可能小的列大小”这一点，介绍如何通过 SQL 脚本的形式半自动化地实现对 Amazon Redshift 数据表中列大小的优化。

应用尽可能小的列大小的必要性

依据亚马逊云科技官网文档上所形容的，尽管 Amazon Redshift 在数据压缩方面十分杰出，定义过大的列长度对数据表自身的大小不会造成很大影响。然而，在运行一些简单的查问时，因为两头过程数据会被长期存储，而这时创立的长期表不会被指定压缩格局，这样就会造成查问占用过多的内存或者长期磁盘空间的景象，从而导致查问性能的升高。

https://docs.aws.amazon.com/z…

测试环境筹备

首先，运行如下 SQL 脚本在你的 Amazon Redshift 数据库中新建一张数据表 test_schema.customer 用于测试。

CREATE SCHEMA test_schema;

CREATE TABLE test_schema.customer
(
  c_custkey      INTEGER NOT NULL encode delta,
  c_name         VARCHAR(65535) NOT NULL encode zstd,
  c_address      VARCHAR(65535) NOT NULL encode zstd,
  c_city         VARCHAR(65535) NOT NULL encode zstd,
  c_nation       VARCHAR(65535) NOT NULL encode zstd,
  c_region       VARCHAR(65535) NOT NULL encode zstd,
  c_phone        VARCHAR(65535) NOT NULL encode zstd,
  c_mktsegment   VARCHAR(65535) NOT NULL encode zstd
) diststyle even;

* 左滑查看更多

其次，执行如下 SQL 脚本将测试数据导入 test_schema.customer 表。测试数据位于 LoadingDataSampleFiles.zip 压缩包中。

copy test_schema.customerfrom 's3://<your-bucket-name>/load/customer-fw.tbl'credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10';

* 左滑查看更多

当初，数据表 test_schema.customer 中的 varchar 字段的长度都被定义为了 65535。运行以下 SQL 脚本，能够看到理论数据中的字段长度远远小于 65535。

select
    max(len(c_name)) as c_name,
    max(len(c_address)) as c_address,
    max(len(c_city)) as c_city,
    max(len(c_nation)) as c_nation,
    max(len(c_region)) as c_region,
    max(len(c_phone)) as c_phone,
    max(len(c_mktsegment)) as c_mktsegment
from test_schema.

* 左滑查看更多

优化现有数据表中列大小的操作过程

接下来，咱们会通过上面的八个步骤，来实现对数据表 test_schema.customer 的优化工作，将数据表中所有 varchar 数据类型的列大小进行优化，依据已有数据的最大长度来进行相应的压缩。

第一步，在 Amazon Redshift 数据库中创立存储过程 proc_replicate_table_with_resized_columns。这个存储过程提供了 4 个参数，别离是：

var_schema_name varchar(50)，该参数用于指定您须要进行列大小的优化的数据表的 schema。
var_table_name varchar(50)，该参数用于指定您须要进行列大小的优化的数据表的表名。
var_postfix varchar(50)，该参数用于在原表名后附加后缀名，作为新创建数据表的表名。
var_ratio decimal(19,2)，该参数用于指定一个系数，将列大小调整为该列最大长度乘以该系数。

首先，该存储过程会创立一个和指定数据表一样表构造的新数据表，该新表的名称会在原先表的名称后附加一个您指定的后缀；其次，该存储过程会查看指定数据表中所有 varchar 数据类型的列，如果改列在现有数据中的最大长度乘以一个系数之后，依然小于表定义中原先设定的长度，则会生成一个 SQL 脚本来调整新创建表中该列的长度，长度被调整为 round(column_actual_len * var_ratio)。

create or replace procedure proc_replicate_table_with_resized_columns
        (var_schema_name in varchar(50),
        var_table_name in varchar(50),
        var_postfix in varchar(50),
        var_ratio in decimal(19,2)
    )
    language plpgsql
as $
DECLARE
    sql_text varchar(65535);
    table_full_name varchar (100) ;
    table_full_name_new varchar (100) ;
    rec RECORD;
BEGIN
    select into table_full_name var_schema_name || '.' || var_table_name;
    select into table_full_name_new  var_schema_name || '.' || var_table_name || var_postfix;

    EXECUTE 'drop table  if exists'  || table_full_name_new;

    --create a new table with the same schema
    EXECUTE 'create table'  || table_full_name_new || '(like' || table_full_name || ')';

    -- Get the temp table for all columns for a table, temp_table_column_scripts
    drop table if exists temp_table_column_scripts;
    create temp table temp_table_column_scripts(script varchar);

    insert into temp_table_column_scripts
    select  'select''' || column_name || '''as column_name,' ||
       'max(len(' || column_name ||')) as column_actual_len,' ||
       cast(character_maximum_length as varchar) || 'as column_def_len' ||
       'from' || table_schema || '.' || table_name as column_script
    from svv_columns
    where table_schema = var_schema_name and table_name = var_table_name and data_type = 'character varying';

    --loop to insert the column info into temp_table_column_info
    drop table if exists temp_table_column_info;
    create temp table temp_table_column_info(column_name varchar, column_actual_len int, column_def_len int);

    FOR rec IN SELECT script FROM temp_table_column_scripts
      LOOP
        RAISE INFO 'insert into temp_table_column_info %', rec.script;
        EXECUTE 'insert into temp_table_column_info' || rec.script;
      END LOOP;

    --Generate temp table for alter scripts
    drop table if exists temp_table_alter_scripts;
    create temp table temp_table_alter_scripts as
    SELECT 'alter table' || table_full_name_new || 'alter column' ||
           column_name || 'type varchar(' || cast(round(column_actual_len * var_ratio) as varchar) || ');'
    FROM temp_table_column_info
    where round(column_actual_len * var_ratio) < column_def_len;

END;
$;

* 左滑查看更多

第二步，执行上一步中创立进去的存储过程，并从后果表中取得用于优化列大小的 SQL 脚本。

具体操作能够参考以下 SQL 脚本，请将脚本中 {s3-bucket-name}、{ACCESS_KEY_ID}、{SECRET_ACCESS_KEY} 依据理论状况进行替换。

call proc_replicate_table_with_resized_columns('test_schema', 'customer', '_resize_columns', '1.3'); select * from temp_table_alter_scripts;

* 左滑查看更多

返回后果如下图所示：

* 左滑查看更多

第三步，运行上一步中从 temp_table_alter_scripts 表中返回的 SQL 脚本。

第四步，运行以下 SQL 脚本用于查看数据表中的列大小是否曾经调整。

select  a.table_name, a.column_name, a.data_type,
       a.character_maximum_length as as_is_length, b.character_maximum_length as to_be_length
from svv_columns as a
inner join svv_columns as b
    on a.table_name + '_resize_columns' = b.table_name
    and a.column_name = b.column_name
where a.table_schema = 'test_schema'
  and a.table_name = 'customer'
  and b.table_name = 'customer_resize_columns';

* 左滑查看更多

运行后果如下图所示。能够看到，数据表中的 varchar 类型字段的长度都根据现有数据理论长度进行了调整。

第五步，将原数据表中的数据 UNLOAD 到指定的 Amazon S3 门路中。具体操作能够参考以下 SQL 脚本，请将脚本中 {s3-bucket-name}、{ACCESS_KEY_ID}、{SECRET_ACCESS_KEY} 依据理论状况进行替换。

unload ('select * from test_schema.customer')
to 's3://{s3-bucket-name}/resize-redshift-columns/customer/'
ACCESS_KEY_ID '{ACCESS_KEY_ID}'
SECRET_ACCESS_KEY '{SECRET_ACCESS_KEY}'
GZIP;

* 左滑查看更多

第六步，将 S3 中的数据 COPY 到新建数据表中。

具体操作能够参考以下 SQL 脚本，请将脚本中 {s3-bucket-name}、{ACCESS_KEY_ID}、{SECRET_ACCESS_KEY} 依据理论状况进行替换。

copy test_schema.customer_resize_columns
from 's3://{s3-bucket-name}/resize-redshift-columns/customer/'
ACCESS_KEY_ID '{ACCESS_KEY_ID}'
SECRET_ACCESS_KEY '{SECRET_ACCESS_KEY}'
GZIP;

* 左滑查看更多

第七步，查看新建数据表中的数据是否与原数据表中的数据完全一致。

运行以下 SQL 脚本，如果返回后果为空，则代表新建数据表中的数据与原数据表完全一致。如果返回有数据，则须要查看之前的步骤是否操作有误，导致数据不统一。

select * from test_schema.customer
except
select * from test_schema.customer_resize_columns
union all
select * from test_schema.customer_resize_columns
except
select * from test_schema.customer

* 左滑查看更多

第八步，重命名数据表，使得新建数据表替换原数据表，并在替换之后删除原数据表。

在该步骤中，首先将原数据表重命名，例如：增加_original 后缀；而后，将新建数据表的名称重命名为原数据表的名称；最初，删除原数据表（留神：删除的数据表是带_original 后缀的数据表）。

具体操作能够参考以下 SQL 脚本：

alter table test_schema.customer
rename to customer_original;

alter table test_schema.customer_resize_columns
rename to customer;

drop table test_schema.customer_original;

* 左滑查看更多‍‍‍‍‍‍‍

通过以上的八个步骤，您就实现了对数据表 test_schema.customer 内所有 varchar 类型列大小的优化工作。

本文探讨了 Amazon Redshift 表设计的最佳实际，专一于应用尽可能小的列大小这一优化点，介绍了如何通过脚本的形式对 Amazon Redshift 数据中现有数据表的列大小进行优化。依据数据表中现有数据的最大列长度，对表定义中的列长度进行适当压缩。

须要指出的是，本文中每一个步骤的脚本还须要独自手工运行并查看运行后果。如果有需要的话，能够编写自动化工作流，在工作流中集成各个步骤中的脚本，从而实现对一个数据库中一张或多张数据表的自动化优化。

参考资料

Amazon Redshift 文档：https://docs.aws.amazon.com/z…

本篇作者

姚亮

亚马逊云科技高级解决方案架构师

负责跨国企业客户的解决方案征询，利用架构设计和优化，同时致力亚马逊云科技数据分析类服务的利用和推广。

扫描上方二维码即刻注册

关于存储:Amazon-Redshift-表设计优化-–-优化已有数据表中的列大小

简介

论断及瞻望