load a .sql file into vertica

anmol_koul

Guys,

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?

regards,
Anmol
http://www.mysqltutorial.org/wp-content/uploads/downloads/2013/05/mysqlsampledatabase1.zip

[Deleted User]

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.

Thanks,
Pravesh

Navin_C

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

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/ProgrammersGuide/vsql/CommandLineOptions....

3. For more help on vsql

dbadmin=> \h
See the Vertica Programmer's Guide for information on available commands.
General
  \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

Input/Output
  \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

 

Adrian_Oprea

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 !