文章图片

简介 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_custkeyINTEGER NOT NULL encode delta, c_nameVARCHAR(65535) NOT NULL encode zstd, c_addressVARCHAR(65535) NOT NULL encode zstd, c_cityVARCHAR(65535) NOT NULL encode zstd, c_nationVARCHAR(65535) NOT NULL encode zstd, c_regionVARCHAR(65535) NOT NULL encode zstd, c_phoneVARCHAR(65535) NOT NULL encode zstd, c_mktsegmentVARCHAR(65535) NOT NULL encode zstd ) diststyle even;

*左滑查看更多
【Amazon Redshift 表设计优化 – 优化已有数据表中的列大小】其次，执行如下 SQL 脚本将测试数据导入 test_schema.customer 表。测试数据位于LoadingDataSampleFiles.zip 压缩包中。

copy test_schema.customerfrom 's3:///load/customer-fw.tbl'credentials 'aws_access_key_id=; aws_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_newvar_schema_name || '.' || var_table_name || var_postfix; EXECUTE 'drop tableif 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 脚本用于检查数据表中的列大小是否已经调整。

selecta.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...

本篇作者

文章图片

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

文章图片

扫描上方二维码即刻注册

推荐阅读

• 

  分期 肛裂如何分类？
• 

  武装突袭怎么修车 武装突袭怎么开车
• 

  k9x|潮玩“硬核”旗舰，OPPO K9x图赏
• 

  史密斯热水器加热多久可以洗澡 史密斯热水器烧一次能用多久
• 

  乙醚易燃吗
• 

  抖音网红收入最高的前十名 抖音网红怎么赚钱
• 

  膝关节|上了年纪，膝盖疼痛，真的需要“换关节”么？
• 

  买什么洗发水好
• 

  金融全牌照有哪些公司
• 

  对合作伙伴的分析,主要合作伙伴分析怎么写
• 

  离核的桃有什么好品种
• 

  最适合炒股的笔记本电脑，想买笔记本电脑游戏和炒股用的求推荐
• 

  有谁能晒一下今日头条上创作带来的收益金额,谢谢？
• 

  有什么私密的聊天工具可推荐,最好是让别人不知道这是聊天软件？
• 

  化妆水和纯露的区别是什么
• 

  三菱空调面板故障代码大全是什么故障,找找这个方面的原因
• 

  关于传染病防治失职行为法定刑罚内容有哪些
• 

  海星是蒸着吃还是煮着吃
• 

  烟灰缸里不放水是什么意思
• 

  贵人鸟董事长有几个儿子

• 基于Amazon ECS Fargate构建Apache Superset
• Apache Atlas 数据血缘
• 面向未来，我们来聊一聊什么是现代化数据架构
• Amazon DynamoDB 在智能湖仓架构中的实践
• 呼叫医生云！ Amazon HealthLake 正式推出
• 在Amazon SageMaker中灵活使用多种存储服务