Vertica Analytics Platform Version 10.1.x Documentation

Database Designer Functions

Database Designer functions perform the following operations, generally performed in the following order:

  1. Create a design.
  2. Set design properties.
  3. Populate a design.
  4. Create design and deployment scripts.
  5. Get design data.
  6. Clean up.

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.

For detailed information, see Workflow for Running Database Designer Programmatically. For information on required privileges, see Privileges for Running Database Designer Functions

Before running Database Designer functions on an existing schema, back up the current design by calling EXPORT_CATALOG.

Create a design

DESIGNER_CREATE_DESIGN directs Database Designer to create a design.

Set design properties

The following functions let you specify design properties:

DESIGNER_SET_DESIGN_TYPE Specifies whether the design is comprehensive or incremental.
DESIGNER_DESIGN_PROJECTION_ENCODINGS Analyzes encoding in the specified projections and creates a script that implements encoding recommendations.
DESIGNER_SET_DESIGN_KSAFETY Sets the K-safety value for a comprehensive design.
DESIGNER_SET_OPTIMIZATION_OBJECTIVE Specifies whether the design optimizes for query or load performance.
DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS Enables inclusion of unsegmented projections in the design.

Populate a design

The following functions let you add tables and queries to your Database Designer design:

DESIGNER_ADD_DESIGN_TABLES Adds the specified tables to a design.
DESIGNER_ADD_DESIGN_QUERY Adds queries to the design and weights them.
DESIGNER_ADD_DESIGN_QUERIES
DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS

Create design and deployment scripts

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_RUN_POPULATE_DESIGN_AND_DEPLOY Populates the design and creates design and deployment scripts.
DESIGNER_WAIT_FOR_DESIGN Waits for a currently running design to complete.

Reset a design

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:

DESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS Sends to standard output DDL statements that define design projections.
DESIGNER_OUTPUT_DEPLOYMENT_SCRIPT Sends to standard output a design's deployment script.

Clean up

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.