Vertica Blog

Vertica Blog

VSQL

Vintage businessman concept wearing futuristic helmet at office

Quick Tip: Combine SQL and vsql Meta-commands on the vsql Command Line

Jim Knicely, Vertica Field Chief Technologist
June 10, 2021

When using vsql, the -c (--command) flag lets you pass a single statement to vsql as a string and then exits. Examples: While you can't combine a SQL statement with a vsql meta-command... ...You can use echo to combine and pipe SQL statements and vsql meta-functions to vsql! Have Fun!
Visual Storytelling

VSQL, Visual Storytelling through Query Language

Moshe Goldberg, Vertica System Engineer
May 21, 2020

This tip expands on my earlier post: “Pure Vertica SQL Graph Flavors” Visual story telling has not just the ability to capture attention, it make things simple to understand. Consider the following to boost your query result set readability. What is the visual correlation between CPU utilization and Canary Queries elapsed time?      ...
Helpful Tips message on post-it note

Export to CSV File With Fields Enclosed by Quotes

Jim Knicely, Vertica Field Chief Technologist
March 29, 2020

It's easy to export table data to a CSV file using vsql. Although there isn't a VSQL option to enclose each output field in quotes, you can accomplish that task with the Linux sed command. Once the formatting looks good, you can redirect the output to your CSV file: Helpful Links: Exporting Data Using VSQL...
Hand writing the text: Helpful Tips

Clearing the Screen in VSQL

Jim Knicely, Vertica Field Chief Technologist
January 27, 2020

Lots of old school folks use VSQL for administrative tasks. As you enter commands, eventually the command prompt will end up at the bottom left of the screen making it a little difficult to read any additional output from your commands. You can clear the screen and move the prompt back up to the top...
Photo of actual roadrunner running on a road showing yellow stripe in the middle

Have VSQL Beep Upon Command Completion

Jim Knicely, Vertica Field Chief Technologist
September 6, 2019

There are a lot of cool command line options available to VSQL. One of my favorites is the –b option which causes Vertica to “beep” when a command completes. This is very useful if you are running a very long sequence of SQL commands via a script. You can go off and do other things...
hands sweeping into a dustpan

Easy Development Schema Cleanup (i.e., Have Vertica Drop a Bunch of Tables for You)

Jim Knicely, Vertica Field Chief Technologist
September 5, 2019

On a development database, I have a lot of tables in the PUBLIC schema having similar names starting with “test_”. I’d like to drop all of these tables quickly. To do that, I can have Vertica generate the DROP commands and then execute them for me! P.S. Be careful with this command! Make sure when...
Helpful Tips in blue text with magnifying glass

Investigate Data Collector Table Data Produced by Scrutinize

Jim Knicely, Vertica Field Chief Technologist
September 5, 2019

The diagnostics tool scrutinize collects a broad range of information from a Vertica cluster. Part of the output from scrutinize includes data collector table data. Once extracted, we can easily load the data from a data collector table file into a Vertica Flex Table for analysis! Let’s take a look at the data from the...
Cow peering around edge of frame with blue sky and clouds in shape of a comic book thought bubble.

Make a Cow Display Query Results in VSQL

Jim Knicely, Vertica Field Chief Technologist
August 19, 2019

I used to have fun with the amusing old Linux command Cowsay. It inserts any input into a word bubble and draws an ASCII cow to talk to you. You can make the cow display your query results in VSQL! Helpful Links: https://en.wikipedia.org/wiki/Cowsay https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm Have fun!
Business card that says Expert Tips,

Stop a Stubborn Query that Won’t Cancel

Jim Knicely, Vertica Field Chief Technologist
August 19, 2019

Sometimes a running SQL statement hangs and cannot be stopped using the CLOSE_SESSION or INTERRUPT_STATEMENT function. To stop it, first grab the culprit’s SESSION_ID, TRANSACTION_ID, and STATEMENT_ID from the QUERY_REQUESTS system table, then you can move it to a resource pool that has no resources! After the move and subsequent re-plan, the query will “usually”...
Compass rose with true north pointing to "Helpful Tips" text

Change Your User Password in VSQL with a Meta-Command

Jim Knicely, Vertica Field Chief Technologist
August 14, 2019

The \password VSQL meta-command can be used to change your database user’s password! The admin user can change the database password for any user: Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm Have fun!
Hand holding old-fashioned brass balance scale on a black background

View the History of Connection Load Balance Operations

Jim Knicely, Vertica Field Chief Technologist
August 9, 2019

The Data Collector table DC_LOAD_BALANCE_OPERATIONS tracks the history of connection load balance operations. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ManagingClientConnections/LoadBalancing/ConnectionLoadBalancing.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/RetainingMonitoringInformation.htm Have fun!
Hand holding old-fashioned brass balance scale on a black background

Load Balance Older Clients (That You Can’t Upgrade for Some Weird Reason)

Jim Knicely, Vertica Field Chief Technologist
August 8, 2019

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries as well as vsql. To load balance vsql you can use the - -enable-connection-load-balance or -C command line options. In really old versions of vsql those options did not exist. How do you load balance connections from those...