Load a SQL file using shell comand

Cristobal_Ibarr

Hello Everybody,

 

I write because I have a litle issue.

 

 

I tried to load a sql file using this sentences:

 

/opt/vertica/bin/vsql -f "/root/Desktop/Files/bienes.sql" -U user -w pass

 

Always show this messager but doesn´t load the data in the table:

 

OUTPUT
--------
1
(1 row)

OUTPUT
--------
1
(1 row)

 

 

I tried to insert 1 tuple manually using GUI: Dbeaver

 

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

 

and the data was load.

 

Please anybode can help me whit this ?  in my file.sql I have more than 13mm of tuples and is imposible to load using gui.

 

Please help me.

 

Best Regards

eli_revach

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

Cristobal_Ibarr

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 

eli_revach

vsql -v AUTOCOMMIT=on

Adrian_Oprea_1

 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  

 

Cristobal_Ibarr

Adrian,

 

hank you for your help.

 

Best Regards!

Adrian_Oprea_1

 Was this able to do the trick ? Job ?

 

Happy to help

Cristobal_Ibarr

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. 

Adrian_Oprea_1

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

Cristobal_Ibarr

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...

 

Adrian_Oprea_1

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.

 

Cristobal_Ibarr

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.

 

 

 

Adrian_Oprea_1

 

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.

Cristobal_Ibarr

Problem continue...

Adrian_Oprea_1

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.