NZ-Load-Examples.pdf

Publish in

Documents

19 views

Please download to get full document.

View again

of 11
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Share
Description
NZ Load
Tags
Transcript
  NZLoad Working Examples nzload is the utility used by Netezza to load data into tables. Here‘s some working examples.nzload options. Option Information -u Username to access the database -pw Password for the username supplied to get into the database -db Name of the database into which you want to load the data. -t Table name into which the data is to be loaded. -delim The delimiter for the data in the file being loaded. -df The datafile containing the data to be loaded. -cf The file which contains the formatting and option for nvload. Useful if you are going to repeat the command often. nzload alone is powerful enough to load most required data into netezza. for example, for a file containing tab delimited data, use the following options. To load into database prod, using user fred, password barney and table wilma from a tab delimited file called loadme.data, you would use the following: nzload -u fred -pw barney -db prod -t wilma - delim ‗t‘ -df loadme.data. If your file is delimited by a | symbol, you could then use the following to load into table piped table from file datafile.dat nzload -u admin -pw password -db thedatabase - delim ―|‖ -df datafile.dat Using Named Pipes If you are loading a lot of data, and do not have space to keep the data on the system, you can feed it to a named pipe, and feed the command from that. This will not exit until the end of file indicator is given. First, create the pipe file.    mkfifo pipefile  Now you can pipe the data from a supplied file and place it on the pipe.     cat /export/home/nz/dbase/loadme.data > pipefile  Now you specify the pipe file with the -df option to load the data in.    nzload -u admin -pw password -db thedatabase - delim ―|‖ -df pipefile Datafile loadme.dat { Database mydatabase TableName datatable. }  Now you can run that with the following:    nzload -cf loadme.bat Load session of table ‗DATATABLE‘ completed successfully  When you use the nzload command, note that you cannot specify both the -cf and -df options in the same command. You can load from a specified data file, or load from a control file, but not  both in one command. This is because the control file requires a datafile definition, so you cannot specify the file from outside of the controlfile. The following control file options define two data sets to load. Note that the options can vary for each data set.  Datafile /home/operation/data/customer.dat {  Database dev TableName customer  Delimiter ‘|’    Logfile operation.log  Badfile customer.bad  }  Datafile /home/imports/data/inventory.dat {  Database dev TableName inventory  Delimiter ‘#’    Logfile importload.log  Badfile inventory.bad  }  If you save these control file contents as a text file (named import_def.txt in this example) you can specify it using the nzload command as follows: nzload -cf /home/nz/sample/import_def.txt Load session of table ‗CUSTOMER‘ completed successfully   Load session of table ‗INVENTORY‘ completed successfully   View deleted data in Netezza with view_deleted_records. So, Netezza does not delete data until the table has been groomed, they are marked as deleted on the system. So, if they are deleted how are you supposed to see them ? We know the row exists, and it is still there where we have not groomed the table, so here‘s an example of how to view deleted data. LABDB(ADMIN)=> d bod Table ―BOD‖  Attribute | Type | Modifier | Default Value  ———–  +  ——–  +  ———  -+  —————   ID | BIGINT | | Distributed on hash: ―ID‖  LABDB(ADMIN)=> insert into bod values (1); INSERT 0 1 LABDB(ADMIN)=> insert into bod values (2); INSERT 0 1 LABDB(ADMIN)=> delete from bod where id = 1; DELETE 1 LABDB(ADMIN)=> insert into bod values (3); INSERT 0 1 LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod; CREATEXID | DELETEXID | ROWID | ID  ———–  +  ———–  +  ———–  +  —  - 353286 | 0 | 515101001 | 2 353290 | 0 | 515101002 | 3 (2 rows) LABDB(ADMIN)=> set show_deleted_records=true; SET VARIABLE LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod; CREATEXID | DELETEXID | ROWID | ID  ———–  +  ———–  +  ———–  +  —  - 353286 | 0 | 515101001 | 2  353290 | 0 | 515101002 | 3 353284 | 353288 | 515101000 | 1 (3 rows) But updates do the same, don‘t they ? An updated row is logically updated but physically deleted and recreated. LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod; CREATEXID | DELETEXID | ROWID | ID | JUNK  ———–  +  ———–  +  ——  -+  —  -+  ——   (0 rows) LABDB(ADMIN)=> insert into bod values (1, null); INSERT 0 1 LABDB(ADMIN)=> insert into bod values (2, null); INSERT 0 1 LABDB(ADMIN)=> update bod set junk = ‗TWO‘ where id = 2;  UPDATE 1 LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod order by createxid; CREATEXID | DELETEXID | ROWID | ID | JUNK  ———–  +  ———–  +  ———–  +  —  -+  ——   353362 | 0 | 515102002 | 1 | 353364 | 353366 | 515102003 | 2 | 353366 | 0 | 515102003 | 2 | TWO (3 rows) This shows that the row has NOT been updated, but actually inserted and the old record marked as deleted. There is a gotcha here, the view_deleted_records does not work in exactly the way that you would expect. LABDB(ADMIN)=> select createxid, deletexid, rowid, * from bod order by createxid, rowid; CREATEXID | DELETEXID | ROWID | ID | JUNK  ———–  +  ———–  +  ———–  +  —  -+  ——   353284 | 353288 | 515101000 | 1 | 353286 | 0 | 515101001 | 2 | 353290 | 0 | 515101002 | 3 | (3 rows) This is fine. Let‘s now update the table.   LABDB(ADMIN)=> update bod set junk = ‗ONE‘ where id = 1;  ERROR: 0×56408 : Concurrent update or delete of same row Well, that would be silly that you could actually update a deleted row in a table.
Related Search

Previous Document

Muhammad Ali Cv

We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks