下推优化和数据可见性

通过下推优化，Snowflake 通过筛选行来帮助更快、更高效地处理查询。但是，由于筛选器重新排序的方式，下推可能会公开您可能不希望公开的数据。

This topic describes pushdown and how it can expose sensitive data. To prevent sensitive data from becoming visible, you can make a UDF secure as described in Protecting Sensitive Information with Secure UDFs and Stored Procedures.

什么是下推？

下推通过在查询处理过程中尽早筛选出不需要的行来提高性能。下推还可以减少内存消耗。但是，下推可能会允许间接公开机密数据。

请考虑以下查询：

SELECT col1
  FROM tab1
  WHERE location = 'New York';

处理查询的一种方法是：

1. 将表中的所有行读入内存（即执行 FROM 子句）。
2. Scan the rows in memory, filtering out any rows that do not match New York (i.e. execute the WHERE clause).
3. Select col1 from the rows still remaining in memory (i.e. execute the SELECT list).

可以将其视为“先加载，后筛选”的策略，这种方法很简单，但效率低下。

尽早筛选通常更有效。早期筛选称为“将筛选器向下推入查询计划更深处”，或简称为“下推”。

在上面的查询示例中，告诉表扫描代码不要加载与 WHERE 子句不匹配的记录会更有效。这不会节省筛选时间（每行的位置仍必须读取一次），但可以节省大量内存并减少后续处理时间，因为要处理的行更少。

在某些情况下，可以更高效地处理数据。例如，假设数据按州分区（即纽约州的所有数据都在一个微分区中，佛罗里达州的所有数据都在另一个微分区中，依此类推）。在这种情况下：

• Snowflake 不需要将所有行都存储在内存中。
• Snowflake 不需要读取所有行。

我们粗略地将其定义为另一种形式的“下推”。

“下推筛选器”的原则适用于各种查询。通常，最具选择性的筛选器（筛选出最多的数据）被推送到最深处（最早执行），以减少剩余查询必须执行的工作。

下推可以与其他技术结合使用，例如群集（对数据进行排序），以减少需要读取、加载和处理的不相关数据量。

通过下推间接公开数据的示例

以下示例演示了下推可能间接导致公开有关查询的基础详细信息的一种方式。此示例重点介绍视图，但相同的原则也适用于 UDFs。

假设有一个存储患者信息的表：

Copy codeExpand code block

CREATE TABLE patients
  (patient_ID INTEGER,
   category VARCHAR,      -- 'PhysicalHealth' or 'MentalHealth'
   diagnosis VARCHAR
   );

INSERT INTO patients (patient_ID, category, diagnosis) VALUES
  (1, 'MentalHealth', 'paranoia'),
  (2, 'PhysicalHealth', 'lung cancer');

Show lessSee more

Scroll to top

有两种视图，一种显示心理健康信息，另一种显示身体健康信息：

Copy codeExpand code block

CREATE VIEW mental_health_view AS
  SELECT * FROM patients WHERE category = 'MentalHealth';

CREATE VIEW physical_health_view AS
  SELECT * FROM patients WHERE category = 'PhysicalHealth';

Show lessSee more

Scroll to top

大多数用户无法直接访问该表。相反，系统会为用户分配以下两个角色之一：

• MentalHealth, which has privileges to read from mental_health_view, or
• PhysicalHealth, which has privileges to read from physical_health_view.

现在假设一位仅对身体健康数据有权限的医生想知道表中当前是否有任何心理健康患者。医生可以构造类似于以下内容的查询：

Copy codeExpand code block

SELECT * FROM physical_health_view
  WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1;

Show lessSee more

Scroll to top

此查询等效于：

Copy codeExpand code block

SELECT * FROM patients
  WHERE
    category = 'PhysicalHealth' AND
    1/IFF(category = 'MentalHealth', 0, 1) = 1;

Show lessSee more

Scroll to top

Snowflake 可以使用（至少）两种方法来处理此查询。

• 方法 1：

  1. 读取患者表中的所有行。
  2. Apply the view’s security filter (i.e. filter out the rows for which the category is not PhysicalHealth).
  3. Apply the WHERE clause in the query (i.e. filter based on WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1).

• 方法 2 更改筛选器的顺序，以便查询按如下方式执行：

  1. 读取患者表中的所有行。
  2. Apply the WHERE clause in the query (i.e. filter based on WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1).
  3. Apply the view’s security filter (i.e. filter out the rows for which the category is not PhysicalHealth).

从逻辑上讲，这两个序列似乎是等效的；它们返回相同的行集。但是，根据这两个筛选器的选择性，一个处理顺序可能更快，并且 Snowflake 的查询计划器可能会选择执行速度更快的计划。

Suppose that the optimizer chooses the second plan, in which the clause WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1 is executed before the security filter. If the patients table has any rows in which category = 'MentalHealth', then the IFF function returns 0 for that row, and the clause effectively becomes WHERE 1/0 = 1, so the statement causes a divide-by-zero error. The user with physical_health_view privileges does not see any rows for people with mental health issues, but can deduce that at least one person in the mental health category exists.

请注意，这种技术并不总是会公开底层细节；它在很大程度上依赖于查询规划器所做的选择，以及视图（或 UDFs）的编写方式。但此示例显示，用户可以推断出有关用户无法直接查看的行的信息。