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.

While no technical issue prevents you from using CREATE LOCATION to add one or more Network File System (NFS) storage locations, Vertica does not support NFS data or catalog storage except for MapR mount points. You will be unable to run queries against any other NFS data. When creating locations on MapR file systems, you must specify ALL NODES SHARED.

If you use HDFS storage locations, the HDFS data must be available when you start Vertica. Your HDFS cluster must be operational, and the ROS files must be present. If you moved data files, or they are corrupted, or your HDFS cluster is not responsive, Vertica cannot start.

Syntax

CREATE LOCATION 'path'
    [NODE 'nodename' | ALL NODES]
    [SHARED]
    [USAGE 'use‑type']
    [LABEL 'label']
    [LIMIT 'size']

Arguments

path	Specifies where to store this location's data. The type of file system on which the location is based determines the path format. See Location Paths below.
ALL NODES NODE 'node-name'	Specifies the node or nodes on which the storage location is defined, one of the following: `ALL NODES` (default): Create the storage location on all nodes. `NODE 'node-name'`: Create the storage location on a single node, where node-name identifies the node in system table `NODES`.
`SHARED`	Indicates the location set by the path is shared (used by all nodes) rather than local to each node. For details, see Shared Versus Local Storage. If path is set to S3 comunal storage, `SHARED` is always implied and can be omitted.
`USAGE 'use‑type'`	The type of data the storage location can hold, where use‑type 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 configuration parameter `RemoteStorageForTemp` 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 of this storage location on the local Linux file system, on S3 communal storage, and external tables. `DEPOT`: The storage location is used in Eon Mode to store the depot. Only create DEPOT storage locations on local Linux filesystems. 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. 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. However you specify this value, the depot size cannot be more than 80 percent of disk space of the file system where the depot is stored. If you specify a value that is too large, Vertica warns you of this limitation and automatically changes the value to 80% of the size of the file system. If you omit this parameter, it is set to 60 percent.

Privileges

Superuser

File System Permissions

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

File system	Requirements
Linux	Database superuser account (usually named dbadmin) must have full read and write access to the directory in the path argument.
HDFS without Kerberos	Hadoop user whose username matches the Vertica database administrator username (usually dbadmin). This Hadoop user must have read and write access to the HDFS directory specified in the path argument
HDFS with Kerberos	Hadoop user whose username matches the principal in the keytab file on each Vertica node. This is not the same as the database administrator username. This Hadoop user must have read and write access to the HDFS directory stored in the path argument

Location Paths

Location path formats vary, depending on the storage location's file system:

File system	Path
Linux	Absolute path to the directory where Vertica can write the storage location's data.
HDFS	URL in the `hdfs` scheme. To use HDFS URLs, you must give Vertica access to some HDFS configuration files. For details, see Using HDFS URLs and Configuring the hdfs Scheme.
S3	URLs with the following form: s3://bucket/path
Google Cloud storage	URLs with the following form: gs://bucket/path

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 in the /user/dbadmin directory. 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