时间戳

首页 > 解决方案 > Redshift SQL 结果集 100s 行宽效率（从长到宽）

sql - Redshift SQL 结果集 100s 行宽效率（从长到宽）

问题描述

场景：向州政府报告的医疗记录需要管道分隔的文本文件作为输入。

挑战：从事实表中选择数百个值并生成一个广泛的结果集（Redshift）UNLOADed 到磁盘。

到目前为止，我尝试的是一个我想变成 VIEW 的 SQL。

;WITH 
CTE_patient_record AS 
(
 SELECT 
   record_id 
 FROM fact_patient_record
 WHERE update_date = <yesterday>
)
,CTE_patient_record_item AS
(
 SELECT 
    record_id
   ,record_item_name
   ,record_item_value
 FROM fact_patient_record_item fpri
 INNER JOIN CTE_patient_record cpr ON fpri.record_id = cpr.record_id
)

请注意，fact_patient_record 有 87M 行，fact_patient_record_item 有 97M 行。上面的代码在 2 秒内运行 2 条测试记录，CTE_patient_record_item CTE 每条记录大约有 200 行，总共大约 400 行。

现在，生成结果集：

,CTE_result AS 
(
  SELECT 
     cpr.record_id
    ,cpri002.record_item_value AS diagnosis_1
    ,cpri003.record_item_value AS diagnosis_2
    ,cpri004.record_item_value AS medication_1
    ... 

  FROM CTE_patient_record cpr
  INNER JOIN CTE_patient_record_item cpri002 ON cpr.cpr.record_id = cpri002.cpr.record_id 
             AND cpri002.record_item_name = 'diagnosis_1'
  INNER JOIN CTE_patient_record_item cpri003 ON cpr.cpr.record_id = cpri003.cpr.record_id 
             AND cpri003.record_item_name = 'diagnosis_2'
  INNER JOIN CTE_patient_record_item cpri004 ON cpr.cpr.record_id = cpri004.cpr.record_id 
             AND cpri003.record_item_name = 'mediation_1'
  ...

) SELECT * FROM CTE_result

结果集如下所示：

record_id diagnosis_1 diagnosis_2 medication_1 ...
100001             09          9B          88X ...

...然后我使用 Reshift UNLOAD 命令写入分隔的磁盘管道。

我在一个完整的生产规模的环境中对此进行测试，但仅针对 2 个测试记录。这 2 个测试记录各有大约 200 个项目。处理输出为 2 行 200 列宽。

仅处理 2 条记录需要 30 到 40 分钟。

您可能会问我为什么要加入作为字符串的项目名称。基本上没有项目ID，没有整数，加入。很长的故事。

我正在寻找有关如何提高性能的建议。只有 2 条记录，30 到 40 分钟是不可接受的。当我有 1000 条记录时会发生什么？

我也尝试过将 VIEW 设为 MATERIALIZED VIEW，但是编译物化视图也需要 30 到 40 分钟（不足为奇）。

我不确定从这里走哪条路。

  1. 存储过程？我有存储过程的经验。
  2. 创建新表，以便我可以创建整数 id 来加入和索引？然而，我的经理们是“新表”厌恶的。
  3. ?

我可以停止前两个 CTE，将数据拉到python并使用我之前成功完成的pandas 数据帧进行处理，但如果我能有一个有效的查询，那就太好了，只需使用 Redshift UNLOAD 并完成它.

任何帮助，将不胜感激。

更新：非常感谢 Paul Coulson 和 Bill Weiner 为我指明了正确的方向！（保罗我无法支持你的答案，因为我在这里太新了）。

使用（伪代码）：

MAX(CASE WHEN t1.name = 'somename' THEN t1.value END ) AS name
...
FROM table1 t1

将执行时间从 30 分钟减少到 30 秒。原始解决方案的 EXPLAIN PLAN 为 2700 行，而使用条件聚合的新解决方案为 40 行。多谢你们。

标签： sqlperformanceamazon-redshiftcommon-table-expression

解决方案

如果没有更多信息，就不可能确定发生了什么，但您所做的可能并不理想。解释计划和每一步的执行时间会有所帮助。

我怀疑你正在阅读 97M 行表 200 次。这会减慢速度，但不应花费 40 分钟。所以我也怀疑record_item_name 不是每个record_id 值唯一的。这将导致行复制，并可能将数据集扩展许多倍。在 fact_patient_record 中，record_id 也是唯一的吗？如果不是，那么这将导致行复制。如果所有这些都大到足以导致严重的溢出和大量的网络广播，那么您的 40 分钟执行时间是非常合理的。

当所有数据都在表的单个副本中时，无需加入。@PhilCoulson 是正确的，可以应用某种条件聚合，如果您不喜欢大小写，则 decode() 语法可以为您节省空间。可能会影响您的联接的上述几个问题也会使此聚合变得复杂。如果每个 record_id 和 record_item_name 对的 record_item_value 有多个值，您在寻找什么？我希望你对你的数据在你的未来有一些发现。

推荐阅读