Vertica Vsql command line examples

First of many tutorials on how we can write administration scripts using the VSQL command line tool and its options.

We will start with basic options: Connect to the database :

    [dbadmin@Vertica_Master1 /]$ vsql -Uusername -wpassword
    Welcome to vsql, the Vertica Analytic Database interactive terminal.
    Type: h for help with SQL commands
    ? for help with vsql commands
    g or terminate with semicolon to execute query
    q to quit
    dbadmin:
Download Vertica Free Hardware Setup Manual from here:

Vertica Free Hardware Setup Manual How to Connect to a specific node/host form the command line Where "-h" option will be followed by host name or ip address. 
    [dbadmin@Vertica_Master1 /]$ vsql -hVertica_Master2 -Udbadmin -wdbadmin
    Welcome to vsql, the Vertica Analytic Database interactive terminal.
    Type: h for help with SQL commands
    ? for help with vsql commands
    g or terminate with semicolon to execute query
    q to quit
    dbadmin: select node_name,user_name from sessions;
    node_name | user_name
    --------------------+-----------

    v_cluster_node0002 | dbadmin
    (1 row)
     dbadmin:
Now change the node/host Where "-h" option will be follwed by host name or ip address. 
    [dbadmin@Vertica_Master1 /]$ vsql -hVertica_Master1 -Udbadmin -wdbadmin
    Welcome to vsql, the Vertica Analytic Database interactive terminal.
    Type: h for help with SQL commands
    ? for help with vsql commands
    g or terminate with semicolon to execute query
    q to quit
    dbadmin= select node_name,user_name from sessions;
    node_name | user_name
    --------------------+-----------

    v_cluster_node0001 | dbadmin
    (1 row)
    dbadmin=
Connect to specific database: Where "-d" option will be followd by database name. 
    [dbadmin@Vertica_Master1 /]$ vsql -d dbaname -Udbadmin -w dbadmin
Run a single command from the vsql line: Where "-c" option will be followd by the command you want to be executed. -command will execute and exit 
    [dbadmin@Vertica_Master1 /]$ vsql -Udbadmin -wdbadmin -c 'select database_name from databases';
    database_name
    ---------------

    cluster
    (1 row)
    [dbadmin@Vertica_Master1 /]$
Redirect output to a file 
    vsql -Udbadmin -wdbadmin -c 'select database_name from databases'  file location/file name ;
Execute commands from file, then exit 
    [dbadmin@Vertica_Master1 tmp]$ vsql -U dbadmin -w dbadmin -f test
    database_name
    ---------------

    cluster
    (1 row)
How to List all available databases from vsql. 
    [dbadmin@Vertica_Master1 tmp]$ vsql -U dbadmin -w dbadmin -l

    List of databases
       name | user_name
   ---------+-----------

    cluster | dbadmin
    (1 row)
This are just few of the many examples of how Vsql command line can be used.

Copyright 2020. All Rights Reserved