Avatar of Debbie Cooper
Debbie CooperFlag for United States of America

asked on 

Netezza SQL limit number of rows imported

I have been given a massive text file (40gb) that I need to process in Netezza. I figured I could import the data in pieces (say 10000 rows at a time), run my logic against that data which filters out a lot of rows and keep that filtered data in a table that I can append to.  I've tried the Import Wizard but I don't see any way of limiting the number of rows.  I've also tried the following:
CALL DROP_IF_EXISTS('IMPORTED_DATA');
 CREATE TABLE IMPORTED_DATA
 (
   MEMBER_ID        VARCHAR (11),
   AGE_BUCKET       VARCHAR (7),
   ZIPCODE          VARCHAR (5),
   CLM_NBR          VARCHAR (10),
   DTL_LN_NBR       VARCHAR (3),
   FST_SRVC_DT      DATE,
   LST_SRVC_DT      DATE,
 )


insert into imported_data
select * from
external 'Q:\Dept\\data\\folder\\huge_file.txt'
USING(
DELIMITER '|'
MAXROWS 10
REMOTESOURCE 'ODBC'
DATESTYLE 'DMONY'
MAXERRORS 200
Y2BASE 2000
ENCODING 'internal'
);
This might be working but it's taking forever so I'm wondering if the maxrows is working.  Can I do something like this with the insert statement:

insert into imported_data
select * from
external 'Q:\Dept\\data\\folder\\huge_file.txt'
USING(
DELIMITER '|'
MAXROWS 10
REMOTESOURCE 'ODBC'
DATESTYLE 'DMONY'
MAXERRORS 200
Y2BASE 2000
ENCODING 'internal'
) limit 10;
Or if anyone can point me to other ways to read this data in that would be very helpful.  Thanks
SQL* Netezza SQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Debbie Cooper

ASKER

Thanks, I found a Powershell script on the web called split-file.ps1.  I tried to run it after I typed set-executionpolicy remotesigned from the powershell window but it looks like I don't have permissions.  I'm going to look into that.  Thanks
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Personally, I would look at nzload first.

I know from Oracle and its tools, they have a sqlldr utility that rips through flat files to load data.
SQL
SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo