In this article, we will discuss about the Netezza external table DATAOBJECT and REMOTESOURCE option and how these options work. You must specify these options in case if you are using separate Linux host or an edge node to connect to Netezza data warehouse appliance.
Edge Node Background
An edge node is something that most of the organisations uses to connect to Netezza server. The edge node will have the all the required scripts to perform the ETL or ELT operations. Normally, edge nodes are installed with JDBC, ODBC or OLEDB drivers, and using those drivers you can connect to Netezza host.
Read:
- Commonly used Netezza Basic Commands
- Netezza Skew and How to Avoid it
- How Netezza Update Records in Table?
- nzsession Command: How to Manage Netezza Sessions
Netezza External Table DATAOBJECT option
DATAOBJECT option usually holds the source file name and is a string representation of a fully qualified source filename that contains data for the external table.
The source file is interpreted differently depending on REMOTESOURCE used in the external table creation script.
Read:
Netezza External Table DATAOBJECT option Syntax
Below is the external table syntax that uses DATAOBJECT syntax:
create external table ext_table
col1 int,
col2 varchar(10)
col3 char(1)
)using(dataobject('/data/table.tbl') remotesource 'odbc' delimiter '~');
Netezza External Table DATAOBJECT Examples
create external table ext_user
user_id int,
name varchar(100)
status char(1)
) using(dataobject('/data/user_list.csv') remotesource 'odbc' delimiter ',');
Note: If REMOTESOURCE is not specified for the external table definition script, then the file specified by DATAOBJECT option is always local to the Netezza host. That is, you are storing the source file directly onto Netezza server.
If REMOTESOURCE is specified, then the filename specified by DATAOBJECT is local to the machine where the external table is being created and referenced. That is, if you have stored the source file on the edge node. In this case, you will not end up with error if miss to mention REMOTESOURCE option.