Vertica Blog

Vertica Blog

VSQL

Helpful Tips message on post-it note

Eonclose CSV File With Fields Enclosed by Quotes

Jim Knicely, Vertica Principal Solution Architect
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, but 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

Jim Knicely, Vertica Principal Solution Architect
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 Principal Solution Architect
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 Principal Solution Architect
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 Principal Solution Architect
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 Principal Solution Architect
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: Have fun!
Business card that says Expert Tips,

Stop a Stubborn Query that Won’t Cancel

Jim Knicely, Vertica Principal Solution Architect
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 Principal Solution Architect
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: Have fun!
Hand holding old-fashioned brass balance scale on a black background

View the History of Connection Load Balance Operations

Jim Knicely, Vertica Principal Solution Architect
August 9, 2019

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)

Jim Knicely, Vertica Principal Solution Architect
August 8, 2019

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

Jim Knicely, Vertica Principal Solution Architect
August 7, 2019

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...
Disk usage, traffic, and other usage concerns

Diving into Disk Usage

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
August 6, 2019

Would you like to know how much disk space Vertica is using as it runs? This could be useful for capacity planning, monitoring trends, or debugging. Here are some ways to follow disk usage trends and also look at temporary events like Tuple Mover and Join Spills: Helpful Link: Have fun!