How to Bulk Import data from file into Netezza DB?

Question

I want to bulk import data from a file present on my local into the Netezza Database using NZSQL language.

I tried with below query but, I am not able to do it.

create external table ext_tab (
login_email VARCHAR(10),identifier int,first_name VARCHAR(10),last_name VARCHAR(10)
)
USING (
DATAOBJECT('C:\Business\Imp Links\Netezza\Bulk Dir\email.csv') 
REMOTESOURCE 'jdbc'
DELIMITER ';'
SKIPROWS 1
MAXERRORS 1000
LOGDIR 'C:\Business\Imp Links\Netezza\Bulk Dir\Logs' );

create table email_details as select * from ext_tab;

This is my csv file named email.csv -

Login email;Identifier;First name;Last name
[email protected];2070;Laura;Grey
[email protected];4081;Craig;Johnson
[email protected];9346;Mary;Jenkins
[email protected];5079;Jamie;Smith 

When I try to run this below command it gives error -

create table email_details as select * from ext_tab;

Error -

[Code: 1100, SQL State: HY000]  ERROR:  Transaction rolled back by client

I am not able to bulk import data into Netezza database hence, please guide me.

Tried with another below query, still throwing another error -

CREATE EXTERNAL TABLE my_external_table_new (
login_email VARCHAR(10),identifier int,first_name VARCHAR(10),last_name 
VARCHAR(10)
)
USING (DATAOBJECT ('C:\Business\Imp Links\Netezza\Bulk 
Dir\email_1.csv')
DELIMITER ','
REMOTESOURCE 'JDBC'
ESCAPECHAR '\' );
   
CREATE TABLE my_table_new (
login_email VARCHAR(10),identifier int,first_name VARCHAR(10),last_name 
VARCHAR(10)
)

insert into my_table_new (login_email, identifier, first_name, 
last_name)
select login_email, identifier, first_name, last_name
from my_external_table_new;

Error -

[Code: 1100, SQL State: HY000] ERROR: Remotesource option of external table was not defined to load/unload using a jdbc/dotnet client

Answer 1

In the log directory you should be able to find two files one with the extension .nzlog and the other with the extension .nzbad. the nzlog will tell you things like how many rows were loaded, how many were rejected, etc. the .nzbad file will contain all the records that failed to load. Your example fails to load because your first column 'login_email VARCHAR(10)' is not large enough to contain these email addresses. in my .nzlog file I see these errors

1: 2(10) [1, VARCHAR(10)] text field too long for column, "laura@exam"[p]
2: 3(10) [1, VARCHAR(10)] text field too long for column, "craig@exam"[p]
3: 4(10) [1, VARCHAR(10)] text field too long for column, "mary@examp"[l]
4: 5(10) [1, VARCHAR(10)] text field too long for column, "jamie@exam"[p]

you should retry with a larger size. if you hit more than the MAXERRORS argument you're setting that the transaction will roll back. for example, below you'll see that if I set maxerrors to 1 and try with this small amount of sample data the transaction will fail.

SYSTEM.ADMIN(ADMIN)=> create external table ext_tab2 ( login_email VARCHAR(10), identifier int, first_name VARCHAR(10), last_name VARCHAR(10)) using (dataobject('/tmp/exttbl') remotesource 'nzsql' delimiter ';' skiprows 1 maxerrors 1 logdir '/tmp');
CREATE EXTERNAL TABLE
SYSTEM.ADMIN(ADMIN)=> select * from ext_tab2;
ERROR:  External Table : count of bad input rows reached maxerrors limit
SYSTEM.ADMIN(ADMIN)=> create table example as select * from ext_tab2;
ERROR:  External Table : count of bad input rows reached maxerrors limit
SYSTEM.ADMIN(ADMIN)=> select * from example;
ERROR:  relation does not exist SYSTEM.ADMIN.EXAMPLE

If I increase the column size for email everything works

SYSTEM.ADMIN(ADMIN)=> create external table ext_tab3 ( login_email VARCHAR(50), identifier int, first_name VARCHAR(10), last_name VARCHAR(10)) using (dataobject('/tmp/exttbl') remotesource 'nzsql' delimiter ';' skiprows 1 maxerrors 1000 logdir '/tmp');
CREATE EXTERNAL TABLE
SYSTEM.ADMIN(ADMIN)=> create table example3 as select * from ext_tab3;
INSERT 0 4
SYSTEM.ADMIN(ADMIN)=> select * from example3;
    LOGIN_EMAIL    | IDENTIFIER | FIRST_NAME | LAST_NAME 
-------------------+------------+------------+-----------
 [email protected] |       2070 | Laura      | Grey
 [email protected] |       4081 | Craig      | Johnson
 [email protected]  |       9346 | Mary       | Jenkins
 [email protected] |       5079 | Jamie      | Smith
(4 rows)