load a .sql file into vertica


How do i load the schema, data and all other constraints from a .sql file.
I am trying to install the mysql sample database file which is in a .sql format.

While i was installing VMart database, i saw a lot of sql files which i guess were being called by the script. 

How do i do it manually?



  • Options
    Hi Anmol,

    Following syntax should help you:

    1. From linux shell:

    /opt/vertica/bin/vsql -f "./test.sql" -U dbadmin -w password

    2. From VSQL:

    vsql=> \i '/home/dbadmin/test.sql'

    Thee examples assumes that sql file is named as "test.sql". Hope this helps.

  • Options
    Navin_CNavin_C Vertica Customer
    Hi Anmol,

    The following can be achieved using vsql utility by Vertica.

    To work with vsql

    1.If you are a dbadmin user, you can do a man command for vsql.
    man vsql
    2.For more help on working with vsql


    3. For more help on vsql
    dbadmin=> \h
    See the Vertica Programmer's Guide for information on available commands.
      \c[onnect] [DBNAME|- [USER]]
                     connect to new database (currently "dbadmin")
      \cd [DIR]      change the current working directory
      \q             quit vsql
      \set [NAME [VALUE]]
                     set internal variable, or list all if no parameters
      \timing        toggle timing of commands (currently on)
      \unset NAME    unset (delete) internal variable
      \! [COMMAND]   execute command in shell or start interactive shell
      \password [USER]
                     change user's password

      \echo [STRING] write string to standard output
      \i FILE        execute commands from file
      \o FILE        send all query results to file
      \o | COMMAND   pipe all query results to command
      \o             close query-results file or pipe
      \qecho [STRING]
                     write string to query output stream (see \o)

    Hope this helps


  • Options
    Before running the script make sure you alter the content of the mysqlsampledatabase.sql script to be accepted by Vertica, you have plenty terms and data types that will not work in Vertica ! 
    As for the data i suggest you dump it into a csv file and load it via copy command ! 

Leave a Comment

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