预聚合 UDTF 结果

CREATE PROJECTION 可以定义调用用户定义的转换函数 (UDTF) 的实时聚合投影。为了在查询这些投影时最大限度降低开销，Vertica 会在后台处理这些函数，并将其结果存储在磁盘上。

定义含 UDTF 的投影

投影定义通过两种方法之一来确定其 UDTF 特征：

  • 将 UDTF 标识为预传递 UDTF，此时 UDTF 会在新加载的数据存储在投影 ROS 容器之前对其进行转换。

  • 将 UDTF 标识为批量 UDTF，此时 UDTF 会聚合和存储投影数据。

投影定义在窗口分区子句中通过关键字 PREPASS 或 BATCH 将 UDTF 标识为预传递 UDTF 或批量 UDTF。投影可以指定一个预传递或批量 UDTF，或者包含两者（请参阅 UDTF 规范选项）。

在所有情况下，投影都在 PARTITION BY 列上隐式地分段和排序。

UDTF 规范选项

投影可以批量调用和预传递单个 UDTF 或 UDTF 组合。

单个预传递 UDTF：

将数据加载到投影的锚表时（例如通过 COPY 或 INSERT 语句），Vertica 会调用预传递 UDTF。预传递 UDTF 会转换新数据，然后将转换后的数据存储到投影的 ROS 容器中。

使用以下语法：


CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ] }[,...]
) ]

AS SELECT { table-column | expr-with-table-columns }[,...], prepass-udtf(prepass-args)
    OVER (PARTITION PREPASS BY partition-column-expr[,...])
    [ AS (prepass-output-columns) ] FROM table [[AS] alias]

单个批量 UDTF

单个调用时，批量 UDTF 会在执行合并、数据加载和查询操作时转换并聚合投影数据。UDTF 会将聚合结果存储到投影的 ROS 容器中。聚合会在合并和加载操作期间累计，并在查询执行时完成（如有需要）。

使用以下语法：


CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ]  }[,...]
) ]
AS SELECT { table-column | expr-with-table-columns }[,...], batch-udtf(batch-args)
   OVER (PARTITION BATCH BY partition-column-expr[,...])
   [ AS (batch-output-columns) FROM table [ [AS] alias ]

组合预传递和批量 UDTF

您可以定义一个含子查询的投影，并用它来调用预传递 UDTF。该预传递 UDTF 会将转换后的数据返回给外部批量查询。然后，批量 UDTF 会以迭代方式在多个合并操作期间聚合结果。它会在执行查询时完成聚合（如有需要）。

使用以下语法：


CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ] }[,...]
) ]
AS SELECT { table-column | expr-with-table-columns }[,...], batch-udtf(batch-args)
   OVER (PARTITION BATCH BY partition-column-expr[,...]) [ AS (batch-output-columns) ] FROM (
      SELECT { table-column | expr-with-table-columns }[,...], prepass-udtf (prepass-args)
      OVER (PARTITION PREPASS BY partition-column-expr[,...]) [ AS (prepass-output-columns) ] FROM table ) sq-ref

示例

单个预传递 UDTF：
以下示例说明了如何使用从出现多次的文本文档字符串中提取内容的 UDTFtext_index。

以下投影指定按预传递 UDTF 调用 text_index：


=> CREATE TABLE documents ( doc_id INT PRIMARY KEY, text VARCHAR(140));

=> CREATE PROJECTION index_proj (doc_id, text)
     AS SELECT doc_id, text_index(doc_id, text)
     OVER (PARTITION PREPASS BY doc_id) FROM documents;

将数据加载到锚表 documents 时即会调用 UDTF。 text_index 会转换新加载的数据，并且 Vertica 会将转换后的数据存储在实时聚合投影 ROS 容器中。

所以，如果您将以下数据加载到 documents：

=> INSERT INTO documents VALUES
(100, 'A SQL Query walks into a bar. In one corner of the bar are two tables.
 The Query walks up to the tables and asks - Mind if I join you?');
 OUTPUT
--------
      1
(1 row)

text_index 会转换新加载的数据，并且会将其存储在投影 ROS 容器中。当您查询投影时，它会返回以下结果：


doc_id | frequency |     term
-------+-----------+--------------
100    | 2         | bar
100    | 2         | Query
100    | 2         | tables
100    | 2         | the
100    | 2         | walks

组合预传递和批量 UDTF
以下投影分别指定了预传递和批量 UDTF stv_intersect 及 aggregate_classified_points：


CREATE TABLE points( point_id INTEGER, point_type VARCHAR(10), coordinates GEOMETRY(100));

CREATE PROJECTION aggregated_proj
   AS SELECT point_type, aggregate_classified_points( sq.point_id, sq.polygon_id)
   OVER (PARTITION BATCH BY point_type)
   FROM
      (SELECT point_type, stv_intersect(
         point_id, coordinates USING PARAMETERS index=‘polygons’ )
       OVER (PARTITION PREPASS BY point_type) AS (point_id, polygon_id) FROM points) sq;

预传递查询 UDTF stv_intersect 将其结果（点集和匹配的多边形 ID）返回给外部批量查询。然后，外部批量查询会调用 UDTF aggregate_classified_points。Vertica 会在合并操作整合投影数据时聚合 aggregate_classified_points 返回的结果集。查询投影时会发生最终聚合（如有需要）。

批量 UDTF 参数的名称和顺序必须与预传递 UDTF stv_intersect 返回的输出列完全匹配。在此示例中，预传递子查询显式命名了预传递 UDTF 输出列 point_id 和 polygon_id。因此，批量 UDTF 实参在名称和顺序方面与它们匹配： sq.point_id 和 sq.polygon_id。