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.

Caution: 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 any HDFS storage locations, the HDFS data must be available at the time you start Vertica. Your HDFS cluster must be operational, and the ROS files must be present. If you have moved data files, or if they have become corrupted, or if your HDFS cluster is not responsive, Vertica cannot start.

Syntax

CREATE LOCATION 'path'
    [NODE 'nodename' | ALL NODES]
    [SHARED]
    [USAGE 'usetype']
    [LABEL 'labelname']
    [LIMIT 'MaxSize']

Arguments

path	Where Vertica will store this location's data. The type of filesystem on which the location is based determines the format of this argument: For storage locations on the Linux file system, path must be an absolute path to the directory where Vertica can write the storage location's data. For storage locations on HDFS, path must be an HDFS URL where Vertica can write the storage location's data. See more information below. For USER storage locations on S3 in Eon Mode only, path must be a URL of the form 's3:/ bucket/ path'.
`NODE 'nodename'` `ALL NODES`	The node or nodes on which the storage location is defined. `NODE`: Use this keyword to create the storage location on a single node. Specify the node using its name as it appears in the NODES system table. `ALL NODES` (default): Use this keyword to create the storage location on all nodes
`SHARED`	Indicates the location set by the path is shared (used by all of the nodes) rather than local to each node. See below for details.
`USAGE 'use‑type'`	The type of data the storage location can hold, where use‑type is one of the following: `TEMP`: Vertica uses the location to store temporary files it creates while processing queries. `DATA`: The storage location can only store data. `TEMP,DATA` (default): The storage location can store both temporary files and data. `USER`: Users who have been granted access to the storage location can read and store data there (see GRANT (Storage Location)). This usage type can be used with external tables (see CREATE EXTERNAL TABLE AS COPY), which by default are readable only by administrators.
`LABEL 'labelname'`	A label for the storage location. You use this name later when assigning the storage location to data objects.
`LIMIT 'MaxSize'`	Maximum size for the storage location. You use this name later when assigning the storage location to data objects. If set to 1, there is no limit on the storage location size.

Privileges

Superuser

The Vertica process must also have read and write permissions to the location where date will be stored. Each type of filesystem has its own requirements:

Linux: The database superuser account (usually named dbadmin) must have full read and write access to the directory in the path argument.
HDFS without Kerberos: Requires a 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: Requires a 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.

Shared Versus Local Storage

The SHARED keyword indicates that the location set by the path argument is shared by all nodes. Most remote filesystems (such as HDFS) are shared. For these filesystems, the path argument represents a single location where all of the nodes store data. Each node creates its own subdirectory to hold its own files in a shared storage location. These subdirectories prevent the nodes from overwriting each other's files. Even if your cluster has only one node, you must include the SHARED keyword if you are using a remote filesystem. If the location is declared as USER Vertica does not create sub directories for each node. The setting of USER takes precedence over SHARED.

If you do not supply this keyword, the new storage location is local. The path argument specifies a location that is unique for each node in the cluster. This location is usually a path in the node's own filesystem. Storage locations contained in filesystems that are local to each node (such as the Linux filesystem) are always local.

HDFS URLs

To specify a path for a location on HDFS, use URLs in the hdfs scheme. In most cases you can use hdfs:/// (three slashes) followed by the HDFS path. To use HDFS URLs you must give Vertica access to some HDFS configuration files. For more information, see Using HDFS URLs and Configuring the hdfs Scheme.

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.

Examples

The following example shows how to create a storage location in the local Linux filesystem for temporary data storage.

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

The following example shows how to create a storage location on HDFS in the /user/dbadmin directory. The HDFS cluster does not use Kerberos.

=> CREATE LOCATION 'hdfs:///user/dbadmin' ALL NODES SHARED 
   USAGE 'data' LABEL 'coldstorage';

The following example shows how to 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:///user/dbadmin' ALL NODES SHARED 
		     USAGE 'data' LABEL 'coldstorage';
NOTICE 0: Performing HDFS operations using kerberos principal [vertica/hadoop.example.com] 
CREATE LOCATION

The following example shows how to 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

For an example of a USER storage location using S3, see Browsing S3 Data Using External Tables in Using Eon Mode.