Options

Load a SQL file using shell comand

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

Comments

  • Options

    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

  • Options

    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 

  • Options
    vsql -v AUTOCOMMIT=on
  • Options

     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 :) 

     

  • Options

    Adrian,

     

    hank you for your help.

     

    Best Regards!

  • Options

     Was this able to do the trick ? Job ?

     

    Happy to help :)

  • Options

    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. 

  • Options

    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

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

     

  • Options

    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.

     

  • Options

    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.

     

     

     

  • Options

     

    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.

  • Options

    Problem continue...

  • Options

    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.

     

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file