Vertica Blog

Vertica Blog

VSQL

Visual Storytelling with SQL

VSQL, Visual Storytelling through Query Language

This tip expands on my earlier post: “” 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

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: Have fun!
Hand writing the text: Helpful Tips

Clearing the Screen in VSQL

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

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)

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

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

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: Have fun!
Business card that says Expert Tips,

Stop a Stubborn Query that Won’t Cancel

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

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: Have fun!
Hand holding old-fashioned brass balance scale on a black background

View the History of Connection Load Balance Operations

The Data Collector table DC_LOAD_BALANCE_OPERATIONS tracks the history of connection load balance operations. Helpful Links: 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)

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries as well as . 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...
Hand holding old-fashioned brass balance scale on a black background

Testing the Native Load Balancer

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries as well as . The RUN_LOAD_BALANCE_POLICY function returns a string formatted as address:port that results from running the current Native Load Balancer policy. It’s a great tool for testing after enabling a new Load Balance Policy! Because the...