Load a SQL file using shell comand

Hi ,
Vsql do not set autocommit on be default
Add commit to your sql file (right after the insert command ) and you should be fine
I hope you will find it useful
Thanke

Thank you Eli_revach for you reply.

Can I set autocommit Vsql to default ?  I have 13k of inserts.

If you know, please let me know.

Best regards 

vsql -v AUTOCOMMIT=on

 Just to put in my two cents ! 

Why would you use insert satements ? 

Vertica is not build to deal with this types of statements in an optimized manner ! 

Transform your insert statements into a csv file i garantee you will get maybe 1000x better performance.

 Example :

cat test.csv
INSERT INTO DW.bienes(x1,x2,x3,x4,x5,X,Y,x6) VALUES (12345,'blah','lala','lalaa',asdf,NULL,NULL,NULL);

Make it CSV format: comma delimited:

sed -i 's/INSERT INTO DW.bienes(x1,x2,x3,x4,x5,X,Y,x6) VALUES (/\x27/g'  /root/Desktop/Files/bienes.sql
sed -i 's/);/\x27/g'  /root/Desktop/Files/bienes.sql

 The output should be a csv content:

'12345,'blah','lala','lalaa',asdf,NULL,NULL,NULL'

To load the data with copy when you have all columns in the talbe in the csv files as well:

copy DW.bienes FROM /root/Desktop/Files/bienes.sql delimiter ',' NULL AS 'NULL' abort on error no commit;

To load data by skipping column :

- lets say you have 12 col in your table and only provide 8 col in you csv file.

- put data into fillers then associate filers with the cols you wanna populate.

- good to remenber that columns that wont be populated need to have null enabled or a default value in their definition. 

copy DW.bienes(
x1_fill FILLER int,
x2_fill FILLER VARCHAR(10), 
x3_fill FILLER VARCHAR(10),
x4_fill FILLER VARCHAR(10),
x5_fill FILLER VARCHAR(10),
x6_fill FILLER VARCHAR(10),
x7_fill FILLER VARCHAR(10),
x8_fill FILLER VARCHAR(10),
x1 AS x1_fill
,x2 AS x2_fill
,x3 AS x3_fill
,x4 AS x4_fill
,x5 AS x5_fill
,X AS x6_fill
,Y AS x7_fill
,x6 AS x8_fill

)
 FROM /root/Desktop/Files/bienes.sql delimiter ',' NULL AS 'NULL' abort on error no commit;

Hope this helps !  

Use Vertica for what it is . Fast loading/ Fast Querying ! and leave the OLTP stuff to OLTP db server types  

Adrian,

hank you for your help.

Best Regards!

 Was this able to do the trick ? Job ?

Happy to help

Adrian,

How can I run this command: 

copy DW.bienes FROM /root/Desktop/Files/bienes.sql delimiter ',' NULL AS 'NULL' abort on error no commit;

Using VSQL ?

Please advices. 

Run this command :

vsql -h 11.11.11.121 -d dbname -U dbadmin -w password -c 
 "copy DW.bienes FROM /root/Desktop/Files/bienes.sql delimiter 
',' NULL AS 'NULL' abort on error no commit;"

-- increment your commnd with this , it will help you to fix/ track bad records
STREAM NAME 'Loading into table bla bla ' REJECTED DATA AS TABLE public.table_bla_rejects;

- just edit your ip  dbname and password

vsql -h 11.11.11.121 -d dbname -U dbadmin -w password -c 
 "copy DW.bienes FROM /root/Desktop/Files/bienes.sql delimiter 
',' NULL AS 'NULL' abort on error no commit;"

I received the following error:

 ERROR 4856: Syntax error at or near "/" at character 30
LINE 1: copy DW.bienes.sql FROM /root/Escritorio/dw/bienes.sql...

You need ot emmbed the file location in ' '

Eg:

vsql -h 11.11.11.121 -d dbname -U dbadmin -w password -c 
 "copy DW.bienes FROM '/root/Desktop/Files/bienes.sql' delimiter 
',' NULL AS 'NULL' abort on error no commit;"

- make sure dbadmin has rights to read from the file.

Now, I received the following error:

ERROR 2886: Could not open file [/root/Escritorio/dw/bienes.sql] for reading; Permission denied
HINT: Make sure the file is available on the specified node. 
If using ON ANY NODE, it must be available at this path on all nodes in the cluster since any node could load the file

Please advice.

The file must be on one of the nodes.

Move the file into a direcotry where dbadmin has access , eg : /tmp

If so logged as root run the command below:

chown -R dbadmin:verticadba /root/Escritorio/dw/bienes.sql

- then run again the copy cmd.

Problem continue...

Ok ?!

run this :

mv /root/Escritorio/dw/bienes.sql /tmp/bienes.sql
chown -R dbadmin:verticadba /tmp/bienes.sql
vsql -h 11.11.11.121 -d dbname -U dbadmin -w password -c 
 "copy DW.bienes FROM '/root/Desktop/Files/bienes.sql' delimiter 
',' NULL AS 'NULL' abort on error no commit;"

Note:

make sure you are connected to the vertica node where the ip is !! if you are so you can get rid of the ip address form the cmd striing.