input query from file output result to file

I have a complex query I am testing some performance issues against, I want to load the query from file and be able to explain the query, profile the query etc but I also want to output the result from the query / explain plan to another file for comparisons


  • Abhishek_RanaAbhishek_Rana Vertica Employee Employee
    Hi Steve,

    Below are two options for your requirement:


     To execute commands from a specific file: 

    \i <filename>

    This command reads input from the file filename and executes it as though it had been typed on the keyboard.

    dbadmin=> \i query1.sql 


     To create a file of output of commands: 

    \o meta-command is used to control where vsql directs its query output. 

    The output can be written to a file, piped to a shell command, or sent to the standard output.
    \o FILE sends all subsequent query output to FILE.
    dbadmin> \o a.out
    dbadmin> execute set of queries .....
    dbadmin> \o


  • Hi Abhishek, yep I know how to get the individual bits to work what I want to do is do it in one command - is this possible ?

    I thought as follows would work but it didn't seem to:

    dbadmin> \i Input_Query.sql \o output_result.txt

    With above all i got was a blank output file ..

  • Abhishek_RanaAbhishek_Rana Vertica Employee Employee
    I think you can use below small workaround trick for this:

    dbadmin=> cat a.sql | vsql -p 5433 -w password -o out.txt

    where , a.sql is sql file.
    And      out.txt is file which stores the output.


  • >> is this possible ?
    Everything is possible if to think a little, just change an order of meta commands:
    dbadmin=> \o output_result.txt \i Input_Query.sql
    daniel=> \! ls -l /tmp/out.csv
    ls: cannot access /tmp/out.csv: No such file or directory

    daniel=> \! cat /tmp/query.sql
    select * from visits limit 3;

    daniel=> \o /tmp/out.csv \i /tmp/query.sql
    daniel=> \! cat /tmp/out.csv
             in          |         out         | person 
     2013-11-01 22:52:34 | 2013-11-01 23:07:24 | Jager
     2013-11-02 22:55:22 | 2013-11-02 23:04:36 | James
     2013-11-03 22:03:45 | 2013-11-03 23:56:13 | Jager
    (3 rows)


Leave a Comment

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