Exporting Directed Queries
You can batch export any number of query plans as directed queries to an external SQL file, as follows:
- Create a SQL file that contains the input queries whose query plans you wish to save. See Output File below.
- Call the meta-function
EXPORT_DIRECTED_QUERIES
on that SQL file. The meta-function takes two arguments: - The input queries file.
- The name of an external file.
EXPORT_DIRECTED_QUERIES
writes SQL for creating directed queries to this file. If you supply an empty string, Vertica writes the SQL to standard output. For details, see Output File below.
For example, the following EXPORT_DIRECTED_QUERIES
statement specifies input file inputQueries
and output file 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)
Input File
The input file that you supply to EXPORT_DIRECTED_QUERIES
contains one or more input queries. For each input query, you can optionally specify two fields that are used in the generated directed query:
DirQueryName
provides the directed query's unique identifier, a string that conforms to conventions described in Identifiers.DirQueryComment
specifies a quote-delimited string, up to 128 characters.
You format each input query as follows:
--DirQueryName=query-name --DirQueryComment='comment' input-query
For example, a file can specify one input query as follows:
--DirQueryName=FindEmployeesBoston --DirQueryComment='This query finds all Boston employees, ordered by position' 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;
Output File
EXPORT_DIRECTED_QUERIES
generates SQL for creating directed queries, and writes the SQL to the specified file or to standard output. In both cases, output conforms to the following format:
/* 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
For example, given the previous input, Vertica writes the following output to /home/dbadmin/outputQueries
:
/* Query: FindEmployeesBoston */ /* Comment: This query finds all Boston employees, ordered by position */ SAVE 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; CREATE DIRECTED QUERY CUSTOM 'FindEmployeesBoston' COMMENT 'This query finds all Boston employees, ordered by position' OPTVER 'Vertica Analytic Database v8.0.1-20161013' PSDATE '2016-10-13 08:59:58.054505' SELECT /*+verbatim*/employee_dimension.employee_first_name AS employee_first_name, employee_dimension.employee_last_name AS employee_last_name, employee_dimension.job_title AS job_title FROM public.employee_dimension AS employee_dimension/*+projs('public.employee_dimension')*/ WHERE (employee_dimension.employee_city = 'Boston'::varchar(6)) ORDER BY 3 ASC;
If a given input query omits DirQueryName
and DirQueryComment
fields, EXPORT_DIRECTED_QUERIES
automatically generates the following output:
/* Query: Autoname:
timestamp.n */
, where n is a zero-based integer index that ensures uniqueness among auto-generated names with the same timestamp./* Comment: Optimizer-generated directed query */
For example, the following input file contains one SELECT
statement, and omits the DirQueryName
and DirQueryComment
fields:
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
Given this file, EXPORT_DIRECTED_QUERIES
generates the following output :
/* Query: Autoname:2016-10-13 09:44:33.527548.0 */ /* Comment: Optimizer-generated directed query */ SAVE 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; CREATE DIRECTED QUERY CUSTOM 'Autoname:2016-10-13 09:44:33.527548.0' COMMENT 'Optimizer-generated directed query' ...
Error File
If any errors or warnings occur during EXPORT_DIRECTED_QUERIES
execution, it returns with a message like this one:
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
writes all errors and warnings to a file that it creates on the same path as the output file, and uses the output file's base name.
In the previous example, the output filename is /home/dbadmin/outputQueries
, so EXPORT_DIRECTED_QUERIES
writes errors to /home/dbadmin/outputQueries.err
.
The error file can capture a number of errors, such as all instances where EXPORT_DIRECTED_QUERIES
was unable to create a directed query. In the following example, the error file contains a warning that no name field was supplied for the specified input query, and records the name that was auto-generated for it:
---------------------------------------------------------------------------------------------------- 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