vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. It also provides a number of meta-commands and various shell-like features that facilitate writing scripts and automating a variety of tasks.
If you are using the vsql client installed on the server, then you can connect from the:
SQL statements can be spread over several lines for clarity.
vsql can handle input and output in UTF-8 encoding. The terminal emulator running vsql must be set up to display the UTF-8 characters correctly. The following example shows the settings in PuTTy:
When you disconnect a user session, any transactions in progress are automatically rolled back.
To view wide result sets, use the Linux less utility to truncate long lines.
Before connecting to the database, specify that you want to use less for query output:
$ export PAGER=less
Connect to the database.
Query a wide table:
=> select * from wide_table;
At the less prompt, type:
-S
If a shell running vsql fails (crashes or freezes), the vsql processes continue to run even if you stop the database. In that case, log in as root on the machine on which the shell was running and manually terminate the vsql process. For example: $ ps -ef | grep vertica ... fred 2401 1 0 06:02 pts/1 00:00:00 /opt/vertica/bin/vsql -p 5433 -h test01_site01 quick_start_single ... $ kill -9 2401
Enabling autocommit
By default, you must COMMIT to save changes made in a transaction. To enable automatic commits, see SET SESSION AUTOCOMMIT.
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:
Run the installer and follow the prompts to install the drivers.
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:
The default raster font does not work well with the ANSI code page.
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.
Note
1252 is a code page that is appropriate for European languages. Replace it with your preferred locale code page.
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 to connect to a database using vsql on any node in the cluster.
You can use the Administration tools to connect to a database using vsql on any node in the cluster.
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.
Run the Administration Tools.
$ /opt/vertica/bin/admintools
On the Main Menu, select Connect to Database.
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.
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
=>
Note
See Meta-commands for the various commands you can run while connected to the database through the Administration Tools.
4 - 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.
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.
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.
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.
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.
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.
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.
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.
Specifies the field separator for unaligned output (default: "|") (-P fieldsep=). (See -A --no-align.) Using this command is equivalent to \psetfieldsep or \f.
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.
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.
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.
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.
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.
-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.
-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.
-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)
Note
If you use double quotes (") with
echo, you must double the backslashes ().
4.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.
4.1.5 - -E
-E displays queries generated by internal commands.
-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.
-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=).
-F separator or --field-separator separator specifies the field separator for unaligned output (default: "|") (-P fieldsep=). (See -A --no-align.) This is equivalent to \psetfieldsep 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.
-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.
-? --help displays help about vsql command line arguments and exits.
4.1.11 - -H --html
-H --html turns on HTML tabular output.
-H --html turns on HTML tabular output. This is equivalent to \psetformat 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.
-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 methodHOST (rather than LOCAL).
4.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 -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.
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.
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.
-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.
-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.
-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.
4.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.
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.
-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.
-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.
-R separator or --record-separator separator specifies separator as the record separator. This is equivalent to the \psetrecordsep 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.
-S --single-line runs in single-line mode where a newline terminates a SQL command, like the semicolon does.
Note
This mode is provided for those who insist on it, but you are not necessarily encouraged to use it, particularly if you mix SQL and meta-commands on a line. The order of execution might not always be clear to the inexperienced user.
4.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.
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.
-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.
-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.
-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.
-v assignment, --variable assignment, and --set assignment perform a variable assignment, like the vsql meta-command \set.
Note
You must separate name and value, if any, by an equals sign (=) on the command line.
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.
-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.
-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:
Download the appropriate RPM package by browsing to Vertica website. On the Support tab, select Customer Downloads.
If the system you used to download the RPM is not the non-cluster host, transfer the file to the non-cluster host.
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.
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.
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.
Edits the query buffer (or specified file) with an external editor. For details, see
\edit.
\echo [str]
Writes str to standard output.
Tip
Use \qecho to redirect query output to the query output stream, as set by \o.
\f [str]
Sets the field separator for unaligned query output. The default is the vertical bar (|).
-F --field-separator
\g [file-name|shell-command]
Sends the query in the input buffer (see \p) to the server. You can send query results to file-name, or pipe results to shell-comand; otherwise, \g sends query results to standard output.
\H
Renders output in HTML markup as a table. For details, see
\pset format aligned.
-H --html
\h[elp]
Displays help information about the meta-commands, the same as \?.
--help
\i file
Reads and executes input from filename.
-f --file
\l \list
Lists available databases and owners.
-l --list
\locale [locale]
Displays the current locale setting or sets a new locale for the session. For details, see
\locale.
\o [file-name|shell-command]
Controls where vsql directs query output. You can send query results to file-name, or pipe results to shell-comand; otherwise, \o sends query results to standard output.
-o --output
\p
Prints the current query buffer to standard output.
\password [user-name]
Starts the password change process. Superusers can specify a user name to change that user's password; otherwise, users can only change their own passwords.
Sets options that control how Vertica formats query result output. For details, see
\pset.
-P --pset
\q
Quits the vsql program
\qecho [str]
Writes str to the query output stream, as specified by by \o.
\r
Clears (resets) the query buffer
\s [file]
Valid only if vsql is configured to use the GNU Readline library, prints or saves the command line history to file, or to standard output if no file name is supplied.
Sets internal variable var to value. If you specify multiple values, var is set to their concantenated values. If no values are specified, var is set to no value.
Specifies attributes to be placed inside the HTML table tag—for example, cellpadding or bgcolor, the same as
\pset tableattr html-attribute[...]. For sample usage, see Output formatting examples.
-T --table-attr
\timing
If set to on, returns how long (in milliseconds) each SQL statement runs. For details, see
\timing.
-i -- timing
\unset var
Deletes internal variable var that was set by the meta-command \set .
\w file-name
Outputs the current query buffer to file file-name.
\x
Toggles between regular and expanded format. For details, see
\pset format expanded.
-x --expanded
\z
Returns a summary of privileges on all objects in system table
V_CATALOG.GRANTS: grantee, grantor, privileges, schema, and object name (equivalent to
\dp).
\z supports the same options as \dp for filtering output by schema and object name patterns, . For example:
> \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.
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.
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.
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).
Tip
vsql searches the environment variables VSQL_EDITOR, EDITOR, and VISUAL (in that order) for an editor to use. If all of them are unset, vi is used on Linux systems, notepad.exe on Windows systems.
Syntax
\e[dit] [ file ]
5.5 - \i
Reads and executes input from the specified file.
Reads and executes input from the specified file.
Note
To see the lines on the screen as they are read, set the variable ECHO to all.
Syntax
\i filename
Examples
The Vertica vsql client on Linux supports backquote (backtick) expansion. For example:
Set an environment variable to a path that contains scripts you want to run:
$ export MYSCRIPTS=/home/dbadmin/testscripts
Issue the vsql command.
$ vsql
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.
Displays or sets the locale setting for the current session.
Note
This command does not alter the default locale for all database sessions. To change the default for all sessions, set configuration parameter DefaultSessionLocale.
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.
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.
Sets options that control how Vertica formats query result output.
Syntax
\pset output-option
Output options
Note
Unless otherwise specified, output options are valid for all formats.
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.
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.
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.
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:
If set to on, returns how long (in milliseconds) each SQL statement runs.
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.
Note
You can also enable \timing from the command line using the
vsql -i command.
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.
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.
Note
The arguments of \set are subject to the same substitution rules as with other commands. For example, \set dim :fact is a valid way to copy a variable.
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:
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:
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.
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.
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.
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.
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 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.
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.
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 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.
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
Note
If you invoke the script on Linux with
vsql -f, vsql returns with error code 3 to indicate that an error occurred in the script.
6.10 - PORT
The database server port to which you are currently connected.
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.
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.
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.
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.
This variable is equivalent to the command line option -S.
6.15 - SINGLESTEP
This variable is equivalent to the command line option -s.
This variable is equivalent to the command line option -s.
6.16 - USER
The database user you are currently connected as.
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.
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.
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.
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, 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 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:
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:.
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.
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.
The default terminal emulator under Linux is gnome-terminal, although xterm can also be used.
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
From the tabs at the top of the vsql screen, select Terminal.
Click Set Character Encoding.
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
Right click the vsql screen title bar and select Change Settings.
Click Window and click Translation.
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.
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.
Data Type
Inserting to vsql using
Example Use in INSERT INTO table...
For More Information See...
Binary types, such as BINARY and VARBINARY
Helper functions such as HEX_TO_BINARY, octal strings, specified data format in COPY statements, casting string values to binary.
Before starting up, vsql attempts to read and execute commands from the system-wide vsqlrc file and the user's ~/.vsqlrc file.
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.
Tip
If you want to save your old history file, open another terminal window and save a copy to a different file name.
13 - Exporting data using vsql
You can use 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).
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
Note
You could encounter issues with empty strings being converted to NULLs or the reverse using this technique. You can prevent any confusion by explicitly setting null values to output a unique string such as NULLNULLNULL (for example, \pset null 'NULLNULLNULL'). Then, on the import end, convert the unique string back to a null value. For example, if you are copying the file back into a Vertica database, you would give the argument NULL 'NULLNULLNULL' to the COPY statement.
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.
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:
The above example copies the data only, not the table design. The target table for the data copy must already exist in the target database. You can export the design of the table using EXPORT_OBJECTS or EXPORT_CATALOG.
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.
Note
Pipe Viewer isn't a standard Linux command, so you will need to download and install it yourself. See the Pipe Viewer page for download packages and instructions. Vertica does not support Pipe Viewer. Install and use it at your own risk.
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.
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:.
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):
