DataObject option

Specifies the operating system path to the source data file or any media that can be treated as a file.

You must specify a value for the data object path name. There is no default value for the external table data object. When the RemoteSource option is not set or is set to an empty string, the path must be an absolute path, not a relative path. The file name must be a valid UTF-8 string.

For loads, the file must exist, and the operating system user who initiates the load must have read permission for the file. For unloads, the operating system user who initiates the unload must have read and write permissions for the parent directory of the file, and the data file is overwritten if it exists. Typically, the unloads are owned by the nz user, so the nz user must have permission to read and write files in the target path.

As a best practice, the external table locations should not be in the /nz directory or its subdirectories because the data object files might accidentally interfere with Netezza Performance Server operations. Also, the data object files might use disk space that is needed for the operation of the Netezza Performance Server database and software.

Management of external table locations

By default, data objects can be created in any of the paths on the Netezza Performance Server host that are accessible by the nz user account.

Starting in Release 7.1.0.1, the admin user can specify and manage the locations on the Netezza Performance Server host where users can store the external table data object files. Users who have the Manage System privilege can also manage the locations for the external table data object files. When you change or restrict the external table locations, the changes or restrictions apply only to the new external tables that are created on the system. Any existing external tables continue to use their current data object locations.

To display the current table locations, use the SHOW EXTERNAL TABLE LOCATION command. An example follows: 

TESTDB.ADMIN(ADMIN)=> SHOW EXTERNAL TABLE LOCATION;
 ALLOWDIRECTORY 
----------------
 *
(1 row)

The asterisk indicates that there are no restrictions on the locations for the external table object files.

To restrict the locations for the external table data objects, use the following steps.

  1. Connect to a Netezza Performance Server database as the admin user or any database user with the Manage System privilege.
  2. Review the current table location path names by issuing the SHOW EXTERNAL TABLE LOCATION command.
  3. Take one of the following steps:
    • To remove access to all the paths that the nz user can access, delete the '*' wildcard location. An example follows: 
      TESTDB.ADMIN(ADMIN)=> REMOVE EXTERNAL TABLE LOCATION '*';
REMOVE EXTERNAL TABLE LOCATION
    • Add the locations where the external table objects are allowed by issuing the ADD EXTERNAL TABLE LOCATION command. Any new external tables that are created on the system must be stored in a permitted directory. Command examples follow:
      TESTDB.ADMIN(ADMIN)=> ADD EXTERNAL TABLE LOCATION '/export/home/nz/ext_tbl';
ADD EXTERNAL TABLE LOCATION
TESTDB.ADMIN(ADMIN)=> ADD EXTERNAL TABLE LOCATION '/tmp/ext_tbl';
ADD EXTERNAL TABLE LOCATION

      The locations and the object file must exist on the system and be accessible by the nz user account before you can insert into or read from the external table.

  4. Optional: Review the list of supported table locations by issuing the SHOW EXTERNAL TABLE LOCATION command.

If a user tries to create an external table by specifying a data object path that is not part of the allowed location list, the command fails with an error, as shown in the following example:

TESTDB.ADMIN(ADMIN)=> CREATE EXTERNAL TABLE my_ext_tbl SAMEAS tbl_retail 
USING (DATAOBJECT ('/mydir'));
ERROR:  Invalid path specified in DATAOBJECT, path not allowed '/mydir'

If a user tries to create an external table by specifying a data object path that is in the allowed locations list but the nz user does not have read or write access to the file, the CREATE EXTERNAL TABLE command succeeds, but commands to insert data into the table fail with a permission error. An example follows:

TESTDB.ADMIN(ADMIN)=> CREATE EXTERNAL TABLE my_ext_tbl SAMEAS tbl_retail 
USING (DATAOBJECT ('/tmp/ext_tbl'));
CREATE EXTERNAL TABLE
TESTDB.ADMIN(ADMIN)=> INSERT INTO my_ext_tbl VALUES (1,2,3,4);
ERROR:  /tmp/ext_tbl : Permission denied