You can also use meta-function DESIGNER_SINGLE_RUN, which encapsulates all of these steps with a single call. The meta-function iterates over all queries within a specified timespan, and returns with a design ready for deployment.
The following functions populate the Database Designer workspace and create design and deployment scripts. You can also analyze statistics, deploy the design automatically, and drop the workspace after the deployment:
DESIGNER_RESET_DESIGN discards all the run-specific information of the previous Database Designer build or deployment of the specified design but retains its configuration.
Get design data
The following functions display information about projections and scripts that the Database Designer created:
The following functions cancel any running Database Designer operation or drop a Database Designer design and all its contents:
DESIGNER_CANCEL_POPULATE_DESIGN: Cancels population or deployment operation for the specified design if it is currently running.
DESIGNER_DROP_DESIGN: Removes the schema associated with the specified design and all its contents.
DESIGNER_DROP_ALL_DESIGNS: Removes all Database Designer-related schemas associated with the current user.
1 - DESIGNER_ADD_DESIGN_QUERIES
Reads and evaluates queries from an input file, and adds the queries that it accepts to the specified design.
Reads and evaluates queries from an input file, and adds the queries that it accepts to the specified design. All accepted queries are assigned a weight of 1.
If the design type is incremental, the Database Designer reads only the first 100 queries in the input file, and ignores all queries beyond that number.
All accepted queries are added to the system table DESIGN_QUERIES.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Absolute path and name of the file that contains the queries to evaluate, on the local file system of the node where the session is connected, or another file system or object store that Vertica supports.
return-results
Boolean, optionally specifies whether to return results of the add operation to standard output. If set to true, Database Designer returns the following results:
Number of accepted queries
Number of queries referencing non-design tables
Number of unsupported queries
Number of illegal queries
Privileges
Non-superuser: design creator with all privileges required to execute the queries in input-file.
Errors
Database Designer returns an error in the following cases:
The query contains illegal syntax.
The query references:
External or system tables only
Local temporary or other non-design tables
DELETE or UPDATE query has one or more subqueries.
INSERT query does not include a SELECT clause.
Database Designer cannot optimize the query.
Examples
The following example adds queries from vmart_queries.sql to the VMART_DESIGN design. This file contains nine queries. The statement includes a third argument of true, so Database Designer returns results of the add operation:
=> SELECT DESIGNER_ADD_DESIGN_QUERIES ('VMART_DESIGN', '/tmp/examples/vmart_queries.sql', 'true');
...
DESIGNER_ADD_DESIGN_QUERIES
----------------------------------------------------
Number of accepted queries =9
Number of queries referencing non-design tables =0
Number of unsupported queries =0
Number of illegal queries =0
(1 row)
Executes the specified query and evaluates results in the following columns:.
Executes the specified query and evaluates results in the following columns:
QUERY_TEXT (required): Text of potential design queries.
QUERY_WEIGHT (optional): The weight assigned to each query that indicates its importance relative to other queries, a real number >0 and ≤ 1. Database Designer uses this setting when creating the design to prioritize the query. If DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS returns any results that omit this value, Database Designer sets their weight to 1.
After evaluating the queries in QUERY_TEXT, DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS adds all accepted queries to the design. An unlimited number of queries can be added to the design.
A valid SQL query whose results contain columns named QUERY_TEXT and, optionally, QUERY_WEIGHT.
Privileges
Non-superuser: design creator with all privileges required to execute the specified query, and all queries returned by this function
Errors
Database Designer returns an error in the following cases:
The query contains illegal syntax.
The query references:
External or system tables only
Local temporary or other non-design tables
DELETE or UPDATE query has one or more subqueries.
INSERT query does not include a SELECT clause.
Database Designer cannot optimize the query.
Examples
The following example queries the system table
QUERY_REQUESTS for all long-running queries (> 1 million microseconds) and adds them to the VMART_DESIGN design. The query returns no information on query weights, so all queries are assigned a weight of 1:
=> SELECT DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS ('VMART_DESIGN',
'SELECT request as query_text FROM query_requests where request_duration_ms > 1000000 AND request_type =
''QUERY'';');
Reads and parses the specified query, and if accepted, adds it to the design.
Reads and parses the specified query, and if accepted, adds it to the design. Before you add queries to a design, you must add the queried tables with
DESIGNER_ADD_DESIGN_TABLES.
All accepted queries are added to the system table
DESIGN_QUERIES.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Optionally assigns a weight to each query that indicates its importance relative to other queries, a real number >0 and ≤ 1. Database Designer uses this setting to prioritize queries in the design .
If you omit this parameter, Database Designer assigns a weight of 1.
Privileges
Non-superuser: design creator with all privileges required to execute the specified query
Errors
Database Designer returns an error in the following cases:
The query contains illegal syntax.
The query references:
External or system tables only
Local temporary or other non-design tables
DELETE or UPDATE query has one or more subqueries.
INSERT query does not include a SELECT clause.
Database Designer cannot optimize the query.
Examples
The following example adds the specified query to the VMART_DESIGN design and assigns that query a weight of 0.5:
=> SELECT DESIGNER_ADD_DESIGN_QUERY (
'VMART_DESIGN',
'SELECT customer_name, customer_type FROM customer_dimension ORDER BY customer_name ASC;', 0.5
);
Adds the specified tables to a design. You must run DESIGNER_ADD_DESIGN_TABLES before adding design queries to the design. If no tables are added to the design, Vertica does not accept design queries.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Cancels population or deployment operation for the specified design if it is currently running.
Cancels population or deployment operation for the specified design if it is currently running. When you cancel a deployment, the Database Designer cancels the projection refresh operation. It does not roll back projections that it already deployed and refreshed.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Specifies where to send output, one of the following:
Empty string ('') writes the script to standard output.
Pathname of a SQL output file. If you specify a file that does not exist, the function creates one. If you specify only a file name, Vertica creates it in the catalog directory. If the file already exists, the function silently overwrites its contents.
deploy
Boolean that specifies whether to deploy encoding changes.
Default: false
reanalyze-encodings
Boolean that specifies whether DESIGNER_DESIGN_PROJECTION_ENCODINGS analyzes encodings in a projection where all columns are already encoded:
false: Analyzes no columns and generates no recommendations if all columns are encoded.
true: Ignores existing encodings and generates recommendations.
Default: false
Privileges
Superuser, or DBDUSER with the following privileges:
OWNER of all projections to analyze
USAGE privilege on the schema for the specified projections
Examples
The following example requests that Database Designer analyze encodings of the table online_sales.call_center_dimension:
The second parameter destination is set to an empty string, so the script is sent to standard output (shown truncated below).
The last two parameters deploy and reanalyze-encodings are omitted, so Database Designer does not execute the script or reanalyze existing encodings:
=> SELECT DESIGNER_DESIGN_PROJECTION_ENCODINGS ('online_sales.call_center_dimension','');
DESIGNER_DESIGN_PROJECTION_ENCODINGS
----------------------------------------------------------------
CREATE PROJECTION call_center_dimension_DBD_1_seg_EncodingDesign /*+createtype(D)*/
(
call_center_key ENCODING COMMONDELTA_COMP,
cc_closed_date,
cc_open_date,
cc_name ENCODING ZSTD_HIGH_COMP,
cc_class ENCODING ZSTD_HIGH_COMP,
cc_employees,
cc_hours ENCODING ZSTD_HIGH_COMP,
cc_manager ENCODING ZSTD_HIGH_COMP,
cc_address ENCODING ZSTD_HIGH_COMP,
cc_city ENCODING ZSTD_COMP,
cc_state ENCODING ZSTD_FAST_COMP,
cc_region ENCODING ZSTD_HIGH_COMP
)
AS
SELECT call_center_dimension.call_center_key,
call_center_dimension.cc_closed_date,
call_center_dimension.cc_open_date,
call_center_dimension.cc_name,
call_center_dimension.cc_class,
call_center_dimension.cc_employees,
call_center_dimension.cc_hours,
call_center_dimension.cc_manager,
call_center_dimension.cc_address,
call_center_dimension.cc_city,
call_center_dimension.cc_state,
call_center_dimension.cc_region
FROM online_sales.call_center_dimension
ORDER BY call_center_dimension.call_center_key
SEGMENTED BY hash(call_center_dimension.call_center_key) ALL NODES KSAFE 1;
select refresh('online_sales.call_center_dimension');
select make_ahm_now();
DROP PROJECTION online_sales.call_center_dimension CASCADE;
ALTER PROJECTION online_sales.call_center_dimension_DBD_1_seg_EncodingDesign RENAME TO call_center_dimension;
(1 row)
Removes all Database Designer-related schemas associated with the current user.
Removes all Database Designer-related schemas associated with the current user. Use this function to remove database objects after one or more Database Designer sessions complete execution.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
The following example removes all schema and their contents associated with the current user. DESIGNER_DROP_ALL_DESIGNS returns the number of designs dropped:
Removes the schema associated with the specified design and all its contents.
Removes the schema associated with the specified design and all its contents. Use DESIGNER_DROP_DESIGN after a Database Designer design or deployment completes successfully. You must also use it to drop a design before creating another one under the same name.
Boolean that overrides any dependencies that otherwise prevent Vertica from executing this function—for example, the design is in use or is currently being deployed. If you omit this parameter, Vertica sets it to false.
Privileges
Non-superuser: design creator
Examples
The following example deletes the Database Designer design VMART_DESIGN and all its contents:
Discards all run-specific information of the previous Database Designer build or deployment of the specified design but keeps its configuration.
Discards all run-specific information of the previous Database Designer build or deployment of the specified design but keeps its configuration. You can make changes to the design as needed, for example, by changing parameters or adding additional tables and/or queries, before running the design again.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
The following example resets the Database Designer design VMART_DESIGN:
=> SELECT DESIGNER_RESET_DESIGN ('VMART_DESIGN');
13 - DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY
Populates the design and creates the design and deployment scripts.
Populates the design and creates the design and deployment scripts. DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY can also analyze statistics, deploy the design, and drop the workspace after the deployment.
The files output by this function have the permissions 666 or rw-rw-rw-, which allows any Linux user on the node to read or write to them. It is highly recommended that you keep the files in a secure directory.
Caution
DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY does not create a backup copy of the current design before deploying the new design. Before running this function, back up the existing schema design with EXPORT_CATALOG.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Absolute path and name of the file to contain DDL statements that create design projections, on the local file system of the node where the session is connected, or another file system or object store that Vertica supports.
output-deployment-filename
Absolute path and name of the file to contain the deployment script, on the local file system of the node where the session is connected, or another file system or object store that Vertica supports.
analyze-statistics
Specifies whether to collect or refresh statistics for the tables before populating the design. If set to true, Vertica Invokes ANALYZE_STATISTICS. Accurate statistics help Database Designer optimize compression and query performance. However, updating statistics requires time and resources.
Default: false
deploy
Specifies whether to deploy the Database Designer design using the deployment script created by this function.
Default: true
drop-design-workspace
Specifies whether to drop the design workspace after the design is deployed.
Default: true
continue-after-error
Specifies whether DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY continues to run after an error occurs. By default, an error causes this function to terminate.
Default: false
Privileges
Non-superuser: design creator with WRITE privileges on storage locations of design and deployment scripts
Requirements
Before calling this function, you must:
Create a design, a logical schema with tables.
Associate tables with the design.
Load queries to the design.
Set design properties (K-safety level, mode, and policy).
Examples
The following example creates projections for and deploys the VMART_DESIGN design, and analyzes statistics about the design tables.
Sets K-safety for a comprehensive design and stores the K-safety value in the DESIGNS table.
Sets K-safety for a comprehensive design and stores the K-safety value in the
DESIGNS table. Database Designer ignores this function for incremental designs.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Name of the design for which you want to set the K-safety value, type VARCHAR.
k-level
An integer between 0 and 2 that specifies the level of K-safety for the target design. This value must be compatible with the number of nodes in the database cluster:
k-level = 0: ≥ 1 nodes
k-level = 1: ≥ 3 nodes
k-level = 2: ≥ 5 nodes
If you omit this parameter, Vertica sets K-safety for this design to 0 or 1, according to the number of nodes: 1 if the cluster contains ≥ 3 nodes, otherwise 0.
If you are a DBADMIN user and k-level differs from system K-safety, Vertica changes system K-safety as follows:
If k-level is less than system K-safety, Vertica changes system K-safety to the lower level after the design is deployed.
If k-level is greater than system K-safety and is valid for the database cluster, Vertica creates the required number of buddy projections for the tables in this design. If the design applies to all database tables, or all tables in the database have the required number of buddy projections, Database Designer changes system K-safety to k-level.
If the design excludes some database tables and the number of their buddy projections is less than k-level, Database Designer leaves system K-safety unchanged. Instead, it returns a warning and indicates which tables need new buddy projections in order to adjust system K-safety.
If you are a DBDUSER, Vertica ignores this parameter.
Privileges
Non-superuser: design creator
Examples
The following example set K-safety for the VMART_DESIGN design to 1:
Specifies whether Database Designer creates a comprehensive or incremental design.
Specifies whether Database Designer creates a comprehensive or incremental design. DESIGNER_SET_DESIGN_TYPE stores the design mode in the
DESIGNS table.
Important
If you do not explicitly set a design mode with this function, Database Designer creates a comprehensive design.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Name of the mode that Database Designer should use when designing the database, one of the following:
COMPREHENSIVE: Creates an initial or replacement design for all tables in the specified schemas. You typically create a comprehensive design for a new database.
INCREMENTAL: Modifies an existing design with additional projection that are optimized for new or modified queries.
Note
Incremental designs always inherit the K-safety value of the database.
Valid only for comprehensive database designs, specifies the optimization objective Database Designer uses.
Valid only for comprehensive database designs, specifies the optimization objective Database Designer uses. Database Designer ignores this function for incremental designs.
DESIGNER_SET_OPTIMIZATION_OBJECTIVE stores the optimization objective in the
DESIGNS table.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Specifies whether a design can include unsegmented projections.
Specifies whether a design can include unsegmented projections. Vertica ignores this function on a one-node cluster, where all projections must be unsegmented.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Boolean that specifies whether Database Designer can propose unsegmented projections for tables in this design. When you create a design, the propose_unsegmented_projections value in system table
DESIGNS for this design is set to true. If DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS sets this value to false, Database Designer proposes only segmented projections.
Privileges
Non-superuser: design creator
Examples
The following example specifies that Database Designer can propose only segmented projections for tables in the design VMART_DESIGN:
Evaluates all queries that completed execution within the specified timespan, and returns with a design that is ready for deployment.
Evaluates all queries that completed execution within the specified timespan, and returns with a design that is ready for deployment. This design includes projections that are recommended for optimizing the evaluated queries. Unless you redirect output, DESIGNER_SINGLE_RUN returns the design to stdout.
Specifies an interval of time that precedes the meta-function call. Database Designer evaluates all queries that ran to completion over the specified interval.