Vertica Concepts

Simulate NULLS FIRST and NULLS LAST in the ORDER BY Clause

When your query contains the ORDER BY clause to sort the result set, alphanumeric NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC), while integer NULL data does the opposite. Example: dbadmin=> \d test List of Fields […]

Do you have experience using Vertica?

The Vertica Product Management team is collecting feedback on what new features and functionality our customers would like to see added to the product. We’ve been actively gathering product-related feedback for nearly a year and have been using it to make more informed decisions about how we manage the Vertica product roadmap. Thank you for […]

Using sed to Handle Empty String NULL Values Enclosed in Control Characters

You can load data files into a Vertica table using the COPY command. Your data can be delimited and separated by control characters. Unfortunately if you also enclose NULL values as an empty string, you might run into a data cast issue. Example: dbadmin=> \d test1 List of Fields by Tables Schema | Table | […]

Tracking the Current Transaction Start Date and Time

The built-in Vertica function TRANSACTION_TIMESTAMP returns a value of type TIME WITH TIMEZONE, which represents the start of the current transaction. It’s very useful for keeping track of when the transaction started for a group of table inserts. Example: dbadmin=> SELECT * FROM test; c1 | created_timestamp | created_transaction_timestamp —-+——————-+——————————- (0 rows) dbadmin=> INSERT INTO […]

View Tuple Mover Operation Run-time Intervals

Vertica’s Tuple Mover manages WOS and ROS data storage. To do that, the Tuple Mover performs two operations: Moveout: Moves data from WOS to ROS. Mergeout: Combines small ROS containers into larger ones and purges deleted data. The Tuple Mover operations are controlled by a set of configurable parameters. To quickly view the relevant parameter’s […]

Auditing Flex Tables Quick Tip

The AUDIT_FLEX function returns the estimated ROS size of __raw__ column in a Flex table, equivalent to the export size of the flex data in the audited objects. Example: dbadmin=> CREATE FLEX TABLE flex_tables.flex(); CREATE TABLE dbadmin=> INSERT INTO flex_tables.flex SELECT 1 c1, 1 c2; OUTPUT ——– 1 (1 row) dbadmin=> INSERT INTO flex_tables.flex SELECT […]

Auditing Database DDL Operations: Quick Tip

Starting with Vertica 9.2, you can now track all DDL operations (i.e. CREATE, ALTER, TRUNCATE, etc.) that are being executed in your database! The LOG_QUERIES system table provides summary information about those operations. Example: dbadmin=> SELECT issued_time, dbadmin-> user_name, dbadmin-> audit_type, dbadmin-> request_type, dbadmin-> request dbadmin-> FROM log_queries dbadmin-> WHERE session_id = current_session() dbadmin-> ORDER […]

Elastic Throughput Scaling: Overview

Manvendra Panwar authored this post. Elastic Throughput Scaling (ETS) enables Vertica to increase concurrency through which we can achieve higher throughput. ETS can take advantage of redundant shard subscriptions to automatically identify the nodes in the cluster that can execute a query. More Nodes (N) than Shards (S): Achieving higher throughput • S nodes can […]

Finding All Columns Storing Some Value: Quick Tip

A long time ago a database developer asked me how she could produce a list of all table columns in the database which store a given value. Note that she was in the process of normalizing tables and wanted to find every fact table that referenced a particular string value to replace it with a […]

Viewing External Table Definitions: Quick Tip

An alternative to importing data into Vertica is to query it in place. For that we use external tables. Once an external table is created you can view its definition via the TABLES system table. Example: dbadmin=> \! cat /home/dbadmin/ext.txt 1|TEST1 2|TEST2 dbadmin=> CREATE EXTERNAL TABLE ext (c1 INT, c2 VARCHAR(10)) AS COPY FROM ‘/home/dbadmin/ext.txt’; […]

Multi-Factor Authentication for Vertica with Kerberos and Duo

This blog post was authored by Poojan Khanpara, Tom Wall, and Elizabeth Michaud. Multi-factor authentication (MFA) provides a secure method for validating users on login. And as we mentioned in another recent post, GDPR Ready Recipe, the full implementation of GDPR may soon make multi-factor authentication a legal necessity for vendors doing business in the […]

Don’t Get Stuck With a Technology Dinosaur

Unless you have been hiding out in Jurassic Park, you’ve probably heard that most of the IBM PureData System models, aka Netezza, are going the way of the dinosaur this summer. With the announcement of the end of support for one of the first data warehouse appliances, it’s time to look at where to go […]

View Vertica Features Recently Used: Quick Tip

Curious if Vertica features are being used in your database? If so, the data collector table DC_FEATURES_USED stores recently used Vertica features (i.e. commands, meta-functions and UDXs). Example: dbadmin=> SELECT node_name, component, description dbadmin-> FROM data_collector dbadmin-> WHERE table_name = ‘dc_features_used’; node_name | component | description ——————–+————–+——————————– v_test_db_node0001 | FeaturesUsed | Vertica features used recently […]

Try Vertica in a Virtual Machine

Curious about Vertica? Check out the Vertica Community Edition VM! It’s a free, quick, and an easy hands-on introduction. In the VM, you’ll find everything up and running. You won’t have any setup to do. You’ll find a single-node installation of Vertica Community Edition with the VMart example database, the Vertica client tools, and a […]

Determining the Current K-Safety: Quick Tip

K-safety sets the fault tolerance in your Vertica database cluster. The value K represents the number of times the data in the database cluster is replicated. These replicas allow other nodes to take over query processing for any failed nodes. You can view the current K-Safety of a Vertica database in several ways. Example: dbadmin=> […]

Encode Projection Columns with Zstandard Compression: Quick Tip

Vertica 9.2 now supports the Zstandard Compression Encoding Type which offers higher compression than the BZIP and GZIP Encoding Types! Example: dbadmin=> 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 […]

Authentication Methods for dbadmin

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

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

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

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

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

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 […]

Time Series Analytics

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

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

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

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 […]

Big Flat Fact Tables

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

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 […]

Create and Assign Roles

A role is a collection of privileges that can be granted to one or more users or roles. Assigning roles prevents you from having to manually grant sets of privileges for each individual user. For the most part, creating and assigning roles is fairly straightforward. However, the user to which roles are assigned needs to […]

Filtering Data While Loading into Vertica

Suppose you have a CSV file and you want to copy some, but not all, of its contents into a Vertica table. There are two ways you can to do this: • Use the SKIP keyword with COPY. • Use the head or tail Linux command. Let’s see how this works. The Data Here’s a […]

Dynamic Row and Column Access Policies

The content of this blog post is based on an article authored by Maurizio Felici. The Vertica Analytic Database access policies act on columns and rows to provide extra security on data in your tables. You can create flexible access policies that limit which users can access certain data by applying the access policy to […]

LDAP and User Accounts

This blog post was authored by Soniya Shah. If you are a database administrator, you probably need to authenticate users in Vertica. There are many methods users can use to authenticate, including Ident, Kerberos, LDAP, and hash. This blog walks you through the steps to take if you want to authenticate some users using LDAP […]

The Life of a Query, According to Henry Ford

While Henry Ford did not in fact develop or even patent the modern assembly line (that credit goes to Ransom E. Olds), he relied heavily on the process for automobile production.

Redesigning Projections for Query Optimization

When you submit a query to Vertica, the Vertica query optimizer automatically assembles a query plan, which consists of a set of operations to compute the requested result. Depending on the properties of the projections defined in your database, the query optimizer can choose faster and more efficient operations. Thus, it?s important to recognize what you can do to optimize your projections to improve query performance

Troubleshooting Vertica Query Performance with System Tables

Do you want to learn how to troubleshoot your query performance issues?  We’ve got you covered. Just attend the Query Performance Tuning and Troubleshooting Issues session at Vertica’s Big Data Conference.

Vertica Joins: A Refresher

As a Vertica user, you know that using joins can improve query performance by combining records from one or more tables. But sometimes, you need to develop complex joins. Vertica supports many different kinds of joins that perform different functions based on your needs.

Tell the Optimizer You Have a Better Plan

You’re upgrading your database and some query appears to execute less efficiently than before. What can you do? Vertica addresses this concern in Vertica 7.2 with directed queries