This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Create a database in Eon Mode

The easiest way to create an Eon Mode database in the cloud is to use the MC.

Create an Eon Mode database in a cloud environment

The easiest way to create an Eon Mode database in the cloud is to use the MC. The MC can create your database and provision the nodes to run the database at the same time. For specific instructions for your cloud environment, see:

On AWS and Azure, you can also create an Eon Mode database using admintools. For specific instructions for your cloud environment, see:

Create an on-premises Eon Mode database

If you have an on-premises install, you can create an Eon Mode database using admintools. See Eon on-premises storage for a list of object stores that Vertica supports for communal storage. The following topics detail installation instructions for each on-premises communal storage option:

1 - Create an Eon Mode database on-premises with FlashBlade

You have two options on how to create an Eon Mode database on premises with Pure Storage FlashBlade as your S3-compatible communal storage:.

You have two options on how to create an Eon Mode database on premises with Pure Storage FlashBlade as your S3-compatible communal storage:

Step 1: create a bucket and credentials on the Pure Storage FlashBlade

To use a Pure Storage FlashBlade appliance as a communal storage location for an Eon Mode database you must have:

  • The IP address of the FlashBlade appliance. You must also have the connection port number if your FlashBlade is not using the standard port 80 or 443 to access the bucket. All of the nodes in your Vertica cluster must be able to access this IP address. Make sure any firewalls between the FlashBlade appliance and the nodes are configured to allow access.

  • The name of the bucket on the FlashBlade to use for communal storage.

  • An access key and secret key for a user account that has read and write access to the bucket.

See the Pure Storage support site for instructions on how to create the bucket and the access keys needed for a communal storage location.

Step 2: install Vertica on your cluster

To install Vertica:

  1. Ensure your nodes are configured properly by reviewing all of the content in the Before you install Vertica section.

  2. Use the install_vertica script to verify that your nodes are correctly configured and to install the Vertica binaries on all of your nodes. Follow the steps under Install Vertica using the command line to install Vertica.

Step 3: create an authorization file

Before you create your Eon Mode on-premises database, you must create an authorization file that admintools will use to authenticate with the FlashBlade storage.

  1. On the Vertica node where you will run admintools to create your database, use a text editor to create a file. You can name this file anything you wish. In these steps, it is named auth_params.conf. The location of this file isn't important, as long as it is readable by the Linux user you use to create the database (usually, dbadmin).

  2. Add the following lines to the file:

    awsauth = FlasbBlade_Access_Key:FlashBlade_Secret_Key
    awsendpoint = FlashBladeIp:FlashBladePort
    
  3. If you are not using TLS encryption for the connection between Vertica and the FlashBlade, add the following line to the file:

    awsenablehttps = 0
    
  4. Save the file and exit the editor.

This example auth_params.conf file is for an unencrypted connection between the Vertica cluster and a FlashBlade appliance at IP address 10.10.20.30 using the standard port 80.

awsauth = PIWHSNDGSHVRPIQ:339068001+e904816E02E5fe9103f8MQOEAEHFFVPKBAAL
awsendpoint = 10.10.20.30
awsenablehttps = 0

Step 4: choose a depot path on all nodes

Choose or create a directory on each node for the depot storage path. The directory you supply for the depot storage path parameter must:

  • Have the same path on all nodes in the cluster (i.e. /home/dbadmin/depot).

  • Be readable and writable by the dbadmin user.

  • Have sufficient storage. By default, Vertica uses 60% of the filesystem space containing the directory for depot storage. You can limit the size of the depot by using the --depot-size argument in the create_db command. See Configuring your Vertica cluster for Eon Mode for guidelines on choosing a size for your depot.

The admintools create_db tool will attempt to create the depot path for you if it doesn't exist.

Step 5: create the Eon on-premises database

Use the admintools create_db tool to create the database. You must pass this tool the following arguments:

Argument Description
-x The path to the auth_params.conf file.
--communal-storage-location The S3 URL for the bucket on the FlashBlade appliance (usually, this is s3://bucketname).
--depot-path The absolute path to store the depot on the nodes in the cluster.
--shard-count The number of shards for the database. This is an integer number that is usually either a multiple of the number of nodes in your cluster, or an even divider. See Planning for Scaling Your Cluster for more information.
-s A comma-separated list of the nodes in your database.
-d The name for your database.

Some common optional arguments include:

Argument Description
-l The absolute path to the Vertica license file to apply to the new database.
-p The password for the new database.
--depot-size

The maximum size for the depot. Defaults to 60% of the filesystem containing the depot path.

You can specify the size in two ways:

  • integer%: Percentage of filesystem's disk space to allocate.

  • integer{K|M|G|T}: Amount of disk space to allocate for the depot in kilobytes, megabytes, gigabytes, or terabytes.

However you specify this value, the depot size cannot be more than 80 percent of disk space of the file system where the depot is stored.

To view all arguments for the create_db tool, run the command:

admintools -t create_db --help

The following example demonstrates creating a three-node database named verticadb, specifying the depot will be stored in the home directory of the dbadmin user.

$ admintools -t create_db -x auth_params.conf \
  --communal-storage-location=s3://verticadbbucket \
  --depot-path=/home/dbadmin/depot --shard-count=6 \
  -s vnode01,vnode02,vnode03 -d verticadb -p 'YourPasswordHere'

Step 6: disable streaming limitations

After creating the database, disable the AWSStreamingConnectionPercentage configuration parameter. This setting is unnecessary for an Eon Mode on-premises install with communal storage on FlashBlade or MinIO. This configuration parameter controls the number of connections to the object store that Vertica uses for streaming reads. In a cloud environment, this setting helps avoid having streaming data from the object store use up all of the available file handles. It leaves some file handles available for other object store operations. Due to the low latency of on-premises object stores, this option is unnecessary. Set it to 0 to disable it.

The following example shows how to disable this parameter using ALTER DATABASE...SET PARAMETER:

=> ALTER DATABASE DEFAULT SET PARAMETER AWSStreamingConnectionPercentage = 0;
ALTER DATABASE

Deciding whether to disable the depot

The FlashBlade object store's performance is fast enough that you may consider disabling the depot in your Vertica database. If you disable the depot, you can get by with less local storage on your nodes. However, there is always a performance impact of disabling the depot. The exact impact depends mainly on the types of workloads you run on your database. The performance impact can range from a 30% to 4000% decrease in query performance. Only consider disabling the depot if you will see a significant benefit from reducing the storage requirements of your nodes. Before disabling the depot on a production database, always run a proof of concept test that executes the same workloads as your production database.

To disable the depot, set the UseDepotForReads configuration parameter to 0. The following example demonstrates disabling this parameter using ALTER DATABASE...SET PARAMETER:


=> ALTER DATABASE DEFAULT SET PARAMETER UseDepotForReads = 0;
ALTER DATABASE

2 - Create an Eon Mode database on-premises with HDFS

To use HDFS as a communal storage location for an Eon Mode database you must:.

Step 1: satisfy HDFS environment prerequisites

To use HDFS as a communal storage location for an Eon Mode database you must:

  • Run the WebHDFS service.

  • If using Kerberos, create a Kerberos principal for the Vertica (system) user as described in Kerberos authentication, and grant it read and write access to the location in HDFS where you will place your communal storage. Vertica always uses this system principal to access communal storage.

  • If using High Availability Name Node or swebhdfs, distribute the HDFS configuration files to all Vertica nodes as described in Configuring HDFS access. This step is necessary even though you do not use the hdfs scheme for communal storage.

  • If using swebhdfs (wire encryption) instead of webhdfs, configure the HDFS cluster with certificates trusted by the Vertica hosts and set dfs.encrypt.data.transfer in hdfs-site.xml.

  • Vertica has no additional requirements for encryption at rest. Consult the documentation for your Hadoop distribution for information on how to configure encryption at rest for WebHDFS.

Step 2: install Vertica on your cluster

To install Vertica:

  1. Ensure your nodes are configured properly by reviewing all of the content in the Before you install Vertica section.

  2. Use the install_vertica script to verify that your nodes are correctly configured and to install the Vertica binaries on all of your nodes. Follow the steps under Install Vertica using the command line to install Vertica.

Step 3: create a bootstrapping file

Before you create your Eon Mode on-premises database, you must create a bootstrapping file to specify parameters that are required for database creation. This step applies if you are using Kerberos, High Availability Name Node, or TLS (wire encryption).

  1. On the Vertica node where you will run admintools to create your database, use a text editor to create a file. You can name this file anything you wish. In these steps, it is named bootstrap_params.conf. The location of this file isn't important, as long as it is readable by the Linux user you use to create the database (usually, dbadmin).

  2. Add the following lines to the file. HadoopConfDir is typically set to /etc/hadoop/conf; KerberosServiceName is usually set to vertica.

    HadoopConfDir = config-path
    KerberosServiceName = principal-name
    KerberosRealm = realm-name
    KerberosKeytabFile = keytab-path
    

    If you are not using HA Name Node, for example in a test environment, you can omit HadoopConfDir and use an explicit Name Node host and port when specifying the location of the communal storage.

  3. Save the file and exit the editor.

Step 4: choose a depot path on all nodes

Choose or create a directory on each node for the depot storage path. The directory you supply for the depot storage path parameter must:

  • Have the same path on all nodes in the cluster (i.e. /home/dbadmin/depot).

  • Be readable and writable by the dbadmin user.

  • Have sufficient storage. By default, Vertica uses 60% of the filesystem space containing the directory for depot storage. You can limit the size of the depot by using the --depot-size argument in the create_db command. See Configuring your Vertica cluster for Eon Mode for guidelines on choosing a size for your depot.

The admintools create_db tool will attempt to create the depot path for you if it doesn't exist.

Step 5: create the Eon on-premises database

Use the admintools create_db tool to create the database. You must pass this tool the following arguments:

Argument Description
-x The path to the bootstrap configuration file (bootstrap_params.conf in the examples in this section).
--communal-storage-location The webhdfs or swebhdfs URL for the HDFS location. You cannot use the hdfs scheme.
--depot-path The absolute path to store the depot on the nodes in the cluster.
--shard-count The number of shards for the database. This is an integer number that is usually either a multiple of the number of nodes in your cluster, or an even divider. See Planning for Scaling Your Cluster for more information.
-s A comma-separated list of the nodes in your database.
-d The name for your database.

Some common optional arguments include:

Argument Description
-l The absolute path to the Vertica license file to apply to the new database.
-p The password for the new database.
--depot-size

The maximum size for the depot. Defaults to 60% of the filesystem containing the depot path.

You can specify the size in two ways:

  • integer%: Percentage of filesystem's disk space to allocate.

  • integer{K|M|G|T}: Amount of disk space to allocate for the depot in kilobytes, megabytes, gigabytes, or terabytes.

However you specify this value, the depot size cannot be more than 80 percent of disk space of the file system where the depot is stored.

To view all arguments for the create_db tool, run the command:

admintools -t create_db --help

The following example demonstrates creating a three-node database named verticadb, specifying the depot will be stored in the home directory of the dbadmin user.

$ admintools -t create_db -x bootstrap_params.conf \
  --communal-storage-location=webhdfs://mycluster/verticadb \
  --depot-path=/home/dbadmin/depot  --shard-count=6 \
  -s vnode01,vnode02,vnode03 -d verticadb -p 'YourPasswordHere'

If you are not using HA Name Node, for example in a test environment, you can use an explicit Name Node host and port for --communal-storage-location as in the following example.

$ admintools -t create_db -x bootstrap_params.conf \
  --communal-storage-location=webhdfs://namenode.hadoop.example.com:50070/verticadb \
  --depot-path=/home/dbadmin/depot  --shard-count=6 \
  -s vnode01,vnode02,vnode03 -d verticadb -p 'YourPasswordHere'

3 - Create an Eon Mode database on-premises with MinIO

To use MinIO as a communal storage location for an Eon Mode database, you must have:.

Step 1: create a bucket and credentials on MinIO

To use MinIO as a communal storage location for an Eon Mode database, you must have:

  • The IP address and port number of the MinIO cluster. MinIO's default port number is 9000. A Vertica database running in Eon Mode defaults to using port 80 for unencrypted connections and port 443 for TLS encrypted connection. All of the nodes in your Vertica cluster must be able to access the MinIO cluster's IP address. Make sure any firewalls between the MinIO cluster and the nodes are configured to allow access.

  • The name of the bucket on the MinIO cluster to use for communal storage.

  • An access key and secret key for a user account that has read and write access to the bucket.

See the MinIO documentation for instructions on how to create the bucket and the access keys needed for a communal storage location.

Step 2: install Vertica on your cluster

To install Vertica:

  1. Ensure your nodes are configured properly by reviewing all of the content in the Before you install Vertica section.

  2. Use the install_vertica script to verify that your nodes are correctly configured and to install the Vertica binaries on all of your nodes. Follow the steps under Install Vertica using the command line to install Vertica.

Step 3: create an authorization file

Before you create your Eon Mode on-premises database, you must create an authorization file that admintools will use to authenticate with the MinIO storage cluster.

  1. On the Vertica node where you will run admintools to create your database, use a text editor to create a file. You can name this file anything you wish. In these steps, it is named auth_params.conf. The location of this file isn't important, as long as it is readable by the Linux user you use to create the database (usually, dbadmin).

  2. Add the following lines to the file:

    awsauth = MinIO_Access_Key:MinIO_Secret_Key
    awsendpoint = MinIOIp:MinIOPort
    
  3. If you are not using TLS encryption for the connection between Vertica and MinIO, add the following line to the file:

    awsenablehttps = 0
    
  4. Save the file and exit the editor.

This example auth_params.conf file is for an unencrypted connection between the Vertica cluster and a MinIO cluster at IP address 10.20.30.40 using port 9000 (which is the default for MinIO).

awsauth = PIWHSNDGSHVRPIQ:339068001+e904816E02E5fe9103f8MQOEAEHFFVPKBAAL
awsendpoint = 10.20.30.40:9000
awsenablehttps = 0

Step 4: choose a depot path on all nodes

Choose or create a directory on each node for the depot storage path. The directory you supply for the depot storage path parameter must:

  • Have the same path on all nodes in the cluster (i.e. /home/dbadmin/depot).

  • Be readable and writable by the dbadmin user.

  • Have sufficient storage. By default, Vertica uses 60% of the filesystem space containing the directory for depot storage. You can limit the size of the depot by using the --depot-size argument in the create_db command. See Configuring your Vertica cluster for Eon Mode for guidelines on choosing a size for your depot.

The admintools create_db tool will attempt to create the depot path for you if it doesn't exist.

Step 5: create the Eon on-premises database

Use the admintools create_db tool to create the database. You must pass this tool the following arguments:

Argument Description
-x The path to the auth_params.conf file.
--communal-storage-location The S3 URL for the bucket on the MinIO cluster (usually, this is s3://bucketname).
--depot-path The absolute path to store the depot on the nodes in the cluster.
--shard-count The number of shards for the database. This is an integer number that is usually either a multiple of the number of nodes in your cluster, or an even divider. See Planning for Scaling Your Cluster for more information.
-s A comma-separated list of the nodes in your database.
-d The name for your database.

Some common optional arguments include:

Argument Description
-l The absolute path to the Vertica license file to apply to the new database.
-p The password for the new database.
--depot-size

The maximum size for the depot. Defaults to 60% of the filesystem containing the depot path.

You can specify the size in two ways:

  • integer%: Percentage of filesystem's disk space to allocate.

  • integer{K|M|G|T}: Amount of disk space to allocate for the depot in kilobytes, megabytes, gigabytes, or terabytes.

However you specify this value, the depot size cannot be more than 80 percent of disk space of the file system where the depot is stored.

To view all arguments for the create_db tool, run the command:

admintools -t create_db --help

The following example demonstrates creating a three-node database named verticadb, specifying the depot will be stored in the home directory of the dbadmin user.

$ admintools -t create_db -x auth_params.conf \
  --communal-storage-location=s3://verticadbbucket \
  --depot-path=/home/dbadmin/depot  --shard-count=6 \
  -s vnode01,vnode02,vnode03 -d verticadb -p 'YourPasswordHere'

Step 6: disable streaming limitations

After creating the database, disable the AWSStreamingConnectionPercentage configuration parameter. This setting is unnecessary for an Eon Mode on-premises install with communal storage on FlashBlade or MinIO. This configuration parameter controls the number of connections to the object store that Vertica uses for streaming reads. In a cloud environment, this setting helps avoid having streaming data from the object store use up all of the available file handles. It leaves some file handles available for other object store operations. Due to the low latency of on-premises object stores, this option is unnecessary. Set it to 0 to disable it.

The following example shows how to disable this parameter using ALTER DATABASE...SET PARAMETER:

=> ALTER DATABASE DEFAULT SET PARAMETER AWSStreamingConnectionPercentage = 0;
ALTER DATABASE

4 - Manually create an Eon Mode database on Azure

Once you have met the cluster and storage requirements for using an Eon Mode database on Azure, you are ready to create an Eon Mode database.

Once you have met the cluster and storage requirements for using an Eon Mode database on Azure, you are ready to create an Eon Mode database. Use the admintools create_db tool to create your Eon Mode database.

Creating an authentication file

If your database will use a managed identity to authenticate with the Azure storage container, you do not need to supply any additional configuration information to the create_db tool.

If your database will not use a managed identity, you must supply create_db with authentication information in a configuration file. It must contain at least the AzureStorageCredentials parameter that defines one or more account names and keys Vertica will use to access blob storage. It can also contain an AzureStorageEnpointConfig parameter that defines an alternate endpoint to use instead of the the default Azure host name. This option is useful if you are creating a test environment using an Azure storage emulator such as Azurite.

The following table defines the values that can be set in these two parameters.

AzureStorageCredentials
Collection of JSON objects, each of which specifies connection credentials for one endpoint. This parameter takes precedence over Azure managed identities.

The collection must contain at least one object and may contain more. Each object must specify at least one of accountName or blobEndpoint, and at least one of accountKey or sharedAccessSignature.

  • accountName: If not specified, uses the label of blobEndpoint.
  • blobEndpoint: Host name with optional port (host:port). If not specified, uses account.blob.core.windows.net.
  • accountKey: Access key for the account or endpoint.
  • sharedAccessSignature: Access token for finer-grained access control, if being used by the Azure endpoint.
AzureStorageEndpointConfig
Collection of JSON objects, each of which specifies configuration elements for one endpoint. Each object must specify at least one of accountName or blobEndpoint.
  • accountName: If not specified, uses the label of blobEndpoint.
  • blobEndpoint: Host name with optional port (host:port). If not specified, uses account.blob.core.windows.net.
  • protocol: HTTPS (default) or HTTP.
  • isMultiAccountEndpoint: true if the endpoint supports multiple accounts, false otherwise (default is false). To use multiple-account access, you must include the account name in the URI. If a URI path contains an account, this value is assumed to be true unless explicitly set to false.

The authentication configuration file is a text file containing the configuration parameter names and their values. The values are in a JSON format. The name of this file is not important. The following examples use the file name auth_params.conf.

The following example is a configuration file for a storage account hosted on Azure. The storage account name is mystore, and the key value is a placeholder. In your own configuration file, you must provide the storage account's access key. You can find this value by right-clicking the storage account in the Azure Storage Explorer and selecting Copy Primary Key.

AzureStorageCredentials=[{"accountName": "mystore", "accountKey": "access-key"}]

The following example shows a configuration file that defines an account for a storage container hosted on the local system using the Azurite storage system. The user account and key are the "well-known" account provided by Azurite by default. Because this configuration uses an alternate storage endpoint, it also defines the AzureStorageEndpointConfig parameter. In addition to reiterating the account name and endpoint definition, this example sets the protocol to the non-encrypted HTTP.

AzureStorageCredentials=[{"accountName": "devstoreaccount1", "blobEndpoint": "127.0.0.1:10000 ",
                          "accountKey":
"Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw=="
                        }]

AzureStorageEndpointConfig=[{"accountName": "devstoreaccount1",
                             "blobEndpoint": "127.0.0.1:10000", "protocol": "http"}]

Creating the Eon Mode database

Use the admintools create_db tool to create your Eon Mode database. The required arguments you pass to this tool are:

Argument Description
--communal-storage-location The URI for the storage container Vertica will use for communal storage. This URI must use the azb:// schema. See Azure Blob Storage object store for the format of this URI.
-x The path to the file containing the authentication parameters Vertica needs to access the communal storage location. This argument is only required if your database will use a storage account name and key to authenticate with the storage container. If it is using a managed identity, you do not need to specify this argument.
--depot-path The absolute path to store the depot on the nodes in the cluster.
--shard-count The number of shards for the database. This is an integer number that is usually either a multiple of the number of nodes in your cluster, or an even divisor. See Planning for Scaling Your Cluster for more information.
-s A comma-separated list of the nodes in your database.
-d The name for your database.

Some other common optional arguments for create_db are:

Argument Description
-l The absolute path to the Vertica license file to apply to the new database.
-p The password for the new database.
--depot-size

The maximum size for the depot. Defaults to 60% of the filesystem containing the depot path.

You can specify the size in two ways:

  • integer%: Percentage of filesystem's disk space to allocate.

  • integer{K|M|G|T}: Amount of disk space to allocate for the depot in kilobytes, megabytes, gigabytes, or terabytes.

However you specify this value, the depot size cannot be more than 80 percent of disk space of the file system where the depot is stored.

To view all arguments for the create_db tool, run the command:

admintools -t create_db --help

The following example demonstrates creating an Eon Mode database with the following settings:

  • Vertica will use a storage account named mystore.

  • The communal data will be stored in a directory named verticadb located in a storage container named db_blobs.

  • The authentication information Vertica needs to access the storage container is in the file named auth_params.conf in the current directory. The contents of this file are shown in the first example under Creating an Authentication File.

  • The hostnames of the nodes in the cluster are node01 through node03.

$ admintools -t create_db \
             --communal-storage-location=azb://mystore/db_blobs/verticadb \
             -x auth_params.conf -s node01,node02,node03  \
             -d verticadb --depot-path /vertica/depot --shard-count 3 \
             -p 'mypassword'