Using Database Designer to create a comprehensive design

Vertica Database Designer:.

1: Running Database Designer with Management Console
2: Running Database Designer with administration tools

Vertica Database Designer:

Analyzes your logical schema, sample data, and, optionally, your sample queries.
Creates a physical schema design (a set of projections) that can be deployed automatically or manually.
Does not require specialized database knowledge.
Can be run and rerun any time for additional optimization without stopping the database.
Uses strategies to provide optimal query performance and data compression.

Use Database Designer to create a comprehensive design, which allows you to create projections for all tables in your database.

You can also use Database Designer to create an incremental design, which creates projections for all tables referenced in the queries you supply.

You can create a comprehensive design with Database Designer using Management Console or through Administration Tools. You can also choose to run Database Designer programmatically.

1 - Running Database Designer with Management Console

In this tutorial, you'll create a comprehensive design with Database Designer through the Management Console interface.

In this tutorial, you'll create a comprehensive design with Database Designer through the Management Console interface. If, in the future, you have a query that you want to optimize, you can create an enhanced (incremental) design with additional projections. You can tune these projections specifically for the query you provide.

Note

To run Database Designer outside Administration Tools, you must be a dbadmin user. If you are not a dbadmin user, you must have the DBDUSER role assigned to you and own the tables for which you are designing projections. For details, see Database Designer access requirements.

You can choose to create the design manually or use the Management Console wizard, as described below.

Important

Set your browser so it does not cache pages. If a browser caches pages, you might be unable to see the new design added.

Follow these steps to to create a comprehensive design with the Management Console wizard:

Log in to Management Console.
Verify that your database is up and running.
Choose the database for which you want to create the design. You can find the database under the Recent Databases section or by clicking Existing Infrastructure to reach the Databases and Clusters page.

The database overview page opens.
At the bottom of the screen, click the Design button.
In the New Design dialog box, enter the design name.
Click Wizard to continue.
Create an initial design. For Design Type, select Comprehensive and click Next.
In the Optimization Objective window, select Balance Load and Performance to create a design that is balanced between database size and query performance. Click Next.
Select the schemas. Because the VMart design is a multi-schema database, select all three schemas (public, store, and online_sales) for your design in the Select Sample Data window. Click Next.

If you include a schema that contains tables without data, the design could be suboptimal. You can choose to continue, but Vertica recommends that you deselect the schemas that contain empty tables before you proceed.
Choose the K-safety value for your design. The K-Safety value determines the number of buddy projections you want Database Designer to create.
Choose Analyze Correlations Mode. Analyze Correlations Mode determines if Database Designer analyzes and considers column correlations when creating the design.
- Ignore: When creating a design, ignore any column correlations in the specified tables.
- Consider existing: Consider the existing correlations in the tables when creating the design. If you set the mode to 1, and there are no existing correlations, Database Designer does not consider correlations.
- Analyze missing: Analyze column correlations on tables where the correlation analysis was not previously performed. When creating the design, consider all column correlations (new and existing).
- Analyze all: Analyze all column correlations in the tables and consider them when creating the design. Even if correlations exist for a table, reanalyze the table for correlations.
Click Next.
Submit query files to Database Designer in one of two ways:
- Supply your own query files by selecting the Browse button.
- Click Use Query Repository, which submits recently executed queries from the QUERY_REQUESTS system table.
Click Next.
In the Execution Options window, select any of the following options:
- Analyze statistics: Select this option to run statistics automatically after design deployment, so Database Designer can make better decisions for its proposed design.
- Auto-build: Select this option to run Database Designer as soon as you complete the wizard. This option only builds the proposed design.
- Auto-deploy: Select this option for auto-build designs that you want to deploy automatically.
Click Submit Design. The Database Designer page opens:
- If you chose to automatically deploy your design, Database Designer executes in the background.
- If you did not select the Auto-build or Auto-deploy options, you can click Build Design or Deploy Design on the Database Designer page.
In the My Designs pane, view the status of your design:
- When the deployment completes, the My Design pane shows Design Deployed.
- The event history window shows the details of the design build and deployment.

To run Database Designer with Administration Tools, see Running Database Designer with administration tools in this guide.

2 - Running Database Designer with administration tools

In this procedure, you create a comprehensive design with Database Designer using the Administration Tools interface.

In this procedure, you create a comprehensive design with Database Designer using the Administration Tools interface. If, in the future, you have a query that you want to optimize, you can create an enhanced (incremental) design with additional projections. You can tune these projections specifically for the query you provide. See Incremental Design for more information.

Follow these steps to create the comprehensive design using Database Designer in Administration Tools:

If you are not in Administration Tools, exit the vsql session and access Administration Tools:
- Type \q to exit vsql.
- Type admintools to access the Administration Tools Main Menu.
Start the database for which you want to create a design.
From the Main Menu, click Configuration Menu and then click OK.
From the Configuration Menu, click Run Database Designer and then click OK.
When the Select a database for design dialog box opens, select VMart and then click OK.

If you are prompted to enter the password for the database, click OK to bypass the message. Because no password was assigned when you installed the VMart database, you do not need to enter one now.
Click OK to accept the default directory for storing Database Designer output and log files.
In the Database Designer window, enter a name for the design, for example, vmart_design, and click OK. Design names can contain only alphanumeric characters or underscores. No other special characters are allowed.
Create a complete initial design. In the Design Type window, click Comprehensive and click OK.
Select the schemas. Because the VMart design is a multi-schema database, you can select all three schemas (online_sales, public, and store) for your design. Click OK.

If you include a schema that contains tables without data, the Administration Tools notifies you that designing for tables without data could be suboptimal. You can choose to continue, but Vertica recommends that you deselect the schemas that contain empty tables before you proceed.
In the Design Options window, accept all three options and click OK.

The three options are:
- Optimize with queries: Supplying the Database Designer with queries is especially important if you want to optimize the database design for query performance. Vertica recommends that you limit the design input to 100 queries.
- Update statistics: Accurate statistics help the Database Designer choose the best strategy for data compression. If you select this option, the database statistics are updated to maximize design quality.
- Deploy design: The new design deploys automatically. During deployment, new projections are added, some existing projections retained, and any necessary existing projections removed. Any new projections are refreshed to populate them with data.
Because you selected the Optimize with queries option, you must enter the full path to the file containing the queries that will be run on your database. In this example, it is:
```
/opt/vertica/examples/VMart_Schema/vmart_queries.sql
```
The queries in the query file must be delimited with semicolons (;). The last query must end with a semicolon (;).
Choose the K-safety value you want and click OK. The design K-Safety determines the number of buddy projections you want Database Designer to create.

If you create a comprehensive design on a single node, you are not prompted to enter a K-safety value.
In the Optimization Objective window, select Balanced query/load performance to create a design that is balanced between database size and query performance. Click OK.
When the informational message displays, click Proceed.

Database Designer automatically performs these actions:
- Sets up the design session.
- Examines table data.
- Loads queries from the query file you provided (in this example, /opt/vertica/examples/VMart_Schema/vmart_queries.sql).
- Creates the design.
Deploys the design or saves a SQL file containing the commands to create the design, based on your selections in the Desgin Options window.

Depending on system resources, the design process could take several minutes. You should allow this process to complete uninterrupted. If you must cancel the session, use Ctrl+C.
When Database Designer finishes, press Enter to return to the Administration Tools menu. Examine the steps taken to create the design. The files are in the directory you specified to store the output and log files. In this example, that directory is /opt/vertica/examples/VMart_Schema. For more information about the script files, see About Database Designer.

For additional information about managing your designs, see Creating a database design in the Administrator’s Guide.