Effective vsql in Vertica

This blog post was authored by Maurizio Felici.

vsql is included in each Vertica installation and is lightweight, with a tight integration with Vertica. Vsql is installed on every Vertica server and can also be installed on non-server hosts using the client package. Executing SQL commands through vsql is often faster than navigating GUI’s menu. vsql is also well integrated with editors and shell. This document describes how you can make the most of vsql and Vertica.

vsql for Interactive Sessions

vsql uses libtecla to provide command line editing features:

For a full list of libtecla capabilities, see the documentation.

vsql saves your commands in a file (~/.vsql_history) and you can navigate your history using the up and down arrow keys. You can also use the following settings to manage your history:

• To change the number of commands in the history file, using the histsize function. The default is 500:
\set histsize 1000
• To avoid saving repeated commands, using the histcontrol ignoredups function:
\set histcontrol ignoredups
• To avoid saving commands that start with a space, using the histcontrol ignorespace function:
\set histcontrol ignorespace

You can also use the tab key to do the following:

Export and Load Data Using vsql

If you see two consecutive field separators in the output file, you might wonder if they are NULL values or EMPTY strings. Specify the string used to represent REAL NULL values:
$ vsql -F $'\001' -R $'\002' -P null='MYNULL' -AXtnqc " SELECT * FROM

This way, you can distinguish EMPTY strings from NULL VALUES. You can also use the COPY command to load data that is exported this way:

COPY <table> FROM '<file>' delimiter E'\001' record terminator E'\002' null 'MYNULL' ...

You might also want to use different SELECT commands in parallel to extract data. You must extract the same amount of data for each data stream:
$ for n in {0..3} ; do
{ vsql … -c "SELECT * FROM <table> WHERE hash(<column>) % 4 = ${n}" –o file_${n} & }
done

vsql and Shell Integration

You can set the following environment variables in your shell to avoid setting the corresponding values in the vsql command line:
export VSQL_HOST=172.16.61.104 # Target host ( -h hostname )
export VSQL_DATABASE=vmftest # Database name ( -d dbname )
export VSQL_PORT=5433 # Port number ( -p port_number )
export VSQL_USER=dbadmin # User name ( -U username )
export VSQL_PASSWORD=secret # Password ( -w password )
export VSQL_EDITOR=vim
You can pass one or more shell variables to vsql using the following syntax:
-v =
You can use here document syntax to run vsql queries from within a shell script:
$ cat query.sh
#!/bin/bash
suppkey=3701
eprice=13309.60
cdate="'1996-03-05'"
shipmode="'REG AIR'"
vsql -X -x -f - <<-EOF select * from tpch.lineitem where l_suppkey = ${suppkey} and l_extendedprice = ${eprice} and l_commitdate = ${cdate} and l_shipmode = ${shipmode} ; EOF echo "Continue with your shell script"
You can also run a complete benchmark by running the same query multiple times and then extracting values such as the average or median:
$ for q in /path/to/scripts/*.sql ; do
echo "Now running ${q}”
for i in {1..5} ; do
vsql –X –o /dev/null –q –f ${q} \
| sed -n 's/^.*formatted: \([0-9.]*\) ms$/\1/p'
done | Rscript -e 'x <-scan(file="stdin"); summary(x)' done
The output of the query looks like the following:
Now running q01.sql
Min. 1st Qu. Median Mean 3rd Qu. Max.
224.8 227.8 243.4 282.1 274.5 439.9
...

Customizing vsql

vsql reads and executes commands in your $HOME/vsqlrc unless you use the –X command line option. You can use your .vsqlrc to customize your vsql sessions as shown in the following:
-- Print timing
\timing
--Enable the \timing command
-i –-timing
-- Print NULL values as (null)
\pset null '(null)'
-- Set prompt like this: "dbadmin@vmftest (14:14:16) SQL>"
\set PROMPT1 '%n@%/ (%`date +%T`) SQL> '
\set PROMPT2 '%n@%/ (%`date +%T`) SQL> '
-- Save 2000 commands in the history file
\set histsize 2000
-- Ignore duplicates and commands starting with space
\set histcontrol ignoreboth
-- Default emacs editing mode
\edit-mode emacs
-- Rebind standard tecla keys
\bind ^L list-history
\bind ^W change-case
\bind ^N forward-word
\bind ^P backward-word
\bind ^R history-search-backward