Batch Exporting Directed Queries

Posted December 16, 2016 by Vertica Technical Team

 An earlier blog covered the first edition of directed queries, which appeared with the first release of Vertica 7.2. With each release since then, Vertica has offered various enhancements to directed queries functionality.

Notably, these enhancements include bypassing the database catalog, and batch-exporting query plans as directed queries to an external SQL file. By offloading query plan storage, you can save any number of query plans from the current database without impacting catalog size and performance. After the upgrade, you can decide which query plans you want to retain in the new database, and selectively import the corresponding directed queries.

Vertica provides two meta-functions that support this approach:

  • EXPORT_DIRECTED_QUERIES generates query plans from a set of input queries, and writes SQL commands for creating directed queries that encapsulate those plans.
  • IMPORT_DIRECTED_QUERIES imports to the database catalog directed queries from an SQL file that was generated by EXPORT_DIRECTED_QUERIES.

Exporting Directed Queries to an External File

You can export directed queries to an external SQL file with the Vertica meta-function EXPORT_DIRECTED_QUERIES. You typically do this before you upgrade your Vertica database.

EXPORT_DIRECTED_QUERIES takes two arguments:

  • An input queries file, which contains one or more input queries. Each input query conforms to this format:
  • --DirQueryName=query-name --DirQueryComment='comment' input-query
  • The name of an external file.

For each query in the input file, EXPORT_DIRECTED_QUERIES creates a directed query and writes it to the external file.

For example, given this input query:

--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;

EXPORT_DIRECTED_QUERIES generates the following output:

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

Importing Directed Queries

After you finish exporting directed queries, edit the output file to include only those directed queries that you want to import into the upgraded database. After the upgrade completes, pass the output file as an argument to the Vertica function IMPORT_DIRECTED_QUERIES, which imports all the directed queries. Alternatively, specify which directed queries to import in a comma-delimited argument list.

For example, the following statement imports a single directed query:

=> SELECT IMPORT_DIRECTED_QUERIES('/home/dbadmin/outputQueries',      
'FindEmployeesBoston');                                
IMPORT_DIRECTED_QUERIES                               
--------------------------------------------------------------------------------------  
1 directed queries successfully imported. To activate a query named 'my_query1': 
=>ACTIVATE DIRECTED QUERY 'my_query1';   (1 row)

After importing the desired directed queries, you must activate them with ACTIVATE DIRECTED QUERY before you can use them to create query plans.

For More Information

See Batch Query Plan Export in the Vertica Administrator’s Guide.