Vertica Blog

Vertica Blog

Vertica Concepts

Authentication Methods for dbadmin

Soniya Shah, Information Developer
January 3, 2018

This blog post was authored by Sumeet Keswani. In Vertica, when you create a new database, there are no configured authentication methods. In this case, Vertica assumes that all users, including the dbadmin, have an implicit password authentication. Users can use this authentication method both for authenticating over a network interface and for over a...

Flattened Tables

Soniya Shah, Information Developer
December 4, 2017

This blog post was authored by Soniya Shah. Before release 8.1., Vertica users could denormalize their data by combining all fact and dimension table columns in a single 'fat' table. These tables facilitated faster query execution. However, this approach required users to maintain redundant sets of normalized and denormalized data, which incurred its own overhead....

Phrase Search with Vertica Text Search

Soniya Shah, Information Developer
November 27, 2017

This blog post was authored by Serge Bonte. Vertica Text Search Vertica already provides Text Search. Text Search allows you to quickly search the contents of a single CHAR, VARCHAR, LONG VARCHAR, VARBINARY, or LONG VARBINARY field within a table to locate a specific token. Vertica implements that capability using a dedicated Text Index to...

Effective vsql in Vertica

Soniya Shah, Information Developer
October 10, 2017

This blog post was authored by Maurizio Felici. vsql is included in each Vertica installation and is lightweight, with a tight integration with Vertica. Vsql is installed on every Vertica server and can also be installed on non-server hosts using the client package. Executing SQL commands through vsql is often faster than navigating GUI's menu....

Analytic Queries in Vertica

Soniya Shah, Information Developer
October 2, 2017

This blog post was authored by Soniya Shah. Analytic functions handle complex analysis and reporting tasks. Here are some example use cases for Vertica analytic functions: • Rank the longest standing customers in a particular state • Calculate the moving average of retail volume over a specific time • Find the highest score among all...

Working with Joins

Soniya Shah, Information Developer
September 26, 2017

This blog post was authored by Soniya Shah. Vertica supports a variety of join types. This post discusses the following joins: • Inner joins • Left, right, and full outer joins • Natural joins • Cross joins In Vertica, we refer to the tables participating in the join as left or right. The left table...
Three 3D arrows, different colors pointing in different directions

Time Series Analytics

Soniya Shah, Information Developer
September 18, 2017

This blog post was authored by Soniya Shah. Time series analytics is a powerful Vertica tool that evaluates the values of a given set of variables over time and groups those values into a window based on a time interval for analysis and aggregation. Time series analytics is useful when you want to analyze discrete...

Concurrency and Workload Management

Soniya Shah, Information Developer
June 26, 2017

This blog post was authored by Soniya Shah. Vertica workloads range from simple primary key lookups to analytical queries that include several large tables and joins. Different types of load jobs must keep the data updated. Vertica has a mixed-workload management capability that is easy to use. Vertica can process queries both concurrently and in...

Understanding Users, Privileges, and Roles

Soniya Shah, Information Developer
June 12, 2017

This blog post was authored by Soniya Shah. Every Vertica database has one or more users. When users connect to the database, they log in with credentials that a superuser defines. Database users should only have access to the database resources they need to perform their tasks. To navigate these necessities, Vertica has designated users,...

Getting Rid of Range Joins

Soniya Shah, Information Developer
June 1, 2017

This blog post was authored by Soniya Shah. You can use range joins to categorize data into buckets. Vertica provides performance optimizations for =, and BETWEEN predicates. These optimizations are particularly useful when a column from one table is restricted to be in a range specified by two columns of another table. Range joins can...
Modern Database Analytics

Big Flat Fact Tables

Soniya Shah, Information Developer
May 11, 2017

This blog post was authored by Steve Sarsfield. For decades, it's been widely accepted that snowflake and star schemas facilitate getting optimal performance from your data warehouse. You normalize data by identifying the rows of data that you typically ingest, and creating a schema that is optimized for the types of queries you want to...

Understanding AT TIME ZONE

Soniya Shah, Information Developer
April 4, 2017

TIMESTAMPTZ AT TIME ZONE and TIMESTAMP AT TIME ZONE return date input in another time zone. How Vertica executes AT TIME ZONE varies, depending on whether the input is a TIMESTAMPTZ or TIMESTAMP. At first glance, this might be confusing. More about that later. First, let's review AT TIME ZONE syntax: { TIMESTAMPTZ | TIMESTAMP...