Directed queries functions

• 1: EXPORT_DIRECTED_QUERIES
• 2: IMPORT_DIRECTED_QUERIES

1 - EXPORT_DIRECTED_QUERIES

Generates SQL for creating directed queries from a set of input queries.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

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 output-file 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

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 */

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 Exporting directed queries.

See also

• Batch query plan export
• IMPORT_DIRECTED_QUERIES

2 - IMPORT_DIRECTED_QUERIES

Imports to the database catalog directed queries from a SQL file that was generated by EXPORT_DIRECTED_QUERIES. If no directed queries are specified, Vertica lists all directed queries in the SQL file.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

IMPORT_DIRECTED_QUERIES( 'export-file'[, 'directed-query-name'[,...] ] )

Parameters

export-file

A SQL file generated by EXPORT_DIRECTED_QUERIES. When you run this file, Vertica creates the specified directed queries in the current database catalog.

directed-query-name

The name of a directed query that is defined in export-file. You can specify multiple comma-delimited directed query names.

If you omit this parameter, Vertica lists the names of all directed queries in export-file.

Privileges

Examples

See Importing directed queries.

See also