亚马逊AWS官方博客
在最大程度上优化 Amazon Redshift 上的数据提取与报告性能
这是一篇由 ZS 提供的客座博文。用他们自己的话来说：“ZS 是一家专业服务公司，它与其他公司密切合作以帮助其开发与交付能为客户创造价值，并且提升公司业绩的产品和解决方案。ZS 所经营的业务融合了技术、咨询、分析和运营，其目标是改善客户的商业体验。”
ZS 曾参与设立和运行基于 MicroStrategy 的 BI 应用，通过 Amazon Redshift 处理700 GB的数据，并将其作为 Amazon 托管后端架构的数据仓库。ZS 从不同系统（如 Amazon S3 存储桶和 FTP 系统等）的多家医药数据提供商那里取得健康医疗数据，然后传送到数据湖中。他们使用瞬态 Amazon EMR 集群处理此类数据，将其存储在 Amazon S3 上供报告使用。用于报告的特定数据会通过 COPY 命令被移动到 Amazon Redshift，而 MicroStrategy 则会使用它们来刷新前端仪表面板。
ZS 有严格的客户设定的 SLA，以及相应的 Amazon Redshift 基础设施。为了找到使用小型 Amazon Redshift 集群处理大型数据卷的方法，我们开展了大量实验。
本文提供了从 S3 将大型数据卷加载到 Amazon Redshift 的方法，并运用有效的分发技术优化在相对小型的 Amazon Redshift 集群上运行报告查询的性能。
数据处理方法
ZS 基础设施在 AWS 上托管；他们还会使用 AWS 服务在 AWS 上存储与处理来自多家提供商的医药行业数据，之后在 MicroStrategy BI 报告工具上报告此类数据。下图显示了从平面文件到展示给最终用户的 MicroStrategy 报告的整体数据流。
第 1 步：从多家提供商和不同的系统（如 FTP 位置、个别系统和 Amazon S3 存储桶等）获取医药数据。
第 2 步：在必要时运行高成本效益的瞬态集群，为执行 pyspark 代码提供计算能力。
第 3 步：在处理以后，数据会被存储在 Amazon S3 存储桶，以供下游应用使用。
第 4 步：然后，将 700 GB 数据提取到 Amazon Redshift，供 MSTR (MicroStrategy)使用。
第 5 步：从 Amazon Redshift 读取此类数据，并在 MicroStrategy 上以报告的形式向最终用户展示详细见解。
考虑中的数据集
在此特定的情况下，ZS 会处理来自医药领域的数据。下表显示的是数据的典型结构：它有多个医生、患者、治疗相关 ID 和医疗护理指标。
| 表 1 | ||
| 列名 | EMR 数据类型 | Amazon Redshift 数据类型 |
| Time ID | integer | int |
| Geography ID | integer | int |
| Product ID | integer | int |
| Market ID | integer | int |
| Doctor ID | integer | int |
| Doctor Attribute 1 ID | integer | int |
| Doctor Attribute 2 ID | integer | int |
| Doctor Attribute 3 ID | integer | int |
| Doctor Attribute 4 ID | integer | int |
| Doctor Rank | integer | int |
| Metric 1 | double | decimal(18,6) |
| Metric 2 | double | decimal(18,6) |
| Metric 3 | double | decimal(18,6) |
| Metric 4 | double | decimal(18,6) |
| Metric 5 | double | decimal(18,6) |
| Metric 6 | double | decimal(18,6) |
| Metric 7 | double | decimal(18,6) |
| Metric 8 | double | decimal(18,6) |
| Metric 9 | double | decimal(18,6) |
| Metric 10 | double | decimal(18,6) |
| Metric 11 | double | decimal(18,6) |
| Metric 12 | double | decimal(18,6) |
| Metric 13 | double | decimal(18,6) |
| Metric 14 | double | decimal(18,6) |
| Metric 15 | double | decimal(18,6) |
| Metric 16 | double | decimal(18,6) |
| Metric 17 | double | decimal(18,6) |
| Metric 18 | double | decimal(18,6) |
| Metric 19 | double | decimal(18,6) |
| Metric 20 | double | decimal(18,6) |
| Metric 21 | double | decimal(18,6) |
| Metric 22 | double | decimal(18,6) |
| Metric 23 | double | decimal(18,6) |
| Data Snapshot Date | timestamp | timestamp |
| Data Refresh Date | timestamp | timestamp |
| Data Refresh ID | string | varchar |
每个表都有大约 35-40 个列，以及大约 2-2.5 亿行数据。ZS 使用 40 个这样的表；他们从多家健康护理数据提供商那里获得此类表中的数据，然后根据报告需要对其进行处理。
若采用 CSV 格式，总数据集的大小约为 2 TB；如果采用 Parquet 格式则约为 700 GB。
挑战与限制
上文概述的数据刷新和见解生成的五步骤流程在指定时段的周末执行。默认时，在一个 2node ds2.8xlarge 集群上从 S3 将未优化状态的数据加载到 Amazon Redshift，以及 MicroStrategy 刷新（上图中的第 4 步）需要差不多 13-14 个小时，而且会影响整体周末运行 SLA（1.5 个小时）。
下图概述了 ZS 为满足客户需求必须要突破的三项限制：
基于每周时间的 SLA—在 1 个小时内加载，并在 1.5 个小时内获取 MSTR 上的数据
客户的 IT 和业务团队会制定严格的 SLA，以便将 700 GB Parquet 数据（相当于采用 CSV 的 2 TB）加载到 Amazon Redshift ，并且会在 MicroStrategy BI 工具上刷新报告。在此情况下，客户的团队从原来的其他提供商那里转而求助于 AWS，他们的整体期待是降低成本，而不会对性能造成严重的负面影响。
固定的集群大小—预先确定的 2 node ds2.8xlarge 集群
客户的 IT 团队确定集群大小和配置，并考虑成本、数据卷和加载模式。这些是固定不可调整的：一个 2 node ds2.8xlarge 集群。ZS 会开展 PoC，以优化受这些限制影响的环境。
大型数据卷—以 Parquet 格式截断加载 700GB 数据
ZS 使用的数据都与医药领域有关。此情况下考虑的数据集有 700 GB，且采用 Parquet 格式。在此特定的用例中，即使是历史数据也会在每次刷新时更新，很多数据都无法被附加。因此，我们采用了截断和加载流程。
迭代优化
由于时间、数据卷和集群大小的限制，ZS 开展了各种实验来优化 Amazon Redshift 数据加载和读取时间，它们也是衡量性能的两项关键因素。ZS 构建了有助于执行以下操作的迭代框架：
- 确定文件格式
- 通过分配键和排序键定义最优数据分布
- 确定并行化数据加载流程的技术
下表显示了可在任何 Amazon Redshift 集群上获得最佳数据加载和读取性能的关键步骤。
数据加载优化
我们确定并优化了影响数据加载性能的四项关键因素：文件格式、源文件大小、并发性和列编码。
文件格式
很多项目通常都会以 CSV 格式从 S3 加载数据到 Amazon Redshift。ZS 还以 Parquet 格式提供数据，将 snappy 压缩用于 Spark 进程的输出。（此组合最适用于 Spark 进程。）
要确定适用于 Amazon Redshift 的高效格式，我们比较了 Parquet 和常用的 CSV 和 GZIP 格式。我们从 S3 加载表，它有 2 亿行数据通过 Spark 进程生成，相当于采用 CSV 格式的 41 GB，Parquet 的 11 GB 和 GZIP 的 10 GB，而且我们还比较了加载时间和 CPU 利用率。下表显示了以不同文件格式保存的相同数据的加载时间和 CPU 利用率的对比。
针对我们要解决的数据集和限制问题，相对于 CSV 和 GZIP，以 Parquet 文件格式加载需要较低 CPU 利用率和较少 I/O，在 S3 上的内存占用也比内存密集型的 CSV 格式小。在此情形中的较低 CPU 利用率实现了更并行化的加载，从而缩短了加载 Parquet 文件所需的整体运行时间。
源文件大小
接下来要选择的是 Parquet 文件被拆分并存储在 S3 上所使用的块大小。Spark 作业常用的块大小为 128 MB，它被视为最适用于数据处理。不过，大尺寸文件更能发挥出 Amazon Redshift 的优点。
我们加载了 10 GB Parquet 数据，以 250 MB、750 MB、1 GB、1.5 GB 和 3 GB 块大小将其拆分成大小相当的较小文件，并留意每种情况的性能。下图显示的是不同的加载时间。
数据加载时间逐渐优化，直到块大小达到 1 GB（此时的加载时间最短）。在超出 1 GB 以后，处理较大文件时的性能有所下降，而且 Amazon Redshift 需要花更长时间处理较大文件。
这些数据针对我们所处理的不同文件类型而各有差异。随着数据形式和形状的更改，我们的建议也不一样。
最好的做法是，确定数据块大小，并以 Amazon Redshift 集群切片数量的倍数作为文件的数量。这样做可以确保每个切片都能承担相同的工作量，而且不会有空闲切片，从而提高效率并优化性能。如需更多信息，见使用 Amazon Redshift 进行高性能 ETL 处理的 8 大最佳实践。
并发性
COPY 命令所占用的内存相对较低。加载的并行化程度越高，性能也会随之改善。ZS 会通过独立的并发性设置多次加载大小约为 7.3 GB 的表。我们使用每 GB 文件所需的平均时间来衡量以 1 到 20 个并发加载将文件移动到 Amazon Redshift 的吞吐量。下表对结果进行了总结。
| 测试 | 并行加载的表的数量（并发性） | 加载的数据总量 (GB) |
| Test 1 | 1 | 7.3 |
| Test 2 | 5 | 36.5 |
| Test 3 | 10 | 73 |
| Test 4 | 15 | 109.5 |
| Test 5 | 20 | 146 |
下图显示了加载 1GB 数据所用的时间，以及不同并发性设置的 CPU 利用率。
针对我们要解决的数据集和限制问题，特定数据集的吞吐量在并发性为 10 时达到最高，CPU 的可用缓冲区约为 25%。根据数据的性质和每次释放的数据量变化，您可以选择不同的缓冲区。
列编码
要确定能在 Amazon Redshift 上提供最佳性能并占用更小内存的列编码和压缩，ZS 比较了 ZSTD（ANALYZE COMPRESSSION 命令推荐）、LZO 和无编码格式 Amazon Redshift 表的加载性能。下图显示了采用无列编码、ZSTD 和 LZO 列编码的表加载相同的数据卷所需的时间。
针对我们要解决的数据集和限制问题，采用 ZSTD 列编码可以为我们的使用案例提供较高的压缩率（约为 3，与未使用压缩相比）和最佳数据复制性能，而且在 Amazon Redshift 上占用较小存储空间。根据数据类型和数据基数，您可能会得到不同的结果。
注意：此解决方案在 AZ64 编码功能发布前就已被执行，因此未考虑它的影响。您可以采用本博文中所描述的方法，在所有 Amazon Redshift 支持的压缩编码中考虑选择 AZ64 压缩编码。
数据读取优化
ZS 还通过使用分配键和排序键，以及 SQL 优化（将 MicroStrategy 自动生成 SQL 查询的过滤器最小化）改善了 MicroStrategy 从 Amazon Redshift 读取数据的性能。
SQL 查询
MicroStrategy 是一种商业智能工具，能通过智能构建自己的 SQL 从数据库读取数据。我们比较了有过滤器和无过滤器 MSTR SQL（典型的 DW 查询，如 SELECT、GROUP BY，或临时表）的性能，并观察了在几乎同时运行而且使用相同资源的数据集上运行的有或无过滤器的查询。无过滤器查询的数据均多出四倍。下表对结果进行了总结。
| 有或无过滤器？ |
获取的行数 | 查询的运行时间 | CPU 利用率 |
| Y |
500K |
5.1（分钟） |
15% |
| N | 2M | 5.1（分钟） | 15% |
与使用较多过滤器进行数据读取相比，调整 SQL 以使其在最大程度上减少或完全不使用过滤器可以帮助获取明显更多的行数，而处理时间仅略微增加。如需使用完整的数据集，您最好通过一条查询获取完整的数据，而不是并行运行多条有独立过滤器的 SQL 查询。
分配键和排序键
下表显示的是在我们的使用案例中使用与不使用分配键及排序键时对表的加载和读取性能的分析。
| 分配样式 | 分配键 | 排序键 | 加载时间（分钟） | 查询运行时间（分钟） | 查询的 CPU 利用率 |
| 无 | – | – | 16.7 | 59.5 | 59% |
| 键 | 最合适的分配键（根据均匀分配的查询） | WHERE 子句中的 6 列，按照 SQL 中的子句分组排序 | 17.5 | 36.1 | 32% |
无分配键表的加载速度稍快。不过，两条查询之间的数据读取时间和 CPU 利用率差异明显。这意味着如果对键进行适当设置，越来越多的并行加载均可高效运行，因为整体 CPU 利用率大幅降低。我们的主要收获是，采用分配样式—自动、均匀或手动—可优化从 Amazon Redshift 读取数据，并允许更多并行处理。ZS 基于对数据和刷新频率的深入理解使用了手动分配样式并选择分配和排序键。
从您的 Amazon Redshift 实例获得最佳输出的后续步骤
多次 POC 测试所得到的结果之一是，我们确定了最合适的文件格式、压缩技术和重新分区 Parquet 文件的块大小，以及分配和交错排序逻辑，以在 Amazon Redshift 作为数据库时为我们的数据集的 MicroStrategy 报告提供最佳性能。这有助于我们确定最佳的数据加载和读取组合，使用可用的固定 2 node ds2.8xlarge 集群在客户端集 2.5 小时 SLA 内加载 700 GB Parquet 数据（相当于 2 TB CSV 数据）。
下图显示了您可以用来确定适用于 Amazon Redshift 集群配置的最佳数据加载和读取技术的迭代过程。
下列是若干关键的收获：
- Parquet 和 Amazon Redshift 可完美兼容。采用 Parquet 格式的数据的 CPU 利用率和对 I/O 的要求较低，可允许更多并行加载。
- ZSTD 编码最适用于此特定数据集，因为它也会对数字进行编码。
- 相对于不使用分配和排序逻辑的表，在表上应用排序键和分配键可将读取时间缩短约 80%。
- 在 Amazon Redshift 上过滤数据和在一般数据库上的不同。您可以通过适当的排序键优化数据过滤性能。
- 源文件大小和并发性密切相关，您应该根据实际情况进行选择。在 Amazon Redshift 上加载较大数据块（针对此特定数据集，最大为 1 GB）的速度更快。
关于作者
Vasu Kiran Gorti 是一位以结果为导向的专业人士，他所拥有的技术和功能/领域经验主要集中在销售及市场推广咨询。他在 ZS Associates 担任助理顾问职位，经常与生命科学及医疗护理客户开展合作以帮助其达成业务目标和期待。他擅长于 MicroStrategy、商业智能、分析和报告。Vasu 具有前瞻性创新思维，喜欢投身于可缩小技术和业务差距的激励性活动当中。他不断试验，总是在学习、创新和发展。
Ajit Pathak 是 ZS Associates 的技术顾问，负责领导医药公司的 BI 和数据管理项目。他感兴趣而且擅长的领域包括 MSTR、Redshift 和 AWS 套件。他热衷于设计复杂的应用与推荐最佳的架构实践，以打造高效而简洁的控制面板。作为一名出色的技术顾问，Ajit 专注于通过清晰的数据通信促进正确的业务决策的做出。除了研究数据可视化和应用，Ajit 还喜欢阅读、打羽毛球，以及参与从政治到体育等各种话题的辩论。