EXPORT_DIRECTED_QUERIES
Generates SQL for creating directed queries from a set of input queries, and writes the SQL to the specified file or to standard output.
Syntax
EXPORT_DIRECTED_QUERIES('input-file', '[output-file]')
Parameters
input‑file | A SQL file that contains one or more input queries. See Input Format below for details on format requirements. |
output‑file | Specifies where to write the generated SQL for creating directed queries. If the file name already exists, EXPORT_DIRECTED_QUERIES returns with an error. If you supply an empty string, Vertica writes the SQL to standard output. See Output Format below for details. |
Privileges
Input Format
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
Output Format
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
Error Handling
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.
For example:
--------------------------------------------------------------------------------------------------- WARNING: Name field not supplied. Using auto-generated name: 'Autoname:2016-04-25 15:03:32.115317.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
Examples
See Batch Query Plan Export in the Administrator's Guide.