Using vsql

• 1: Installing the vsql client
• 2: vsql usage on Windows
• 3: Connecting from the administration tools
• 4: Connecting from the command line
• 4.1: Command-line options
• 4.1.1: -A --no-align
• 4.1.2: -a --echo-all
• 4.1.3: -c --command
• 4.1.4: -d --dbname
• 4.1.5: -E
• 4.1.6: -e --echo-queries
• 4.1.7: -r --workload
• 4.1.8: -F --field-separator
• 4.1.9: -f --file
• 4.1.10: ? --help
• 4.1.11: -H --html
• 4.1.12: -h --host
• 4.1.13: -i -- timing
• 4.1.14: -g --label
• 4.1.15: -l --list
• 4.1.16: -m --sslmode
• 4.1.17: -n
• 4.1.18: -o --output
• 4.1.19: -P --pset
• 4.1.20: -p --port
• 4.1.21: -q --quiet
• 4.1.22: -R --record-separator
• 4.1.23: -S --single-line
• 4.1.24: -s --single-step
• 4.1.25: -T --table-attr
• 4.1.26: -t --tuples-only
• 4.1.27: -V --version
• 4.1.28: -v --variable --set
• 4.1.29: -X --no-vsqlrc
• 4.1.30: -x --expanded
• 4.2: Connecting from a non-cluster host
• 5: Meta-commands
• 5.1: Meta-commands quick reference
• 5.2: \connect
• 5.3: \d meta-commands
• 5.4: \edit
• 5.5: \i
• 5.6: \locale
• 5.7: \pset
• 5.8: \set
• 5.9: \timing
• 6: Variables
• 6.1: DBNAME
• 6.2: ECHO
• 6.3: ECHO_HIDDEN
• 6.4: ENCODING
• 6.5: HISTCONTROL
• 6.6: HISTSIZE
• 6.7: HOST
• 6.8: IGNOREEOF
• 6.9: ON_ERROR_STOP
• 6.10: PORT
• 6.11: PROMPT1 PROMPT2 PROMPT3
• 6.12: QUIET
• 6.13: ROWS_AT_A_TIME
• 6.14: SINGLELINE
• 6.15: SINGLESTEP
• 6.16: USER
• 6.17: VERBOSITY
• 6.18: VSQL_HOME
• 6.19: VSQL_SSLMODE
• 7: Prompting
• 8: Command line editing
• 9: vsql environment variables
• 10: Locales
• 11: Entering data with vsql
• 12: Files
• 13: Exporting data using vsql
• 14: Copying data using vsql
• 15: Output formatting examples

1 - Installing the vsql client

This page covers a non-FIPS installation. To install on a FIPS-compliant system, see Installing the FIPS client driver for ODBC and vsql.

Linux and macOS

To install vsql manually on another system:

1. Download vsql.

2. Extract or install vsql:

   • If you downloaded the .tar, create the /opt/vertica/ directory if it does not already exist, copy the .tar to it, navigate to it, and extract the .tar:

     
     $ mkdir -p /opt/vertica/
     $ cp driver_name.tar.gz /opt/vertica/
     $ tar vzxf driver_name.tar.gz
     

   • If you downloaded the .rpm, install it with:

     $ rpm -Uvh driver_name.rpm
     

3. Optionally add the vsql directory to your PATH. For example:

   $ export PATH=$PATH:\opt\vertica\bin
   

4. Make the vsql client executable. For example, to allow all users to run vsql:

   $ chmod ugo+x /path/to/vsql
   

5. Set your shell locale to a locale supported by vsql (which ones?). For example, in your .profile, add:

   export LANG=end_US.UTF-8
   

Windows

To install the vsql client:

1. Download the Windows client driver installer. For details on the drivers included in this installer, see Windows client driver installer.

2. Run the installer and follow the prompts to install the drivers.

3. Reboot your system.

After installing the driver, you can optionally add the vsql directory to your PATH. For example, to append the vsql directory to your PATH with Windows PowerShell for the current session:

PS C:\> $Env:PATH += ";C:\Program Files\Vertica Systems\VSQL64\"

You can verify that the vsql directory is in your PATH by running vsql -?:

PS C:\> vsql -?
This is vsql, the Vertica Analytic Database interactive terminal.

Usage:
  vsql [OPTIONS]... [DBNAME [USERNAME]]

For usage details, see vsql usage on Windows

2 - vsql usage on Windows

Font

The default raster font does not work well with the ANSI code page. Set the console font to "Lucida Console."

Console encoding

vsql is built as a "console application." The Windows console windows use a different encoding than the rest of the system, so take care when you use 8-bit characters within vsql. If vsql detects a problematic console code page, it warns you at startup.

To change the console code page, set the code page by entering cmd.exe /c chcp 1252.

Running under cygwin

Verify that your cygwin.bat file does not include the "tty" flag. If the "tty" flag is included in your cywgin.bat file, then banners and prompts are not displayed in vsql.

To verify, enter:

set CYGWIN=binmode tty ntsec

To remove the "tty" flag, enter:

set CYGWIN=binmode ntsec

Additionally, when running under Cygwin, vsql uses Cygwin shell conventions as opposed to Windows console conventions.

Tab completion

Tab completion is a function of the shell, not vsql. Because of this, tab completion does not work the same way in Windows vsql as it does on Linux versions of vsql.

On Windows, instead of using tab-completion, press F7 to pop-up a history window of commands. You can also press F8 after typing a few letters of a command to cycle through commands in the history buffer which begin with the same letters.

3 - Connecting from the administration tools

You can use the Administration tools to connect to a database using vsql on any node in the cluster.

1. Log in as the database administrator user; for example, dbadmin.

   Note

   Vertica does not allow users with root privileges to connect to a database for security reasons.

2. Run the Administration Tools.

   $ /opt/vertica/bin/admintools
   

3. On the Main Menu, select Connect to Database.

   

4. If prompted, enter the database password:

   Password:
   

   When you create a new user with the CREATE USER command, you can configure the password or leave it empty. You cannot bypass the password if the user was created with a password configured. You can change a user's password using the ALTER USER command.

5. The Administration Tools connect to the database and transfer control to vsql.

   Welcome to vsql, the Vertica Analytic Database interactive terminal.
   Type:  \h or \? for help with vsql commands
          \g or terminate with semicolon to execute query
          \q to quit
   
   =>
   

4 - Connecting from the command line

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

4.1 - Command-line options

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).

Note

You can only use load balancing with one address family in dual stack environments. For example, if you've configured load balancing for IPv6 addresses, then when an IPv4 client connects and requests load balancing the server does not allow it.

--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.

Note

Using this command-line option displays the database password in plain text. Use it with care, particularly if you are connecting as the database administrator, to avoid exposing sensitive information.

--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.

Note

This mode is provided only by customer request. Vertica recommends that you not use single-line mode in cases where you mix SQL and meta-commands on a line. In single-line mode, the order of execution might be unclear to the inexperienced user.

4.1.1 - -A --no-align

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

4.1.2 - -a --echo-all

-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.

4.1.3 - -c --command

-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)

4.1.4 - -d --dbname

-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.

4.1.5 - -E

-E displays queries generated by internal commands.

4.1.6 - -e --echo-queries

-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.

4.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.

4.1.8 - -F --field-separator

-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

4.1.9 - -f --file

-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.

4.1.10 - ? --help

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

4.1.11 - -H --html

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

4.1.12 - -h --host

-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).

4.1.13 - -i -- timing

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);"

4.1.14 - -g --label

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.

4.1.15 - -l --list

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

4.1.16 - -m --sslmode

-m or --sslmode specifies the policy for making SSL connections to the server. Options are verify_full, verify_ca 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.

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

4.1.17 - -n

-n disables command line editing.

4.1.18 - -o --output

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

4.1.19 - -P --pset

-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.

4.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. Defaults to port 5433.

4.1.21 - -q --quiet

-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.

4.1.22 - -R --record-separator

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

4.1.23 - -S --single-line

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

4.1.24 - -s --single-step

-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.

4.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. See \pset for details.

4.1.26 - -t --tuples-only

-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.

4.1.27 - -V --version

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

4.1.28 - -v --variable --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.

4.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.

4.1.30 - -x --expanded

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

4.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.

5 - Meta-commands

Anything you enter in vsql that begins with an unquoted backslash is a vsql meta-command that is processed by vsql itself. These commands help make vsql more useful for administration or scripting. Meta-commands are more commonly called slash or backslash commands.

The format of a vsql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of whitespace characters.

To include whitespace into an argument you can quote it with a single quote. To include a single quote into such an argument, precede it by a backslash. Anything contained in single quotes is furthermore subject to C-like substitutions for \n (new line), \t (tab), \digits, \0digits, and \0xdigits (the character with the given decimal, octal, or hexadecimal code).

If an unquoted argument begins with a colon (:), it is taken as a vsql variable and the value of the variable is used as the argument instead.

Arguments that are enclosed in backquotes (```) are taken as a command line that is passed to the shell. The output of the command (with any trailing newline removed) is taken as the argument value. The above escape sequences also apply in backquotes.

Some commands take a SQL identifier (such as a table name) as argument. These arguments follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while double quotes (") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotes, paired double quotes reduce to a single double quote in the resulting name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.

Parsing for arguments stops when another unquoted backslash occurs. This is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and vsql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

5.1 - Meta-commands quick reference

> \z *.*myseq*
     Access privileges for database "dbadmin"
 Grantee | Grantor | Privileges | Schema |  Name
---------+---------+------------+--------+--------
 dbadmin | dbadmin | SELECT*    | public | mySeq
 dbadmin | dbadmin | SELECT*    | public | mySeq2
(2 rows)

5.2 - \connect

Establishes a connection to database db, under the specified user user-name. The previous connection is closed. If you omit specifying a database name, Vertica connects to the current database. If you omit specifying a user name argument, Vertica assumes the current user.

Syntax

\c[connect] [db [user-name]]

Error handling

Errors that prevent execution include specifying an unknown user and denial of access to the specified database. Vertica handles errors differently, depending on whether this command is executed interactively in vsql, or in a script:

• VSQL handling: The current connection is maintained.

• Script: Processing immediately stops with an error. This is prevents scripts from acting on the wrong database.

5.3 - \d meta-commands

Vertica supports a number of \d commands, which return information on different categories of database objects. For a full list, see \d Reference below.

Syntax

Unless otherwise noted, \d commands generally conform to the following syntax:

\dCommand [ [schema.]pattern ]

Arguments

You can supply most \d commands with a string pattern argument, which filters the results that the command returns. The pattern can optionally be qualified by a schema name.

schema

Valid for most \d commands, restricts output to only database objects in schema. For example, the following \dp command obtains privileges information for all V_MONITOR tables that contain the string resource:

=> \dp V_MONITOR.*resource*
                Access privileges for database "dbadmin"
 Grantee | Grantor | Privileges |  Schema   |            Name
---------+---------+------------+-----------+----------------------------
 public  | dbadmin | SELECT     | v_monitor | resource_rejections
 public  | dbadmin | SELECT     | v_monitor | disk_resource_rejections
 public  | dbadmin | SELECT     | v_monitor | resource_usage
 public  | dbadmin | SELECT     | v_monitor | resource_acquisitions
 public  | dbadmin | SELECT     | v_monitor | resource_rejection_details
 public  | dbadmin | SELECT     | v_monitor | resource_pool_move
 public  | dbadmin | SELECT     | v_monitor | host_resources
 public  | dbadmin | SELECT     | v_monitor | node_resources
 public  | dbadmin | SELECT     | v_monitor | resource_queues
 public  | dbadmin | SELECT     | v_monitor | resource_pool_status
(10 rows)
  

pattern

Returns only the database objects that match the specified string. Pattern strings can include the following wildcards:

• * (asterisk): zero or more characters.

• ? (question mark): any single character.

For example, the following \dt command returns tables that start with the string store:

=> \dt store*
                     List of tables
 Schema |       Name        | Kind  |  Owner  | Comment
--------+-------------------+-------+---------+---------
 public | store_orders      | table | dbadmin |
 public | store_orders_2018 | table | dbadmin |
 public | store_overseas    | table | dbadmin |
 store  | store_dimension   | table | dbadmin |
 store  | store_orders_fact | table | dbadmin |
 store  | store_sales_fact  | table | dbadmin |
(6 rows)
  

\d reference

\d

Unqualified by a pattern argument, returns all tables with their schema names, owners, and comments. If qualified by a pattern argument, \d returns all matching tables and all columns in each table, with details about each column, such as data type, size, and default value.

\df

Returns all function names, the function return data type, and the function argument data type. Also returns the procedure names and arguments for all procedures that are available to the user.

\dj

Returns all projections showing the schema, projection name, owner, and node. The returned rows include superprojections, live aggregate projections, Top-K projections, and projections with expressions.

\dn

Returns the schema names and schema owner.

\dp

Returns a summary of privileges on all objects in system table V_CATALOG.GRANTS: grantee, grantor, privileges, schema, and object name (equivalent to \z).

\dS

Unqualified by a pattern argument, returns all V_CATALOG and V_MONITOR system tables. To obtain system tables for just one schema, qualify the command with the schema name, as follows:

\dS { V_CATALOG | V_MONITOR }.*

\ds

Returns sequences and their parameters.

\dT

Returns all data types that Vertica supports.

Note

\dT returns no results if qualified with a pattern argument.

\dt

Unqualified by a pattern argument, returns the same information as an unqualified \d command. If qualified by a pattern argument, \dt returns matching tables with the same level of detail as an unqualified \dt command.

\dtv

Returns tables and views.

\du

Returns database users and whether they are superusers.

\dv

Unqualified by a pattern argument, returns all views with their schema names, owners, and comments. If qualified by a pattern argument, \dv returns all matching views and the columns in each view, with each column's data type and size.

5.4 - \edit

Edits the query buffer (or specified file) with an external editor. When the editor exits, its content is copied back to the query buffer. If no argument is given, the current query buffer is copied to a temporary file which is then edited in the same fashion.

The new query buffer is then re-parsed according to the normal rules of vsql, where the whole buffer up to the first semicolon is treated as a single line. (Thus you cannot make scripts this way. Use \i for that.) If there is no semicolon, vsql waits for one to be entered (it does not execute the query buffer).

Syntax

\e[dit] [ file ]

5.5 - \i

Reads and executes input from the specified file.

Syntax

\i filename

Examples

The Vertica vsql client on Linux supports backquote (backtick) expansion. For example:

1. Set an environment variable to a path that contains scripts you want to run:

   $ export MYSCRIPTS=/home/dbadmin/testscripts
   

2. Issue the vsql command.

   $ vsql
   

3. Use backquote expansion to include the path for running an existing script—for example, sample.sql.

   => \i `echo $MYSCRIPTS/sample.sql`
   

5.6 - \locale

Displays or sets the locale setting for the current session.

Syntax

\locale [locale-identifier]

Arguments

locale-identifier

Specifies the ICU locale identifier to use, by default set to:

en_US@collation=binary

If set to an empty string, Vertica sets locale to en_US_POSIX.

If you omit this argument, \locale returns the current locale setting.

For details on identifier options, see About locale. For a complete list of locale identifiers, see the ICU Project.

Examples

View the current locale setting:

=> \locale
en_US@collation=binary

Change the default locale for this session:

=> \locale en_GBINFO:
INFO 2567:  Canonical locale: 'en_GBINFO:'
Standard collation: 'LEN'
English (GBINFO:)

Notes

The server locale settings impact only the collation behavior for server-side query processing. The client application is responsible for ensuring that the correct locale is set in order to display the characters correctly. Below are the best practices recommended by Vertica to ensure predictable results:

• The locale setting in the terminal emulator for vsql (POSIX) should be set to be equivalent to session locale setting on server side (ICU) so data is collated correctly on the server and displayed correctly on the client.

• The vsql locale should be set using the POSIX LANG environment variable in terminal emulator. Refer to the documentation of your terminal emulator for how to set locale.

• Server session locale should be set using the set as described in Specify the default locale for the database.

• All input data for vsql should be in UTF-8 and all output data is encoded in UTF-8.

• Non UTF-8 encodings and associated locale values are not supported.

5.7 - \pset

Sets options that control how Vertica formats query result output.

Syntax

\pset output-option

Output options

format format-option

Sets output format, where format-option is one of the following:

• u[naligned] writes all column data of each row on a single line, where each field is separated only by the current separator character. Use this output for use as input to other programs—for example, comma-delimited fields for CSV input.

• a[ligned] (default): Renders column-aligned output.

• h[tml]: Renders output in HTML markup as a table.

• l[atex]: Renders output in LaTex markup.

border int

Valid only if output format is set to html, specifies the table border, where int specifies the border type.

expanded

Toggles between regular and expanded format. When expanded format is enabled, all output has two columns with the column name on the left and the data on the right. This mode is especially useful for wide tables.

fieldsep 'arg'

Valid only if output format is set to unaligned, specifies the field separator, by default | (vertical bar).

For example, to specify tab as the field separator:

\pset fieldsep '\t'

footer

Toggles display of the default footer:

(int rows)

null 'string'

Specifies to represent column null values as string. By default, Vertica renders null values as an empty field, which might be mistaken as an empty string.

For example:

\pset null '(null)'

pager [always]

Toggles use of a pager for query and vsql help output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more) is used.

When the pager is off, the pager is not used. When the pager is on, the pager is used only when appropriate; that is, the output is to a terminal and does not fit on the screen. (vsql does not do a perfect job of estimating when to use the pager.)

If qualified with the argument always, the pager is always used.

recordsep 'char'

Valid only if output format is set to unaligned, specifies the character used to delimit table records (tuples), by default a newline character.

tableattr html-attribute[...]

Specifies attributes to be placed inside the HTML table tag—for example, cellpadding or bgcolor.

title ['title-str']

Sets a title that precedes query result output, to title-str. HTML output renders this as follows:

<caption>title-str</caption>

To remove the title, reissue the command omit the title-str argument.

trailingrecordsep

Toggles on or off the trailing record separator to use in unaligned output mode.

t[uples_only]

Toggles between tuples only and full display. Full display might show extra information such as column headers, titles, and various footers. In tuples only mode, only actual table data is shown.

Shortcuts

The following \pset commands have short-cuts:

\pset expanded

\x

\pset fieldsep 'arg'

\f

\pset format aligned

\a

\pset format html

\H

\pset tableattr html-attribute[...]

\T html-attribute[...]

\pset title title-str

\C ['title-str']

\pset tuples_only

\t

Examples

See Output formatting examples.

5.8 - \set

Sets an internal variable to one or more values. If multiple values are specified, they are concantenated. An unqualified \set command lists all internal variables.

To unset a variable, use vsql meta-command\unset.

Syntax

\set [var [value]...]

Arguments

var

The name of an internal variable to set. Valid variable names are case sensitive and can contain characters, digits, and underscores. vsql treats several variables as special, which are described in Variables.

value

A value to set in variable var. If no value is specified, the variable is set to no value.

If set to an empty string, the variable is set to no value. If you omit this argument, \set returns all internal variables.

If no arguments are supplied, \set returns all internal variables. For example:

=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'

5.9 - \timing

If set to on, returns how long (in milliseconds) each SQL statement runs. Results include:

• Length of time required to fetch the first block of rows

• Total time until the last block is formatted.

Unqualified, \timing toggles timing on and off. You can explicitly turn timing on and off by qualifying the command with options ON and OFF, respectively.

Syntax

\timing [ON | OFF]

Examples

The following unqualified \timing commands toggle timing on and off:

=> \timing
Timing is on
=> \timing
Timing is off

The following example shows a SQL command with timing on:

=> \timing
Timing is on.
=> SELECT user_name, ssl_state, authentication_method, client_authentication_name,
     client_type FROM sessions WHERE session_id=(SELECT session_id FROM current_session);
 user_name | ssl_state | authentication_method | client_authentication_name | client_type
-----------+-----------+-----------------------+----------------------------+-------------
 dbadmin   | None      | ImpTrust              | default: Implicit Trust    | vsql
(1 row)

Time: First fetch (1 row): 73.684 ms. All rows formatted: 73.770 ms

6 - Variables

vsql provides variable substitution features similar to common Linux command shells. Variables are name/value pairs, where the value can be a string of any length. To set variables, use the vsql meta-command \set. For example, the following statement sets the variable fact to the value dim:

=> \set fact dim

If you call \set on a variable and supply no value, the variable is set to an empty string.

Getting variables

To retrieve the content of a given variable, precede the name with a colon and use it as the argument of any slash command. For example:

=> \echo :fact
dim

An unqualified \set command returns all current variables and their values:

dbadmin=> \set
VERSION = 'vsql'
AUTOCOMMIT = 'off'
VERBOSITY = 'default'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
ROWS_AT_A_TIME = '1000'
DBNAME = 'dbadmin'
USER = 'dbadmin'
PORT = '5433'
LOCALE = 'en_US@collation=binary'
HISTSIZE = '500'

Deleting variables

To unset (or delete) a variable, use the vsql meta-command \unset.

Variable naming conventions

vsql internal variable names can contain letters, numbers, and underscores in any order and any number. Some variables are treated specially by vsql. They indicate certain option settings that can be changed at run time by altering the value of the variable or represent some state of the application. Although you can use these variables for any other purpose, this is not recommended. By convention, all specially treated variables consist of all upper-case letters (and possibly numbers and underscores). To ensure maximum compatibility in the future, avoid using such variable names for your own purposes.

SQL interpolation

You can substitute ("interpolate") vsql variables into regular SQL statements. You do so by prepending the variable name with a colon (:). For example, the following statements query the table my_table:

=> \set fact 'my_table'
=> SELECT * FROM :fact;

The value of the variable is copied literally, so it can even contain unbalanced quotes or backslash commands. Make sure that it makes sense where you put it. Variable interpolation is not performed into quoted SQL entities. One exception applies: the contents of backquoted strings (````) are passed to a system shell, and replaced with the shell's output. See Using Backquotes to Read System Variables below.

Using backquotes to read system variables

In vsql, the contents of backquotes are passed to the system shell to be interpreted (the same behavior as many UNIX shells). This is particularly useful in setting internal vsql variables, since you may want to access UNIX system variables (such as HOME or TMPDIR) rather than hard-code values.

For example, to set an internal variable to the full path for a file in your UNIX user directory, you can use backquotes to get the content of the system HOME variable, which is the full path to your user directory:

=> \set inputfile `echo $HOME`/myinput.txt=> \echo :inputfile
/home/dbadmin/myinput.txt

The contents of the backquotes are replaced with the results of running the contents in a system shell interpreter. In this case, the echo $HOME command returns the contents of the HOME system variable.

6.1 - DBNAME

The name of the database to which you are currently connected. DBNAME is set every time you connect to a database (including program startup), but it can be unset.

6.2 - ECHO

If set to all, all lines entered from the keyboard or from a script are written to the standard output before they are parsed or run.

To select this behavior on program start-up, use the switch -a. If set to queries, vsql merely prints all queries as they are sent to the server. The switch for this is -e.

6.3 - ECHO_HIDDEN

When this variable is set and a backslash command queries the database, the query is first shown. This way you can study the Vertica internals and provide similar functionality in your own programs. (To select this behavior on program start-up, use the switch -E.)

If you set the variable to the value noexec, the queries are just shown but are not actually sent to the server and run.

6.4 - ENCODING

The current client character set encoding.

6.5 - HISTCONTROL

If this variable is set to ignorespace, lines that begin with a space are not entered into the history list. If set to a value of ignoredups, lines matching the previous history line are not entered. A value of ignoreboth combines the two options. If unset, or if set to any other value than those previously mentioned, all lines read in interactive mode are saved on the history list.

Source: Bash.

6.6 - HISTSIZE

Specifies how much storage space is allocated to store the history of SQL statements issued in the current vsql session. vsql uses this setting, by default 500, to calculate the size of the history buffer:

HISTSIZE * 50 (bytes)

where 50 bytes approximates the average length of a SQL statement. The actual length of SQL statements in the current session determines how many statements vsql stores.

HISTSIZE has no effect on the history that is stored in .vsql_history.

Source: Bash

6.7 - HOST

The database server host you are currently connected to. This is set every time you connect to a database (including program startup), but can be unset.

6.8 - IGNOREEOF

If unset, sending an EOF character (usually Control+D) to an interactive session of vsql terminates the application. If set to a numeric value, that many EOF characters are ignored before the application terminates. If the variable is set but has no numeric value, the default is 10.

Source: Bash.

6.9 - ON_ERROR_STOP

By default, if a script command results in an error, for example, because of a malformed command or invalid data format, processing continues. If you set ON_ERROR_STOP to ON in a script and an error occurs during processing, the script terminates immediately.

For example:

=> \set ON_ERROR_STOP ON

6.10 - PORT

The database server port to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be unset.

6.11 - PROMPT1 PROMPT2 PROMPT3

These specify what the prompts vsql issues look like. See Prompting for details.

6.12 - QUIET

This variable is equivalent to the command line option -q. It is probably not too useful in interactive mode.

6.13 - ROWS_AT_A_TIME

ROWS_AT_A_TIME is set by default to 1000, and retrieves results as blocks of rows of that size. The column formatting for the first block is used for all blocks, so in later blocks some entries could overflow.

When formatting results, Vertica buffers ROWS_AT_A_TIME rows in memory to calculate the maximum column widths. It is possible that rows after this initial fetch are not properly aligned if any of the field values are longer than those see in the first ROWS_AT_A_TIME rows. ROWS_AT_A_TIME can be unset with vsql meta-command\unset to guarantee perfect alignment. However, this requires re-buffering the entire result set in memory and might cause vsql to fail if the result set is too big.

6.14 - SINGLELINE

This variable is equivalent to the command line option -S.

6.15 - SINGLESTEP

This variable is equivalent to the command line option -s.

6.16 - USER

The database user you are currently connected as. This is set every time you connect to a database (including program startup), but can be unset.

6.17 - VERBOSITY

This variable can be set to the values default, verbose, or terse to control the verbosity of error reports.

6.18 - VSQL_HOME

By default, the vsql program reads configuration files from the user's home directory. In cases where this is not desirable, the configuration file location can be overridden by setting the VSQL_HOME environment variable in a way that does not require modifying a shared resource.

In the following example, vsql reads configuration information out of /tmp/jsmith rather than out of ~.

# Make an alternate configuration file in /tmp/jsmith
mkdir -p /tmp/jsmith
echo "\\echo Using VSQLRC in tmp/jsmith" > /tmp/jsmith/.vsqlrc
# Note that nothing is echoed when invoked normally
vsql
# Note that the .vsqlrc is read and the following is
# displayed before the vsql prompt
#
# Using VSQLRC in tmp/jsmith
VSQL_HOME=/tmp/jsmith vsql

6.19 - VSQL_SSLMODE

VSQL_SSLMODE specifies how (or whether) clients (like admintools) use SSL when connecting to servers. The default value is prefer, meaning to use SSL if the server offers it. Legal values are require, prefer, allow, and disable. This variable is equivalent to the command-line -m option (or --sslmode).

7 - Prompting

The prompts vsql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when vsql requests a new command. Prompt 2 is issued when more input is expected during command input because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you run a SQL COPY command and you are expected to type in the row values on the terminal.

The value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:

%M

The full host name (with domain name) of the database server, or [local] if the connection is over a socket, or [local:/dir/name], if the socket is not at the compiled in default location.

%m

The host name of the database server, truncated at the first dot, or [local].

%>

The port number at which the database server is listening.

%n

The database session user name.

%/

The name of the current database.

%~

Like %/, but the output is ~ (tilde) if the database is your default database.

%#

If the session user is a database superuser, then a #, otherwise a >. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

%R

In prompt 1 normally =, but ^ if in single-line mode, and ! if the session is disconnected from the database (which can happen if \connect fails). In prompt 2 the sequence is replaced by -, *, a single quote, a double quote, or a dollar sign, depending on whether vsql expects more input because the command wasn't terminated yet, because you are inside a /* ... */ comment, or because you are inside a quoted or dollar-escaped string. In prompt 3 the sequence doesn't produce anything.

%x

Transaction status: an empty string when not in a transaction block, or * when in a transaction block, or ! when in a failed transaction block, or ? when the transaction state is indeterminate (for example, because there is no connection).

%digits

The character with the indicated numeric code is substituted. If digits starts with 0x the rest of the characters are interpreted as hexadecimal; otherwise if the first digit is 0 the digits are interpreted as octal; otherwise the digits are read as a decimal number.

%:name:

The value of the vsql variable name. See the section Variables for details.

%`command`

The output of command, similar to ordinary "back- tick" substitution.

%[ ... %]

Prompts may contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these may occur within the prompt. The following example results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals:

testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%#%[%033[0m%] '

To insert a percent sign into your prompt, write %%. The default prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.

Note: See the specification for terminal control sequences (applicable to gnome-terminal and xterm).

8 - Command line editing

vsql supports the tecla library for line editing and retrieval. You can define a tecla configuration with the following files:

• ~/.vsqlrc (user)

• /opt/vertica/config/vsqlrc (global)

For details, see the tecla documentation.

Command history is automatically saved in ~/.vsql_history when vsql exits and is reloaded when vsql starts.

Disabling tab completion

To disable tab completion, add the following to .vsqlrc:

\bind ^I

Key bindings

Key bindings are read from a global configuration at /opt/vertica/config/vsqlrc, if present. To override key bindings, add definitions to ~/.vsqlrc.

Key bindings must be prefixed with a backslash (\). For example, the following definition binds the "backward-word" action to Ctrl+B:

\bind ^B backward-word

The following key bindings are specific to vsql:

• Insert switches between insert mode (the default) and overwrite mode.

• Delete deletes the character to the right of the cursor.

• Home moves the cursor to the front of the line.

• End moves the cursor to the end of the line.

• ^R Performs a history backwards search.

Implementation differences

The vsql implementation of the tecla library deviates from the tecla documentation in the following ways:

• Unlike the standard tecla library, which saves all executed lines in the command history, vsql only saves unique non-empty lines.

• vsql standardizes the name and location of the history file (~/.vsql_history).

• vsql does not support 8-bit meta characters. This can affect international character sets, meta keys, and locales. You can verify that a meta character sends an escape by setting the EightBitInput X resource to False. You can do this in the following ways:

  • Add the following to ~/.Xdefaults:

    XTerm*EightBitInput: False
    

  • Start an xterm session with the -xrm '*EightBitInput: False'.

9 - vsql environment variables

Set one or more of the following environment variables to be used by the defined properties automatically, each time you start vsql:

PAGER

If the query results do not fit on the screen, they are piped through this command. Typical values are more or less. The default is platform-dependent. Use the \pset command to enable/disable the pager.

VSQL_CLIENT_LABEL

The label to identify the vsql client in various system tables like SESSIONS. This is an alternative to setting the client label with the --label option or SET_CLIENT_LABEL, but if either of these is used, they take precedence over VSQL_CLIENT_LABEL.

VSQL_DATABASE

The database to which you are connecting. For example, VMart.

TMPDIR

Directory for storing temporary files. The default is platform-dependent. On Unix-like systems the default is /tmp.

VSQL_EDITOR
EDITOR
VISUAL

Editor used by the \e command. The variables are examined in the order listed; the first that is set is used.

VSQL_HOME

By default, the vsql program reads configuration files from the user's home directory. In cases where this is not desirable, the configuration file location can be overridden by setting the VSQL_HOME environment variable in a way that does not require modifying a shared resource.

VSQL_HOST

Host name or IP address of the Vertica node.

VSQL_PASSWORD

The database password. Using this environment variable increases site security by precluding the need to enter the database password on the command line.

VSQL_PORT

Port to use for the connection.

VSQL_SSLMODE

Specifies whether and how clients such as admintools use SSL when connecting to servers.

VSQL_USER

User name to use for the connection.

VSQL_WORKLOAD

The workload to use for the connection.

10 - Locales

The default terminal emulator under Linux is gnome-terminal, although xterm can also be used.

Vertica recommends that you use gnome-terminal with vsql in UTF-8 mode, which is its default.

To change settings on Linux

1. From the tabs at the top of the vsql screen, select Terminal.

2. Click Set Character Encoding.

3. Select Unicode (UTF-8).

   Note

   This works well for standard keyboards. xterm has a similar UTF-8 option.

To change settings on Windows using PuTTy

1. Right click the vsql screen title bar and select Change Settings.

2. Click Window and click Translation.

3. Select UTF-8 in the drop-down menu on the right.

Notes

• vsql has no way of knowing how you have set your terminal emulator options.

• The tecla library is prepared to do POSIX-type translations from a local encoding to UTF-8 on interactive input, using the POSIX LANG, etc., environment variables. This could be useful to international users who have a non-UTF-8 keyboard. See the tecla documentation for details.

  Vertica recommends the following (or whatever other .UTF-8 locale setting you find appropriate):

  export LANG=en_US.UTF-8
  

• The vsql \locale command invokes and tracks the server SET LOCALE TO command, described. vsql itself currently does nothing with this locale setting, but rather treats its input (from files or from tecla), all its output, and all its interactions with the server as UTF-8. vsql ignores the POSIX locale variables, except for any "automatic" uses in printf, and so on.

11 - Entering data with vsql

You often need to insert literal data when using vsql. For example:

• Adding a row of data to a table using an INSERT statement.

• Adding multiple rows of data through a COPY FROM STDIN statement.

The following table lists the data types that Vertica supports, and the format you use to enter that data in queries when using vsql.

12 - Files

Before starting up, vsql attempts to read and execute commands from the system-wide vsqlrc file and the user's ~/.vsqlrc file. The command-line history is stored in the file ~/.vsql_history.

13 - Exporting data using vsql

You can use vsql for simple data-export tasks by changing its output format options so the output is suitable for importing into other systems (tab-delimited or comma-separated files, for example). These options can be set either from within an interactive vsql session, or through command-line arguments to the vsql command (making the export process suitable for automation through scripting). After you have set vsql's options so it outputs the data in a format your target system can read, you run a query and capture the result in a text file.

The following table lists the meta-commands and command-line options that are useful for changing the format of vsql's output.

The following example demonstrates disabling padding and column headers in the output, and setting a field separator to dump a table to a tab-separated text file within an interactive session.

=> SELECT * FROM my_table;
 a |   b   | c
---+-------+---
 a | one   | 1
 b | two   | 2
 c | three | 3
 d | four  | 4
 e | five  | 5
(5 rows)
=> \a
Output format is unaligned.
=> \t
Showing only tuples.
=> \pset fieldsep '\t'
Field separator is "    ".
=> \o dumpfile.txt
=> select * from my_table;
=> \o
=> \! cat dumpfile.txt
a       one     1
b       two     2
c       three   3
d       four    4
e       five    5

When logged into one of the database nodes, you can create the same output file directly from the command line by passing the right parameters to vsql:

$ vsql -U username -F $'\t' -At -o dumpfile.txt -c "SELECT * FROM my_table;"
Password:
$ cat dumpfile.txt
a       one     1
b       two     2
c       three   3
d       four    4
e       five    5

If you want to convert null values to a unique string as mentioned earlier, you can add the argument -P null='NULLNULLNULL' (or whatever unique string you choose).

By adding the -w vsql command-line option to the example command line, you could use the command within a batch script to automate the data export. However, the script would contain the database password as plain text. If you take this approach, you should prevent unauthorized access to the batch script, and also have the script use a database user account that has limited access.

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

14 - Copying data using vsql

You can use vsql to copy data between two Vertica databases. This technique is similar to the technique explained in Exporting data using vsql, except instead of having vsql save data to a file for export, you pipe one vsql's output to the input of another vsql command that runs a COPY statement from STDIN. This technique can also work for other databases or applications that accept data from an input stream.

The easiest way to copy using vsql is to log in to a node of the target database, then issue a vsql command that connects to the source Vertica database to dump the data you want. For example, the following command copies the store.store_sales_fact table from the vmart database on node testdb01 to the vmart database on the node you are logged into:

vsql -U username -w passwd -h testdb01 -d vmart -At -c "SELECT * from store.store_sales_fact"  \
| vsql -U username -w passwd -d vmart -c "COPY store.store_sales_fact FROM STDIN DELIMITER '|';"

If you are using the Bash shell, you can escape special delimiter characters. For example, DELIMITER E'\t' specifies tab. Shells other than Bash may have other string-literal syntax.

Monitoring progress (optional)

You may want some way of monitoring progress when copying large amounts of data between Vertica databases. One way of monitoring the progress of the copy operation is to use a utility such as Pipe Viewer that pipes its input directly to its output while displaying the amount and speed of data it passes along. Pipe Viewer can even display a progress bar if you give it the total number of bytes or lines you expect to be processed. You can get the number of lines to be processed by running a separate vsql command that executes a SELECT COUNT query.

The following command demonstrates how you can use Pipe Viewer to monitor the progress of the copy shown in the prior example. The command is complicated by the need to get the number of rows that will be copied, which is done using a separate vsql command within a Bash backquote string, which executes the string's contents and inserts the output of the command into the command line. This vsql command just counts the number of rows in the store.store_sales_fact table.

vsql -U username -w passwd -h testdb01 -d vmart -At -c "SELECT * from store.store_sales_fact"  \
| pv -lpetr -s `vsql -U username -w passwd -h testdb01 -d vmart -At -c "SELECT COUNT (*) FROM store.store_sales_fact;"` \
| vsql -U username -w passwd -d vmart -c "COPY store.store_sales_fact FROM STDIN DELIMITER '|';"

While running, the above command displays a progress bar that looks like this:

0:00:39 [12.6M/s] [=============================>                             ] 50% ETA 00:00:40

15 - Output formatting examples

By default, Vertica formats query output as follows:

=> SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description;
       category_description
----------------------------------
 Food
 Medical
 Misc
 Non-food
(4 rows)

You can control the format of query output in various ways with the \pset command—for example, change the border:

=> \pset border 2
Border style is 2.
=> SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description;
+----------------------------------+
|       category_description       |
+----------------------------------+
| Food                             |
| Medical                          |
| Misc                             |
| Non-food                         |
+----------------------------------+
(4 rows)
=> \pset border 0
Border style is 0.
=> SELECT DISTINCT category_description FROM product_dimension ORDER BY category_description;
      category_description
--------------------------------
Food
Medical
Misc
Non-food
(4 rows)

The following sequence of pset commands change query output in several ways:

• Set border style to 1.

• Remove column alignment.

• Change the field separator to a comma.

• Remove column headings

=> \pset border 1
Border style is 1.
=> \pset format unaligned
Output format is unaligned.
=> \pset fieldsep ','
Field separator is ",".
=> \pset tuples_only
Showing only tuples.
=> SELECT product_key, product_description, category_description FROM product_dimension LIMIT 10;
1,Brand #2 bagels,Food
1,Brand #1 butter,Food
2,Brand #6 chicken noodle soup,Food
3,Brand #11 vanilla ice cream,Food
4,Brand #14 chocolate chip cookies,Food
4,Brand #12 rash ointment,Medical
6,Brand #18 bananas,Food
7,Brand #25 basketball,Misc
8,Brand #27 french bread,Food
9,Brand #32 clams,Food

The following example uses meta-commands to toggle output format—in this case, \a (alignment), \t (tuples only), and -x (extended display):

=> \a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is off.
=> SELECT product_key, product_description, category_description FROM product_dimension LIMIT 10;
 product_key |       product_description        |       category_description
-------------+----------------------------------+----------------------------------
           1 | Brand #2 bagels                  | Food
           1 | Brand #1 butter                  | Food
           2 | Brand #6 chicken noodle soup     | Food
           3 | Brand #11 vanilla ice cream      | Food
           4 | Brand #14 chocolate chip cookies | Food
           4 | Brand #12 rash ointment          | Medical
           6 | Brand #18 bananas                | Food
           7 | Brand #25 basketball             | Misc
           8 | Brand #27 french bread           | Food
           9 | Brand #32 clams                  | Food
(10 rows)

The following example sets output format to HTML, so Vertica renders query results in HTML markup as a table:

=> \pset format html
Output format is html.
=> \pset tableattr 'border="2" cellpadding="3"'
Table attribute is "border="2" cellpadding="3"".
=> SELECT product_key, product_description, category_description FROM product_dimension LIMIT 2;
<table border="1" border="2" cellpadding="3">
  <tr>
    <th align="center">product_key</th>
    <th align="center">product_description</th>
    <th align="center">category_description</th>
  </tr>
  <tr valign="top">
    <td align="right">1</td>
    <td align="left">Brand #2 bagels</td>
    <td align="left">Food                            </td>
  </tr>
  <tr valign="top">
    <td align="right">1</td>
    <td align="left">Brand #1 butter</td>
    <td align="left">Food                            </td>
  </tr>
</table>
<p>(2 rows)<br />
</p>