Vertica Blog

Vertica Blog

query

NLP augmented analytics is like Ask Me Anything with your data

Natural Language Processing Augmented Analytics

By simplifying the asking of questions and understanding of answers, NLP augmented analytics broadens the number of people who can use analytic technology

List the Missing Dates in a Date Series

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)

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

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...
Larry Bird Monument - Photo by Eric Kilby WC

Practice Makes Perfect at BDC 2020

Larry Bird, Boston Celtics legend and NBA Hall of Famer, shot 500 free throws every morning before his first class in high school. But, he didn’t stop there. Larry was known to shoot 100 free throws after every NBA game. The result? The sharp-shooting forward from French Lick, Indiana led the NBA in free throw...
Cubes with binary ones and zeroes on them clustered together floating in blue space with light shining out

2 – What Are the Benefits of Using Complex Data Types in SQL?

Co-authored by James Clampffer, Deepak Majeti. In this second post in the Complex Data Types blog series, let’s look at some of the reasons complex types are used extensively in organized big data formats like ORC and Parquet. Be sure to read the first post in this series where we discussed the what complex data...
Construction site crane building a blue SQL 3D text.

Get the Row Count from an Outer Table Join

Aggregate functions summarize data over groups of rows from a query result set. When using an aggregate function like COUNT with an "*" or "1" parameter value, you may get a different result when the query implements a LEFT join verses an INNER join. If you want a row count of just the rows from...
Create Read Update Delete in red 3D letters surrounded by 3D ones and zeroes

Vertica 9.3 Supports UPDATE and DELETE Operations on Tables Having Live Aggregate Projections!

A live aggregate projection contains columns with values that are aggregated from columns in its anchor table. When you load data into the table, Vertica aggregates the data before loading it into the live aggregate projection. On subsequent loads (for example, through INSERT or COPY) Vertica recalculates aggregations with the new data and updates the...
Focused young woman working at laptop in office

The Vertica Academy Is Open for Learning

One of the greatest impacts from digital transformation, both from a personal and professional point of view, is not just the democratization of data - a very popular topic here at Vertica! - but the democratization of knowledge. Today, we are excited to announce the new Vertica Academy, a platform that offers self-paced, technical training...
Passenger balloon floating in blue sky

Announcing Vertica Version 9.2.1 – Take Analytics Efficiency to the Next Level

This week, some very cool things have gone into the newest version of the Vertica Analytics Platform that make your analytics fly and save on your AWS budget. The theme for this release is Improving Vertica in Eon Mode Integration with Amazon S3, and there are a whole bunch of new features designed to do...

Use MERGE to Update 1 Million Rows in 2 Seconds

This blog post was co-authored by Yassine Faihe, Michael Flower, and Moshe Goldberg. Updating One Million Records in Two Seconds To illustrate the true power of MERGE, this article describes how we used MERGE to demonstrate Vertica's performance at scale. SQL MERGE statements combine INSERT and UPDATE operations. They are a great way to update...

The Life of a Query, According to Henry Ford