This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Connecting from the command line

You can connect to a database using vsql from the command line on multiple client platforms.

You can connect to a database using vsql from the command line on multiple client platforms.

If the connection cannot be made for any reason—for example, you have insufficient privileges, or the server is not running on the targeted host—vsql returns an error and terminates.

Syntax

/opt/vertica/bin/vsql [-h host] [ -p port ] [ option...] [ dbname [ username ] ]

Parameters

host
Optional if you connect to a local server. You can provide an IPv4 or IPv6 IP address or a host name.

For Vertica servers that have both IPv4 and IPv6 addressed and you have provided a host name instead of an IP address, you can prefer to use an IPv4 address with the -4 option and to use the IPv6 adddress with the -6 option if the DNS is configured to provide both IPv4 and IPv6 addresses. If you are using IPv6 and provide an IP address, you must append the address with an %interface name.

port
The database server port.

Default: 5433

option
One or more vsql command-line options.

If the database is password protected, you must specify the -w or --password command line option.

dbname
The name of the target database. If unspecified, vsql automatically connects to the database on the specified host and port.
username
A database username, by default your system username.

Exit codes

vsql returns 0 to the shell when it terminates normally. Otherwise, it returns one of the following:

  • 1: A fatal error occurred—for example, out of memory or file not found.

  • 2: The connection to the server went bad and the session was not interactive

  • 3: An error occurred in a script and the variable ON_ERROR_STOP was set.

  • Unrecognized words in the command line might be interpreted as database or user names.

Examples

The following example shows how to capture error messages by redirecting vsql output to the output file retail_queries.out:

$ vsql --echo-all < retail_queries.sql > retail_queries.out 2>&1

1 - Command-line options

This section contains the command-line options for vsql.

This section contains the command-line options for vsql.

General options

--command command
-c command
Runs one command and exits. This command is useful in shell scripts.

Variables set with -v are not processed when referenced in a -c command. To use variables, create a .sql file that references the variable and pass it to vsql with the -f option.

--dbname dbname
-d dbname
Specifies the name of the database to which you want to connect. Using this command is equivalent to specifying dbname as the first non-option argument on the command line.
--file filename
-f filename
Uses the filename as the source of commands instead of reading commands interactively. After the file is processed, vsql terminates.
--help
Displays help about vsql command line arguments and exits.
--timing
-i
Enables the \timing meta-command.
--list
-l
Returns all available databases, then exits. Other non-connection options are ignored. This command is similar to the internal command \list.
--set assignment
--variable assignment
-v assignment
Performs a variable assignment, like the vsql command\set.
--version -V
Prints the vsql version and exits.
--no-vsqlr
-X
Disables all command line editing and history functionality.

Connection options

-4
When resolving hostnames in dual stack environments, prefer IPv4 addresses.
-6
When resolving hostnames in dual stack environments, prefer IPv6 addresses.
-B server:port[,...]
Sets connection backup server/port. Use comma-separated multiple hosts (default: not set). If using an IPv6 address, enclose the address in brackets ([, ]) and place the port outside of the brackets. For example \B [2620:0:a13:8a4:9d9f:e0e3:1181:7f51]:5433
--enable-connection
-load-balance -C
Enables connection load balancing (default: not enabled).
--host hostname
-h hostname
Specifies the host name of the machine on which the server is running.
-k krb-service
Provides the service name portion of the Kerberos principal (default: vertica). Using -k is equivalent to using the drivers' KerberosServiceName connection string.
-K krb-host
Provides the instance or host name portion of the Kerberos principal. -K is equivalent to the drivers' KerberosHostName connection string.
-g client-label
--label client-label
Sets the client label for the connection.
--sslmode
-m
Specifies the policy for making SSL connections to the server. Options are require, prefer, allow, and disable. You can also set the VSQL_SSLMODE variable to achieve the same effect. If the variable is set, the command-line option overrides it.
--port port
-p port
Specifies the TCP port or the local socket file extension on which the server is listening for connections. Defaults to port 5433.
`--username` *`username`*
`-U` *`username`*
Connects to the database as the user username instead of the default.
-w password
Specifies the password for a database user.
--password
-W
Forces vsql to prompt for a password before connecting to a database.The password is not displayed on the screen. This option remains set for the entire session, even if you change the database connection with the meta-command \connect.

Output formatting

--no-align
-A
Switches to unaligned output mode. (The default output mode is aligned.)
-b
Beep on command completion.
--field-separator separator
-F separator
Specifies the field separator for unaligned output (default: "|") (-P fieldsep=). (See -A --no-align.) Using this command is equivalent to \pset fieldsep or \f.
--html
-H
Turns on HTML tabular output. Using this command is equivalent to using the \pset format html or the \H command.
--pset assignment
-P assignment
Lets you specify printing options in the style of \pset on the command line. You must separate the name and value with an equals (=) sign instead of a space. Thus, to set the output format to LaTeX, you could write -P format=latex.
-Q
Turns on trailing record separator. Use \pset trailingrecordsep to toggle the trailing record separator on or off.
--record-separator separator
-R separator
Uses separator as the record separator. Using this command is equivalent to using the \pset recordsep command.
--tuples-only
-t
Disables printing of column names, result row count footers, and so on. This is equivalent to the vsql meta-command \t.
--table-attr options
-T options
Allows you to specify options to be placed within the HTML table tag. See \pset for details.
--expanded
-x
Enables extended table formatting mode. This is equivalent to the vsql meta-command \x.

Input and output options

--echo-all
-a
Prints all input lines to standard output as they are read. This approach is more useful for script processing than interactive mode. It is the same as setting the variable ECHO to all.
--echo-queries
-e
Copies all SQL commands sent to the server to standard output. Using this command is equivalent to setting the variable ECHO to queries.
-E
Displays queries generated by internal commands.
-n
Disables command line editing.
--output filename
-o filename
Writes all query output to filename. Using this command is equivalent to using the vsql meta-command \o.
--quiet
-q
Specifies that vsql do its work quietly (without informational output, such as welcome messages). This command is useful with the -c option. Within vsql you can also set the QUIET variable to achieve the same effect.
--single-step
-s
Runs in single-step mode for debugging scripts. Forces vsql to prompt before each statement is sent to the database and allows you to cancel execution.
--single-line -S
Runs in single-line mode where a newline terminates a SQL command, as if you are using a semicolon.

1.1 - -A --no-align

-A or --no-align switches to unaligned output mode.

-A or --no-align switches to unaligned output mode. The default output mode is aligned.

1.2 - -a --echo-all

-a or --echo-all prints all input lines to standard output as they are read.

-a or --echo-all prints all input lines to standard output as they are read. This is more useful for script processing than interactive mode. It is equivalent to setting the variable ECHO to all.

1.3 - -c --command

-c command or--command command runs one command and exits.

-c command or--command command runs one command and exits. This is useful in shell scripts.

Use either:

  • A command string that can be completely parsed by the server that does not contain features specific to vsql

  • A single meta-command

You cannot mix SQL and vsql meta-commands. You can, however, pipe the string into vsql as shown:

echo "\\timing\\\\select * from t" | ../Linux64/bin/vsql
                Timing is on.
                i | c | v
                ---+---+---
                (0 rows)

1.4 - -d --dbname

-d db-name or --dbname db-name specifies the name of the database to connect to.

-d db-name or --dbname db-name specifies the name of the database to connect to. This is equivalent to specifying db-name as the first non-option argument on the command line.

1.5 - -E

-E displays queries generated by internal commands.

-E displays queries generated by internal commands.

1.6 - -e --echo-queries

-e --echo-queries copies all SQL commands sent to the server to standard output as well.

-e --echo-queries copies all SQL commands sent to the server to standard output as well. This is equivalent to setting the variable ECHO to queries.

1.7 - -r --workload

-r --workload lets you specify the name of a workload. If a routing rule is associated with that workload, your connection is routed to the subcluster reserved for that workload. For details, see Workload routing.

You can also specify a workload with the VSQL_WORKLOAD environment variable. For details, see vsql environment variables.

1.8 - -F --field-separator

-F separator or --field-separator separator specifies the field separator for unaligned output (default: "|") (-P fieldsep=).

-F separator or --field-separator separator specifies the field separator for unaligned output (default: "|") (-P fieldsep=). (See -A --no-align.) This is equivalent to \pset fieldsep or \f.

To set the field separator value to a control character, use your shell's control character escape notation. In Bash, you specify a control character in an argument using a dollar sign ($) followed by a string contained in single quotes. This string can contain C-string escapes (such as \t for tab), or a backslash () followed by an octal value for the character you want to use.

The following example demonstrates setting the separator character to tab (\t), vertical tab (\v) and the octal value of vertical tab (\013).

$ vsql -At -c "SELECT * FROM testtable;"
A|1|2|3
B|4|5|6

$ vsql -F $'\t' -At -c "SELECT * FROM testtable;"
A       1       2       3
B       4       5       6

$ vsql -F $'\v' -At -c "SELECT * FROM testtable;"
A
 1
  2
   3
B
 4
  5
   6
$ vsql -F $'\013' -At -c "SELECT * FROM testtable;"
A
 1
  2
   3
B
 4
  5
   6

1.9 - -f --file

-f filename or --file filename uses filename as the source of commands instead of reading commands interactively.

-f filename or --file filename uses filename as the source of commands instead of reading commands interactively. After the file is processed, vsql terminates.

If filename is a hyphen (-), standard input is read.

Using this option is different from writing vsql < filename. Using -f enables some additional features such as error messages with line numbers. Conversely, the variant using the shell's input redirection should always yield exactly the same output that you would have gotten had you entered everything manually.

1.10 - ? --help

-? --help displays help about vsql command line arguments and exits.

-? --help displays help about vsql command line arguments and exits.

1.11 - -H --html

-H --html turns on HTML tabular output.

-H --html turns on HTML tabular output. This is equivalent to \pset format html or the \H command.

1.12 - -h --host

-h hostname or --host hostname specifies the host name of the machine on which the server is running.

-h hostname or --host hostname specifies the host name of the machine on which the server is running. Use this flag to connect to Vertica remotely.

The following requirements and restrictions apply:

  • If you use client authentication with a Kerberos connection method of either gss or krb5, you must specify -h hostname.

  • Use the -h option if you want to connect to Vertica from a local connection, but want to use the an authentication record with the access method HOST (rather than LOCAL).

1.13 - -i -- timing

Enables the \timing meta-command.

Enables the \timing meta-command. You can only use this command with the -c --command and -f --file commands:

$VSQL -h host1 -U user1 -d VMart -p 15 -w ****** -i -f transactions.sql

You can only use -i with the -c (command) and -f (filename) commands. For more information see Command-line options.

From the command line enter the -i option before running a session to turn timing on. For example:

$VSQL -h host1 -U user1 -d VMart -p 15 -w ****** -i -f transactions.sql
$VSQL-h host1 -U user1 -d VMart -p 15 -w ****** -i -c "SELECT user_name,
ssl_state, authentication_method, client_authentication_name, client_type FROM sessions
WHERE session_id=(SELECT session_id FROM current_session);"

1.14 - -g --label

Assigns a client label to the connection at the start of the session.

Assigns a client label to the connection at the start of the session. Client connections and their labels appear in the SESSIONS and some Data collector tables like DC_REQUESTS_ISSUED. Client labels set with this option appear in DC_SESSION_STARTS.

If used, this option takes precedence over the VSQL_CLIENT_LABEL environment variable.

To set client labels for ongoing sessions, use SET_CLIENT_LABEL.

1.15 - -l --list

-l or --list returns all available databases, then exits.

-l or --list returns all available databases, then exits. Other non-connection options are ignored. This command is similar to the internal command \list.

1.16 - -m --sslmode

-m or --sslmode specifies the policy for making SSL connections to the server.

-m or --sslmode specifies the policy for making SSL connections to the server. Options are verify-full, verify-ca require, prefer, and disable. You can also set the VSQL_SSLMODE variable to achieve the same effect. If the variable is set, the command-line option overrides it.

For information on these modes see Configuring TLS for ODBC Clients.

1.17 - -n

-n disables command line editing.

-n disables command line editing.

1.18 - -o --output

-o filename or --output filename writes all query output into file filename.

-o filename or --output filename writes all query output into file filename. This is equivalent to the vsql meta-command \o.

1.19 - -P --pset

-P assignment or --pset assignment lets you specify printing options in the style of \pset on the command line.

-P assignment or --pset assignment lets you specify printing options in the style of \pset on the command line. Note that you have to separate name and value with an equal sign instead of a space. Thus to set the output format to LaTeX, you could write -P format=latex.

1.20 - -p --port

-p port or--port port specifies the TCP port or the local socket file extension on which the server is listening for connections.

-p port or--port port specifies the TCP port or the local socket file extension on which the server is listening for connections. Defaults to port 5433.

1.21 - -q --quiet

-q or --quiet specifies that vsql do its work quietly.

-q or --quiet specifies that vsql do its work quietly. By default, it prints welcome messages and various informational output. If this option is used, none of this appears. This is useful with the -c option. Within vsql you can also set the QUIET variable to achieve the same effect.

1.22 - -R --record-separator

-R separator or --record-separator separator specifies separator as the record separator.

-R separator or --record-separator separator specifies separator as the record separator. This is equivalent to the \pset recordsep command.

1.23 - -S --single-line

-S --single-line runs in single-line mode where a newline terminates a SQL command, like the semicolon does.

-S --single-line runs in single-line mode where a newline terminates a SQL command, like the semicolon does.

1.24 - -s --single-step

-s --single-step runs in single-step mode for debugging scripts.

-s --single-step runs in single-step mode for debugging scripts. Forces vsql to prompt before each statement is sent to the database and allows you to cancel execution.

1.25 - -T --table-attr

-T table-options or --table-attr table-options lets you specify options to be placed within the HTML table tag.

-T table-options or --table-attr table-options lets you specify options to be placed within the HTML table tag. See \pset for details.

1.26 - -t --tuples-only

-t or --tuples-only disables printing of column names, result row count footers, and so on.

-t or --tuples-only disables printing of column names, result row count footers, and so on. This is equivalent to the vsql meta-command \t.

1.27 - -V --version

-V or --version prints the vsql version and exits.

-V or --version prints the vsql version and exits.

1.28 - -v --variable --set

-v assignment, --variable assignment, and --set assignment perform a variable assignment, like the vsql meta-command \set.

-v assignment, --variable assignment, and --set assignment perform a variable assignment, like the vsql meta-command \set.

To unset a variable, omit the equal sign. To set a variable without a value, use the equals sign but omit the value. Make these assignments at a very early stage of start-up, so that variables reserved for internal purposes can get overwritten later.

1.29 - -X --no-vsqlrc

-X --no-vsqlrc prevents the start-up file from being read: the system-wide vsqlrc file or the user's ~/.vsqlrc file.

-X --no-vsqlrc prevents the start-up file from being read: the system-wide vsqlrc file or the user's ~/.vsqlrc file.

1.30 - -x --expanded

-x or --expanded enables extended table formatting mode.

-x or --expanded enables extended table formatting mode. This is equivalent to the vsql meta-command \x.

2 - Connecting from a non-cluster host

You can use the Vertica vsql executable image on a non-cluster Linux host to connect to a Vertica database.

  • On Red Hat, CentOS, and SUSE systems, you can install the client driver RPM, which includes the vsql executable. See Installing the vsql client for details.

  • If the non-cluster host is running the same version of Linux as the cluster, copy the image file to the remote system. For example:

    $ scp host01:/opt/vertica/bin/vsql .$ ./vsql
    
  • If the non-cluster host is running a different distribution or version of Linux than your cluster hosts, you must install the Vertica server RPM in order to get vsql:

    1. Download the appropriate RPM package by browsing to Vertica website. On the Support tab, select Customer Downloads.

    2. If the system you used to download the RPM is not the non-cluster host, transfer the file to the non-cluster host.

    3. Log into the non-cluster host as root and install the RPM package using the command:

      # rpm -Uvh filename
      

      Where filename is the package you downloaded. Note that you do not have to run the install_vertica script on the non-cluster host to use vsql.

Notes

  • Use the same Command-line options that you would on a cluster host.

  • You cannot run vsql on a Cygwin bash shell (Windows). Use ssh to connect to a cluster host, then run vsql.