2.2 GB of inserts.sql How to insert them.

Moderator: NorbertKrupa

Post Reply
Benjamin_e
Newbie
Newbie
Posts: 15
Joined: Tue Aug 26, 2014 1:43 pm

2.2 GB of inserts.sql How to insert them.

Post by Benjamin_e » Tue Nov 25, 2014 11:46 pm

Hey, I have 2.2GB sql file with planty of inserts.
How do I Move it into the table?
When i try \i x.sql i get the message that said the file is too large.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1823
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: 2.2 GB of inserts.sql How to insert them.

Post by JimKnicely » Wed Nov 26, 2014 12:07 am

Interesting. What is the message exactly?

You could split the file into smaller files in Linux... Here is an example that creates smaller files having 2 lines each:

Code: Select all

[dbadmin@vertica01 jim]$ ls
x.sql
[dbadmin@vertica01 jim]$ cat x.sql
insert into jim values (1);
insert into jim values (1);
insert into jim values (1);
insert into jim values (1);
insert into jim values (1);
insert into jim values (1);
insert into jim values (1);
insert into jim values (1);
insert into jim values (1);
insert into jim values (1);
[dbadmin@vertica01 jim]$ split -l 2 -d -a 3 x.sql x.sql
[dbadmin@vertica01 jim]$ ls
x.sql  x.sql000  x.sql001  x.sql002  x.sql003  x.sql004
[dbadmin@vertica01 jim]$ cat x.sql000
insert into jim values (1);
insert into jim values (1);
You may want to do 1000 inserts at a time in each file.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: 2.2 GB of inserts.sql How to insert them.

Post by NorbertKrupa » Wed Nov 26, 2014 12:49 am

Benjamin_e wrote:Hey, I have 2.2GB sql file with planty of inserts.
How do I Move it into the table?
When i try \i x.sql i get the message that said the file is too large.
Is this a one time insert? You should really try to avoid having so many single INSERTs.
Checkout vertica.tips for more Vertica resources.

Benjamin_e
Newbie
Newbie
Posts: 15
Joined: Tue Aug 26, 2014 1:43 pm

Re: 2.2 GB of inserts.sql How to insert them.

Post by Benjamin_e » Wed Nov 26, 2014 7:06 am

After review the docs a out transfering I Modify it. the script has 10m inserta. Its planty even to split them. I manipulate the file to cs file delimited by ',' now i will use the opy command to move it.


Thank you.

Post Reply

Return to “Vertica Data Load”