CREATE statements

• 1: CREATE ACCESS POLICY
• 2: CREATE AUTHENTICATION
• 3: CREATE CA BUNDLE
• 4: CREATE CERTIFICATE
• 5: CREATE DATA LOADER
• 6: CREATE DIRECTED QUERY
• 7: CREATE EXTERNAL TABLE AS COPY
• 8: CREATE EXTERNAL TABLE ICEBERG
• 9: CREATE FAULT GROUP
• 10: CREATE FLEXIBLE EXTERNAL TABLE AS COPY
• 11: CREATE FLEXIBLE TABLE
• 12: CREATE FUNCTION statements
• 12.1: CREATE AGGREGATE FUNCTION
• 12.2: CREATE ANALYTIC FUNCTION
• 12.3: CREATE FILTER
• 12.4: CREATE FUNCTION (scalar)
• 12.5: CREATE FUNCTION (SQL)
• 12.6: CREATE PARSER
• 12.7: CREATE SOURCE
• 12.8: CREATE TRANSFORM FUNCTION
• 13: CREATE HCATALOG SCHEMA
• 14: CREATE KEY
• 15: CREATE LIBRARY
• 16: CREATE LOAD BALANCE GROUP
• 17: CREATE LOCAL TEMPORARY VIEW
• 18: CREATE LOCATION
• 19: CREATE NAMESPACE
• 20: CREATE NETWORK ADDRESS
• 21: CREATE NETWORK INTERFACE
• 22: CREATE NOTIFIER
• 23: CREATE PROCEDURE (external)
• 24: CREATE PROCEDURE (stored)
• 25: CREATE PROFILE
• 26: CREATE PROJECTION
• 26.1: Encoding types
• 26.2: GROUPED clause
• 26.3: Hash segmentation clause
• 26.4: Live aggregate projection
• 26.5: Standard projection
• 26.6: Top-k projection
• 26.7: UDTF projection
• 26.8: Unsegmented clause
• 27: CREATE RESOURCE POOL
• 27.1: Built-in pools
• 27.2: Built-in resource pools configuration
• 28: CREATE ROLE
• 29: CREATE ROUTING RULE
• 30: CREATE SCHEDULE
• 31: CREATE SCHEMA
• 32: CREATE SEQUENCE
• 33: CREATE SUBNET
• 34: CREATE TABLE
• 34.1: Column-constraint
• 34.2: Column-definition
• 34.3: Column-name-list
• 34.4: Partition clause
• 34.5: Table-constraint
• 35: CREATE TEMPORARY TABLE
• 36: CREATE TEXT INDEX
• 37: CREATE TLS CONFIGURATION
• 38: CREATE TRIGGER
• 39: CREATE USER
• 40: CREATE VIEW

1 - CREATE ACCESS POLICY

Creates an access policy that filters access to table data to users and roles. You can create access policies for table rows and columns. Vertica applies the access policy filters with each query and returns only the data that is permissible for the current user or role.

You cannot set access policies on columns of complex data types other than native arrays. If the table contains complex-type columns, you can still set row access policies and column access policies on other columns.

Syntax

CREATE ACCESS POLICY ON [[database.]schema.]table
    { FOR COLUMN column | FOR ROWS WHERE } expression [GRANT TRUSTED] { ENABLE | DISABLE }

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

table

The table with the target column or rows.

FOR COLUMN column

The column on which to apply this access policy. The column can be a native array, but other complex types are not supported. (See Complex types.)

FOR ROWS WHERE

The rows on which to apply this access policy.

expression

A SQL expression that specifies conditions for accessing row or column data:

• Row access policies limit access to specific rows in a table, as specified by the policy's WHERE expression. Only rows that satisfy this expression are fetched from the table. For details and sample usage, see Creating row access policies.

• Column access policies limit access to specific table columns. The access policy expression can also specify how to render column data to specific users and roles. For details and sample usage, see Creating column access policies.

GRANT TRUSTED

Specifies that GRANT statements take precedence over the access policy in determining whether users can perform DML operations on the target table. If omitted, users can only modify table data if the access policy allows them to see the stored data in its original, unaltered state. For more information, see Access policies and DML operations.

Important

GRANT TRUSTED only affects DML operations and does not enable users to see data that the access policy would otherwise mask. Specifying this option may allow users with certain grants to update data that they cannot see.

ENABLE | DISABLE

Whether to enable the access policy. You can enable and disable existing access policies with ALTER ACCESS POLICY.

Privileges

Non-superuser: Ownership of the table

Restrictions

The following limitations apply to access policies:

• A column can have only one access policy.

• Column access policies cannot be set on columns of complex types other than native arrays.

• Column access policies cannot be set for materialized columns on flex tables. While it is possible to set an access policy for the __raw__ column, doing so restricts access to the whole table.

• Row access policies are invalid on temporary tables and tables with aggregate projections.

• Access policy expressions cannot contain:

  • Subqueries

  • Aggregate functions

  • Analytic functions

  • User-defined transform functions (UDTF)

• If the query optimizer cannot replace a deterministic expression that involves only constants with their computed values, it blocks all DML operations such as INSERT.

See also

• Access policies

• ALTER ACCESS POLICY

• DROP ACCESS POLICY

2 - CREATE AUTHENTICATION

Creates and enables an authentication record associated with users or roles. Authentication records are automatically enabled after creation.

Syntax

CREATE AUTHENTICATION auth-record-name
            METHOD 'auth-method'
            access-method
            [ FALLTHROUGH ]

Parameters

Privileges

DBADMIN

Examples

See Creating authentication records.

See also

• ALTER AUTHENTICATION
• DROP AUTHENTICATION
• GRANT (authentication)
• REVOKE (authentication)

3 - CREATE CA BUNDLE

Creates a certificate authority (CA) bundle. These contain root CA certificates.

Syntax

CREATE CA BUNDLE name [CERTIFICATES ca_cert[, ca_cert[, ...]]

Parameters

name

The name of the CA bundle.

ca_cert

The name of the CA certificate. If no certificates are specified, the bundle will be empty.

Privileges

Ownership of the CA certificates in the CA bundle.

Examples

See Managing CA bundles.

See also

• ALTER CA BUNDLE
• DROP CA BUNDLE

4 - CREATE CERTIFICATE

Creates or imports a certificate, Certificate Authority (CA), or intermediate CA. These certificates can be used with ALTER TLS CONFIGURATION to set up client-server TLS, LDAPLink TLS, LDAPAuth TLS, and internode TLS.

CREATE CERTIFICATE generates x509v3 certificates.

Syntax

CREATE [TEMP[ORARY]] [CA] CERTIFICATE certificate_name
    {AS cert [KEY key_name]
    | SUBJECT subject
      [ SIGNED BY ca_cert ]
      [ VALID FOR days ]
      [ EXTENSIONS ext = val[,...] ]
      [ KEY private_key ]}

Parameters

TEMPORARY

Create with session scope. The key is stored in memory and is valid only for the current session.

CA

Designates the certificate as a CA or intermediate certificate. If omitted, the operation creates a normal certificate.

certificate_name

The name of the certificate.

AS cert

The imported certificate (string).

This parameter should include the entire chain of certificates, excluding the CA certificate.

KEY key_name

The name of the key.

This parameter only needs to be set for client/server certificates and CA certificates that you intend to sign other certificates with in Vertica. If your imported CA certificate will only be used for validating other certificates, you do not need to specify a key.

SUBJECT subject

The entity to issue the certificate to (string).

SIGNED BY ca_cert

The name of the CA that signed the certificate.

When adding a CA certificate, this parameter is optional. Specifying it will create an intermediate CA that cannot be used to sign other CA certificates.

When creating a certificate, this parameter is required.

VALID FOR days

The number of days that the certificate is valid.

EXTENSIONS ext=val

Strings specifying certificate extensions. For a full list of extensions, see the OpenSSL documentation.

KEY private_key

The name of the certificate's private key.

When importing a certificate, this parameter is required.

Privileges

Superuser

Default extensions

CREATE CERTIFICATE generates x509v3 certificates and includes several extensions by default. These differ based on the type of certificate you create:

CA Certificate:

• 'basicConstraints' = 'critical, CA:true'

• 'keyUsage' = 'critical, digitalSignature, keyCertSign'

• 'nsComment' = Vertica generated [CA] certificate'

• 'subjectKeyIdentifier' = 'hash'

Certificate:

• 'basicConstraints' = 'CA:false'

• 'keyUsage' = 'critical, digitalSignature, keyEncipherment'

Examples

See Generating TLS certificates and keys.

See also

• CREATE KEY

5 - CREATE DATA LOADER

CREATE DATA LOADER creates an automatic data loader that executes a COPY statement when new data files appear in a specified path. The loader records which files have already been successfully loaded and skips them. These events are recorded in the DATA_LOADER_EVENTS system table.

You can execute a data loader in the following ways:

• On demand: Use EXECUTE DATA LOADER to execute the data loader once.

• On a schedule: Use a scheduled stored procedure.

• In response to a notification (AWS SQS only): Define a trigger on the data loader.

Executing the loader automatically commits the transaction.

The DATA_LOADERS system table shows all defined loaders. The DATA_LOADER_EVENTS system table records paths that were attempted and their outcomes. To prevent unbounded growth, records in DATA_LOADER_EVENTS are purged after a specified retention interval. If previously-loaded files are still in the source path after this purge, the data loader sees them as new files and loads them again.

Syntax

CREATE [ OR REPLACE ] DATA LOADER [ IF NOT EXISTS ]
   [schema.]name
   [ RETRY LIMIT { NONE | DEFAULT | limit } ]
   [ RETENTION INTERVAL monitoring-retention ]
   [ TRIGGERED BY integration-json ]
   AS copy-statement

Arguments

OR REPLACE

If a data loader with the same name in the same schema exists, replace it. The original data loader's monitoring table is dropped.

This option cannot be used with IF NOT EXISTS.

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

This option cannot be used with OR REPLACE.

schema

Schema containing the data loader. The default schema is public.

name

Name of the data loader.

RETRY LIMIT { NONE | DEFAULT | limit }

Maximum number of times to retry a failing file. Each time the data loader is executed, it attempts to load all files that have not yet been successfully loaded, up to this per-file limit. If set to DEFAULT, at load time the loader uses the value of the DataLoaderDefaultRetryLimit configuration parameter.

Default: DEFAULT

RETENTION INTERVAL monitoring-retention

How long to keep records in the events table. DATA_LOADER_EVENTS records events for all data loaders, but each data loader has its own retention interval.

Default: 14 days

TRIGGERED BY integration-json

Information about an external input for the data loader, a JSON object with the following keys:

• provider (required): name of the provider; the only supported value is "AWS-SQS".
• resourceUrl (required): endpoint for the integration.
• timeout: number of seconds to wait between loads. The default is 10.
• minFileNumPerLoad: if the number of files waiting to be loaded reaches this threshold, the load begins immediately instead of waiting for the next scheduled load. The default is 10 files.
• minTotalSizePerLoad: if the total size of files waiting to be loaded reaches this threshold, the load begins immediately. The value is an integer with a unit of measure, one of K, M, G, or T. The default is 1G.

If a data loader has a trigger, the loader is initially disabled. When you are ready to use it, use ALTER DATA LOADER with the ENABLE option. When the loader is enabled, Vertica executes it automatically when SQS produces notifications for new files. For details, see Triggers.

AS copy-statement

The COPY statement that the loader executes. The FROM clause typically uses a glob.

Privileges

Non-superuser: CREATE privileges on the schema.

Restrictions

• COPY NO COMMIT is not supported.
• Data loaders are executed with COPY ABORT ON ERROR.
• The COPY statement must specify file paths. You cannot use COPY FROM VERTICA.

Triggers (AWS SQS only)

If the data to be loaded is stored on AWS and you have configured an SQS queue, then instead of executing a data loader explicitly using EXECUTE DATA LOADER, you can define the loader to respond to S3 events. When files are added to the AWS bucket, a service running on AWS adds a message to the SQS queue. Vertica reads from this queue, executes the data loader, and, if the load was successful, removes the message from the queue. After the initial setup, this process is automatic.

To configure the data loader to read from SQS, use the TRIGGERED BY option with a JSON object following this template:

{
  "provider" : "AWS-SQS",
  "resourceUrl" : "https://sqs.region.amazonaws.com/account_number/queue_name"
}

You can set additional values to control timing. See the description of the TRIGGERED BY syntax.

Use the following SQS parameters to authenticate to SQS:

• SQSAuth, to set an ID and key for all queue access.
• SQSQueueCredentials, to set per-queue access. Queue-specific values take precedence over SQSAuth.

A data loader with an SQS trigger is initially disabled. Use ALTER DATA LOADER with the ENABLE option when you are ready to begin processing events. To pause execution, call ALTER DATA LOADER with the DISABLE option.

You can alter the trigger JSON using ALTER DATA LOADER. You must disable the loader before changing the trigger and then enable it again.

If a data loader has a trigger, you must disable it before dropping it.

File systems

The source path can be any shared file system or object store that all database nodes can access. To use an HDFS or Linux file system safely, you must prevent the loader from reading a partially-written file. One way to achieve this is to only execute the loader when files are not being written to the loader's path. Another way to achieve this is to write new files in a temporary location and move them to the loader's path only when they are complete.

Examples

The following data loader can be executed manually or by using a scheduled stored procedure:

=> CREATE DATA LOADER s.dl1 RETRY LIMIT NONE 
   AS COPY s.local FROM 's3://b/data/*.dat';

=> SELECT name, schemaname, copystmt, retrylimit FROM data_loaders;
 name | schemaname |              copystmt                 | retrylimit 
------+------------+---------------------------------------+------------
 dl1  | s          | COPY s.local FROM 's3://b/data/*.dat' |         -1
(1 row)

The following data loader reads from an SQS queue on AWS and loads new files automatically at least every 10 seconds, or immediately when five files or 100MB of data accumulate:

=> CREATE DATA LOADER s.dl2
   TRIGGERED BY '{ "provider" : "AWS-SQS", 
                   "resourceUrl" : "https://sqs.us-east-1.amazonaws.com/accountID/queueName",
                   "minFileNumPerLoad" : "5",
                   "minTotalSizePerLoad" : "100M" }'
   AS COPY s.local FROM 's3://b/data/*.dat';

See Automatic load for an extended example.

See also

• EXECUTE DATA LOADER
• ALTER DATA LOADER
• DROP DATA LOADER

6 - CREATE DIRECTED QUERY

Saves an association between an input query and a query that is annotated with optimizer hints.

CREATE DIRECTED QUERY has two variants:

• CREATE DIRECTED QUERY OPTIMIZER directs the query optimizer to generate annotated SQL from the specified input query. The annotated query contains hints that the optimizer can use to recreate its current query plan for that input query.
• CREATE DIRECTED QUERY CUSTOM specifies an annotated query supplied by the user. Vertica associates the annotated query with the input query specified by the last SAVE QUERY statement.

In both cases, Vertica associates the annotated query and input query, and registers their association in the system table DIRECTED_QUERIES under query_name.

Syntax

Optimizer-generated

CREATE DIRECTED QUERY OPT[IMIZER] query-name [COMMENT 'comments'] input-query

User-defined (custom)

CREATE DIRECTED QUERY CUSTOM query-name [COMMENT 'comments'] annotated-query

Parameters

OPT[IMIZER]

Directs the query optimizer to generate an annotated query from input-query, and associate both in the new directed query.

CUSTOM

Specifies to associate annotated-query with the query previously specified by SAVE QUERY.

query-name

A unique identifier for the directed query, a string that conforms to conventions described in Identifiers.

COMMENT 'comments'

Comment about the directed query, up to 128 characters. Comments can be useful for future reference—for example, to explain why a given directed query was created.

If you omit this argument, Vertica inserts one of the following comments:

• Optimizer-generated directed query

• Custom directed query

input-query

The input query to associate with an optimizer-generated directed query. The input query supports only one optimizer hint, :v (alias IGNORECONST).

annotated-query

A query with embedded optimizer hints to associate with the input query most recently saved with SAVE QUERY.

Privileges

See also

7 - CREATE EXTERNAL TABLE AS COPY

CREATE EXTERNAL TABLE AS COPY creates a table definition for data external to your Vertica database. This statement is a combination of the CREATE TABLE and COPY statements, supporting a subset of each statement's parameters.

Canceling a CREATE EXTERNAL TABLE AS COPY statement can cause unpredictable results. If you need to make a change, allow the statement to complete, drop the table, and then retry.

You can use ALTER TABLE to change the data types of columns instead of dropping and recreating the table.

You can use CREATE EXTERNAL TABLE AS COPY with any types except types from the Place package.

Vertica also supports external tables backed by Iceberg data. See CREATE EXTERNAL TABLE ICEBERG.

Syntax

CREATE EXTERNAL TABLE [ IF NOT EXISTS ] [[{namespace. | database. }]schema.]table-name 
    ( column-definition[,...] )
[{INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES]
AS COPY
    [ ( { column-as-expression | column }
       [ DELIMITER [ AS ] 'char' ]
       [ ENCLOSED [ BY ] 'char' ]
       [ ENFORCELENGTH ]
       [ ESCAPE [ AS ] 'char' | NO ESCAPE ]
       [ FILLER datatype ]
       [ FORMAT 'format' ]
       [ NULL [ AS ] 'string' ]
       [ TRIM 'byte' ]
    [,...] ) ]
    [ COLUMN OPTION ( column 
       [ DELIMITER [ AS ] 'char' ]
       [ ENCLOSED [ BY ] 'char' ]
       [ ENFORCELENGTH ]
       [ ESCAPE [ AS ] 'char' | NO ESCAPE ]
       [ FORMAT 'format' ]
       [ NULL [ AS ] 'string' ]
       [ TRIM 'byte' ]
    [,...] ) ]


FROM {
   { 'path-to-data'
       [ ON { nodename | (nodeset) | ANY NODE | EACH NODE } ] [ compression ] }[,...]
     [ PARTITION COLUMNS column[,...] ]
   | LOCAL 'path-to-data' [ compression ] [,...]
   | VERTICA {source-namespace. | source-database. }[source-schema.]source-table[( source-column[,...] ) ]
  }
      [ NATIVE
        | FIXEDWIDTH COLSIZES {( integer )[,...]}
        | NATIVE VARCHAR
        | ORC
        | PARQUET
      ]
   [ ABORT ON ERROR ]
   [ DELIMITER [ AS ] 'char' ]
   [ ENCLOSED [ BY ] 'char' ]
   [ ENFORCELENGTH ]
   [ ERROR TOLERANCE ]
   [ ESCAPE AS 'char' | NO ESCAPE ]
   [ EXCEPTIONS 'path' [ ON nodename ] [,...] ]
   [ [ WITH ] FILTER filter( [ arg=value[,...] ] ) ]
   [ NULL [ AS ] 'string' ]
   [ [ WITH ] PARSER parser([arg=value [,...] ]) ]
   [ RECORD TERMINATOR 'string' ]
   [ REJECTED DATA 'path' [ ON nodename ] [,...] ]
   [ REJECTMAX integer ]
   [ SKIP integer ]
   [ SKIP BYTES integer ]
   [ TRAILING NULLCOLS ]
   [ TRIM 'byte' ]

Parameters

For all supported parameters, see the CREATE TABLE and COPY statements. For information on using this statement with UDLs, see User-defined load (UDL).

For additional guidance on using COPY parameters, see Specifying where to load data from.

Privileges

Superuser, or non-superuser with the following privileges:

• READ privileges on the USER-accessible storage location. See GRANT (storage location)

• Full access (including SELECT) to an external table that the user has privileges to create

Partitioned data

Data can be partitioned using its directory structure and Vertica can take advantage of that partitioning to improve query performance for external tables. For details, seePartitioned data.

If you see unexpected results when reading data, verify that globs in your file paths correctly align with the partition structure. See Troubleshooting external tables.

ORC and Parquet data

When using the ORC and Parquet formats, Vertica supports some additional options in the COPY statement and data structures for columns. See ORC and PARQUET.

Examples

The following example defines an external table for delimited data stored in HDFS:

=> CREATE EXTERNAL TABLE sales (itemID INT, date DATE, price FLOAT)
    AS COPY FROM 'hdfs:///data/ext1.csv' DELIMITER ',';

The following example uses data in the ORC format that is stored in S3. The data has two partition columns. For more information about partitions, see Partitioned data.

=> CREATE EXTERNAL TABLE records (id int, name varchar(50), created date, region varchar(50))
   AS COPY FROM 's3://datalake/sales/*/*/*'
   PARTITION COLUMNS created, region;

The following example shows how you can read from all Parquet files in a local directory, with no partitions and no globs:

=> CREATE EXTERNAL TABLE sales (itemID INT, date DATE, price FLOAT)
    AS COPY FROM '/data/sales/*.parquet' PARQUET;

The following example creates an external table for data containing arrays:

=> CREATE EXTERNAL TABLE cust (cust_custkey int,
        cust_custname varchar(50),
        cust_custstaddress ARRAY[varchar(100)],
        cust_custaddressln2 ARRAY[varchar(100)],
        cust_custcity ARRAY[varchar(50)],
        cust_custstate ARRAY[char(2)],
        cust_custzip ARRAY[int],
        cust_email varchar(50), cust_phone varchar(30))
   AS COPY FROM 'webhdfs://data/*.parquet' PARQUET;

To allow users without superuser access to use external tables with data on the local file system, S3, or GCS, create a location for 'user' usage and grant access to it. This example shows granting access to a user named Bob to any external table whose data is located under /tmp (including in subdirectories to any depth):

=> CREATE LOCATION '/tmp' ALL NODES USAGE 'user';
=> GRANT ALL ON LOCATION '/tmp' to Bob;

The following example shows CREATE EXTERNAL TABLE using a user-defined source:

=> CREATE SOURCE curl AS LANGUAGE 'C++' NAME 'CurlSourceFactory' LIBRARY curllib;
=> CREATE EXTERNAL TABLE curl_table1 as COPY SOURCE CurlSourceFactory;

See also

8 - CREATE EXTERNAL TABLE ICEBERG

Creates an external table for data stored by Apache Iceberg. An Iceberg table consists of data files and metadata describing the schema. Unlike other external tables, an Iceberg external table need not specify column definitions (DDL). The information is read from Iceberg metadata at query time. For certain data types you can adjust column definitions, for example to specify VARCHAR sizes.

A single Iceberg table can have more than one metadata file, each describing a different version of the table. You can create an external table using either the base location of the table or a specific metadata file.

If a metadata file specifies columns or struct fields that are not present in the data, Vertica treats the missing values as NULL.

All Iceberg files, both data and metadata, must be accessible to all database nodes.

Iceberg can store data in several file formats. Vertica can read Iceberg data in the Parquet format only and with either version 1 or version 2 metadata. If the Parquet files encode field IDs, Vertica uses them directly. Otherwise, Vertica uses Iceberg fallback name mapping to read field IDs from the metadata (the schema.name-mapping.default property). If the metadata does not contain a mapping for a field, Vertica sets the column values to NULL.

Syntax

CREATE EXTERNAL TABLE [[{namespace. | database. }]schema.]table
   STORED BY ICEBERG LOCATION { path | metadata-file }
   [COLUMN TYPES (column-name type[,...])] ;

Arguments

{ database | namespace }

Name of the database or namespace that contains table:

• Database name: If specified, it must be the current database.

• Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

schema

Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.

If you specify a schema name that contains a period, the part before the period cannot be the same as the name of an existing namespace.

table

Name of the table to create, which must be unique among names of all sequences, tables, projections, views, and models within the schema.

STORED BY ICEBERG LOCATION { path|metadata-file }

Location of Iceberg data, one of:

• Base location of an Iceberg File System table. Vertica uses the latest metadata file.

• Path to a metadata file with a name ending in .metadata.json. Vertica uses this metadata file even if it is not the latest.

On S3, an Iceberg table is not a File System table but a metastore. This means you cannot specify a base location on S3. You must specify the full path to a metadata file.

If the paths embedded in the Iceberg data are not accessible to Vertica, use the IcebergPathMapping configuration parameter to provide mappings. See Path Prefixes.

COLUMN TYPES (column-name type[,...])

Column names and types for VARCHAR, VARBINARY, or ARRAY columns or ROW fields only. You can specify types only to set lengths or array bounds, not type coercion. See Data Types. If you do not specify a type, the table uses the Vertica defaults.

You cannot specify any other column properties, such as defaults or constraints.

Columns that are specified but not found in the Iceberg schema are ignored.

Privileges

Superuser, or non-superuser with the following privileges:

• READ privileges on the USER-accessible storage location. See GRANT (storage location)

• Full access (including SELECT) to an external table that the user has privileges to create

Path prefixes

Iceberg tables store file paths in the metadata as absolute URIs (host and port). Sometimes this URI differs from the URI that Vertica can use to access the data. This can particularly be an issue for files stored on HDFS, where the metadata can use a different URI scheme and port number than what Vertica expects.

To change the URIs, set the IcebergPathMapping configuration parameter. The value is a list of one or more pairs of Iceberg URI prefixes and corresponding Vertica prefixes:

=> ALTER SESSION SET IcebergPathMapping=
   '{"hdfs://node-196.example.com:9000":"webhdfs://node-196.example.com:9870"}';

Include only the URI prefix (up through the port), not complete paths. If IcebergPathMapping contains more than one mapping that could apply, Vertica uses the longest entry that matches.

You can set IcebergPathMapping at the database, session, or user level.

Data types

The following table shows the mappings of Iceberg data types to Vertica data types. For types that allow it, you can use the COLUMN TYPES clause to override these defaults.

Restrictions

The following restrictions apply to external tables backed by Iceberg:

• Data files must be in Parquet format.

• Metadata files must use version 1 or version 2 format.

• Iceberg column defaults are not supported.

• Iceberg delete files are not supported.

• Malformed data is an error that aborts the load. You cannot reject bad data and continue.

• VARCHAR values are not truncated. If a string is too long, it is treated as an error.

The following restrictions apply to queries of Iceberg tables:

• You cannot use a column in an Iceberg table as a DEFAULT or SET USING option in another table. The following example is an error:

  => CREATE TABLE t(
      id INT DEFAULT (SELECT COUNT(*) FROM iceberg_table));
  ERROR 0:  Default and set using expressions cannot refer to external iceberg tables
  

• Errors in Iceberg data or metadata, such as missing files or type mismatches, can manifest as query errors such as the following:

  => SELECT * FROM iceberg_table;
  ERROR 0:  Problem reading metadata for table iceberg_table. Detail: Could not determine type of column a. User specified type: int. Iceberg type: boolean
  

Examples

The following example creates a table based on the Parquet data files with no overrides:

=> CREATE EXTERNAL TABLE sales
   STORED BY ICEBERG LOCATION 's3:/sales/*';

In the following example, the data uses a struct for the shipping address, with fields for street address (string), city (string), and zip code (integer). The following table definition overrides the default VARCHAR lengths. Note that the zip code is not included in COLUMN TYPES overrides. The ROW column contains only the fields being changed, but all fields including the zip code are part of the table definition and are included in query results:

=> CREATE EXTERNAL TABLE sales
   STORED BY ICEBERG LOCATION 's3:/sales/*'
   COLUMN TYPES (address ROW(street VARCHAR(50), city VARCHAR(50)));

See also

EXTERNAL_DATA_FILES_PRUNED event in QUERY_EVENTS.

9 - CREATE FAULT GROUP

Enterprise Mode only

Creates a fault group, which can contain the following:

• One or more nodes

• One or more child fault groups

• One or more nodes and one or more child fault groups

CREATE FAULT GROUP creates an empty fault group. Use ALTER FAULT GROUP to add nodes or other fault groups to an existing fault group.

Syntax

CREATE FAULT GROUP name

Parameters

name

The name of the fault group to create, unique among all fault groups, where name conforms to conventions described in Identifiers.

Privileges

Superuser

Examples

The following command creates a fault group called parent0:

=> CREATE FAULT GROUP parent0;
CREATE FAULT GROUP

See also

• FAULT_GROUPS
• CLUSTER_LAYOUT
• Fault groups
• High availability with fault groups

10 - CREATE FLEXIBLE EXTERNAL TABLE AS COPY

CREATE FLEXIBLE EXTERNAL TABLE AS COPY creates a flexible external table. This statement combines statements CREATE FLEXIBLE TABLE and COPY statements, supporting a subset of each statement's parameters.

You can also use user-defined load functions (UDLs) to create external flex tables. For details about creating and using flex tables, see Using Flex Tables.

For details about creating and using flex tables, see Creating flex tables in Using Flex Tables.

Syntax

CREATE FLEX[IBLE] EXTERNAL TABLE [ IF NOT EXISTS ] [[database.]schema.]table-name
   ( [ column-definition[,...] ] )
   [ INCLUDE | EXCLUDE [SCHEMA] PRIVILEGES ]
AS COPY [ ( { column-as-expression | column } [ FILLER datatype ] ]
   FROM {
      'path-to-data' [ ON nodename | ON ANY NODE | ON (nodeset) ] input-format [,...]
      | [ WITH ] UDL-clause[...]
   }
   [ ABORT ON ERROR ]
   [ DELIMITER [ AS ] 'char' ]
   [ ENCLOSED [ BY ] 'char' ]
   [ ENFORCELENGTH ]
   [ ESCAPE [ AS ] 'char' | NO ESCAPE ]
   [ EXCEPTIONS 'path' [ ON nodename ] [,...] ]
   [ NULL [ AS ] 'string' ]
   [ RECORD TERMINATOR 'string' ]
   [ REJECTED DATA 'path' [ ON nodename ][,...] ]
   [ REJECTMAX integer ]
   [ SKIP integer ]
   [ SKIP BYTES integer ]
   [ TRAILING NULLCOLS ]
   [ TRIM 'byte' ]

Parameters

For parameter descriptions, see CREATE TABLE and Parameters.

Privileges

Superuser, or non-superuser with the following privileges:

• READ privileges on the USER-accessible storage location. See GRANT (storage location)

• Full access (including SELECT) to an external table that the user has privileges to create

Examples

To create an external flex table:

=> CREATE flex external table mountains() AS COPY FROM 'home/release/KData/kmm_ountains.json' PARSER fjsonparser();
CREATE TABLE

As with other flex tables, creating an external flex table produces two regular tables: the named table and its associated _keys table. The keys table is not an external table:

=> \dt mountains
                 List of tables
 Schema |   Name    | Kind  |  Owner  | Comment
--------+-----------+-------+---------+---------
 public | mountains | table | release |
(1 row)

You can use the helper function, COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW, to compute keys and create a view for the external table:

=> SELECT compute_flextable_keys_and_build_view ('appLog');

                     compute_flextable_keys_and_build_view
--------------------------------------------------------------------------------------------------
Please see public.appLog_keys for updated keys
The view public.appLog_view is ready for querying
(1 row)

Check the keys from the _keys table for the results of running the helper application:

=> SELECT * FROM appLog_keys;
                          key_name                       | frequency |   data_type_guess
----------------------------------------------------------+-----------+------------------
contributors                                             |         8 | varchar(20)
coordinates                                              |         8 | varchar(20)
created_at                                               |         8 | varchar(60)
entities.hashtags                                        |         8 | long varbinary(186)
.
.
.
retweeted_status.user.time_zone                          |         1 | varchar(20)
retweeted_status.user.url                                |         1 | varchar(68)
retweeted_status.user.utc_offset                         |         1 | varchar(20)
retweeted_status.user.verified                           |         1 | varchar(20)
(125 rows)

You can query the view:

=> SELECT "user.lang" FROM appLog_view;
 user.lang
-----------
it
en
es
en
en
es
tr
en
(12 rows)

See also

• CREATE EXTERNAL TABLE AS COPY

11 - CREATE FLEXIBLE TABLE

Creates a flexible (flex) table in the logical schema.

When you create a flex table, Vertica automatically creates two dependent objects:

• Keys table that is named flex-table-name_keys

• View that is named flex-table-name_view

The flex table requires the keys table and view. Neither of these objects can exist independently of the flex table.

Syntax

Create with column definitions:

CREATE [[ scope ] TEMP[ORARY]] FLEX[IBLE] TABLE [ IF NOT EXISTS ]
   [[database.]schema.]table-name
   ( [ column-definition[,...] [, table-constraint ][,...] ] )
   [ ORDER BY column[,...] ]
   [ segmentation-spec ]
   [ KSAFE [k-num] ]
   [ partition-clause]
   [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
   [ DISK_QUOTA quota ]

Create from another table:

CREATE FLEX[IBLE] TABLE [[database.]schema.] table-name
   [ ( column-name-list ) ]
   [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
   AS  query [ ENCODED BY column-ref-list ]
   [ DISK_QUOTA quota ]

Parameters

For general parameter descriptions, see CREATE TABLE; for parameters specific to temporary flex tables, see CREATE TEMPORARY TABLE and Creating flex tables.

You cannot partition a flex table on any virtual column (key).

Privileges

Non-superuser: CREATE privilege on table schema

Default columns

The CREATE statement can omit specifying any column definitions. CREATE FLEXIBLE TABLE always creates two columns automatically:

__raw__

LONG VARBINARY type column to store unstructured data that you load. By default, this column has a NOT NULL constraint.

__identity__

IDENTITY column that is used for segmentation and sorting when no other column is defined.

Default projections

Vertica automatically creates superprojections for both the flex table and keys tables when you create them.

If you create a flex table with one or more of the ORDER BY, ENCODED BY, SEGMENTED BY, or KSAFE clauses, the clause information is used to create projections. If no clauses are in use, Vertica uses the following defaults:

Examples

The following example creates a flex table named darkdata without specifying any column information. Vertica creates a default superprojection and buddy projection as part of creating the table:

=> CREATE FLEXIBLE TABLE darkdata();
CREATE TABLE
=> \dj darkdata1*
                         List of projections
 Schema |         Name         |  Owner  |       Node       | Comment
--------+----------------------+---------+------------------+---------
 public | darkdata1_b0         | dbadmin |                  |
 public | darkdata1_b1         | dbadmin |                  |
 public | darkdata1_keys_super | dbadmin | v_vmart_node0001 |
 public | darkdata1_keys_super | dbadmin | v_vmart_node0002 |
 public | darkdata1_keys_super | dbadmin | v_vmart_node0003 |
(5 rows)

=> SELECT export_objects('','darkdata1_b0');
CREATE PROJECTION public.darkdata1_b0 /*+basename(darkdata1),createtype(P)*/
(
 __identity__,
 __raw__
)
AS
 SELECT darkdata1.__identity__,
        darkdata1.__raw__
 FROM public.darkdata1
 ORDER BY darkdata1.__identity__
SEGMENTED BY hash(darkdata1.__identity__) ALL NODES OFFSET 0;

SELECT MARK_DESIGN_KSAFE(1);
(1 row)

=> select export_objects('','darkdata1_keys_super');
CREATE PROJECTION public.darkdata1_keys_super /*+basename(darkdata1_keys),createtype(P)*/
(
 key_name,
 frequency,
 data_type_guess
)
AS
 SELECT darkdata1_keys.key_name,
        darkdata1_keys.frequency,
        darkdata1_keys.data_type_guess
 FROM public.darkdata1_keys
 ORDER BY darkdata1_keys.frequency
UNSEGMENTED ALL NODES;

SELECT MARK_DESIGN_KSAFE(1);
(1 row)

The following example creates a table called darkdata1 with one column definition (date_col). The statement specifies the partition by clause to partition the data by year. Vertica creates a default superprojection and buddy projections as part of creating the table:

=> CREATE FLEX TABLE darkdata1 (date_col date NOT NULL) partition by
  extract('year' from date_col);
CREATE TABLE

See also

• Creating flex tables
• CREATE FLEXIBLE EXTERNAL TABLE AS COPY

12 - CREATE FUNCTION statements

Vertica provides CREATE statements for each type of user-defined extension. Each CREATE statement adds a user-defined function to the Vertica catalog:

Vertica also provides CREATE FUNCTION (SQL), which stores SQL expressions as functions that you can invoke in a query.

12.1 - CREATE AGGREGATE FUNCTION

Adds a user-defined aggregate function (UDAF) to the catalog. The library containing the function must have been previously added using CREATE LIBRARY.

CREATE AGGREGATE FUNCTION automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading aggregate functions. When you call the SQL function, Vertica passes the input table to the function to process.

User-defined aggregate functions run in unfenced mode only.

Syntax

CREATE [ OR REPLACE ] AGGREGATE FUNCTION [ IF NOT EXISTS ]
  [[database.]schema.]function AS
  [ LANGUAGE 'language' ]
  NAME 'factory'
  LIBRARY library
  [ NOT FENCED ];

Arguments

OR REPLACE

If a function with the same name and arguments exists, replace it. You can use this to change between fenced and unfenced modes, for example. If you do not use this directive and the function already exists, the CREATE statement returns with a rollback error.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the function to create. This is the name used in SQL invocations of the function. It does not need to match the name of the factory, but it is less confusing if they are the same or similar.

The function name must conform to the restrictions on Identifiers.

LANGUAGE 'language'

The language used to develop this function, currently C++ only (the default).

NAME 'factory'

Name of the factory class that generates the function instance.

LIBRARY library

Name of the shared library that contains the function. This library must have already been loaded by CREATE LIBRARY.

NOT FENCED

Indicates that the function runs in unfenced mode. Aggregate functions cannot be run in fenced mode.

Privileges

Non-superuser:

• CREATE privilege on the function's schema

• USAGE privilege on the function's library

Examples

The following example demonstrates loading a library named AggregateFunctions and then defining functions named ag_avg and ag_cat. The functions are mapped to the AverageFactory and ConcatenateFactory classes in the library:

=> CREATE LIBRARY AggregateFunctions AS '/opt/vertica/sdk/examples/build/AggregateFunctions.so';
CREATE LIBRARY
=> CREATE AGGREGATE FUNCTION ag_avg AS LANGUAGE 'C++' NAME 'AverageFactory'
   library AggregateFunctions;
CREATE AGGREGATE FUNCTION
=> CREATE AGGREGATE FUNCTION ag_cat AS LANGUAGE 'C++' NAME 'ConcatenateFactory'
   library AggregateFunctions;
CREATE AGGREGATE FUNCTION
=> \x
Expanded display is on.
select * from user_functions;
-[ RECORD 1 ]----------+------------------------------------------------------------------
schema_name            | public
function_name          | ag_avg
procedure_type         | User Defined Aggregate
function_return_type   | Numeric
function_argument_type | Numeric
function_definition    | Class 'AverageFactory' in Library 'public.AggregateFunctions'
volatility             |
is_strict              | f
is_fenced              | f
comment                |
-[ RECORD 2 ]----------+------------------------------------------------------------------
schema_name            | public
function_name          | ag_cat
procedure_type         | User Defined Aggregate
function_return_type   | Varchar
function_argument_type | Varchar
function_definition    | Class 'ConcatenateFactory' in Library 'public.AggregateFunctions'
volatility             |
is_strict              | f
is_fenced              | f
comment                |

See also

• DROP AGGREGATE FUNCTION
• USER_FUNCTIONS
• Developing user-defined extensions (UDxs)
• Aggregate functions (UDAFs)

12.2 - CREATE ANALYTIC FUNCTION

Adds a user-defined analytic function (UDAnF) to the catalog. The library containing the function must have been previously added using CREATE LIBRARY.

CREATE ANALYTIC FUNCTION automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading analytic functions. When you call the SQL function, Vertica passes the input table to the function in the library to process.

Syntax

CREATE [ OR REPLACE ] ANALYTIC FUNCTION [ IF NOT EXISTS ]
    [[database.]schema.]function AS
    [ LANGUAGE 'language' ]
    NAME 'factory'
    LIBRARY library
    [ FENCED | NOT FENCED ]

Arguments

OR REPLACE

If a function with the same name and arguments exists, replace it. You can use this to change between fenced and unfenced modes, for example. If you do not use this directive and the function already exists, the CREATE statement returns with a rollback error.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the function to create. This is the name used in SQL invocations of the function. It does not need to match the name of the factory, but it is less confusing if they are the same or similar.

The function name must conform to the restrictions on Identifiers.

LANGUAGE 'language'

Language used to develop this function, one of the following:

• C++ (default)

• Java

NAME 'factory'

Name of the factory class that generates the function instance.

LIBRARY library

Name of the library that contains the function. This library must already be loaded by CREATE LIBRARY.

FENCED | NOT FENCED

Enables or disables fenced mode for this function.

Default: FENCED

Privileges

Non-superuser:

• CREATE privilege on the function's schema

• USAGE privilege on the function's library

Examples

This example creates an analytic function named an_rank based on the factory class named RankFactory in the AnalyticFunctions library:

=> CREATE ANALYTIC FUNCTION an_rank AS LANGUAGE 'C++'
   NAME 'RankFactory' LIBRARY AnalyticFunctions;

See also

12.3 - CREATE FILTER

Adds a user-defined load filter function to the catalog. The library containing the filter function must have been previously added using CREATE LIBRARY.

CREATE FILTER automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading load filter functions. When you call the SQL function, Vertica passes the input table to the function in the library to process.

Syntax

CREATE [ OR REPLACE ] FILTER [ IF NOT EXISTS ]
   [[database.]schema.]function AS
   [ LANGUAGE 'language' ]
   NAME 'factory' LIBRARY library
   [ FENCED | NOT FENCED ]

Arguments

OR REPLACE

If a function with the same name and arguments exists, replace it. You can use this to change between fenced and unfenced modes, for example. If you do not use this directive and the function already exists, the CREATE statement returns with a rollback error.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the function to create. This is the name used in SQL invocations of the function. It does not need to match the name of the factory, but it is less confusing if they are the same or similar.

The function name must conform to the restrictions on Identifiers.

LANGUAGE 'language'

The language used to develop this function, one of the following:

• C++ (default)

• Java

• Python

NAME 'factory'

Name of the factory class that generates the function instance. This is the same name used by the RegisterFactory class.

LIBRARY library

Name of the C++ library shared object file, Python file, or Java Jar file. This library must already have been loaded by CREATE LIBRARY.

FENCED | NOT FENCED

Enables or disables fenced mode for this function.

Default: FENCED

Privileges

Superuser

Examples

The following example demonstrates loading a library named iConverterLib, then defining a filter function named Iconverter that is mapped to the iConverterFactory factory class in the library:

=> CREATE LIBRARY iConverterLib as '/opt/vertica/sdk/examples/build/IconverterLib.so';
CREATE LIBRARY
=> CREATE FILTER Iconverter AS LANGUAGE 'C++' NAME 'IconverterFactory' LIBRARY IconverterLib;
CREATE FILTER FUNCTION
=> \x
Expanded display is on.
=> SELECT * FROM user_functions;
-[ RECORD 1 ]----------+--------------------
schema_name            | public
function_name          | Iconverter
procedure_type         | User Defined Filter
function_return_type   |
function_argument_type |
function_definition    |
volatility             |
is_strict              | f
is_fenced              | f
comment                |

See also

• DROP FILTER
• USER_FUNCTIONS
• User-defined load (UDL)

12.4 - CREATE FUNCTION (scalar)

Adds a user-defined scalar function (UDSF) to the catalog. The library containing the function must have been previously added using CREATE LIBRARY.

A UDSF takes in a single row of data and returns a single value. These functions can be used anywhere a native Vertica function or statement can be used, except CREATE TABLE with its PARTITION BY or any segmentation clause.

CREATE FUNCTION automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading UDxs. When you call the function, Vertica passes the parameters to the function in the library to process.

Syntax

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ]
   [[database.]schema.]function AS
   [ LANGUAGE 'language' ]
   NAME 'factory'
   LIBRARY library
   [ FENCED | NOT FENCED ]

Arguments

OR REPLACE

If a function with the same name and arguments exists, replace it. You can use this to change between fenced and unfenced modes, for example. If you do not use this directive and the function already exists, the CREATE statement returns with a rollback error.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the function to create. This is the name used in SQL invocations of the function. It does not need to match the name of the factory, but it is less confusing if they are the same or similar.

The function name must conform to the restrictions on Identifiers.

LANGUAGE 'language'

Language used to develop this function, one of the following:

• C++ (default)

• Python

• Java

• R

NAME 'factory'

Name of the factory class that generates the function instance.

LIBRARY library

Name of the C++ shared object file, Python file, Java Jar file, or R functions file. This library must already have been loaded by CREATE LIBRARY.

FENCED | NOT FENCED

Enables or disables fenced mode for this function. Functions written in Java and R always run in fenced mode.

Default: FENCED

Privileges

• CREATE privilege on the function's schema

• USAGE privilege on the function's library

Examples

The following example loads a library named ScalarFunctions and then defines a function named Add2ints that is mapped to the Add2intsInfo factory class in the library:

=> CREATE LIBRARY ScalarFunctions AS '/opt/vertica/sdk/examples/build/ScalarFunctions.so';
CREATE LIBRARY
=> CREATE FUNCTION Add2Ints AS LANGUAGE 'C++' NAME 'Add2IntsFactory' LIBRARY ScalarFunctions;
CREATE FUNCTION
=> \x
Expanded display is on.
=> SELECT * FROM USER_FUNCTIONS;

-[ RECORD 1 ]----------+----------------------------------------------------
schema_name            | public
function_name          | Add2Ints
procedure_type         | User Defined Function
function_return_type   | Integer
function_argument_type | Integer, Integer
function_definition    | Class 'Add2IntsFactory' in Library 'public.ScalarFunctions'
volatility             | volatile
is_strict              | f
is_fenced              | t
comment                |

=> \x
Expanded display is off.
=> -- Try a simple call to the function
=> SELECT Add2Ints(23,19);
 Add2Ints
----------
       42
(1 row)

The following example uses a scalar function that returns a ROW:

=> CREATE FUNCTION div_with_rem AS LANGUAGE 'C++' NAME 'DivFactory' LIBRARY ScalarFunctions;

=> SELECT div_with_rem(18,5);
        div_with_rem
------------------------------
 {"quotient":3,"remainder":3}
(1 row)

See also

12.5 - CREATE FUNCTION (SQL)

Stores SQL expressions as functions for use in queries. User-defined SQL functions are useful for executing complex queries and combining Vertica built-in functions. You can call the function in a given query. If multiple SQL functions with the same name and argument types are in the search path, Vertica calls the first match that it finds.

SQL functions do not support complex types for arguments or return values.

SQL functions are flattened in all cases, including DDL.

Syntax

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ]
    [[database.]schema.]function( [ argname argtype[,...] ] )
    RETURN return_type
    AS
    BEGIN
       RETURN expression;
    END;

Arguments

OR REPLACE

If a function of the same name and arguments exists, replace it. If you only change the function arguments, Vertica ignores this option and maintains both functions under the same name.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the function to create, which must conform to the conventions described in Identifiers.

argname argtype[,...]

A comma-delimited list of argument names and their data types. Complex types are not supported.

return_type

The data type that this function returns. Complex types are not supported.

RETURN expression

The SQL function body, which can contain built-in functions, operators, and argument names specified in the CREATE FUNCTION statement. The expression must end with a semicolon.

Note

CREATE FUNCTION allows only one RETURN expression. Return expressions do not support the following:

• FROM, WHERE, GROUP BY, ORDER BY, and LIMIT clauses

• Aggregation, analytics, and meta-functions

Privileges

Non-superuser:

• CREATE privilege on the function's schema

• USAGE privilege on the function's library

Strictness and volatility

Vertica infers the strictness and volatility (stable, immutable, or volatile) of a SQL function from its definition. Vertica then determines the correctness of usage, such as where an immutable function is expected but a volatile function is provided.

SQL functions and views

You can create views on the queries that use SQL functions and then query the views. When you create a view, a SQL function replaces a call to the user-defined function with the function body in a view definition. Therefore, when the body of the user-defined function is replaced, the view should also be replaced.

Examples

See Creating user-defined SQL functions.

See also

• ALTER FUNCTION (scalar)
• DROP FUNCTION
• User-defined SQL functions

12.6 - CREATE PARSER

Adds a user-defined load parser function to the catalog. The library containing the function must have been previously added using CREATE LIBRARY.

CREATE PARSER automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading load parser functions. When you call the SQL function, Vertica passes the input table to the function in the library to process.

Syntax

CREATE [ OR REPLACE ] PARSER [ IF NOT EXISTS ]
   [[database.]schema.]function AS
   [ LANGUAGE 'language' ]
   NAME 'factory'
   LIBRARY library
   [ FENCED | NOT FENCED ]

Arguments

OR REPLACE

If a function with the same name and arguments exists, replace it. You can use this to change between fenced and unfenced modes, for example. If you do not use this directive and the function already exists, the CREATE statement returns with a rollback error.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the function to create. This is the name used in SQL invocations of the function. It does not need to match the name of the factory, but it is less confusing if they are the same or similar.

The function name must conform to the restrictions on Identifiers.

LANGUAGE 'language'

The language used to develop this function, one of the following:

• C++ (default)

• Java

• Python

NAME 'factory'

Name of the factory class that generates the function instance. This is the same name used by the RegisterFactory class.

LIBRARY library

Name of the C++ library shared object file, Python file, or Java Jar file. This library must already have been loaded by CREATE LIBRARY.

FENCED | NOT FENCED

Enables or disables fenced mode for this function.

Default: FENCED

Privileges

Superuser

Examples

The following example demonstrates loading a library named BasicIntegrerParserLib, then defining a parser function named BasicIntegerParser that is mapped to the BasicIntegerParserFactory factory class in the library:

=> CREATE LIBRARY BasicIntegerParserLib as '/opt/vertica/sdk/examples/build/BasicIntegerParser.so';
CREATE LIBRARY
=> CREATE PARSER BasicIntegerParser AS LANGUAGE 'C++' NAME 'BasicIntegerParserFactory' LIBRARY BasicIntegerParserLib;
CREATE PARSER FUNCTION
=> \x
Expanded display is on.
=> SELECT * FROM user_functions;
-[ RECORD 1 ]----------+--------------------
schema_name            | public
function_name          | BasicIntegerParser
procedure_type         | User Defined Parser
function_return_type   |
function_argument_type |
function_definition    |
volatility             |
is_strict              | f
is_fenced              | f
comment                |

See also

• DROP PARSER
• USER_FUNCTIONS
• User-defined load (UDL)

12.7 - CREATE SOURCE

Adds a user-defined load source function to the catalog. The library containing the function must have been previously added using CREATE LIBRARY.

CREATE SOURCE automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading load source functions. When you call the SQL function, Vertica passes the input table to the function in the library to process.

Syntax

CREATE [ OR REPLACE ] SOURCE [ IF NOT EXISTS ]
    [[database.]schema.]function AS
    [ LANGUAGE 'language' ]
    NAME 'factory'
    LIBRARY library
    [ FENCED | NOT FENCED ]

Arguments

OR REPLACE

If a function with the same name and arguments exists, replace it. You can use this to change between fenced and unfenced modes, for example. If you do not use this directive and the function already exists, the CREATE statement returns with a rollback error.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the function to create. This is the name used in SQL invocations of the function. It does not need to match the name of the factory, but it is less confusing if they are the same or similar.

The function name must conform to the restrictions on Identifiers.

LANGUAGE 'language'

Language used to develop this function, one of the following:

• C++ (default)

• Java

NAME 'factory'

Name of the factory class that generates the function instance. This is the same name used by the RegisterFactory class.

LIBRARY library

Name of the C++ library shared object file or Java Jar file. This library must already have been loaded by CREATE LIBRARY.

FENCED | NOT FENCED

Enables or disables fenced mode for this function.

Default: FENCED

Privileges

Superuser

Examples

The following example demonstrates loading a library named curllib, then defining a source function named curl that is mapped to the CurlSourceFactory factory class in the library:

=> CREATE LIBRARY curllib as '/opt/vertica/sdk/examples/build/cURLLib.so';
CREATE LIBRARY
=> CREATE SOURCE curl AS LANGUAGE 'C++' NAME 'CurlSourceFactory' LIBRARY curllib;
CREATE SOURCE
=> \x
Expanded display is on.
=> SELECT * FROM user_functions;
-[ RECORD 1 ]----------+--------------------
schema_name            | public
function_name          | curl
procedure_type         | User Defined Source
function_return_type   |
function_argument_type |
function_definition    |
volatility             |
is_strict              | f
is_fenced              | f
comment                |

See also

• DROP SOURCE
• USER_FUNCTIONS
• User-defined load (UDL)

12.8 - CREATE TRANSFORM FUNCTION

Adds a user-defined transform function (UDTF) to the catalog. The library containing the function must have been previously added using CREATE LIBRARY.

CREATE TRANSFORM FUNCTION automatically determines the function parameters and return value from data supplied by the factory class. Vertica supports overloading transform functions. When you call the SQL function, Vertica passes the input table to the transform function in the library to process.

Syntax

CREATE [ OR REPLACE ] TRANSFORM FUNCTION [ IF NOT EXISTS ]
    [[database.]schema.]function AS
    [ LANGUAGE 'language' ]
    NAME 'factory'
    LIBRARY library
    [ FENCED | NOT FENCED ]

Arguments

OR REPLACE

If a function with the same name and arguments exists, replace it. You can use this to change between fenced and unfenced modes, for example. If you do not use this directive and the function already exists, the CREATE statement returns with a rollback error.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the function to create. This is the name used in SQL invocations of the function. It does not need to match the name of the factory, but it is less confusing if they are the same or similar.

The function name must conform to the restrictions on Identifiers.

LANGUAGE 'language'

The language used to develop this function, one of the following:

• C++ (default)

• Java

• R

• Python

NAME 'factory'

Name of the factory class that generates the function instance.

LIBRARY library

Name of the C++ shared object file, Python file, Java Jar file, or R functions file. This library must already have been loaded by CREATE LIBRARY.

FENCED | NOT FENCED

Enables or disables fenced mode for this function. Functions written in Java and R always run in fenced mode.

Default: FENCED

Privileges

Non-superuser:

• CREATE privilege on the function's schema

• USAGE privilege on the function's library

Restrictions

A query that includes a UDTF cannot:

• Include statements other than the SELECT statement that calls the UDTF and a PARTITION BY expression unless the UDTF is marked as a one-to-many UDTF

• Call an analytic function

• Call another UDTF

• Include one of the following clauses:

  • TIMESERIES

  • Pattern matching

  • Gap filling and interpolation

Examples

The following example loads a library named TransformFunctions and then defines a function named tokenize that is mapped to the TokenFactory factory class in the library:

=> CREATE LIBRARY TransformFunctions AS
   '/home/dbadmin/TransformFunctions.so';
CREATE LIBRARY
=> CREATE TRANSFORM FUNCTION tokenize
   AS LANGUAGE 'C++' NAME 'TokenFactory' LIBRARY TransformFunctions;
CREATE TRANSFORM FUNCTION

See also

• DROP FUNCTION
• Developing user-defined extensions (UDxs)

13 - CREATE HCATALOG SCHEMA

Define a schema for data stored in a Hive data warehouse using the HCatalog Connector. For more information, see Using the HCatalog Connector.

Most of the optional parameters are read out of Hadoop configuration files if available. If you copied the Hadoop configuration files as described in Configuring Vertica for HCatalog, you can omit most parameters. By default this statement uses the values specified in those configuration files. If the configuration files are complete, the following is a valid statement:

=> CREATE HCATALOG SCHEMA hcat;

If a value is not specified in the configuration files and a default is shown in the parameter list, then that default value is used.

Some parameters apply only if you are using HiveServer2 (the default). Others apply only if you are using WebHCat, a legacy Hadoop service. When using HiveServer2, use HIVESERVER2_HOSTNAME to specify the server host. When using WebHCat, use WEBSERVICE_HOSTNAME to specify the server host.

If you need to use WebHCat you must also set the HCatalogConnectorUseHiveServer2 configuration parameter to 0. See Hadoop parameters.

After creating the schema, you can change many (but not all) parameters using ALTER HCATALOG SCHEMA.

Syntax

CREATE HCATALOG SCHEMA [IF NOT EXISTS] schemaName
    [AUTHORIZATION user-id]
    [WITH [param=value [,...] ] ]

Arguments

Parameters

The default values for HCATALOG_CONNECTOR_TIMEOUT, HCATALOG_SLOW_TRANSFER_LIMIT, and HCATALOG_SLOW_TRANSFER_TIME are set by the database configuration parameters HCatConnectionTimeout, HCatSlowTransferLimit, and HCatSlowTransferTime. See Hadoop parameters for more information.

Configuration files

The HCatalog Connector uses the following values from the Hadoop configuration files if you do not override them when creating the schema.

Privileges

The user must be a superuser or be granted all permissions on the database to use this statement.

The user also requires access to Hive data in one of the following ways:

• Have USAGE permissions on hcatalog_schema, if Hive does not use an authorization service (Sentry or Ranger) to manage access.

• Have permission through an authorization service, if Hive uses it to manage access. In this case you must either set EnableHCatImpersonation to 0, to access data as the Vertica principal, or grant users access to the HDFS data. For Sentry, you can use ACL synchronization to manage HDFS access.

• Be the dbadmin user, with or without an authorization service.

Examples

The following example shows how to use CREATE HCATALOG SCHEMA to define a new schema for tables stored in a Hive database and then query the system tables that contain information about those tables:

=> CREATE HCATALOG SCHEMA hcat WITH HOSTNAME='hcathost' PORT=9083
   HCATALOG_SCHEMA='default' HIVESERVER2_HOSTNAME='hs.example.com'
   SSL_CONFIG='/etc/hadoop/conf/ssl-client.xml' HCATALOG_USER='admin';
CREATE SCHEMA
=> \x
Expanded display is on.

=> SELECT * FROM v_catalog.hcatalog_schemata;
-[ RECORD 1 ]----------------+-------------------------------------------
schema_id                    | 45035996273748224
schema_name                  | hcat
schema_owner_id              | 45035996273704962
schema_owner                 | admin
create_time                  | 2017-12-05 14:43:03.353404-05
hostname                     | hcathost
port                         | -1
hiveserver2_hostname         | hs.example.com
webservice_hostname          |
webservice_port              | 50111
webhdfs_address              | hs.example.com:50070
hcatalog_schema_name         | default
ssl_config                   | /etc/hadoop/conf/ssl-client.xml
hcatalog_user_name           | admin
hcatalog_connection_timeout  | -1
hcatalog_slow_transfer_limit | -1
hcatalog_slow_transfer_time  | -1
custom_partitions            | f

=> SELECT * FROM v_catalog.hcatalog_table_list;
-[ RECORD 1 ]------+------------------
table_schema_id    | 45035996273748224
table_schema       | hcat
hcatalog_schema    | default
table_name         | nation
hcatalog_user_name | admin
-[ RECORD 2 ]------+------------------
table_schema_id    | 45035996273748224
table_schema       | hcat
hcatalog_schema    | default
table_name         | raw
hcatalog_user_name | admin
-[ RECORD 3 ]------+------------------
table_schema_id    | 45035996273748224
table_schema       | hcat
hcatalog_schema    | default
table_name         | raw_rcfile
hcatalog_user_name | admin
-[ RECORD 4 ]------+------------------
table_schema_id    | 45035996273748224
table_schema       | hcat
hcatalog_schema    | default
table_name         | raw_sequence
hcatalog_user_name | admin

The following example shows how to specify more than one metastore host.

=> CREATE HCATALOG SCHEMA hcat
   WITH HOSTNAME='thrift://node1.example.com:9083,thrift://node2.example.com:9083';

The following example shows how to include custom partition locations:

=> CREATE HCATALOG SCHEMA hcat WITH HCATALOG_SCHEMA='default'
    HIVESERVER2_HOSTNAME='hs.example.com'
    CUSTOM_PARTITIONS='yes';

14 - CREATE KEY

Creates a private key.

Syntax

CREATE [TEMP[ORARY]] KEY name
       { 'AES' [ PASSWORD 'password' ] | 'RSA' }
       {LENGTH length | AS key_text}

Parameters

TEMPORARY

Create with session scope. The key is stored in memory and is valid only for the current session.

name

The name of the key.

password

Password for the key.

length

Size of the key in bits.

Example: 2048

key_text

The contents of the key to import.

Example:

-----BEGIN RSA PRIVATE KEY-----...ABCD1234...-----END RSA PRIVATE KEY-----

Privileges

Superuser

Examples

See Generating TLS certificates and keys.

See also

• CREATE CERTIFICATE

15 - CREATE LIBRARY

Loads a library containing user-defined extensions (UDxs) into the Vertica catalog. Vertica automatically distributes copies of the library file and supporting libraries to all cluster nodes.

Because libraries are added to the database catalog, they persist across database restarts.

After loading a library in the catalog, you can use statements such as CREATE FUNCTION to define the extensions contained in the library. See Developing user-defined extensions (UDxs) for details.

Syntax

CREATE [OR REPLACE] LIBRARY
    [[database.]schema.]name
    AS 'path'
    [ DEPENDS 'depends-path' ]
    [ LANGUAGE 'language' ]

Arguments

OR REPLACE

If a library with the same name exists, replace it. UDxs defined in the catalog that reference the updated library automatically start using the new library file.

If you do not use this directive and the library already exists, the CREATE statement returns with an error.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

name

Name of the library to create. This is the name used when creating functions in the library (see Creating UDx Functions). While not required, it is good practice to match the file name.

AS path

Path of the library to load, either an absolute path on the initiator node file system or a URI for another supported file system or object store.

DEPENDS 'depends-path'

Files or libraries on which this library depends, one or more files or directories on the initiator node file system or other supported file systems or object stores. For a directory, end the path entry with a slash (/), optionally followed by a wildcard (*). To specify more than one file, separate entries with colons (:).

If any path entry contain colons, such as a URI, place brackets around the entire DEPENDS path and use double quotes for the individual path elements, as in the following example:

DEPENDS '["s3://mybucket/gson-2.3.1.jar"]'

To specify libraries with multiple directory levels, see Multi-level Library Dependencies.

DEPENDS has no effect for libraries written in R. R packages must be installed locally on each node, including external dependencies.

Important

The performance of CREATE LIBRARY can degrade in Eon Mode, in proportion to the number and depth of dependencies specified by the DEPENDS clause.

If a Java library depends on native libraries (SO files), use DEPENDS to specify the path and call System.loadLibrary() in your UDx to load the native libraries from that path.

LANGUAGE 'language'

The programming language of the functions in the library, one of:

• C++ (default)

• Python

• Java

• R

Privileges

Superuser, or UDXDEVELOPER and CREATE on the schema. Non-superusers must explicitly enable the UDXDEVELOPER role, as in the following example:

=> SET ROLE UDXDEVELOPER;
SET

-- Not required, but you can confirm the role as follows:
=> SHOW ENABLED ROLES;
     name      |   setting
---------------+--------------
 enabled roles | udxdeveloper
(1 row)

=> CREATE LIBRARY MyLib AS '/home/dbadmin/my_lib.so';
CREATE LIBRARY

-- Create functions...

-- UDXDEVELOPER also grants DROP (replace):
=> CREATE OR REPLACE LIBRARY MyLib AS '/home/dbadmin/my_lib.so';

Requirements

• Vertica makes its own copies of the library files. Later modification or deletion of the original files specified in the statement does not affect the library defined in the catalog. To update the library, use ALTER LIBRARY.

• Loading a library does not guarantee that it functions correctly. CREATE LIBRARY performs some basic checks on the library file to verify it is compatible with Vertica. The statement fails if it detects that the library was not correctly compiled or it finds other basic incompatibilities. However, CREATE LIBRARY cannot detect many other issues in shared libraries.

Multi-level library dependencies

If a DEPENDS clause specifies a library with multiple directory levels, Vertica follows the library path to include all subdirectories of that library. For example, the following CREATE LIBRARY statement enables the UDx library mylib to import all Python packages and modules that it finds in subdirectories of site-packages:

=> CREATE LIBRARY mylib AS '/path/to/python_udx' DEPENDS '/path/to/python/site-packages' LANGUAGE 'Python';

Examples

Load a library in the home directory of the dbadmin account:

=> CREATE LIBRARY MyFunctions AS '/home/dbadmin/my_functions.so';

Load a library located in the directory where you started vsql:

=> \set libfile '\''`pwd`'/MyOtherFunctions.so\'';
=> CREATE LIBRARY MyOtherFunctions AS :libfile;

Load a library from the cloud:

=> CREATE LIBRARY SomeFunctions AS 'S3://mybucket/extensions.so';

Load a library that depends on multiple JAR files in the same directory:

=> CREATE LIBRARY DeleteVowelsLib AS '/home/dbadmin/JavaLib.jar'
   DEPENDS '/home/dbadmin/mylibs/*' LANGUAGE 'Java';

Load a library with multiple explicit dependencies:

=> CREATE LIBRARY mylib AS '/path/to/java_udx'
   DEPENDS '/path/to/jars/this.jar:/path/to/jars/that.jar' LANGUAGE 'Java';

Load a library with dependencies in the cloud:

=> CREATE LIBRARY s3lib AS 's3://mybucket/UdlLib.jar'
   DEPENDS '["s3://mybucket/gson-2.3.1.jar"]' LANGUAGE 'Java';

16 - CREATE LOAD BALANCE GROUP

Creates a group of network addresses that can be targeted by a load balancing routing rule. You create a group either using a list of network addresses, or basing it on one or more fault groups or subclusters.

Syntax

CREATE LOAD BALANCE GROUP group_name WITH {
      ADDRESS address[,...]
    | FAULT GROUP  fault_group[,...] FILTER 'IP_range'
    | SUBCLUSTER subcluster[,...] FILTER 'IP_range'
    }
    [ POLICY 'policy_setting' ]

Parameters

group_name

Name of the group to create. You use this name later when defining load balancing rules.

address[,...]

Comma-delimited list of network addresses you created earlier.

fault_group[,...]

Comma-delimited list of fault groups to use as the basis of the load balance group.

Note

Before you create your load balance group from a fault group, you must create network addresses on the nodes you want in your load balance group. Load balance groups only work with the network addresses you define on nodes, rather than IP addresses. See CREATE NETWORK ADDRESS.

subcluster[,...]

Comma-delimited list of subclusters to use as the basis of the load balance group.

Note

As with fault groups, you must create network addresses on the nodes in the subcluster you want to be part of the load balance group.

IP_range

Range of IP addresses in CIDR notation to include in the load balance group from the fault groups or subclusters. This range can be either IPv4 or IPv6. Only nodes that have a network address with an IP address that falls within this range are added to the load balancing group.

policy_setting

Determines how the initially-contacted node chooses a target from the group, one of the following:

• ROUNDROBIN (default) rotates among the available members of the load balancing group. The initially-contacted node keeps track of which node it chose last time, and chooses the next one in the cluster.

  Note

  Each node in the cluster maintains its own round-robin pointer that indicates which node it should pick next for each load-balancing group. Therefore, if clients connect to different initial nodes, they may be redirected to the same node.

• RANDOM chooses an available node from the group randomly.

• NONE disables load balancing.

Privileges

Examples

The following statement demonstrates creating a load balance group that contains several network addresses:

=> CREATE NETWORK ADDRESS addr01 ON v_vmart_node0001 WITH '10.20.110.21';
CREATE NETWORK ADDRESS
=> CREATE NETWORK ADDRESS addr02 ON v_vmart_node0002 WITH '10.20.110.22';
CREATE NETWORK ADDRESS
=> CREATE NETWORK ADDRESS addr03 on v_vmart_node0003 WITH '10.20.110.23';
CREATE NETWORK ADDRESS
=> CREATE NETWORK ADDRESS addr04 on v_vmart_node0004 WITH '10.20.110.24';
CREATE NETWORK ADDRESS
=> CREATE LOAD BALANCE GROUP group_1 WITH ADDRESS addr01, addr02;
CREATE LOAD BALANCE GROUP
=> CREATE LOAD BALANCE GROUP group_2 WITH ADDRESS addr03, addr04;
CREATE LOAD BALANCE GROUP

=> SELECT * FROM LOAD_BALANCE_GROUPS;
    name    |   policy   |     filter      |         type          | object_name
------------+------------+-----------------+-----------------------+-------------
 group_1    | ROUNDROBIN |                 | Network Address Group | addr01
 group_1    | ROUNDROBIN |                 | Network Address Group | addr02
 group_2    | ROUNDROBIN |                 | Network Address Group | addr03
 group_2    | ROUNDROBIN |                 | Network Address Group | addr04
(4 rows)

This example demonstrates creating a load balancing group using a fault group:

=> CREATE FAULT GROUP fault_1;
CREATE FAULT GROUP
=> ALTER FAULT GROUP fault_1 ADD NODE  v_vmart_node0001;
ALTER FAULT GROUP
=> ALTER FAULT GROUP fault_1 ADD NODE  v_vmart_node0002;
ALTER FAULT GROUP
=> ALTER FAULT GROUP fault_1 ADD NODE  v_vmart_node0003;
ALTER FAULT GROUP
=> ALTER FAULT GROUP fault_1 ADD NODE  v_vmart_node0004;
ALTER FAULT GROUP
=> SELECT node_name,node_address,node_address_family,export_address
   FROM v_catalog.nodes;
    node_name     | node_address | node_address_family | export_address
------------------+--------------+---------------------+----------------
 v_vmart_node0001 | 10.20.110.21 | ipv4                | 10.20.110.21
 v_vmart_node0002 | 10.20.110.22 | ipv4                | 10.20.110.22
 v_vmart_node0003 | 10.20.110.23 | ipv4                | 10.20.110.23
 v_vmart_node0004 | 10.20.110.24 | ipv4                | 10.20.110.24
(4 rows)

=> CREATE LOAD BALANCE GROUP group_all WITH FAULT GROUP fault_1 FILTER
   '0.0.0.0/0';
CREATE LOAD BALANCE GROUP

=> CREATE LOAD BALANCE GROUP group_some WITH FAULT GROUP fault_1 FILTER
   '10.20.110.21/30';
CREATE LOAD BALANCE GROUP

=> SELECT * FROM LOAD_BALANCE_GROUPS;
      name      |   policy   |     filter      |         type          | object_name
----------------+------------+-----------------+-----------------------+-------------
 group_all      | ROUNDROBIN | 0.0.0.0/0       | Fault Group           | fault_1
 group_some     | ROUNDROBIN | 10.20.110.21/30 | Fault Group           | fault_1
(2 rows)

See also

• ALTER LOAD BALANCE GROUP
• ALTER NETWORK ADDRESS
• ALTER ROUTING RULE
• LOAD_BALANCE_GROUPS
• NETWORK_ADDRESSES

17 - CREATE LOCAL TEMPORARY VIEW

Creates or replaces a local temporary view. Views are read only, so they do not support insert, update, delete, or copy operations. Local temporary views are session-scoped, so they are visible only to their creator in the current session. Vertica drops the view when the session ends.

Syntax

CREATE [OR REPLACE] LOCAL TEMP[ORARY] VIEW view [ (column[,...] ) ] AS query

Parameters

OR REPLACE

Specifies to overwrite the existing view view-name. If you omit this option and view-name already exists, CREATE VIEW returns an error.

view

Identifies the view to create, where view conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

column[,...]

List of up to 9800 names to use as view column names. Vertica maps view column names to query columns according to the order of their respective lists. By default, the view uses column names as they are specified in the query.

AS query

A SELECT statement that the temporary view executes. The SELECT statement can reference tables, temporary tables, and other views.

Privileges

See Creating views.

Examples

The following CREATE LOCAL TEMPORARY VIEW statement creates the temporary view myview. This view sums all individual incomes of customers listed in the store.store_sales_fact table, and groups results by state:

=> CREATE LOCAL TEMP VIEW myview AS
   SELECT SUM(annual_income), customer_state FROM public.customer_dimension
     WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact)
     GROUP BY customer_state
     ORDER BY customer_state ASC;

The following example uses the temporary view myview with a WHERE clause that limits the results to combined salaries greater than $2 billion:

=> SELECT * FROM myview WHERE SUM > 2000000000;


     SUM     | customer_state
-------------+----------------
  2723441590 | AZ
 29253817091 | CA
  4907216137 | CO
  3769455689 | CT
  3330524215 | FL
  4581840709 | IL
  3310667307 | IN
  2793284639 | MA
  5225333668 | MI
  2128169759 | NV
  2806150503 | PA
  2832710696 | TN
 14215397659 | TX
  2642551509 | UT
(14 rows)

See also

• ALTER VIEW
• CREATE VIEW
• Creating views

18 - CREATE LOCATION

Creates a storage location where Vertica can store data. After you create the location, you create storage policies that assign the storage location to the database objects that will store data in the location.

Syntax

CREATE LOCATION 'path'
    [NODE 'node' | ALL NODES]
    [ SHARED ]
    [ COMMUNAL ]
    [ USAGE 'usage']
    [LABEL 'label']
    [LIMIT 'size']

Arguments

path

Where to store this location's data. The type of file system on which the location is based determines the path format:

• Linux: Absolute path to the directory where Vertica can write the storage location's data.
• Shared file systems: See the URL specifications in HDFS file system, S3 object store, Google Cloud Storage (GCS) object store, and Azure Blob Storage object store.

HDFS storage locations have additional requirements.

Important

Vertica performs no validation on storage location paths. Confirm that the path value points to a valid location.

ALL NODES | NODE 'node'

The node or nodes on which the storage location is defined, one of the following:

• ALL NODES (default): Create the storage location on each node that is currently part of the cluster. If you later add nodes, you must also create the location on those nodes. If SHARED is also specified, create the storage location once for use by all nodes.
• NODE 'node': Create the storage location on a single node, where node is the name of the node in the NODES system table. You cannot use this option with SHARED.

SHARED

Indicates the location set by path is shared (used by all nodes) rather than local to each node. You cannot specify individual nodes with SHARED; you must use ALL NODES.

Most remote file systems such as HDFS and S3 are shared. For these file systems, the path argument represents a single location in the remote file system where all nodes store data. If using a remote file system, you must specify SHARED, even for one-node clusters.

If path is set to S3 communal storage, SHARED is always implied and can be omitted.

Deprecated

SHARED DATA and SHARED DATA,TEMP storage locations are deprecated.

COMMUNAL

The location set by path is to be a communal storage location.

If you specify COMMUNAL, you do not need to provide the SHARED, ALL NODES, or NODE options because the storage location is necessarily shared by all nodes in the database. For the USAGE option, you must specify a value of DATA.

After the communal storage location is created, you can assign objects to it using the SET_OBJECT_STORAGE_POLICY function.

USAGE 'usage'

The type of data the storage location can hold, where usage is one of the following:

• DATA,TEMP (default): The storage location can store persistent and temporary DML-generated data, and data for temporary tables.

• TEMP: A path-specified location to store DML-generated temporary data. If path is set to S3, then this location is used only when the RemoteStorageForTemp configuration parameter is set to 1, and TEMP must be qualified with ALL NODES SHARED. For details, see S3 Storage of Temporary Data.

• DATA: The storage location can only store persistent data.

• USER: Users with READ and WRITE privileges can access data and external tables of this storage location.

• DEPOT: The storage location is used in Eon Mode to store the depot. Only create DEPOT storage locations on local Linux file systems.

  Vertica allows a single DEPOT storage location per node. If you want to move your depot to different location (on a different file system, for example) you must first drop the old depot storage location, then create the new location.

LABEL 'label'

A label for the storage location, used when assigning the storage location to data objects. You use this name later when assigning the storage location to data objects.

Important

You must supply a label for depot storage locations.

LIMIT 'size'

Valid only if the storage location usage type is set to DEPOT, specifies the maximum amount of disk space that the depot can allocate from the storage location's file system.

You can specify size in two ways:

• integer%: Percentage of storage location disk size.

• integer{K|M|G|T}: Amount of storage location disk size in kilobytes, megabytes, gigabytes, or terabytes.

Important

The depot size cannot exceed 80 percent of the file system disk space where the depot is stored. If you specify a value that is too large, Vertica issues a warning and automatically changes the value to 80 percent of the file system size.

If you do not specify a limit, it is set to 60 percent.

Privileges

Superuser

File system access

The Vertica process must have read and write permissions to the location where data is to be stored. Each file system has its own requirements:

Examples

Create a storage location in the local Linux file system for temporary data storage:

=> CREATE LOCATION '/home/dbadmin/testloc' USAGE 'TEMP' LABEL 'tempfiles';

Create a storage location on HDFS. The HDFS cluster does not use Kerberos:

=> CREATE LOCATION 'hdfs://hadoopNS/vertica/colddata' ALL NODES SHARED
   USAGE 'data' LABEL 'coldstorage';

Create the same storage location, but on a Hadoop cluster that uses Kerberos. Note the output that reports the principal being used:

=> CREATE LOCATION 'hdfs://hadoopNS/vertica/colddata' ALL NODES SHARED
   USAGE 'data' LABEL 'coldstorage';
NOTICE 0: Performing HDFS operations using kerberos principal [vertica/hadoop.example.com]
CREATE LOCATION

Create a location for user data, grant access to it, and use it to create an external table:

=> CREATE LOCATION '/tmp' ALL NODES USAGE 'user';
CREATE LOCATION
=> GRANT ALL ON LOCATION '/tmp' to Bob;
GRANT PRIVILEGE
=> CREATE EXTERNAL TABLE ext1 (x integer) AS COPY FROM '/tmp/data/ext1.dat' DELIMITER ',';
CREATE TABLE

Create a user storage location on S3 and a role, so that users without their own S3 credentials can read data from S3 using the server credential:

   --- set database-level credential (once):
=> ALTER DATABASE DEFAULT SET AWSAuth = 'myaccesskeyid123456:mysecretaccesskey123456789012345678901234';

=> CREATE LOCATION 's3://datalake' SHARED USAGE 'USER' LABEL 's3user';

=> CREATE ROLE ExtUsers;
   --- Assign users to this role using GRANT (Role).

=> GRANT READ ON LOCATION 's3://datalake' TO ExtUsers;

Create a communal storage location on S3 with the label cold-storage:

=> CREATE LOCATION 's3://bucket/s3' COMMUNAL USAGE 'DATA' LABEL 'cold-storage';

See also

• Managing storage locations
• Using HDFS storage locations
• ALTER_LOCATION_LABEL
• ALTER_LOCATION_USE
• RETIRE_LOCATION
• SET_OBJECT_STORAGE_POLICY

19 - CREATE NAMESPACE

Eon Mode only

Creates a namespace for an Eon Mode database.

When you create a database, a namespace named default_namespace is created with the shard count defined during database creation. This function lets you create additional namespaces. Each namespace has a distinct shard count that determines the segmentation of its member objects. For more information, see Managing namespaces and Namespaces and shards.

Every schema and table in an Eon Mode database belongs to a namespace. When you create a table or schema, you optionally specify the namespace under which to create the object. If no namespace is specified, the table or schema is created in default_namespace.

Syntax

CREATE NAMESPACE namespace [ SHARD COUNT shards ]

Arguments

namespace

Name of the namespace. Cannot be the same as the initial substring of any schema name containing a period. For example, if a schema named prefix.s exists, you cannot create a namespace named prefix.

SHARD COUNT shards

Shard count of the namespace. If unspecified, the shard count is set to that of default_namespace.

Privileges

Superuser

Examples

Create the analytics namespace with a shard count of 12:

=> CREATE NAMESPACE analytics SHARD COUNT 12;
CREATE NAMESPACE

You can view all namespaces in your database by querying the NAMESPACES system table:

=> SELECT namespace_name, is_default, default_shard_count FROM NAMESPACES;
  namespace_name   | is_default | default_shard_count
-------------------+------------+---------------------
 default_namespace | t          |                   6
 analytics         | f          |                   12
(2 rows)

For a more in-depth example, see Managing namespaces.

See also

• DROP NAMESPACE

20 - CREATE NETWORK ADDRESS

Creates a network address that can be used as part of a connection load balancing policy. A network address creates a name in the Vertica catalog for an IP address and port number associated with a node. Nodes can have multiple network addresses, up to one for each IP address they have on the network.

Syntax

CREATE NETWORK ADDRESS name ON node WITH 'ip-address' [PORT port-number] [ENABLED | DISABLED]

Parameters

name

The name of the new network address. Use this name when creating connection load balancing groups.

node

The name of the node on which to create the network address. This should be name of the node as it appears in the node_name column of system table NODES.

ip-address

The IPv4 or and IPv6 address on the node to associate with the network address.

Note

Vertica does not verify that the IP address you supply in this parameter is actually associated with the specified node. Be sure that the IP address actually belongs to the node. Otherwise, your load balancing policy is liable to send a client connection to the wrong node, or a non-Vertica host. Vertica rejects IP address that are invalid for a node. For example, it checks whether the IP address falls in the loopback address range of 127.0.0.0/8. If it finds that the IP address is invalid, CREATE NETWORK ADDRESS returns an error.

PORT port-number

Sets the port number for the network address. You must supply a network address when altering the port number.

ENABLED | DISABLED

Enables or disables the network address.

Privileges

Superuser

Examples

Create three network addresses, one for each node in a three-node cluster:

=> SELECT node_name,export_address from v_catalog.nodes;
      node_name      | export_address
---------------------+----------------
 v_vmart_br_node0001 | 10.20.100.62
 v_vmart_br_node0002 | 10.20.100.63
 v_vmart_br_node0003 | 10.20.100.64
(3 rows)

=> CREATE NETWORK ADDRESS node01 ON v_vmart_br_node0001 WITH '10.20.100.62';
CREATE NETWORK ADDRESS
=> CREATE NETWORK ADDRESS node02 ON v_vmart_br_node0002 WITH '10.20.100.63';
CREATE NETWORK ADDRESS
=> CREATE NETWORK ADDRESS node03 ON v_vmart_br_node0003 WITH '10.20.100.64';

See also

21 - CREATE NETWORK INTERFACE

Identifies a network interface to which a node belongs.

Use this statement when you want to configure import/export operations from individual nodes to other Vertica clusters. By default, when you install Vertica, it creates interfaces for all connected networks. You would only need CREATE NETWORK INTERFACE in situations where the network topology has changed since you installed Vertica.

Syntax

CREATE NETWORK INTERFACE network-interface-name ON node-name [WITH] 'node-IP-address' [PORT port-number] [ENABLED | DISABLED]

network-interface-name

The name you assign to the network interface, where network-interface-name conforms to conventions described in Identifiers.

node-name

The name of the node.

node-IP-address

The node's IP address, either a public or private IP address. For more information, see Using Public and Private IP Networks.

PORT port-number

Sets the port number for the network interface. You must supply a network interface when altering the port number.

[ENABLED | DISABLED]

Enables or disables the network interface.

Privileges

Superuser

Examples

Create a network interface:

=> CREATE NETWORK INTERFACE mynetwork ON v_vmart_node0001 WITH '123.4.5.6' PORT 456 ENABLED;

22 - CREATE NOTIFIER

Creates a push-based notifier to send event notifications and messages out of Vertica.

Syntax

CREATE NOTIFIER [ IF NOT EXISTS ] notifier-name ACTION 'notifier-type'
    [ ENABLE | DISABLE ]
    [ MAXPAYLOAD 'integer{K|M}' ]
    MAXMEMORYSIZE 'integer{K|M|G|T}'
    [ TLS CONFIGURATION tls-configuration ]
    [ TLSMODE 'tls-mode' ]
    [ CA BUNDLE bundle-name [ CERTIFICATE certificate-name ] ]
    [ IDENTIFIED BY 'uuid' ]
    [ [NO] CHECK COMMITTED ]
    [ PARAMETERS 'adapter-params' ]

Parameters

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

notifier-name

This notifier's unique identifier.

ACTION 'notifier-type'

String, the type of notifier, one of the following:

• URL, with the following format, that identifies one or more target Kafka servers:

  kafka://kafka-server-ip-address:port-number
  

  To enable failover when a Kafka server is unavailable, specify additional hosts in a comma-delimited list. For example:

  kafka://192.0.2.0:9092,192.0.2.1:9092,192.0.2.2:9092
  

• syslog: Notifications are sent to syslog. To use notifiers of this type, you must set the SyslogEnabled parameter:

  => ALTER DATABASE DEFAULT SET SyslogEnabled = 1
  

  Events monitored by this notifier type are not logged to MONITORING_EVENTS nor vertica.log.

• sns: Notifications are sent to a Simple Notification Service (SNS) endpoint.

ENABLE | DISABLE

Specifies whether to enable or disable the notifier.

Default: ENABLE.

MAXPAYLOAD 'integer{K|M}'

The maximum size of the message, up to 10^9 bytes, specified in kilobytes or megabytes.

The following restrictions apply:

• MAXPAYLOAD cannot be greater than MAXMEMORYSIZE.

• If you configure syslog to send messages to a remote destination, ensure that MaxMessageSize (in /etc/rsyslog for rsyslog) is greater than or equal to MAXPAYLOAD.

• The MAXPAYLOAD for SNS notifiers cannot exceed 256KB.

Defaults:

• Kafka: 1M

• syslog: 1M

• SNS: 256K

MAXMEMORYSIZE 'integer{K|M|G|T}'

The maximum size of the internal notifier, up to 2 TB, specified in kilobytes, megabytes, gigabytes, or terabytes.

MAXMEMORYSIZE must be greater than MAXPAYLOAD.

If the size of the message queue exceeds MAXMEMORYSIZE, the notifier drops excess messages.

TLS CONFIGURATION tls-configuration

The TLS CONFIGURATION to use for TLS.

Notifiers support the following TLS modes:

• DISABLE

• TRY_VERIFY (behaves like VERIFY_CA)

• VERIFY_CA

• VERIFY_FULL

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

• SNSCAFile or AWSCAFile

• SNSCAPath or AWSCAPath

• SNSEnableHttps

TLSMODE 'tls-mode'

Deprecated

This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.

Specifies the type of connection between the notifier and an endpoint, one of the following:

• disable (default): Plaintext connection.

• verify-ca: Encrypted connection, and the server's certificate is verified as being signed by a trusted CA.

If you set this parameter to verify-ca, the generated TLS Configuration will be set to TRY_VERIFY, which has the same behavior as VERIFY_CA.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

• SNSCAFile or AWSCAFile

• SNSCAPath or AWSCAPath

• SNSEnableHttps

CA BUNDLE bundle-name

Deprecated

This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.

Specifies a CA bundle. The certificates inside the bundle are used to validate the Kafka server's certificate if the TLSMODE requires it.

If a CA bundle is specified for a notifier that currently uses disable, which doesn't validate the Kafka server's certificate, the bundle will go unused when connecting to the Kafka server. This behavior persists unless the TLSMODE is changed to one that validates server certificates.

Changes to contents of the CA bundle take effect either after the notifier is disabled and re-enabled or after the database restarts. However, changes to which CA bundle the notifier uses takes effect immediately.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

• SNSCAFile or AWSCAFile

• SNSCAPath or AWSCAPath

• SNSEnableHttps

CERTIFICATE certificate-name

Deprecated

This parameter has been superseded by the TLS CONFIGURATION parameter. If you use this parameter while the TLS CONFIGURATION parameter is not set, Vertica automatically creates a new TLS Configuration for the notifier uses the same values as the deprecated parameter.

Specifies a client certificate for validation by the endpoint.

If the notifier ACTION is 'syslog' or 'sns', this parameter has no effect.

To encrypt messages sent to syslog, you must configure syslog for TLS.

To encrypt messages sent to an SNS endpoint, you must set the following configuration parameters:

• SNSCAFile or AWSCAFile

• SNSCAPath or AWSCAPath

• SNSEnableHttps

IDENTIFIED BY uuid

Specifies the notifier's unique identifier. If set, all the messages published by this notifier have this attribute.

[NO] CHECK COMMITTED

Specifies to wait for delivery confirmation before sending the next message in the queue.

Some messaging systems, like syslog, do not support delivery confirmation.

For SNS notifiers, CHECK COMMITTED must be specified, and NO CHECK COMMITTED behaves like CHECK COMMITTED.

PARAMETERS 'adapter-params'

Specifies one or more optional adapter parameters that are passed as a string to the adapter. Adapter parameters apply only to the adapter associated with the notifier.

For Kafka notifiers, refer to Kafka and Vertica configuration settings.

For syslog notifiers, specify the severity of the event with eventSeverity=severity, where severity is one of the following:

• 0: Emergency

• 1: Alert

• 2: Critical

• 3: Error

• 4: Warning

• 5: Notice

• 6: Informational

• 7: Debug

Most syslog implementations, by default, do not log events with a severity level of 7. You must configure syslog to record these types of events.

Parameters cannot be set for SNS notifiers.

Privileges

Encrypted notifiers for SASL_SSL Kafka configurations

Follow this procedure to create or alter notifiers for Kafka endpoints that use SASL_SSL. Note that you must repeat this procedure whenever you change the TLSMODE, certificates, or CA bundle for a given notifier.

1. Create a TLS Configuration with the desired TLS mode, certificate, and CA certificates.

2. Use CREATE or ALTER to disable the notifier and set the TLS Configuration:

   => ALTER NOTIFIER encrypted_notifier
       DISABLE
       TLS CONFIGURATION kafka_tls_config;
   

3. ALTER the notifier and set the proper rdkafka adapter parameters for SASL_SSL:

   => ALTER NOTIFIER encrypted_notifier PARAMETERS
       'sasl.username=user;sasl.password=password;sasl.mechanism=PLAIN;security.protocol=SASL_SSL';
   

4. Enable the notifier:

   => ALTER NOTIFIER encrypted_notifier ENABLE;
   

Examples

Kafka notifiers

Create a Kafka notifier:

=> CREATE NOTIFIER my_dc_notifier
    ACTION 'kafka://172.16.20.10:9092'
    MAXMEMORYSIZE '1G'
    IDENTIFIED BY 'f8b0278a-3282-4e1a-9c86-e0f3f042a971'
    NO CHECK COMMITTED;

Create a notifier with an adapter-specific parameter:

=> CREATE NOTIFIER my_notifier
    ACTION 'kafka://127.0.0.1:9092'
    MAXMEMORYSIZE '10M'
    PARAMETERS 'queue.buffering.max.ms=1000';

Create a notifier that uses an encrypted connection and verifies the Kafka server's certificate with the CA certificates in the notifier_tls_config object:

=> CREATE NOTIFIER encrypted_notifier
    ACTION 'kafka://127.0.0.1:9092'
    MAXMEMORYSIZE '10M'
    TLS CONFIGURATION 'notifier_tls_config'

Syslog notifiers

The following example creates a notifier that writes a message to syslog when the Data collector (DC) component LoginFailures updates:

1. Enable syslog notifiers for the current database:

   => ALTER DATABASE DEFAULT SET SyslogEnabled = 1;
   

2. Create and enable a syslog notifier v_syslog_notifier:

   => CREATE NOTIFIER v_syslog_notifier ACTION 'syslog'
       ENABLE
       MAXMEMORYSIZE '10M'
       IDENTIFIED BY 'f8b0278a-3282-4e1a-9c86-e0f3f042a971'
       PARAMETERS 'eventSeverity = 5';
   

3. Configure the syslog notifier v_syslog_notifier for updates to the LoginFailures DC component with SET_DATA_COLLECTOR_NOTIFY_POLICY:

   => SELECT SET_DATA_COLLECTOR_NOTIFY_POLICY('LoginFailures','v_syslog_notifier', 'Login failed!', true);
   

   This notifier writes the following message to syslog (default location: /var/log/messages) when a user fails to authenticate as the user Bob:

   Apr 25 16:04:58
   vertica_host_01
   vertica:
       Event Posted:
           Event Code:21
           Event Id:0
           Event Severity: Notice [5]
           PostedTimestamp: 2022-04-25 16:04:58.083063
           ExpirationTimestamp: 2022-04-25 16:04:58.083063
           EventCodeDescription: Notifier
           ProblemDescription: (Login failed!)
       {
          "_db":"VMart",
          "_schema":"v_internal",
          "_table":"dc_login_failures",
          "_uuid":"f8b0278a-3282-4e1a-9c86-e0f3f042a971",
          "authentication_method":"Reject",
          "client_authentication_name":"default: Reject",
          "client_hostname":"::1",
          "client_label":"",
          "client_os_user_name":"dbadmin",
          "client_pid":523418,
          "client_version":"",
          "database_name":"dbadmin",
          "effective_protocol":"3.8",
          "node_name":"v_vmart_node0001",
          "reason":"REJECT",
          "requested_protocol":"3.8",
          "ssl_client_fingerprint":"",
          "ssl_client_subject":"",
          "time":"2022-04-25 16:04:58.082568-05",
          "user_name":"Bob"
       }#012
       DatabaseName: VMart
       Hostname: vertica_host_01
   

For details on syslog notifiers, see Configuring reporting for syslog.

See also

• ALTER NOTIFIER
• DROP NOTIFIER

• Monitoring Vertica Using Notifiers

23 - CREATE PROCEDURE (external)

Enterprise Mode only

Adds an external procedure to Vertica. See External procedures for more information.

Syntax

CREATE PROCEDURE [ IF NOT EXISTS ]
    [[database.]schema.]procedure( [ argument-list ] )
    AS executable
    LANGUAGE 'EXTERNAL'
    USER OS-user

Parameters

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

This option cannot be used with OR REPLACE.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

procedure

Specifies a name for the external procedure, where procedure-name conforms to conventions described in Identifiers.

argument-list

A comma-delimited list of procedure arguments, where each argument is specified as follows:

[ argname ] argtype

• argname optionally provides a descriptive name for this argument.

• argtype must be one of the following data types supported by Vertica:

  • BIGINT

  • BOOLEAN

  • DECIMAL

  • DOUBLE PRECISION

  • FLOAT

  • FLOAT8

  • INT

  • INT8

  • INTEGER

  • MONEY

  • NUMBER

  • NUMERIC

  • REAL

  • SMALLINT

  • TINYINT

  • VARCHAR

executable

The name of the executable program in the procedures directory, a string.

OS-user

The owner of the file, a string. The owner:

• Cannot be root

• Must have execute privileges on executable

Privileges

Superuser

System security

• The procedure file must be owned by the database administrator (OS account) or by a user in the same group as the administrator. The procedure file must also have the set UID attribute enabled, and allow read and execute permission for the group.

• External procedures that you create with CREATE PROCEDURE (external) are always run with Linux dbadmin privileges. If a dbadmin or pseudosuperuser grants a non-dbadmin permission to run a procedure using GRANT (procedure), be aware that the non-dbadmin user runs the procedure with full Linux dbadmin privileges.

Examples

The following example shows how to create a procedure named helloplanet for the procedure file helloplanet.sh. This file accepts one VARCHAR argument.

Create the file:

#!/bin/bash
echo "hello planet argument: $1" >> /tmp/myprocedure.log

Create the procedure with the following SQL:

=> CREATE PROCEDURE helloplanet(arg1 varchar) AS 'helloplanet.sh' LANGUAGE 'external' USER 'dbadmin';

See also

• DROP PROCEDURE (external)
• Installing external procedure executable files

24 - CREATE PROCEDURE (stored)

Creates a stored procedure.

Syntax

CREATE [ OR REPLACE ] PROCEDURE [ IF NOT EXISTS ]
    [[{namespace. | database. }]schema.]procedure( [ parameter-list ] )
    [ LANGUAGE 'language-name' ]
    [ SECURITY { DEFINER | INVOKER } ]
    AS $$ source $$;

Parameters

OR REPLACE

If a procedure with the same name already exists, replace it. Users and roles with privileges on the original procedure retain these privileges on the new procedure.

This option cannot be used with IF NOT EXISTS.

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

This option cannot be used with OR REPLACE.

{ database | namespace }

Name of the database or namespace that contains table:

• Database name: If specified, it must be the current database.

• Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

schema

Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.

If you specify a schema name that contains a period, the part before the period cannot be the same as the name of an existing namespace.

procedure

The name of the stored procedure, where *procedure-name*conforms to conventions described in Identifiers.

parameter-list

A comma-delimited list of formal parameters, each specified as follows:

[ parameter-mode ] parameter-name parameter-type

• parameter-name: the name of the parameter.

• parameter-type: Any SQL data type, with the following exceptions:

  • DECIMAL

  • NUMERIC

  • NUMBER

  • MONEY

  • UUID

  • GEOGRAPHY

  • GEOMETRY

  • Complex types

language-name

Specifies the language of the procedure source, one of the following (both options refer to PLvSQL; PLpgSQL is included to maintain compatibility with existing scripts):

• PLvSQL

• PLpgSQL

Default: PLvSQL

SECURITY { DEFINER | INVOKER }

Determines whose privileges to use when the procedure is called and executes it as if the user is one of the following:

• DEFINER: User who defined the procedure

• INVOKER: User who called the procedure

A procedure with SECURITY DEFINER effectively executes the procedure as that user, so changes to the database appear to be performed by the procedure's definer rather than its caller.

Caution

Improper use of SECURITY DEFINER can lead to the confused deputy problem and introduce vulnerabilities into your system like SQL injection.

For more information, see Executing stored procedures.

source

The procedure source code. For details, see Scope and structure.

Privileges

Non-superuser: CREATE on the procedure's schema

Examples

For more complex examples, see Stored procedures: use cases and examples

The following procedure echoes its inputs as a result set:

=> CREATE PROCEDURE echo_int_varchar(INOUT x INT, INOUT y VARCHAR) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'This procedure outputs a result set of its inputs:';
END
$$;

=> CALL echo_int_varchar(3, 'a string');
NOTICE 2005:  This procedure outputs a result set of its inputs:
 x |    y
---+----------
 3 | a string
(1 row)

The example uses a set of procedures that convert a given Fahrenheit temperature to Celsius and Kelvin and returns them all as a result set. The procedure f_to_c_and_k() calls the helper procedures f_to_c() and f_to_k() to convert to Celsius and Kelvin, respectively. The f_to_k() procedure uses the output of f_to_c() for part of the conversion:

=> CREATE PROCEDURE f_to_c_and_k(IN f_temp DOUBLE PRECISION, OUT c_temp DOUBLE PRECISION, OUT k_temp DOUBLE PRECISION) AS $$
BEGIN
    c_temp := CALL f_to_c(f_temp);
    k_temp := CALL f_to_k(f_temp);
END;
$$;

=> CREATE PROCEDURE f_to_c(INOUT temp DOUBLE PRECISION) AS $$
BEGIN
    temp := (temp - 32) * 5/9;
END;
$$;

=> CREATE PROCEDURE f_to_k(INOUT temp DOUBLE PRECISION) AS $$
BEGIN
    temp := CALL f_to_c(temp);
    temp := temp + 273.15;
END;
$$;

=> CALL f_to_c_and_k(80);
      c_temp      |      k_temp
------------------+------------------
 26.6666666666667 | 299.816666666667
(1 row)

See also

• PL/vSQL
• CALL
• DO
• DROP PROCEDURE (stored)

25 - CREATE PROFILE

Creates a profile that controls password requirements for users.

Syntax

CREATE PROFILE profile-name LIMIT [
    PASSWORD_LIFE_TIME setting
    PASSWORD_MIN_LIFE_TIME setting
    PASSWORD_GRACE_TIME setting
    FAILED_LOGIN_ATTEMPTS setting
    PASSWORD_LOCK_TIME setting
    PASSWORD_REUSE_MAX setting
    PASSWORD_REUSE_TIME setting
    PASSWORD_MAX_LENGTH setting
    PASSWORD_MIN_LENGTH setting
    PASSWORD_MIN_LETTERS setting
    PASSWORD_MIN_UPPERCASE_LETTERS setting
    PASSWORD_MIN_LOWERCASE_LETTERS setting
    PASSWORD_MIN_DIGITS setting
    PASSWORD_MIN_SYMBOLS setting
    PASSWORD_MIN_CHAR_CHANGE setting ]

Parameters

Privileges

Superuser

Profile settings and client authentication

The following profile settings affect client authentication methods, such as LDAP or GSS:

• FAILED_LOGIN_ATTEMPTS

• PASSWORD_LOCK_TIME

All other profile settings are used only by Vertica to manage its passwords.

Examples

=> CREATE PROFILE sample_profile LIMIT PASSWORD_MAX_LENGTH 20;

See also

• ALTER PROFILE
• DROP PROFILE
• Creating a database name and password
• Profiles

26 - CREATE PROJECTION

Creates metadata for a projection in the Vertica catalog. Vertica supports four types of projections:

• Standard projection: Stores a collection of table data in a format that optimizes execution of certain queries on that table.

• Live aggregate projection: Stores the grouped results of queries that invoke aggregate functions (such as SUM) on table columns.

• Top-K projection: Stores the top k rows from partitions of selected rows.

• UDTF projection: Stores newly-loaded data after it is transformed and/or aggregated by user-defined transformation functions (UDTFs).

Complex data types have additional restrictions when used within a projection:

• Each projection must include at least one column that is a primitive type or native array.

• An AS SELECT clause can use a complex-type column, but any other expression must be of a scalar type or native array.

• The ORDER BY, PARTITION BY, and GROUP BY clauses cannot use complex types.

• If a projection does not include an ORDER BY or segmentation clause, Vertica uses only the primitive columns from the select list to order or segment data.

• Projection columns cannot be complex types returned from functions such as ARRAY_CAT.

• TopK and UDTF projections do not support complex types.

26.1 - Encoding types

Vertica supports various encoding and compression types, specified by the following ENCODING parameter arguments:

• AUTO (default)

• BLOCK_DICT

• BLOCKDICT_COMP

• BZIP_COMP

• COMMONDELTA_COMP

• DELTARANGE_COMP

• DELTAVAL

• GCDDELTA

• GZIP_COMP

• RLE

• Zstandard Compression

You can set encoding types on a projection column when you create the projection. You can also change the encoding of one or more projection columns for a given table with ALTER TABLE...ALTER COLUMN.

AUTO (default)

AUTO encoding is ideal for sorted, many-valued columns such as primary keys. It is also suitable for general purpose applications for which no other encoding or compression scheme is applicable. Therefore, it serves as the default if no encoding/compression is specified.

The CPU requirements for this type are relatively small. In the worst case, data might expand by eight percent (8%) for LZO and twenty percent (20%) for integer data.

BLOCK_DICT

For each block of storage, Vertica compiles distinct column values into a dictionary and then stores the dictionary and a list of indexes to represent the data block.

BLOCK_DICT is ideal for few-valued, unsorted columns where saving space is more important than encoding speed. Certain kinds of data, such as stock prices, are typically few-valued within a localized area after the data is sorted, such as by stock symbol and timestamp, and are good candidates for BLOCK_DICT. By contrast, long CHAR/VARCHAR columns are not good candidates for BLOCK_DICT encoding.

CHAR and VARCHAR columns that contain 0x00 or 0xFF characters should not be encoded with BLOCK_DICT. Also, BINARY/VARBINARY columns do not support BLOCK_DICT encoding.

BLOCK_DICT encoding requires significantly higher CPU usage than default encoding schemes. The maximum data expansion is eight percent (8%).

BLOCKDICT_COMP

This encoding type is similar to BLOCK_DICT except dictionary indexes are entropy coded. This encoding type requires significantly more CPU time to encode and decode and has a poorer worst-case performance. However, if the distribution of values is extremely skewed, using BLOCK_DICT_COMP encoding can lead to space savings.

BZIP_COMP

BZIP_COMP encoding uses the bzip2 compression algorithm on the block contents. See bzip web site for more information. This algorithm results in higher compression than the automatic LZO and gzip encoding; however, it requires more CPU time to compress. This algorithm is best used on large string columns such as VARCHAR, VARBINARY, CHAR, and BINARY. Choose this encoding type when you are willing to trade slower load speeds for higher data compression.

COMMONDELTA_COMP

This compression scheme builds a dictionary of all deltas in the block and then stores indexes into the delta dictionary using entropy coding.

This scheme is ideal for sorted FLOAT and INTEGER-based (DATE/TIME/TIMESTAMP/INTERVAL) data columns with predictable sequences and only occasional sequence breaks, such as timestamps recorded at periodic intervals or primary keys. For example, the following sequence compresses well: 300, 600, 900, 1200, 1500, 600, 1200, 1800, 2400. The following sequence does not compress well: 1, 3, 6, 10, 15, 21, 28, 36, 45, 55.

If delta distribution is excellent, columns can be stored in less than one bit per row. However, this scheme is very CPU intensive. If you use this scheme on data with arbitrary deltas, it can cause significant data expansion.

DELTARANGE_COMP

This compression scheme is primarily used for floating-point data; it stores each value as a delta from the previous one.

This scheme is ideal for many-valued FLOAT columns that are sorted or confined to a range. Do not use this scheme for unsorted columns that contain NULL values, as the storage cost for representing a NULL value is high. This scheme has a high cost for both compression and decompression.

To determine if DELTARANGE_COMP is suitable for a particular set of data, compare it to other schemes. Be sure to use the same sort order as the projection, and select sample data that will be stored consecutively in the database.

DELTAVAL

For INTEGER and DATE/TIME/TIMESTAMP/INTERVAL columns, data is recorded as a difference from the smallest value in the data block. This encoding has no effect on other data types.

DELTAVAL is best used for many-valued, unsorted integer or integer-based columns. CPU requirements for this encoding type are minimal, and data never expands.

GCDDELTA

For INTEGER and DATE/TIME/TIMESTAMP/INTERVAL columns, and NUMERIC columns with 18 or fewer digits, data is recorded as the difference from the smallest value in the data block divided by the greatest common divisor (GCD) of all entries in the block. This encoding has no effect on other data types.

ENCODING GCDDELTA is best used for many-valued, unsorted, integer columns or integer-based columns, when the values are a multiple of a common factor. For example, timestamps are stored internally in microseconds, so data that is only precise to the millisecond are all multiples of 1000. The CPU requirements for decoding GCDDELTA encoding are minimal, and the data never expands, but GCDDELTA may take more encoding time than DELTAVAL.

GZIP_COMP

This encoding type uses the gzip compression algorithm. See gzip web site for more information. This algorithm results in better compression than the automatic LZO compression, but lower compression than BZIP_COMP. It requires more CPU time to compress than LZO but less CPU time than BZIP_COMP. This algorithm is best used on large string columns such as VARCHAR, VARBINARY, CHAR, and BINARY. Use this encoding when you want a better compression than LZO, but at less CPU time than bzip2.

RLE

RLE (run length encoding) replaces sequences (runs) of identical values with a single pair that contains the value and number of occurrences. Therefore, it is best used for low cardinality columns that are present in the ORDER BY clause of a projection.

The Vertica execution engine processes RLE encoding run-by-run and the Vertica optimizer gives it preference. Use it only when run length is large, such as when low-cardinality columns are sorted.

Zstandard compression

Vertica supports three ZSTD compression types:

• ZSTD_COMP provides high compression ratios. This encoding type has a higher compression than gzip. Use this when you want a better compression than gzip. For general use cases, use this or the ZSTD_FAST_COMP encoding type.

• ZSTD_FAST_COMP uses the fastest compression level that the zstd library provides. It is the fastest encoding type of the zstd library, but takes up more space than the other two encoding types. For general use cases, use this or the ZSTD_COMP encoding type.

• ZSTD_HIGH_COMP offers the best compression in the zstd library. It is slower than the other two encoding types. Use this type when you need the best compression, with slower CPU time.

26.2 - GROUPED clause

Enterprise Mode only

Groups two or more columns into a single disk file. Doing so minimizes file I/O for the following tasks:

• Read a large percentage of the columns in a table.

• Perform single row look-ups.

• Query against many small columns.

• Frequently update data in these columns.

You can improve query performance by grouping columns that are always accessed together and are not used in predicates. Once columns are grouped, queries can no longer retrieve from disk records for one column independently of the others.

You can group columns in several ways:

• Group some of the columns:

  (a, GROUPED(b, c), d)
  

• Group all of the columns:

  (GROUPED(a, b, c, d))
  

• Create multiple groupings in the same projection:

  (GROUPED(a, b), GROUPED(c, d))
  

Grouping columns

The following example shows how to group columns bid and ask. The stock column is stored separately.

=> CREATE TABLE trades (stock CHAR(5), bid INT, ask INT);
=> CREATE PROJECTION tradeproj (stock ENCODING RLE,
   GROUPED(bid ENCODING DELTAVAL, ask))
   AS (SELECT * FROM trades) KSAFE 1;

The following example show how to create a projection that uses expressions in the column definition. The projection contains two integer columns a and b, and a third column product_value that stores the product of a and b:

=> CREATE TABLE values (a INT, b INT);
=> CREATE PROJECTION product (a, b, product_value) AS
   SELECT a, b, a*b FROM values ORDER BY a KSAFE;

26.3 - Hash segmentation clause

Specifies how to segment projection data for distribution across all cluster nodes. You can specify segmentation for a table and a projection. If a table definition specifies segmentation, Vertica uses it for that table's auto-projections.

It is strongly recommended that you use Vertica's built-in HASH function, which distributes data evenly across the cluster, and facilitates optimal query execution.

Syntax

SEGMENTED BY expression ALL NODES [ OFFSET offset ]

Parameters

SEGMENTED BY expression

A general SQL expression. Hash segmentation is the preferred method of segmentation. Vertica recommends using its built-in HASH function, whose arguments resolve to table columns. If you use an expression other than HASH, Vertica issues a warning.

The segmentation expression should specify columns with a large number of unique data values and acceptable skew in their data distribution. In general, primary key columns that meet these criteria are good candidates for hash segmentation.

For details, see Expression Requirements below.

ALL NODES

Automatically distributes data evenly across all nodes when the projection is created. Node ordering is fixed.

OFFSET offset

A zero-based offset that indicates on which node to start segmentation distribution.

This option is not valid for CREATE TABLE and CREATE TEMPORARY TABLE.

Important

If you create a projection for a table with the OFFSET option, be sure to create enough copies of each projection segment to satisfy system K-safety; otherwise, Vertica regards the projection as unsafe and cannot use it to query the table.

You can ensure K-safety compliance when you create projections by combining OFFSET and KSAFE options in the CREATE PROJECTION statement. On executing this statement, Vertica automatically creates the necessary number of projection copies.

Expression requirements

A segmentation expression must specify table columns as they are defined in the source table. Projection column names are not supported.

The following restrictions apply to segmentation expressions:

• All leaf expressions must be constants or column references to a column in the CREATE PROJECTION 's SELECT list.

• The expression must return the same value over the life of the database.

• Aggregate functions are not allowed.

• The expression must return non-negative INTEGER values in the range 0 <= x < 263, and values are generally distributed uniformly over that range.

  Note

  If the expression produces a value outside the expected range—for example, a negative value—no error occurs, and the row is added to the projection's first segment.

Examples

The following CREATE PROJECTION statement creates projection public.employee_dimension_super. It specifies to include all columns in table public.employee_dimension. The hash segmentation clause invokes the Vertica HASH function to segment projection data on the column employee_key; it also includes the ALL NODES clause, which specifies to distribute projection data evenly across all nodes in the cluster:

=> CREATE PROJECTION public.employee_dimension_super
    AS SELECT * FROM public.employee_dimension
    ORDER BY employee_key
    SEGMENTED BY hash(employee_key) ALL NODES;

26.4 - Live aggregate projection

Stores the grouped results of queries that invoke aggregate functions (such as SUM) on table columns. For details, see Live aggregate projections.

Vertica does not regard live aggregate projections as superprojections, even those that include all table columns. For other requirements and restrictions, see Creating live aggregate projections.

Syntax

CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ] }[,...]
) ]
AS SELECT { table-column | expr-with-table-columns }[,...] FROM [[database.]schema.]table [ [AS] alias]
   GROUP BY column-expr[,...]
   [ KSAFE [ k-num ] ]

Arguments

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

[database.]schema

Schema for this projection and its anchor table. The value must be the same for both. If you specify a database, it must be the current database.

projection

Name of the projection to create, which must conform to Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

projection-column

The name of a projection column. The list of projection columns must match the SELECT list columns and expressions in number, type, and sequence.

If projection column names are omitted, Vertica uses the anchor table column names specified in the SELECT list.

grouped-clause

See GROUPED clause.

ENCODING encoding-type

The column encoding type, by default set to AUTO.

ACCESSRANK integer

Overrides the default access rank for a column. Use this parameter to increase or decrease the speed at which Vertica accesses a column. For more information, see Overriding Default Column Ranking.

AS SELECT

Specifies the table data to query:

{table-column | expr-with-table-columns } [ [AS] alias] }[,...]

You can optionally assign an alias to each column expression and reference that alias elsewhere in the SELECT statement.

If you specify projection column names, the two lists of projection columns and table columns/expressions must exactly match in number and order.

GROUP BY column-expr[,...]

One or more column expressions from the SELECT list. The first expression must be the first column expression in the SELECT list, the second expression must be the second column expression in the SELECT list, and so on.

Privileges

Non-superusers:

• Anchor table owner

• CREATE privilege on the schema

Examples

See Live aggregate projection example.

26.5 - Standard projection

Stores a collection of table data in a format that optimizes execution of certain queries on that table. For details, see Projections .

Syntax

CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ] }[,...]
) ]
AS SELECT { * | { MATCH_COLUMNS('pattern') | expression [ [AS] alias ] }[,...] }
   FROM [[database.]schema.]table [ [AS] alias]
   [ ORDER BY column[,...] ]
   [ SEGMENTED BY expression ALL NODES [ OFFSET offset ] | UNSEGMENTED ALL NODES ]
   [ KSAFE [ k-num ]
   [ ON PARTITION RANGE BETWEEN min-val AND max-val ] ]

Arguments

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

[database.]schema

Schema for this projection and its anchor table. The value must be the same for both. If you specify a database, it must be the current database.

projection

Name of the projection to create, which must conform to Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

projection-column

The name of a projection column. The list of projection columns must match the SELECT list columns and expressions in number, type, and sequence.

If projection column names are omitted, Vertica uses the anchor table column names specified in the SELECT list.

grouped-clause

See GROUPED clause.

ENCODING encoding-type

The column encoding type, by default set to AUTO.

ACCESSRANK integer

Overrides the default access rank for a column. Use this parameter to increase or decrease the speed at which Vertica accesses a column. For more information, see Overriding Default Column Ranking.

AS SELECT

Columns or column expressions to select from the specified table:

• * (asterisk): Returns all columns in the queried tables.

• MATCH_COLUMNS('pattern'): Returns the names of all columns in the queried anchor table that match pattern.

• expression[[AS]alias]: Resolves to column data from the queried anchor table. You can optionally assign an alias to each column expression and reference that alias elsewhere in the SELECT statement—for example, in the ORDER BY or segmentation clause.

If you specify projection column names, the two lists of projection columns and table columns/expressions must exactly match in number and order.

ORDER BY column

Columns from the SELECT list on which to sort the projection. The ORDER BY clause can only be set to ASC (the default). Vertica always stores projection data in ascending sort order.

If you order by a column with a collection data type (ARRAY or SET), queries that use that column in an ORDER BY clause perform the sort again. This is because projections and queries perform the ordering differently.

If you omit the ORDER BY clause, Vertica uses the SELECT list to sort the projection.

SEGMENTED BY expression ALL NODES [ OFFSET offset ] | UNSEGMENTED ALL NODES

How to distribute projection data:

• Hash-segmentation using an expression: segment projection data evenly and distribute across cluster nodes.

• Unsegmented

If neither the anchor table nor the projection specifies segmentation, Vertica uses hash segmentation using all columns:

SEGMENTED BY HASH(column[,...]) ALL NODES OFFSET 0;

Tip

Vertica recommends segmenting large tables.

KSAFE k-num

Specifies K-safety for the projection. The value must be equal to or greater than database K-safety. Vertica ignores this option if set for unsegmented projections.

If you omit this clause, Vertica uses database K-safety.

For general information, see K-safety in an Enterprise Mode database.

ON PARTITION RANGE BETWEEN min-val AND max-val

Limits projection data to a range of partition keys. The minimum value must be less than or equal to the maximum value.

Values can be NULL. A null minimum value indicates no lower bound and a null maximum value indicates no upper bound. If both are NULL, this statement drops the projection endpoints, producing a regular projection instead of a range projection.

For other requirements and usage details, see Partition range projections.

Privileges

Non-superusers:

• Anchor table owner

• CREATE privilege on the schema

Examples

See:

• Segmented projections
• Unsegmented projections
• Partition range projections

26.6 - Top-k projection

Stores the top k rows from partitions of selected rows. For details, see Top-k projections.

Vertica does not regard Top-K projections as superprojections, even those that include all table columns. For other requirements and restrictions, see Creating top-k projections.

Syntax

CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ] }[,...]
) ]
AS SELECT { table-column | expr-with-table-columns }[,...] FROM [[database.]schema.]table [ [AS] alias]
   LIMIT num-rows OVER ( window-partition-clause window-order-clause )
   [ KSAFE [ k-num ] ]

Arguments

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

[database.]schema

Schema for this projection and its anchor table. The value must be the same for both. If you specify a database, it must be the current database.

projection

Name of the projection to create, which must conform to Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

projection-column

The name of a projection column. The list of projection columns must match the SELECT list columns and expressions in number, type, and sequence.

If projection column names are omitted, Vertica uses the anchor table column names specified in the SELECT list.

grouped-clause

See GROUPED clause.

ENCODING encoding-type

The column encoding type, by default set to AUTO.

ACCESSRANK integer

Overrides the default access rank for a column. Use this parameter to increase or decrease the speed at which Vertica accesses a column. For more information, see Overriding Default Column Ranking.

AS SELECT

Specifies the table data to query:

{table-column | expr-with-table-columns } [ [AS] alias] }[,...]

You can optionally assign an alias to each column expression and reference that alias elsewhere in the SELECT statement.

If you specify projection column names, the two lists of projection columns and table columns/expressions must exactly match in number and order.

LIMIT num-rows

The number of rows to return from the specified partition.

OVER (window-partition-clause window-order-clause)

Specifies window partitioning by one or more comma-delimited column expressions from the SELECT list. The first partition expression must be the first SELECT list item, the second partition expression the second SELECT list item, and so on.

The order clause is required, and specifies the order in which the top k rows are returned. The default is ascending (ASC) order. All column expressions must be from the SELECT list, where the first window order expression must be the first SELECT list item not specified in the window partition clause.

Top-K projections support ORDER BY NULLS FIRST/LAST.

Privileges

Non-superusers:

• Anchor table owner

• CREATE privilege on the schema

Examples

See Top-k projection examples.

26.7 - UDTF projection

Stores newly-loaded data after it is transformed and/or aggregated by user-defined transformation functions (UDTFs). For details and examples, see Pre-aggregating UDTF results.

Syntax

CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ]  }[,...]
) ]
AS { batch-query FROM { prepass-query sq-ref | table [[AS] alias] }
     | prepass-query }

batch-query:

SELECT { table-column | expr-with-table-columns }[,...], batch-udtf(batch-args)
   OVER (PARTITION BATCH BY partition-column-expr[,...])
   [ AS (output-columns) ]

prepass-query:

SELECT { table-column | expr-with-table-columns }[,...], prepass-udtf(prepass-args)
   OVER (PARTITION PREPASS BY partition-column-expr[,...])
   [ AS (output-columns) ] FROM table

Arguments

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

[database.]schema

Schema for this projection and its anchor table. The value must be the same for both. If you specify a database, it must be the current database.

projection

Name of the projection to create, which must conform to Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

projection-column

The name of a projection column. The list of projection columns must match the SELECT list columns and expressions in number, type, and sequence.

If projection column names are omitted, Vertica uses the anchor table column names specified in the SELECT list.

grouped-clause

See GROUPED clause.

ENCODING encoding-type

The column encoding type, by default set to AUTO.

ACCESSRANK integer

Overrides the default access rank for a column. Use this parameter to increase or decrease the speed at which Vertica accesses a column. For more information, see Overriding Default Column Ranking.

SELECT

Specifies the table data to query:

{table-column | expr-with-table-columns } [ [AS] alias] }[,...]

You can optionally assign an alias to each column expression and reference that alias elsewhere in the SELECT statement.

If you specify projection column names, the two lists of projection columns and table columns/expressions must exactly match in number and order.

batch-udtf(batch-args)

The batch UDTF to invoke each time the following events occur:

• Tuple mover mergeout

• Queries on the projection

• If invoked singly, on data load operations

Important

If the projection definition includes a pre-pass subquery, batch-args must exactly match the pre-pass UDTF output columns, in name and order.

prepass-udtf(prepass-args)

The pre-pass UDTF to invoke on each load operation such as COPY or INSERT.

If specified in a subquery, the pre-pass UDTF returns transformed data to the batch query for further processing. Otherwise, the pre-pass query results are added to projection data storage.

OVER (PARTITION { BATCH | PREPASS } BY partition-column-expr[,...])

Specifies the UDTF type and how to partition the data it returns:

• BATCH identifies the UDTF as a batch UDTF.

• PREPASS identifies the UDTF as a pre-pass UDTF.

In both cases, the OVER clause specifies partitioning with one or more column expressions from the SELECT list. The first expression is the first column expression in the SELECT list, the second expression is the second column expression in the SELECT list, and so on.

Note

The projection is implicitly segmented and ordered on PARTITION BY columns.

AS output-columns

Optionally names columns that are returned by the UDTF.

If a pre-pass subquery omits this clause, the outer batch query UDTF arguments (batch-args) must reference the column names as they are defined in the pre-pass UDTF.

table[[AS]alias]

The projection's anchor table, optionally qualified by an alias.

sq-results

Subquery result set that is returned to the outer batch UDTF.

Privileges

Non-superusers:

• Anchor table owner

• CREATE privilege on the schema

• EXECUTE privileges on all UDTFs that are referenced by the projection

Examples

See Pre-aggregating UDTF results.

26.8 - Unsegmented clause

Specifies to distribute identical copies of table or projection data on all nodes across the cluster. Use this clause to facilitate distributed query execution on tables and projections that are too small to benefit from segmentation.

Vertica uses the same name to identify all instances of an unsegmented projection. For more information about projection name conventions, see Projection naming.

Syntax

UNSEGMENTED ALL NODES

Examples

This example creates an unsegmented projection for table store.store_dimension:

=> CREATE PROJECTION store.store_dimension_proj (storekey, name, city, state)
             AS SELECT store_key, store_name, store_city, store_state
             FROM store.store_dimension
             UNSEGMENTED ALL NODES;
CREATE PROJECTION

=>  SELECT anchor_table_name anchor_table, projection_name, node_name
      FROM PROJECTIONS WHERE projection_basename='store_dimension_proj';
  anchor_table   |   projection_name    |    node_name
-----------------+----------------------+------------------
 store_dimension | store_dimension_proj | v_vmart_node0001
 store_dimension | store_dimension_proj | v_vmart_node0002
 store_dimension | store_dimension_proj | v_vmart_node0003
(3 rows)

27 - CREATE RESOURCE POOL

Creates a user-defined resource pool.

Syntax

CREATE RESOURCE POOL pool-name [ FOR subcluster ] [ parameter-name setting ]...

Arguments

pool-name

Name of the resource pool. If you specify a resource pool name with uppercase letters, Vertica converts them to lowercase letters.

The following naming requirements apply:

• Built-in pool names cannot be used for user-defined pools.

• All user-defined global resource pools must have unique names.

• Names of global and subcluster-assigned resource pools cannot be the same. However, the same name can be shared by resource pools of different subclusters.

FOR subcluster

Eon Mode only, the subcluster to associate with this resource pool, where subcluster is one of the following:

• SUBCLUSTER subcluster-name: Resource pool for an existing subcluster. You cannot be connected to this subcluster, otherwise Vertica returns an error.
• CURRENT SUBCLUSTER: Resource pool for the subcluster that you are connected to.

If omitted, the resource pool is created globally.

parameter-name setting

A resource pool parameter and its initial value. If you omit this argument, Vertica sets this resource pool's parameters to their default values (see Parameters).

Parameters

Default values specified here pertain only to user-defined resource pools. For built-in pool default values, see Built-in resource pools configuration, or query system table RESOURCE_POOL_DEFAULTS.

CASCADE TO

Secondary resource pool for executing queries that exceed the RUNTIMECAP setting of their assigned resource pool:

CASCADE TO secondary-pool

CPUAFFINITYMODE

Specifies whether the resource pool has exclusive or shared use of the CPUs specified in CPUAFFINITYSET:

CPUAFFINITYMODE { SHARED | EXCLUSIVE | ANY }

• SHARED: Queries that run in this resource pool share its CPUAFFINITYSET CPUs with other Vertica resource pools.
• EXCLUSIVE: Dedicates CPUAFFINITYSET CPUs to this resource pool only, and excludes other Vertica resource pools. If CPUAFFINITYSET is set as a percentage, then that percentage of CPU resources available to Vertica is assigned solely for this resource pool.
• ANY: Queries in this resource pool can run on any CPU, invalid if CPUAFFINITYSET designates CPU resources.

Default: ANY

CPUAFFINITYSET

CPUs available to this resource pool. All cluster nodes must have the same number of CPUs. The CPU resources assigned to this set are unavailable to general resource pools.

CPUAFFINITYSET {
  'cpu-index[,...]'
| 'cpu-indexi-cpu-indexn'
| 'integer%'
| NONE
}

• cpu-index[,...]: Dedicates one or more comma-delimited CPUs to this resource pool.
• cpu-indexi-cpu-indexn: Dedicates a range of contiguous CPU indexes i through n to this resource pool.
• integer%: Percentage of all available CPUs to use for this resource pool. Vertica rounds this percentage down to include whole CPU units.
• NONE (empty string): No affinity set is assigned to this resource pool. Queries associated with this pool are executed on any CPU.

Default: NONE

Important

CPUAFFINITYSET and CPUAFFINITYMODE must be set together in the same statement.

EXECUTIONPARALLELISM

Number of threads used to process any single query issued in this resource pool.

EXECUTIONPARALLELISM { limit | AUTO }

• limit: An integer value between 1 and the number of cores. Setting this parameter to a reduced value increases throughput of short queries issued in the resource pool, especially if queries are executed concurrently.
• AUTO or 0: Vertica calculates the setting from the number of cores, available memory, and amount of data in the system. Unless memory is limited, or the amount of data is very small, Vertica sets this parameter to the number of cores on the node.

Default: AUTO

MAXCONCURRENCY

Maximum number of concurrent execution slots available to the resource pool across the cluster:

MAXCONCURRENCY { integer | NONE }

NONE (empty string): Unlimited number of concurrent execution slots.

Default: NONE

MAXMEMORYSIZE

Maximum size per node the resource pool can grow by borrowing memory from the GENERAL pool:

MAXMEMORYSIZE {
  'integer%'
  |'integer{K|M|G|T}'
  NONE
}

• integer%: Percentage of total memory
• integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes
• NONE (empty string): Unlimited, resource pool can borrow any amount of available memory from the GENERAL pool.

Default: NONE

MAXQUERYMEMORYSIZE

Maximum amount of memory this resource pool can allocate at runtime to process a query. If the query requires more memory than this setting, Vertica stops execution and returns an error.

Set this parameter as follows:

MAXQUERYMEMORYSIZE {
  'integer%'
| 'integer{K|M|G|T}'
| NONE
}

• integer%: Percentage of MAXMEMORYSIZE for this resource pool.
• integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes, up to the value of MAXMEMORYSIZE.
• NONE (empty string): Unlimited; resource pool can borrow any amount of available memory from the GENERAL pool, within the limits set by MAXMEMORYSIZE.

Default: NONE

MEMORYSIZE

Total per-node memory available to the Vertica resource manager that is allocated to this resource pool:

MEMORYSIZE {
  'integer%'
| 'integer{K|M|G|T}'
}

• integer%: Percentage of total memory
• integer{K|M|G|T}: Amount of memory in kilobytes, megabytes, gigabytes, or terabytes

Default: 0%. No memory allocated, the resource pool borrows memory from the GENERAL pool.

PLANNEDCONCURRENCY

Preferred number of queries to execute concurrently in the resource pool. This setting applies to the entire cluster:

PLANNEDCONCURRENCY { num-queries | AUTO }

• num-queries: Integer value ≥ 1, the preferred number of queries to execute concurrently in the resource pool. When possible, query resource budgets are limited to allow this level of concurrent execution.

• AUTO: Value is calculated automatically at query runtime. Vertica sets this parameter to the lower of these two calculations, but never less than 4:

  • Number of logical cores

  • Memory divided by 2GB

  If the number of logical cores on each node is different, AUTO is calculated differently for each node. Distributed queries run like the minimal effective planned concurrency. Single node queries run with the planned concurrency of the initiator.

Default: AUTO

Tip

Change this parameter only after evaluating performance over a period of time.

PRIORITY

Priority of queries in this resource pool when they compete for resources in the GENERAL pool:

PRIORITY { integer | HOLD }

• integer: Negative or positive integer value, where higher numbers denote higher priority:

  • User-defined resource pool: -100 to 100

  • Built-in resource pools SYSQUERY, RECOVERY, and TM: -110 to 110

• HOLD: Sets priority to -999. Queries in this resource pool are queued until QUEUETIMEOUT is reached.

Default: 0

QUEUETIMEOUT

Maximum time a request can wait for pool resources before it is rejected, not more than one year:

QUEUETIMEOUT { integer | 'interval' | 'NONE' }

• integer: Maximum wait time in seconds

• [interval](/en/sql-reference/language-elements/literals/datetime-literals/interval-literal/): Maximum wait time expressed in the following format:

  num year num months num [days] HH:MM:SS.ms
  

• NONE (empty string): No maximum wait time, request can be queued indefinitely, up to one year.

If the value that you specify resolves to more than one year, Vertica returns with a warning and sets the parameter to 365 days:

=> ALTER RESOURCE POOL user_0 QUEUETIMEOUT '11 months 50 days 08:32';
WARNING 5693:  Using 1 year for QUEUETIMEOUT
ALTER RESOURCE POOL
=> SELECT QUEUETIMEOUT FROM resource_pools WHERE name = 'user_0';
 QUEUETIMEOUT
--------------
 365
(1 row)

Default: 00:05 (5 minutes)

RUNTIMECAP

Maximum execution time allowed to queries in this resource pool, not more than one year, otherwise Vertica returns with an error. If a query exceeds this setting, it tries to cascade to a secondary pool:

RUNTIMECAP { 'interval' | NONE }

• interval: Maximum wait time expressed in the following format:

  num year num month num [day] HH:MM:SS.ms
  

• NONE (empty string): No maximum wait time, request can be queued indefinitely, up to one year.

If the user or session also has a RUNTIMECAP, the shorter limit applies.

RUNTIMEPRIORITY

Determines how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool:

RUNTIMEPRIORITY { HIGH | MEDIUM | LOW }

Default: MEDIUM

RUNTIMEPRIORITYTHRESHOLD

Maximum time (in seconds) in which query processing must complete before the resource manager assigns to it the resource pool's RUNTIMEPRIORITY. All queries begin execution with a priority of HIGH.

RUNTIMEPRIORITYTHRESHOLD seconds

Default: 2

SINGLEINITIATOR

Set to false for backward compatibility. Do not change this setting.

Privileges

Superuser

Examples

This example shows how to create a resource pool with MEMORYSIZE of 1800 MB.

=> CREATE RESOURCE POOL ceo_pool MEMORYSIZE '1800M' PRIORITY 10;
CREATE RESOURCE POOL

Assuming the CEO report user already exists, associate this user with the preceding resource pool using ALTER USER statement.

=> GRANT USAGE ON RESOURCE POOL ceo_pool to ceo_user;
GRANT PRIVILEGE
=> ALTER USER ceo_user RESOURCE POOL ceo_pool;
ALTER USER

Issue the following command to confirm that the ceo_user is associated with the ceo_pool:

=> SELECT * FROM users WHERE user_name ='ceo_user';
-[ RECORD 1 ]-----+--------------------------------------------------
user_id           | 45035996273733402
user_name         | ceo_user
is_super_user     | f
profile_name      | default
is_locked         | f
lock_time         |
resource_pool     | ceo_pool
memory_cap_kb     | unlimited
temp_space_cap_kb | unlimited
run_time_cap      | unlimited
all_roles         |
default_roles     |
search_path       | "$user", public, v_catalog, v_monitor, v_internal

This exampleshows how to create and designate secondary resource pools.

=> CREATE RESOURCE POOL rp3 RUNTIMECAP '5 minutes';
=> CREATE RESOURCE POOL rp2 RUNTIMECAP '3 minutes' CASCADE TO rp3;
=> CREATE RESOURCE POOL rp1 RUNTIMECAP '1 minute' CASCADE TO rp2;
=> SET SESSION RESOURCE_POOL = rp1;

This Eon Mode example confirms the current subcluster name, then creates a resource pool for the current subcluster:

=> SELECT CURRENT_SUBCLUSTER_NAME();
 CURRENT_SUBCLUSTER_NAME
-------------------------
 analytics_1
(1 row)

=> CREATE RESOURCE POOL dashboard FOR SUBCLUSTER analytics_1;
CREATE RESOURCE POOL

See also

• ALTER RESOURCE POOL
• CREATE USER
• DROP RESOURCE POOL
• SET SESSION RESOURCE_POOL
• SET SESSION MEMORYCAP
• Managing workloads

27.1 - Built-in pools

Vertica is preconfigured with built-in pools for various system tasks:

• GENERAL

• BLOBDATA

• DBD

• JVM

• METADATA

• RECOVERY

• REFRESH

• SYSQUERY

• TM

For details on resource pool settings, see ALTER RESOURCE POOL.

GENERAL

Catch-all pool used to answer requests that have no specific resource pool associated with them. Any memory left over after memory has been allocated to all other pools is automatically allocated to the GENERAL pool. The MEMORYSIZE parameter of the GENERAL pool is undefined (variable), however, the GENERAL pool must be at least 1GB in size and cannot be smaller than 25% of the memory in the system.

The MAXMEMORYSIZE parameter of the GENERAL pool has special meaning; when set as a % value it represents the percent of total physical RAM on the machine that the Resource manager can use for queries. By default, it is set to 95%. MAXMEMORYSIZE governs the total amount of RAM that the Resource Manager can use for queries, regardless of whether it is set to a percent or to a specific value (for example, '10GB').

User-defined pools can borrow memory from the GENERAL pool to satisfy requests that need extra memory until the MAXMEMORYSIZE parameter of that pool is reached. If the pool is configured to have MEMORYSIZE equal to MAXMEMORYSIZE, it cannot borrow any memory from the GENERAL pool. When multiple pools request memory from the GENERAL pool, they are granted access to general pool memory according to their priority setting. In this manner, the GENERAL pool provides some elasticity to account for point-in-time deviations from normal usage of individual resource pools.

Vertica recommends reducing the GENERAL pool MAXMEMORYSIZE if your catalog uses over 5 percent of overall memory. You can calculate what percentage of GENERAL pool memory the catalog uses as follows:

=> WITH memory_use_metadata AS (SELECT node_name, memory_size_kb FROM resource_pool_status WHERE pool_name='metadata'),
        memory_use_general  AS (SELECT node_name, memory_size_kb FROM resource_pool_status WHERE pool_name='general')
   SELECT m.node_name, ((m.memory_size_kb/g.memory_size_kb) * 100)::NUMERIC(4,2) pct_catalog_usage
   FROM memory_use_metadata m JOIN memory_use_general g ON m.node_name = g.node_name;
    node_name     | pct_catalog_usage
------------------+-------------------
 v_vmart_node0001 |              0.41
 v_vmart_node0002 |              0.37
 v_vmart_node0003 |              0.36
(3 rows)

BLOBDATA

Controls resource usage for in-memory blobs. In-memory blobs are objects used by a number of the machine learning SQL functions. You should adjust this pool if you plan on processing large machine learning workloads. For information about tuning the pool, see Tuning for machine learning.

If a query using the BLOBDATA pool exceeds its query planning budget, then it spills to disk. For more information about tuning your query budget, see Query budgeting.

DBD

Controls resource usage for Database Designer processing. Use of this pool is enabled by configuration parameter DBDUseOnlyDesignerResourcePool, by default set to false.

By default, QUEUETIMEOUT is set to 0 for this pool. When resources are under pressure, this setting causes the DBD to time out immediately, and not be queued to run later. Database Designer then requests the user to run the designer later, when resources are more available.

JVM

Controls Java Virtual Machine resources used by Java User Defined Extensions. When a Java UDx starts the JVM, it draws resources from the those specified in the JVM resource pool. Vertica does not reserve memory in advance for the JVM pool. When needed, the pool can expand to 10% of physical memory or 2 GB of memory, whichever is smaller. If you are buffering large amounts of data, you may need to increase the size of the JVM resource pool.

You can adjust the size of your JVM resource pool by changing its configuration settings. Unlike other resource pools, the JVM resource pool does not release resources until a session is closed.

METADATA

Tracks memory allocated for catalog data and storage data structures. This pool increases in size as Vertica metadata consumes additional resources. Memory assigned to the METADATA pool is subtracted from the GENERAL pool, enabling the Vertica resource manager to make more effective use of available resources. If the METADATA resource pool reaches 75% of the GENERAL pool, Vertica stops updating METADATA memory size and displays a warning message in vertica.log. You can enable or disable the METADATA pool with configuration parameter EnableMetadataMemoryTracking.

If you created a "dummy" or "swap" resource pool to protect resources for use by your operating system, you can replace that pool with the METADATA pool.

Users cannot change the parameters of the METADATA resource pool.

RECOVERY

Used by queries issued when recovering another node of the database. The MAXCONCURRENCY parameter is used to determine how many concurrent recovery threads to use. You can use the PLANNEDCONCURRENCY parameter (by default, set to twice the MAXCONCURRENCY) to tune how to apportion memory to recovery queries.

See Tuning for recovery.

REFRESH

Used by queries issued by PROJECTION_REFRESHES operations. Refresh does not currently use multiple concurrent threads; thus, changes to the MAXCONCURRENCY values have no effect.

See Scenario: Tuning for Refresh.

SYSQUERY

Runs queries against all system monitoring and catalog tables. The SYSQUERY pool reserves resources for system table queries so that they are never blocked by contention for available resources.

TM

The Tuple Mover (TM) pool. You can set the MAXCONCURRENCY parameter for the TM pool to allow concurrent TM operations.

See Tuning tuple mover pool settings.

27.2 - Built-in resource pools configuration

To view the current and default configuration for built-in resource pools, query the system tables RESOURCE_POOLS and RESOURCE_POOL_DEFAULTS, respectively. The sections below provide this information, and also indicate which built-in pool parameters can be modified with ALTER RESOURCE POOL:

• GENERAL
• BLOBDATA
• DBD
• JVM
• METADATA
• RECOVERY
• REFRESH
• SYSQUERY
• TM

GENERAL

MAXMEMORYSIZE {
  'integer%'
 | 'integer{K|M|G|T}'
}

MAXQUERYMEMORYSIZE {
  'integer%'
 | 'integer{K|M|G|T}'
}

BLOBDATA

DBD

JVM

METADATA

RECOVERY

MAXMEMORYSIZE {
  'integer%'
  |'integer{K|M|G|T}'
  NONE
}

REFRESH

SYSQUERY

TM

28 - CREATE ROLE

Creates a role. After creating a role, use GRANT statements to specify role permissions.

Syntax

CREATE ROLE role

Parameters

role

The name for the new role, where role conforms to conventions described in Identifiers.

Privileges

Superuser

Examples

This example shows to create an empty role called roleA.

=> CREATE ROLE roleA;
CREATE ROLE

See also

• ALTER ROLE
• DROP ROLE

29 - CREATE ROUTING RULE

Creates one of the following rules:

• A load balancing routing rule that directs incoming client connections from an IP address range to a group of Vertica nodes. This group of Vertica nodes is defined by a load balance group. Once you create a routing rule, any client connection originating from the rule's IP address range is redirected to one of the nodes in the load balance group if the client opts into load balancing.
• A workload routing rule that routes incoming client connections to the specified subcluster. To view existing workload routing rules, see WORKLOAD_ROUTING_RULES.

Syntax

CREATE ROUTING RULE
{
    rule_name ROUTE 'address_range' TO group_name |
    ROUTE WORKLOAD workload_name TO SUBCLUSTER subcluster_name [,...] [ PRIORITY priority]
}

Arguments

rule_name

A name for the load balancing routing rule.

address_range

An IPv4 or IPv6 address range in CIDR format, the address range of client connections that this rule applies to.

group_name

The name of the load balance group to handle the client connections from the address range. You create this group with CREATE LOAD BALANCE GROUP.

workload_name

The name of the workload.

subcluster_name

A list of subclusters to route client connections to.

priority

A non-negative INTEGER, the priority of the workload (default: 0). The greater the value, the greater the priority. If a user or their role is granted multiple routing rules, the one with the greatest priority applies.

Privileges

Superuser

Examples

The following example creates a routing rule that routes all client connections from 192.168.1.0 to 192.168.1.255 to a load balance group named internal_clients:

=> CREATE ROUTING RULE internal_clients ROUTE '192.168.1.0/24' TO internal_clients;
CREATE ROUTING RULE

The following example creates a routing rule that routes analytics workloads to subclusters sc_analytics and sc_analytics_2 with a priority of 5. For details, see Workload routing:

=> CREATE ROUTING RULE ROUTE WORKLOAD analytics TO SUBCLUSTER sc_analytics, sc_analytics_2 PRIORITY 5;

See also

• ALTER ROUTING RULE
• DROP ROUTING RULE

30 - CREATE SCHEDULE

Creates a schedule. For details, see Scheduled execution.

To view existing schedules, query USER_SCHEDULES.

Syntax

CREATE SCHEDULE [ IF NOT EXISTS ] [[database.]schema.]schedule
    { USING CRON 'cron_expression' | USING DATETIMES timestamp_list }

Arguments

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

schedule

The name of the schedule.

cron_expression

A cron expression. You should use this for recurring tasks. Value separators are not currently supported.

timestamp_list

A comma-separated list of timestamps. You should use this to schedule non-recurring events at arbitrary times.

Privileges

Superuser

Examples

To create a schedule for October 2, 2022 and November 2, 2022:

=> CREATE SCHEDULE oct_nov_2 USING DATETIMES('2022-10-02 12:00:00', '2022-11-02 12:00:00');

To create a schedule for January 1 at 12:00 PM:

=> CREATE SCHEDULE annual_schedule USING CRON '0 12 1 1 *';

To create a schedule for the first of every month:

=> CREATE SCHEDULE monthly_schedule USING CRON '0 0 1 * *';

To create a schedule for Sunday at 12:00 AM:

=> CREATE SCHEDULE weekly_schedule USING CRON '0 0 * * 0';

To create a schedule for every day at 1:00 PM:

=> CREATE SCHEDULE daily_schedule USING CRON '0 13 * * *';

To create a schedule for every five minutes:

=> CREATE SCHEDULE every_5_minutes USING CRON '*/5 * * * *';

To create a schedule for every hour from 9:00 AM to 4:59 PM:

=> CREATE SCHEDULE 9_to_5 USING CRON '0 9-16 * * *';

31 - CREATE SCHEMA

Defines a schema.

Syntax

CREATE SCHEMA [ IF NOT EXISTS ] [{ database | namespace }.]schema
   [ AUTHORIZATION username]
   [ DEFAULT { INCLUDE | EXCLUDE } [ SCHEMA ] PRIVILEGES ]
   [ DISK_QUOTA quota ]

Arguments

IF NOT EXISTS

If an object with the same name exists, return without creating the object. If you do not use this directive and the object already exists, Vertica returns with an error message.

The IF NOT EXISTS clause is useful for SQL scripts where you might not know if the object already exists. The ON ERROR STOP directive can be helpful in scripts.

database

Name of the database. If specified, it must be the current database.

namespace

For Eon Mode databases, namespace under which to create the schema. If unspecified, the schema is created in the default namespace.

schema

Name of the schema to create, with the following requirements:

• Must be unique among all other schema names in the namespace under which the schema is created.

• Must comply with keyword restrictions and rules for Identifiers.

• Cannot begin with v_; this prefix is reserved for Vertica system tables.

• If schema contains a period, the part before the period cannot be the same as the name of an existing namespace.

AUTHORIZATION username

Valid only for superusers, assigns ownership of the schema to another user. By default, the user who creates a schema is also assigned ownership.

After you create a schema, you can reassign ownership to another user with ALTER SCHEMA.

DEFAULT {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES

Specifies whether to enable or disable default inheritance of privileges for new tables in the specified schema:

• EXCLUDE SCHEMA PRIVILEGES (default): Disables inheritance of schema privileges.

• INCLUDE SCHEMA PRIVILEGES: Specifies to grant tables in the specified schema the same privileges granted to that schema. This option has no effect on existing tables in the schema.

If you omit INCLUDE PRIVILEGES, you must explicitly grant schema privileges on the desired tables.

For more information see Enabling schema inheritance.

DISK_QUOTA quota

String, an integer followed by a supported unit: K, M, G, or T. Data-load, DML, and ILM operations that increase the schema's usage beyond the set quota fail. For details, see Disk quotas.

If not specified, the schema has no quota.

Privileges

• Superuser (required to set disk quota)

• CREATE privilege for the database

Supported sub-statements

CREATE SCHEMA can include one or more sub-statements—for example, to create tables or projections within the new schema. Supported sub-statements include:

• CREATE TABLE / CREATE TEMPORARY TABLE
• GRANT statements
• CREATE PROJECTION
• CREATE SEQUENCE
• CREATE TEXT INDEX
• CREATE VIEW

CREATE SCHEMA statement and all sub-statements are treated as a single transaction. If any statement fails, Vertica rolls back the entire transaction. The owner of the new schema is assigned ownership of all objects that are created within this transaction.

For example, the following CREATE SCHEMA statement also grants privileges on the new schema, and creates a table and view of that table:

=> \c - Joan
You are now connected as user "Joan".
=> CREATE SCHEMA s1
     GRANT USAGE, CREATE ON SCHEMA s1 TO public
     CREATE TABLE s1.t1 (a varchar)
     CREATE VIEW s1.t1v AS SELECT * FROM s1.t1;
CREATE SCHEMA
=> \dtv s1.*
             List of tables
 Schema | Name | Kind  | Owner | Comment
--------+------+-------+-------+---------
 s1     | t1   | table | Joan  |
 s1     | t1v  | view  | Joan  |
(2 rows)