IBM PureData System for Analytics, Version 7.1

The 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 set to empty string), this path must be an absolute path and not a relative path. The file name must be a valid UTF-8 string.

For loads, this file must be an existing file with read permission for the OS user that initiates the load.
For unloads, the parent directory of this file must have read and write permissions for the OS user that initiates the unload, 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 within the /nz directory or its subdirectories because the data object files might accidentally interfere with IBM® Netezza® operations, and they might consume disk space that is needed for the operation of the Netezza database and software.

Manage External Table Locations

Starting in Release 7.1.0.1, the admin user can specify and manage the locations on the IBM Netezza 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 object files.

Note: When you change or restrict the external table locations, the 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 path name.

You use the SHOW EXTERNAL TABLE LOCATION command to display the current table locations. By default, data objects can be created in any of the paths on the Netezza host that are accessible by the nz user account.

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, the admin user or any privileged database user can add and remove table locations using the following steps.

Connect to a Netezza database as the admin user or any database user with Manage System privilege.
Use the SHOW EXTERNAL TABLE LOCATION command to review the current table location path names.
Delete the '*' wildcard location to remove access to all the paths that the nz user can access.
```
TESTDB.ADMIN(ADMIN)=> REMOVE EXTERNAL TABLE LOCATION '*';
REMOVE EXTERNAL TABLE LOCATION
```
Add the locations where the external table objects are allowed using the ADD EXTERNAL TABLE LOCATION command. Any new external tables created on the system must be stored in a permitted directory.
```
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 to or read from the external table.

After you specify the external table locations, you can use the SHOW EXTERNAL TABLE LOCATION command to review the list of supported table locations. After you restrict the external table locations, the restrictions apply when you create new external tables. Any existing external tables continue to use their specified data object locations.

When a user creates an external table and specifies a data object path that is not part of the allowed location list, the command fails with an error:

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'

When a user creates an external table and specifies 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 to the table will fail with a permission error:

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