为数据仓库编写SQL存储过程的技巧 - babbling

在数据仓库应用程序中，我们需要想办法有效地回填我们的数据并大规模快速运行我们的 SQL。回填是指我们想要在表中填充过去 X 天的数据。为此，我们的 SQL 必须是可重复和可水平扩展的。我们需要以不会泄漏数据或导致重复的方式一次填充数天的数据。以下是实现这一目标的一些技巧。

模板
python 中的模板库最初是为了 Web 开发而开发的，作为在服务器端生成静态 HTML 内容的一种方式。静态内容可以使用像 NGINX 这样的快速网络服务器廉价地托管在 CDN 上。模板对于生成 SQL 非常有用，因为它们允许参数化和在 SQL 脚本中嵌入 Python 逻辑。
数据仓库中的一个常见模式是参数化指定脚本的日期。我们通常为今天的报告处理昨天的数据。使用模板，我们可以根据我们想要报告的日期在 python 中动态生成昨天的日期。
当您需要使用命令式编程语言来处理棘手的逻辑时，模板化也很有用。例如，如果您需要进行复杂的时区转换，您可以在 python 中进行并将代码写入模板本身。在带有 SQL 扩展的模板中使用 python 允许您的 IDE 使用 SQL 语法突出显示，促进关注点分离，并使用文件搜索使数据库逻辑更易于发现。
将 SQL 嵌入到您的应用程序代码中很诱人。问题是工程师必须通读所有应用程序代码以了解它如何与数据库交互。
使用 SQL 的全部优势在于它是一种抽象，需要与可以独立推理的应用程序分离。
我最喜欢的一些 SQL 特性是它具有跨组织级别（包括分析师、产品团队、数据工程师、数据科学家）的互操作性，并且本质上是声明性的。而将它嵌入到应用程序中，则会失去一些最强大的功能。

不要做：

-- PostgreSQL
SELECT * 
   FROM transaction 
WHERE transaction_time 
BETWEEN DATE(NOW() AT TIMEZONE 'America/New_York')
          AND DATE(NOW() AT TIMEZONE 'America/New_York' - INTERVAL ‘1 DAY’);

使用现在存储的函数的问题是脚本只能用于处理与当前时刻相关的数据。回填是指我们想要重新处理过去的数据。使用存储函数编写的获取当前时间的代码不能用于在不手动更改脚本的情况下进行回填。
假设我们定义了一个 python 函数convert_to_eastern，它接受一个时间戳并将其转换为东部时区。我们将此函数传递给模板库的渲染函数，以便它在模板中可用。

/* PostgreSQL with Jinja2 templating from Apache Airflow */
SELECT * 
   FROM transaction 
 WHERE transaction_time 
BETWEEN DATE(NOW() AT TIMEZONE 'America/New_York')
         AND DATE(NOW() AT TIMEZONE 'America/New_York' - INTERVAL ‘1 DAY’);
 BETWEEN ‘{{ convert_to_eastern(execution_date.date().isoformat()) }}’                            
        AND  ‘{{ convert_to_eastern(execution_date - macros.timedelta(days=1)).isoformat() }}’

使用模板化 SQL，我们可以将 execution_date 定义为参数。然后，进行回填将包括通过任务调度程序（如 Apache Airflow）使用从过去某个时间点到今天的所有日期迭代运行相同的任务。

覆盖优先
SQL 数据库的主要目的是存储有关用户或业务的状态信息。很自然地认为更新语句是更改给定记录状态的好方法。更新的问题在于它们在专为高吞吐量读取和写入而设计，对数据仓库没有得到很好的优化。
出于性能原因，数据库在后台使用了不可变的数据结构。例如，数据库可以使用强类型列的压缩和编码来实现其某些性能提升。更新通常被实现为先删除再插入，过多的删除操作确实会损害性能。由于记录不是连续存储在面向列的数据库中，因此删除操作将触及多个不同的内存块以删除一行。
在为数据仓库编写 SQL 时，最好先覆盖分区，然后再进行更新插入（通常当然也有例外）。
这样做的好处是简化了一些操作，并且在许多情况下非常快。假设我们要回填前几天。我们已经编写了将行插入表中的代码，假设行不在表中。这种方法帮助我们避免将我们的插入重写为更新和插入，并避免编写额外的重复数据删除逻辑。如果我们简单地覆盖现有分区，我们可以重用我们的插入语句，因为知道已经存在的数据将被简单地删除。
假设我们在 Postgres 中有一个表分区在日期时间列上。如果我们编写代码来覆盖分区，那么无论我们是否回填，SQL 逻辑每次都是相同的。当我们再次运行代码时，它只会吹走已经存在的东西并用我们想要的东西替换它。
不要做：

-- hiveSQL
UPDATE transaction
SET
      amount = loading.amount,
      transaction_time = loading.transaction_time
FROM loading 
WHERE transaction.id = loading.id;
INSERT INTO transaction
SELECT * FROM loading
WHERE (transaction_id) NOT IN (SELECT DISTINCT transaction_id FROM transaction);

在上面的查询中，我们首先更新目标表中已经存在的记录，然后插入不存在的记录。我们可能还需要第三步来删除任何可能作为重复产生的记录。
应该这样做：

-- hiveSQL
INSERT OVERWRITE TABLE transaction
PARTITION (transaction_time=transaction_time) 
SELECT * FROM loading;

覆盖分区很快的另一个原因是旧分区会立即从内存中释放，而无需实际写入磁盘。这意味着只要将新数据写入磁盘，旧数据不会影响性能。

中间表
编写SQL时使用中间表通常很有用，尤其是在使用列式存储以实现快速写入速度的数据仓库中。一个好处是它允许您将查询分解成更小的更易于管理的部分，使 SQL 更易于阅读和推理。另一个好处是性能，当您在中间表中拥有要处理的数据时，您可以添加索引（或排序顺序和分区）以适应后续连接和查询以优化性能。
使用中间表时，您应该创建一个作用域为数据库会话的本地临时表。这将防止问题在具有不同参数的不同进程中并行运行相同的查询。避免使用同一个表的另一个原因是在执行可能降低性能甚至导致死锁的删除和更新时避免排他 (X) 锁。
不要做：

TRUNCATE intermediate_table;
INSERT INTO intermediate_table SELECT * FROM table

使用永久表作为中间体，然后截断并插入其中是很诱人的。问题是这种方法不能扩展到分布式系统，如果两个进程写入同一个表，可能会导致奇怪的行为。一个进程可以在另一个进程插入同一个表后立即截断该表。在分布式系统中，您希望进程彼此隔离，以便它们可以安全地并行运行。
应该：

-- postgreSQL
CREATE TEMP TABLE intermediate_table AS SELECT * FROM table;

结论
本文介绍了为数据仓库开发 SQL 的一些技巧，这些 SQL 由一组工作器中的任务运行器执行。还有许多其他技术取决于特定的数据库供应商。开发 SQL 的最佳方式取决于您使用的数据库供应商。