导出定向查询

可以将任意数量的查询计划作为定向查询批量导出到外部 SQL 文件中，如下所示：

  1. 创建一个 SQL 文件，其中包含要保存其查询计划的输入查询。请参阅下文的输入格式。

  2. 对该 SQL 文件调用元函数 EXPORT_DIRECTED_QUERIES。此元函数具有两个实参：

    • 输入查询文件。

    • （可选）输出文件的名称。EXPORT_DIRECTED_QUERIES 将用于创建定向查询的 SQL 写入此文件。如果您提供的是空字符串，则 Vertica 会将 SQL 写入标准输出。

例如，以下 EXPORT_DIRECTED_QUERIES 语句指定了输入文件 inputQueries 和输出文件 outputQueries：

=> SELECT EXPORT_DIRECTED_QUERIES('/home/dbadmin/inputQueries','/home/dbadmin/outputQueries');
                               EXPORT_DIRECTED_QUERIES
---------------------------------------------------------------------------------------------
 1 queries successfully exported.
Queries exported to /home/dbadmin/outputQueries.

(1 row)

输入文件

您提供给 EXPORT_DIRECTED_QUERIES 的输入文件包含一个或多个输入查询。对于每个输入查询，您可以选择指定两个字段，这两个字段将用于生成的定向查询中：

  • DirQueryName 提供了定向查询的唯一标识符，即一个符合标识符中所述惯例的字符串。

  • DirQueryComment 指定了一个最多 128 个字符并以引号分隔的字符串。

您可以按如下方式设置每个输入查询的格式：

--DirQueryName=query-name
--DirQueryComment='comment'
input-query

例如，一个文件可以指定一个输入查询，如下所示：

/* Query: findEmployeesCityJobTitle_OPT */
/* Comment: This query finds all employees of a given city and job title, ordered by employee name */
SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name, employee_dimension.job_title FROM public.employee_dimension WHERE (employee_dimension.employee_city = 'Boston'::varchar(6)) ORDER BY employee_dimension.job_title;

输出文件

EXPORT_DIRECTED_QUERIES 会将生成用于创建定向查询的 SQL，并将此 SQL 写入到指定文件或标准输出中。对于这两种情况，输出都遵循以下格式：

/* Query: directed-query-name */
/* Comment: directed-query-comment */
SAVE QUERY input-query;
CREATE DIRECTED QUERY CUSTOM 'directed-query-name'
COMMENT 'directed-query-comment'
OPTVER 'vertica-release-num'
PSDATE 'timestamp'
annotated-query

例如，给定前面的输入时，Vertica 会将以下输出写入 /home/dbadmin/outputQueries：

/* Query: findEmployeesCityJobTitle_OPT */
/* Comment: This query finds all employees of a given city and job title, ordered by employee name */
SAVE QUERY SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name FROM public.employee_dimension WHERE ((employee_dimension.employee_city = 'Boston'::varchar(6) /*+:v(1)*/) AND (employee_dimension.job_title = 'Cashier'::varchar(7) /*+:v(2)*/)) ORDER BY employee_dimension.employee_last_name, employee_dimension.employee_first_name;
CREATE DIRECTED QUERY CUSTOM 'findEmployeesCityJobTitle_OPT'
COMMENT 'This query finds all employees of a given city and job title, ordered by employee name'
OPTVER 'Vertica Analytic Database v11.1.0-20220102'
PSDATE '2022-01-06 13:45:17.430254'
SELECT /*+verbatim*/ employee_dimension.employee_first_name AS employee_first_name, employee_dimension.employee_last_name AS employee_last_name
FROM public.employee_dimension AS employee_dimension/*+projs('public.employee_dimension')*/
WHERE (employee_dimension.employee_city = 'Boston'::varchar(6) /*+:v(1)*/) AND (employee_dimension.job_title = 'Cashier'::varchar(7) /*+:v(2)*/)
ORDER BY 2 ASC, 1 ASC;

如果给定的输入查询省略了 DirQueryName 和 DirQueryComment 字段，则 EXPORT_DIRECTED_QUERIES 会自动生成以下输出：

  • /* Query: Autoname:时间戳 .n */，其中 n 为基于零的整数索引，用于确保具有相同时间戳的自动生成名称具有唯一性。

  • /* Comment: Optimizer-generated directed query */

例如，以下输入文件包含一个 SELECT 语句，并且省略 DirQueryName 和 DirQueryComment 字段：


SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name
FROM public.employee_dimension WHERE (employee_dimension.employee_city = 'Boston'::varchar(6))
ORDER BY employee_dimension.job_title;

给定这个文件，EXPORT_DIRECTED_QUERIES 会返回一个关于缺少输入字段的警告，它还会将该警告写入错误文件：

> SELECT EXPORT_DIRECTED_QUERIES('/home/dbadmin/inputQueries2','/home/dbadmin/outputQueries3');
                                              EXPORT_DIRECTED_QUERIES
--------------------------------------------------------------------------------------------------------------
 1 queries successfully exported.
1 warning message was generated.
Queries exported to /home/dbadmin/outputQueries3.
See error report, /home/dbadmin/outputQueries3.err for details.
(1 row)

输出文件包含以下内容：

/* Query: Autoname:2022-01-06 14:11:23.071559.0 */
/* Comment: Optimizer-generated directed query */
SAVE QUERY SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name FROM public.employee_dimension WHERE (employee_dimension.employee_city = 'Boston'::varchar(6) /*+:v(1)*/) ORDER BY employee_dimension.job_title;
CREATE DIRECTED QUERY CUSTOM 'Autoname:2022-01-06 14:11:23.071559.0'
COMMENT 'Optimizer-generated directed query'
OPTVER 'Vertica Analytic Database v11.1.0-20220102'
PSDATE '2022-01-06 14:11:23.071559'
SELECT /*+verbatim*/ employee_dimension.employee_first_name AS employee_first_name, employee_dimension.employee_last_name AS employee_last_name
FROM public.employee_dimension AS employee_dimension/*+projs('public.employee_dimension')*/
WHERE (employee_dimension.employee_city = 'Boston'::varchar(6) /*+:v(1)*/)
ORDER BY employee_dimension.job_title ASC;

错误文件

如果在执行 EXPORT_DIRECTED_QUERIES 期间发生任何错误或警告，它会返回一条类似于以下内容的消息：

1 queries successfully exported.
1 warning message was generated.
Queries exported to /home/dbadmin/outputQueries.
See error report, /home/dbadmin/outputQueries.err for details.

EXPORT_DIRECTED_QUERIES 会将所有错误或警告都写入到一个在与输出文件相同的路径下所创建的文件中，并使用输出文件的基名。

在上一示例中，输出文件名为 /home/dbadmin/outputQueries，因此 EXPORT_DIRECTED_QUERIES 会将错误写入 /home/dbadmin/outputQueries.err。

错误文件可以捕获许多错误和警告，例如，可以捕获 EXPORT_DIRECTED_QUERIES 无法创建定向查询的所有实例。在以下示例中，错误文件包含一个警告，指示没有为指定的输入查询提供名称字段，并且还记录了自动为该查询生成的名称：


----------------------------------------------------------------------------------------------------
WARNING: Name field not supplied. Using auto-generated name: 'Autoname:2016-10-13 09:44:33.527548.0'
Input Query: SELECT employee_dimension.employee_first_name, employee_dimension.employee_last_name, employee_dimension.job_title FROM public.employee_dimension WHERE (employee_dimension.employee_city = 'Boston'::varchar(6)) ORDER BY employee_dimension.job_title;
END WARNING