Vertica Blog

Vertica Blog

Quick Tips

Helpful Tips in blue text with magnifying glass

Quick Tip: Determining Database “Cell” Count

James Knicely, Vertica Field Chief Technologist
July 14, 2021

The Vertica system tables LICENSE_AUDITS and USER_AUDITS include a mysterious column called CELL_COUNT. What exactly is the Cell Count? It's simply a calculation that looks like this: DATABASE CELL_COUNT = (Table_1 Column Count * Table_1 Row Count) + (Table_2 Column Count * Table_2 Row Count) + … + (Table_n Column Count * Table_n Row Count)...
SQL

Quick Tip: WITH Clause Recursion

James Knicely, Vertica Field Chief Technologist
June 11, 2021

Starting with version 10.1.x, Vertica supports recursive queries. These are incredibly useful in modeling and working with self-referential hierarchical data. Let's take a look at a natural instance of this form of data: the manager-subordinate relationship. The following table contains employees (EMP_NAME), their IDs (EMP_ID), and the IDs of their managers (EMP_MGR_ID). For example, the...
Vintage businessman concept wearing futuristic helmet at office

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

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

Quick Tip: Create User-Defined Inverse Hyperbolic Functions

James Knicely, Vertica Field Chief Technologist
May 28, 2021

Vertica includes a ton of built-in mathematical functions to help you on your analytic journey, and creating new ones is quick and easy. Let's create the inverse hyperbolic functions: ACOSH - To compute the inverse (arc) hyperbolic cosine of its argument. ASINH - To compute the inverse (arc) hyperbolic sine of its argument. ATANH -...
Tips and Tricks Orange Button

Quick Tip: Create “Super Wide” Tables

James Knicely, Vertica Field Chief Technologist
May 27, 2021

Starting in Vertica 10.1.0, you can go super wide with up to 9,800 column definitions in a table! Prior to Vertica 10.1.x, tables were limited to 1,600 columns. Example Creating a table with 9,800 columns: Hint: Try to avoid running a SELECT * FROM … a table with 9,800 columns and a trillion+ rows 😊...
Hand writing the text: Helpful Tips

Quick Tip: Use a “Seed” Table to Populate a Table with Random Values

James Knicely, Vertica Field Chief Technologist
May 26, 2021

Oftentimes, I need to do some testing with randomly generated data. For instance, if I need to insert a million rows I used to cross join several system tables with one another in an INSERT statement. Method 1 (bad): CROSS JOIN Suppose we need a table full of many random UUIDs. We could do this...
Helpful Tips message on post-it note

Quick Tip: EXPORT TO PARQUET Compression with GZIP, Brotli, and ZSTD

James Knicely, Vertica Field Chief Technologist
May 25, 2021

EXPORT TO PARQUET exports a table, columns from a table, or query results to files in the Parquet format. These Parquet files use Snappy compression by default. Starting in Vertica 10.1.1, EXPORT TO PARQUET supports the GZIP, Brotli, and ZSTD compression types! Let’s see how these compression types compare in disk usage: Snappy compression: GZIP...

List the Missing Dates in a Date Series

James Knicely, Vertica Field Chief Technologist
October 22, 2020

The TIMESERIES clause in Vertica makes it easy to list all of the gap days (that is, those that are missing) from a series of dates. Say I have this simple date series: As you can see, there are several dates missing from the series (2020-10-30, 2020-11-02, 2020-11-03 and 2020-11-04). I can list these absent...
Compass rose with true north pointing to "Helpful Tips" text

Getting Every Nth Row from a Table (Enhanced)

James Knicely, Vertica Field Chief Technologist
October 20, 2020

In a previous Vertica Quick Tip we learned how to query every Nth row from a table when we had a unique ID column having sequential values (that is, from a sequence). See, https://www.vertica.com/blog/vertica-quick-tip-getting-every-n-th-row-table/ What if we don’t have a key like that in our table? No problem thanks to the ROW_NUMBER() Analytic Function! Say...

Extracting Log Data into Vertica With Regex Parsing

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
October 5, 2020

Formatted text such as system logs hold a huge amount of actionable data if you can extract and discover the content. If there is a pattern to the lines, then you can build a Vertica regex parser to extract fields and contents into Vertica for query and analysis at scale. Vertica’s regex parser uses Perl-like...
Electronic medical record on digital smart device

Vertica in Good Health: Ingesting, Securing, and Analyzing Healthcare Data

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
September 29, 2020

Electronic Medical Records and new personal healthcare technology, particularly medical devices, generate a huge amount of data. Organizations including Cerner and Philips Healthcare rely on Vertica for data analysis. How can Vertica help you analyze this data? Vertica excels at data ingest and provides hundreds of built-in SQL and analytic functions with broad integration with...
Quick Tip - blue button

Copy a Table with an Identity to a Table with an Identity on the Same Database Keeping the Identities in Sync

James Knicely, Vertica Field Chief Technologist
May 25, 2020

An IDENTITY associates a table column with a sequence. This sequence automatically increments the column value as new rows are added. There are some noteable restrictions on IDENTITY columns: You cannot INSERT values manually into an IDENTITY column You cannot UPDATE the values of an IDENTITY column once they has been populated via the sequence...