Vertica Blog

Vertica Blog

Quick Tips

Red arrow points to silver ball with 4 red arrows pointing away representing dividing a single thing into multiple

Parallel Processing Using Partitions With Vertica UDx

You can add functionality to Vertica using UDx, but what if you need to process more data than can be efficiently processed in a single thread or a single node? Vertica can divide data into partitions defined with the OVER() clause and distribute computing across nodes. This partition processing is "shared-nothing" similar to the Map...
Vertica and Python logos stacked

Extending Vertica with Python functions: Adding NumPy FFT as a UDx

User-Defined Transform Function (UDTF) support for Python UDx were added back in Vertica 9.1, allowing you to add a much greater range of existing libraries and functions to Vertica. In this example, I'll add Fast Fourier Transform (FFT) from the NumPy package. FFT is a way to transform time-domain data into frequency-domain data. My test...
Clusters of points colored differently by grouping on a black background

Finding the “K” in K-means Clustering With a UDx

You can apply k-means clustering to partition data points into k different groups. Along with the data, the number of clusters "k" is an input to the algorithm. Common examples like the Iris data set tell you upfront how many different groups exist, so you set k=3. What if you don't know how many clusters...
Hand writing the text: Helpful Tips

Tracking Raw Schema Size Over Time

There is no way to go back in time to see how large a schema was a day/month/year ago. But you can use the AUDIT function to get a “raw” size of a schema and track growth over time by storing the results from a daily audit. Using cron, you can run this daily. Helpful...
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...
Construction site crane building a blue SQL 3D text.

Load Data Enclosed By and Embedded With Double Quotes

Loading string data that is enclosed in double quotes where the string also contains double quotes can be problematic. We could alter the data so that it includes an escape character prior to each embedded double quote, but maybe that’s not possible. Another solution would be to use a FILLER to load the data then...
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: 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

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: 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

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!