时间戳

首页 > 解决方案 > 如何高效地按小时查询大型数据库？

python - 如何高效地按小时查询大型数据库？

问题描述

背景：

我有多个资产表存储在每个城市的红移数据库中，总共 8 个城市。这些资产表按小时显示状态更新。8 个 SQL 表，一年大约 5 亿行数据。 （我还可以访问每分钟更新此数据的服务器。）

示例：一个市场可以有 20k 资产，每天显示 480k（20k*24 小时）状态更新。

这些状态更新是原始格式，需要经过当前写入 SQL 视图的转换过程。最终状态将进入我们的 BI 工具 (Tableau) 以供外部利益相关者查看。

问题：

当前处理数据的方式既慢又低效，在 Tableau 中每小时运行此作业可能不现实。状态转换需要我回顾 30 天的数据，所以我确实需要回顾整个查询的历史。

可能的解决方案：

以下是一些我认为可能可行的解决方案，我想就在我的情况下最有意义的方法获得反馈。

请让我知道您将如何解决此问题。我的知识是 SQL、有限的数据工程经验、Tableau（Prep 和桌面）以及 Python 或 R 脚本。

标签： pythonramazon-redshiftdatabase-administration

解决方案

因此，首先要做的事情是-您说数据处理“缓慢且效率低下”，并询问如何有效地查询大型数据库。首先，我会看看如何改进这个过程。您表示该过程基于过去 30 天的数据 - 是否对大型表进行时间排序、清理和分析？在处理大型表时，最大限度地利用元数据非常重要。确保您的 where 子句在消除事实表块方面是有效的 - 不要依赖维度表 where 子句来选择日期范围。

接下来看看您的分布键以及它们如何影响您的关键查询在网络中移动大量数据的需求。节点间网络在 Redshift 集群中具有最低的带宽，并且不必要地在其中推送大量数据会使事情变得缓慢和低效。根据您的查询模式，使用 EVEN 分布可能会成为性能杀手。

现在让我回答你的问题，让我解释一下——“使用汇总表、物化视图或外部存储（tableau 数据源）存储每小时更新的汇总数据更好吗？” 所有 3 项工作，每个都有自己的优点和缺点。

  1. 汇总表很好，因为您可以选择数据存储的分布，如果此数据需要与其他数据库表组合，则可以最有效地完成。但是，需要执行更多的数据管理以使这些数据保持最新并保持同步。
  2. 物化视图很好，因为需要担心的管理操作要少得多 - 当数据更改时，只需刷新视图即可。数据仍在数据库中，因此很容易与其他数据表组合，但由于您无法控制数据的存储，因此这些操作可能不是最有效的。
  3. 外部存储很好，因为数据在您的 BI 工具中，因此如果您需要在一小时内重新获取结果，则数据是本地的。但是，它并没有被锁定在您的 BI 工具中，并且与其他数据库表组合的效率要低得多。

摘要数据通常不是那么大，所以它的存储方式不是一个大问题，而且我有点懒，所以我会使用物化视图。就像我在开始时所说的那样，我首先会查看我每小时运行的“缓慢且低效”的查询。

希望这可以帮助

推荐阅读