on 08-30-2012 7:13 AM
Hi All, I'm importing large text files from the local HANA server file system and it's working well except the remaining records beyond an even multiple of the batch size (total_records mod batch_size) are not being committed. For example my test table has 809,159 rows. If I specify 1,000 as the batch size I get 809,000 rows committed, 100,000 gives me 800,000 etc. If I specify exactly 809,159 as the batch size, I get 809,159 committed. Here is my code with an example batch size of 100000:
IMPORT FROM CSV FILE '/dropbox/my-utf-8-data.csv' INTO MYSCHEMA.MYTABLE WITH
RECORD DELIMITED BY '\n'
FIELD DELIMITED BY ','
BATCH 100000;
my-utf-8-data.csv contains 809,159 rows, but the above command commits only 800.000.
Other points:
I'm sorry if I'm missing something obvious. Thanks in advance for any assistance you may be able to provide.
I can't imagine the behaviour above is 'by design', but just in case anybody else is having trouble with this, I have a couple of short-cuts. Basically, we count the number of lines in the text file and set that (or a multiple) as the batch size. Painful, yes. Error prone, yes. But nothing beats the performance of server-side import for bulk loads.
To get the number of lines in a file (you need to be logged-on to the Linux server) where the file name is my-utf-8-data.csv:
# wc -l my-utf-8-data.csv
809154 my-utf-8-data.csv
Obviously you need to be very careful about header rows and trailing empty lines. Here is a way to quickly peek at the last 11 lines of a large text file:
# sed -e :a -e '$q;N;11,$D;ba' my-utf-8-data.csv
27137323,14639241,1,281586,0,2912,NULL
27137325,14639243,2,,2,2250,A
...
Then check the number of rows committed meticulously!
Good luck.
-Leuis
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.