Restarting a Downed Node: Quick Tip

Jim Knicely authored this tip. I’m a big fan of scripting with admintools which provides us with many database tools. One of those awesome tools, that I just became familiar with, is command_start. It allows us to start a downed node, with one caveat: you have to run the command on the node that is […]

Plotting data with vsql and gnuplot

Maurizio Felici authored this post. We all know how plotting data is a very useful – and widely adopted – technique. Linux (and MacOS) offer a wonderful command line tool to plot data called gnuplot. You can install and run gnuplot on one of your Vertica nodes so you don’t have to move data out […]

What’s New in Vertica 9.2?

Soniya Shah authored this post. The latest version of Vertica was released earlier this month and with it comes a host of new features. In Vertica 9.2, we introduced several new features including: Eon Mode Updates Management Console changes Performance improvements Upgrade and installation changes Voltage SecureData updates Eon Mode Updates The Eon Mode updates […]

Handling Expression Errors in COPY Statements: Quick Tip

Jim Knicely authored this tip. We can transform data as it is loaded via a COPY command using a FILLER column and a COPY expression. In the example below, a COPY expression uses the LOWER function to convert uppercase text to lowercase as it is loaded. Example: dbadmin=> CREATE TABLE test (a INT, b VARCHAR(10)); […]

Encode Projection Columns with Zstandard Compression: Quick Tip

Jim Knicely authored this tip. CREATE TABLE test (a VARCHAR(10000), b VARCHAR(10000), c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000)); CREATE TABLE dbadmin=> CREATE PROJECTION test_pr dbadmin-> (a ENCODING BZIP_COMP, dbadmin(> b ENCODING GZIP_COMP, dbadmin(> c ENCODING ZSTD_COMP, dbadmin(> d ENCODING ZSTD_FAST_COMP, dbadmin(> e ENCODING ZSTD_HIGH_COMP) AS dbadmin-> SELECT a, b, c, d, e dbadmin-> FROM test; […]

Display Tables Referenced by a View: Quick Tip

Jim Knicely authored this tip. Vertica 9.2 introduces the new VIEW_TABLES system table that shows details about view-related dependencies, including the table that reference a view, its schema, and owner. Example: dbadmin=> CREATE TABLE base_table (c INT); CREATE TABLE dbadmin=> CREATE TABLE base_table2 (c INT); CREATE TABLE dbadmin=> CREATE OR REPLACE VIEW base_table_vw AS dbadmin-> […]

Calculate the Catalog Size in Memory on Each Node: Quick Tip

Jim Knicely authored this tip. The RESOURCE_POOL_STATUS system table provides the current state of built-in and user-defined resource pools on each node, including memory usage. The METADATA built-in pool tracks memory allocated for catalog data and storage data structures. Knowing those two facts, we can calculate catalog memory usage on all nodes by querying the […]

Labeling Queries: Quick Tip

Jim Knicely authored this tip. To quickly identify queries for profiling and debugging purposes, include the LABEL hint. Later you can search the QUERY_REQUESTS and QUERY_PROFILES systems table for the label! Example: dbadmin=> CREATE TABLE big_table_test AS /*+ LABEL(big_table_test_label) */ SELECT * FROM big_table; CREATE TABLE dbadmin=> SELECT request FROM query_requests WHERE request_label = ‘big_table_test_label’; […]

Create a User Defined SQL Function to Calculate Fibonacci Numbers: Quick Tip

Jim Knicely authored this tip. The Fibonacci Sequence is the series of numbers (i.e. 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, …) where the next number in the sequence is found by taking the sum of the previous two numbers. Calculating these numbers in SQL would be a bit complicated if it […]

Calculate Future Thanksgiving Dates: Quick Tip

Jim Knicely authored this tip. In the United States, Thanksgiving is always celebrated on the fourth Thursday of November. In Canada, Thanksgiving is celebrated the second Monday in October. So that you don’t miss another holiday at home with mom, make sure to plan ahead and calculate (using SQL) future Thanksgiving dates! Example: dbadmin=> SELECT […]